GoldenGate specifies a time point to re-extract transaction changes

GoldenGate specifies a time point to re-extract transaction changes

Today's experiment demonstrates how to re-extract transactions at a specified time point in goldengate.

Tutorial steps:
1. Create a re-extracted SCN
2. Locate the start time based on the SCN in the v $ archived_log view.
3. Stop the extraction process.
4. Reset the start time of the extraction process.
5. Start the extraction process to re-capture the transaction (obtained from the archive log)

1. view the source's current SCN and record this value as the time point for re-extraction.
SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
1507066

2. view the ex1 status of the current extraction process.
GGSCI (node3) 2> info ex1 detail

EXTRACT EX1 Last Started Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:06 ago)
Log Read Checkpoint Oracle Redo Logs
19:09:40 Thread 1, Seqno 12, RBA 10750464
SCN 0.1507682 (1507682)
Log Read Checkpoint Oracle Redo Logs
19:09:39 Thread 2, Seqno 12, RBA 9300480
SCN 0.1507682 (1507682)

Target Extract Trails:

Remote Trail Name Seqno RBA Max MB

/Goldengate/dirdat/ex 2 205729 5

Extract Source Begin End

+ DATA/prod/onlinelog/group_4.302.859509357 2014-11-02 18:02 2014-11-02 19:09
Not Available * Initialized *

Current directory/goldengate

Report file/goldengate/dirrpt/EX1.rpt
Parameter file/goldengate/dirprm/ex1.prm
Checkpoint file/goldengate/dirchk/EX1.cpe
Process file/goldengate/dirpcs/EX1.pce
Stdout file/goldengate/dirout/EX1.out
Error log/goldengate/ggserr. log

3. view the current system time and existing local trail files

[Oracle @ single goldengate] $ ls-l dirdat
Total 424
-Rw-1 oracle oinstall 205826 Nov 1 rt1_00
-Rw-1 oracle oinstall 1192 Nov 1 rt000001
-Rw-1 oracle oinstall 1161 Nov 1 rt1_02

4. log on to the test user snow at source node 2, and insert 1 million records to the test table.
SYS @ PROD2> conn snow/snow

SNOW @ PROD2> begin for I in 1001 .. 1000000 loop
2 insert into t1 values (I, 'fin ');
3 end loop;
4 commit;
5 end;
6/

The destination snow. t1 successfully copies 1 million records.
SQL> conn snow/snow
Connected.
SQL> select count (*) from t1;

COUNT (*)
----------
1000000

Insert 100 entries of data so that the source generates 22 local trail files from the ex000003-ex000024

[Oracle @ node3 dirdat] $ ll
Total 104128
-Rw-1 oracle oinstall 205795 Nov 2 ex000000
-Rw-1 oracle oinstall 1076 Nov 2 ex000001
-Rw-1 oracle oinstall 4999907 Nov 3 ex000003
-Rw-1 oracle oinstall 4999952 Nov 3 ex000004
-Rw-1 oracle oinstall 4999951 Nov 3 ex000005
-Rw-1 oracle oinstall 4999951 Nov 3 ex000006
-Rw-1 oracle oinstall 4999951 Nov 3 ex000007
-Rw-1 oracle oinstall 4999951 Nov 3 ex000008
-Rw-1 oracle oinstall 4999951 Nov 3 ex000009
-Rw-1 oracle oinstall 4999951 Nov 3 ex1_10
-Rw-1 oracle oinstall 4999951 Nov 3 ex1_11
-Rw-1 oracle oinstall 4999951 Nov 3 ex1_12
-Rw-1 oracle oinstall 4999951 Nov 3 ex1_13
-Rw-1 oracle oinstall 4999951 Nov 3 ex1_14
-Rw-1 oracle oinstall 4999951 Nov 3 ex000015
-Rw-1 oracle oinstall 4999951 Nov 3 ex1_16
-Rw-1 oracle oinstall 4999951 Nov 3 ex1_17
-Rw-1 oracle oinstall 4999951 Nov 3 ex1_18
-Rw-1 oracle oinstall 4999951 Nov 3 ex000019
-Rw-1 oracle oinstall 4999951 Nov 3 ex000020
-Rw-1 oracle oinstall 4999951 Nov 3 ex000021
-Rw-1 oracle oinstall 4999951 Nov 3 ex000022
-Rw-1 oracle oinstall 4999951 Nov 3 ex1_23
-Rw-1 oracle oinstall 1122278 Nov 3 ex1_24

5. Find the corresponding time based on SCN1507066.

The archived log is marked in red. The log is generated at 17:41:26,. Then, the time is used as the parameter to regenerate the local trail.

NAME FIRST_CHANGE # FIRST_TIME
-----------------------------------------------------------------------------------------------------------
+ ARCH/prod/archivelog/2014_11_03/thread_2_seq_12.295.862650053 1484800 17:41:26
+ ARCH/prod/archivelog/2014_11_03/thread_eclipseq_12.296.862650059 1484803 17:41:26
+ ARCH/prod/archivelog/2014_11_03/thread_2_seq_13.297.862650071 1514934 09:00:52
+ ARCH/prod/archivelog/2014_11_03/thread_eclipseq_13.298.862650077 1514938 09:00:57
+ ARCH/prod/archivelog/2014_11_03/thread_2_seq_14.299.862650079 1514995 09:01:10
+ ARCH/prod/archivelog/2014_11_03/thread_eclipseq_14.300.862650089 1514999 09:01:16
+ ARCH/prod/archivelog/2014_11_03/thread_eclipseq_15.301.862653569 1515027 09:01:28
+ ARCH/prod/archivelog/2014_11_03/thread_2_seq_15.302.862653567 1515017 09:01:19
+ ARCH/prod/archivelog/2014_11_03/thread_2_seq_16.303.862653581 1526456 09:59:27
+ ARCH/prod/archivelog/2014_11_03/thread_eclipseq_16.304.862653587 1526452 09:59:29
+ ARCH/prod/archivelog/2014_11_03/thread_eclipseq_17.305.862653599 1526496 09:59:47

6. Delete the 1 million data records that have been successfully copied by the target end.
SQL> truncate table t1;

7. The extraction process extracts the transaction again from 17:41:26
GGSCI (node3) 2> stop ex1

GGSCI (node3) 3> alter extract ex1, TRANLOG, BEGIN 2014-11-02 17:41:26

GGSCI (node3) 5> start ex1

Sending START request to MANAGER...
EXTRACT EX1 starting

GGSCI (node3) 6> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
Extract running DP1 00:00:00 <the latency is gradually decreasing
Extract running EX1 19:41:40

GGSCI (node3) 8> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
Extract running DP1 03:20:53 <the latency is gradually decreasing
Extract running EX1 03:20:52

GGSCI (node3) 9> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
Extract running DP1 03:20:53 <the latency is gradually decreasing
Extract running EX1 00:00:01

 

The target end replicated 1 million records again.
SQL> select count (*) from t1;

COUNT (*)
----------
1000000

GoldenGate unidirectional table DML Synchronization

Oracle GoldenGate series: restoration principles of Extract processes

Oracle GoldenGate installation Configuration

OGG-01004 OGG-1296 error for Oracle goldengate

Oracle GoldenGate Quick Start Tutorial: Basic Concepts and configurations