Connection to Auxiliary using connect string failed with ORA-12528 (文件 ID 419440.1)

In this Document

Symptoms

Cause

Solution

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Net Services - Version 10.1.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 22-JUL-2014***

Symptoms

Duplicate database fails while trying to connect to auxiliary instance.

Connection from target database server to the auxiliary database fails with error:

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

rman target / auxiliary sys/password@uat

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Mar 16 00:11:05 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: PROD (DBID=1451299937)
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

Note:

If "Duplicate from Active Database" available and used
Then you must connect using 'TNS alias' to both, Target and AUXiliary Instance
AUXiliary Instance should have a static Listener configured like:

$ rman target sys/password@Target auxiliary sys/password@AUXiliary

# To test, sqlplus (sysdba user), and rman must be able to connect to AUXiliary Instance, even if AUX instance is down/nomount
# For this recommendation is to create a 'Static Listener' entry for the AUX instance

Cause

The cause of the problem is that the Auxiliary Instance is started in NOMOUNT status, so it's not registered with listener.

PMON process registers the instances with listener when they are in MOUNT status, while instances are in NOMOUNT status appear in listener as BLOCKED.

Solution

There are 3 options to solve this problem

Option 1

Create a static entry for the auxiliary database in the listener.ora file of the auxiliary database Oracle_home and restart the listener. For example

Listener.ora
-------------

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = UAT)
      (ORACLE_HOME = G:\oracle\product\10.2.0\db_1)
      (SID_NAME = UAT)
    )
  )

Option 2 ( does not work for ACTIVE DUPLICATE )

Start rman from Auxiliary environment.

So you can connect to the auxiliary instance using OS authentication (as /) , then you need to use sqlnet connection for target database.

For example make below connection in RMAN from auxiliary database server

E:> rman target sys/sys@prod auxiliary /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Mar 15 23:57:08 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: PROD (DBID=1451288337)
connected to auxiliary database: UAT (not mounted)

Option 3 ( This does not apply/work for ACTIVE DUPLICATE )

Use the Oracle10G feature ( specify (UR=A) in connect data )
Make below entry in the tnsnames.ora file of the auxiliary database Oracle_home

tnsnames.ora
------------

UAT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = uat.company.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = UAT)(UR=A)
    )
  )

If issues with NET configuration, then a Request with NET Team should be opened .