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';
