Rolling Forward a Physical Standby Database using RMAN Incremental Backup
There is a huge gap around 5k between the archive logs of Standby Database and Primary Database.
Instead of waiting for the Redo Apply to sync the archive logs, we follow the below steps to sync the Standby database.
1. Cancel the Managed Recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
2. Find the SCN of the Standby Database
SELECT CURRENT_SCN FROM V$DATABASE;
11878709541860
select min(checkpoint_change#) from v$datafile_header;
11878709541861
We have to use the lower of the two scn values. 11878709541860
3. Connect to Primary and Identify the datafile needed for backup
SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 11878709541860;
322 +DATA/PRODNEW/DATAFILE/data.593.1063685263
323 +DATA/PRODNEW/DATAFILE/data.594.1063879113
324 +DATA/PRODNEW/DATAFILE/data.595.1063879183
325 +DATA/PRODNEW/DATAFILE/data.596.1063879207
326 +DATA/PRODNEW/DATAFILE/data.597.1063902133
327 +DATA/PRODNEW/DATAFILE/data.598.1063965125
328 +DATA/PRODNEW/DATAFILE/data.599.1063965193
4. Connect to Primary and take the backup.
RMAN> backup datafile 322,323,324,325,326,327,328 format '/u01/backup/ForStandby_%U' tag 'FORSTANDBY';
RMAN> backup incremental from SCN 11878709541860 database format '/u01/backup/ForStandby_%U' tag 'FORSTANDBY';
RMAN> backup current controlfile for standby format '/u01/backup/ForStandbyCTRL.bck';
5. Copy the Backup to the standby server using scp or cp.
6. Catalog the backup files as shown below:
RMAN> catalog start with '/u01/backup/';
using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/backup/
List of Files Unknown to the Database
=====================================
File Name: /u01/backup/ForStandby_5svmrkto_1_1
File Name: /u01/backup/ForStandby_5tvmrkto_1_1
File Name: /u01/backup/ForStandby_5uvmrkto_1_1
File Name: /u01/backup/ForStandby_5vvmrkto_1_1
File Name: /u01/backup/ForStandby_60vmrktp_1_1
File Name: /u01/backup/ForStandby_61vmrktp_1_1
File Name: /u01/backup/ForStandby_62vmrktp_1_1
File Name: /u01/backup/ForStandby_63vmrktp_1_1
File Name: /u01/backup/ForStandby_64vmrktp_1_1
File Name: /u01/backup/ForStandby_65vmrktp_1_1
File Name: /u01/backup/ForStandby_66vmrlmr_1_1
File Name: /u01/backup/ForStandby_67vmrlms_1_1
File Name: /u01/backup/ForStandby_68vmrlms_1_1
File Name: /u01/backup/ForStandby_69vmrlmt_1_1
File Name: /u01/backup/ForStandby_6avmrln5_1_1
File Name: /u01/backup/ForStandby_6bvmrlnj_1_1
File Name: /u01/backup/ForStandby_6cvmrlo4_1_1
File Name: /u01/backup/ForStandby_6dvmrlon_1_1
File Name: /u01/backup/ForStandby_6evmrlpd_1_1
File Name: /u01/backup/ForStandby_6fvmrlq3_1_1
File Name: /u01/backup/ForStandby_6gvmrlr8_1_1
File Name: /u01/backup/ForStandby_6hvmrlsk_1_1
File Name: /u01/backup/ForStandby_6ivmrlu0_1_1
File Name: /u01/backup/ForStandby_6jvmrlva_1_1
File Name: /u01/backup/ForStandby_6kvmrm0o_1_1
File Name: /u01/backup/ForStandby_6lvmrm2n_1_1
File Name: /u01/backup/ForStandbyCTRL.bck
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
List of Cataloged Files
=======================
File Name: /u01/backup/ForStandby_5svmrkto_1_1
File Name: /u01/backup/ForStandby_5tvmrkto_1_1
File Name: /u01/backup/ForStandby_5uvmrkto_1_1
File Name: /u01/backup/ForStandby_5vvmrkto_1_1
File Name: /u01/backup/ForStandby_60vmrktp_1_1
File Name: /u01/backup/ForStandby_61vmrktp_1_1
File Name: /u01/backup/ForStandby_62vmrktp_1_1
File Name: /u01/backup/ForStandby_63vmrktp_1_1
File Name: /u01/backup/ForStandby_64vmrktp_1_1
File Name: /u01/backup/ForStandby_65vmrktp_1_1
File Name: /u01/backup/ForStandby_66vmrlmr_1_1
File Name: /u01/backup/ForStandby_67vmrlms_1_1
File Name: /u01/backup/ForStandby_68vmrlms_1_1
File Name: /u01/backup/ForStandby_69vmrlmt_1_1
File Name: /u01/backup/ForStandby_6avmrln5_1_1
File Name: /u01/backup/ForStandby_6bvmrlnj_1_1
File Name: /u01/backup/ForStandby_6cvmrlo4_1_1
File Name: /u01/backup/ForStandby_6dvmrlon_1_1
File Name: /u01/backup/ForStandby_6evmrlpd_1_1
File Name: /u01/backup/ForStandby_6fvmrlq3_1_1
File Name: /u01/backup/ForStandby_6gvmrlr8_1_1
File Name: /u01/backup/ForStandby_6hvmrlsk_1_1
File Name: /u01/backup/ForStandby_6ivmrlu0_1_1
File Name: /u01/backup/ForStandby_6jvmrlva_1_1
File Name: /u01/backup/ForStandby_6kvmrm0o_1_1
File Name: /u01/backup/ForStandby_6lvmrm2n_1_1
File Name: /u01/backup/ForStandbyCTRL.bck
7. Recover the Database using recover database command with noredo option
RMAN> recover database noredo;
I faced the below error in this command:
channel ORA_DISK_1: reading from backup piece /u01/backup/ForStandby_66vmrlmr_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/10/2021 20:19:21
ORA-19870: error while restoring backup piece /u01/backup/ForStandby_66vmrlmr_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 10
DGMGRL> edit database mwprod set state='APPLY-OFF';
Succeeded.
DGMGRL> EXIT
8. Rerun the recover database command:
RMAN> recover database noredo;
Starting recover at 10-FEB-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00010: +DATA_PRD/PROD/DATA/wmmwsdata.274.975865083
destination for restore of datafile 00015: +DATA_MWPRD/MWPROD/DATAFILE/sysaux.279.990873289
destination for restore of datafile 00028: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.292.1017879367
destination for restore of datafile 00045: +DATA_MWPRD/MWPROD/DATAFILE/bpmmws.310.1023630077
destination for restore of datafile 00063: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.328.1030733483
destination for restore of datafile 00081: +DATA_MWPRD/MWPROD/DATAFILE/bpmis.346.1036576923
destination for restore of datafile 00098: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.364.1040497895
destination for restore of datafile 00114: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.380.1043660485
destination for restore of datafile 00130: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.396.1046338377
destination for restore of datafile 00148: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.414.1050082413
destination for restore of datafile 00164: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.430.1052014897
destination for restore of datafile 00181: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.447.1053768029
destination for restore of datafile 00197: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.463.1055320021
destination for restore of datafile 00213: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.479.1057249435
destination for restore of datafile 00230: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.496.1058435243
destination for restore of datafile 00248: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.514.1059662621
destination for restore of datafile 00264: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.530.1060506509
destination for restore of datafile 00280: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.546.1061628269
destination for restore of datafile 00296: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.567.1062408681
destination for restore of datafile 00312: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.583.1063167545
channel ORA_DISK_1: reading from backup piece /u01/backup/ForStandby_66vmrlmr_1_1
channel ORA_DISK_1: piece handle=/u01/backup/ForStandby_66vmrlmr_1_1 tag=FORSTANDBY
As it progresses you will see the restoration of files in the alert log
-----------------------------
Incremental restore complete of datafile 314 +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.585.1063275209
checkpoint is 11879707151160
last deallocation scn is 11879558491811
Incremental restore complete of datafile 298 +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.569.1062485497
checkpoint is 11879707151160
last deallocation scn is 11877341663346
Wed Feb 10 21:22:45 2021
-----------------------------
In case there are files added at primary, you need to restore the control file at standby first and then mount the database
RMAN> startup nomount;
RMAN> restore standby controlfile from '/u01/backup/ForStandbyCTRL.bck';
RMAN> alter database mount;
RMAN> CATALOG START WITH '/tmp/ForStandby';
The restore the datafiles using below script
run
{
set newname for datafile 322 to '+DATA_MWPRD';
set newname for datafile 323 to '+DATA_MWPRD';
set newname for datafile 324 to '+DATA_MWPRD';
set newname for datafile 325 to '+DATA_MWPRD';
set newname for datafile 326 to '+DATA_MWPRD';
set newname for datafile 327 to '+DATA_MWPRD';
set newname for datafile 328 to '+DATA_MWPRD';
set newname for datafile 329 to '+DATA_MWPRD';
set newname for datafile 330 to '+DATA_MWPRD';
set newname for datafile 331 to '+DATA_MWPRD';
set newname for datafile 332 to '+DATA_MWPRD';
set newname for datafile 333 to '+DATA_MWPRD';
restore datafile 322,323,324,325,326,327,328,329,330,331,332,333;
}
8) Rename the datafiles in new standby controlfile
Since the controlfile is restored from PRIMARY the datafile locations in STANDBY controlfile will be same as PRIMARY database, so catalog datafiles in STANDBY will do the necessary rename operations.
Perform the below step in STANDBY for each diskgroup where the datafile directory structure between primary and standby are different.
Once all files have been cataloged, switch the database to copy:
RMAN> CATALOG START WITH '+DATA_MWPRD/MWPROD/DATAFILE';
RMAN> SWITCH DATABASE TO COPY;
RMAN> RECOVER DATABASE NOREDO;
channel ORA_DISK_7: piece handle=/u01/backup/ForStandby_9gvmu25p_1_1 tag=FORSTANDBY
channel ORA_DISK_7: restored backup piece 1
channel ORA_DISK_7: restore complete, elapsed time: 00:40:03
channel ORA_DISK_13: piece handle=/u01/backup/ForStandby_9nvmu2e4_1_1 tag=FORSTANDBY
channel ORA_DISK_13: restored backup piece 1
channel ORA_DISK_13: restore complete, elapsed time: 00:50:27
Finished recover at 11-FEB-21
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
