oracle resetlogs后goldengate如何继续工作

【背景】

最近有朋友在群里面咨询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之后数据