Friday, February 8, 2013

1 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.
image

Root cause

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

 select logins from v$instance;

image

 

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

image

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))
    )
  )
 

image

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

1 comments:

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates