Friday, February 8, 2013

2 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

2 comments:

  1. erptree training institute is one of the best oracle fusion procurement training center in Hyderabad provide training on all oracle fusion modules with real time experts across the world like USA UK India Australia
    Oracle Fusion procurement Online Training
    Oracle Fusion cloud procurement Online Training

    ReplyDelete

 

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