Oracle GoldenGate extract recovery (Missing archivelogs)

Issue description: The issue occurs when archive logs that are needed by Oracle GoldenGate extract gets deleted. For example this can happen when purging old archive logs using RMAN with “force” option. RMAN is integrated with OGG so under normal circumstances RMAN never deletes an archive log that is required by OGG extract. But with “force” option it does delete archive logs required by OGG extract. Although it is certainly not the best practice to use “force” option while purging old archive logs, however accident do happen. This post illustrates the steps needed to recover from such situation.

The error in the Oracle GoldenGate extract report file looks similar to the following example

Example:

ERROR   OGG-00868  Error code 1291, error message: ORA-01291: missing logfile

(Missing Log File WAITING FOR DICTIONARY REDO. Read Position SCN: 0.46921993 (46921993)).

Solution:

Figure 1: Extract failed as archives were deleted

1) Find out the archive logs (and the range) that the extract is complaining about. Let’s say the extract is complaining about SCN number 46921993.

Find out which archive log that SCN number belongs to.

   SQL> Select sequence# from v$archived_log where 46921993 is between first_change# and next_change#;     Sequence#     ==========     100

   SQL> Select sequence# from v$archived_log where sequence# >100 and name is NULL;

(Say the query came back with 10 archive logs (101 through 110)).

    Sequence#     ==========      100      101      102      103      104      105      106      107      108      109      110

2) Restore the archive log using RMAN.

   RMAN> Restore archive logs from logseq 100 until logseq 110;

Figure 2: Restore archive logs using RMAN and restart the extract

3) Register the archive logs for the capture process.

   SQL> Alter database register logical logfile “/<path to logfile>/logfilename> for "OGG Capture Name";

4) Restart the OGG extract.

   GGSCI> Start extract <OGG extract name> 

Note: This solution is generic and applies to both Oracle cloud and on-premise customers