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
TNS:listener: all appropriate instances are in restricted mode
TNS:listener: all appropriate instances are blocking new connections
TNS:listener: all instances are in restricted mode or blocking
SQL*Plus: Release 184.108.40.206.0 Production on Tue Apr 28 15:54:21 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: sys as sysdba
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 ) ) )
[[email protected] ~]$ 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.
1,428 views last month, 2 views today