The error OGG-02028: Failed to attach to logmining server OGG$<extract_name> error #,### – ORA-01292 LogMiner for upstream capture cannot find log file – is an indication that the Oracle archive log file that is needed for the extract to start is missing. There are a few ways this can be resolved:

1. Restore the archive log that it is looking for (the easiest but longest way)
2. Rest extract to begin now (doesn’t always word)
3. Figure out what was the last applied transaction and start from there (easiest way but will skip some transactions)

This document is not going to focus on option one, instead this will focus on how to identify the last applied SCN and start from there.

Extract Status:

Before attempting to start the extract, the status of the extract can be seen in the DBA_CAPTURE view. The important columns in this view are – START_SCN, CAPTURED_SCN, APPLIED_SCN, FIRST_SCN, OLDEST_SCN, and FILTERED_SCN. These columns provide details on where the extract is at according to the system change number (SCN).

A query to review the timestamps of the SCN in each of these columns, is a follows:

select  capture_name
, (select scn_to_timestamp(39718416866710) from dual) as start_scn
, (select scn_to_timestamp(39719410027791) from dual) as captured_scn
, (select scn_to_timestamp(39719054864590) from dual) as applied_scn
, (select scn_to_timestamp(39718416866710) from dual) as first_scn
, (select scn_to_timestamp(39719054864590) from dual) as oldest_scn
, (select scn_to_timestamp(39648390214061) from dual) as filtered_scn
from dba_capture;

If an error is returned for any of these columns, that line in the query should be commented out. When it comes to OGG-02028 error, columns START_SCN, FIRST_SCN, and FILTER_SCN will error out. This is an indication that the extract is off.

Last applied transaction:

The APPLIED_SCN can be used to identify what was the last transaction applied to the database. In this case, the date of the last applied transaction was 30-JUN-23 11.03.00.00000000 AM. This can be seen by converting the SCN to a timestamp with the SCN_TO_TIMESTAMP function.

select scn_to_timestamp(39719054864590) from dual;

At this point, we need to locate the archive log that has the transactions from 30-JUN-23 11.03.00.00000000 AM and forward.

Locating Archive Log:

Once we have the START_SCN and know what the last applied transaction was (APPLIED_SCN), we can attempt to find the archive log needed to start the extract. In this case, we are using the START_SCN of 39718416866710.

To query for the archive log need, we use the V$ARCHIVE_LOG view for the FIRST_CHANGE# that matches the START_SCN.

SELECT *  FROM V$ARCHIVED_LOG
where first_change# = 39718416866710

This can be refined a bit more by only looking at the FIRST_CHANGE#, ARCHIVED, and DELETED columns.

SELECT first_change#, first_time, archived, deleted  
FROM V$ARCHIVED_LOG
where first_change# = 39718416866710

What this shows is that the extract last applied transaction by the extract was on 30-JUN-23 and was in an archive log dated 26-JUN-23. Additionally, the archive log was deleted, possibility during the last backup cycle. Upon any sort of restart of the extract, it will be looking for a transaction in the middle of the 26-JUN-23 archive log, which will cause the extract not to start with OGG-02028 error.

Remedy:

The simplest way of restarting an extract that has a missing archive log is to perform an ALTER EXTRACT, SCN <CAPTURED_SCN>. What this will do is reset the extract from the CURRENT_SCN to the CAPTURED_SCN. Allowing the extract to start.

Although this is the fastest way to resolve this error, it will lead to a few missing transactions that will be skipped within the redo/archive logs on the source. After the extract restarts successfully, the GoldenGate Administrator needs to do a data comparison across a DBLINK using a look like DBMS_COMPARISION or using Veridata to identify any missing rows and manually sync them.

In the end, this is the quickest way to resolve the OGG-02028 error.