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     d2-oratrn-s01  dnatra
 StandBy  SB      d1-oratrn-s01  dnatra_sb

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

PRE

 -- Get FRA values on Primary (example below)
 SHOW PARAMETER db_recovery_file_dest;
 /u03/fra
 SHOW PARAMETER db_recovery_file_dest_size;
 2304G

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

 -- Set db_recovery_file_dest_* to match Primary.
 cat > $ORACLE_HOME/dbs/initdnatra.ora <<'EOF'
 DB_NAME=dnatra
 DB_UNIQUE_NAME=dnatra_sb
 db_recovery_file_dest=/u03/fra
 db_recovery_file_dest_size=2304G
 control_files=/u03/controlfiles/dnatra_sb/control01.ctl
 EOF

Remove leading space from each line.

2. On SB, Ensure Directory Structure Exists

 mkdir -p /u01/app/oracle/admin/dnatra/adump
 mkdir -p /u01/app/oracle/oradata/dnatra
 mkdir -p /u02/oradata/DNATRA_SB
 mkdir -p /u03/fra/dnatra
 mkdir -p /u03/controlfiles/dnatra
 mkdir -p /u05/oradata/DNATRA_SB/datafile
 chown -R oracle:oinstall /u01/app/oracle/oradata /u02 /u03 /u05

3. On SB, Start NOMOUNT Using the PFILE

 export ORACLE_SID=dnatra
 sqlplus / as sysdba
 shutdown immediate
 STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/initdnatra.ora';

4. From PRI, Recreate Password File

 ping d1-oratrn-s01
 scp /u01/app/oracle/product/19.3.0.0.0/dbhome_1/dbs/orapwdnatra oracle@d1-oratrn-s01:$ORACLE_HOME/dbs/orapwdnatra

