oracledba.help
Legacy

Data Guard via Standard Files (RMAN Duplicate)

<- Legacy

Overview

The following covers the creation of Oracle 12c Data Guard environment with the below specs. 

 Hostname  Role     DB_NAME  DB_UNIQUE_NAME  ORACLE_SID (Env Var)
 --------  -------  -------  ---------------  --------------------
 lnx01     Primary  oradb    oradb            oradb    
 lnx02     Standby  oradb    oradb_sb         oradb_sb

 db_create_file_dest        = /u01/oradata
 db_recovery_file_dest      = /u02/oradata
 db_recovery_file_dest_size = 15G

 Primary DB created with 4 Redo Log Groups.

SB = Standby

TOC

Prerequisites

  • You have two systems with the same OS (ex: Oracle RHEL 7.4).
  • The OS's have been prepped for Oracle. ⇒
  • Both systems have Oracle 12c database product installed. ⇒?
  • The primary system has:
    • A fully operational Oracle 12c database. ⇒
    • REMOTE_LOGIN_PASSWORDFILE set to EXCLUSIVE.
      sho parameter REMOTE_LOGIN_PASSWORDFILE
    • LOG_MODE set to ARCHIVELOG.
      SELECT log_mode FROM v$database;
  • Get control file names and paths on Primary. 
col NAME_COL_PLUS_SHOW_PARAM  format A15
col TYPE                      format A6
col VALUE_COL_PLUS_SHOW_PARAM format A150
show parameter control_files
These values will be used in the RMAN script that creates SB.

Primary Database Changes

Create Standby Redo Log (SRL) Files

  • The SIZE must be the same as your redo log files.
  • The standby-redo-logs must have at least one more than the redo logs on the primary database. So if your Primary has 4 create 5. 
ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 5 SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 6 SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 7 SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 8 SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 9 SIZE 200M;

Verify SRL Files Created
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

Set Parameters

ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SET log_archive_config='DG_CONFIG=(oradb,oradb_sb)' SCOPE=both;
ALTER SYSTEM SET log_archive_max_processes=10                    SCOPE=both;
ALTER SYSTEM SET standby_file_management='AUTO'                  SCOPE=both;

ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u01/oradata/ORADB/onlinelog/ 
      VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradb' SCOPE=both;
ALTER SYSTEM SET log_archive_dest_2='SERVICE=oradb_sb ASYNC  
      VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradb_sb' SCOPE=both;
ALTER SYSTEM SET log_archive_dest_state_1=ENABLE SCOPE=both;

ALTER SYSTEM SET db_file_name_convert='/ORADB_SB/','/ORADB/'  SCOPE=spfile;
ALTER SYSTEM SET log_file_name_convert='/ORADB_SB/','/ORADB/' SCOPE=spfile;

ALTER SYSTEM SET fal_server='oradb_sb' SCOPE=both;
ALTER SYSTEM SET fal_client='oradb'    SCOPE=both;

Most settings here are duplicated on the SB via RMAN DUPLICATE operation later.

Bounce Primary

Some changes above do not take effect until after a database bounce. 

shutdown immediate
startup

Network Changes

tnsnames.ora

Add the same entries on both systems.
gedit $ORACLE_HOME/network/admin/tnsnames.ora 

LISTENER_ORADB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = lnx01)(PORT = 1521))

ORADB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lnx01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradb)
    )
  )

ORADB_SB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lnx02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradb_sb)
    )
  )

Comments:

  • Change the LISTENER_ entry accordingly on the SB. 
LISTENER_ORADB_SB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = lnx02)(PORT = 1521))
  • SERVER = DEDICATED is required for many DGMGRL communications.

Configure Listener

Insert static entries in the listener.ora ensuring the DGMGRL can start\stop databases.
Examples: Primary | Standby. 

 a. Stop Listener: lsnrctl stop
 b. Make changes appropriate to system (Primary\Standby).
    gedit $ORACLE_HOME/network/admin/listener.ora
 c. Start Listener: lsnrctl start

The listener on the SB does not exist yet so does not need to be stopped. Just started after configured.

Standby Database Changes

Create required directories on the Standby.

Example here.

Create a password file.

Make the password the same as the SYS password on the Primary. 
orapwd file=/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/orapworadb_sb 
   password=go entries=10 force=y
If newly upgraded to 12.2 include: format=12 
orapwd file=/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/orapworadb_sb 
   password=go entries=10 force=y format=12

Create a parameter file and start database.

