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快速入門教程：基本概念和配置
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
