DataGuard

Oracle 19c - Data Guard - Mult SBs

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

References

Overview

Oracle Data Guard consists of services to create and maintain standby databases. What follows are example specs to create a multiple SB Data Guard environment. Each SB was create one at a time.

Session Specifications

 Initial Role   System   DB_NAME   DB_UNIQUE_NAME    DB File Paths /u01-2/oradata/<name>
 ------------   ------   -------   --------------    ----------------------------------------
 Primary(PRI)   alfa     dnaprod   dnaprod           DNAPROD
 Standby(SB)    beta     dnaprod   dnaprod_beta      DNAPROD_BETA
 Standby(SB)    gama     dnaprod   dnaprod_gama      DNAPROD_GAMA
 Standby(SB)    zeta     dnaprod   dnaprod_zeta      DNAPROD_ZETA

ORACLE_HOME

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

/etc/hosts entries

 192.168.56.71 alfa.local alfa
 192.168.56.72 beta.local beta
 192.168.56.73 gama.local gama
 192.168.56.74 zeta.local zeta

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

On the Primary and Standby, the Linux versions (OEL 8), Oracle versions (19c), and Oracle patch versons must be the same.
Primary already has database created, SBs just Oracle database product installed.
Latest Opatch and Oracle patches applied.
Confirm Connectivity
- ping alfa
- ping beta
- ping gama
- ping zeta

Fun Facts

Log_Archive_dest_n is only needed on the original PRI during creation.
- SB databases are receivers of archive redo logs, not senders. They only apply the archive redo logs shipped from the Primary to keep their data synchronized.
- Configuring Log_Archive_dest_n on a SB would be redundant because it doesn't generate its own archive redo logs.
- Exception: If you have an extended disaster or need to have the SB permanently become the PRI, then you will need archive redo logs shipped to the other SB locations. These docs reflect that scenario.

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

Uses the DB_UNIQUE_NAME.

 alter system set log_archive_config='DG_CONFIG=
 (dnaprod,dnaprod_beta,dnaprod_gama,dnaprod_zeta)' scope=both;

Put this on one line.

Set log_archive_dest_n

 alter system set LOG_ARCHIVE_DEST_n='SERVICE="<DB_UNIQUE_NAME>" ASYNC 
    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=<DB_UNIQUE_NAME>';

 alter system set LOG_ARCHIVE_DEST_2='SERVICE="dnaprod_beta" ASYNC 
   VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dnaprod_beta';

Just configure current SB. When DG configured it will update these.

Set fal_server

 show parameter fal_server;
 alter system set fal_server ='dnaprod,dnaprod_beta,dnaprod_gama,dnaprod_zeta' scope=both;

fal_client is not needed anymore in Oracle 19c.
After DG configured: PRI will have no value & SBs will show all but currrent.

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 $ORACLE_HOME/network/admin

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

DNAPROD_BETA,dnaprod_beta =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = beta)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dnaprod_beta)
    )
  )

DNAPROD_GAMA,dnaprod_gama =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = gama)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dnaprod_gama)
    )
  )

DNAPROD_ZETA,dnaprod_zeta =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = zeta)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dnaprod_zeta)
    )
  )

Create\Edit listener.ora

cd $ORACLE_HOME/network/admin

PRI

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

SID_LIST_LISTENER =
  (SID_LIST =
      (SID_DESC =
      (SID_NAME=dnaprod)
      (GLOBAL_DBNAME = 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

beta

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

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

ADR_BASE_LISTENER = /u01/app/oracle

gama

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

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

ADR_BASE_LISTENER = /u01/app/oracle

zeta

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

SID_LIST_LISTENER =
  (SID_LIST =
      (SID_DESC =
        (SID_NAME=dnaprod)
        (GLOBAL_DBNAME = dnaprod_zeta)
        (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

Create Dirs on All Systems

Alfa Dirs (already created on Primary\alfa during DBCA)

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

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

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

Beta Dirs

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

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

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

Gama Dirs

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

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

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

Zeta Dirs

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

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

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

Admin & Oradata Privs (set this on all SBs)

chown -R oracle:oinstall /u01/app/oracle/admin
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.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_BETA
 tnsping DNAPROD_GAMA
 tnsping DNAPROD_ZETA

Just tnsping SBs cfg'd thus far.

Set ORACLE_SID on SB

 export ORACLE_SID=dnaprod

Ensure set in .bashrc also, ex: export ORACLE_SID=dnaprod

Start the SB in NOMOUNT

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

From SB, Duplicate Database

Connect to RMAN

Set dnaprod_sb to current SB (beta, gama, zeta)

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

Set Per Each SB (beta, gama, zeta):

DB_UNIQUE_NAME 'dnaprod_sb'
SET AUDIT_FILE_DEST '/u01/app/oracle/admin/dnaprod_sb/adump'
SET CONTROL_FILES '/u01/oradata/DNAPROD_SB/controlfile/control01.ctl'

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;

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 SID_LIST_LISTENER

PRI

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

SB

beta

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

gama

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

zeta

     (SID_DESC=
        (GLOBAL_DBNAME=dnaprod_zeta_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;
 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_beta AS CONNECT IDENTIFIER IS dnaprod_beta MAINTAINED AS PHYSICAL;
 Database "dnaprod_beta" added

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

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

 DGMGRL> ENABLE CONFIGURATION;
 Enabled.

Above is by-the-book. In reality, you probably will need to do this after first SB added.

Enable Flashback Database

-- On Primary

 sqlplus / as sysdba
 SELECT flashback_on FROM v$database;

   -- If Needed on PRI  
   ALTER DATABASE FLASHBACK ON;

-- For Each SB

 Set _sb to current SB (beta, gama, zeta)
 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

Ensure ORACLE_SID set in .bashrc

 export ORACLE_SID=dnaprod

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.

Add Standby to DG

This cleanly purges and recreates cfg.
Commonly required to add 3rd, 4th...SB.

-- Remove DG Cfg

dgmgrl sys/go@dnaprod
remove configuration;

-- Set DG_BROKER_START=FALSE On All (Pri and SBs)

ALTER SYSTEM SET DG_BROKER_START=FALSE scope=both sid='*';
show parameter dg_broker_config_file

-- Purge OS Files on PRI

cd $ORACLE_HOME/dbs
ls dr* 
rm dr?dnaprod.dat

-- Clear LOG_ARCHIVE_DEST_n

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='' scope=both sid='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='' scope=both sid='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_4='' scope=both sid='*';
show parameter log_archive_des

-- Bounce PRI

sqlplus / as sysdba
shutdown immediately
startup
show parameter log_archive_dest

-- Set DG_BROKER_START=TRUE On All (Pri and SBs)

ALTER SYSTEM SET DG_BROKER_START=TRUE scope=both sid='*';
show parameter DG_BROKER

If Primary, bounce database.

-- On PRI, Configure DG

dgmgrl sys/go@dnaprod
CREATE CONFIGURATION dg_cfg1 AS PRIMARY DATABASE IS dnaprod CONNECT IDENTIFIER IS dnaprod;
ADD DATABASE dnaprod_beta AS CONNECT IDENTIFIER IS dnaprod_beta MAINTAINED AS PHYSICAL;
ADD DATABASE dnaprod_gama AS CONNECT IDENTIFIER IS dnaprod_gama MAINTAINED AS PHYSICAL;
ADD DATABASE dnaprod_zeta AS CONNECT IDENTIFIER IS dnaprod_zeta MAINTAINED AS PHYSICAL;  
ENABLE CONFIGURATION;

Cfg just all SBs active thus far.

References

DBA