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.
Those steps are clear and really helped me a lot.
Thanks for your time to post .
Saludos.
I’m glad the solution is useful.
Hi. I’ve no problem connecting to the database in sql plus. Connecting through sql developer is where I get the damn error. I’ve tried stopind and starting the listener and then shut down and start up the database in sql plus. I’ve tried reinstalling the database itself. I even reset the whole damn windows OS but nothing seems to unblock the xe instance.
Different tools may use different tnsnames.ora file, be aware of that!
Hi brother. I am facing similar issue now. I am not able to connect through sql developer but able to connect in sqlplus. Can you help me in clearing it ?
In SQL developer, please change your “Connection Type” from “Basic” into “TNS”, then select a net service name for your connection properties.
Thanks you very much. For my case , because of window reset, I install new database home again, but different oracle home user name and different oradata path. Old path is …/oradata/orcl1, now is under …/oradata/orcl2, Add (UR=A) in tnsname and change old service_name . Then sqlplus sys/password@ORCL as sysdba , it was connected. And alter database mount; alter database open; (need to give permission to new ora home user for controlfile, datafile and onlinelog files ) .Now I can use old path under orcl1,
Good for you!