ORA-12528 tns listener all appropriate instances are blocking new connections

SQL> set ORACLE_SID=ORCL
SQL> startup nomount
ORACLE instance started.

Total System Global Area  263639040 bytes
Fixed Size                  1332552 bytes
Variable Size             222300856 bytes
Database Buffers           33554432 bytes
Redo Buffers                6451200 bytes

C:\>lsnrctl services

LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production 

Service "ORCL" has 2 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:59
         LOCAL SERVER
  Instance "ORCL", status BLOCKED, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "ORCL_XPT" has 1 instance(s).
  Instance "ORCL", status BLOCKED, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER

Want Solution Do the following : 

you can add the new TNS connect string (UR = A) to the tnsnames entry.

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
      (UR = A)
    )
  )

Leave a comment