Normally in DR setup, the archives from primary shipped to standby and applied there. Suppose some of the archives hasn’t been shipped to secondary .But due to intermittence error or human error, the archives has been deleted from primary. So without those archives, standby can’t be in sync with primary.
So when this situation occurs, beyond understanding why an archive log(s) went missing and addressing the root cause of this, there are basically 3 options available to rectify the issue:
1. Restore the missing archive log from a backup onto the primary server. If you are using Standby then it will be able to pick up this log and transfer it to the DR site to apply to the standby database. 2. Rebuild the standby database. With Standby the standby database can be rebuilt using the CSD (create standby database) 3. Resync the standby database using an incremental RMAN backup from the primary database. This does not require a full rebuild of the standby database and is, therefore, considerably more efficient.
The steps to resync the standby database using an incremental RMAN backup from the primary database are as follows:
Get SCN of both primary and standby:
1 2 3 4 5 6 7 8 9 10 11 12 13 | 1. [Primary] Find current_scn from primary. SQL > select current_scn from v$database; CURRENT_SCN ----------- 9898512 2. [Standby] Find current_scn from standby. SQL > select current_scn from v$database; CURRENT_SCN ----------- 9898121 |
9898121- Take a note of this scn, as we need to recover from this scn
3. [Standby] Stop the managed standby apply process
1 2 | SQL > alter database recover managed standby database cancel; Database altered. |
4. [Standby] Shutdown the standby database
1 2 3 | SQL > shutdown immediate; Database closed. Database dismounted. |
5. [Primary] take an incremental backup in primary from the scn number which we got in step 2 .
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | RMAN> run { allocate channel c1 type disk format '/atg/backup/rman_bkup%U.rmb'; backup incremental from scn 2791087 database; } 2> 3> 4> using target database control file instead of recovery catalog allocated channel: c1 channel c1: SID=90 device type=DISK Starting backup at 16-AUG-21 backup will be obsolete on date 16-AUG-21 archived logs will not be kept or backed up channel c1: starting full datafile backup set channel c1: specifying datafile(s) in backup set input datafile file number=00004 name=/u01/app/oracle/oradata/user01.dbf input datafile file number=00001 name=/u01/app/oracle/oradata/system.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/sysaux.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/undotbs1.dbf channel c1: starting piece 1 at 16-AUG-21 channel c1: finished piece 1 at 16-AUG-21 piece handle=/atg/backup/rman_bkup0aokac2j_1_1.rmb tag=TAG98372729T098371 comment=NONE channel c1: backup set complete, elapsed time: 00:00:22 backup will be obsolete on date 16-AUG-21 archived logs will not be kept or backed up channel c1: starting full datafile backup set channel c1: specifying datafile(s) in backup set including current control file in backup set channel c1: starting piece 1 at 16-AUG-21 channel c1: finished piece 1 at 16-AUG-21 piece handle=/atg/backup/rman_bkup0bokac40_1_1.rmb tag=TAG98372729T098371 comment=NONE channel c1: backup set complete, elapsed time: 00:00:01 Finished backup at 16-AUG-21 released channel: c1 |
6. [Primary] Create a new standby controlfile in primary
1 2 | SQL > alter database create standby controlfile as '/home/oracle/control01.ctl'; Database altered. |
7. Copy the rman backup file and new standby controlfile to standby database.
8. [Standby] Start standby in startup nomount
1 2 3 4 5 6 7 | SQL > startup nomount; ORACLE instance started. Total System Global Area 1068937216 bytes Fixed Size 2233336 bytes Variable Size 813698056 bytes Database Buffers 247463936 bytes Redo Buffers 5541888 bytes |
9. [Standby] Find the location of controlfile in standby.
1 2 3 4 | SQL > show parameter control_files NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /u01/app/oracle/oradata/sha/control01.ctl |
10. [Standby] Replace the controlfile in standby side with the one you just created in primary.
cp /home/oracle/control01.ctl /u01/app/oracle/oradata/sha/control01.ctl
11. [Standby] Mount standby database
1 2 | SQL > alter database mount standby database; Database altered. |
12. [Standby] Catalog RMAN Files
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | rman target / Recovery Manager: Release 11.2.0.2.0 - Production Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: TESTER (DBID=8763981253, not open) RMAN> catalog start with '/home/oracle'; using target database control file instead of recovery catalog searching for all files that match the pattern /home/oracle/raj List of Files Unknown to the Database ===================================== File Name: /home/oracle/control01.ctl Do you really want to catalog the above files (enter YES or NO)? yes cataloging files... cataloging done List of Cataloged Files ======================= File Name: /home/oracle/control01.ctl |
13. [Standby] Recover database.
[Standby] Stop the managed standby apply process
1 2 | SQL > alter database recover managed standby database cancel; Database altered. |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | RMAN> recover database; Starting recover at 16-AUG-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 00001: /u01/app/oracle/oradata/sha/system.dbf destination for restore of datafile 00002: /u01/app/oracle/oradata/sha/sysaux.dbf destination for restore of datafile 00003: /u01/app/oracle/oradata/sha/undotbs1.dbf destination for restore of datafile 00004: /u01/app/oracle/oradata/sha/user01.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/raj/rman_bkup0aokac2j_1_1.rmb channel ORA_DISK_1: piece handle=/home/oracle/raj/rman_bkup0aokac2j_1_1.rmb tag=TAG20130920T080539 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 starting media recovery archived log for thread 1 with sequence 250 is already on disk as file /u01/app/oracle/oradata/sha/REDO_STDBY/1_250_824551947.arc archived log file name=/u01/app/oracle/oradata/sha/REDO_STDBY/1_250_824551947.arc thread=1 sequence=250 unable to find archived log archived log thread=1 sequence=251 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== |
This error are expected errors. This happens because we have come to the last of the archived logs.
14. Start MRP in Standby .
1 2 | SQL > alter database recover managed standby database disconnect from session; Database altered. |
Now the standby is completely in sync with primary and recovery is running fine.
15. Check SCN number in both primary & Standby.
1 2 3 4 5 6 7 8 9 10 11 12 13 | 1. [Primary] Find current_scn from primary. SQL > select current_scn from v$database; CURRENT_SCN ----------- 9898512 2. [Standby] Find current_scn from standby. SQL > select current_scn from v$database; CURRENT_SCN ----------- 9898512 |
16. [Standby] Check the processes running on standby.
1 2 3 4 5 6 7 8 | SQL > select sequence#,process,status from v$managed_standby; SEQUENCE# PROCESS STATUS ---------- --------- ------------ 341 ARCH CLOSING 0 ARCH CONNECTED 342 RFS IDLE 342 MRP0 WAIT_FOR_LOG |
Notes:
– Service “primary_tns” should exist in the standby server tnsnames.ora file pointing to the primary DB.
– Including “USING COMPRESSED BACKUP” will be wise, to send the packets in a compressed format to save the bandwidth and transfer the packets faster through the network.
1 2 3 4 5 6 | 18c ONWARDS: With one command, you can get the job done: On standby DB: [From mount mode] RMAN> RECOVER STANDBY DATABASE FROM SERVICE primary_tns USING COMPRESSED BACKUP; |