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

ORA-12528

Tried to connect a NOMOUNT database, but it failed with ORA-12528.

C:\Users\ed>sqlplus sys/password@orcl as sysdba
...
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

ORA-12528 means that the database is at NOMOUNT state, there's no way to connect to a NOMOUNT database for normal users at client side. On the other side, there's a way that can allow SYS to connect to a NOMOUNT database externally.

In such moment, the connection to the service of database is BLOCKED in the listener, which means, normal connections will be rejected. For example:

SQL> conn hr/hr@orcl
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Let's check the service status of database in listener.

[oracle@test ~]$ lsnrctl status
...
Services Summary...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

Solution

For normal users who want to connect to the database, you have to open the database to able them to access. For connections by SYS, the solution is to add a special parameter UR=A in connect descriptor to lift off the restriction. More specifically, we added (UR=A) for a connect identifier ORCL.

[oracle@test ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
...
ORCL =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = standby01)(PORT = 1521))
        (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = ORCL)(UR=A)
        )
    )

Let's see how we connect the NOMOUNT database.

C:\Users\ed>sqlplus sys/password@ORCL as sysdba
...
Connected.

We solved it.

For connecting idle, nomount or restricted database externally, you need some skills to do it.