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.

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
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:
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = or1)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
   (SID_LIST =
    (SID_DESC =
       (SID_NAME = STAN)
       (ORACLE_HOME =C:\app\Inam\product\11.2.0\dbhome_1)
   )

   )

Note: If you started your instance with nomount and try to connect using tns entry, you will get the same error.PMON process registers the instances with listener when they are in MOUNT status,  while instances are in NOMOUNT status appear in listener as BLOCKED. Create a static entry for the database in the listener.ora,adding the SID_NAME in SID_DESC in listener.ora will resolve the issue.

C:\Windows\system32>sqlplus sys/oracle123@stan as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 20 13:13:42 2013

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

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

OR

Use the Oracle10G feature ( specify (UR=A) in connect data )
Make below entry in the tnsnames.ora file of the database Oracle_home 

STAN=
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = or1)(PORT = 1521))
        (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME= STAN) (UR=A)
        )
)