In this blog post, we will show you how to Convert Classic Extract to Integrated Extract in Oracle Goldengate.
Follow the below steps to do that.
The extract is running in classic mode.
==============================
GGSCI (ora01.cloudintegrators.com as ggs@ORADB01) 77> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EINT1 00:00:00 00:00:06
REPLICAT RUNNING RINT1 00:00:00 00:00:03
GGSCI (ora01.cloudintegrators.com as ggs@ORADB01) 61> sh ls -ltr ./dirdat/eh*
-rw-r—– 1 cloud cloud 1453 Jul 15 11:38 ./dirdat/eh000000000
Verified the table has no data or it can data . It doesn’t matter ?
================================
SQL> select count(*) from test01.PIT_TESTTAB;
COUNT(*)
———-
0
SQL> select count(*) from test01.PIT_TESTTAB_02;
COUNT(*)
———-
0
Load script Running
=================
[ora01]/home/cloud/load> tail -100f run_load.log
.
Starting at 1468583258.908714
SQL> select count(*) from test01.PIT_TESTTAB;
COUNT(*)
———-
8034
The procedure to Move from Classic Extract to Integrated Extract
GGSCI (ora01.cloudintegrators.com as ggs@ORADB01) 85> stop EINT1
Sending STOP request to EXTRACT EINT1 …
Request processed.
GGSCI (ora01.cloudintegrators.com as ggs@ORADB01) 89> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EINT1 00:00:00 00:01:44
REPLICAT RUNNING RINT1 00:00:00 00:00:03
Make a note of the SCN when you registered the extract
GGSCI (ora01.cloudintegrators.com as ggs@ORADB01) 91> register extract EINT1 database
2016-07-15 11:51:32 INFO OGG-02003 Extract EINT1 successfully registered with database at SCN 53809105.
Now if you do a info extract eint1, it shows that the classic extract is stopped at an SCN which is not yet registered by the logminer.
Note: Logminer cannot go back and mine the SCN before it registers. It can only start mining from the SCN from where it registers.
GGSCI (ora01.cloudintegrators.com as ggs@ORADB01) 92> info EINT1,showch
EXTRACT EINT1 Last Started 2016-07-15 11:38 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:02:58 ago)
Log Read Checkpoint Oracle Redo Logs
2016-07-15 11:48:04 Seqno 2249, RBA 25189376
SCN 0.53721343 (53721343)
Current Checkpoint Detail:
Read Checkpoint #1
Oracle Redo Log
Startup Checkpoint (starting position in the data source):
Thread #: 1
Sequence #: 2249
RBA: 13853184
Timestamp: 2016-07-15 11:34:33.000000
SCN: Not available
Redo File:
Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
Thread #: 1
Sequence #: 2249
RBA: 24239632
Timestamp: 2016-07-15 11:48:03.000000
SCN: 0.53720666 (53720666)
Redo File: Not Available
Current Checkpoint (position of last record read in the data source):
Thread #: 1
Sequence #: 2249
RBA: 25189376
Timestamp: 2016-07-15 11:48:04.000000
SCN: 0.53721343 (53721343)
Redo File: /cloud5/ap/ora01/app/oracle/oradata/ORADB01/redo2.log
Write Checkpoint #1
GGS Log Trail
Current Checkpoint (current write position):
Sequence #: 0
RBA: 1335118
Timestamp: 2016-07-15 11:49:15.696847
Extract Trail: ./dirdat/eh
Seqno Length: 9
Flip Seqno Length: No
Trail Type: EXTTRAIL
Header:
Version = 2
Record Source = A
Type = 10
# Input Checkpoints = 1
# Output Checkpoints = 1
File Information:
Block Size = 2048
Max Blocks = 100
Record Length = 2048
Current Offset = 0
Configuration:
Data Source = 3
Transaction Integrity = 1
Task Type = 0
Status:
Start Time = 2016-07-15 11:38:37
Last Update Time = 2016-07-15 11:49:15
Stop Status = G
Last Result = 400
Before converting to Integrated Extract, start the classic extract and make sure the SCN which classic extract is processing is later than the registered logminer SCN.
GGSCI (ora01.cloudintegrators.com as ggs@ORADB01) 93> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EINT1 00:00:00 00:05:49
REPLICAT RUNNING RINT1 00:00:00 00:00:08
GGSCI (ora01.cloudintegrators.com as ggs@ORADB01) 94> start EINT1
Sending START request to MANAGER …
EXTRACT EINT1 starting
GGSCI (ora01.cloudintegrators.com as ggs@ORADB01) 96> INFO EINT1,SHOWCH
EXTRACT EINT1 Last Started 2016-07-15 11:55 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:10 ago)
Process ID 14010
Log Read Checkpoint Oracle Redo Logs
2016-07-15 11:55:29 Seqno 2263, RBA 45249536
SCN 0.53912350 (53912350)
Current Checkpoint Detail:
Read Checkpoint #1
Oracle Redo Log
Startup Checkpoint (starting position in the data source):
Thread #: 1
Sequence #: 2249
RBA: 24239632
Timestamp: 2016-07-15 11:48:03.000000
SCN: 0.53720666 (53720666)
Redo File: Not Available
Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
Thread #: 1
Sequence #: 2263
RBA: 44544016
Timestamp: 2016-07-15 11:55:29.000000
SCN: 0.53912023 (53912023)
Redo File: Not Available
Current Checkpoint (position of last record read in the data source):
Thread #: 1
Sequence #: 2263
RBA: 45249536
Timestamp: 2016-07-15 11:55:29.000000
SCN: 0.53912350 (53912350)
Redo File: /cloud5/ap/ora01/app/oracle/oradata/ORADB01/redo1.log
Write Checkpoint #1
GGS Log Trail
Current Checkpoint (current write position):
Sequence #: 1
RBA: 126276330
Timestamp: 2016-07-15 11:56:28.186157
Extract Trail: ./dirdat/eh
Seqno Length: 9
Flip Seqno Length: No
Trail Type: EXTTRAIL
Header:
Version = 2
Record Source = A
Type = 10
# Input Checkpoints = 1
# Output Checkpoints = 1
File Information:
Block Size = 2048
Max Blocks = 100
Record Length = 2048
Current Offset = 0
Configuration:
Data Source = 3
Transaction Integrity = 1
Task Type = 0
Status:
Start Time = 2016-07-15 11:55:14
Last Update Time = 2016-07-15 11:56:28
Stop Status = A
Last Result = 400
GGSCI > SEND EXTRACT EINT1 TRANLOGOPTIONS PREPAREFORUPGRADETOIE
=? This is a dynamic parameter and can be issued when the extract is running
Tail the ggserror.log and see if all extract is ready for the upgrade.
In the above SCN which classic extract is processing 53912350 > logminer registered SCN 53809105
Now we are stopping the extract
GGSCI (ora01.cloudintegrators.com as ggs@ORADB01) 98> stop EINT1
Sending STOP request to EXTRACT EINT1 …
Request processed.
GGSCI (ora01.cloudintegrators.com as ggs@ORADB01) 99> info EINT1,showch
EXTRACT EINT1 Last Started 2016-07-15 11:55 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:00:18 ago)
Log Read Checkpoint Oracle Redo Logs
2016-07-15 11:57:40 Seqno 2269, RBA 44133888
SCN 0.53964567 (53964567)
Current Checkpoint Detail:
Read Checkpoint #1
Oracle Redo Log
Startup Checkpoint (starting position in the data source):
Thread #: 1
Sequence #: 2249
RBA: 24239632
Timestamp: 2016-07-15 11:48:03.000000
SCN: 0.53720666 (53720666)
Redo File: Not Available
Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
Thread #: 1
Sequence #: 2269
RBA: 43327504
Timestamp: 2016-07-15 11:57:39.000000
SCN: 0.53964516 (53964516)
Redo File: Not Available
Current Checkpoint (position of last record read in the data source):
Thread #: 1
Sequence #: 2269
RBA: 44133888
Timestamp: 2016-07-15 11:57:40.000000
SCN: 0.53964567 (53964567)
Redo File: /cloud5/ap/ora01/app/oracle/oradata/ORADB01/redo1.log
Write Checkpoint #1
GGS Log Trail
Current Checkpoint (current write position):
Sequence #: 1
RBA: 163189320
Timestamp: 2016-07-15 11:58:23.329091
Extract Trail: ./dirdat/eh
Seqno Length: 9
Flip Seqno Length: No
Trail Type: EXTTRAIL
Header:
Version = 2
Record Source = A
Type = 10
# Input Checkpoints = 1
# Output Checkpoints = 1
File Information:
Block Size = 2048
Max Blocks = 100
Record Length = 2048
Current Offset = 0
Configuration:
Data Source = 3
Transaction Integrity = 1
Task Type = 0
Status:
Start Time = 2016-07-15 11:55:14
Last Update Time = 2016-07-15 11:58:23
Stop Status = G
Last Result = 400
Check where the dictionary has started. This will show the same SCN the logminer has been registered to.
SQL> select FIRST_TIME,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# from v$archived_log where DICTIONARY_BEGIN=’YES’;
FIRST_TIME SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
——————– ———- ————- ————
15-jul-2016 11:51:24 2256 53809105 53811965
GGSCI (ora01.cloudintegrators.com as ggs@ORADB01) 103> alter extract EINT1,upgrade INTEGRATED TRANLOG
Extract EINT1 successfully upgraded to integrated capture.
GGSCI (ora01.cloudintegrators.com as ggs@ORADB01) 104> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EINT1 00:00:00 00:04:42
REPLICAT RUNNING RINT1 00:00:00 00:00:00
GGSCI (ora01.cloudintegrators.com as ggs@ORADB01) 105> info EINT1
EXTRACT EINT1 Initialized 2016-07-15 11:55 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:04:48 ago)
Log Read Checkpoint Oracle Integrated Redo Logs
2016-07-15 11:57:40
SCN 0.53964567 (53964567)
Started the Integrated extract and the integrated extract is moving
GGSCI (ora01.cloudintegrators.com as ggs@ORADB01) 130> info EINT1
EXTRACT EINT1 Last Started 2016-07-15 12:03 Status RUNNING
Checkpoint Lag 00:00:04 (updated 00:00:00 ago)
Process ID 17087
Log Read Checkpoint Oracle Integrated Redo Logs
2016-07-15 12:03:51
SCN 0.54093278 (54093278)
After sometime Stopped the LOAD SCRIPT
==============
Verified the source and target and the count matches
SQL> select count(*) from test01.PIT_TESTTAB;
COUNT(*)
———-
786376
SQL> select count(*) from test01.PIT_TESTTAB_02;
COUNT(*)
———-
786376
SQL>
Recent Comments