Encountering ORA-12528: all appropriate instances are blocking new connections

book

Article ID: 159902

calendar_today

Updated On:

Products

Data Loss Prevention Enforce

Issue/Introduction

When attempting to log into SQLPlus from the commandline as a user other than sys, the following error appears:
ORA-12528 TNS:listener: all appropriate instances are blocking new 	  connections 

Resolution

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.

1) attempt to connect again as this may be a temporary situation.

Else

2) log in as sys as sysdba and confirm the status of the database using the following script:

select * from v$instance;

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 protect
L-018591-A
10.2.0.4.0        07-DEC-09 OPEN         NO           1 STOPPED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO

Note that the status says, "OPEN".  If the database isn't open, then this is the cause of the error.