Oracle 19c Data Guard - Step by Step

• Prepare Primary
• Prepare SB
• Status
• Configure Data Guard
• Enable Standby READ-ONLY Mode
• Post Creation Startup

Overview

Oracle Data Guard consists of services to create and maintain standby databases. What follows is an example step-by-step session to create a Data Guard environment.

Session Specifications

 Role           System   DB_NAME   DB_UNIQUE_NAME
 Primary(PRI)   lnx01    dnaprod   dnaprod       
 Standby(SB)    lnx02    dnaprod   dnaprod_sb    

 Storage Locations       Primary                  SB
 DB_CREATE_FILE_DEST     /u02/oradata/dnaprod...  /u02/oradata/DNAPROD_SB...
 DB_RECOVERY_FILE_DEST   /u03/oradata/dnaprod...  /u03/oradata/DNAPROD_SB...

ORACLE_HOME

 /u01/app/oracle/product/19.3.0.0.0/dbhome_1

/etc/hosts entries

 192.168.56.71 lnx01.local lnx01
 192.168.56.72 lnx02.local lnx02

SB Logs (once created)

  Alert log: /u01/app/oracle/diag/rdbms/dnaprod_sb/dnaprod/trace/alert_dnaprod.log
  Data Guard Broker log: /u01/app/oracle/diag/rdbms/dnaprod_sb/dnaprod/trace/drcdnaprod.log

Prerequisite

• The Linux version (OEL 8), Oracle version (19c), and Oracle patch version must match on both the Primary and Standby.
  • Linux Kernel Version: uname -r
  • Linux Relase Version: cat /etc/system-release
  • Oracle Version: SELECT * FROM v$version;
• Primary already has database created, SB just Oracle database product installed.
• Latest Opatch and Oracle patches applied.
• Confirm Connectivity
  • ping lnx01
  • ping lnx02

Prepare Primary

Enable archivelog

 sqlplus / as sysdba
 select log_mode from v$database;
 ARCHIVELOG

If LOG_MODE = NOARCHIVELOG then:

alter system set log_archive_dest_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;
shutdown immediate
startup mount
alter database archivelog;
alter database open;
alter system switch logfile;
select name from v$archived_log;

Enable Force Logging

 select force_logging from v$database; --Show Status
 alter database force logging;

Get Redo Log File Info

 select group#, thread#, bytes/1024/1024 mb from v$log;
     GROUP#    THREAD#	      MB
  ---------- ---------- ----------
  	 1	    1	     200
  	 2	    1	     200
  	 3	    1	     200
  	 4	    1	     200

 select thread#, instance from v$thread ;
     THREAD# INSTANCE
  ---------- ----------
  	 1 dnaprod

Create SB Redo Logs

• Make sure to keep the thread# and logfile size exactly same.
• Also, create one additional standby redo log.

 alter database add standby logfile thread 1 size 200m;
 alter database add standby logfile thread 1 size 200m;
 alter database add standby logfile thread 1 size 200m;
 alter database add standby logfile thread 1 size 200m;
 alter database add standby logfile thread 1 size 200m;

 select group#, thread#, sequence#, bytes/1024/1024 mb, archived, status from v$standby_log;
 select member from v$logfile where type = 'STANDBY';

In this session 4 redo logs found above and their size is 200m, so create 5 standby logfiles of 200m each.

Show db_unique_name

show parameter db_unique_name

 dnaprod

Set log_archive_config

 alter system set log_archive_config='DG_CONFIG=(dnaprod,dnaprod_sb)' scope=both;

Use DB_UNIQUE_NAME for each.

Set log_archive_dest_2

 alter system set log_archive_dest_2='SERVICE=dnaprod_sb ASYNC 
 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dnaprod_sb' scope=both;

Set fal_server

 show parameter fal_server;
 alter system set fal_server ='dnaprod_sb' scope=both;

fal_client is not needed anymore in Oracle 19c.

Set standby_file_management

 show parameter standby_file_management
 alter system set standby_file_management='AUTO' scope=both;

Enable flashback

 alter database flashback on;

