4 How to fix ORA-12528: TNS:listener: all appropriate instances are blocking new connections

I get this error “ORA-12528: TNS:listener: all appropriate instances are blocking new connections” while testing the rman backup.

Root cause

The database is in the restricted mode. In my case, It is in the nomount

 select logins from v$instance;

 

We can check the listener status and it shows BLOCKED hence we can not connect it form remote.

Solution

Update the listener.ora on the AUX DB ( oradb2) then restart the listener

SID_LIST_LISTENER_ORACLEDB2 =

        (SID_LIST =

                (SID_DESC =

                        (SID_NAME = oradb2)

                )

        )

LISTENER_ORACLEDB2 =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = panda2.localdomain)(PORT = 7001))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC7001))

    )

  )

I found some other blog post for adding the (UR=A) in the tnsname.ora. In my testing, it does not seems to work on 11g. ( I am using non default port).

 

Reference

• http://francispaulraj.wordpress.com/ora-12528-tnslistener-all-appropriate-instances-are-blocking-new-connections/
• http://dbhk.wordpress.com/2010/06/11/ora-12528-tnslistener-all-appropriate-instances-are-blocking-new-connections/
• http://oracleabout.blogspot.com/2012/01/duplicate-database-from-active-database.html