關於Oracle GoldenGate中Extract的checkpoint的理解
什麼是checkpoint？
在Oracle 資料庫中checkpoint的意思是將記憶體中的髒資料強制寫入到磁碟的事件,其作用是保持記憶體中的資料與磁碟上的資料一致。SCN是用來描述該事件發生的準確的時間點。
而GoldenGate中出現的checkpoint有著不同的含義。簡單的說就是position，位置的意思。它記錄了Extract程式在抽取事務時的進度。
使用INFO showch 命令可以檢視當前正在執行的Extract程式中所記錄的checkpoint。
當GoldenGate被計劃內或者是計劃外中斷了，為了保證事務的連續性我們從哪裡續傳呢？答案是：哪裡中斷，哪裡繼續。在一個繁忙的資料庫中用時間戳來衡量OGG抽取進度是不準確的，因為任何一個時間點都有可能截斷了正在處理中的事務。準確的記錄的方法是參考該事物在發起時所在trail檔案的位元組位置（RBA）。
startup checkpoint:
Extract程式啟動時的資訊，用多個引數描述了一個確切的時間點。包括thread,sequence,timestamp,RBA,SCN,redo log
sequence表示redo log、或者archive log的序列號。
RBA表示改時間點處於該sequence號日誌檔案中的位元組地址。
SCN表示Oracle內部時鐘
recovery checkpoint:
到目前為止還沒結束的並且耗時最長的事務資訊。包括thread,sequence,timestamp,RBA,SCN,redo log。
如果源端的抽取程式中斷了，那麼斷點續傳的位置就是這裡了。
current checkpoint:
當前正在讀取的事務資訊。包括thread,sequence,timestamp,RBA,SCN,redo log
write checkpoint：
當前正在寫入的trail檔案資訊。包括佇列號（sequence）,RBA,時間戳，佇列檔案
sequence表示trail檔案的序號，有別於資料庫中的同名概念。例如：trail檔案ex000001的sequence為1
RBA表示當前資訊在trail檔案中的位置，有別於資料庫中事務日誌的RBA。通常這個RBA的數值等於當前正在寫的trail檔案的大小。
可以透過ls -al ./dirdat/ex000001來檢視該檔案的位元組數來對比write checkpoint的RBA
下面透過一個螢幕輸出來對應一下剛才提到的概念。在實驗開始時，還需要再次執行該命令來獲取最新的檢查點資訊。
檢視抽取程式ex1的檢查點：
GGSCI (node3) 9> info ex1 showch
EXTRACT EX1 Last Started 2014-11-03 13:23 Status RUNNING << 程式開始抽取事務的時間，ADD EXTRACT之後就生效了
Checkpoint Lag 00:00:03 (updated 00:00:07 ago)
Log Read Checkpoint Oracle Redo Logs
2014-11-03 15:13:04 Thread 1, Seqno 21, RBA 26012672 << 2個節點的RAC就有2個Checkpoint
SCN 0.1614343 (1614343)
Log Read Checkpoint Oracle Redo Logs
2014-11-03 15:13:02 Thread 2, Seqno 29, RBA 6584832
SCN 0.1614345 (1614345)
Current Checkpoint Detail:
Read Checkpoint #1 << 抽取程式的讀檢查點 節點1
Oracle Threaded Redo Log
Startup Checkpoint (starting position in the data source):
Thread #: 1
Sequence #: 11
RBA: 6084096
Timestamp: 2014-11-02 17:41:26.000000
SCN: Not available
Redo File:
Recovery Checkpoint是我們續傳時關注的位置
Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
Thread #: 1
Sequence #: 21 << 所在佇列序號
RBA: 26012176 << 所在佇列中的具體位置
Timestamp: 2014-11-03 15:13:04.000000 << 事務發起的時間戳
SCN: 0.1614343 (1614343)
Redo File: +DATA/prod/onlinelog/group_1.299.859509355
Current Checkpoint (position of last record read in the data source):
Thread #: 1
Sequence #: 21
RBA: 26012672
Timestamp: 2014-11-03 15:13:04.000000
SCN: 0.1614343 (1614343)
Redo File: +DATA/prod/onlinelog/group_1.299.859509355
Read Checkpoint #2 << 抽取程式的讀檢查點 節點2
Oracle Threaded Redo Log
Startup Checkpoint (starting position in the data source):
Thread #: 2
Sequence #: 11
RBA: 4263936
Timestamp: 2014-11-02 17:41:26.000000
SCN: Not available
Redo File:
Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
Thread #: 2
Sequence #: 29
RBA: 6584336
Timestamp: 2014-11-03 15:13:02.000000
SCN: 0.1614345 (1614345)
Redo File: +DATA/prod/onlinelog/group_3.301.859509357
Current Checkpoint (position of last record read in the data source):
Thread #: 2
Sequence #: 29
RBA: 6584832
Timestamp: 2014-11-03 15:13:02.000000
SCN: 0.1614345 (1614345)
Redo File: +DATA/prod/onlinelog/group_3.301.859509357
Write Checkpoint #1
GGS Log Trail
Current Checkpoint (current write position):
Sequence #: 47
RBA: 1124760
Timestamp: 2014-11-03 15:13:07.093886
Extract Trail: /goldengate/dirdat/ex
CSN state information:
CRC: E4-D6-3B-B4
CSN: Not available
Header:
Version = 2
Record Source = A
Type = 11
# Input Checkpoints = 2
# Output Checkpoints = 1
File Information:
Block Size = 2048
Max Blocks = 100
Record Length = 4096
Current Offset = 0
Configuration:
Data Source = 3
Transaction Integrity = 1
Task Type = 0
Status:
Start Time = 2014-11-03 13:23:05
Last Update Time = 2014-11-03 15:13:07
Stop Status = A
Last Result = 0
當前源端有100萬條記錄
SYS@PROD2 > select count(*) from snow.t1;
COUNT(*)
----------
1000000
系統時間為2014-11-03 15:37:08
SYS@PROD2 > alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SYS@PROD2 > select sysdate from dual;
SYSDATE
-------------------
2014-11-03 15:37:08
源端插入一條新資料，不提交
SYS@PROD2 > insert into snow.t1 values(1000001,'Snow');
1 row created.
SYS@PROD2 >
關閉源端所有Extract程式
GGSCI (node3) 4> stop *
Sending STOP request to EXTRACT DP1 ...
Request processed.
Sending STOP request to EXTRACT EX1 ...
Request processed.
觀察最新的checkpoint資訊
GGSCI (node3) 14> info ex1 showch
EXTRACT EX1 Last Started 2014-11-03 15:41 Status RUNNING
Checkpoint Lag 00:00:02 (updated 00:00:01 ago)
Log Read Checkpoint Oracle Redo Logs
2014-11-03 15:41:48 Thread 1, Seqno 21, RBA 27887616
SCN 0.1620285 (1620285)
Log Read Checkpoint Oracle Redo Logs
2014-11-03 15:41:46 Thread 2, Seqno 29, RBA 7889920
SCN 0.1620284 (1620284)
Current Checkpoint Detail:
Read Checkpoint #1
Oracle Threaded Redo Log
Startup Checkpoint (starting position in the data source):
Thread #: 1
Sequence #: 21
RBA: 27644432
Timestamp: 2014-11-03 15:39:18.000000
SCN: Not available
Redo File: +DATA/prod/onlinelog/group_3.301.859509357
Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
Thread #: 1
Sequence #: 21
RBA: 27887120
Timestamp: 2014-11-03 15:41:48.000000
SCN: 0.1620285 (1620285)
Redo File: +DATA/prod/onlinelog/group_1.299.859509355
Current Checkpoint (position of last record read in the data source):
Thread #: 1
Sequence #: 21
RBA: 27887616
Timestamp: 2014-11-03 15:41:48.000000
SCN: 0.1620285 (1620285)
Redo File: +DATA/prod/onlinelog/group_1.299.859509355
Read Checkpoint #2
Oracle Threaded Redo Log
Startup Checkpoint (starting position in the data source):
Thread #: 2
Sequence #: 29
RBA: 7750160
Timestamp: 2014-11-03 15:39:16.000000
SCN: Not available
Redo File: +DATA/prod/onlinelog/group_3.301.859509357
Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
Thread #: 2
Sequence #: 29
RBA: 7750160
Timestamp: 2014-11-03 15:37:40.000000
SCN: 0.1619666 (1619666)
Redo File: +DATA/prod/onlinelog/group_3.301.859509357
Current Checkpoint (position of last record read in the data source):
Thread #: 2
Sequence #: 29
RBA: 7889920
Timestamp: 2014-11-03 15:41:46.000000
SCN: 0.1620284 (1620284)
Redo File: +DATA/prod/onlinelog/group_3.301.859509357
Write Checkpoint #1
GGS Log Trail
Current Checkpoint (current write position):
Sequence #: 48
RBA: 1076
Timestamp: 2014-11-03 15:41:50.718150
Extract Trail: /goldengate/dirdat/ex
CSN state information:
CRC: CE-DB-CE-78
CSN: Not available
Header:
Version = 2
Record Source = A
Type = 11
# Input Checkpoints = 2
# Output Checkpoints = 1
File Information:
Block Size = 2048
Max Blocks = 100
Record Length = 4096
Current Offset = 0
Configuration:
Data Source = 3
Transaction Integrity = 1
Task Type = 0
Status:
Start Time = 2014-11-03 15:41:29
Last Update Time = 2014-11-03 15:41:50
Stop Status = A
Last Result = 0
修改抽取程式啟動位置，兩個程式
GGSCI (node3) 7> ALTER EXTRACT ex1, EXTSEQNO 21, EXTRBA 27644432 THREAD 1
EXTRACT altered.
GGSCI (node3) 8> ALTER EXTRACT ex1, EXTSEQNO 29, EXTRBA 7750160 THREAD 2
EXTRACT altered.
重啟程式後源端發起提交命名，結束該事務。
SYS@PROD2 > commit;
Commit complete.
第1000001條資料插入完成
SYS@PROD2 > select count(*) from snow.t1;
COUNT(*)
----------
1000001
目標端第1000001條資料複製完成！
SQL> select count(*) from snow.t1;
COUNT(*)
----------
1000001
實驗結束
在Oracle 資料庫中checkpoint的意思是將記憶體中的髒資料強制寫入到磁碟的事件,其作用是保持記憶體中的資料與磁碟上的資料一致。SCN是用來描述該事件發生的準確的時間點。
而GoldenGate中出現的checkpoint有著不同的含義。簡單的說就是position，位置的意思。它記錄了Extract程式在抽取事務時的進度。
使用INFO
當GoldenGate被計劃內或者是計劃外中斷了，為了保證事務的連續性我們從哪裡續傳呢？答案是：哪裡中斷，哪裡繼續。在一個繁忙的資料庫中用時間戳來衡量OGG抽取進度是不準確的，因為任何一個時間點都有可能截斷了正在處理中的事務。準確的記錄的方法是參考該事物在發起時所在trail檔案的位元組位置（RBA）。
startup checkpoint:
Extract程式啟動時的資訊，用多個引數描述了一個確切的時間點。包括thread,sequence,timestamp,RBA,SCN,redo log
sequence表示redo log、或者archive log的序列號。
RBA表示改時間點處於該sequence號日誌檔案中的位元組地址。
SCN表示Oracle內部時鐘
recovery checkpoint:
到目前為止還沒結束的並且耗時最長的事務資訊。包括thread,sequence,timestamp,RBA,SCN,redo log。
如果源端的抽取程式中斷了，那麼斷點續傳的位置就是這裡了。
current checkpoint:
當前正在讀取的事務資訊。包括thread,sequence,timestamp,RBA,SCN,redo log
write checkpoint：
當前正在寫入的trail檔案資訊。包括佇列號（sequence）,RBA,時間戳，佇列檔案
sequence表示trail檔案的序號，有別於資料庫中的同名概念。例如：trail檔案ex000001的sequence為1
RBA表示當前資訊在trail檔案中的位置，有別於資料庫中事務日誌的RBA。通常這個RBA的數值等於當前正在寫的trail檔案的大小。
可以透過ls -al ./dirdat/ex000001來檢視該檔案的位元組數來對比write checkpoint的RBA
下面透過一個螢幕輸出來對應一下剛才提到的概念。在實驗開始時，還需要再次執行該命令來獲取最新的檢查點資訊。
檢視抽取程式ex1的檢查點：
GGSCI (node3) 9> info ex1 showch
EXTRACT EX1 Last Started 2014-11-03 13:23 Status RUNNING << 程式開始抽取事務的時間，ADD EXTRACT之後就生效了
Checkpoint Lag 00:00:03 (updated 00:00:07 ago)
Log Read Checkpoint Oracle Redo Logs
2014-11-03 15:13:04 Thread 1, Seqno 21, RBA 26012672 << 2個節點的RAC就有2個Checkpoint
SCN 0.1614343 (1614343)
Log Read Checkpoint Oracle Redo Logs
2014-11-03 15:13:02 Thread 2, Seqno 29, RBA 6584832
SCN 0.1614345 (1614345)
Current Checkpoint Detail:
Read Checkpoint #1 << 抽取程式的讀檢查點 節點1
Oracle Threaded Redo Log
Startup Checkpoint (starting position in the data source):
Thread #: 1
Sequence #: 11
RBA: 6084096
Timestamp: 2014-11-02 17:41:26.000000
SCN: Not available
Redo File:
Recovery Checkpoint是我們續傳時關注的位置
Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
Thread #: 1
Sequence #: 21 << 所在佇列序號
RBA: 26012176 << 所在佇列中的具體位置
Timestamp: 2014-11-03 15:13:04.000000 << 事務發起的時間戳
SCN: 0.1614343 (1614343)
Redo File: +DATA/prod/onlinelog/group_1.299.859509355
Current Checkpoint (position of last record read in the data source):
Thread #: 1
Sequence #: 21
RBA: 26012672
Timestamp: 2014-11-03 15:13:04.000000
SCN: 0.1614343 (1614343)
Redo File: +DATA/prod/onlinelog/group_1.299.859509355
Read Checkpoint #2 << 抽取程式的讀檢查點 節點2
Oracle Threaded Redo Log
Startup Checkpoint (starting position in the data source):
Thread #: 2
Sequence #: 11
RBA: 4263936
Timestamp: 2014-11-02 17:41:26.000000
SCN: Not available
Redo File:
Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
Thread #: 2
Sequence #: 29
RBA: 6584336
Timestamp: 2014-11-03 15:13:02.000000
SCN: 0.1614345 (1614345)
Redo File: +DATA/prod/onlinelog/group_3.301.859509357
Current Checkpoint (position of last record read in the data source):
Thread #: 2
Sequence #: 29
RBA: 6584832
Timestamp: 2014-11-03 15:13:02.000000
SCN: 0.1614345 (1614345)
Redo File: +DATA/prod/onlinelog/group_3.301.859509357
Write Checkpoint #1
GGS Log Trail
Current Checkpoint (current write position):
Sequence #: 47
RBA: 1124760
Timestamp: 2014-11-03 15:13:07.093886
Extract Trail: /goldengate/dirdat/ex
CSN state information:
CRC: E4-D6-3B-B4
CSN: Not available
Header:
Version = 2
Record Source = A
Type = 11
# Input Checkpoints = 2
# Output Checkpoints = 1
File Information:
Block Size = 2048
Max Blocks = 100
Record Length = 4096
Current Offset = 0
Configuration:
Data Source = 3
Transaction Integrity = 1
Task Type = 0
Status:
Start Time = 2014-11-03 13:23:05
Last Update Time = 2014-11-03 15:13:07
Stop Status = A
Last Result = 0
當前源端有100萬條記錄
SYS@PROD2 > select count(*) from snow.t1;
COUNT(*)
----------
1000000
系統時間為2014-11-03 15:37:08
SYS@PROD2 > alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SYS@PROD2 > select sysdate from dual;
SYSDATE
-------------------
2014-11-03 15:37:08
源端插入一條新資料，不提交
SYS@PROD2 > insert into snow.t1 values(1000001,'Snow');
1 row created.
SYS@PROD2 >
關閉源端所有Extract程式
GGSCI (node3) 4> stop *
Sending STOP request to EXTRACT DP1 ...
Request processed.
Sending STOP request to EXTRACT EX1 ...
Request processed.
觀察最新的checkpoint資訊
GGSCI (node3) 14> info ex1 showch
EXTRACT EX1 Last Started 2014-11-03 15:41 Status RUNNING
Checkpoint Lag 00:00:02 (updated 00:00:01 ago)
Log Read Checkpoint Oracle Redo Logs
2014-11-03 15:41:48 Thread 1, Seqno 21, RBA 27887616
SCN 0.1620285 (1620285)
Log Read Checkpoint Oracle Redo Logs
2014-11-03 15:41:46 Thread 2, Seqno 29, RBA 7889920
SCN 0.1620284 (1620284)
Current Checkpoint Detail:
Read Checkpoint #1
Oracle Threaded Redo Log
Startup Checkpoint (starting position in the data source):
Thread #: 1
Sequence #: 21
RBA: 27644432
Timestamp: 2014-11-03 15:39:18.000000
SCN: Not available
Redo File: +DATA/prod/onlinelog/group_3.301.859509357
Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
Thread #: 1
Sequence #: 21
RBA: 27887120
Timestamp: 2014-11-03 15:41:48.000000
SCN: 0.1620285 (1620285)
Redo File: +DATA/prod/onlinelog/group_1.299.859509355
Current Checkpoint (position of last record read in the data source):
Thread #: 1
Sequence #: 21
RBA: 27887616
Timestamp: 2014-11-03 15:41:48.000000
SCN: 0.1620285 (1620285)
Redo File: +DATA/prod/onlinelog/group_1.299.859509355
Read Checkpoint #2
Oracle Threaded Redo Log
Startup Checkpoint (starting position in the data source):
Thread #: 2
Sequence #: 29
RBA: 7750160
Timestamp: 2014-11-03 15:39:16.000000
SCN: Not available
Redo File: +DATA/prod/onlinelog/group_3.301.859509357
Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
Thread #: 2
Sequence #: 29
RBA: 7750160
Timestamp: 2014-11-03 15:37:40.000000
SCN: 0.1619666 (1619666)
Redo File: +DATA/prod/onlinelog/group_3.301.859509357
Current Checkpoint (position of last record read in the data source):
Thread #: 2
Sequence #: 29
RBA: 7889920
Timestamp: 2014-11-03 15:41:46.000000
SCN: 0.1620284 (1620284)
Redo File: +DATA/prod/onlinelog/group_3.301.859509357
Write Checkpoint #1
GGS Log Trail
Current Checkpoint (current write position):
Sequence #: 48
RBA: 1076
Timestamp: 2014-11-03 15:41:50.718150
Extract Trail: /goldengate/dirdat/ex
CSN state information:
CRC: CE-DB-CE-78
CSN: Not available
Header:
Version = 2
Record Source = A
Type = 11
# Input Checkpoints = 2
# Output Checkpoints = 1
File Information:
Block Size = 2048
Max Blocks = 100
Record Length = 4096
Current Offset = 0
Configuration:
Data Source = 3
Transaction Integrity = 1
Task Type = 0
Status:
Start Time = 2014-11-03 15:41:29
Last Update Time = 2014-11-03 15:41:50
Stop Status = A
Last Result = 0
修改抽取程式啟動位置，兩個程式
GGSCI (node3) 7> ALTER EXTRACT ex1, EXTSEQNO 21, EXTRBA 27644432 THREAD 1
EXTRACT altered.
GGSCI (node3) 8> ALTER EXTRACT ex1, EXTSEQNO 29, EXTRBA 7750160 THREAD 2
EXTRACT altered.
重啟程式後源端發起提交命名，結束該事務。
SYS@PROD2 > commit;
Commit complete.
第1000001條資料插入完成
SYS@PROD2 > select count(*) from snow.t1;
COUNT(*)
----------
1000001
目標端第1000001條資料複製完成！
SQL> select count(*) from snow.t1;
COUNT(*)
----------
1000001
實驗結束
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29863023/viewspace-1331083/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- goldengate的extract程式調優Go
- Oracle中的checkpoint程式Oracle
- Oracle中checkpoint存在的意義Oracle
- 關於Oracle的redo和undo的理解Oracle
- 關於oracle中的undoOracle
- GoldenGate 不支援從oracle 11g的 active dataguard 目的端進行extractGoOracle
- 對checkpoint的理解(轉載)
- 寫的很好的關於GoldenGate的文章Go
- 理解TimesTen中兩個CheckPoint檔案與Trans Log的關係及其原理
- Oracle GoldenGate 系列：Extract 程式遇長事務執行 Forcestop 引發的慘案OracleGo
- 關於react中setState的深入理解React
- 關於 oracle 外來鍵引用 與 goldengateOracleGo
- 關於oracle中的row pieceOracle
- 關於oracle補充日誌作用的理解Oracle
- OGG EXTRACT / REPLICAT CHECKPOINT RBA IS LARGER THAN LOCAL TRAIL SIZEAI
- 對“checkpoint not completed！”的理解 (zt)
- 關於MySQL中的自聯結的通俗理解MySql
- php中的extract函式PHP函式
- goldengate關於pump程式的解釋Go
- 關於Dart中Future的一些理解Dart
- 關於Swift中Properties的一些理解Swift
- php中關於會話機制的理解PHP會話
- 關於Vue中插槽的理解和總結Vue
- 關於 DOM 的理解
- 關於Vuex的理解Vue
- 關於servlet的理解Servlet
- 關於-this指向的理解
- 關於DCI的理解
- 關於oracle中的反連線Oracle
- 關於oracle中的半連線Oracle
- Oracle中關於函式的使用Oracle函式
- oracle中關於null的定義OracleNull
- GoldenGate準確修改抽取程式checkpointGo
- 關於引用（python中的偽指標）的理解Python指標
- 關於log file switch and checkpoint機制
- OGG 中replicat 和extract 關係圖
- 關於v$process與v$session中process的理解Session
- 關於Oracle GoldenGate 引數TRANLOGOPTIONS altarchivelogdestOracleGoHive