DataGuard

Rebuild SB (Data ReSync of Existing SB)

Overview

This process covers the refresh (AKA rebuild) of the SB.
Assumes all the base settings are the same, i.e. you just want to refresh the data on the SB.

 Role     Abbrev  Hostname  db_unique_name
 -------  ------  --------  --------------
 Primary  PRI     lnx01     dev
 StandBy  SB      lnx02     dev_sb

Change entries to match your PRI and SB environment.
SELECT value FROM v$parameter WHERE name = 'db_unique_name';

1. On SB, Create Fresh Parameter File (PFILE)

 cat <<EOF > $ORACLE_HOME/dbs/initdev.ora
 DB_NAME=dev
 DB_UNIQUE_NAME=dev_sb
 CONTROL_FILES='/u03/controlfiles/dev/control01.ctl'
 DB_CREATE_FILE_DEST='/u01/app/oracle/oradata/dev'
 DB_RECOVERY_FILE_DEST='/u02/fra/dev'
 DB_RECOVERY_FILE_DEST_SIZE=7000G
 FAL_SERVER=dev
 FAL_CLIENT=dev_sb
 LOG_ARCHIVE_CONFIG='DG_CONFIG=(dev,dev_sb)'
 LOG_ARCHIVE_DEST_2='SERVICE=dev LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dev'
 EOF

Remove leading space from each line.

2. On SB, Ensure Directory Structure Exists

 mkdir -p /u01/app/oracle/oradata/dev
 mkdir -p /u02/fra/dev
 mkdir -p /u03/controlfiles/dev
 mkdir -p /u01/app/oracle/admin/dev/adump
 chown -R oracle:oinstall /u01 /u02 /u03

3. On SB, Start NOMOUNT Using the PFILE

 export ORACLE_SID=dev
 sqlplus / as sysdba
 STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/initdev.ora';

4. From PRI, Recreate Password File

 ping lnx02
 scp /u01/app/oracle/product/19.3.0.0.0/dbhome_1/dbs/orapwdev oracle@lnx02:$ORACLE_HOME/dbs/orapwdev

5. On SB, Prepurge Stale Datafiles

 rm -rf /u02/oradata/DEV_SB/*
 rm -f /u03/controlfiles/dev/control01.ctl

 Check disk space: df -h

6. On PRI, run RMAN DUPLICATE

 rman target sys/********@dev auxiliary sys/********@dev_sb
 connected to target database: DEV (DBID=4191999671)
 connected to auxiliary database: DEV (not mounted)

 If not connected as shown:
 SHUTDOWN IMMEDIATE then STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/initdev.ora';

 RMAN>
 DUPLICATE TARGET DATABASE 
   FOR STANDBY 
   FROM ACTIVE DATABASE 
   DORECOVER 
   SPFILE
     SET DB_UNIQUE_NAME='dev_sb'
     SET DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/dev','/u01/app/oracle/oradata/dev'
     SET LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/dev','/u01/app/oracle/oradata/dev'
     SET DB_RECOVERY_FILE_DEST='/u02/fra/dev'
     SET CONTROL_FILES='/u03/controlfiles/dev/control01.ctl'
 NOFILENAMECHECK;

  On SB: watch -n 10 df -h /u02 /u03 /u01
  with 10gb network: [Est. 2 Hours of 7tb database]

7. On SB, Enable Flashback and Start Recovery

   -- Start Redo Apply (initial test)
   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

   -- Confirm MRP0 recovery process
   SELECT PROCESS, STATUS, THREAD#, SEQUENCE# FROM V$MANAGED_STANDBY;
   You should see: MRP0 with status like WAIT_FOR_LOG

   -- Stop Recovery → Enable Flashback → Restart Recovery
   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
   ALTER DATABASE FLASHBACK ON;
   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

8. On PRI, Restore Data Guard Parameters and Resume Redo Transport
If you previously cleared any Data Guard config (e.g. LOG_ARCHIVE_DEST_2='')
before the schema refresh, you must now restore them.

   -- Reconfigure redo shipping to the SB
   ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=dev_sb LGWR ASYNC 
      VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dev_sb' SCOPE=BOTH;

   -- Restore Data Guard configuration awareness
   ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(dev,dev_sb)' SCOPE=BOTH;

   -- Re-enable redo transport
   ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

   -- Confirm status is VALID
   SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;

9. Post-Validation Checks

   -- Confirm redo apply is active
   SELECT PROCESS, STATUS, THREAD#, SEQUENCE# FROM V$MANAGED_STANDBY;

   -- Confirm archive destination status and apply sequence
   SELECT DEST_ID, STATUS, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID > 1;

   -- Confirm standby role, open mode, switchover readiness
   SELECT NAME, OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS FROM V$DATABASE;

   -- Confirm Flashback Database is ON
   SELECT FLASHBACK_ON FROM V$DATABASE;

As Needed

Reset RMAN

The following command resets all the RMAN parameters back to their default values.

 SQL> execute dbms_backup_restore.resetConfig;

Reset RMAN values for your environment (rman.cfg.rmn).

 # rman.cfg.rmn
 CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';
 CONFIGURE CONTROLFILE AUTOBACKUP on;
 CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE disk TO '/u04/dev/rman/%F';
 CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
 CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u03/rman/snapshot.ctl';

 CONFIGURE DEVICE TYPE disk BACKUP TYPE TO compressed backupset PARALLELISM 4;
 CONFIGURE CHANNEL 1 DEVICE TYPE disk FORMAT '/u04/dev/rman/%U.bkp';
 CONFIGURE CHANNEL 2 DEVICE TYPE disk FORMAT '/u04/dev/rman/%U.bkp';
 CONFIGURE CHANNEL 3 DEVICE TYPE disk FORMAT '/u04/dev/rman/%U.bkp';
 CONFIGURE CHANNEL 4 DEVICE TYPE disk FORMAT '/u04/dev/rman/%U.bkp';

 show all;