ora-12528:TNS:Listener, My server has this Options

[mutale]

Hi every one:
I have a problem to share. I have tried all. first i need a recovery and all seemed to work but after a while this error came up. What happens is that the database runs for a while and then starts this:
ora-12528:TNS:Listener:All Appropriate instances are blocking new connections

I think someone may help.

Mutale.

[burleson]

Hi Mutale,

Wow, this is a cutting-edge error, and the ora-12528 is brand-new with 10g.

The docs note:

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

Cause: All instances supporting the service requested by the client reported that they were blocking the new connections. This condition may be temporary, such as at instance startup.

Action: Attempt the connection again. If error persists, then contact the administrator to check the status of the instances.

See here: http://www.dba-oracle.com/sf_ora_12528_tns...on_tips_bc1.htm

Brian Peasland notes: "This is a known problem with Oracle 10g where a SHUTDOWN IMMEDIATE is followed by a STARTUP MOUNT."

Try bouncing the instance and try again . . .

[Gurpreet]

I find same error, RESTART the server solves my problem.

Gurpreet S. Gill

[mutale]

thanks all for prompt reply.

I found something, just incase someone encounters this:

I dont remember the url reference but here it is:

Typically (in Oracle 9i and above), when you "shutdown" an Oracle database, that process "unregisters" the databse with the LISTENER. Then when you "startup" the database, it
"registers" with the LISTENER.
If the "unregister" did not complete properly, then when you go to "startup" the next time, since the LISTENER did not unregister the instance, you receive the error, "ORA-12528: TNS:listener: all appropriate instances are blocking new connections" since an "appropriate" instance is already running.

Check the status of the listener by

# lsnrctl
LSNRCTL> status

One method that should resolve this problem is to restart (bounce) the LISTENER:

LSNRCTL> stop
LSNRCTL> start

Then restart your instance: sqlplus with sysdba privileges

SQL> startup

Make sure all parameters are set.
Example

Adding some information to the listener.ora file will let the listener know where to find the database even when it's down. Here's basically what can be added under the SID_LIST_LISTENER:

(SID_DESC =
(GLOBAL_DBNAME =%DBNAME%)
(ORACLE_HOME = %Oracle_Home%)
(SID_NAME = %SID%)
)


Hope it helps someone