Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Wednesday, February 20, 2013

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

Cause: All instances supporting the service requested by the client reported that they were blocking the new connections. This condition may be temporary, such as at instance startup.

Action: Attempt the connection again. If error persists, then contact the administrator to check the status of the instances.
Typically (in Oracle 9i and above), when you "shutdown" an Oracle database, that process "unregisters" the database with the LISTENER. Then when you "startup" the database, it "registers" with the LISTENER. 

If the "unregister" did not complete properly, then when you go to "startup" the next time, since the LISTENER did not unregister the instance, you receive the error, "ORA-12528: TNS:listener: all appropriate instances are blocking new connections" since an "appropriate" instance is already running.
Check the status of the listener by

# lsnrctl
LSNRCTL> status

One method that should resolve this problem is to restart (bounce) the LISTENER:

LSNRCTL> start

Then restart your instance: sqlplus with sysdba privileges

SQL> startup

Make sure all parameters are set.

Adding some information to the listener.ora file will let the listener know where to find the database even when it's down. Here's basically what can be added under the 
      (ADDRESS = (PROTOCOL = TCP)(HOST = or1)(PORT = 1521))

   (SID_LIST =
    (SID_DESC =
       (SID_NAME = STAN)
       (ORACLE_HOME =C:\app\Inam\product\11.2.0\dbhome_1)

Note: If you started your instance with nomount and try to connect using tns entry, you will get the same error.PMON process registers the instances with listener when they are in MOUNT status,  while instances are in NOMOUNT status appear in listener as BLOCKED. Create a static entry for the database in the listener.ora,adding the SID_NAME in SID_DESC in listener.ora will resolve the issue.

C:\Windows\system32>sqlplus sys/oracle123@stan as sysdba

SQL*Plus: Release Production on Wed Feb 20 13:13:42 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ORA-12528: TNS:listener: all appropriate instances are blocking new connections 
Use the Oracle10G feature ( specify (UR=A) in connect data )
Make below entry in the tnsnames.ora file of the database Oracle_home
    (ADDRESS = (PROTOCOL = TCP)(HOST = or1)(PORT = 1521))
        (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME= STAN) (UR=A)

No comments: