How to Resolve GAP
PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.Following Steps will show you how to resolve GAP in your Physical Standby DB:
You can find more
detail in alert log.
Execute following command
You can check on
primary DB, which sequences are applied and which are missing.
SELECT * FROM V$ARCHIVE_GAP;
SQL> SELECT SEQUENCE#, FIRST_TIME,
NEXT_TIME, applied from v$archived_log order by sequence#;
SQL> SELECT SEQUENCE#, FIRST_TIME,
NEXT_TIME, applied from v$archived_log where applied='YES' order by sequence#;
Login to the Standby DB.
rman TARGET sys/xxxx@KARDBPRD2 AUXILIARY /
RMAN> list backup
of archivelog sequence 67772;
RMAN> restore
archivelog sequence 67772;
OR:
RMAN> run
{
RESTORE ARCHIVELOG
FROM SEQUENCE 67773 UNTIL SEQUENCE 67786;
}
Stop MRPO Process.
NOTE: For logical
standby don’t need to stop apply log it will handle automatically.
SQL> alter database recover managed standby database cancel;
The new generated
archive logs will be automatically applied on Standby DB.
On Standby DB:
rman target /
RMAN> recover database ;
Start MRPO process:
SQL> alter database recover managed standby database disconnect;
For apply immediate
log need to do following statement.
alter database recover managed standby database using current logfile
disconnect;
Verify the Log are applied:
SQL> SELECT SEQUENCE#, FIRST_TIME,
NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SQL> select sequence#,applied from
v$archived_log order by sequence#;
=========================
Example from prod gap:
Restoring arch file on
primary which will apply automatically on standby
On standby:
SQL> SELECT * FROM
V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE# CON_ID
---------- -------------
-------------- ----------
1
29423 29496 1
SQL> Select THREAD#,LOW_SEQUENCE#,HIGH_SEQUENCE#
From V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1
29423 29496
Restore
the missing arch file from the RMAN backup.
On Primary DB execute the script:
more ddboost_restore_GAP.script
run{
allocate channel ch1 device type
SBT_TAPE TRACE 0 PARMS
'SBT_LIBRARY=/u12/oracle/product/12.1.0/db_12c/lib/libddobk.so,
ENV=(STORAGE_UNIT=user_ddboost_restore,BACKUP_HOST=storage_prd.th.com,ORACLE_HOME=/u12/oracle/product/12.1.0/db_12c)';
allocate channel ch2 device type
SBT_TAPE TRACE 0 PARMS
'SBT_LIBRARY=/u12/oracle/product/12.1.0/db_12c/lib/libddobk.so,
ENV=(STORAGE_UNIT=user_ddboost_restore,BACKUP_HOST=storage_prd.th.com,ORACLE_HOME=/u12/oracle/product/12.1.0/db_12c)';
allocate channel ch3 device type
SBT_TAPE TRACE 0 PARMS
'SBT_LIBRARY=/u12/oracle/product/12.1.0/db_12c/lib/libddobk.so,
ENV=(STORAGE_UNIT=user_ddboost_restore,BACKUP_HOST=storage_prd.th.com,ORACLE_HOME=/u12/oracle/product/12.1.0/db_12c)';
allocate channel ch4 device type
SBT_TAPE TRACE 0 PARMS
'SBT_LIBRARY=/u12/oracle/product/12.1.0/db_12c/lib/libddobk.so,
ENV=(STORAGE_UNIT=user_ddboost_restore,BACKUP_HOST=storage_prd.th.com,ORACLE_HOME=/u12/oracle/product/12.1.0/db_12c)';
send 'set username struser password
xxxxxx servername storage_prd.th.com';
restore
archivelog from logseq 29423 until logseq 29496;
RELEASE CHANNEL ch1;
RELEASE CHANNEL ch2;
RELEASE CHANNEL ch3;
RELEASE CHANNEL ch4;
}
