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. Which means, 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, you have to open the database to solve the problem. For SYS, the solution is to add a special parameter UR=A in connect descriptor to lift 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.