Oracle DBA

Keeping a standby database synchronized with its primary is critical for fast failover, reporting offload, and disaster recovery readiness. When archived redo transfer is slow, missing, or the standby has fallen behind significantly, an SCN-based incremental RMAN refresh is a fast, reliable way to bring the standby forward. This article provides a clear, step-by-step operational procedure, sample RMAN and SQL commands, and the rationale and safeguards you must observe.

Core idea

Instead of copying and applying a long chain of archived redo (which forces the standby to roll forward and roll back many transactions), take an incremental backup from the primary using the standby’s current SCN, ship the incremental backup to the standby, restore and recover. This method applies only committed changes and is usually faster and cleaner than replaying a long sequence of archived logs.

High-level workflow (what we do)

1. Check archive sequence numbers on both primary and standby.
2. Stop MRP (managed recovery) on standby.
3. Capture current standby SCN.
4. Shutdown mount standby to prepare for restore.
5. On primary, take an RMAN incremental backup FROM SCN <standby_scn> (level 1 cumulative or differential as required).
6. Create a standby control file on the primary and copy it if needed.
7. Transfer backup pieces and standby controlfile to the standby host.
8. Catalog backup pieces on the standby (if using RMAN there).
9. Restore the datafile copies (incremental apply) and run RECOVER DATABASE.
10. Start MRP and verify sequences and applied logs.

Below are the concrete commands and sample outputs from a representative run that follows this approach.

Step-by-step procedure with commands and sample outputs

1) Check archive sequence on primary and standby

On primary:

SQL> SELECT thread#, MAX(sequence#) "Last Primary Seq Generated"
     FROM v$archived_log val, v$database vdb
     WHERE val.resetlogs_change# = vdb.resetlogs_change#
     GROUP BY thread# ORDER BY 1;

Sample:

THREAD#   Last Primary Seq Generated
1         68

On standby:

SQL> SELECT thread#, MAX(sequence#) "Last Standby Seq Received"
     FROM v$archived_log val, v$database vdb
     WHERE val.resetlogs_change# = vdb.resetlogs_change#
     GROUP BY thread# ORDER BY 1;

Sample:

THREAD#   Last Standby Seq Received
1         62

2) Stop managed recovery (MRP) on standby

On standby:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
-- or, if using DISCONNECT syntax:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Sample response:

Database altered.

3) Record the standby SCN

On standby (while mounted):

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

Sample:

CURRENT_SCN
2173711

Capture this SCN — you will use it as FROM SCN for the RMAN incremental backup on the primary.

4) Shutdown the standby (clean state for restore)

On standby:

SQL> SHUTDOWN IMMEDIATE;

Confirm the instance is down. This ensures the standby will accept the restored files and controlfile replacement if needed.

5) Take an RMAN incremental backup from the captured SCN on the primary

On primary, start RMAN and run:

$ rman target /
RMAN> RUN {
  ALLOCATE CHANNEL c1 TYPE DISK FORMAT '/u01/backup/incr_%U.bkp';
  BACKUP INCREMENTAL FROM SCN 2173711 DATABASE;
  RELEASE CHANNEL c1;
}

Sample RMAN excerpt:

Starting backup at 22-JUL-19
channel c1: starting full datafile backup set
...
piece handle=/u01/backup/archive2pu4o8oo_1_1.bkp
Finished backup at 22-JUL-19

Notes:

• Use BACKUP INCREMENTAL FROM SCN <scn> to capture changes since the standby SCN.
• Consider using BACKUP INCREMENTAL LEVEL 1 CUMULATIVE FROM SCN ... if that better fits your backup strategy.

6) Create (or refresh) a standby control file on the primary (optional but often useful)

On primary:

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/backup/standby.ctl';

This controlfile can be copied to the standby and used to mount the database if needed.

7) Transfer backup pieces and standby controlfile to the standby server

Use scp or secure copy methods:

# On primary:
scp /u01/backup/incr_*.bkp oracle@standby:/u01/backup/
scp /u01/backup/standby.ctl oracle@standby:/u01/backup/

Confirm files are present on standby:

ls -l /u01/backup

8) On standby: start instance in MOUNT and catalog (if needed)

On standby, bring the instance to MOUNT (if not yet):

SQL> STARTUP MOUNT;

Start RMAN and catalog the transferred backup pieces or the directory:

$ rman target /
RMAN> CATALOG START WITH '/u01/backup';
# (RMAN will prompt to confirm unknown files; answer YES to catalog relevant pieces)

Sample RMAN output (cataloging):

List of Cataloged Files
File Name: /u01/backup/standby.ctl

If the incremental pieces are from the same DBID, they should be usable; if RMAN reports foreign DBID, ensure you are working on the correct standby matching the primary DBID.