5. On SB, Prepurge Stale Datafiles

 rm -rf /u02/oradata/DNATRA_SB/*
 rm -f /u03/controlfiles/dnatra/control01.ctl

 -- If Required
 rm -f /u05/oradata/DNATRA/datafile/indexes01.dbf
 rm -f /u05/oradata/DNATRA_SB/datafile/indexes01.dbf

 Check disk space: df -h

6. On PRI, run RMAN DUPLICATE

 rman target sys/********@dnatra auxiliary sys/********@dnatra_sb
 connected to target database: DNATRA (DBID=4191999671)
 connected to auxiliary database: DNATRA (not mounted)

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


 -- RMAN Cmd Factoring in a TS (INDEXES) File Moved outside the DB_CREATE_FILE_DEST
    -- Get File Number of files outside the DB_CREATE_FILE_DEST (ex. below)
    SELECT file_id, file_name FROM  dba_data_files
    WHERE  tablespace_name = 'INDEXES' ORDER  BY file_id;
    21

 RUN {
  -- Pin any files outside the DB_CREATE_FILE_DESTINDEXES (ex. below)
  SET NEWNAME FOR DATAFILE 21 TO '/u05/oradata/DNATRA_SB/datafile/indexes01.dbf';

  DUPLICATE TARGET DATABASE
    FOR STANDBY
    FROM ACTIVE DATABASE
    DORECOVER
    SPFILE
      SET DB_UNIQUE_NAME='dnatra_sb'
      SET DB_CREATE_FILE_DEST='/u02/oradata/DNATRA_SB'
      SET DB_RECOVERY_FILE_DEST='/u03/fra'
      SET CONTROL_FILES='/u03/controlfiles/dnatra_sb/control01.ctl'
      SET DB_FILE_NAME_CONVERT=
        '/u02/oradata/DNATRA','/u02/oradata/DNATRA_SB',
        '/u05/oradata/DNATRA','/u05/oradata/DNATRA_SB'
      SET LOG_FILE_NAME_CONVERT=
        '/u02/oradata/DNATRA','/u02/oradata/DNATRA_SB',
        '/u05/oradata/DNATRA','/u05/oradata/DNATRA_SB'
    NOFILENAMECHECK;
 }


 -- Standard RMAN (all dbf files here DB_CREATE_FILE_DEST)
 RMAN>
 DUPLICATE TARGET DATABASE 
   FOR STANDBY 
   FROM ACTIVE DATABASE 
   DORECOVER 
   SPFILE
     SET DB_UNIQUE_NAME='dnatra_sb'
     SET DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/dnatra','/u01/app/oracle/oradata/dnatra'
     SET LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/dnatra','/u01/app/oracle/oradata/dnatra'
     SET DB_RECOVERY_FILE_DEST='/u02/fra/dnatra'
     SET CONTROL_FILES='/u03/controlfiles/dnatra/control01.ctl'
 NOFILENAMECHECK;

  On SB: watch -n 10 df -h /u02 /u03 /u05
  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 or APPLYING_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.

   show parameter LOG_ARCHIVE_DEST_2
   show parameter  LOG_ARCHIVE_CONFIG
   show parameter LOG_ARCHIVE_DEST_STATE_2

   IF REQUIRED (per above param checks)

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

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

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

9. Post-Validation Checks

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

   -- Confirm redo apply is active (Sequence# Check)
   SELECT PROCESS, STATUS, THREAD#, SEQUENCE# FROM V$MANAGED_STANDBY;
   PRI = WRITING
   SB  = APPLYING_LOG
   Sequence# should match.

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

   --Make the PRI view catch up
   Run a switch (or two) on the PRI and recheck:

   -- On PRI
   ALTER SYSTEM SWITCH LOGFILE;
   ALTER SYSTEM ARCHIVE LOG CURRENT;

   -- PRI
   SELECT ads.dest_id, ad.db_unique_name, ads.recovery_mode,
          ads.archived_seq#, ads.applied_seq#
   FROM   v$archive_dest_status ads
   JOIN   v$archive_dest ad ON ad.dest_id = ads.dest_id
   WHERE  ads.status='VALID' AND ad.target='STANDBY'
   ORDER  BY ads.dest_id;

   -- BOTH
   SELECT PROCESS, STATUS, THREAD#, SEQUENCE# FROM V$MANAGED_STANDBY;

   -- Quick sanity Check if INDEXES Outside DB_CREATE_FILE_DEST
   SELECT df.file#,ts.name AS tablespace_name,df.name AS file_name 
   FROM   v$datafile df JOIN   v$tablespace ts ON ts.ts# = df.ts#
   WHERE  ts.name = 'INDEXES' ORDER  BY df.file#;

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/dnatra/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/dnatra/rman/%U.bkp';
 CONFIGURE CHANNEL 2 DEVICE TYPE disk FORMAT '/u04/dnatra/rman/%U.bkp';
 CONFIGURE CHANNEL 3 DEVICE TYPE disk FORMAT '/u04/dnatra/rman/%U.bkp';
 CONFIGURE CHANNEL 4 DEVICE TYPE disk FORMAT '/u04/dnatra/rman/%U.bkp';

 show all;

APPENDIX

Legacy Pfile

 %green%--This Type Better if You Need to Set Everything before RMAN DUPLICATE%%
 cat <<EOF > $ORACLE_HOME/dbs/init{$:vPrimary}.ora
 DB_NAME={$:vPrimary}
 DB_UNIQUE_NAME={$:vPrimary}_sb
 CONTROL_FILES='/u03/controlfiles/{$:vPrimary}/control01.ctl'
 DB_CREATE_FILE_DEST='/u01/app/oracle/oradata/{$:vPrimary}'
 DB_RECOVERY_FILE_DEST='/u02/fra/{$:vPrimary}'
 DB_RECOVERY_FILE_DEST_SIZE=7000G
 FAL_SERVER={$:vPrimary}
 FAL_CLIENT={$:vPrimary}_sb
 LOG_ARCHIVE_CONFIG='DG_CONFIG=({$:vPrimary},{$:vPrimary}_sb)'
 LOG_ARCHIVE_DEST_2='SERVICE={$:vPrimary} LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME={$:vPrimary}'
 EOF

File Move QC

 -- Post RMAN Quick Check
 If indexes01.dbf was created here /u05/oradata/DNATRA/datafile move it as so:  
    ALTER DATABASE MOVE DATAFILE '/u05/oradata/DNATRA/datafile/indexes01.dbf'
    TO '/u05/oradata/DNATRA_SB/datafile/indexes01.dbf';