Document Control
Date Author Version
16/02/2023 Ahmed Sayed Ahmed
© copyright 2023, all rights reserved.
Gap Solution
***********Step-1: Check the SCN of Standby ( Data guard ) database and Find the lowest
SCN from the following Queries.
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
6191944894628
**********Step-2: Stop the Dataguard MRP ( Apply ) Process and shutdown Standby
database.
SQL> alter database recover managed standby database cancel;
SQL> shutdown immediate;
**********Step-3: Take an Incremental Backup from Production database using the lowest
SCN as follows.
run
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
© copyright 2023, all rights reserved.
backup as compressed backupset incremental from scn 6191944894628 DATABASE FORMAT
'/backup/Standbybkp_%U' tag 'STANDBYbkp';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
**********Step-4: Transfer the Incremental backup to the Standby database from Production
server.
*********Step-5: Create a new Standby Controlfile on Production database and transfer it to
Standby database.
SQL> alter database create standby controlfile as '/oracle/stand01.ctl';
*********Step-6: Startup Standby database in nomount mode.
SQL> startup nomount
********Step-7: Restore new standby controlfile on Standby database.
RMAN> restore standby controlfile from '/oracle/backup/standb01.ctl';
© copyright 2023, all rights reserved.
********Step-8: mount the standby database.
SQL> alter database mount standby database;
********Step-9: Catalog the new Incremental backups on Standby Server as follows.
RMAN> catalog start with '/oracle/backup/';
List of Cataloged Files
=======================
File Name: /oracle/backup/increment.rman
**********Step-10: start Recover database on Standby database.
RMAN> recover database;
**********error i faced here
RMAN> recover database noredo;
Starting recover at 24-JAN-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=227 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
© copyright 2023, all rights reserved.
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/24/2018 11:25:30
RMAN-06094: datafile 417 must be restored
RMAN> switch database to copy;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 01/24/2018 11:40:54
RMAN-06571: datafile 1 does not have recoverable copy
solution :
backup it and restore
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
ORA MOUNTED
SQL>
SQL> select name,database_role,open_mode from v$database;
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- --------------------
ORA PHYSICAL STANDBY MOUNTE
© copyright 2023, all rights reserved.
You can query losing file using the next sql
SQL> col name for a45
SQL> set linesize 300
SQL> select file#,name,bytes,status,to_date(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss')
"Create",to_date(ONLINE_TIME,'yyyy-mm-dd hh24:mi:ss') "Online" from v$datafile where
file#=19;
1- Shut immediate
2- Startup nomount
3- Alter database mount # note you must mount database using old control file because
the new have a catalog and datafile you will backup it doesn’t exist in the catalog.
4- Restore datafile
5- After that shut immediate again
6- Alter database using new control file
7- Repeat from step 5
step 10 ***********
RMAN> run
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
RECOVER DATABASE NOREDO;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
© copyright 2023, all rights reserved.
*********************************monitor backup and recovery
select
sid,
start_time,
totalwork
sofar,
(sofar/totalwork) * 100 pct_done
from
v$session_longops
where
totalwork > sofar
AND
opname NOT LIKE '%aggregate%'
AND
opname like 'RMAN%';
******************************************************************************
***********
Drop and recreate standby redo logs
ACTION PLAN
I. ON STANDBY
=========
© copyright 2023, all rights reserved.
1) Cancel managed recovery
SQL > alter database recover managed standby database cancel;
2) Make standby_file_management=manual
SQL > alter system set standby_file_management=manual scope=both sid='*';
3) Drop existing standby redo logs and recreate them.
a. Determine the standby redo logs using
select group#, thread#, status, bytes from v$standby_log;
b. Drop the group which were returned above. Assuming the above query returned group
5,6,7,8.
SQL > Alter database drop logfile group 5;
SQL > Alter database drop logfile group 6;
SQL > Alter database drop logfile group 7;
SQL > Alter database drop logfile group 8;
c. Recreate standby redo logs
Make sure below rules are followed by create standby redo logs.
i. Do not multiplex them.
ii. The number of Standby redo logs will be 1 more than the number of online redo logs on
the primary.
iii. The size of standby redo log should be same as that of the online redo log on primary.
© copyright 2023, all rights reserved.
alter database add standby logfile thread 1 group 5
'/u01/app/oracle/product/oradata/standby_redo01.log' size 524288000;
alter database add standby logfile thread 1 group 6
'/u01/app/oracle/product/oradata/standby_redo02.log' size 524288000;
alter database add standby logfile thread 1 group 7
'/u01/app/oracle/product/oradata/standby_redo03.log' size 524288000;
alter database add standby logfile thread 1 group 8
'/u01/app/oracle/product/oradata/standby_redo04.log' size 524288000;
alter database add standby logfile thread 1 group 9
'/u01/app/oracle/product/oradata/standby_redo05.log' size 524288000;
4) Set the Standby file management back to auto.
SQL > alter system set standby_file_management=auto scope=both sid='*';
5) Restart managed recovery
SQL > alter database recover managed standby database disconnect from session;
On Primary
===========
Defer and enable redo transport
SQL > alter system set log_archive_Dest_state_2=defer;
SQL > alter system set log_archive_Dest_state_2=enable;
*******************************************************
if there is issue in transfering archives
check
© copyright 2023, all rights reserved.
1- network and listener
2- tnsnames (services)
3- parameter file (dg_config, log_archive_config, log_archive_dest_n,log_archive_dest_stat)
4- db_name parameter
5- destination on standby
*********************************************************
Step-11: Now you can start the MRP Process to apply new logs.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
DISCONNECT FROM SESSION;
References:
https://www.dba-career.com/2018/04/failed-to-request-gap-sequence.html
(Doc ID 2822301.1)
https://doyensys.com/blogs/how-to-resolve-gaps-in-data-guard-apply-using-
incremental-rman-backup/
http://d-prototype.com/archives/10387
https://www.dba-oracle.com/t_rman_06172_no_autobackup_found.htm
http://www.bigdatalyn.com/2021/07/13/Oracle_DG_GAP_RecoverBySCN/
https://rajeevjhaoracle.wordpress.com/2011/12/23/resolving-gaps-in-data-guard-using-
incremental-rman-backup/
© copyright 2023, all rights reserved.
