Oracle GoldenGate has various processes like Extract, DataPump, DistPath and Replicat. All these processes can be repositioned as per the requirements. However, repositioning an Integrated Extract process is bit tricky. This article explains about repositioning of an Integrated Extract process with detailed examples.
There are various reasons we want to alter the Extract process to an older SCN.
- New tables added to an existing Extract, users wish to go ‘back in time’ to older SCN for those tables (in this scenario there is no new Extract being created)
- Resolution of unrelated Extract issues. For example, patching or starting w/new Extract to resolve other issues (in this scenario the users would create a new Extract and go ‘back in time’ to an older SCN)
However, altering an Extract process to the previous SCN should not be done regularly or frequently.
With Oracle GoldenGate < 18c, Integrated Extract can only be altered to a SCN which is higher than the FIRST_SCN. In contrast, with Classic Extract you were able to alter to any point in time or SCN based on the availability of archive logs.
With Integrated Extract, if one wants to capture data of SCN below FIRST_SCN, the only possible solution is to have a regular LogMiner Data Dictionary. One can create a new Integrated Extract and register it to the LogMiner Data Dictionary which is below FIRST_SCN. This allows the user to capture data that are below the FIRST_SCN.
The LogMiner data dictionary consists of a snapshot of the database tables used to store and retrieve information about objects and their versions. LogMiner Dictionary provides metadata for mining and GoldenGate. It tracks the system catalog changes and can only guarantee correct metadata at and above the Dictionary FIRST_SCN.
If the LogMiner Dictionary is unavailable, metadata errors may occur when older version objects redo is processed.
LogMiner Dictionary builds have to be done periodically and dumped at the current SCN when built. Below is the command to build a LogMiner Dictionary.
Execute:
This will write a dictionary into the redo log. When you register, you do
This is one of the best practice, so that there will always be a valid LogMiner SCN which allows us to go back to.
Note: To know more about the LogMiner Dictionary Builds, please check the link,
So, for Integrated Extract, below are the requirements to go back to previous SCN.
- LogMiner Dictionary Build (LM SCN)
- Historic archive logs must be available and registered to LogMiner session used by Extract
From Oracle GoldenGate 18c, Integrated Extract has been enhanced in a way that it can be altered to a position or SCN which is below the FIRST SCN. This feature or enhancement only works if your database version is 18c or higher.
Altering the Integrated Extract process with,
| ORACLE DATABASE | RESULT |
| < 18.0.0.0 | Not Supported |
| > = 18.0.0.0 | Issue Warning, then Alter Extract |
For this feature to work, below are the conditions,
- In the range of Start SCN and current FIRST_SCN, there shouldn’t be any DDL operations performed on the interested tables.
- All the archive logs from that SCN must be available and registered to the LogMiner session created for the Extract process.
If there are DDL changes on the interested table and when Extract tries to process redo corresponding to an older version of the table, then by default, a warning message will be written to the report file. The Extract process will not abend, but it will not capture any data from the table which encountered DDL changes.
By default, the Integrated Extract process writes a warning message to the report file and discard file and moves ahead without capturing the old data. Let me explain with a simple example.
Assuming there are no LogMiner Dictionary backups available and for some reason we need to alter the Integrated Extract to the SCN which is below FIRST_SCN.
SQL> SELECT first_change# FROM v$archived_log
WHERE dictionary_begin = ‘YES’
AND standby_dest = ‘NO’
AND name IS NOT NULL
AND status = ‘A’;
no rows selected
Altered the Integrated Extract process before the FIRST SCN
OGG> ALTER EXTRACT extn SCN 47972100
2023-03-17T07:26:43Z WARNING OGG-02411 Altering Extract to an SCN less than the current First SCN.
Current First SCN is 47,974,321. Altered Start SCN is 47,972,100.
2023-03-17T07:26:43Z INFO OGG-08100 Extract altered.
Started the Integrated Extract.
OGG> INFO ALL
Program Status Group Type Lag at Chkpt Time Since Chkpt
ADMINSRVR RUNNING
DISTSRVR RUNNING
PMSRVR RUNNING
RECVSRVR RUNNING
EXTRACT RUNNING EXTN INTEGRATED 00:00:03 00:00:01
After altering and starting Integrated Extract, it captured only the recent operations and not the older operations.
We could also see a WARNING message in the report file of the Extract process.
2023-03-17 07:28:13 WARNING OGG-02462 Received no metadata LCR with objID: 77,302. objName: SOURCE.EMPLOYEE. opType: INSERT
due to altering Extract to an SCN less than the current First SCN.
2023-03-17 07:28:15 INFO OGG-01971 The previous message, ‘WARNING OGG-02462’, repeated 5999 times.
And, below messages will be seen in the Discard file of the Integrated Extract process EXTN,
Encountered unsupported LCR: opcode:1 reason:49 scn:47972104 rba:33818640 thread_id:1 object_id:0 xid:6.33.8973
Encountered unsupported LCR: opcode:1 reason:49 scn:47972104 rba:33819536 thread_id:1 object_id:0 xid:6.33.8973
Encountered unsupported LCR: opcode:1 reason:49 scn:47972104 rba:33820104 thread_id:1 object_id:0 xid:6.33.8973
Encountered unsupported LCR: opcode:1 reason:49 scn:47972104 rba:33820688 thread_id:1 object_id:0 xid:6.33.8973
In this scenario, the Integrated Extract did not abend, but it had loged the messages in Report and Discard file and moved forward.
There may be also a requirement where we want the Integrated Extract process to get abended instead of moving forward when encountering such scenarios. In that case, we need to use the below hidden parameter in the Integrated Extract process.
-
DEFAULT – Write to Extract Report File and Discard Silently.
-
Write to Discard File.
-
Abend
-
Ignore
The parameter has various options which we can see above and it plays a major role in handling the repositioning of Integrated Extract to the SCN below the FIRST_SCN.
NOTE: Please contact Oracle Support before using this parameter
Let us add the below parameter with option “ABEND” to the Integrated Extract process parameter file.
After adding the above parameter, the Integrated Extract process got abended with below error,
2023-03-17 08:23:05 WARNING OGG-02462 Received no metadata LCR with objID: 77,302. objName: SOURCE.EMPLOYEE. opType: INSERT due to altering Extract to an SCN less than the current First SCN.
2023-03-17 08:23:05 ERROR OGG-02139 Extract has encountered an unsupported LCR:
opcode:1 reason:49 scn:47,972,104 rba:33,818,640 thread_id:1 object_id:1 xid:6.33.8973.
2023-03-17 08:23:06 ERROR OGG-02078 Extract encountered a fatal error in a processing thread and is abending.
2023-03-17 08:23:06 ERROR OGG-02171 Error reading LCR from data source. Status 510, data source type TranLogDataSource.
2023-03-17 08:23:06 ERROR OGG-01668 PROCESS ABENDING.
Since we had DDL changes on the interested table and the Extract processed the redo corresponding to , the older table version. It will either ignore the error or abend, which is based on the parameter setting.
There are three use cases or scenarios which are explained one by one in detail.
- Alter the existing Integrated Extract to the SCN below the FIRST_SCN.
- Alter the existing Integrated Extract to the SCN below the FIRST_SCN with LogMiner Data Dictionary backups in place.
- Create and register an Integrated Extract with LogMiner Data Dictionary SCN which is below the FIRST_SCN.
Alter Integrated Extract to the SCN below the FIRST_SCN
The scenario is as below,
| Step |
Time (SCN) |
SCN# in SCENARIO |
OPERATIONS PERFORMED |
| 1 |
SCN 1 |
55158914 |
Perform DML on an unrelated table SOURCE.TEST (table which is not in replication) |
| 2 |
SCN 2 |
55160352 |
Create a table SOURCE.EMPLOYEE |
| 3 |
SCN 3 |
55161125 |
Perform INSERT operations on table SOURCE.EMPLOYEE |
| 4 |
SCN 4 |
55163177 |
Drop the table SOURCE.EMPLOYEE |
| 5 |
SCN 5 |
55165162 |
Add a new Integrated Extract EXTN |
| 6 |
SCN 6 |
55210540 |
Create a new (modified) table SOURCE.EMPLOYEE |
| 7 |
SCN 7 |
55211308 |
Perform INSERT operations on table SOURCE.EMPLOYEE |
| 8 |
FIRST SCN |
55164668 |
Alter the Integrated Extract below the FIRST_SCN 55164668, which is SCN 55161000 |
There are no LogMiner Data Dictionary backups available.
SQL> SELECT first_change# FROM v$archived_log
WHERE dictionary_begin = ‘YES’
AND standby_dest = ‘NO’
AND name IS NOT NULL
AND status = ‘A’;
no rows selected
1. Perform DML on an unrelated table (table which is not in replication)
SQL> SELECT current_scn FROM v$database;
CURRENT_SCN
———–
55158914
SQL> SELECT COUNT(1) FROM source.test;
COUNT(1)
———-
50000
SQL> DESC source.test
Name Null? Type
———- ——- —————-
EMP_NO NOT NULL NUMBER(10)
EMP_NAME VARCHAR2(20)
COUNTRY VARCHAR2(20)
SQL> TRUNCATE TABLE source.test;
Table truncated.
SQL> SELECT COUNT(1) FROM source.test;
COUNT(1)
———-
0
SQL> BEGIN
FOR ids IN 1..1000
LOOP
INSERT INTO source.test (emp_no, emp_name, country)
VALUES (IDS, ‘GoldenGate’, ‘India’);
IF MOD(IDS, 1000) = 0 THEN
COMMIT;
END IF;
END LOOP;
END;
/
PL/SQL procedure successfully completed.
SQL>SELECT COUNT(1) FROM source.test;
COUNT(1)
———-
1000
Now, creating a new table SOURCE.EMPLOYEE
SQL> SELECT current_scn FROM v$database;
CURRENT_SCN
———–
55160352
2. Create a table SOURCE.EMPLOYEE
SQL> CREATE TABLE source.employee
(emp_no NUMBER(10), emp_name VARCHAR2(20), salary NUMBER(10));
Table created.
SQL> DESC source.employee
Name Null? Type
———- ——- —————-
EMP_NO NUMBER(10)
EMP_NAME VARCHAR2(20)
SALARY NUMBER(10)
3. Perform INSERT operations on table SOURCE.EMPLOYEE
SQL> SELECT current_scn FROM v$database;
CURRENT_SCN
———–
55161125
SQL> BEGIN
FOR ids IN 1..1000
LOOP
INSERT INTO source.employee (emp_no, emp_name, salary)
VALUES (ids, ‘GoldenGate’, 1000);
IF MOD(ids, 1000) = 0 THEN
COMMIT;
END IF;
END LOOP;
END;
/
PL/SQL procedure successfully completed.
SQL> SELECT COUNT(1) FROM employee;
COUNT(1)
———-
1000
4. Drop the table SOURCE.EMPLOYEE
SQL> SELECT current_scn FROM v$database;
CURRENT_SCN
———–
55163177
SQL> DROP TABLE source.employee;
Table dropped.
SQL> DESC source.employee
ERROR:
ORA-04043: object source.employee does not exist
5. Added a new Integrated Extract EXTN
OGG> ADD EXTRACT extn INTEGRATED TRANLOG BEGIN NOW
2023-04-07T09:12:55Z INFO OGG-08100 Integrated Extract added.
OGG> REGISTER EXTRACT extn database container (orclpdb1)
2023-04-07T09:13:12Z INFO OGG-02003 Extract group EXTN successfully registered with database at SCN 55165162.
OGG> ADD EXTTRAIL et EXTRACT extn
2023-04-07T09:13:15Z INFO OGG-08100 EXTTRAIL added.
OGG> START EXTRACT extn
2023-04-07T09:13:58Z INFO OGG-00975 Extract group EXTN starting.
2023-04-07T09:13:58Z INFO OGG-15426 Extract group EXTN started.
OGG> INFO ALL
Program Status Group Type Lag at Chkpt Time Since Chkpt
ADMINSRVR RUNNING
DISTSRVR RUNNING
PMSRVR RUNNING
RECVSRVR RUNNING
EXTRACT RUNNING EXTN INTEGRATED 00:00:00 00:00:02
OGG> info EXTRACT extn DETAIL
Extract EXTN Last Started 2023-04-07 09:14 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:06 ago)
Process ID 1731627
Log Read Checkpoint Oracle Integrated Redo Logs
2023-04-07 09:14:33
SCN 0.55207084 (55207084)
Encryption Profile LocalWallet
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
et 0 1297 500 EXTTRAIL
Integrated Extract outbound server first scn: 0.55164668 (55164668)
Integrated Extract outbound server filtering start scn: 0.55165162 (55165162)
Extract Source Begin End
Not Available 2023-04-07 09:12 2023-04-07 09:14
Not Available * Initialized * 2023-04-07 09:12
Not Available * Initialized * 2023-04-07 09:12
Not Available * Initialized * 2023-04-07 09:12
Current directory /scratch/ogg21ma/ogg_ma
Report file /scratch/ogg21ma/ogg_dep/var/lib/report/EXTN.rpt
Parameter file /scratch/ogg21ma/ogg_dep/etc/conf/ogg/EXTN.prm
Checkpoint file /scratch/ogg21ma/ogg_dep/var/lib/checkpt/EXTN.cpe
Process file /scratch/ogg21ma/ogg_dep/var/run/EXTN.pce
Error log /scratch/ogg21ma/ogg_dep/var/log/ggserr.log
The FIRST_SCN of this Integrated Extract is 55164668
The CURRENT_SCN of the database is 55207225
SQL> SELECT current_scn FROM v$database;
CURRENT_SCN
———–
55207225
Since we registered a new Integrated Extract, we have a new Dictionary Build at SCN 55164668
SQL> SELECT first_change# FROM v$archived_log
WHERE dictionary_begin = ‘YES’
AND standby_dest = ‘NO’
AND name IS NOT NULL
AND status = ‘A’;
FIRST_CHANGE#
————-
55164668
6. Create a new (modified) table SOURCE.EMPLOYEE
SQL> SELECT current_scn FROM v$database;
CURRENT_SCN
———–
55210540
SQL> CREATE TABLE source.employee
(emp_no NUMBER(10), emp_name VARCHAR2(20), salary NUMBER(10));
Table created.
SQL> DESC source.employee
Name Null? Type
———- ——- —————-
EMP_NO NUMBER(10)
EMP_NAME VARCHAR2(20)
SALARY NUMBER(10)
7. Perform INSERT operations on table SOURCE.EMPLOYEE
SQL> SELECT current_scn FROM v$database;
CURRENT_SCN
———–
55211308
SQL> BEGIN
FOR ids IN 1..3000
LOOP
INSERT INTO source.employee (emp_no, emp_name, salary)
VALUES (ids, ‘GoldenGate’, 1000);
IF MOD(ids, 1000) = 0 THEN
COMMIT;
END IF;
END LOOP;
END;
/
PL/SQL procedure successfully completed.
SQL> SELECT COUNT(1) FROM source.employee;
COUNT(1)
———-
3000
The Integrated Extract has captured the 3000 operations which we can see from the STATS output,
OGG> STATS EXTRACT extn LATEST
Sending STATS latest request to Extract group EXTN …
Start of statistics at 2023-04-07 09:25:57.
Output to et:
Extracting from ORCLPDB1.SOURCE.EMPLOYEE to ORCLPDB1.SOURCE.EMPLOYEE:
*** Latest statistics since 2023-04-07 09:25:05 ***
Total inserts 3000.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 3000.00
End of statistics.
Now, let us assume that for some reason the Integrated Extract has abended and we need to reposition to the SCN which is below the FIRST_SCN.
OGG> INFO ALL
Program Status Group Type Lag at Chkpt Time Since Chkpt
ADMINSRVR RUNNING
DISTSRVR RUNNING
PMSRVR RUNNING
RECVSRVR RUNNING
EXTRACT RUNNING EXTN INTEGRATED 00:00:00 00:00:48
FIRST_SCN of the Integrated Extract “EXTN” is 55164668
We want to alter it to the SCN 55161000 which is below the FIRST_SCN 55164668
8. Alter the Integrated Extract below the FIRST_SCN 55164668, which is SCN 55161000
OGG> ALTER EXTRACT extn SCN 55161000
2023-04-07T09:39:58Z WARNING OGG-02411 Altering Extract to an SCN less than the current First SCN.
Current First SCN is 55,164,668. Altered Start SCN is 55,161,000.
2023-04-07T09:39:58Z INFO OGG-08100 Extract altered.
You can see a WARNING message above which says the Integrated Extract has been altered to an SCN which is below the FIRST SCN.
Started the Integrated Extract process.
OGG> START EXTRACT extn
2023-04-07T09:40:17Z INFO OGG-00975 Extract group EXTN starting.
2023-04-07T09:40:17Z INFO OGG-15426 Extract group EXTN started.
OGG> INFO ALL
Program Status Group Type Lag at Chkpt Time Since Chkpt
ADMINSRVR RUNNING
DISTSRVR RUNNING
PMSRVR RUNNING
RECVSRVR RUNNING
EXTRACT RUNNING EXTN INTEGRATED 00:00:00 00:00:00
From the below INFO DETAIL output, we can see the FIRST SCN has been changed or altered to SCN 55161000
OGG> info EXTRACT extn DETAIL
Extract EXTN Last Started 2023-04-07 09:40 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Process ID 1732529
Log Read Checkpoint Oracle Integrated Redo Logs
2023-04-07 09:41:10
SCN 0.55215871 (55215871)
Encryption Profile LocalWallet
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
et 1 380071 500 EXTTRAIL
Integrated Extract outbound server first scn: 0.55161000 (55161000)
Integrated Extract outbound server filtering start scn: 0.55161000 (55161000)
Extract Source Begin End
Not Available * Initialized * 2023-04-07 09:41
Not Available * Initialized * First Record
Not Available * Initialized * First Record
Not Available * Initialized * First Record
Not Available 2023-04-07 09:12 2023-04-07 09:36
Not Available * Initialized * 2023-04-07 09:12
Not Available * Initialized * 2023-04-07 09:12
Not Available * Initialized * 2023-04-07 09:12
Current directory /scratch/ogg21ma/ogg_ma
Report file /scratch/ogg21ma/ogg_dep/var/lib/report/EXTN.rpt
Parameter file /scratch/ogg21ma/ogg_dep/etc/conf/ogg/EXTN.prm
Checkpoint file /scratch/ogg21ma/ogg_dep/var/lib/checkpt/EXTN.cpe
Process file /scratch/ogg21ma/ogg_dep/var/run/EXTN.pce
Error log /scratch/ogg21ma/ogg_dep/var/log/ggserr.log
STATS output of the Integrated Extract “EXTN” process
OGG> STATS EXTRACT extn LATEST
Sending STATS latest request to Extract group EXTN …
Start of statistics at 2023-04-07 09:42:56.
Output to et:
Extracting from ORCLPDB1.SOURCE.EMPLOYEE to ORCLPDB1.SOURCE.EMPLOYEE:
*** Latest statistics since 2023-04-07 09:40:24 ***
Total inserts 3000.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 3000.00
End of statistics.
As I mentioned earlier in this article, by default a WARNING message will be written to the Integrated Extract’s report file and discard file which we can see below,
WARNING messages in Report File of the Extract process EXTN
2023-04-07 09:40:23 WARNING OGG-02462 Received no metadata LCR with objID: 97,491.
objName: SOURCE.BIN$+Lv9eBpXaLDgUy5MRmSbaA==$0. opType: INSERT
due to altering Extract to an SCN less than the current First SCN.
2023-04-07 09:40:24 INFO OGG-01971 The previous message, ‘WARNING OGG-02462’, repeated 999 times.
Messages in Discard File of the Extract process EXTN
Oracle GoldenGate Capture for Oracle process started, group EXTN discard file opened: 2023-04-07 09:40:17.517430
Encountered unsupported LCR: opcode:1 reason:49 scn:55161198 rba:3707548 thread_id:1 object_id:0 xid:2.25.9764
Encountered unsupported LCR: opcode:1 reason:49 scn:55161198 rba:3708076 thread_id:1 object_id:0 xid:2.25.9764
Encountered unsupported LCR: opcode:1 reason:49 scn:55161198 rba:3708376 thread_id:1 object_id:0 xid:2.25.9764
Encountered unsupported LCR: opcode:1 reason:49 scn:55161198 rba:3708692 thread_id:1 object_id:0 xid:2.25.9764
Alter Integrated Extract to the SCN below the FIRST_SCN with LogMiner Data Dictionary backups
| Step |
SCN |
SCN# in SCENARIO |
OPERATIONS PERFORMED |
| 1 |
SCN 1 |
61148444 |
Already LogMiner Dictionary Backup is taken |
| 2 |
SCN 2 |
61149782 |
Perform DML on an unrelated table (table which is not in replication) – Truncated the Table SOURCE.TEST |
| 3 |
SCN 3 |
61150030 |
Performed INSERT operations on table SOURCE.TEST |
| 4 |
SCN 4 |
61151908 |
LogMiner Dictionary backup is taken |
| 5 |
SCN 5 |
61153097 |
Created a new table SOURCE.EMPLOYEE |
| 6 |
SCN 6 |
61153510 |
Perform INSERT operations on table SOURCE.EMPLOYEE |
| 7 |
SCN 7 |
61154170 |
Drop the table SOURCE.EMPLOYEE |
| 8 |
SCN 8 |
61155210 |
Added a new Integrated Extract “EXTN”. FIRST SCN is 61154717 |
| 9 |
SCN 9 |
61200294 |
Create a new table SOURCE.EMPLOYEE |
| 10 |
SCN 10 |
61201103 |
Perform INSERT operations on table SOURCE.EMPLOYEE |
| 11 |
FIRST SCN |
61154717 |
Alter the Integrated Extract to is SCN 61151908 which is below the FIRST_SCN 61154717 |
In this scenario, we are going to alter the Integrated Extract to the SCN 61151908 and this SCN is listed in the LogMiner Dictionary Backups. All the steps till Step 10 in the table / diagram are completed. We will directly start from Step 11.
SQL> SELECT first_change# FROM v$archived_log
WHERE dictionary_begin = ‘YES’
AND standby_dest = ‘NO’
AND name IS NOT NULL
AND status = ‘A’;
FIRST_CHANGE#
————-
61148444
61151908
61154717
Now, let us assume that for some reason the Integrated Extract has abended and we need to reposition to the SCN which is below the FIRST_SCN.
OGG> INFO ALL
Program Status Group Type Lag at Chkpt Time Since Chkpt
ADMINSRVR RUNNING
DISTSRVR RUNNING
PMSRVR RUNNING
RECVSRVR RUNNING
EXTRACT ABENDED EXTN INTEGRATED 00:00:00 00:00:48
FIRST_SCN of the Integrated Extract “EXTN” is 61154717
We want to alter it to the SCN 61151908 which is below the FIRST_SCN 61154717
11. Alter the Integrated Extract to SCN 61151908 which is below the FIRST_SCN 61154717
OGG> ALTER EXTRACT extn SCN 61151908
2023-04-26T11:02:58Z WARNING OGG-02411 Altering Extract to an SCN less than the current First SCN.
Current First SCN is 61,154,717. Altered Start SCN is 61,151,908.
2023-04-26T11:02:58Z INFO OGG-08100 Extract altered.
From the below INFO DETAIL output, we could see the FIRST_SCN has been changed or altered to SCN 61151908
OGG> info EXTRACT extn DETAIL
Extract EXTN Last Started 2023-04-26 11:03 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Process ID 2524496
Log Read Checkpoint Oracle Integrated Redo Logs
2023-04-26 11:05:42
SCN 0.61206575 (61206575)
Encryption Profile LocalWallet
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
et 1 380075 500 EXTTRAIL
Integrated Extract outbound server first scn: 0.61151908 (61151908)
Integrated Extract outbound server filtering start scn: 0.61151908 (61151908)
Extract Source Begin End
Not Available * Initialized * 2023-04-26 11:05
Not Available * Initialized * First Record
Not Available * Initialized * First Record
Not Available * Initialized * First Record
Not Available 2023-04-26 10:46 2023-04-26 10:59
Not Available * Initialized * 2023-04-26 10:46
Not Available * Initialized * 2023-04-26 10:46
Not Available * Initialized * 2023-04-26 10:46
Current directory /scratch/ogg21ma/ogg_ma
Report file /scratch/ogg21ma/ogg_dep/var/lib/report/EXTN.rpt
Parameter file /scratch/ogg21ma/ogg_dep/etc/conf/ogg/EXTN.prm
Checkpoint file /scratch/ogg21ma/ogg_dep/var/lib/checkpt/EXTN.cpe
Process file /scratch/ogg21ma/ogg_dep/var/run/EXTN.pce
Error log /scratch/ogg21ma/ogg_dep/var/log/ggserr.log
STATS output of the Extract process
OGG> STATS EXTRACT extn LATEST
Sending STATS latest request to Extract group EXTN …
Start of statistics at 2023-04-26 11:06:32.
Output to et:
Extracting from ORCLPDB1.SOURCE.EMPLOYEE to ORCLPDB1.SOURCE.EMPLOYEE:
*** Latest statistics since 2023-04-26 11:03:23 ***
Total inserts 3000.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 3000.00
End of statistics.
As I mentioned earlier in this article, by default a WARNING message will be written to the Integrated Extract’s report file and discard file which we can see below,
WARNING messages in Report File of the Extract process EXTN
2023-04-26 11:03:22 WARNING OGG-02462 Received no metadata LCR with objID: 99,055.
objName: SOURCE.BIN$+jvmd8OhgB3gUy5MRmS9kw==$0. opType: INSERT
due to altering Extract to an SCN less than the current First SCN.
2023-04-26 11:03:23 INFO OGG-01971 The previous message, ‘WARNING OGG-02462’, repeated 999 times.
Messages in Discard File of the Extract process EXTN
Oracle GoldenGate Capture for Oracle process started, group EXTN discard file opened: 2023-04-26 11:03:16.206588
Encountered unsupported LCR: opcode:1 reason:49 scn:61153528 rba:189904 thread_id:1 object_id:0 xid:8.32.10608
Encountered unsupported LCR: opcode:1 reason:49 scn:61153530 rba:190480 thread_id:1 object_id:0 xid:8.32.10608
Encountered unsupported LCR: opcode:1 reason:49 scn:61153530 rba:190824 thread_id:1 object_id:0 xid:8.32.10608
Eventhough if you have LogMiner Dictionary backups and position the Integrated Extract to LogMiner Dictionary Backup SCN 61151908, it will not capture the old transactions.
But this is possible, if you create a new Integrated Extract process and register it to that LogMiner Dictionary Backup.
Let us try that now.
Create and register an Integrated Extract with LogMiner Data Dictionary SCN which is below the FIRST_SCN
Unregistering the existing Integrated Extract process and re-creating it and registering it to the LogMiner Dictionary Backup SCN 61151908
OGG> INFO ALL
Program Status Group Type Lag at Chkpt Time Since Chkpt
ADMINSRVR RUNNING
DISTSRVR RUNNING
PMSRVR RUNNING
RECVSRVR RUNNING
EXTRACT RUNNING EXTN INTEGRATED 00:00:00 00:00:05
OGG> STOP EXTRACT extn
2023-04-26T11:25:11Z INFO OGG-08100 Sending STOP request to Extract group EXTN.
2023-04-26T11:25:11Z INFO OGG-02964 Extract group EXTN is down (gracefully).
OGG> unREGISTER EXTRACT extn database
2023-04-26T11:25:25Z INFO OGG-01750 Successfully unregistered Extract group EXTN from database.
OGG> DELETE EXTRACT extn
2023-04-26T11:25:33Z INFO OGG-08100 OCI Error ORA (status = 942-ORA-00942: table or view does not exist
)
2023-04-26T11:25:33Z WARNING OGG-14064 Could not delete heartbeat table entries for group name EXTN: ().
2023-04-26T11:25:33Z INFO OGG-08100 Extract group EXTN deleted.
OGG> INFO ALL
Program Status Group Type Lag at Chkpt Time Since Chkpt
ADMINSRVR RUNNING
DISTSRVR RUNNING
PMSRVR RUNNING
RECVSRVR RUNNING
We have completely removed the Integrated Extract EXTN.
Let us re-add it and register it to the LogMiner Dictionary SCN 61151908
OGG> ADD EXTRACT extn INTEGRATED TRANLOG BEGIN NOW
2023-04-26T11:47:10Z INFO OGG-08100 Integrated Extract added.
OGG> REGISTER EXTRACT extn DATABASE CONTAINER (orclpdb1) SCN 61151908
2023-04-26T11:47:22Z INFO OGG-02003 Extract group EXTN successfully registered with database at SCN 61152404.
OGG> ADD EXTTRAIL et EXTRACT extn
2023-04-26T11:47:27Z INFO OGG-08100 EXTTRAIL added.
Here, we have added the Integrated Extract EXTN with BEGIN NOW option. Eventhough it is registered with LogMiner Dicationary Build SCN, it will not start capturing the data from that point. So, we need to alter the Extract to that SCN.
OGG> ALTER EXTRACT extn SCN 61151908
2023-04-26T11:48:53Z INFO OGG-08100 Extract altered.
OGG> info EXTRACT extn DETAIL
Extract EXTN Initialized 2023-04-26 11:48 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:00:51 ago)
Log Read Checkpoint Oracle Integrated Redo Logs
First Record
SCN 0.61151908 (61151908)
Encryption Profile LocalWallet
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
et 0 0 500 EXTTRAIL
Integrated Extract outbound server first scn: 0.61151908 (61151908)
Integrated Extract outbound server filtering start scn: 0.61152404 (61152404)
Extract Source Begin End
Not Available * Initialized * First Record
Not Available * Initialized * 2023-04-26 11:47
Current directory /scratch/ogg21ma/ogg_ma
Report file /scratch/ogg21ma/ogg_dep/var/lib/report/EXTN.rpt
Parameter file /scratch/ogg21ma/ogg_dep/etc/conf/ogg/EXTN.prm
Checkpoint file /scratch/ogg21ma/ogg_dep/var/lib/checkpt/EXTN.cpe
Process file /scratch/ogg21ma/ogg_dep/var/run/EXTN.pce
Error log /scratch/ogg21ma/ogg_dep/var/log/ggserr.log
Begin SCN and FIRST_SCN are same 61151908 in the above INFO output
OGG> INFO ALL
Program Status Group Type Lag at Chkpt Time Since Chkpt
ADMINSRVR RUNNING
DISTSRVR RUNNING
PMSRVR RUNNING
RECVSRVR RUNNING
EXTRACT RUNNING EXTN INTEGRATED 00:16:03 00:00:09
We can see the latency in the Extract process, as it has started processing the redo from SCN 61151908
OGG> INFO ALL
Program Status Group Type Lag at Chkpt Time Since Chkpt
ADMINSRVR RUNNING
DISTSRVR RUNNING
PMSRVR RUNNING
RECVSRVR RUNNING
EXTRACT RUNNING EXTN INTEGRATED 00:00:00 00:00:08
The Integrated Extract has captured the old transaction which is 1000 INSERTS and the recent transaction 3000 INSERT operations which we can see from the STATS output,
OGG> STATS EXTRACT extn LATEST
Sending STATS latest request to Extract group EXTN …
Start of statistics at 2023-04-26 11:52:02.
Output to et:
Extracting from ORCLPDB1.SOURCE.EMPLOYEE to ORCLPDB1.SOURCE.EMPLOYEE:
*** Latest statistics since 2023-04-26 11:50:09 ***
Total inserts 4000.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 4000.00
End of statistics.
Messages in Report File of the Extract process EXTN
2023-04-26 11:50:09 INFO OGG-06507 MAP (TABLE) resolved (entry orclpdb1.source.employee):
table “ORCLPDB1″.”SOURCE”.”EMPLOYEE”.
2023-04-26 11:50:09 WARNING OGG-06439 No unique key is defined for table EMPLOYEE.
All viable columns will be used to represent the key, but may not guarantee uniqueness.
KEYCOLS may be used to define the key.
2023-04-26 11:50:09 INFO OGG-06509 Using the following key columns for source
table ORCLPDB1.SOURCE.EMPLOYEE: EMP_NO, EMP_NAME, SALARY.
2023-04-26 11:50:11 INFO OGG-06507 MAP (TABLE) resolved (entry orclpdb1.source.employee):
table “ORCLPDB1″.”SOURCE”.”EMPLOYEE”.
2023-04-26 11:50:11 WARNING OGG-06439 No unique key is defined for table EMPLOYEE.
All viable columns will be used to represent the key, but may not guarantee uniqueness.
KEYCOLS may be used to define the key.
2023-04-26 11:50:11 INFO OGG-06509 Using the following key columns for source
table ORCLPDB1.SOURCE.EMPLOYEE: EMP_NO, EMP_NAME, SALARY.
2023-04-26 11:52:02 INFO OGG-01021 Command received from application REST API: STATS latest.
Messages in Discard File of the Extract process EXTN
Hope this article helped you to understand on how an Integrated Extract process can be altered or repositioned to previous SCN.