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

Mehmet Salih Deveci

3 years ago

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

Cause: Database instances supporting the service requested by the client were in restricted mode.

The Listener does not allow connections to instances in restricted mode. This condition may be temporary,

such as during periods when database administration is performed.

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

the mode of the instance, if appropriate.

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

SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 28 15:54:21 2018

Enter user-name: sys as sysdba
Enter password:
ERROR:
ORA-12526: TNS:listener: all appropriate instances
are in restricted mode

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/

1,427 views last month, 1 views today