gedit $ORACLE_HOME/dbs/init_tmp.ora 
DB_NAME='oradb'
DB_UNIQUE_NAME='oradb_sb'
DB_BLOCK_SIZE=8192

sqlplus / as sysdba
SQL> STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/init_tmp.ora';

Test Connectivity 

sqlplus sys/go@oradb as sysdba
SQL> SELECT instance_name FROM v$instance;

sqlplus sys/go@oradb_sb as sysdba
SQL> SELECT instance_name FROM v$instance;

Perform RMAN Duplicate database operation.

rman target sys/go@oradb auxiliary sys/go@oradb_sb 
connected to target database: ORADB (DBID=2703748640)
connected to auxiliary database: ORADB (not mounted)
Run corresponding RMAN commands.
  • Once the RMAN restore has completed the primary database and standby database will exist.
  • Successful RMAN operation shows similar message to below: 
     Finished Duplicate Db at 23-AUG-17
 released channel: p1
 released channel: p2
 released channel: a1

Start Recovery 

 alter system set log_archive_dest_2='' scope=both; # Required for 12.x
 alter database recover managed standby database disconnect from session;

Enable Broker

Ensure FAL Entries Set

show parameter fal

If Needed: 

 -- On Primary
 alter system set fal_server='oradb_sb' scope=both;
 alter system set fal_client='oradb'    scope=both;

 -- On SB
 alter system set fal_server='oradb'    scope=both;
 alter system set fal_client='oradb_sb' scope=both;

Note the single quotes used. This is to ensure same case used.

Broker Configuration

 -- On Both
 SQLPlus> ALTER SYSTEM SET dg_broker_start=true sid='*' scope=both;

 -- On Primary
 dgmgrl sys/go@oradb

 DGMGRL> CREATE CONFIGURATION dg_cfg1 AS PRIMARY DATABASE IS oradb CONNECT IDENTIFIER IS oradb;
    Configuration "dg_cfg1" created with primary database "oradb"
 DGMGRL> ADD DATABASE oradb_sb AS CONNECT IDENTIFIER IS oradb_sb MAINTAINED AS PHYSICAL;
    Database "oradb_sb" added
 DGMGRL> ENABLE CONFIGURATION;
    Enabled.

Check Status?
If show configuration; displays any ORA-16nnn errors they should resolve automatically in a few minutes. If not, on the SB do a shutdown immediate then startup mount;

Ensure Logs Being Transferred

 -- On Both
 SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG 
 WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG) 
 GROUP BY THREAD#;

 -- On Primary
 ALTER SYSTEM SWITCH LOGFILE; 
 ALTER SYSTEM SWITCH LOGFILE;

 -- On Both
 SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG 
 WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG) 
 GROUP BY THREAD#;

Enable Flashback Database

 -- On Primary 
 sqlplus / as sysdba
 ALTER DATABASE FLASHBACK ON;

 -- On SB
 dgmgrl sys/go@oradb_sb
 DGMGRL> edit database oradb_sb set state=apply-off;
 DGMGRL> sql "alter database flashback on";
 DGMGRL> edit database oradb_sb set state=apply-on;

 -- On Both (QC check)
 SQLPlus> SELECT flashback_on FROM v$database;

The standby redo apply services must be stopped on the Standby before flashback database can be enabled.

If Flashback Database is not enabled, you will have to manually recreate your primary as a standby in a failover event to reinstate it!

APPENDIX

Create Directories

As root: 

mkdir -p /u01/app/oracle/admin/oradb/adump
chown -R oracle:oinstall /u01/app/oracle/admin/oradb/adump
chmod -R 775 /u01/app/oracle/admin/oradb/adump

mkdir -p /u01/oradata/ORADB/controlfile
mkdir -p /u01/oradata/ORADB/datafile
mkdir -p /u01/oradata/ORADB/onlinelog

mkdir -p /u01/oradata/ORADB_SB/controlfile
mkdir -p /u01/oradata/ORADB_SB/datafile
mkdir -p /u01/oradata/ORADB_SB/onlinelog

mkdir -p /u02/oradata/ORADB/archivelog
mkdir -p /u02/oradata/ORADB/autobackup
mkdir -p /u02/oradata/ORADB/controlfile
mkdir -p /u02/oradata/ORADB/onlinelog

mkdir -p /u02/oradata/ORADB_SB/archivelog
mkdir -p /u02/oradata/ORADB_SB/autobackup
mkdir -p /u02/oradata/ORADB_SB/controlfile
mkdir -p /u02/oradata/ORADB_SB/onlinelog

