“Duplicate from Active Database” Failed With ORA-12528 When Connecting to Auxiliary Instance

SYMPTOM

RMAN “duplicate target database for standby from active database” failed with below errors:

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

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

CAUSE

Since Auxiliary instance is started in NOMOUNT , so the instance is not registered with the listener. PMON register the instance when instance needs to be in MOUNT/OPEN status. When instance is in NOMOUNT status, it appears in listener as BLOCKED. “duplicate from active database” need shutdown and startup instance several times, so the connection might succeed in the beginning, but still fails later.

SOLUTION

1) For Non-RAC auxiliary instance, create a static entry for auxiliary instance in the current listener.ora, and restart or reload the listener:

-- listener.ora
--
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
      (SID_NAME = ORCL)
    )
  )

2) For 10g/11g RAC auxiliary instance,  previous step 1 still works, if only VIP addresses are used in cluster/GI listener.

--listener.ora
--
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = ORCL1)
    )
  )

3) For 12c RAC auxiliary instance, a  new temp listener is required for RAC auxiliary instance, because from 12c on, the SCAN are used for listener, which makes previous step not working for RAC auxiliary instance.

-- Temp listener.ora in RAC ORACLE_HOME/network/admin

LISTENER_STANDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = tcp)(HOST=racnode1-vip)(PORT=1522))
    )
  )
SID_LIST_LISTENER_STANDBY =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
      (SID_NAME = ORCL1)
    )
  )

3) Create appropriate TNS alias for auxiliary instance.

-- Non-RAC Auxiliary Instance
--
STDBYDB =
   (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = host_name)(PORT = 1521))
   (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ORCL)
   (INSTANCE_NAME = ORCL))
 )

-- RAC Auxiliary Instance
--
STDBYDB =
   (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip)(PORT = 1522))
   (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ORCL)
   (INSTANCE_NAME = ORCL1))
 )

4) Connect to RMAN , and run “duplicate target database for standby from active database;” successfully:

$ rman target sys@PRIDB auxiliary sys@STDBYDB
RMAN > run { 
allocate channel prim type disk;
allocate auxiliary channel aux type disk;
duplicate target database for standby from active database;
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.