Skip to main content

Rolling Forward a Physical Standby Database using RMAN Incremental Backup

  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;


Comments

Post a Comment

Popular posts from this blog

Warning Message When Creating A Keystore

  Warning Message When Creating A Keystore When we try to list the contents of the keystore, using the below command, keytool -list -v -keystore adkeystore.dat Enter keystore password: Keystore type: JKS Keystore provider: SUN Your keystore contains 1 entry Alias name: prod_apps002 Creation date: Jan 26, 2012 Entry type: PrivateKeyEntry Certificate chain length: 1 Certificate[1]: Owner: CN=PROD_apps002, OU=apps, O=apps002, C=US Issuer: CN=PROD_apps002, OU=apps, O=apps002, C=US Serial number: 4f208085 Valid from: Thu Jan 26 01:21:57 AST 2012 until: Tue Jan 16 01:21:57 AST 2052 Certificate fingerprints:          MD5:  XX: XX XX XX          SHA1:  XX: XX XX XX          SHA256:  XX: XX XX XX Signature algorithm name: SHA1withDSA Subject Public Key Algorithm: 1024-bit DSA key Version: 3 ******************************************* ******************************************* Warning: The JKS keystore...
Post a Comment
Read more

CLEARING an ADOP patching session

In some case adop prepare phase fails due to some issues and if you wish to apply any patch in hotpatch mode, it will fail with below errors [STATEMENT] There is already a session which is incomplete. Details are: [STATEMENT]     Session Id: 2 [STATEMENT]     Prepare phase status: R [STATEMENT]     Apply phase status: N [STATEMENT]     Cutover  phase status: N [STATEMENT]     Abort phase status: N [STATEMENT]     Session status: F [ERROR]     Cannot apply hotpatch as another online patching cycle is going on [ERROR]     Unrecoverable error occured. Exiting the current session. [STATEMENT] [START 2019/10/22 16:13:00] Unlocking sessions table [STATEMENT] [END   2019/10/22 16:13:00] Unlocking sessions table [STATEMENT] Log file: /adop_20191022_161221.log [STATEMENT] [START 2019/10/22 16:13:02] Unlocking sessions table [STATEMENT] [E...
4 comments
Read more

XX_XXXXXXX is not a valid responsibility for the current user. Please contact your System Administrator.

  XX_XXXXXXX is not a valid responsibility for the current user. Please contact your System Administrator. Issue : When user logs into EBS, he gets the following Error :  xx_XXXXX  is not a valid responsibility for the current user. Please contact your System Administrator. Solution: 1. Check if the responsibility is end dated.  Extend the end-date if required and try to login again.  2. Check the profile option  Applications Start Page for the user ( User Level). If there is any value defined, remove it and save it. Try to login again and it worked in our case.  Before: After:
Post a Comment
Read more