TNS-12526: TNS:listener: all appropriate instances are in restricted mode

Hi,

Sometimes You can get “TNS-12526: TNS:listener: all appropriate instances are in restricted mode ” error.

Details of error are as follows.

TNS-12526: TNS:listener: all appropriate instances are in restricted mode

ORA-12526 (TNS-12526)
TNS:listener: all appropriate instances are in restricted mode

ORA-12528 (TNS-12528)
TNS:listener: all appropriate instances are blocking new connections

ORA-12527 (TNS-12527)
TNS:listener: all instances are in restricted mode or blocking

This error is related with database in restricted mode.

To solve this error, you can create a static listener and start it as follows.

Add the following listener descriptions to the listener.ora file under $ORACLE_HOME/network/admin.

Check your Hostname, Port, DB Name and fill them correctly.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.63.34)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

SID_LIST_LISTENER = 
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = MSDB)
(ORACLE_HOME = /u01/app/db_home_18c )
(SID_NAME = MSDB1 )
)
)

[oracle@msdbadm01 ~]$ lsnrctl start LISTENER

The (UR=A) clause for TNS connect strings was created in response to an enhancement request.  This clause can be inserted into the “(CONNECT_DATA=” section of a TNS connect string and allow a privileged or administrative user to connect via the listener even when the service handler is blocking connections for non-privileged users.

Here’s an example of a connect string configured with (UR=A):

PROD =
   (DESCRIPTION =
     (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = 1521)))
      (CONNECT_DATA =
          (UR=A) <===Add this parameter
          (SERVICE_NAME = <service.domain>)
       )
     )

Please note that the (UR=A) clause is intended to work with a dynamically registered handler so the use of SERVICE_NAME versus SID is required when using dynamic registration (i.e. handler exists in lsnrctl output but is BLOCKED).  The use of SID in a TNS connect string may allow a connection if using a static handler in the listener.ora file under SID_DESC.

Do you want to learn more details about RMAN, then read the following articles.

https://ittutorial.org/rman-backup-restore-and-recovery-tutorials-for-beginners-in-the-oracle-database/