GoldenGate 指定时间点重新抽取事务变化
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, 根据SCN1507066，查找相对应时间。
所在归档日志为红色表示记录。该日志生成的时间为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
GoldenGate单向表DML同步
Oracle GoldenGate 系列：Extract 进程的恢复原理
Oracle GoldenGate安装配置
Oracle goldengate的OGG-01004 OGG-1296错误
Oracle GoldenGate快速入门教程：基本概念和配置
评论暂时关闭