How to open standby in Read-Write mode with ArchiveLog GAP’s

How to open standby in Read-Write mode with Archive Log GAP’s

There was an interesting post in OTN forums, OP is trying to Open Standby database in Read-Write Mode and There is 99 archive logs GAP between primary and standby site, Hence to fulfill requirement to open standby in Read-Write environment OP tried to perform fail over using “Alter database activate standby database” and encountered with below errors.

 ALTER DATABASE ACTIVATE STANDBY DATABASE
 ALTER DATABASE ACTIVATE [PHYSICAL] STANDBY DATABASE (work)
 Begin: Standby Redo Logfile archival
 ...................
 Fetching gap sequence in thread 1, gap sequence 1174115-1174214
 Standby crash recovery need archive log for thread 1 sequence 1174115 to continue.
 Please verify that primary database is transporting redo logs to the standby database.
 Wait timeout: thread 1 sequence 1174115
 Standby crash recovery aborted due to error 16016.
 Errors in file /oracle/diag/rdbms/b2resnew/work/trace/work_ora_36176058.trc:
 ORA-16016: archived log for thread 1 sequence# 1174115 unavailable
 Recovery interrupted!
 Completed standby crash recovery.

Fail-over terminated with ORA-16016 unavailable Archive Log File.

16016, 00000, "archived log for thread %s sequence# %s unavailable"
 // *Cause: Redo apply timed out waiting for the requested archived log
 // file.
 // *Action: Verify that the primary database is still archiving redo data to
 // the standby database and reissue the RECOVER STANDBY DATABASE
 // command.

Error code is referring, The requested archived log is timed out and it needs the archive log sequence 1174115 to archived and to apply that specific archived log on standby database. But OP doesn’t have either backup of archive log and that archive doesn’t exist in either primary or standby database.

Now lets see what other options been tried to open standby in Read-Write mode.

 SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
 ALTER DATABASE ACTIVATE STANDBY DATABASE
 *
 ERROR at line 1:
 ORA-01152: file 1 was not restored from a sufficiently old backup
 ORA-01110: data file 1: '/oradata/ctrl/system01.dbf'
 SQL>

 SQL> RECOVER MANAGED STANDBY DATABASE FINISH
 ORA-10877: error signaled in parallel recovery slave
 SQL>

Now what are the options to open Standby in Read-Write mode?

Of course we can do FAKE recover using below recovery scenario as shown below.

 SQL> recover standby database; 
 ORA-00279: change 4810910 generated at 11/17/2012 22:14:13 needed for thread 1 
 ORA-00289: suggestion : /u02/app/oracle/flash_recovery_area/standby/archivelog/2012_11_17/o1_mf_1_847_%u_.arc  
 ORA-00280: change 4810910 for thread 1 is in sequence #847   
 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}   
 cancel  
 Media recovery cancelled. 
 SQL>

By above procedure chances to perform Fake recovery success ratio is very less and this is been issues solved by OP and it is much appreciated with his plan and am much impressed with this thought and like to share this, may be it can useful for me in future 😉

OP changed control file to primary controlfile type and open databbase with resetlogs as below

 CREATE CONTROLFILE REUSE DATABASE "WORK" RESETLOGS FORCE LOGGING ARCHIVELOG
 +++select open_mode,database_role from v$database;+
 +++OPEN_MODE DATABASE_ROLE+
 READ WRITE PRIMARY
 SQL> 

Conclusion:  It is really a great work around if you want to open database with Read-Write mode, if in case of archive log GAP’s on standby database.

–Happy Reading–