How to recover standby database from a missing archive log gap

 02-08-2022

#how to recover standby database from a missing archive log gap

#Hossam Note

#if there is a Gap on DG there is 2 scenarios

#1- missing archivelogs on standby but it exist on prod this can be fixed by using rsync and catalog start with 

#2-missing archivelogs on standby but its NOT exist on prod this can be fixed by below 

https://techgoeasy.com/recovering-standby-database-fro/

#On the standby database check the current SCN

SQL>  col current_SCN for 99999999999999999999;

SQL> select current_scn from v$database;

          CURRENT_SCN

---------------------

      111050674405375

#On the primary database create the needed incremental backup from the above SCN

rman target /

RMAN> 

{

allocate channel c1 type disk;

BACKUP INCREMENTAL FROM SCN 111050674405375 DATABASE FORMAT '/tmp/inc_standby_%U';

}

#Cancel managed recovery at the standby database

SQL> alter database recover managed standby database cancel;

Database altered

#scp the backup files to standby server to /tmp folder.

scp -rp oraprod@

#Apply the Incremental Backup to the Standby Database

rman target /

RMAN>RECOVER DATABASE NOREDO;

#Put the standby database back to managed recovery mode.

qlplus "/ as sysdba"

SQL>recover managed standby database disconnect;

Media recovery complete.

#On the primary create new standby controlfile

alter database create standby controlfile as '/tmp/standby01.ctl';

#Copy the standby control file to the standby site. Shutdown the standby database and replace the standby control files and restart the standby database in the managed recovery mode using the below command

RMAN> SHUTDOWN IMMEDIATE ;

RMAN> STARTUP NOMOUNT;

RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/standby01.ctl';

#Mount the standby

RMAN> ALTER DATABASE MOUNT;

#On STANDBY database, clear all standby redo log groups:

select GROUP# from v$logfile where TYPE='STANDBY' group by GROUP#;

    GROUP#

----------

        10

        11

        12

        13

ALTER DATABASE CLEAR LOGFILE GROUP 10;

ALTER DATABASE CLEAR LOGFILE GROUP 11;

ALTER DATABASE CLEAR LOGFILE GROUP 12;

ALTER DATABASE CLEAR LOGFILE GROUP 13;

#if dont clear restart standby db

#Now you can start the MRP

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

#if needed 

cd /oradata/proddata19/LOCALERP/archive/ 

rsync -avzhe ssh --progress oraprod@10.143.250.164:/oradata/proddata19/LOCALERP/archive/ .

rman> catalog start with '/oradata/proddata19/LOCALERP/archive';