ORA-12528: TNS:listener: all appropriate instances are blocking new connections

dbtut

6 years ago

When trying to connect to an open intance via the listener, you may receive the error “ORA-12528: TNS: listener: all appropriate instances are blocking new connections”. The current instance is in RESTRICTED or NOMOUNT mode. In this case, you need to add the line (UR = A) to the corresponding tns record in the tnsnames.ora file.

When connected with Rman, an error will be received as follows.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections

The same error is also seen when connected with Sqlplus.

ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

In this case, the corresponding TNS record in the tnsnames.ora file under the corresponding $ ORACLE_HOME/network/admin directory should be similar to the following.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcl-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
      (UR = A)
    )
  )

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = orcl-scan)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORCL)

(UR = A)

)

Then, you can connect successfully.