Friday, February 8, 2013

5 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

5 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
  2. Excellent post. you have post an very useful and interesting information for our vision. and i learnt lots of new information from your blog post. keep update. Uptime Monitoring

    ReplyDelete
  3. Solve Oracle 12528 Error with Remote DBA Services
    This Oracle mistake 12528 happens just when in the event that you are endeavoring to interface with your Oracle database and because of some specialized issues it happens. One technique which takes care of this issue is to restart the LISTNER however in the event that this progression isn't alright then you can basically contact to Cognegic's Database Administration for Oracle or Online Oracle DB Support. We splendidly deal with the whole Oracle condition from a solitary dashboard. Here clients will get 24*7 specialized help to determine their issues.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete

 

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