 |
|
ORA-12528: TNS : listener
tips
Oracle Error Tips by Burleson Consulting
|
The Oracle docs note this on the
ORA-12528 error:
-
SQL> oerr ora 12528
-
- 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.
The ORA-12528 is a new error which has begun
with Oracle 10g. Restarting the
Oracle instance can solve transient ORA-12528 errors.
Typically (in Oracle 9i and above), when you "shutdown" an Oracle
database, that process "unregisters" the database 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> 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%)
)
Furthermore, on
Oracle Forums, a user describes ORA-12528 as suggesting "that your
instance is either not open or opened in exclusive mode."
