ORA-12528: TNS : listener
Oracle Error Tips by Burleson Consulting
The Oracle docs note this on the
SQL> oerr ora 12528
- ORA-12528: TNS: listener: all
appropriate instances are blocking new connections
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.
Attempt the connection again. If error persists, then
contact the administrator to check the status of the
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
One method that should resolve this problem is to restart (bounce) the
Then restart your instance: sqlplus with sysdba privileges
Make sure all parameters are set.
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:
(ORACLE_HOME = %Oracle_Home%)
(SID_NAME = %SID%)
Oracle Forums, a user describes ORA-12528 as suggesting "that your
instance is either not open or opened in exclusive mode."