Standby Database Refresh Using RMAN SCN Backup
Description :-
In this Blog we are going to see standby log sync failure and sync the standby database using incremental scn backup.
Refresh Using RMAN SCN Backup:
In case of any log sync gap has been occur in standby database or archivelogs received but not applied in standby database we can refresh the standby database using SCN based RMAN backup.
Overall Steps:
1. Check the archivelog GAP both primary and standby database. 2. Check the SCN number in standby. 3. Take RMAN incremental SCN backup in primary database. 4. Create Standby Control file in primary database. 5. Transfer Backup and control file to standby. 6. Replace the standby control file. 7. Catalog backuppiece on standby database. 8. cancel MRP process. 9. recover database using backup piece.
Step 1:- Check the archive log GAP for both primary and standby database.
Primary Database:
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
SYSTEM READ WRITE PRIMARY
SQL> select max(sequence#) from v$archived_log where archived=’YES’;
MAX(SEQUENCE#)
————–
34
Standby database:
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
SYSTEM MOUNTED PHYSICAL STANDBY
SQL> select max(sequence#) from v$archived_log where archived=’YES’;
MAX(SEQUENCE#)
————–
30
Step 2:- Check the SCN number in Standby.
SQL> select current_scn from v$database;
CURRENT_SCN
———–
2058645
Step 3:-Take RMAN incremental SCN backup in the primary database.
RMAN> backup incremental from SCN 2058645 database format '/u02/oracle/backup/DELL_BACKUP/database_%d_%u_%s'; Starting backup at 24-DEC-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/SYSTEM/datafile/o1_mf_system_jwbgjd16_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/SYSTEM/datafile/o1_mf_sysaux_jwbgktdq_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/SYSTEM/datafile/o1_mf_undotbs1_jwbglmk3_.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/SYSTEM/datafile/o1_mf_users_jwbglnno_.dbf channel ORA_DISK_1: starting piece 1 at 24-DEC-21 channel ORA_DISK_1: finished piece 1 at 24-DEC-21 piece handle=/u02/oracle/backup/DELL_BACKUP/database_SYSTEM_0d0hil0t_13 tag=TAG20211224T230253 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 24-DEC-21 channel ORA_DISK_1: finished piece 1 at 24-DEC-21 piece handle=/u02/oracle/backup/DELL_BACKUP/database_SYSTEM_0e0hil21_14 tag=TAG20211224T230253 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 24-DEC-21
RMAN> exit
Step 4:-Create Standby Control file in primary database
SQL> alter database create standby controlfile as ‘/u02/oracle/backup/DELL_BACKUP/std_control.ctl’;
Database altered.
Step 5:- Transfer Backup and control file to standby
[oracle@oracle DELL_BACKUP]$ scp * [email protected]:/u02/backup
[email protected]’s password:
database_SYSTEM_0d0hil0t_13 100% 74MB 37.0MB/s 00:02
database_SYSTEM_0e0hil21_14 100% 10MB 46.6MB/s 00:00
[oracle@oracle DELL_BACKUP]$ ll
total 97160
-rw-rw—-. 1 oracle oracle 77619200 Dec 24 23:03 database_SYSTEM_0d0hil0t_13
-rw-rw—-. 1 oracle oracle 10977280 Dec 24 23:03 database_SYSTEM_0e0hil21_14
-rw-rw—-. 1 oracle oracle 10895360 Dec 24 23:08 std_control.ctl
[oracle@oracle DELL_BACKUP]$ scp std_control.ctl [email protected]:/u02/backup
[email protected]’s password:
std_control.ctl 100% 10MB 59.1MB/s 00:00
[oracle@oracle DELL_BACKUP]$ scp std_control.ctl [email protected]:/u02/backup
[email protected]’s password:
std_control.ctl 100% 10MB 62.2MB/s 00:00
[oracle@oracle DELL_BACKUP]$
Step 6:- Replace the control file start the database in the mount stage.
[oracle@local MONITOR]$ mv std_control.ctl standby1.ctl
SQL> startup mount
ORACLE instance started.
Total System Global Area 1258290752 bytes
Fixed Size 8896064 bytes
Variable Size 754974720 bytes
Database Buffers 486539264 bytes
Redo Buffers 7880704 bytes
Database mounted.
SQL> exit
Step 7:- Catalog backup piece on standby database.
[oracle@local dbs]$ rman target /
Recovery Manager: Release 19.0.0.0.0 – Production on Fri Dec 24 12:45:33 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: SYSTEM (DBID=1671570, not open)
RMAN> catalog backuppiece ‘/u02/backup/database_SYSTEM_0d0hil0t_13’;
Starting implicit crosscheck backup at 24-DEC-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=55 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 24-DEC-21
Starting implicit crosscheck copy at 24-DEC-21
using channel ORA_DISK_1
Finished implicit crosscheck copy at 24-DEC-21
searching for all files in the recovery area
cataloging files…
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_7_jwbnc21n_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_6_jwbnc2jy_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_9_jwbnc3bo_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_10_jwbncmjz_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_11_jwbnq5ng_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_13_jwbnq5oh_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_12_jwbnq5ol_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_15_jwbnqc96_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_18_jwbtbchp_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_19_jwbtbcjq_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_20_jwbtbcyb_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_17_jwbtbd6d_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_16_jwbtbd6j_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_21_jwbtbd71_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_23_jwbtbdpg_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_24_jwbv467w_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_26_jwbv469p_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_25_jwbv46cg_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_27_jwbv4720_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/28.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/29.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_28_jwbvp1mg_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_29_jwbvp2ql_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_32_jwbvrx0o_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/30.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/31.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_33_jwbwmzy2_.arc
File Name: /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_34_jwbwn2p4_.arc
channel ORA_DISK_1: cataloged backup piece
backup piece handle=/u02/backup/database_SYSTEM_0d0hil0t_13 RECID=8 STAMP=1092142036
RMAN> catalog backuppiece ‘/u02/backup/database_SYSTEM_0e0hil21_14’;
channel ORA_DISK_1: cataloged backup piece
backup piece handle=/u02/backup/database_SYSTEM_0e0hil21_14 RECID=9 STAMP=1092142060
RMAN> list backup ; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 1 Full 10.20M DISK 00:00:00 24-DEC-21 BP Key: 1 Status: EXPIRED Compressed: NO Tag: TAG20211224T092140 Piece Name: /u01/app/oracle/fast_recovery_area/SYSTEM/autobackup/2021_12_24/o1_mf_s_1092129700_jwbjydcj_.bkp SPFILE Included: Modification time: 24-DEC-21 SPFILE db_unique_name: SYSTEM Control File Included: Ckp SCN: 2032248 Ckp time: 24-DEC-21 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 2 173.98M DISK 00:00:02 24-DEC-21 BP Key: 2 Status: EXPIRED Compressed: NO Tag: TAG20211224T115303 Piece Name: /u01/app/oracle/fast_recovery_area/SYSTEM/backupset/2021_12_24/o1_mf_annnn_TAG20211224T115303_jwbst7c2_.bkp List of Archived Logs in backup set 2 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 5 2008822 24-DEC-21 2032572 24-DEC-21 1 6 2032572 24-DEC-21 2037484 24-DEC-21 1 7 2037484 24-DEC-21 2037797 24-DEC-21 1 8 2037797 24-DEC-21 2040754 24-DEC-21 1 9 2040754 24-DEC-21 2040789 24-DEC-21 1 10 2040789 24-DEC-21 2040796 24-DEC-21 1 11 2040796 24-DEC-21 2041066 24-DEC-21 1 12 2041066 24-DEC-21 2041524 24-DEC-21 1 13 2041524 24-DEC-21 2041533 24-DEC-21 1 14 2041533 24-DEC-21 2041659 24-DEC-21 1 15 2041659 24-DEC-21 2042175 24-DEC-21 1 16 2042175 24-DEC-21 2054365 24-DEC-21 1 17 2054365 24-DEC-21 2054426 24-DEC-21 1 18 2054426 24-DEC-21 2054437 24-DEC-21 1 19 2054437 24-DEC-21 2054442 24-DEC-21 1 20 2054442 24-DEC-21 2054630 24-DEC-21 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 3 Incr 0 1.14G DISK 00:00:48 24-DEC-21 BP Key: 3 Status: EXPIRED Compressed: NO Tag: TAG20211224T115310 Piece Name: /u01/app/oracle/fast_recovery_area/SYSTEM/backupset/2021_12_24/o1_mf_nnnd0_TAG20211224T115310_jwbstgsy_.bkp List of Datafiles in backup set 3 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 1 0 Incr 2054665 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_system_jwbgjd16_.dbf 3 0 Incr 2054665 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_sysaux_jwbgktdq_.dbf 4 0 Incr 2054665 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_undotbs1_jwbglmk3_.dbf 7 0 Incr 2054665 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_users_jwbglnno_.dbf
BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 4 26.50K DISK 00:00:00 24-DEC-21 BP Key: 4 Status: EXPIRED Compressed: NO Tag: TAG20211224T115407 Piece Name: /u01/app/oracle/fast_recovery_area/SYSTEM/backupset/2021_12_24/o1_mf_annnn_TAG20211224T115407_jwbsw7qd_.bkp List of Archived Logs in backup set 4 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 21 2054630 24-DEC-21 2054749 24-DEC-21 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 5 Full 10.20M DISK 00:00:01 24-DEC-21 BP Key: 5 Status: EXPIRED Compressed: NO Tag: TAG20211224T115408 Piece Name: /u01/app/oracle/fast_recovery_area/SYSTEM/autobackup/2021_12_24/o1_mf_s_1092138848_jwbsw9gv_.bkp SPFILE Included: Modification time: 24-DEC-21 SPFILE db_unique_name: SYSTEM Control File Included: Ckp SCN: 2054766 Ckp time: 24-DEC-21 BS Key Type LV Size ------- ---- -- ---------- 6 Incr 74.02M List of Datafiles in backup set 6 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 1 Incr 2100466 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_system_jwbgjd16_.dbf 3 Incr 2100466 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_sysaux_jwbgktdq_.dbf 4 Incr 2100466 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_undotbs1_jwbglmk3_.dbf 7 Incr 2100466 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_users_jwbglnno_.dbf Backup Set Copy #1 of backup set 6 Device Type Elapsed Time Completion Time Compressed Tag ----------- ------------ --------------- ---------- --- DISK 00:00:28 24-DEC-21 NO TAG20211224T230253 List of Backup Pieces for backup set 6 Copy #1 BP Key Pc# Status Piece Name ------- --- ----------- ---------- 6 1 EXPIRED /u02/oracle/backup/DELL_BACKUP/database_SYSTEM_0d0hil0t_13 Backup Set Copy #2 of backup set 6 Device Type Elapsed Time Completion Time Compressed Tag ----------- ------------ --------------- ---------- --- DISK 00:00:28 24-DEC-21 NO TAG20211224T230253 List of Backup Pieces for backup set 6 Copy #2 BP Key Pc# Status Piece Name ------- --- ----------- ---------- 8 1 AVAILABLE /u02/backup/database_SYSTEM_0d0hil0t_13 BS Key Type LV Size ------- ---- -- ---------- 7 Incr 10.45M Control File Included: Ckp SCN: 2100521 Ckp time: 24-DEC-21 Backup Set Copy #1 of backup set 7 Device Type Elapsed Time Completion Time Compressed Tag ----------- ------------ --------------- ---------- --- DISK 00:00:01 24-DEC-21 NO TAG20211224T230253 List of Backup Pieces for backup set 7 Copy #1 BP Key Pc# Status Piece Name ------- --- ----------- ---------- 7 1 EXPIRED /u02/oracle/backup/DELL_BACKUP/database_SYSTEM_0e0hil21_14 Backup Set Copy #2 of backup set 7 Device Type Elapsed Time Completion Time Compressed Tag ----------- ------------ --------------- ---------- --- DISK 00:00:01 24-DEC-21 NO TAG20211224T230253 List of Backup Pieces for backup set 7 Copy #2 BP Key Pc# Status Piece Name ------- --- ----------- ---------- 9 1 AVAILABLE /u02/backup/database_SYSTEM_0e0hil21_14
Step 8:- Cancel MRP Process.
SQL> recover managed standby database cancel; Media recovery complete.
Step 9:- Recover standby database using the backup piece.
RMAN> RECOVER DATABASE; Starting recover at 24-DEC-21 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=38 device type=DISK 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 00001: /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_system_jwbgjd16_.dbf destination for restore of datafile 00003: /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_sysaux_jwbgktdq_.dbf destination for restore of datafile 00004: /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_undotbs1_jwbglmk3_.dbf destination for restore of datafile 00007: /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_users_jwbglnno_.dbf channel ORA_DISK_1: reading from backup piece /u02/backup/database_SYSTEM_0d0hil0t_13 channel ORA_DISK_1: piece handle=/u02/backup/database_SYSTEM_0d0hil0t_13 tag=TAG20211224T230253 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 starting media recovery archived log for thread 1 with sequence 33 is already on disk as file /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_33_jwbwmzy2_.arc archived log for thread 1 with sequence 34 is already on disk as file /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_34_jwbwn2p4_.arc archived log for thread 1 with sequence 35 is already on disk as file /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_35_jwbwwc6o_.arc archived log file name=/u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_33_jwbwmzy2_.arc thread=1 sequence=33 archived log file name=/u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_34_jwbwn2p4_.arc thread=1 sequence=34 archived log file name=/u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_35_jwbwwc6o_.arc thread=1 sequence=35 media recovery complete, elapsed time: 00:00:02 Finished recover at 24-DEC-21 RMAN> exit
Step 10:- enable the MRP process.
SQL> alter database recover managed standby database disconnect from session ;
Step 11:- Defer and enable dest 2 in primary database
SQL> alter system set log_archive_dest_state_2=defer;
System altered.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
Step 12:- Check the log sync primary and standby.
Primary side:
SQL> select max(sequence#) from v$archived_log where archived=’YES’;
MAX(SEQUENCE#)
————–
37
Standby side:
SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; 2 3 4
Thread Last Sequence Received Last Sequence Applied Difference
1 37 37 0
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