chown -R oracle:oinstall /u01/oradata
chown -R oracle:oinstall /u02/oradata
chmod -R 775 /u01/oradata
chmod -R 775 /u02/oradata

RMAN Duplicate

Make sure to enter the control file names from the Primary.
rman target sys/go@oradb auxiliary sys/go@oradb_sb 

run 
{ 
   allocate channel p1 type disk;
   allocate channel p2 type disk;
   allocate channel p3 type disk;
   allocate channel p4 type disk;
   allocate auxiliary channel a1 type disk; 
   duplicate target database for standby from active database 
   spfile 
      parameter_value_convert 'ORADB','ORADB_SB' 
      set db_unique_name='oradb_sb' 
      set control_files='/u01/oradata/ORADB/controlfile/o1_mf_dw2pz6gy_.ctl',
                        '/u02/oradata/ORADB/controlfile/o1_mf_dw2pz6hp_.ctl'   
      set log_archive_dest_1='LOCATION=/u01/oradata/ORADB_SB/onlinelog/ 
          VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradb_sb'
      set log_archive_config='dg_config=(oradb,oradb_sb)' 
      set fal_server='oradb'
      set fal_client='oradb_sb'
      set standby_file_management='AUTO' 
   nofilenamecheck;
}

SRL Legacy Files Example

 ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oradata/oradb/rdo/sb_redo01.rdo') SIZE 200M;
 ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oradata/oradb/rdo/sb_redo02.rdo') SIZE 200M;
 ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oradata/oradb/rdo/sb_redo03.rdo') SIZE 200M;
 ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oradata/oradb/rdo/sb_redo04.rdo') SIZE 200M;
 ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oradata/oradb/rdo/sb_redo05.rdo') SIZE 200M;

Listener Examples

Notice both systems have both entries for DGMGRL. This is so the corresponding database can be restarted.

Primary

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnx01)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
	  (SID_DESC =
	    (SID_NAME = oradb)
	    (GLOBAL_DBNAME = oradb)
	    (ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1)
	  )
	  (SID_DESC=
	    (SID_NAME=oradb)
	    (GLOBAL_DBNAME=oradb_DGMGRL)
	    (ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1)
	  )
	  (SID_DESC=
	    (SID_NAME=oradb_sb)
	    (GLOBAL_DBNAME=oradb_sb_DGMGRL)
	    (ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1)
	  )
  )

ADR_BASE_LISTENER = /u01/app/oracle

Standby

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnx02)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
	  (SID_DESC =
	    (SID_NAME = oradb_sb)
	    (GLOBAL_DBNAME = oradb_sb)
	    (ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1)
	  )
	  (SID_DESC=
	    (SID_NAME=oradb)
	    (GLOBAL_DBNAME=oradb_DGMGRL)
	    (ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1)
	  )
	  (SID_DESC=
	    (SID_NAME=oradb_sb)
	    (GLOBAL_DBNAME=oradb_sb_DGMGRL)
	    (ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1)
	  )
  )

ADR_BASE_LISTENER = /u01/app/oracle

If needed: alter system set local_listener='' scope=both;

Common Errors

Issue

BAD PARAM for DESTINATION path. 

 -- Below Shows BAD PARAM
 SELECT destination, status, archived_thread#, archived_seq# 
 FROM v$archive_dest_status
 WHERE status <> 'DEFERRED' AND status <> 'INACTIVE';

Source

In most cases a bad value was used for LOG_ARCHIVE_DEST_n in the RMAN Duplicate operation.

Fix

Set corresponding LOG_ARCHIVE_DEST to correct value. 

 -- Get Primary Value
 show parameter LOG_ARCHIVE_DEST_1
 LOCATION=/u01/oradata/ORADB/onlinelog/ 
 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradb 

 -- Set Corresponding SB Value
 ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/oradata/ORADB_SB/onlinelog/ 
 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradb_sb' scope=both;

Warning: ORA-16714: the value of property xxx is inconsistent with the member setting

  1. On Primary get the current correct value.
    SQLPlus> show parameter <param_name>
  2. Set the value(s) on the Standby

Common Changes Example 

 alter system set log_archive_max_processes=4    scope=both sid='*';
 alter system set archive_lag_target=0           scope=both sid='*';
 alter system set log_archive_min_succeed_dest=1 scope=both sid='*';
 alter system set data_guard_sync_latency=0      scope=both sid='*'; 

 show parameter log_archive_max_processes;
 show parameter archive_lag_target;
 show parameter log_archive_min_succeed_dest;
 show parameter data_guard_sync_latency;