How to Solve Gaps in Oracle Data Guard Apply

   In most companies that have Oracle database running you will have a standby database that is used for HA or for reports or any other reasons. Some times your standby database will lag behind and will get out of sync with primary database.

Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 1 sequence 1200
Fetching gap sequence in thread 1, gap sequence 1200-1200
FAL[client]: Failed to request gap sequence

Why this might happen ?

How to fix this ?

• 1 - Well you can recreate the standby database but this might take some effort and resources depending on your database size, network performance, database use, etc..
• 2 - Another option is to apply an incremental backup of Primary starting with the SCN registered in the Standby.

Let's go now and see how we can do this Step by Step

• 1 - On Standby find the SCN value

sql:SELECT to_char(CURRENT_SCN) FROM V$DATABASE;
50353294709

• 2 - Stop the MRP process in your Standby database and shutdown the database.

sql:ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
...
sql: shu immediate;

• 3 - Connect to the primary database as the RMAN target and create an incremental backup from the current SCN of the standby database that was recorded in step 1

BACKUP INCREMENTAL FROM SCN 50353294709 DATABASE FORMAT '/tmp/STB_INC_%U' tag 'FOR STB';

• 4 - On the Primary, create a new standby controlfile

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

• 5 - Copy all of the backup-sets and the controlfile to the host where the Standby database is located.

host@primary: scp /tmp/ctl_stb.ctl STB_INC_* oracle@stb_host:/u01/backup/

• 6 - Replace the standby control file with the newly generated controlfile form step 4

• 6.1 - find the location of the actual controlfile

SQL show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------

control_files                        string      /u02/oradata/Standby/control
                                                 01.ctl, /u02/oradata/Standby
                                                 /control02.ctl

• 6.2 - Replace the controlfile with the new one(make sure your database will be shutdown while doing this)

cp /u01/backup/ctl_stb.ctl /u02/oradata/Standby/control01.ctl
cp /u01/backup/ctl_stb.ctl /u02/oradata/Standby/control02.ctl

• 7 - Start your standby database in mount mode

SQL alter database mount standby database;

Database altered.

• 8 - Now will recover the Standby database using the incremental backup of primary taken at step 3

$ rman nocatalog target /
RMANcatalog start with '/u01/backup/';
....
....
....
-- issue the RECOVER command after the backupsets are cataloged

RMANRECOVER DATABASE NOREDO;

• 9 - After the recover is done and no errors comeup we will start the Standby Managerd Recovery process

SQL alter database recover managed standby database disconnect from session;

select 'ALTER DATABASE RENAME FILE  '''||name||''' to ''/NEW_STANDBY_LOCATION'|| substr(name,instr(name,'/',-1))||''';' as remane_tmp from v$datafile;