关于Oracle GoldenGate中Extract的checkpoint的理解
浏览数：33 /
时间：2015年06月12日
什么是checkpoint？
在Oracle 数据库中checkpoint的意思是将内存中的脏数据强制写入到磁盘的事件,其作用是保持内存中的数据与磁盘上的数据一致。SCN是用来描述该事件发生的准确的时间点。
而GoldenGate中出现的checkpoint有着不同的含义。简单的说就是position，位置的意思。它记录了Extract进程在抽取事务时的进度。
使用INFO <Extract name> 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 <Extract name> 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
实验结束
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
