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:Windowssystem32>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:oracleproduct10.2.0db_1networkadminlistener.ora

Listener Log File        C:oracleproduct10.2.0db_1networkloglistener.log

Listening Endpoints Summary…

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

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeEXTPROC0ipc)))

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.

Advertisements
Leave a comment

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

%d bloggers like this: