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
實驗步驟：
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29960155/viewspace-1320193/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用goldengate交付指定時間前的資料Go
- goldengate抽取程式延遲90小時Go
- rman恢復到指定時間點
- 基於事務日誌還原到時間點
- iOS時間那點事--NSDateiOS
- db2恢復到指定時間點DB2
- JavaScript實時變化時間日期JavaScript
- JavaScript實時變化的時間JavaScript
- Win10更新補丁後如何指定重新啟動時間Win10
- GoldenGate表的重新再同步（無需生產端時間視窗)Go
- rman 刪除指定時間點的歸檔日誌
- javascript實現的按鈕間隔指定時間再能點選JavaScript
- GoldenGate準確修改抽取程式checkpointGo
- OGG初始單表的方法 goldengate–使用filter+@GETENV線上重新初始化指定的tableGoFilter
- golang根據配置的時間和時區計算定時任務是否到了重新整理時間Golang
- restore、recover到指定時間REST
- 網頁每隔指定的時間重新整理一次例項程式碼網頁
- zt_Oracle事務rollback回滾時間估算Oracle
- Goldengate 抽取程式的常用新增方法Go
- goldengate中長事務引起的問題Go
- Oracle Goldengate重新初始化的 3種方法OracleGo
- 計算時間，指定時間的多少個月後
- MySQL使用bin-log異庫恢復到指定時間點MySql
- Bash: sleep - 延遲指定時間
- 使用flashback query巧妙抽取指定資料
- GoldenGate新增加表進行資料抽取Go
- js實現指定時間倒數計時JS
- DM7使用dmrestore執行指定時間點或LSN還原REST
- 通過事務日誌恢復SqlServer資料庫到一個特定的時間點SQLServer資料庫
- jwt_token的有效時間和重新整理時間JWT
- GoldenGate抽取Informix資料庫安裝及配置GoORM資料庫
- skip a transaction in goldengate(跳過一個事務OGG)Go
- Oracle goldengate初始化資料注意事項OracleGo
- 檢查點SCN在事務發生後以及發生檢查點和歸檔操作後的變化
- 改變ogg抽取程式檢查點檔案中的檢查點
- js獲取指定時間日期和當前時間日期的相差多少時間JS
- 白樹高地奇遇任務NPC重新整理時間地點大全 明日之後白樹高地NPC位置
- DM7使用DMRMAN恢復資料庫到指定時間點/LSN資料庫