How to Roll Forward a Standby Database Using RMAN Incremental Backup After Adding a Datafile to Primary

Step 1: Check and compare the current condition of the primary and standby databases

1.1. On the primary, identify the current sequence number of archived logs that have not been applied to the standby, which is 119311

1.2. On the standby, identify the latest sequence number of archive logs from the primary that has been applied to the standby, which is 102323″

1.3. Determine the size and current SCN of the primary and standby databases

On the primary 

On the standby

As you can see, the primary database has grown by about 10GB since it was last in sync with the standby database. Therefore, the best and fastest way to sync the primary and standby is to get incremental backup from SCN 5975444041629 on the primary, apply it on the standby, and then sync 2 databases.

1.4.    On the primary, check whether datafiles have been added after the standby became out of sync

First, we need to determine the date of the current SCN on the standby database using the following query:

SELECT SCN_TO_TIMESTAMP(5975444041629) AS last_update_date FROM dual;

Then, we should check the primary to identify the datafiles that were created after this date.

Step 2: Normally, we need to get an incremental backup from the primary database based on the minimum checkpoint_change# of the datafiles in the standby, as well as a backup of the primary control file. In addition to these two backups, we need to include a backup of the newly added datafile on the primary. Therefore, the following is what we should do:

2.1.      On the standby database, find the SCN which will be used for the incremental backup at the primary database. The results of the following queries usually are the same. However, if there is a huge difference it is better to take the backup using the SCN from the second query (lesser SCN), as one of the datafiles may be behind

SQL> SELECT CURRENT_SCN FROM V$DATABASE; 
SQL> select min(checkpoint_change#) from v$datafile_header;

On the standby database, stop the managed recovery process (MRP)

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2.2.      On the primary database, back up the current control file

2.3.       On the primary database, back up the database from the specific SCN

2.4       On the primary database, back up the added datafiles

Step 3. Copy all backup sets created on the primary system to the standby system and restore them to the standby database.

3.1.      Restore the backed-up control file.

3.2.      Catalog the backups

3.3.      Restore newly added datafiles:

3.4.      Update the datafile names in the new standby control file. Because the control file is restored from the PRIMARY database and OMF is enabled, cataloging the datafiles in the STANDBY will automatically handle the necessary renaming operations.

You can use the following command to catalog datafiles. 

RMAN> CATALOG START WITH '+DATA/<db_unique_name>/datafile/';

Since my datafiles were on the ACFS, I used the following commands to catalog datafiles.

After cataloging all the files, switch the database to the copy:

3.5.      Recover the database using the incremental backup (the backup that we have from SCN 5975444041629)

3.6.      On the standby database, clear all standby redo log groups:

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1; 
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2; 
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

4.  Now, we can verify that the databases are working correctly and are synchronized on the primary.

4.1.      On the standby database, start the MRP and verify that log shipping is functioning correctly