Goldengate 指定时间点重新抽取事务变化

今天的实验是演示goldengate指定时间点重新抽取事务。

实验步骤：
1，制定一个重新抽取的SCN
2，根据SCN在v$archived_log视图找到起始时间
3，停止抽取进程
4，重置抽取进程的开始时间
5，启动抽取进程重新捕获事务（从归档日志中获取）

1，查看源端当前的SCN，记录该值作为重新抽取的时间点
SQL> select dbms_flashback.get_system_change_number from dual;

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

2，查看当前抽取进程ex1的状态
GGSCI (node3) 2> info ex1 detail

EXTRACT    EX1       Last Started 2014-11-02 18:19   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:06 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2014-11-02 19:09:40  Thread 1, Seqno 12, RBA 10750464
                     SCN 0.1507682 (1507682)
Log Read Checkpoint  Oracle Redo Logs
                     2014-11-02 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 *   2014-11-02 18:02

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，查看当前系统时间以及目前存在的local trail文件

[oracle@single goldengate]$ ls -l dirdat
total 424
-rw-rw-rw- 1 oracle oinstall 205826 Nov  1 16:02 rt000000
-rw-rw-rw- 1 oracle oinstall   1192 Nov  1 16:57 rt000001
-rw-rw-rw- 1 oracle oinstall   1161 Nov  1 17:20 rt000002

4，在源端节点2登录测试用户snow，向测试表插入100万条记录
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  /

目标端snow.t1成功复制100万条记录
SQL> conn snow/snow
Connected.
SQL> select count(*) from t1;

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

插入100条数据使得源端生成了22个local trail文件，从ex000003-ex000024

[oracle@node3 dirdat]$ ll
total 104128
-rw-rw-rw- 1 oracle oinstall  205795 Nov  2 17:24 ex000000
-rw-rw-rw- 1 oracle oinstall    1076 Nov  2 18:19 ex000001
-rw-rw-rw- 1 oracle oinstall 4999907 Nov  3 10:02 ex000003
-rw-rw-rw- 1 oracle oinstall 4999952 Nov  3 10:02 ex000004
-rw-rw-rw- 1 oracle oinstall 4999951 Nov  3 10:02 ex000005
-rw-rw-rw- 1 oracle oinstall 4999951 Nov  3 10:02 ex000006
-rw-rw-rw- 1 oracle oinstall 4999951 Nov  3 10:02 ex000007
-rw-rw-rw- 1 oracle oinstall 4999951 Nov  3 10:02 ex000008
-rw-rw-rw- 1 oracle oinstall 4999951 Nov  3 10:02 ex000009
-rw-rw-rw- 1 oracle oinstall 4999951 Nov  3 10:02 ex000010
-rw-rw-rw- 1 oracle oinstall 4999951 Nov  3 10:02 ex000011
-rw-rw-rw- 1 oracle oinstall 4999951 Nov  3 10:02 ex000012
-rw-rw-rw- 1 oracle oinstall 4999951 Nov  3 10:02 ex000013
-rw-rw-rw- 1 oracle oinstall 4999951 Nov  3 10:02 ex000014
-rw-rw-rw- 1 oracle oinstall 4999951 Nov  3 10:02 ex000015
-rw-rw-rw- 1 oracle oinstall 4999951 Nov  3 10:02 ex000016
-rw-rw-rw- 1 oracle oinstall 4999951 Nov  3 10:02 ex000017
-rw-rw-rw- 1 oracle oinstall 4999951 Nov  3 10:02 ex000018
-rw-rw-rw- 1 oracle oinstall 4999951 Nov  3 10:02 ex000019
-rw-rw-rw- 1 oracle oinstall 4999951 Nov  3 10:02 ex000020
-rw-rw-rw- 1 oracle oinstall 4999951 Nov  3 10:02 ex000021
-rw-rw-rw- 1 oracle oinstall 4999951 Nov  3 10:02 ex000022
-rw-rw-rw- 1 oracle oinstall 4999951 Nov  3 10:02 ex000023
-rw-rw-rw- 1 oracle oinstall 1122278 Nov  3 10:02 ex000024

5, 根据SCN 1507066 ，查找相对应时间。 

所在归档日志为红色表示记录。该日志生成的时间为 2014-11-02 17:41:26 ，接下来采用该时间作为重新生成local trail的参数。

NAME                                                                        FIRST_CHANGE# FIRST_TIME
--------------------------------------------------------------------------- ------------- -------------------
+ARCH/prod/archivelog/2014_11_03/thread_2_seq_12.295.862650053                    1484800 2014-11-02 17:41:26
+ARCH/prod/archivelog/2014_11_03/thread_1_seq_12.296.862650059                     1484803 2014-11-02 17:41:26
+ARCH/prod/archivelog/2014_11_03/thread_2_seq_13.297.862650071                    1514934 2014-11-03 09:00:52
+ARCH/prod/archivelog/2014_11_03/thread_1_seq_13.298.862650077                    1514938 2014-11-03 09:00:57
+ARCH/prod/archivelog/2014_11_03/thread_2_seq_14.299.862650079                    1514995 2014-11-03 09:01:10
+ARCH/prod/archivelog/2014_11_03/thread_1_seq_14.300.862650089                    1514999 2014-11-03 09:01:16
+ARCH/prod/archivelog/2014_11_03/thread_1_seq_15.301.862653569                    1515027 2014-11-03 09:01:28
+ARCH/prod/archivelog/2014_11_03/thread_2_seq_15.302.862653567                    1515017 2014-11-03 09:01:19
+ARCH/prod/archivelog/2014_11_03/thread_2_seq_16.303.862653581                    1526456 2014-11-03 09:59:27
+ARCH/prod/archivelog/2014_11_03/thread_1_seq_16.304.862653587                    1526452 2014-11-03 09:59:29
+ARCH/prod/archivelog/2014_11_03/thread_1_seq_17.305.862653599                    1526496 2014-11-03 09:59:47


6，删除目标端已经成功复制的100万条数据
SQL> truncate table t1;


7，抽取进程从2014-11-02 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      00:00:04   <
EXTRACT     RUNNING     EX1         19:41:40      00:00:03


GGSCI (node3) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DP1         03:20:53      00:00:05   <
EXTRACT     RUNNING     EX1         03:20:52      00:00:06

GGSCI (node3) 9> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DP1         03:20:53      00:00:10  <
EXTRACT     RUNNING     EX1         00:00:01      00:00:00



目标端重新复制了100万条记录
SQL> select count(*) from t1;

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29047826/viewspace-1319396/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29047826/viewspace-1319396/