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.
Those steps are clear and really helped me a lot.
Thanks for your time to post .
Saludos.
I’m glad the solution is useful.