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

Cause:

[oracle@hostname ~]$ oerr ora 12528

12528, 00000, "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.

Usually when an oracle database shutdown, it unregister the database with Listener and when an oracle database startup it register the database with Listener. If the database fail to unregister properly e.g server restarted abruptly for overheat then when the database startup next time it show "ORA-12528: TNS:listener: all appropriate instances are blocking new connections" because an "appropriate" instance is already running.

Solution:

Check the status of the listener by

[oracle@hostname ~]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 24-APR-2017 15:33:49

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Welcome to LSNRCTL, type "help" for information.

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