ORA-12528: TNS:listener: all appropriate instances are blocking new connection
In this case, we are getting the following error when our database is start in mount / nomount / restricted state. Sometime we faced this while configuring the dataguard Environment.
Error:
C:\Windows\system32>sqlplus /nolog
SQL*Plus: Release 10.2.0.3.0 – Production on Wed Oct 1 19:40:51 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SQL> connect sys@dbname as sysdba
Enter password:
ERROR: ORA-12528: TNS:listener: all appropriate instances are blocking new connection
You might get this error if you encounter with Connections via the listener to an instance that is in RESTRICTED status or in NO MOUNT status. The lsnrctl services output will show that the service handler for this instance is in state: BLOCKED or RESTRICTED.
Check the status of services by listener commands:
lsnrctl status
lsnrctl services
As example show below the status is blocked for ORCL service:
lsnrctl status STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for windows: Version 11.2.0.1.0 - Production Start Date 20-DEC-2014 02:39:22 Uptime 14 days 2 hr. 26 min. 18 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File D:\oracle\12.1.0\dbhome_1\network\admin\listener.ora Listener Log File D:\oracle\diag\tnslsnr\test\listener\alert\log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1521))) Services Summary... Service "ORCL" has 1 instance(s). Instance "ORCL", status BLOCKED, has 1 handler(s) for this service...
Solution
For Standalone database
1. Restart the Oracle services.(first step will fixed issue)
SQLPLUS / as sysdba
-- Shutdown the DB Server
Shutdown immediate
-- Start the DB server
Startup
2. Check the listener is working on Dynamic registration for the Service. If you are again and again getting this error. You can need to make manual entry of SID in listener.ora file or add with help of NETCA utility.
Entry as shown in bold: (SID_DESC= (GLOBAL_DBNAME=ORCL ….
Listener.ora file:
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = D:\oracle\12.1.0\dbhome_1) (PROGRAM = extproc) (ENVS = "EXTPROC_DLLS=ONLY:D:\oracle\12.1.0\dbhome_1\bin\oraclr12.dll") ) (SID_DESC = (GLOBAL_DBNAME = ORCL) (ORACLE_HOME = D:\oracle\12.1.0\dbhome_1) (SID_NAME = ORCL) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521)) )
If its a Dataguard Environment or you used Auxiliary channel for RMAN
In Some cases we need to overcome with this problem like dataguard configuration. In which our standby database is in recover mode means it’s in mounted state for apply the redo logs of primary database. In that case listener status for service is Blocked or RESTRICTED. To overcome from it we used the following parameter in tnsnames.ora file:
(UR=A) clause is used for TNS connect strings has been created as an enhancement.*(UR=A)* clause is intended to work with a dynamically registered handler so the use of SERVICE_NAME versus SID is preferred. (ID 362656.1)
Need to modify the tnsnames.ora file for connectivity as shown below:
DBNAME =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xx.xx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = hostname.rnhub.com)
(UR = A)
)
)