Confirm Password File Exists

 ls $ORACLE_HOME/dbs/orapw*
 /u01/app/oracle/product/19.3.0.0.0/dbhome_1/dbs/orapwdnaprod

Create\Edit tnsnames.ora (PRI and SB)

These values are for both PRI and SB.

cd /u01/app/oracle/product/19.3.0.0.0/dbhome_1/network/admin

DNAPROD,dnaprod =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lnx01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dnaprod)
    )
  )

DNAPROD_SB,dnaprod_sb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lnx02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dnaprod_sb)
    )
  )

Create\Edit listener.ora

PRI

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

SID_LIST_LISTENER =
  (SID_LIST =
      (SID_DESC =
      (GLOBAL_DBNAME = dnaprod)
      (SID_NAME=dnaprod)
        (ORACLE_HOME=/u01/app/oracle/product/19.3.0.0.0/dbhome_1)
      )
  )

ADR_BASE_LISTENER = /u01/app/oracle

Reload the listener

 lsnrctl reload

SB

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

SID_LIST_LISTENER =
  (SID_LIST =
      (SID_DESC =
        (GLOBAL_DBNAME = dnaprod_sb)
        (SID_NAME=dnaprod)
        (ORACLE_HOME=/u01/app/oracle/product/19.3.0.0.0/dbhome_1)
      )
  )

ADR_BASE_LISTENER = /u01/app/oracle

Bounce Primary Database and Listener

 -- Stop
 sqlplus / as sysdba
 sqlplus> shutdown immediate
 lsnrctl stop

 -- Start
 lsnrctl start
 sqlplus / as sysdba
 sqlplus> startup

Prepare Standby

Ensure Net Files Configured

• tnsnames.ora
• listener.ora (SB)

Create Dirs on SB

su -
mkdir -p /u01/app/oracle/admin/dnaprod_sb/adump
chmod -R 775 /u01/app/oracle/admin/dnaprod_sb/adump

mkdir -p /u02/oradata/DNAPROD_SB/controlfile
mkdir -p /u02/oradata/DNAPROD_SB/datafile
mkdir -p /u02/oradata/DNAPROD_SB/onlinelog

mkdir -p /u03/oradata/DNAPROD_SB/archivelog
mkdir -p /u03/oradata/DNAPROD_SB/autobackup
mkdir -p /u03/oradata/DNAPROD_SB/controlfile
mkdir -p /u03/oradata/DNAPROD_SB/onlinelog

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

Create Init Files

 lnx02> echo 'DB_NAME=dnaprod' > $ORACLE_HOME/dbs/initdnaprod_sb.ora

Copy the password file from the primary server to the standby server.

 lnx01> scp /u01/app/oracle/product/19.3.0.0.0/dbhome_1/dbs/orapwdnaprod 
        oracle@lnx02:/u01/app/oracle/product/19.3.0.0.0/dbhome_1/dbs/orapwdnaprod

Place on one line to execute.

Confirm On SB

 lnx02> ls -al /u01/app/oracle/product/19.3.0.0.0/dbhome_1/dbs/orapwdnaprod

Start listener on SB

 lsnrctl start

Confirm TNS

Run from Both PRI and SB.

 tnsping DNAPROD
 tnsping DNAPROD_SB

Set ORACLE_SID on SB

 export ORACLE_SID=dnaprod

Start the SB in NOMOUNT

 sqlplus / as sysdba
 STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/initdnaprod_sb.ora';
 exit

From SB, Duplicate Database

Connect to RMAN