9) Restore incremental backup and recover database on standby

Still in RMAN on standby:

RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;

Sample output excerpt:

channel ORA_DISK_1: starting incremental datafile backup set restore
reading from backup piece /u01/backup/incr_...bkp
restored backup piece 1
starting media recovery
media recovery complete
Finished recover at ...

Notes:

• RMAN will apply the incremental image copies to the standby datafiles, applying only committed changes captured in the incremental backup.
• If RMAN needs archived logs not present on standby, supply them (copy from primary or backup) and re-run RECOVER.

10) Start Managed Recovery Process (MRP) on standby

After recover completes, resume managed recovery to continue applying incoming archived logs:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Sample:

Database altered.

11) Verify sequences and apply progress on both nodes

On primary:

SQL> SELECT thread#, MAX(sequence#) "Last Primary Seq Generated"
     FROM v$archived_log val, v$database vdb
     WHERE val.resetlogs_change# = vdb.resetlogs_change#
     GROUP BY thread# ORDER BY 1;

On standby:

SQL> SELECT thread#, MAX(sequence#) "Last Standby Seq Received"
     FROM v$archived_log val, v$database vdb
     WHERE val.resetlogs_change# = vdb.resetlogs_change#
     GROUP BY thread# ORDER BY 1;

To check what has been applied:

SQL> SELECT thread#, MAX(sequence#) "Last Standby Seq Applied"
     FROM v$archived_log val, v$database vdb
     WHERE val.resetlogs_change# = vdb.resetlogs_change#
       AND val.applied IN ('YES','IN-MEMORY')
     GROUP BY thread# ORDER BY 1;

Sample expected outcome (after a successful incremental refresh):

Primary: THREAD# = 71
Standby received: THREAD# = 71
Standby applied: THREAD# = 71

Rationale & Benefits

• Faster synchronization: Incremental backups apply a smaller set of committed changes rather than replaying many archived logs (and their associated rollbacks), so refresh is faster.
• Works with missing archives: If some archives on primary were lost, incremental backups still capture committed changes and can be used to refresh standby.
• Reduced I/O on standby during recovery: Incremental apply is often more efficient than redo apply for long gaps.
• Repeatable and auditable: RMAN provides consistent, logged operations you can script and include in runbooks.

Important cautions and gotchas

• SCN accuracy is critical. Use the CURRENT_SCN from the standby (while mounted) as the FROM SCN on the primary. Using an incorrect SCN can lead to inconsistent backups or RMAN refusing to use pieces.
• DBID must match. RMAN backups/catalog operations require matching DBIDs. Do not mix backups from different databases.
• Control file and SPFILE considerations. If you restore or replace controlfiles, ensure all configured control file locations are updated and consistent.
• Validate backup pieces before restore. Use RMAN> VALIDATE or RESTORE PREVIEW where appropriate.
• Space & performance: Ensure the standby has enough disk space (FRA, datafile locations) for restores and that network transfer of backup pieces is efficient.
• Backup retention/retention policy: Ensure the incremental plus base level backups are present and retained long enough to support the FROM SCN operations.
• Test in non-production first. Always rehearse the full flow in a test environment.

Troubleshooting checklist

• RMAN reports foreign database file DBID when cataloging: verify DBID and that you sent the correct backup pieces for this standby.
• RMAN fails to restore because it needs archived logs: copy the missing archives from primary or your backup repository and re-run RECOVER.
• Datafile mismatch or corrupted backups: run RMAN> RESTORE VALIDATE or BACKUP VALIDATE on primary to confirm backup integrity.
• After restore, standby refuses to start managed recovery: inspect alert log and v$managed_standby for errors and follow messages (often missing archives, controlfile mismatch, or file permission issues).

Recommended best practices

• Schedule periodic incremental-based refreshes for environments where standby lag regularly accumulates.
• Automate SCN collection, incremental backup, secure transfer, and RMAN restore steps as an orchestrated operation with logging and alerting.
• Keep at least one full backup and enough incremental chain coverage to support FROM SCN operations for the expected window.
• Protect archived redo (offsite copies or secondary copies) to avoid gaps that force large recovery windows.
• Monitor standby lag and alert when thresholds are exceeded so refresh actions can be taken proactively.

Post-operation checklist

• Verify v$dataguard_stats, v$managed_standby and archived log sequences match between primary and standby.
• Confirm standby is in RECOVER (MRP) and is applying logs (v$managed_standby shows active processes).
• Run a targeted health check (alert logs, dbv if necessary, sample queries against read-only standby).
• Document the operation: SCN used, backup piece names, timestamps, and any anomalies.