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   DB File Paths
 ------------   ------   -------   --------------    -----------------------------------------------
 Primary(PRI)   lnx01    dnaprod   dnaprod           /u01/oradata/DNAPROD,/u02/oradata/DNAPROD
 Standby(SB)    lnx02    dnaprod   dnaprod_sb        /u01/oradata/DNAPROD_SB,/u02/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

• For the Primary and Standby to be compatible, it's crucial that their Linux versions (OEL 8), Oracle versions (19c), and Oracle patches all align.
• 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 ;
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

Create the standby logfiles same size and number as extracted above.

 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 3 redo logs found above and their size is 200m, so create 3 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;

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; --Show Status
 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 --Show Status
 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)
      )
  )

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

Bounce Primary Database and Listener

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

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

Prepare Standby

Create Dirs on SB

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

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

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

chown -R oracle:oinstall /u01/oradata
chown -R oracle:oinstall /u02/oradata
chmod -R 775 /u01/oradata
chmod -R 775 /u02/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_sb

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 '/u01/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

 alter database recover managed standby database disconnect from session;

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

 -- Legacy Cmd?
 -- 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

Oracle Docs

• Getting Started
• Creating

DBA

• rootfan.com
• oracle-base.com