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

Here is the scenario of getting the error

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

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@dc2soc03 as sysdba

Enter password:

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

When do you get this error?

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.

Here is an example:

LSNRCTL> status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xx.xx)(PORT=1521)))

STATUS of the LISTENER

——————————-

Alias                     LISTENER

Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.3.0 – Production

Start Date                01-OCT-2008 16:38:22

Uptime                    0 days 3 hr. 19 min. 45 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   C:\oracle\product\10.2.0\db_1\network\admin\listener.ora

Listener Log File         C:\oracle\product\10.2.0\db_1\network\log\listener.log

Listening Endpoints Summary…

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xx.xx)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))

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.rnhub.com” has 1 instance(s).

  Instance “ecentric”, status READY, has 1 handler(s) for this service…

Service “ecentricXDB.rnhub.com” has 1 instance(s).

  Instance “ecentric”, status READY, 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

 

How to overcome this?

 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)

    )

  )

 

Then try to connect and see what happens?

 

           SQL> connect sys@dc2soc03 as sysdba

           Enter password:

           Connected.