Connect to the primary database and to the standby at the same time.

 rman TARGET sys/*********@dnaprod AUXILIARY sys/*********@dnaprod_sb

Confirm RMAN Connected OK

Ensure shows connected OK to both as so:

  connected to target database: DNAPROD (DBID=nnnnnnnnnn)
  connected to auxiliary database: DNAPROD (not mounted)

Run RMAN Cmds to DUPLICATE

DUPLICATE TARGET DATABASE 
  FOR STANDBY 
  FROM ACTIVE DATABASE 
  DORECOVER 
  SPFILE 
  SET DB_UNIQUE_NAME 'dnaprod_sb' COMMENT 'Is standby' 
  SET LOG_ARCHIVE_DEST_2 'SERVICE=DNAPROD ASYNC 
      VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dnaprod' 
  SET AUDIT_FILE_DEST '/u01/app/oracle/admin/dnaprod_sb/adump' 
  SET CONTROL_FILES '/u02/oradata/DNAPROD_SB/controlfile/control01.ctl' 
  SET FAL_SERVER 'dnaprod' 
  NOFILENAMECHECK;

Example Output Upon Successful Completion

  Finished Duplicate Db at 23-APR-24

Once the DUPLICATE command is done, the standby database has been created.

If DG Not Enabled Yet or SB Status Shows Not Transferring

 -- Bounce SB
 sqlplus / as sysdba
 SHUTDOWN IMMEDIATE;
 STARTUP MOUNT;

 -- Then Issue
 alter database recover managed standby database disconnect from session;

Take Note (If DG cfg'd on PRI and other SBs)

• This new SB will not auto update until DG configured.
• You can get this SB to update only if you bounce SB and manually issue recover.
  alter database recover managed standby database disconnect from session;

Status

-- DB Status: Both

 COL database_role     FORMAT a20
 COL db_unique_name    FORMAT a15 
 COL instance          FORMAT a15 
 COL open_mode         FORMAT a15
 COL protection_level  FORMAT a25
 COL switchover_status FORMAT a15
 SELECT database_role, db_unique_name Instance, 
        open_mode, protection_level, switchover_status 
 FROM V$DATABASE;

-- archive log: Primary

 archive log list

-- SB Check (after DG configured)

 SELECT process,thread#,sequence#,status 
 FROM v$managed_standby WHERE process='MRP0';

-- Transfer Check

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

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

Configure Data Guard

Add DGMGRL Entry to listener.ora

PRI

     (SID_DESC=
        (GLOBAL_DBNAME=dnaprod_DGMGRL)
        (SID_NAME=dnaprod)
        (ORACLE_HOME=/u01/app/oracle/product/19.3.0.0.0/dbhome_1)
     )

SB

     (SID_DESC=
        (GLOBAL_DBNAME=dnaprod_sb_DGMGRL)
        (SID_NAME=dnaprod)
        (ORACLE_HOME=/u01/app/oracle/product/19.3.0.0.0/dbhome_1)
     )

Reload Listner

  lsnrctl reload

Broker Configuration

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

 -- Clear log_archive_dest_2 on PRI
 show parameter log_archive_dest_2
 SQLPlus> ALTER SYSTEM SET log_archive_dest_2='' scope=both;
 LOG_ARCHIVE_DEST_2 is valid only for the primary role. 

 -- On Primary
 dgmgrl sys/*********@dnaprod

 DGMGRL> CREATE CONFIGURATION dg_cfg1 AS PRIMARY DATABASE IS dnaprod CONNECT IDENTIFIER IS dnaprod;
 Configuration "dg_cfg1" created with primary database "dnaprod"

 DGMGRL> ADD DATABASE dnaprod_sb AS CONNECT IDENTIFIER IS dnaprod_sb MAINTAINED AS PHYSICAL;
 Database "dnaprod_sb" added

 DGMGRL> ENABLE CONFIGURATION;
 Enabled.

Enable Flashback Database

 -- On Primary
 sqlplus / as sysdba
 SELECT flashback_on FROM v$database;

   -- If Needed on PRI
   ALTER DATABASE FLASHBACK ON;

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

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

Enable Standby READ-ONLY Mode

-- On SB

  sqlplus / as sysdba
  --STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/initdnaprod_sb.ora';
 alter database open read only;

Below Not Needed after DG Enabled

 alter database recover managed standby database disconnect from session;

Post Creation Startup

After creation, if you ever need to restart the SB

 sqlplus / as sysdba
 startup mount
 alter database open read only; -- If Licensed

Optimally configure the dbora script and change startup command to startup mount in the $ORACLE_HOME/bin/dbstart.

References

• 19c Getting Started
• 19c Creating
• 12c (12.2)
  • Installation
  • Prerequisites

DBA

• rootfan.com
• oracle-base.com