Data Guard Physical Standby setup in 12c

Setting up a Physical Standby based Data Guard configuration using Oracle Database 12c.

In this post I will just explore the setting up of a simple data guard configuration consisting of a 12c primary database being protected by a physical standby. The configuration details are shown below.

Primary Host	Laboms.example.com
Standby Host	Labomsb.example.com
Primary Db	Oracle 12c with a single pluggable database PDB1.
Standby Db	Clone of primary being maintained by managed recovery.
Os	Oracle Enterprise Linux 64 bit.

 

1. Configure a fast recovery area, if not configured.

For example run these commands as sys:

 Alter system set db_recovery_file_dest=‘/u01/app/oracle/oradata/fra’ scope=spfile;

 Alter system set db_recovery_file_dest_size=10g scope=spfile ;

2. Enable forced logging. Run this sql as sysdba.

Alter database force logging;

3. Enable archiving. (Run these commands as sys on the primary from SqlPlus)

shutdown immediate;

startup mount;

Alter database archivelog;

4. Configure the init.ora parameters listed below.  

Run ALTER SYSTEM set ... commands for the listed parmeters from SqlPlus as sysdba.

ALTER SYSTEM set <PARAM>=<VALUE> scope=spfile;

(Replace param> and <value> with actual values.)

Do only the primary for now.

Parameter Name	Primary Value	Standby Value
Db_name	CDB1	CDB1
Compatible	12.1.0.2.0	Same as primary
Enable_pluggable_database	Already set in primary (true)	Same as primary
Db_unique_name	CDB1	CDB1_DR
Log_archive_config	DG_CONFIG=(CDB1,CDB1_DR)	Same as primary
Fal_server	CDB1_DR	CDB1
Db_file_name_convert	'cdb1_dr','cdb1'	'cdb1','cdb1_dr'
Log_file_name_convert	'cdb1_dr','cdb1'	'cdb1','cdb1_dr'
Db_recovery_file_dest	/u01/app/oracle/oradata/fra	Same as primary
Db_recovery_file_dest_size	10g	Same as primary
Log_archive_dest_1	Location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles)db_unique_name=CDB1	Location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=CDB1_DR
Log_archive_dest_2	Service=CDB1_DR VALID_FOR=(online_logfiles,primary_role db_unique_name=CDB1_DR	Service=CDB1 VALID_FOR=(online_logfiles,primary_role db_unique_name=CDB1

5. Configure the local naming aliases and listeners on both site.

It should be possible to connect to them in the nomount mode. This is required for duplication of the database to the standby site.

Site	Alias	Tnsnames entry
Both sites	CDB1	(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = laboms.example.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = CDB1)))
Both sites	CDB1_DR	(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = labomsb.example.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = CDB1_DR)))

6. On both sites, the listeners listened on port 1521.

Primary Listener Database Service static registration is shown in screenshot below:

7. Standby Listener Database Service static registration is shown in screenshot below:

8. Configure standby redo log for the primary. 

Let's say the primary has 3 online redo log groups. Then, as per the best practices recommendation by Oracle, there should be 4 standby redo log groups. Use the alter database add standby logfile command to create the logs. Keep all the log file group members the same size as the primary. Run the commands as sysdba replacing <path> and <size> as appropriate

alter database add standby logfile group 4 ('/u01/app/oracle/oradata/cdb1/sby4.ora') size 50m;

alter database add standby logfile group 5 ('/u01/app/oracle/oradata/cdb1/sby5.ora') size 50m;

alter database add standby logfile group 6 ('/u01/app/oracle/oradata/cdb1/sby6.ora') size 50m;

alter database add standby logfile group 7  ('/u01/app/oracle/oradata/cdb1/sby7.ora') size 50m;

Copy the oracle password file from primary to standby using ftp. The password file is in the location $ORACLE_HOME/dbs and its name is pwdCDB1. Ftp it to the standby (re-name it there to pwdCDB1_DR).

9. Create an pfile for the standby instance containing these lines 

DB_NAME=CDB1

ENABLE_PLUGGABLE_DATABASE=TRUE

COMPATIBLE=12.1.0.2.0

DB_UNIQUE_NAME=CDB1_DR

LOG_ARCHIVE_CONFIG='DG_CONFIG=(CDB1,CDB1_DR)'

FAL_SERVER=CDB1

DB_RECOVERY_FILE_DEST='/u01/app/oracle/oradata/fra'

