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.
In oracle 12c , We can use the rolling forward method to fix it . See Full article
Here i have created a setup where archive is missing before it is being applied in the physical standby.
1. [Primary] Disable the shipping: PRIMARY_SQL> Alter system set log_archive_dest_state_2=defer scope =both; System altered. 2. [Primary] Creating archives by switching logfile. PRIMARY_SQL > alter system switch logfile; System altered. PRIMARY_SQL > alter system switch logfile; System altered. 3. [Primary] Remove the the last two logs from archive.< as they haven’t been shipped yet. 4. [Primary] Enable shipping again: PRIMARY_SQL > Alter system set log_archive_dest_state_2=enable scope=both; System altered.
As we have removed the new archives from primary, the standby database can’t proceed with the
recovery without these logs.
Solution:
Get scn of both primary and standby:
1. [Primary] Find current_scn from primary. PRIMARY_SQL > select current_scn from v$database; CURRENT_SCN ----------- 2791422 2. [Standby] Find current_scn from standby. STANDBY_SQL > select current_scn from v$database; CURRENT_SCN ----------- 2791087 -
2791087- Take a note of this scn, as we need to recover from this scn
3. [Standby] Stop the managed standby apply process: STANDBY_SQL > alter database recover managed standby database cancel; Database altered. 4. [Standby] Shutdown the standby database STANDBY_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 .
RMAN> run {
allocate channel c1 type disk format '/home/oracle/raj/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 20-SEP-13
backup will be obsolete on date 27-SEP-13
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=/u03/TEST/oradata/user01.dbf
input datafile file number=00001 name=/u03/TEST/oradata/system.dbf
input datafile file number=00002 name=/u03/TEST/oradata/sysaux.dbf
input datafile file number=00003 name=/u03/TEST/oradata/undotbs1.dbf
channel c1: starting piece 1 at 20-SEP-13
channel c1: finished piece 1 at 20-SEP-13
piece handle=/home/oracle/raj/rman_bkup0aokac2j_1_1.rmb tag=TAG20130920T080539
comment=NONE
channel c1: backup set complete, elapsed time: 00:00:45
backup will be obsolete on date 27-SEP-13
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 20-SEP-13
channel c1: finished piece 1 at 20-SEP-13
piece handle=/home/oracle/raj/rman_bkup0bokac40_1_1.rmb tag=TAG20130920T080539
comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-SEP-13
released channel: c1
6. [Primary] Create a new standby controlfile in primary
PRIMARY_SQL > alter database create standby controlfile as '/home/oracle/raj/control02.ctl'; Database altered.
7. Copy the rman backup file and new standby controlfile to standby database.
In standby the files were copied to /home/oracle/raj/.
8. [Standby] Start standby in startup nomount
STANDBY_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.
STANDBY_SQL > show parameter control_files NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /u04/TEST/oradata/control02.ctl
10. [Standby] Replace the controlfile in standby side with the one you just created in primary.
cp /home/oracle/raj/control02.ctl /u04/TEST/oradata/control02.ctl
11. [Standby] Mount standby database
STANDBY_SQL > alter database mount standby database; Database altered.
12. [Standby] Catloging rman files.
RMAN does not know about these files yet; so you must let it know – by a process called cataloging.
Catalog these
STANDBY]$ rman target / Recovery Manager: Release 11.2.0.2.0 - Production on Fri Sep 20 09:14:08 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: TESTER (DBID=3502897675, not open) RMAN> catalog start with '/home/oracle/raj'; 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/raj/control02.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/raj/control02.ctl
13. [Standby] Recover the database
RMAN> recover database; Starting recover at 20-SEP-13 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=76 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: /u04/TEST/oradata/system.dbf destination for restore of datafile 00002: /u04/TEST/oradata/sysaux.dbf9 destination for restore of datafile 00003: /u04/TEST/oradata/undotbs1.dbf destination for restore of datafile 00004: /u04/TEST/oradata/user01.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/raj/rman_bkup0aokac2j_1_1.rmb RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 09/20/2013 09:16:41 ORA-19870: error while restoring backup piece /home/oracle/raj/rman_bkup0aokac2j_1_1.rmb ORA-19573: cannot obtain exclusive enqueue for datafile 4
<< If above errors are coming, then cancel the recovery again >>>
STANDBY_SQL > alter database recover managed standby database cancel; Database altered. Again follow the same process i.e recovering the database RMAN> recover database; Starting recover at 20-SEP-13 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: /u04/TEST/oradata/system.dbf destination for restore of datafile 00002: /u04/TEST/oradata/sysaux.dbf destination for restore of datafile 00003: /u04/TEST/oradata/undotbs1.dbf destination for restore of datafile 00004: /u04/TEST/oradata/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 /u04/TEST/oradata/REDO_STDBY/1_250_824551947.arc archived log file name=/u04/TEST/oradata/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: ===========================================================10 RMAN-03002: failure of recover command at 09/20/2013 09:28:04 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 251 and starting SCN of 2796410
This error are expected errors.This happens because we have come to the last of the archived logs. The expected archived log with sequence# 8008 has not been generated yet.
start MRP
STANDBY_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. Lets check that.
Now check the archive status in both the databases.
1. [Primary] Find current_scn from primary. PRIMARY_SQL > select current_scn from v$database; CURRENT_SCN ----------- 2791422 2. [Standby] Find current_scn from standby. STANDBY_SQL > select current_scn from v$database; CURRENT_SCN ----------- 279142211
3. [Standby] Check the processes running on standby.
STANDBY_SQL > select sequence#,process,status from v$managed_standby; SEQUENCE# PROCESS STATUS ---------- --------- ------------ 258 ARCH CLOSING 0 ARCH CONNECTED 259 RFS IDLE 259 MRP0 WAIT_FOR_LOG RFS IDLE
4. [Primary] switch logfiles to confirm whether new archives are getting applied or not
PRIMARY_SQL > alter system switch logfile; System altered. PRIMARY_SQL > archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u03/TEST/oradata/REDO_STDBY/ Oldest online log sequence 257 Next log sequence to archive 259 Current log sequence 259 STANDBY_SQL > archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u04/TEST/oradata/REDO_STDBY/ Oldest online log sequence 258 Next log sequence to archive 0 Current log sequence 259
Excellent,Thank you.
Fantastic article. Thank you.
Hi ,
while doing recovery at standby if SCN/ apply/recovery process in not progressig wjat would the issue
Thanks
Shankar
1. restart the standby database
2. Check password file at DR is in sync with primary database
3. Restart the MRP process at DR
4. Reset log_archive_dest_state_x – disable / enable log shipping from primary to DR
Thanks a ton, its really helpful.
if you don’t have space we can all using service also instead of taking backup
The article explains the method used in 11g. But the recover using service is available from 12c onward. For that i have already the link in the article.