How to Setup Data Guard Physical Standby in Oracle Database 18c

This post applies to 12c and 19c as well, it introduced two popular ways for creating a standby database: RMAN DUPLICATE and from primary database backup

Introduction

This post demonstrates how to build an Oracle 18c physical standby database managed by data guard.  The method should also apply to 12c and 19c as well.

Subscribe to get access

Read more of this content when you subscribe today.

DUPLICATE FROM ACTIVE DATABASE Fails With RMAN-03009 ORA-17628 ORA-19505

While creating a new physical standby database by using RMAN duplicate command, the following errors occur:

RMAN> run {
allocate channel prim1 type disk;
allocate channel prim2 type disk;
allocate auxiliary channel standby1 type disk;
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
...
..
.
RMAN-03009: failure of backup command on prim2 channel 
ORA-17628: Oracle error 19505 returned by remote Oracle server
ORA-19505: failed to identify file ""

SOLUTION

  1. Check and remove the old standby database data files.
  2. Make sure directories for standby database data files created and accessible.

Create Physical Standby Database from Active Database in 12c

Subscribe to get access

Read more of this content when you subscribe today.

ORA-17628 RMAN-03009 duplicate target database for standby from active database

While run RMAN “duplicate target database for standby from active database nofilenamecheck” in 11g database, then got below errors:

...
..
.
set newname for datafile 3 to "/u02/oradata/TESTDBSTY/undotbs01.dbf";
...
..
.
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u02/oradata/TESTDB/undotbs01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 01/15/2019 15:53:17
ORA-17628: Oracle error 19505 returned by remote Oracle server
continuing other job steps, job failed will not be re-run
...
..
.

Subscribe to get access

Read more of this content when you subscribe today.

“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;
}