DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Saturday, July 15, 2017

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

 SQL> connect sys@dc2soc03 as sysdba
Enter password:
ERROR: ORA-12528: TNS:listener: all appropriate instances are blocking new connection

CAUSE:

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.


LSNRCTL> status

Services Summary…
Service “PLSExtProc” has 1 instance(s).
  Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “dc2soc03.rnhub.com” has 1 instance(s).
  Instance “dc2soc03”, status BLOCKED, has 1 handler(s) for this service…
Service “dc2soc03_XPT.rnhub.com” has 1 instance(s).
  Instance “dc2soc03”, status BLOCKED, has 1 handler(s) for this service…
Service “ecentric_XPT.rnhub.com” has 1 instance(s).
  Instance “ecentric”, status READY, has 1 handler(s) for this service…
The command completed successfully


SOLUTION:

Add  (UR = A) in the TNSnames.ora
The (UR=A) clause for TNS connect strings has been created as an enhancement request.

example:

DC2SOC03 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xx.xx)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DC2SOC03.rnhub.com)
            (UR = A)
    )
  )