【背景】
最近有朋友在群里面咨询oracle resetlogs后，goldengate无法继续抽取日志，如何能够继续工作.
这个文档在2014年写的，时间过去5年多了，虽然没有走上全职ogg岗位，在日常使用比较多，重新通过公众号发出来.Oracle database resetlogs 之后,sequence 直接从1 开始了，同时resetlogs_id 也变化了.这个goldengate extract 进程一直处理等在状态.查看当前磁盘上可能还存在尚未处理的日志，但是goldengate 就是不动.这个时候如何处理剩下尚未处理的归档同时处理新的resetlogs_id下产生日志了.下面将通过实验展示处理过程.其中包括classic extract 和 integrated extract 2者对于resetlogs_id 的适应性吧.其中integrated extract 可以无缝将resetlogs 之前和之后链接起来，但是classic extract 需要手动干预的.
【测试环境】
Database version:11.2.0.4 文件系统单实例
Goldengate version:11.2.1.0.5
【当前环境】
GGSCI (target-primary) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTINT 00:00:00 00:00:08
EXTRACT RUNNING EXTINTED 00:00:10 00:00:03
GGSCI (target-primary) 2> info EXTINT
EXTRACT EXTINT Last Started 2014-08-21 11:44 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Log Read Checkpoint Oracle Redo Logs --classic extract (默认的)
2014-08-21 14:28:01 Seqno 2, RBA7415808
SCN 0.7115298 (7115298)
GGSCI (target-primary) 3> info EXTINTED
EXTRACT EXTINTED Last Started 2014-08-2111:06 Status RUNNING
Checkpoint Lag 00:00:10 (updated 00:00:08 ago)
Log Read Checkpoint Oracle Integrated Redo Logs --integated extract
2014-08-21 14:27:58
SCN 0.7115297 (7115297)
【Classic extract 和integrated extract 差别】
1、 logread checkpoint:后面是否有integrated关键字,有的话就是integrated，否则就是classic
2、 integrated输出没有读到那个sequence，只显示scn，但是classic extract显示sequence同时还有scn.这个机制导致resetlogs之后是否需要手动干预的原因.就是extract依靠equence来顺序读取，但是integrated是按照scn来顺序读取的,其实进过logminer server处理后的结果.
3、如果配置ddl，integrated extract在11.2.0.4 DB开始ddl是采用数据字典方式，而不是基于触发器方式.
4、integrated extract支持数据类型也比classic extract多.以及rac下分布式事务支持
5、Integrated Extract 是GOLDENGATE 11.2 新功能，EXTRACT 直接从logmining server获取lcr信息.
总之2者实现完全不同，集成模式使用oracle流复制接口来与数据库结合更加紧密.
【插入数据验证】
目前查看都没有发生变化数据.
GGSCI (target-primary) 18> stats EXTINT total
Sending STATS request to EXTRACT EXTINT ...
Start of Statistics at 2014-08-21 14:37:05.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 0.00
Mapped operations 0.00
Unmappedoperations 0.00
Otheroperations 0.00
Excludedoperations 0.00
Output to ./dirdat/it:
Extracting from OGG.GGS_MARKER to OGG.GGS_MARKER:
*** Total statistics since 2014-08-21 14:36:42 ***
No databaseoperations have been performed.
End of Statistics.
GGSCI (target-primary) 19> stats EXTINTED total
Sending STATS request to EXTRACT EXTINTED ...
No active extraction maps
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 0.00
Mapped operations 0.00
Unmappedoperations 0.00
Otheroperations 0.00
Excludedoperations 0.00
【插入数据】
SQL> select count(*) from resetlog;
COUNT(*)
----------
16
SQL> insert into resetlog select * from resetlog;
16 rows created.
SQL> commit;
Commit complete.
验证结果发现,classic extract and integrated extract都已经捕获16条插入数据.
GGSCI (target-primary) 21> stats EXTINTED total
Sending STATS request to EXTRACT EXTINTED ...
Start of Statistics at 2014-08-21 14:39:11.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 0.00
Mappedoperations 0.00
Unmappedoperations 0.00
Otheroperations 0.00
Excludedoperations 0.00
Output to ./dirdat/et:
Extracting from TEST.RESETLOG to TEST.RESETLOG:
*** Total statistics since 2014-08-21 14:39:06 ***
Totalinserts 16.00
Totalupdates 0.00
Totaldeletes 0.00
Totaldiscards 0.00
Totaloperations 16.00
End of Statistics.
GGSCI (target-primary) 22> stats EXTINT total
Sending STATS request to EXTRACT EXTINT ...
Start of Statistics at 2014-08-21 14:39:19.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 0.00
Mappedoperations 0.00
Unmappedoperations 0.00
Otheroperations 0.00
Excludedoperations 0.00
Output to ./dirdat/it:
Extracting from OGG.GGS_MARKER to OGG.GGS_MARKER:
*** Total statistics since 2014-08-21 14:36:42 ***
No databaseoperations have been performed.
Extracting from TEST.RESETLOG to TEST.RESETLOG:
*** Total statistics since 2014-08-21 14:36:42 ***
Totalinserts 16.00
Totalupdates 0.00
Totaldeletes 0.00
Totaldiscards 0.00
Totaloperations 16.00
End of Statistics.
停止classic 和integrated extract
GGSCI (target-primary) 24> stop EXTINT
Sending STOP request to EXTRACT EXTINT ...
Request processed.
GGSCI (target-primary) 25> stop EXTINTED
Sending STOP request to EXTRACT EXTINTED ...
Request processed.
【插入数据后,resetlogs数据库后启动extract是否捕获插入的数据】
插入96条数据:
SQL> insert into resetlog select * from resetlog;
32 rows created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> insert into resetlog select * from resetlog;
64 rows created.
Commit complete.
SQL> alter system switch logfile;
System altered.
记录extract停止之前的状态
GGSCI (target-primary) 27> info EXTINT
EXTRACT EXTINT Last Started 2014-08-21 14:36 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:02:34 ago)
Log Read Checkpoint Oracle Redo Logs
2014-08-21 14:40:37 Seqno 2, RBA7967744
SCN 0.7116549 (7116549)
GGSCI (target-primary) 28> info EXTINTED
EXTRACT EXTINTED Last Started 2014-08-2114:36 Status STOPPED
Checkpoint Lag 00:00:06 (updated 00:02:32 ago)
Log Read Checkpoint Oracle Integrated Redo Logs
2014-08-21 14:40:41
SCN 0.7116552 (7116552)
查看数据库,记录current_scn：
SQL> selectresetlogs_change#,prior_resetlogs_change#,current_scn,flashback_on fromv$database;
RESETLOGS_CHANGE# PRIOR_RESETLOGS_CHANGE# CURRENT_SCNFLASHBACK_ON
----------------- ----------------------- -----------------------------
7103876 925702 7116702 YES
SQL> conn as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 738200616 bytes
Database Buffers 96468992 bytes
Redo Buffers 2355200 bytes
Database mounted.
SQL> flashback database to scn 7116687;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
启动extract进程
GGSCI (target-primary) 30> start EXTINT
Sending START request to MANAGER ...
EXTRACT EXTINT starting
GGSCI (target-primary) 32> start EXTINTED
Sending START request to MANAGER ...
EXTRACT EXTINTED starting
查看进程是否工作
GGSCI (target-primary) 33> info EXTINT
EXTRACT EXTINT Last Started 2014-08-21 14:48 Status RUNNING
Checkpoint Lag 00:08:15 (updated 00:00:06 ago)
Log Read Checkpoint Oracle Redo Logs
2014-08-21 14:40:37 Seqno 2, RBA 7967744
SCN 0.7116549 (7116549)
GGSCI (target-primary) 35> info EXTINTED
EXTRACT EXTINTED Last Started 2014-08-2114:36 Status RUNNING
Checkpoint Lag 00:00:06 (updated 00:08:18 ago)
Log Read Checkpoint Oracle Integrated Redo Logs
2014-08-21 14:40:41
SCN 0.7116552(7116552)
GGSCI (target-primary) 46> info EXTINT
EXTRACT EXTINT Last Started 2014-08-21 14:48 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Log Read Checkpoint Oracle Redo Logs
2014-08-21 14:40:37 Seqno 2, RBA 7967744 --没有变化
SCN 0.7116549 (7116549)
GGSCI (target-primary) 47> info EXTINTED
EXTRACT EXTINTED Last Started 2014-08-2114:49 Status RUNNING
Checkpoint Lag 00:07:34 (updated 00:00:00 ago)
Log Read Checkpoint Oracle Integrated Redo Logs
2014-08-21 14:42:00
SCN 0.7116613(7116613)—变化
【查看是否捕获到resetlogs之前数据】
发现classicextract变化数据为0,integratedextract变化数据为96,刚好与之前数据一致的.
接下来如何手动处理classicextract extint进程,如何继续捕获之前数据和resetlogs之后数据.
将下面要演示的:
GGSCI (target-primary) 49> stats EXTINT total
Sending STATS request to EXTRACT EXTINT ...
No active extraction maps
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 0.00
Mappedoperations 0.00
Unmappedoperations 0.00
Otheroperations 0.00
Excludedoperations 0.00
.
GGSCI (target-primary) 50> stats EXTINTED total
Sending STATS request to EXTRACT EXTINTED ...
Start of Statistics at 2014-08-21 14:51:54.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 0.00
Mappedoperations 0.00
Unmappedoperations 0.00
Otheroperations 0.00
Excludedoperations 0.00
Output to ./dirdat/et:
Extracting from TEST.RESETLOG to TEST.RESETLOG:
*** Total statistics since 2014-08-21 14:49:34 ***
Totalinserts 96.00
Totalupdates 0.00
Totaldeletes 0.00
Total discards 0.00
Totaloperations 96.00
End of Statistics.
处理classicextract extint进程:
GGSCI (target-primary) 52> info EXTINT
EXTRACT EXTINT Last Started 2014-08-21 14:48 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint Oracle Redo Logs
2014-08-21 14:40:37 Seqno 2, RBA7967744
SCN 0.7116549 (7116549)
查看database情况：
发现一共有4次resetlogs.当前resetlogs_id就是856190858
SQL> select INCARNATION#,RESETLOGS_TIME,RESETLOGS_ID fromv$database_incarnation;
INCARNATION# RESETLOGS_TIME RESETLOGS_ID
------------ ------------------- ------------
12013-08-24 11:37:30 824297850
22014-06-13 10:54:46 850128886
32014-08-21 11:04:32 856177472
42014-08-21 14:47:38 856190858
通过856190858发现，目前尚未产生新归档.
SQL> select max(sequence#),resetlogs_id fromv$archived_log group by resetlogs_id;
MAX(SEQUENCE#) RESETLOGS_ID
-------------- ------------
447 850128886
4 856177472
SQL> select sysdate from dual;
SYSDATE
-------------------
2014-08-21 15:01:06
从上一次resetlogs:856177472和归档里面信息，发现最大sequence#是为4.
我们的classicextract extint才到sequence#2就停止工作了.
GGSCI (target-primary) 52> info EXTINT
EXTRACT EXTINT Last Started 2014-08-21 14:48 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint Oracle Redo Logs
2014-08-21 14:40:37 Seqno 2, RBA7967744
SCN 0.7116549 (7116549)
这个时候extractextint什么也不做，也不报错.这时候提示ATEOF,表示没有任何事务要处理,
所以一直都是这个状态.
GGSCI (target-primary) 59> send EXTINT status
Sending STATUS request to EXTRACT EXTINT ...
EXTRACT EXTINT (PID 7182)
Current status: Inrecovery[1]: At EOF
Current readposition:
Redo thread #: 1
Sequence #: 2
RBA: 7966224
Timestamp:2014-08-21 14:40:37.000000
SCN: 0.7116548
Current writeposition:
Sequence #: 7
RBA: 1103
Timestamp:2014-08-21 15:07:54.944916
Extract Trail:./dirdat/it
【只能使用alo模式处理剩下的归档日志】
需要修改classic extract extint参数:
查出归档信息:
发现2、3sequence#出现大小完全一致的,有可能logminerserver都一台机器产生的(猜测)
-rw-r----- 1 oracle oinstall 7992320 Aug 21 14:42 o1_mf_1_2_9zc55yz3_.arc
-rw-r----- 1 oracle oinstall 6144 Aug 21 14:42 o1_mf_1_3_9zc56nxy_.arc
-rw-r----- 1 oracle oinstall 51200 Aug 21 14:47 o1_mf_1_4_9zc5jbk5_.arc
-rw-r----- 1 oracle oinstall 6144 Aug 21 14:47 o1_mf_1_3_9zc5jbpn_.arc
-rw-r----- 1 oracle oinstall 7992320 Aug 21 14:47 o1_mf_1_2_9zc5jblp_.arc
Goldengate tranlogoptions altarchivelogdest 不识别fra自动产生这种格式同时不支持asm磁盘
数据库归档格式:
SQL> show parameter log_archive_format
NAME TYPE VALUE
------------------------------------ -----------------------------------------
log_archive_format string %t_%s_%r.dbf
现在修改成这种格式:
-rw-r----- 1 oracle oinstall 7992320 Aug 21 14:42 o1_mf_1_2_9zc55yz3_.arc
-rw-r----- 1 oracle oinstall 6144 Aug 21 14:42 o1_mf_1_3_9zc56nxy_.arc
-rw-r----- 1 oracle oinstall 51200 Aug 21 14:47 o1_mf_1_4_9zc5jbk5_.arc
%t:表示thread
%s:表示sequence#
%r:表示当前resetlogs_id
SQL> select INCARNATION#,RESETLOGS_TIME,RESETLOGS_ID fromv$database_incarnation;
INCARNATION# RESETLOGS RESETLOGS_ID
------------ --------- ------------
124-AUG-13 824297850
213-JUN-14 850128886
321-AUG-14 856177472
421-AUG-14 856190858
[oracle@target-primary 2014_08_21]$ mvo1_mf_1_2_9zc55yz3_.arc 1_2_856190858.dbf
[oracle@target-primary 2014_08_21]$ mvo1_mf_1_3_9zc56nxy_.arc 1_3_856190858.dbf
[oracle@target-primary 2014_08_21]$ mvo1_mf_1_4_9zc5jbk5_.arc 1_4_856190858.dbf
修改extractextint参数
添加如下参数并重启，确认是否捕获之前96条记录
tranlogoptions archivedlogonly
tranlogoptions altarchivelogdest primary u01/oracle/fast_recovery_area/TEST/archivelog/2014_08_21
GGSCI (target-primary) 63> start EXTINT
Sending START request to MANAGER ...
EXTRACT EXTINT starting
GGSCI (target-primary) 64> info EXTINT
EXTRACT EXTINT Last Started 2014-08-21 15:58 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:06 ago)
Log Read Checkpoint Oracle Redo Logs
2014-08-21 14:40:37 Seqno 2, RBA7967744
SCN 0.7116549 (7116549)
间隔1-2分钟: 发现sequence#,rba都变化且捕获到数据.
GGSCI (target-primary) 89> info EXTINT
EXTRACT EXTINT Last Started 2014-08-21 15:59 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint Oracle Redo Logs
2014-08-21 14:46:44 Seqno 4, RBA51200
SCN 0.7116769 (7116769)
GGSCI (target-primary) 90> stats EXTINT total
Sending STATS request to EXTRACT EXTINT ...
Start of Statistics at 2014-08-21 16:00:09.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 0.00
Mapped operations 0.00
Unmappedoperations 0.00
Otheroperations 0.00
Excludedoperations 0.00
Output to ./dirdat/it:
Extracting from TEST.RESETLOG to TEST.RESETLOG:
*** Total statistics since 2014-08-21 15:59:48 ***
Totalinserts 96.00
Totalupdates 0.00
Total deletes 0.00
Totaldiscards 0.00
Totaloperations 96.00
End of Statistics.
现在在重新插入数据看下：classicextract extint 和integrated extract extinted是否都能正常工作?
发现integratedextract extinted无需任何修改都可以正常工作，但是classicextract extint不行,
因为resetlogs之后，sequence#已经变成，所以需要手动修改extract extint>
SQL> insert into resetlog select * from resetlog;
128 rows created.
SQL> commit;
Commit complete.
SQL>
GGSCI (target-primary) 92> stats EXTINT total
Sending STATS request to EXTRACT EXTINT ...
Start of Statistics at 2014-08-21 16:03:18.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 0.00
Mappedoperations 0.00
Unmappedoperations 0.00
Otheroperations 0.00
Excludedoperations 0.00
Output to ./dirdat/it:
Extracting from TEST.RESETLOG to TEST.RESETLOG:
*** Total statistics since 2014-08-21 15:59:48 ***
Totalinserts 96.00
Total updates 0.00
Totaldeletes 0.00
Totaldiscards 0.00
Totaloperations 96.00
End of Statistics.
GGSCI (target-primary) 93> stats EXTINTED total
Sending STATS request to EXTRACT EXTINTED ...
Start of Statistics at 2014-08-21 16:03:24.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 0.00
Mappedoperations 0.00
Unmappedoperations 0.00
Otheroperations 0.00
Excludedoperations 0.00
Output to ./dirdat/et:
Extracting from TEST.RESETLOG to TEST.RESETLOG:
*** Total statistics since 2014-08-21 14:49:34 ***
Totalinserts 224.00
Totalupdates 0.00
Totaldeletes 0.00
Totaldiscards 0.00
Totaloperations 224.00
End of Statistics.
【继续处理extract extint】
1、 去掉alo参数
去掉或注释tranlogoptions参数
2、 Extseqno从1开始
GGSCI (target-primary)96> stop EXTINT
Sending STOP request toEXTRACT EXTINT ...
Request processed.
GGSCI (target-primary)97> alter EXTINT,extseqno 1,extrba 0
EXTRACT altered.
GGSCI (target-primary)98> start EXTINT
Sending START request toMANAGER ...
EXTRACT EXTINT starting
GGSCI (target-primary) 99>
【验证是否捕获到刚才插入数据】
发现捕获到128条记录.处理完成
GGSCI (target-primary) 106> stats EXTINTtotal
Sending STATS request toEXTRACT EXTINT ...
Start of Statistics at2014-08-21 16:07:29.
DDL replication statistics(for all trails):
*** Total statistics sinceextract started ***
Operations 0.00
Mapped operations 0.00
Unmapped operations 0.00
Other operations 0.00
Excluded operations 0.00
Output to ./dirdat/it:
Extracting fromOGG.GGS_MARKER to OGG.GGS_MARKER:
*** Total statistics since2014-08-21 16:06:59 ***
No database operations have beenperformed.
Extracting fromTEST.RESETLOG to TEST.RESETLOG:
*** Total statistics since2014-08-21 16:06:59 ***
Total inserts 128.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 128.00
End of Statistics.
至此处理完成.
【总结】
1、 resetlogs之后,integrated extract不受影响可以继续工作
2、 resetlogs之后,classic extract需要2步处理.
A．处理resetlogs之前尚未处理归档
B．处理resetogs之后数据