DB_RECOVERY_FILE_DEST_SIZE=10g

LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDB1_DR'

LOG_ARCHIVE_DEST_2='SERVICE=CDB1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDB1_DR'

STANDBY_FILE_MANAGEMENT=AUTO

SGA_TARGET=800M

PGA_AGGREGATE_TARGET=600M

LOG_FILE_NAME_CONVERT='cdb1','cdb1_dr'

DB_FILE_NAME_CONVERT='cdb1','cdb1_dr'

10. From SqlPlus, start up the standby instance in nomount mode and using this pfile. 

For example run these command at os:

$ > Sqlplus sys/oracle@cdb1_dr as sysdba

And this at the resulting SQL prompt:

SQL> Startup nomount pfile='pfile.ora';

11.  Ensure that all the folders along the path of the CDB datafile are present on the standby.

If not present, create them. Below are the folders that were created on the standby, before the duplication task was started.

12. Startup rman for the duplication.

From rman connect to both the primary (as target) and standby (as auxiliary) and duplicate the database. We are collecting the rman ouput to a file called rman.log using the tee utility here.

$ > rman target sys/oracle@cdb1 auxiliary sys/oracle@cdb1_dr | tee rman.log

Rman > duplicate target database for standby from active database nofilenamecheck dorecover;

The output from the duplication are logged to rman.log… Partial results here…

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Mar 12 14:58:01 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDB1 (DBID=842184548)

connected to auxiliary database: CDB1 (not mounted)

RMAN> 2>

Starting Duplicate Db at 12-MAR-15

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=12 device type=DISK

current log archived

…

…

starting media recovery

archived log for thread 1 with sequence 52 is already on disk as file /u01/app/oracle/oradata/fra/CDB1_DR/archivelog/2015_03_12/o1_mf_1_52_bj2gmo93_.arc

archived log for thread 1 with sequence 53 is already on disk as file /u01/app/oracle/oradata/fra/CDB1_DR/archivelog/2015_03_12/o1_mf_1_53_bj2gmplz_.arc

archived log for thread 1 with sequence 54 is already on disk as file /u01/app/oracle/oradata/fra/CDB1_DR/archivelog/2015_03_12/o1_mf_1_54_bj2gmqj6_.arc

archived log file name=/u01/app/oracle/oradata/fra/CDB1_DR/archivelog/2015_03_12/o1_mf_1_52_bj2gmo93_.arc thread=1 sequence=52

archived log file name=/u01/app/oracle/oradata/fra/CDB1_DR/archivelog/2015_03_12/o1_mf_1_53_bj2gmplz_.arc thread=1 sequence=53

archived log file name=/u01/app/oracle/oradata/fra/CDB1_DR/archivelog/2015_03_12/o1_mf_1_54_bj2gmqj6_.arc thread=1 sequence=54

media recovery complete, elapsed time: 00:00:02

Finished recover at 12-MAR-15

Finished Duplicate Db at 12-MAR-15

RMAN>

Recovery Manager complete.

13. Connect to the stanby database and start up managed recovery. 

You can query v$managed_standby as shown to view the progress of the redo apply.

SQL> recover managed standby database using current logfile disconnect;

Media recovery complete.

SQL> select client_process,process,status,sequence# from v$managed_standby;

CLIENT_P PROCESS   STATUS        SEQUENCE#

-------- --------- ------------ ----------

ARCH     ARCH      CONNECTED             0

ARCH     ARCH      CONNECTED             0

ARCH     ARCH      CONNECTED             0

ARCH     ARCH      CONNECTED             0

ARCH     RFS       IDLE                  0

LGWR     RFS       IDLE                 55

N/A      MRP0      APPLYING_LOG         55

7 rows selected.

The standby is receiving from primary the log file with sequence # 55 which is also the log file which is being applied. We have completed the data guard setup.

You can query the v$dataguard_config view for the configuration details.

SQL> select * from v$dataguard_config;

DB_UNIQUE_NAME       PARENT_DBUN                    DEST_ROLE         CURRENT_SCN     CON_ID

-------------------- ------------------------------ ----------------- ----------- ----------

CDB1                 NONE                           PRIMARY DATABASE      1266237          0

CDB1_DR              CDB1                           PHYSICAL STANDBY      1266338          0

SQL>

This completes the exercise. In a later post, I will try to configure statspack on the standby (which I will open in Active Data Guard mode). Statspack can be used to tune performance of queries that may run in standby in its Active Data Guard role.