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.

In this case, we are getting the following error when our database is start in mount / nomount / restricted state. Sometime we faced this while configuring the dataguard Environment.

lsnrctl status
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for windows: Version 11.2.0.1.0 - Production
Start Date                20-DEC-2014 02:39:22
Uptime                    14 days 2 hr. 26 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File D:\oracle\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File D:\oracle\diag\tnslsnr\test\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1521)))
Services Summary...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status BLOCKED, has 1 handler(s) for this service...

Solution

For Standalone database
1. Restart the Oracle services.(first step will fixed issue)

SQLPLUS / as sysdba
-- Shutdown the DB Server
Shutdown immediate
-- Start the DB server
Startup


2. Check the listener is working on Dynamic registration for the Service. If you are again and again getting this error. You can need to make manual entry of SID in listener.ora file or add with help of NETCA utility.
Entry as shown in bold: (SID_DESC= (GLOBAL_DBNAME=ORCL ….

Listener.ora file:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\oracle\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\oracle\12.1.0\dbhome_1\bin\oraclr12.dll")
    )
 (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = D:\oracle\12.1.0\dbhome_1)
      (SID_NAME = ORCL)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
    )

Need to modify the tnsnames.ora file for connectivity as shown below:

DBNAME =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xx.xx)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = hostname.rnhub.com)
            (UR = A)
    )
  )