Instance Status RESTRICTED in Oracle Listener

When you don't want any user to connect to database, you can simply restrict your instance from any incoming connection. But if you don't know this concept and you have come across the below error, it means your database is in sort of maintenance mode and you need to disable it to enable users to connect to it via listener.

Note: Maintenance mode still allows users to connect to database without listener, of course you can only do it when you are on DB server and you are not using listener for DB connection i.e. no use of @SID.

PS C:\Users\parvi> lsnrctl status

LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 07-JAN-2025 21:50:56

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DemonoftheFall)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
Start Date                07-JAN-2025 21:48:30
Uptime                    0 days 0 hr. 2 min. 25 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\oracle\product\19.3.0\dbhome_1\network\admin\listener.ora
Listener Log File         C:\app\oracle\product\19.3.0\dbhome_1\log\diag\tnslsnr\DemonoftheFall\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DemonoftheFall)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "cdb1" has 1 instance(s).
  Instance "cdb1", status UNKNOWN, has 1 handler(s) for this service...
Service "cdb2" has 1 instance(s).
  Instance "cdb2", status UNKNOWN, has 1 handler(s) for this service...
Service "practice" has 2 instance(s).
  Instance "practice", status UNKNOWN, has 1 handler(s) for this service...
  Instance "practice", status RESTRICTED, has 1 handler(s) for this service...
Service "practiceXDB" has 1 instance(s).
  Instance "practice", status RESTRICTED, has 1 handler(s) for this service...
The command completed successfully

Let's fix this situation.

PS C:\Users\parvi> set ORACLE_SID=practice
PS C:\Users\parvi> sqlplus sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 7 22:02:04 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
SQL>
SQL> alter system disable restricted session;

System altered.

SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

Check Listener status and it should show the instance with READY state now.

PS C:\Users\parvi> lsnrctl status

LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 07-JAN-2025 21:52:57

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DemonoftheFall)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
Start Date                07-JAN-2025 21:48:30
Uptime                    0 days 0 hr. 4 min. 26 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\oracle\product\19.3.0\dbhome_1\network\admin\listener.ora
Listener Log File         C:\app\oracle\product\19.3.0\dbhome_1\log\diag\tnslsnr\DemonoftheFall\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DemonoftheFall)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "cdb1" has 1 instance(s).
  Instance "cdb1", status UNKNOWN, has 1 handler(s) for this service...
Service "cdb2" has 1 instance(s).
  Instance "cdb2", status UNKNOWN, has 1 handler(s) for this service...
Service "practice" has 2 instance(s).
  Instance "practice", status UNKNOWN, has 1 handler(s) for this service...
  Instance "practice", status READY, has 1 handler(s) for this service...
Service "practiceXDB" has 1 instance(s).
  Instance "practice", status READY, has 1 handler(s) for this service...
The command completed successfully

Try connecting with @SID which will invoke listener and connection should be OK this time.

PS C:\Users\parvi> sqlplus sys@practice as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 7 21:57:46 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

I hope this helps !!