ORA-12527: TNS:listener: all instances are in restricted mode or blocking new connections

Mehmet Salih Deveci

6 years ago

Hi,

Sometimes You can get “ORA-12527: TNS:listener: all instances are in restricted mode or blocking new connections ” error.

Details of error are as follows.

ORA-12527: TNS:listener: all instances are in restricted mode or blocking new connections

Cause: All appropriate database instances supporting the service requested by the client reported that

they either were in restricted mode or were blocking the new connections. The Listener does not allow

connections to such instances. This condition may be temporary, such as at instance startup.

Action: Attempt the connection again. If error persists, then contact the database administrator to

check the status of the instances.

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/