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

Here is the scenario of getting the error

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

C:\Windows\system32>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 – Production on Wed Oct 1 19:40:51 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

 

 SQL> connect sys@dc2soc03 as sysdba

Enter password:

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

When do you get this error?

You might get this error if you encounter with Connections via the listener to an instance that is in RESTRICTED status or in NO MOUNT status.    The lsnrctl services output will show that the service handler for this instance is in state: BLOCKED or RESTRICTED.

Here is an example:

LSNRCTL> status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xx.xx)(PORT=1521)))

STATUS of the LISTENER

——————————-

Alias                     LISTENER

Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.3.0 – Production

Start Date                01-OCT-2008 16:38:22

Uptime                    0 days 3 hr. 19 min. 45 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   C:\oracle\product\10.2.0\db_1\network\admin\listener.ora

Listener Log File         C:\oracle\product\10.2.0\db_1\network\log\listener.log

Listening Endpoints Summary…

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xx.xx)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))

Services Summary…

Service “PLSExtProc” has 1 instance(s).

  Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…

Service “dc2soc03.rnhub.com” has 1 instance(s).

  Instance “dc2soc03″, status BLOCKED, has 1 handler(s) for this service…

Service “dc2soc03_XPT.rnhub.com” has 1 instance(s).

  Instance “dc2soc03″, status BLOCKED, has 1 handler(s) for this service…

Service “ecentric.rnhub.com” has 1 instance(s).

  Instance “ecentric”, status READY, has 1 handler(s) for this service…

Service “ecentricXDB.rnhub.com” has 1 instance(s).

  Instance “ecentric”, status READY, has 1 handler(s) for this service…

Service “ecentric_XPT.rnhub.com” has 1 instance(s).

  Instance “ecentric”, status READY, has 1 handler(s) for this service…

The command completed successfully

 

How to overcome this?

 Add  (UR = A) in the TNSnames.ora

The (UR=A) clause for TNS connect strings has been created as an enhancement request.

example:

 

DC2SOC03 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xx.xx)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = DC2SOC03.rnhub.com)

            (UR = A)

    )

  )

 

Then try to connect and see what happens?

 

           SQL> connect sys@dc2soc03 as sysdba

           Enter password:

           Connected.

22 Responses to “ORA-12528: TNS:listener: all appropriate instances are blocking new connections”

  1. Arun Kauhik Says:

    Its worked … Thanks

  2. Harsono Says:

    I’m facing the same problem, and had tried to add (UR=A) in the tnsnames.ora. The error message is now changed to become “ORA-01033 : ORACLE initialization or shutdown in progress”.

    I’ve tried to restarted the Oracle Instance service several times and has completely Start, but why still the error said the database has not been mounted yet ?

    Please help and really appreciate it. Thanks in advance.

  3. sanchit gupta Says:

    thnx its workng

  4. Andy Coysh Says:

    I came across this issue a lot when cloning instances on Linux. I found that adding the instance to the listener.ora file and bouncing the listener resolved this for me.

  5. restroika Says:

    old thread but u are save my life and time!
    thank u very much!

  6. JC Says:

    thank you very much! it works! but can you explain what does (UR = A) mean?

  7. JAC Says:

    Hi,

    I got the same problem and added (UR = A), but now i am getting a new error “ORA-01033 : ORACLE initialization or shutdown in progress”. Please help me out.

  8. Prashant Tete Says:

    Hi,

    I got the same problem and added (UR = A), but now i am getting a new error “ORA-01033 : ORACLE initialization or shutdown in progress”. Please help me out.

  9. Prashant Tete Says:

    Hi,

    With the above solution I have connected but unable to connect to other users means I am unable to work in it

    Please help me out.

    Thanks & Regards,
    Prashant

  10. Anonymous Says:

    Thx! Who would have know… it works!!

  11. Anonymous Says:

    It worked..thanks!!!

  12. Mitch Says:

    I couldnt believe this worked after a frustrating day this did the trick

  13. Praveen Says:

    Brilliant……..solution worked after 2 days struggle.

    Thx a lot…..

  14. hariharan Says:

    Hi All,

    Iam facing the same issue after setting UR=A , does any one help me to solve this problem. please provide me a solution.

    Thanks,

    • hariharan Says:

      AFTER SETTING UR=A
      ERROR:
      ORA-01033: ORACLE initialization or shutdown in progress
      Process ID: 0
      Session ID: 0 Serial number: 0

  15. syam Says:

    Thanks for sharing this…it helped me …to connect to auxilary instance in nomount…

  16. A Rahim Khan Says:

    Thanks, it helps

  17. Rameshbabu Says:

    Worked nicely!! Thank you

  18. Rajeev Prakash Singh Says:

    very nice thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: