OGG导致归档无法RMAN删除一例

用户的SIEBEL RAC系统中配置了OGG 11.1.1.1.2 ,在最近发现备份脚本未正常将归档日志备份后删除掉,示例日志如下:

 

iece handle=al_18090002_1_848331814 tag=TAG20140523T154330 comment=API Version 2.0,MMS Version 5.0.0.0
channel ch00: backup set complete, elapsed time: 00:01:35
channel ch00: deleting archived log(s)
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/ora/archivelog/2014_05_21/o1_mf_2_171530_1cccD0E1h_.arc thread=2 sequence=171530
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/ora/archivelog/2014_05_21/o1_mf_2_171531_1cccD0VDr_.arc thread=2 sequence=171531
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/ora/archivelog/2014_05_21/o1_mf_1_84695_1cccD0K8I_.arc thread=1 sequence=84695
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/ora/archivelog/2014_05_21/o1_mf_2_171532_1cccD0C2h_.arc thread=2 sequence=171532
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/ora/archivelog/2014_05_21/o1_mf_2_171533_1cccD0Y2l_.arc thread=2 sequence=171533
channel ch00: starting archived log backup set
channel ch00: specifying archived log(s) in backup set
input archived log thread=1 sequence=84696 RECID=261042 STAMP=848117115
input archived log thread=2 sequence=171534 RECID=261053 STAMP=848117139
input archived log thread=2 sequence=171535 RECID=261051 STAMP=848117138
input archived log thread=2 sequence=171536 RECID=261054 STAMP=848117139
input archived log thread=1 sequence=84697 RECID=261048 STAMP=848117125
channel ch00: starting piece 1 at 23-MAY-14
channel ch00: finished piece 1 at 23-MAY-14
piece handle=al_18090003_1_848331909 tag=TAG20140523T154330 comment=API Version 2.0,MMS Version 5.0.0.0
channel ch00: backup set complete, elapsed time: 00:01:35
channel ch00: deleting archived log(s)
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

 

 

针对该问题用户提交了SR,并禁用了TRANLOGOPTIONS LOGRETENTION DISABLED特性:

 

 

USERID ggs, password ************************************************ , ENCRYPTKEY default
exttrail ./dirdat/or
--HANDLECOLLISIONS
--FETCHOPTIONS FETCHPKUPDATECOLS
--FETCHOPTIONS, MISSINGROW IGNORE, NOFETCH
THREADOPTIONS OUTQUEUESIZE 512 INQUEUESIZE 1024
--TRANLOGOPTIONS _NOREADAHEAD ANY
TRANLOGOPTIONS LOGRETENTION DISABLED
--EOFDELAY 30
--BR BROFF
GETUPDATEBEFORES

但仍无法有效删除对应归档日志,相关的Note:

1. Disable logretention.

2. Make sure the capture process is removed from dba_capture.

3. Please follow the solution as per below document to cleanup the Orphan entries from metadata tables.

Ora-1 ''Unique Constraint (System.Logmnr_session_uk1) Violated'' During Streams Config (Doc ID 413774.1)

4. Please enable logretention which recreates the capture process again.
Make sure it has only one session for logminer.
Registered Extract - Archivelog Delete Problem (Doc ID 1487374.1)

 

 

 

这个case通过delete force 强制删除命令绕过了,例如:

 delete force archivelog sequence 171532 thread 2 ;

 

 

对于备份后delete input 的 删除也可以使用:

backup archivelog until time 'sysdate-1' delete input force;

【Goldengate性能优化】优化Extract抽取进程性能,解决OGG抽取日志延迟

一般来说OGG Goldengate 抽取进程对CPU的压力非常小, 而对于I/O 、network的吞吐量有轻量级的要求。

用低配置AIX测试结果如下。

抽取进程支持DB Log生成峰值速度 = 4 * 2.1 = 8.4 MB/秒,或30GB/小时,或726 GB/天。
抽取进程平均CPU占用1.9% 。

投递进程支持DB Log生成平均速度 = 2,096,854 * 2.1 = 4.5 MB/秒,或16 GB/小时,或380 GB/天。
投递进程平均CPU占用7% 。

 

 

对于Extract抽取日志缓慢导致延迟的问题,优先采用如下方法诊断具体慢在 抽取 还是 写trail上:

 

1. 收集原始慢的Extract的性能信息

GGSCI> stats extract <extract_name>, totalsonly *, reportrate sec
GGSCI> stats extract <extract_name>, totalsonly *, reportrate min

 

2. 创建一个新的extract 参数文件

cp <extract_name>.prm ETEST.prm

3. 修改上述 etest params file中的extract名字 和 trail 位置

 

4. 加入TESTMAPPINGSPEED 参数到 etest的params files

TESTMAPPINGSPEED参数的作用是 不让extract 去写trail 文件 而仅仅抽取日志, 若加入该参数后抽取速度大幅提升则说明性能瓶颈在 write trail上

TESTMAPPINGSPEED
REPORTCOUNT EVERY 5000 RECORDS

 

5. 增加etest这个extract

GGSCI> add extract etest, tranlog, begin now

GGSCI> add exttrail ./dirdat/ma , extract etest , megabytes 200

 

6. 为etest指定 原始extract 存在抽取速度问题的archivelog 的sequence

GGSCI> alter extract etest, extseqno <arch_seq_no>, extrba 0

 

7. 启动etest 这个extract

GGSCI> start extract etest

 

等待5分钟并检查

GGSCI> stats extract etest, totalsonly *, reportrate sec
GGSCI> stats extract etest, totalsonly *, reportrate min

 

对比 原始慢的extract 与 新加入的etest的 stats reportrate 报告中的性能指标,若 TESTMAPPINGSPEED 后 性能明显提升则说明问题出在 写trail  (extract 写到本地的情况) 或者 网络传出慢( 直接写到目标机上)。

 

如果TESTMAPPINGSPEED 后性能也无明显变化则继续。

 

8. 将所有extract 的表都注释掉,而仅仅extract 一张很少变化记录的表, 若这样 后性能明显提升则说明 瓶颈不在读archivelog 上而在 日志记录的处理上 log record processing 。

一般来说redo日志的解析分成2部分:

A. Record parsing in Extract
B. Record fetching if needed

 

9.为了进一步确认问题 将TESTMAPPINGSPEED 注释掉, 并 加入 TRACE/TRACE2 参数 以便确认 Extract是否慢在fetch上

 

–TESTMAPPINGSPEED http://www.askmaclean.com
TRACE ./dirtmp/ext.trc
TRACE2 ./dirtmp/ext.trc2

 

10 检查生成的trace 文件 若 其中显示 大量的时间耗费在一些SELECT语句上,则需要DBA介入来调优这些SELECT SQL

 

11. 若看到一些与undo/rollback 相关的错误例如ORA-1555则确保UNDO 表空间可用 空间足够,  也可以加入  FETCHOPTIONS NOUSESNAPSHOT 让 Extract fetch column 数据是尽可能不要走UNDO CR READ

 

12. 如果将大部分表都去掉,只剩下一个不太用的表且仍无明显的性能增长, 且CPU 也不忙, 一般来说这可能是IO瓶颈造成的

 

13. 建议dd测一下archivelog 的读取速度

例如maclean>time dd if=<归档日志> of=/dev/null bs=1M

对比其他磁盘若有明显差异, 则考虑将archivelog 移动到对应磁盘并再次上述测试。

 

 

对于cache较小的sequence 可以引起在replicat DDL 时频繁执行 ALTER SEQUENCE “SEQ_NAME” CYCLE的DDL语句:

 

2013-04-22 09:54:06  INFO    OGG-01487  DDL found, operation [ALTER SEQUENCE "TXN_SEQ" CYCLE  (size 31)], start SCN [20181621217], commit SCN [20181621231] instance [C
ULPRODB (1)], DDL seqno [2734821], marker seqno [2736076].

2013-04-22 09:54:06  INFO    OGG-00487  DDL operation included [Include Mapped], optype [ALTER], objtype [SEQUENCE], objowner [OLSUSER], objname [TXN_SEQ].

2013-04-22 09:54:06  INFO    OGG-00497  Writing DDL operation to extract trail file.
Wildcard SEQUENCE resolved (entry olsuser.*):
  Sequence "OLSUSER"."TXN_SEQ";
Resolving source sequence OLSUSER.TXN_SEQ.

2013-04-22 09:54:07  INFO    OGG-01487  DDL found, operation [ALTER SEQUENCE "TXN_SEQ" CYCLE  (size 31)], start SCN [20181621236], commit SCN [20181621248] instance [C
ULPRODB (1)], DDL seqno [2734822], marker seqno [2736077].

2013-04-22 09:54:07  INFO    OGG-00487  DDL operation included [Include Mapped], optype [ALTER], objtype [SEQUENCE], objowner [OLSUSER], objname [TXN_SEQ].

2013-04-22 09:54:07  INFO    OGG-00497  Writing DDL operation to extract trail file.
Wildcard SEQUENCE resolved (entry olsuser.*):
  Sequence "OLSUSER"."TXN_SEQ";
Resolving source sequence OLSUSER.TXN_SEQ.

2013-04-22 09:54:08  INFO    OGG-01487  DDL found, operation [ALTER SEQUENCE "TXN_SEQ" CYCLE  (size 31)], start SCN [20181621251], commit SCN [20181621261] instance [C
ULPRODB (1)], DDL seqno [2734823], marker seqno [2736078].

2013-04-22 09:54:08  INFO    OGG-00487  DDL operation included [Include Mapped], optype [ALTER], objtype [SEQUENCE], objowner [OLSUSER], objname [TXN_SEQ].

2013-04-22 09:54:08  INFO    OGG-00497  Writing DDL operation to extract trail file.
Wildcard SEQUENCE resolved (entry olsuser.*):
  Sequence "OLSUSER"."TXN_SEQ";
Resolving source sequence OLSUSER.TXN_SEQ.

 

若该replicat target库上尚有extract则 extract挖掘日志时可能因为处理频繁的DDL操作而变得很慢,”Why GoldenGate replicat issues “alter sequence .. cycle|nocycle” in sequence replication? [ID 1535322.1]” 文档指出了 这种频繁的 是为了在target 上上可信赖的同步sequence的高水位。

但是这种超频繁的 几乎每2s 一次的ALTER SEQUENCE CYCLE操作确实拖慢了Extract的速度, 可以通过指定参数 DBOPTIONS _NOAUTOMATICSEQUENCEFLUSH来减少ALTER SEQUENCE CYCLE出现,实际并不能完全避免。 文档指出使用该 DBOPTIONS _NOAUTOMATICSEQUENCEFLUSH参数可能导致TARGET上的SEQUENCE 不同步。

实际优先考虑增加SOURCE上SEQUENCE的CACHE解决问题, 之后再考虑用 DBOPTIONS _NOAUTOMATICSEQUENCEFLUSH; 针对该SEQUENCE  在EXTRACT里 排除该索引 没有效果, 例如 DDL EXCLUDE OBJNAME “XX.SEQNAME”  或者 TABLEEXCLUDE “XX.SEQUENCE”在实际测试中均没有明显的改善, 但修改DBOPTIONS _NOAUTOMATICSEQUENCEFLUSH确实可以该少性能。

了解GoldenGate中LAG的含义

GGSCI中显示的LAG代表 事务被写入到磁盘介质中的时刻例如Oracle中redo被写入到online redo logfile中 和 Replicat将同一个事务分发到目标数据库的时刻 之间的时间间隔。

 

通俗地说,一个事务内的所有行记录将对应同一个LAG; 除非出现了一个事务被打散且被多个REPLICAT分别apply或者变成多个事务的情况。 OGG参数例如RANGE这种对应于第一种情况,即一个事务被多个REPLICATE分别APPLY。 OGG参数MAXTRANSOPS对应后一种情况。

 

LAG在以下情况中被引入:

 

  1. 当Extract进程在读取redolog并写出到TRAIL或REMOTE HOST
  2. 当额外的datapump在读取extract trail并通过网络写出到远程节点REMOTE HOST
  3. 当collector在目标服务器上接受网络数据并写出到LOCAL TRAIL
  4. 当REPLICAT读取LOCAL TRAIL并写出到数据库中

 

 

同时也需要注意通过GGSCI中INFO或STATUS等命令显示的LAG,或通过SEND 对象名,LAG命令获得的LAG可能不一致:

 

INFO命令所获得的LAG可能与SEND命令所得值存在小的差别

INFO命令获得的LAG返回自MANAGER来源于最近记录的checkpoint

SEND <OBJECT>, lag获得的LAG值基于<OBJECT>正在处理的行记录的时间戳

LAG常使用时间单位或需要处理的数据单位Kilobytes来表达

 

归根结底LAG是衡量 数据归档或写出到日志的时间 和 EXTRACT/PUMP/REPLICAT处理该数据的时刻 这2个时间点之间的差距, 而不是说 LAG反映了EXTRACT还要工作多久。

 

实际EXTRACT/PUMP/REPLICAT都不知道自己要工作多久才能追上 REAL TIME,它们的LAG值只是显示 最近它们处理的一条记录的时间 和这条记录被写到REDO LOG的时间点之间的差距,即LAG只说明ER之前的工作延迟,不代表还要工作多久才能追平。

 

举个例子来说,STOP EXTRACT之后等待一段时间再重启看到有很大的LAG,这不代表EXTRACT有什么问题,只是EXTRACT最后处理的一条记录 很早就在REDO LOG里生成了 而EXTRACT真正处理这条记录是等了一段时间的而已。

 

 

 

 

 

 

 

GGSCI (XIANGBLI-CN) 27> stop load2

 

Sending STOP request to EXTRACT LOAD2 …

Request processed.

 

 

GGSCI (XIANGBLI-CN) 28> start load2

 

Sending START request to MANAGER …

EXTRACT LOAD2 starting

 

GGSCI (XIANGBLI-CN) 31> info load2

 

EXTRACT    LOAD2     Last Started 2012-09-18 20:26   Status RUNNING

Checkpoint Lag       00:04:34 (updated 00:00:08 ago)

Log Read Checkpoint  Oracle Redo Logs

2012-09-18 20:21:32  Seqno 44, RBA 13750272

SCN 0.1845479 (1845479)

 

 

GGSCI (XIANGBLI-CN) 35> lag load2

 

Sending GETLAG request to EXTRACT LOAD2 …

Last record lag: 130 seconds.

At EOF, no more records to process.

 

GGSCI (XIANGBLI-CN) 36> info load2

 

EXTRACT    LOAD2     Last Started 2012-09-18 20:26   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:02 ago)

Log Read Checkpoint  Oracle Redo Logs

2012-09-18 20:27:33  Seqno 44, RBA 13817856

SCN 0.1845671 (1845671)

 

 

以上可以看到 Last record lag 和 Checkpoint Lag 是不同的

 

 

EXTRACT/PUMP/REPLICAT 没法预知自己什么时候能追平(catch up), 为什么? 因为虽然看上去可能有几十个GB的redo要处理,但是实际符合EXTRACT/PUMP/REPLICAT 要的记录可能很少。

 

 

又由于INFO的LAG是基于checkpoint的,所以如果出现大事务的情况Long Running Transactions (LRTs),事务可能长时间不提交COMMIT。 该事务可能变成一个最老而又最无聊的数据由于一直不COMMIT而无法写出。 这将造成EXTRACT/PUMP/REPLICAT实际处理这个大事务的时间点远落后于该大事务实际commit的时间点。 对于REPLICAT可以使用MAXTRANSOPS 参数来减少LAG。

了解GoldenGate Replicat的HANDLECOLLISIONS参数

HANDLECOLLISIONS是我们使用goldengate过程中常有的一个REPLICAT参数,该参数依赖于主键或唯一索引处理冲突数据,常用于初始化阶段。对于无主键或唯一索引的表无法处理冲突,且可能导致重复记录。注意打开此参数则所有数据错误不管reperror如何配置均不再写discard文件,即所有数据冲突信息被默认规则处理,没有任何日志(则会忽略error mapping数据错误,而且不会报告到discard文件),因此日常复制不建议使用该参数;可予以考虑的特殊场景为只需新增数据,无需复制历史数据。

 

使用HANDLECOLLISIONS的几个场景:

  1. target丢失delete记录(missing delete),忽略该问题并不记录到discardfile
  2. target丢失update记录(missing update)
    • 更新的键值是主键=》 update转换成INSERT ,默认情况下插入记录不完整
    • 更新的键值是非主键=》 忽略该问题并不记录到discardfile
  3. 重复插入已存在的主键值到target表中,这将被replicat转换为UPDATE现有主键值的行的其他非主键列

情景1 target丢失delete记录(missing delete) :

C:\Users\ML>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 18 13:38:03 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn sender/oracle
Connected.
SQL> create table handlec(t1 int primary key,t2 int);

Table created.

SQL> insert into handlec values(1,2);

1 row created.

SQL> insert into handlec values(3,2);

1 row created.

SQL> insert into handlec values(4,2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from handlec;

        T1         T2
---------- ----------
         1          2
         3          2
         4          2

target :

SQL> conn receiver/oracle
Connected.
SQL> create table handlec(t1 int primary key,t2 int);

Table created.

SQL> insert into handlec values(1,2);

1 row created.

SQL> commit;

SQL> select * from handlec;

        T1         T2
---------- ----------
         1          2

SQL>

GGSCI (XIANGBLI-CN) 1> alter extract load2 , begin now
EXTRACT altered.

GGSCI (XIANGBLI-CN) 4> alter replicat rep2, begin now
REPLICAT altered.

GGSCI (XIANGBLI-CN) 13> add trandata sender.*

Logging of supplemental redo data enabled for table SENDER.HANDLEC.

Logging of supplemental redo log data is already enabled for table SENDER.TV.

GGSCI (XIANGBLI-CN) 14> start mgr
MGR is already running.

GGSCI (XIANGBLI-CN) 15> start er *

Sending START request to MANAGER ...
EXTRACT LOAD2 starting

Sending START request to MANAGER ...
REPLICAT REP2 starting

GGSCI (XIANGBLI-CN) 16> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     LOAD2       00:00:00      00:00:01
REPLICAT    RUNNING     REP2        00:00:00      00:00:08

***SOURCE端删除一条TARGET没有的数据

SQL> delete handlec where t1=3;

1 row deleted.

SQL> commit;

Commit complete.

出现SQL error 1403错误,REPLICAT ABORT

2012-09-18 13:45:48  WARNING OGG-01004  Aborted grouped transaction on 'RECEIVER.HANDLEC', Database error 1403 (OCI Error ORA-01403: no data found, SQL ).

2012-09-18 13:45:48  WARNING OGG-01003  Repositioning to rba 1091 in seqno 3.

2012-09-18 13:45:48  WARNING OGG-01154  SQL error 1403 mapping SENDER.HANDLEC to RECEIVER.HANDLEC OCI Error ORA-01403: no data found, SQL .

2012-09-18 13:45:48  WARNING OGG-01003  Repositioning to rba 1091 in seqno 3.

Source Context :
  SourceModule            : [er.errors]
  SourceID                : [er/errors.cpp]
  SourceFunction          : [take_rep_err_action]
  SourceLine              : [623]
  ThreadBacktrace         : [8] elements
                          : [D:\ogg\V34342-01\gglog.dll(??1CContextItem@@UEAA@XZ+0x3272) [0x000000018010BDD2]]
                          : [D:\ogg\V34342-01\gglog.dll(?_MSG_ERR_MAP_TO_TANDEM_FAILED@@YAPEAVCMessage@@PEAVCSourceContext@@AEBV?$CQualDBObjName@$00@ggapp@gglib@ggs@@1W4MessageDisposition@CMessageFactory@@@Z+0x138) [0x00000001800AD508]]
                          : [D:\ogg\V34342-01\replicat.exe(ERCALLBACK+0x6e1e) [0x0000000140099D5E]]
                          : [D:\ogg\V34342-01\replicat.exe(shutdownMonitoring+0x4411) [0x00000001400C9BE1]]
                          : [D:\ogg\V34342-01\replicat.exe(shutdownMonitoring+0x289cd) [0x00000001400EE19D]]
                          : [D:\ogg\V34342-01\replicat.exe(CommonLexerNewSSD+0x9440) [0x00000001402AE980]]
                          : [C:\windows\system32\kernel32.dll(BaseThreadInitThunk+0xd) [0x000000007733652D]]
                          : [C:\windows\SYSTEM32\ntdll.dll(RtlUserThreadStart+0x21) [0x000000007746C521]]

2012-09-18 13:45:48  ERROR   OGG-01296  Error mapping from SENDER.HANDLEC to RECEIVER.HANDLEC.

***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************

Last record for the last committed transaction is the following: 
___________________________________________________________________
Trail name :  D:\ogg\V34342-01\ex\ze000003
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04) 
UndoFlag   :     .  (x00)     BeforeAfter:     B  (x42) 
RecLength  :     9 (x0009)    IO Time    : 2012-09-18 13:45:38.000000  
IOType     :     3  (x03)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :         44       AuditPos   : 3337232
Continued  :     N  (x00)     RecCount   :     1  (x01)

2012-09-18 13:45:38.000000 Delete             Len     9 RBA 1091
Name: SENDER.HANDLEC
___________________________________________________________________

Reading D:\ogg\V34342-01\ex\ze000003, current RBA 1091, 0 records

Report at 2012-09-18 13:45:48 (activity since 2012-09-18 13:45:48)

From Table SENDER.HANDLEC to RECEIVER.HANDLEC:
       #                   inserts:         0
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         1

Last log location read:
     FILE:      D:\ogg\V34342-01\ex\ze000003
     SEQNO:     3
     RBA:       1091
     TIMESTAMP: 2012-09-18 13:45:38.000000
     EOF:       NO
     READERR:   0

2012-09-18 13:45:48  ERROR   OGG-01668  PROCESS ABENDING.

2012-09-18 13:45:48  INFO    OGG-01237  Trace file D:\ogg\V34342-01\REP_TRACE1.TRC closed.

2012-09-18 13:45:48  INFO    OGG-01237  Trace file D:\ogg\V34342-01\REP_TRACE2.TRC closed.

CACHE OBJECT MANAGER statistics

CACHE MANAGER VM USAGE
vm current     =      0    vm anon queues =      0 
vm anon in use =      0    vm file        =      0 
vm used max    =      0    ==> CACHE BALANCED

CACHE CONFIGURATION
cache size       =   2G   cache force paging = 3.41G
buffer min       =  64K   buffer highwater   =   8M
pageout eligible size =   8M

================================================================================

使用skiptransaction跳过上述失败事务

GGSCI (XIANGBLI-CN) 18> start rep2 skiptransaction

Sending START request to MANAGER ...
REPLICAT REP2 starting

 

 

 

 

情景2 target丢失update记录(missing update),更新的键值是主键 :

 

 

继续我们的测试, 针对source的某条记录进行更新

SQL> update handlec set t1=5 where t1=4;

1 row updated.

SQL> commit;

Commit complete.

对于在target 丢失更新(miss update)的情况也会造成 Database error 1403+OGG-01296

2012-09-18 13:49:30  WARNING OGG-01004  Aborted grouped transaction on 'RECEIVER.HANDLEC', Database error 1403 (OCI Error ORA-01403: no data found, SQL <UPDATE "RECEIVER"."HANDLEC" SET "T1" = :a1 WHERE "T1" = :b0>).

2012-09-18 13:49:30  WARNING OGG-01003  Repositioning to rba 1218 in seqno 3.

2012-09-18 13:49:30  WARNING OGG-01003  Repositioning to rba 1218 in seqno 3.

Source Context :
  SourceModule            : [er.errors]
  SourceID                : [er/errors.cpp]
  SourceFunction          : [take_rep_err_action]
  SourceLine              : [623]
  ThreadBacktrace         : [8] elements
                          : [D:\ogg\V34342-01\gglog.dll(??1CContextItem@@UEAA@XZ+0x3272) [0x000000018010BDD2]]
                          : [D:\ogg\V34342-01\gglog.dll(?_MSG_ERR_MAP_TO_TANDEM_FAILED@@YAPEAVCMessage@@PEAVCSourceContext@@AEBV?$CQualDBObjName@$00@ggapp@gglib@ggs@@1W4MessageDisposition@CMessageFactory@@@Z+0x138) [0x00000001800AD508]]
                          : [D:\ogg\V34342-01\replicat.exe(ERCALLBACK+0x6e1e) [0x0000000140099D5E]]
                          : [D:\ogg\V34342-01\replicat.exe(shutdownMonitoring+0x4411) [0x00000001400C9BE1]]
                          : [D:\ogg\V34342-01\replicat.exe(shutdownMonitoring+0x289cd) [0x00000001400EE19D]]
                          : [D:\ogg\V34342-01\replicat.exe(CommonLexerNewSSD+0x9440) [0x00000001402AE980]]
                          : [C:\windows\system32\kernel32.dll(BaseThreadInitThunk+0xd) [0x000000007733652D]]
                          : [C:\windows\SYSTEM32\ntdll.dll(RtlUserThreadStart+0x21) [0x000000007746C521]]

2012-09-18 13:49:30  ERROR   OGG-01296  Error mapping from SENDER.HANDLEC to RECEIVER.HANDLEC.

加入HANDLECOLLISIONS后,rep可以继续工作且不生成discard记录

GGSCI (XIANGBLI-CN) 23> view params rep2
replicat rep2
userid receiver , password oracle
trace ./rep_trace1.trc
trace2 ./rep_trace2.trc
ASSUMETARGETDEFS
HANDLECOLLISIONS
map sender.*, target receiver.*;

GGSCI (XIANGBLI-CN) 18> start rep2

SQL> select * from handlec;

        T1         T2
---------- ----------
         1          2
         5

 

 

 

这里出现T1=5 T2 NULL记录的原因是 ,丢失update的更新操作是针对主键的更新,此时replicat会尝试插入一条记录而非忽略该update。
注意插入的记录可能不是完整的行,如上例中的T2 为NULL ,若要求完整的行记录则要求EXTRACT使用PKUPDATE选项。

需要加入的选项是FETCHOPTIONS FETCHPKUPDATECOLS

将以上选项加入到EXTRACT参数文件中,并重启EXTRACT。 这将引起extract捕获完整的主键更新镜像。

如以下的例子:

SQL> conn receiver/oracle
Connected.
SQL> select * from handlec;

T1 T2
---------- ----------
1 2
10 100
5
20 200

SQL> delete handlec where t1=5;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from handlec;

T1 T2
---------- ----------
1 2
10 100
20 200

SQL> conn sender/oracle
Connected.

SQL> update handlec set t1=t1+1000 where t1=5;

1 row updated.

SQL> commit;

Commit complete.

SQL> conn receiver/oracle
Connected.
SQL>
SQL>
SQL> select * from handlec;

T1 T2
---------- ----------
1 2
10 100
20 200
1005 2

 

 

 

如上述实验验证FETCHOPTIONS FETCHPKUPDATECOLS将捕获完整的redo image镜像到trail中,这保证把primary key的更新通过HANDLECOLLISIONS转换为对target的一个完整记录的插入。

 

情景3 重复插入已存在的主键值到target表中,这将被replicat转换为UPDATE现有主键值的行的其他非主键列:

 

 

 

*** TARGET 

SQL> conn receiver/oracle
Connected.

SQL> select * from handlec;

        T1         T2
---------- ----------
         1          2
        10          9
		 5

target中已经存在 t1=10 t2=9的记录 ,此时再在source中插入(10,100)的记录

>>SOURCE

SQL> insert into handlec values(10,100);

1 row created.

SQL> commit;

>>TARGET

SQL> select * from handlec;

        T1         T2
---------- ----------
         1          2
        10        100
         5

上面可以看到在source的insert操作,因为在target中已有对应的主键记录所以被启用HANDLECOLLISIONS的REPLICAT转换为UPDATE非主键的其他COLUMNS

 

 

总结

 

HANDLECOLLISIONS是我们使用goldengate过程中常有的一个REPLICAT参数,该参数依赖于主键或唯一索引处理冲突数据,常用于初始化阶段。对于无主键或唯一索引的表无法处理冲突,且可能导致重复记录。注意打开此参数则所有数据错误不管reperror如何配置均不再写discard文件,即所有数据冲突信息被默认规则处理,没有任何日志,因此日常复制不建议使用该参数;可予以考虑的特殊场景为只需新增数据,无需复制历史数据。

 

使用HANDLECOLLISIONS的几个场景:

  1. target丢失delete记录(missing delete),忽略该问题并不记录到discardfile
  2. target丢失update记录(missing update)
    • 更新的键值是主键=》 update转换成INSERT ,默认情况下插入记录不完整
    • 更新的键值是非主键=》 忽略该问题并不记录到discardfile
  3. 重复插入已存在的主键值到target表中,这将被replicat转换为UPDATE现有主键值的行的其他非主键列

另:该参数仅处理数据本身的Insert/Delete冲突,如果出现两端映射或其它结构性问题Replicat进程依然会abend,不能被忽略

 

此外对于主键的更新操作,若在target使用HANDLECOLLISIONS且该update丢失,在会转换为INSERT该主键的操作,注意默认情况下插入的记录不完整,FETCHOPTIONS FETCHPKUPDATECOLS将捕获完整的redo image镜像到trail中,这保证把primary key的更新通过HANDLECOLLISIONS转换为对target的一个完整记录的插入。

 

 

我们可以通过send 命令动态取消HANDLECOLLISIONS

GGSCI (XIANGBLI-CN) 29> send rep2, NOHANDLECOLLISIONS

Sending NOHANDLECOLLISIONS request to REPLICAT REP2 ...
REP2 NOHANDLECOLLISIONS set for 1 tables and 0 wildcard entries

Oracle Goldengate OGG 11g与各操作系统及数据库版本的兼容列表

Oracle Goldengate OGG 11g (11.1.1.0.0)与各操作系统及数据库版本的兼容列表如下,仅供参考:

 

Oracle GoldenGate Certification Matrix 11.1.1.0.0
Version Supported Processor Type OS Version OS
32/64 bit
Oracle FM
32/64 bit
JDK Vendor
Version*
JDK
32/64 bit
Oracle
Database*
Exceptions and Additional Information
11gR1 (11.1.1.1+) x86 Red Hat EL 4 (UL7+) 32 NA NA NA Oracle 9.2.0.7+
Oracle 10.2.0.4+
Oracle 11.1+
Oracle 11.2.+
IBM DB2 9.1
IBM DB2 9.5
IBM DB2 9.7
Sybase 12.5.4
Sybase 15
MySQL 5.0
MySQL 5.1
Teradata 12
Teradata 13
MySQL 5.0 supports Delivery only
11gR1 (11.1.1.1+) x86 Red Hat EL 5 (UL3+) 32 NA NA NA Oracle 9.2.0.7+
Oracle 10.2.0.4+
Oracle 11.1+
Oracle 11.2.+
IBM DB2 9.1
IBM DB2 9.5
IBM DB2 9.7
Sybase 12.5.4
Sybase 15
MySQL 5.0
MySQL 5.1
Teradata 12
Teradata 13
MySQL 5.0 supports Delivery only
11.1.1.0.0 x86 SLES 10 (SP1+) 32 NA NA NA Oracle 9.2.0.7+
Oracle 10.2.0.4+
Oracle 11.1+
Oracle 11.2.+
IBM DB2 9.1
IBM DB2 9.5
IBM DB2 9.7
Sybase 12.5.4
Sybase 15
MySQL 5.0
MySQL 5.1
Teradata 12
Teradata 13
MySQL 5.0 supports Delivery only
11.1.1.0.0 x86 Windows 2003 32 NA NA NA Oracle 9.2.0.7+
Oracle 10.2.0.4+
Oracle 11.1.+
Oracle 11.2.+
IBM DB2 9.1
IBM DB2 9.5
IBM DB2 9.7
Microsoft SQL Server 2000
Microsoft SQL Server 2005
Microsoft SQL Server 2008
MySQL 5.0
MySQL 5.1
Sybase 12.5.4
Sybase 15
TimesTen 7.05
Teradata 12
Teradata 13
MySQL 5.0 supports Delivery only
TimeTen supports Delivery only
11.1.1.0.0 x86 Windows XP Professional with SP3+ 32 NA NA NA Oracle 9.2.0.7+
Oracle 10.2.0.4+
Oracle 11.1.+
Oracle 11.2.+
IBM DB2 9.1
IBM DB2 9.5
IBM DB2 9.7
Microsoft SQL Server 2000
Microsoft SQL Server 2005
Microsoft SQL Server 2008
MySQL 5.0
MySQL 5.1
Sybase 12.5.4
Sybase 15
TimesTen 7.05
Teradata 12
Teradata 13
MySQL 5.0 supports Delivery only
TimeTen supports Delivery only
11.1.1.0.0 x64 Red Hat EL 4 (UL7+) 64 NA NA NA Oracle 9.2.0.7+
Oracle 10.2.0.4+
Oracle 11.1.+
Oracle 11.2.+
IBM DB2 9.1
IBM DB2 9.5
IBM DB2 9.7
Microsoft SQL Server 2000
Microsoft SQL Server 2005
Microsoft SQL Server 2008
MySQL 5.0
MySQL 5.1
Sybase 12.5.4
Sybase 15
TimesTen 7.05
Teradata 12
Teradata 13

11.1.1.0.0 x64 Red Hat EL 5 (UL3+) 64 NA NA NA Oracle 9.2.0.7+
Oracle 10.2.0.4+
Oracle 11.1.+
Oracle 11.2.+
IBM DB2 9.1
IBM DB2 9.5
IBM DB2 9.7
Microsoft SQL Server 2000
Microsoft SQL Server 2005
Microsoft SQL Server 2008
MySQL 5.0
MySQL 5.1
Sybase 12.5.4
Sybase 15
Teradata 12
Teradata 13

11.1.1.0.0 x64 SLES 10 (SP1+) 64 NA NA NA Oracle 9.2.0.7+
Oracle 10.2.0.4+
Oracle 11.1.+
Oracle 11.2.+
IBM DB2 9.1
IBM DB2 9.5
IBM DB2 9.7
Microsoft SQL Server 2000
Microsoft SQL Server 2005
Microsoft SQL Server 2008
MySQL 5.0
MySQL 5.1
Sybase 12.5.4
Sybase 15
TimesTen 7.05
Teradata 12
Teradata 13
MySQL 5.0 supports Delivery only
TimeTen supports Delivery only
11.1.1.0.0 x64 Windows 2003 with SP2/R2+ 64 NA NA NA Oracle 9.2.0.7+
Oracle 10.2.0.4+
Oracle 11.1.+
Oracle 11.2.+
IBM DB2 9.1
IBM DB2 9.5
IBM DB2 9.7
Microsoft SQL Server 2000
Microsoft SQL Server 2005
Microsoft SQL Server 2008
MySQL 5.0
MySQL 5.1
Sybase 12.5.4
Sybase 15
TimesTen 7.05
Teradata 12
Teradata 13
MySQL 5.0 supports Delivery only
TimeTen supports Delivery only
11.1.1.0.0 x64 Windows Server 2008 with SP1+ 64 NA NA NA Oracle 10.2.0.4+
Oracle 11.1.+
Oracle 11.2.+
IBM DB2 9.1
IBM DB2 9.5
Sybase 12.5.4
Sybase 15
MySQL 5.0
MySQL 5.1
Teradata 12
Teradata 13
Microsoft SQL Server 2000
Microsoft SQL Server 2005
Microsoft SQL Server 2008

11.1.1.0.0 Itanium-2 Windows Server 2008 with SP1+ 64 NA NA NA Microsoft SQL Server 2000
Microsoft SQL Server 2005
Microsoft SQL Server 2008

11.1.1.0.0 SPARC Solaris 2.9 Update 9+ 64 NA NA NA Oracle 9.2.0.7+
Oracle 10.2.0.4+
Oracle 11.1.+
Oracle 11.2.+
IBM DB2 9.1
IBM DB2 9.5
IBM DB2 9.7
Sybase 12.5.4
Sybase 15

11.1.1.0.0 SPARC Solaris 10 Update 4+ 64 NA NA NA Oracle 9.2.0.7+
Oracle 10.2.0.4+
Oracle 11.1.+
Oracle 11.2.+
IBM DB2 9.1
IBM DB2 9.5
IBM DB2 9.7
Sybase 12.5.4
Sybase 15

11.1.1.0.0 PA-RISC HP-UX 11i (11.23)
B.11.23.0703.059a Base Quality Pack Bundle for HP-UX 11i v2, March 2007+
64 NA NA NA Oracle 9.2.0.7+
Oracle 10.2.0.4+
Oracle 11.1.+
Oracle 11.2.+
IBM DB2 9.1
IBM DB2 9.5
IBM DB2 9.7
Sybase 12.5.4
Sybase 15

11.1.1.0.0 PA-RISC HP-UX 11i (11.31)
B.11.31.0803.318a Base Quality Pack Bundle for HP-UX 11i v3, March 2008+
64 NA NA NA Oracle 9.2.0.7+
Oracle 10.2.0.4+
Oracle 11.1.+
Oracle 11.2.+
IBM DB2 9.1
IBM DB2 9.5
IBM DB2 9.7
Sybase 12.5.4
Sybase 15

11.1.1.0.0 Itanium-2 HP-UX 11i (11.23)
B.11.23.0703.059a Base Quality Pack Bundle for HP-UX 11i v2, March 2007+
64 NA NA NA Oracle 9.2.0.7+
Oracle 10.2.0.4+
Oracle 11.1.+
Oracle 11.2.+
IBM DB2 9.1
IBM DB2 9.5
IBM DB2 9.7

11.1.1.0.0 POWER AIX 5.3 (TL8+) 64 NA NA NA Oracle 9.2.0.7+
Oracle 10.2.0.4+
Oracle 11.1.+
Oracle 11.2.+
IBM DB2 9.1
IBM DB2 9.5
IBM DB2 9.7
Sybase 12.5.4
Sybase 15

11.1.1.0.0 POWER AIX 6.1 (TL2+) 64 NA NA NA Oracle 9.2.0.7+
Oracle 10.2.0.4+
Oracle 11.1.+
Oracle 11.2.+
IBM DB2 9.1
IBM DB2 9.5
IBM DB2 9.7
Sybase 12.5.4
Sybase 15

Goldengate Parameter SUPPRESSTRIGGERS & DEFERREFCONST

DBOPTIONS SUPPRESSTRIGGERS for delete cascade constraint on the target side (REPLICAT) in 11.1.0.7 is missing.

Ct was using OGG ver 10.4 initially for replicating from 9.2.0.8 on Sun Solaris to 11.1.0.7 on AIX.
Ct ran into issues as they had 232 tables with 250 DELETE CASCADE constraints while replicating delete records.
We gave the recommendation of disabling the constraint which obviously worked but the ct does not want
to disable the constraint and involves lot of manual work.
In working thru’ the issues with GG support, it was mentioned that OGG ver 11.1. would have a parameter
that was to be set in the REPLICAT which would fix this issue.
In reading thru’ the Release notes
http://download.oracle.com/docs/cd/E18101_01/doc.1111/e18165.pdf
(Page 6).

the parameter SUPPRESSTRIGGERS is not available for 11gR1, 11.1.0.7.

I was adivsed to open an SR with GG support to check if there will be an additional build
on top of 11.1.1 so that this parameter becomes available for 11gR1. Otherwise the ct will not be
very happy as initially, we were told the ct that the parameter will be available for 11.1.0.7, but it didnt make it as per the doc.
If we can build the same for 11.1.0.7 it will go a long way in maintaining this high profile ct.

check the OGG v11 guides and ensure that you are looking for SUPPRESSTRIGGERS or DEFERREFCONST.

SUPPRESSTRIGGERS
********************
Valid for Replicat for Oracle. Prevents triggers from firing on target objects that are configured for replication with Oracle GoldenGate. You can use this parameter for Oracle 10.2.0.5 and later patches, and for Oracle 11.2.0.2 and later, instead of manually
disabling the triggers.

DEFERREFCONST
****************
Valid for Replicat for Oracle. Delays referential integrity constraint checking and enforcement by the database until the Replicat transaction is committed. You can use this parameter instead of disabling the constraints on the target tables if the database is
Oracle version 9.2.0.7 and later.

When coming to SUPPRESSTRIGGERS, we have some packages added to 10.2.0.5 or 11.2.0.2 and above. Those packages are needed for this to work.

For 10.2.0.5, we need to use dbms_streams_auth.grant_admin_privilege and For 11.2.0.2, we use dbms_goldengate_auth.grant_admin_privilege.

OGG-01154 SQL error 1400 cannot insert NULL into错误解析

2012-11-22 14:46:37 WARNING OGG-03504 NLS_LANG character set UTF8 on the target is different from the source database character se
t AL32UTF8. Replication may not be valid if the source data has an incompatible character for the target NLS_LANG character set.

2012-11-22 14:46:37 WARNING OGG-00869 Aborting BATCHSQL transaction. Detected inconsistent result: executed 1 operations in batch,
resulting in 0 affected rows.

2012-11-22 14:46:37 WARNING OGG-01137 BATCHSQL suspended, continuing in normal mode.

2012-11-22 14:46:37 WARNING OGG-01003 Repositioning to rba 2834 in seqno 0.

2012-11-22 14:46:37 WARNING OGG-00869 OCI Error ORA-01400: 无法将 NULL 插入 ("EP"."T_SYS_TASK"."ID") (status = 1400). INSERT INTO 
"EP"."T_SYS_TASK" ("ID","TASK_TYPE","UNIT_ID","START_TIME","STOP_TIME","STATUS","INFO_ID","TITLE","CONTENT","EXEC_START_TIME","EXEC_
STOP_TIME","ADDR_LIST_FILE","NOTICE_MAIL_ADDR","TASK_NAME","CREATOR_ID","CREATOR_TIME","AUDITOR_ID","AUDITOR_TIME","ADVICE") VALUES 
(:a0,:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9,:a10,:a11,:a12,:a13,:a14,:a15,:a16,:a17,:a18).

2012-11-22 14:46:37 WARNING OGG-01004 Aborted grouped transaction on 'EP.T_SYS_TASK', Database error 1400 (OCI Error ORA-01400: 无
法将 NULL 插入 ("EP"."T_SYS_TASK"."ID") (status = 1400). INSERT INTO "EP"."T_SYS_TASK" ("ID","TASK_TYPE","UNIT_ID","START_TIME","STO
P_TIME","STATUS","INFO_ID","TITLE","CONTENT","EXEC_START_TIME","EXEC_STOP_TIME","ADDR_LIST_FILE","NOTICE_MAIL_ADDR","TASK_NAME","CRE
ATOR_ID","CREATOR_TIME","AUDITOR_ID","AUDITOR_TIME","ADVICE") VALUES (:a0,:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9,:a10,:a11,:a12,:a13,:a
14,:a15,:a16,:a17,:a18)).

2012-11-22 14:46:37 WARNING OGG-01003 Repositioning to rba 2834 in seqno 0.

2012-11-22 14:46:37 WARNING OGG-01154 SQL error 1400 mapping EP.T_SYS_TASK to EP.T_SYS_TASK 

OCI Error ORA-01400: 无法将 NULL 插入 
("EP"."T_SYS_TASK"."ID") (status = 1400). INSERT INTO "EP"."T_SYS_TASK" ("ID","TASK_TYPE","UNIT_ID","START_TIME","STOP_TIME","STATUS
","INFO_ID","TITLE","CONTENT","EXEC_START_TIME","EXEC_STOP_TIME","ADDR_LIST_FILE","NOTICE_MAIL_ADDR","TASK_NAME","CREATOR_ID","CREAT
OR_TIME","AUDITOR_ID","AUDITOR_TIME","ADVICE") VALUES (:a0,:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9,:a10,:a11,:a12,:a13,:a14,:a15,:a16,:a
17,:a18).

2012-11-22 14:46:37 WARNING OGG-01003 Repositioning to rba 2834 in seqno 0.
WARNING OGG-01396 A complete after image is not available in <schema.table> at rba 123456 in file ./dirdat/yyy, 
while inserting a row into <schema.table> due to missing target row for a key update operation. 
NOCOMPRESSUPDATES or FETCHOPTIONS FETCHPKUPDATECOLS 
may be specified in the EXTRACT parameter file to include a complete image for key update operations.

WARNING OGG-00869 OCI Error ORA-01400: cannot insert NULL into ("<schema>"."<table>"."<PK>") 
(status = 1400), SQL <INSERT INTO "<schema>"."<table>" ("<PK>",...) VALUES (:a0,:...)>.

2011-03-28 10:39:05 WARNING OGG-01004 Aborted grouped transaction on '<schema.table>', 
Database error 1400 (ORA-01400: cannot insert NULL into ("<schema>"."<table>"."<PK>")).

2011-03-28 10:39:05 WARNING OGG-01003 Repositioning to rba 123455 in seqno 678.

2011-03-28 10:39:05 WARNING OGG-01154 SQL error 1400 mapping <schema.table> to <schema.table> 

OCI Error ORA-01400: cannot insert NULL into ("<schema>"."<table>"."<PK>") (status = 1400), 
SQL <INSERT INTO "<schema>"."<table>" ("<PK>",...) VALUES (:a0,...)>.

2011-03-28 10:39:05 WARNING OGG-01003 Repositioning to rba 123455 in seqno 678.

Source Context :
SourceModule : [er.main]
SourceID : [/home/ecloud/workspace/Build_FBO_OpenSys_r11.1.1.0.0_078_[34093]/perforce/src/app/er/rep.c]
SourceFunction : [take_rep_err_action]
SourceLine : [15780]
ThreadBacktrace : [8] elements
: [/oradata/ogg/replicat(CMessageContext::AddThreadContext()+0x26) [0x5d9516]]
: [/oradata/ogg/replicat(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x7b2) [0x5cffb2]]
: [/oradata/ogg/replicat(_MSG_ERR_MAP_TO_TANDEM_FAILED(CSourceContext*, DBString<777> const&, 
DBString<777> const&, CMessageFactory::MessageDisposition)+0x9b) [0x57bd7b]]
: [/oradata/ogg/replicat [0x7df1a3]]
: [/oradata/ogg/replicat [0x8ac301]]
: [/oradata/ogg/replicat(main+0x1d30) [0x4f4b90]]
: [/lib64/libc.so.6(__libc_start_main+0xf4) [0x3fca01d994]]
: [/oradata/ogg/replicat(__gxx_personality_v0+0x1e2) [0x4d86ba]]

2011-03-28 10:39:05 ERROR OGG-01296 Error mapping from <schema.table> to <schema.table>.

 

 

OGG-01154 SQL error 1400 该错误常由replicat端使用了HANDLECOLLISIONS时(启用HANDLECOLLISIONS时 target丢失update记录(missing update)更新的键值是主键=》 update转换成INSERT ,默认情况下插入记录不完整,详见《了解GoldenGate Replicat的HANDLECOLLISIONS参数》)对于丢失的PK UPDATE转换为INSERT但是由于UPDATE记录未包含更新后的所有列的镜像而引起(if the PK is not available at target side, then this error is expected, because HANDLECOLLISIONS turns the PK update into an insert as result of no target record to update.The problem is that the source PK update record doesn’t contain all the after image columns. That is also expected because the update record is intended to only update the affected columns. )

有同学认为OGG-01154 SQL error 1400 是因为数据不一致引起的,这样说也有道理,因为如果说不丢失该PK记录则UPDATE不会转变为INSERT。

对于上述问题常见的Workaround方法是在 capture/extract端加入FETCHOPTIONS FETCHPKUPDATECOLS ,以便extract获取完整的记录更新后镜像,使得HANDLECOLLISIONS 正常工作将PK UPDATE转换为INSERT。

FETCHOPTIONS is an Extract parameter that controls certain aspects of the way that GoldenGate fetches data.

FETCHOPTIONS FETCHPKUPDATECOLS

needs to be added to extract parameter file and extract needs to be restarted. This will cause the extract to capture the full image for primary key update.

Goldengate Best Parameters & TEST from Maclean

Sample
drop user ogg_maclean cascade;
create user ogg_maclean identified by oracle;
alter user ogg_maclean default tablespace users;
grant connect,resource to OGG_MACLEAN;

OGG_MACLEAN

maclean_press

conn ogg_maclean/oracle
create table maclean_press1(a int constraint key1 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press2(a int constraint key2 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press3(a int constraint key3 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press4(a int constraint key4 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press5(a int constraint key5 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press6(a int constraint key6 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press7(a int constraint key7 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press8(a int constraint key8 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press9(a int constraint key9 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);
create table maclean_press10(a int constraint key10 primary key,b varchar2(100),c varchar2(100),d date default sysdate,e date);

create or replace procedure trouble_ogg_sql as 
begin 
    for i in 1..2000000 loop
        insert into maclean_press1(a,b,c,d) values(i,i,i,sysdate);
        insert into maclean_press2(a,b,c,d) values(i,i,i,sysdate);
        insert into maclean_press3(a,b,c,d) values(i,i,i,sysdate);
        insert into maclean_press4(a,b,c,d) values(i,i,i,sysdate);
        insert into maclean_press5(a,b,c,d) values(i,i,i,sysdate);
        insert into maclean_press6(a,b,c,d) values(i,i,i,sysdate);
        insert into maclean_press7(a,b,c,d) values(i,i,i,sysdate);
        insert into maclean_press8(a,b,c,d) values(i,i,i,sysdate);
        insert into maclean_press9(a,b,c,d) values(i,i,i,sysdate);
        insert into maclean_press10(a,b,c,d) values(i,i,i,sysdate);
        if mod(i,2000)=0 then
            commit;
        end if;
    end loop;

        delete from maclean_press1 where a <= 10000;
         commit;
EXECUTE IMMEDIATE('alter table maclean_press1 move');
EXECUTE IMMEDIATE('alter index key1 rebuild');
        delete from maclean_press2 where a <= 10000;
        commit;
EXECUTE IMMEDIATE('alter table maclean_press2 move');
EXECUTE IMMEDIATE('alter index key2 rebuild');
delete from maclean_press3 where a <= 10000;
        commit;
EXECUTE IMMEDIATE('alter table maclean_press3 move');
EXECUTE IMMEDIATE('alter index key3 rebuild');
delete from maclean_press4 where a <= 10000;
    commit;        
EXECUTE IMMEDIATE('alter table maclean_press4 move');
EXECUTE IMMEDIATE('alter index key4 rebuild');
delete from maclean_press5 where a <= 10000;
commit;
EXECUTE IMMEDIATE('alter table maclean_press5 move');
EXECUTE IMMEDIATE('alter index key5 rebuild');
delete from maclean_press6 where a <= 10000;
commit;
EXECUTE IMMEDIATE('alter table maclean_press6 move');
EXECUTE IMMEDIATE('alter index key6 rebuild');
delete from maclean_press7 where a <= 10000;
commit;
EXECUTE IMMEDIATE('alter table maclean_press7 move');
EXECUTE IMMEDIATE('alter index key7 rebuild');
delete from maclean_press8 where a <= 10000;
commit;
EXECUTE IMMEDIATE('alter table maclean_press8 move');
EXECUTE IMMEDIATE('alter index key8 rebuild');
delete from maclean_press9 where a <= 10000;
commit;
EXECUTE IMMEDIATE('alter table maclean_press9 move');
EXECUTE IMMEDIATE('alter index key9 rebuild');
delete from maclean_press10 where a <= 10000;
        commit;
EXECUTE IMMEDIATE('alter table maclean_press10 move');
EXECUTE IMMEDIATE('alter index key10 rebuild');

for i in 1..1 loop
        update maclean_press1 set b=b+1 where a <= 20000;
         commit;
        update maclean_press2 set b=b+1 where a <= 20000;
        commit;
        update maclean_press3 set b=b+1 where a <= 20000;
        commit;
        update maclean_press4 set b=b+1 where a <= 20000;
    commit;        
        update maclean_press5 set b=b+1 where a <= 20000;
commit;
        update maclean_press6 set b=b+1 where a <= 20000;
commit;
        update maclean_press7 set b=b+1 where a <= 20000;
commit;
        update maclean_press8 set b=b+1 where a <= 20000;
commit;
        update maclean_press9 set b=b+1 where a <= 20000;
commit;
        update maclean_press10 set b=b+1 where a <= 20000;
        commit;
end loop;

for i in 1..1 loop
        delete from maclean_press1 where a > 30000 and a <= 40000;
         commit;
        delete from maclean_press2 where a > 30000 and a <= 40000;
        commit;
delete from maclean_press3 where a > 30000 and a <= 40000;
        commit;
delete from maclean_press4 where a > 30000 and a <= 40000;
    commit;        
delete from maclean_press5 where a > 30000 and a <= 40000;
commit;
delete from maclean_press6 where a > 30000 and a <= 40000;
commit;
delete from maclean_press7 where a > 30000 and a <= 40000;
commit;
delete from maclean_press8 where a > 30000 and a <= 40000;
commit;
delete from maclean_press9 where a > 30000 and a <= 40000;
commit;
delete from maclean_press10 where a > 30000 and a <= 40000;
        commit;
end loop;
end;
/

exec  ogg_maclean.trouble_ogg_sql;

select count(*),sum(a),sum(b) from maclean_press1;
select count(*),sum(a),sum(b) from maclean_press2;
select count(*),sum(a),sum(b) from maclean_press3;
select count(*),sum(a),sum(b) from maclean_press4;
select count(*),sum(a),sum(b) from maclean_press5;
select count(*),sum(a),sum(b) from maclean_press6;
select count(*),sum(a),sum(b) from maclean_press7;
select count(*),sum(a),sum(b) from maclean_press8;
select count(*),sum(a),sum(b) from maclean_press9;
select count(*),sum(a),sum(b) from maclean_press10;

主键更新测试(针对Quest)

create table tb1a (id number primary key, name varchar(30));
执行以下sql进行主键更新:
Begin
for i in 1..1000 loop
insert into tb1a values (i, 'aaa');
end loop;
commit;
update tb1a set id=id+100;
commit;
end;
/

select min(id),max(id),sum(id) from tb1a;

分区表

 create table maclean_partition (acct_no number(12),person varchar2(30),week_no number(2)) partition by range (week_no) 
(partition jan values less than(4),partition feb values less than(8),partition others values less than (maxvalue)) enable row movement;

  insert into maclean_partition values(1,'a',2);
 insert into maclean_partition values(2,'b',6);
 insert into maclean_partition values(3,'c',10);
 insert into maclean_partition values(6,'d',6);
 insert into maclean_partition values(8,'e',8);
 insert into maclean_partition values(9,'e',9);
 commit;
 update maclean_partition set person='d' where acct_no=2;
 commit;
 delete maclean_partition where acct_no=1;
 commit;

==========================================================================================================

ASM 的 tnsnames.ora 以及配置监听静态注册

===========================================================================================================

解压软件,配置ogg.sh 

export  GG_HOME=/goldengate
export  LD_LIBRARY_PATH=$ORACLE_HOME/lib   

    GGSCI> create subdirs

打开归档

Select log_mode from v$database;

shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

Select  
SUPPLEMENTAL_LOG_DATA_MIN
,SUPPLEMENTAL_LOG_DATA_PK
,SUPPLEMENTAL_LOG_DATA_UI
,SUPPLEMENTAL_LOG_DATA_FK
,SUPPLEMENTAL_LOG_DATA_ALL from v$database;

alter database add supplemental log data ;
--alter database add supplemental log data (primary key, unique,foreign key) columns;
alter system switch logfile;

Select  
SUPPLEMENTAL_LOG_DATA_MIN
,SUPPLEMENTAL_LOG_DATA_PK
,SUPPLEMENTAL_LOG_DATA_UI
,SUPPLEMENTAL_LOG_DATA_FK
,SUPPLEMENTAL_LOG_DATA_ALL from v$database;

Alter database force logging;

创建ogg 用户

--create tablespace
create tablespace goldengate datafile  size 1024M ;

-- Create the user 
create user goldengate identified by &A default tablespace goldengate;

-- Grant role privileges 
grant  resource, connect, dba to goldengate;

grant create any table to goldengate;
grant create any view to goldengate;
grant create any procedure to goldengate;
grant create any sequence to goldengate;
grant create any index to goldengate;
grant create any trigger to goldengate;
grant create any view to goldengate;

===========================================================================================================
[oracle@vrh1 ~]$ cat ogg.sh

export ORACLE_HOME=/s01/orabase/product/11.2.0/dbhome_1
export ORACLE_SID=VRAC1
export LD_LIBRARY_PATH=/ogg:$ORACLE_HOME/lib:$ORACLE_HOME/lib32
export PATH=/home/oracle/ogg:$PATH

cd /home/oracle/ogg
ggsci 

===========================================================================================================

./GLOBALS

GGSCHEMA goldengate
CheckpointTable goldengate.ckpt
UnlockedTrailFiles

===========================================================================================================

MGR

Port 7809
userid goldengate , password oracle
DYNAMICPORTLIST 9101-9356
CheckMinutes 10
PurgeOldExtracts ./dirdat/*, UseCheckpoints, MinKeepHours 96
PurgeMarkerHistory MinKeepDays 3, MaxKeepDays 7, FrequencyMinutes 120
AutoRestart ER *, WaitMinutes 5, Retries 3
LagInfoMinutes 0
LagReportMinutes 10

===========================================================================================================

源端DDL

cd ogg

 GRANT EXECUTE ON UTL_FILE TO goldengate;

  ALTER SYSTEM SET RECYCLEBIN =OFF SCOPE =BOTH;

11g中 只能ALTER SYSTEM SET RECYCLEBIN =OFF SCOPE =SPFILE; 之后重启

   @marker_setup.sql
    @ddl_setup.sql
    @role_setup.sql

    GRANT GGS_GGSUSER_ROLE TO goldengate;
     @ddl_enable.sql
     @ddl_pin.sql goldengate
     @sequence.sql

dblogin userid  goldengate ,     password oracle
add checkpointtable goldengate.ckpt

ADD TRANDATA XX.XX

===========================================================================================================

add extract ext01, tranlog , begin now , threads 2 
add exttrail ./dirdat/ml , extract ext01 , megabytes 200

extract ext01
SETENV (ORACLE_HOME="/s01/orabase/product/11.2.0/dbhome_1" )
SETENV (ORACLE_SID="VRAC1")
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
--TRANLOGOPTIONS EXCLUDEUSER <Replicat username>
--CacheMgr CacheDirectory ./dirtmp 51980MB, CacheDirectory ./dirtmp/goldengate_tmp
userid goldengate , password oracle
--TranLogOptions ExcludeUser goldengate
--TranLogOptions AltArchivedLogFormat Instance NETDB1 %t_%s_%r.dbf
--TranLogOptions AltArchivedLogFormat Instance NETDB2 %t_%s_%r.dbf
--TranLogOptions AltArchiveLogDest Primary Instance NETDB1 /arch1, AltArchiveLogDest Instance NETDB2 /arch2
tranlogoptions asmuser sys@ASM , ASMPASSWORD oracle
--TRANLOGOPTIONS DBLOGREADER, DBLOGREADER BUFSIZE [x], BUFSIZE [y]
ThreadOptions MaxCommitPropagationDelay 10000 IOLatency 3000
exttrail ./dirdat/ml
DDL Include ALL
--DDL INCLUDE ALL, EXCLUDE OBJNAME "<schema name>.<object name> "
DDLOptions AddTranData, Report
DDLOptions NoCrossRename, Report
Table ogg_maclean.*;

-- Prevent data looping. This is generally used in bi-directional
-- configuration
TRANLOGOPTIONS EXCLUDEUSER <Replicat username>
===========================================================================================================
add extract pump01, EXTTRAILSOURCE ./dirdat/ml 
add rmttrail ./dirdat/ml , extract pump01, megabytes 200

pump 

extract pump01
SETENV (ORACLE_HOME = "<Oracle home path>" )
SETENV (ORACLE_SID="<Oracle sid>")

passthru
rmthost 192.168.1.179 , mgrport 7809 
rmttrail ./dirdat/ml
--DISCARDFILE <diacard file path/<discard file name.dsc>, APPEND Megabytes <n>
table ogg_maclean.*;

===========================================================================================================

add replicat rep01, exttrail ./dirdat/ml

replicat 

replicat rep01
SETENV (ORACLE_HOME = "/s01/oracle/app/oracle/product/11.2.0/dbhome_1" )
SETENV (ORACLE_SID="PRODA")
SETENV (NLS_LANG ="American_America.AL32UTF8")
userid goldengate , password oracle
--HandleCollisions
AssumeTargetDefs
DiscardFile ./dirrpt/rep1.dsc, APPEND Megabytes 800 ,  Purge
DBOptions DeferrefConst
DBOptions SuppressTriggers    
MaxTransOps 10000
GroupTransOps 1000
SQLEXEC "ALTER SESSION SET COMMIT_WRITE = BATCH,NOWAIT"
BatchSQL
--DDL INCLUDE ALL, EXCLUDE OBJNAME "<schema name>.<object name> "
DDLOptions Report
DDLError 24344 Ignore
DDLError 4052 Ignore
DDLError 955 Ignore
DDLError 1408 Ignore
DDLError 911 Ignore
AllowNoOpUpdates
CheckSequenceValue
--IGNORETRUNCATES
--DEFERAPPLYINTERVAL 1 MINUTES
-- Sequence testgg.*, Target testgg.*
MapExclude ogg_maclean.SYS_EXPORT_SCHEMA* ;
map ogg_maclean.* , target ogg_maclean.* ;

--DDLOPTIONS SUPPRESSTRIGGERS
--The SUPPRESSTRIGGERS parameter prevents triggers from firing on 
target objects that are configured for replication with Oracle GoldenGate. 
This alleviates the need to manually disable triggers and constraints.  
To use this option, the Replicat user must be an Oracle Streams administrator 
which can be granted by invoking dbms_goldengate_auth.grant_admin_privilege.
Available FROM ORACLE 10.2.0.5 AND Later Patches and Oracle 11.2.0.2

================================================================================================

DROP TABLE gg_test;
CREATE TABLE gg_test
(
    a        number(10),
    b             VARCHAR20(30),
        PRIMARY KEY (a)
        );
4.8    100字段表测试脚本
create table table_100cols
(
a1        number(10) not null,
a2        number(10),
a3        number(10),
a4        number(10),
a5        number(10),
a6        number(10),
a7        number(10),
a8        number(10),
a9        number(10),
a10       number(10),
a11       number(10),
a12       number(10),
a13       number(10),
a14       number(10),
a15       number(10),
a16       number(10),
a17       number(10),
a18       number(10),
a19       number(10),
a20       number(10),
a21       number(10),
a22       number(10),
a23       number(10),
a24       number(10),
a25       number(10),
a26       number(10),
a27       number(10),
a28       number(10),
a29       number(10),
a30       number(10),
a31       number(10),
a32       number(10),
a33       number(10),
a34       number(10),
a35       number(10),
a36       number(10),
a37       number(10),
a38       number(10),
a39       number(10),
a40       number(10),
a41       number(10),
a42       number(10),
a43       number(10),
a44       number(10),
a45       number(10),
a46       number(10),
a47       number(10),
a48       number(10),
a49       number(10),
a50       number(10),
a51       number(10),
a52       number(10),
a53       number(10),
a54       number(10),
a55       number(10),
a56       number(10),
a57       number(10),
a58       number(10),
a59       number(10),
a60       number(10),
a61       number(10),
a62       number(10),
a63       number(10),
a64       number(10),
a65       number(10),
a66       number(10),
a67       number(10),
a68       number(10),
a69       number(10),
a70       number(10),
a71       number(10),
a72       number(10),
a73       number(10),
a74       number(10),
a75       number(10),
a76       number(10),
a77       number(10),
a78       number(10),
a79       number(10),
a80       number(10),
a81       number(10),
a82       number(10),
a83       number(10),
a84       number(10),
a85       number(10),
a86       number(10),
a87       number(10),
a88       number(10),
a89       number(10),
a90       number(10),
a91       number(10),
a92       number(10),
a93       number(10),
a94       number(10),
a95       number(10),
a96       number(10),
a97       number(10),
a98       number(10),
a99       number(10),
a100      number(10)
)

alter table table_100cols
  add constraint PK_100cols primary key (a1)
  using index 
  tablespace CS_stat
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

  alter table table_100cols
  add constraint PK_100cols primary key (a1)
  using index 
  tablespace users
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

  insert into table_100cols values(1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17,
18,
19,
20,
21,
22,
23,
24,
25,
26,
27,
28,
29,
30,
31,
32,
33,
34,
35,
36,
37,
38,
39,
40,
41,
42,
43,
44,
45,
46,
47,
48,
49,
50,
51,
52,
53,
54,
55,
56,
57,
58,
59,
60,
61,
62,
63,
64,
65,
66,
67,
68,
69,
70,
71,
72,
73,
74,
75,
76,
77,
78,
79,
80,
81,
82,
83,
84,
85,
86,
87,
88,
89,
90,
91,
92,
93,
94,
95,
96,
97,
98,
99,
100);
4.9    性能测试脚本
create sequence seq_gg start with 1 INCREMENT BY 1 MAXVALUE 999999999 cache 500000;

create or replace procedure gg_insert
is
begin
    for i in 1..1000000 loop
        insert into CM_USER (USER_ID,REGION_CODE,ACC_ID,CAUT_ID,USER_STATUS,USER_TYPE)
        values(seq_gg.nextval,571,1111,2222,3,4);
        if mod(i,1000)=0 then
            commit;
        end if;
    end loop;
    commit;
end;
/

create or replace procedure gg_update
is
CURSOR c_gg IS
    SELECT rowid FROM CM_USER;
v_rowid UROWID;
i number(10);
BEGIN
    OPEN c_gg;
    for i in 1..1000000 loop
        FETCH c_gg INTO v_rowid;
        EXIT WHEN c_gg%NOTFOUND;

        UPDATE CM_USER SET ACC_ID=seq_gg.nextval WHERE rowid = v_rowid;
        if mod(i,1000)=0 then
            commit;
        end if;
    end loop;
    commit;
    CLOSE c_gg;
END;
/

create or replace procedure gg_delete
is
begin
    for i in 1..1000 loop
        delete from  CM_USER where rownum<1001;
        commit;
    end loop;
    commit;
end;
/

 

 

attachment:

ogg parameters

 

 

 

Oracle GoldenGate 在其新推出的版本11.2 中提供了全方位的多字节支持,
可实现对表名/列名,以及DDL 中常见多字节文字如中文的支持。

注意:不同字符集DML 复制必须要将目标的NLS_LANG 变量设置为源端的NLS_LANG 实际值。

 

实验证明,本版本可以支持相同和不同字符集之间带有多字节对象以及数据的DML 和DDL 复制。

新版本的 DDL 复制默认不再开启 addtrandata,需要手工配置ddloptions。Oracle 11.2.0.2以上版本则推荐使用schema level 附加日志,参考步骤:

改用schemal level trandata:
SQL> exec dbms_streams_auth.grant_admin_privilege(‘ogg’);
GGSCI (dbsrv64.localdomain) 2> dblogin userid ogg
GGSCI (dbsrv64.localdomain) 3> ADD SCHEMATRANDATA source
2012-02-23 03:26:04 INFO OGG-01788 SCHEMATRANDATA has
been added on schema source.

 

注意:目前似乎不支持在 map 或者 table 里面使用中文进行匹配,
需要进一步验证。例如下面的将全部被认为是unmapped:
map source.中文*, target target.*;
如果两端字符集不同,则需要将目标的 NLS_LANG 变量设置为源
端的NLS_LANG实际值,可参考之前的最佳实践。
使用defgen 可以指定生成的表定义文件编码,不过实际配置要注意环境变量与数据库环境变量的统一,例如中文环境参考如下:
NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
LANG=zh_CN.GBK

 

Oracle GoldenGate 在其新推出的版本11.2 中提供了两种模式抽取:
1 传统的挖掘日志模式;
2 新的整合捕获模式。该模式使用了Oracle XStream 接口进行数据捕捉。

 schema level trandata 必须要Oracle 11.2.0.2 以上;
 整合模式只有在11.2.0.3 以上才能支持更为广泛的数据类型。

 

1) 数据库打开最小附加日志;
alter database add supplemental log data;
2) 为source schema 加入schema level 附加日志:
SQL> exec dbms_streams_auth.grant_admin_privilege(‘ogg’);
GGSCI (dbsrv64.localdomain) 2> dblogin userid ogg
GGSCI (dbsrv64.localdomain) 3> ADD SCHEMATRANDATA source
2012-02-23 03:26:04 INFO OGG-01788 SCHEMATRANDATA has been
added on schema source.
3) 给抽取进程用户赋予admin 权限:
begin
DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => ‘source’,
privilege_type => ‘capture’,
grant_select_privileges => true,
do_grants => TRUE
);
end;
/
4) 在数据库中注册抽取进程,此时会自动配置XStream:
DBLOGIN USERID source, PASSWORD oracle
REGISTER EXTRACT ext01 DATABASE
5) 配置OGG 的extract 和replicat 进程
ADD EXTRACT ext01, INTEGRATED TRANLOG, BEGIN NOW

 

 

Add exttrail ./dirdat/ml, ext ext01
Add rep rep01, exttrail ./dirdat/ia
GGSCI (dbsrv64.localdomain) 5> view param ext01
EXTRACT ext01
SETENV (ORACLE_SID = “oragbk”)
–SETENV (NLS_LANG = “AMERICAN_AMERICA.ZHS16GBK”)
SETENV (NLS_LANG = “SIMPLIFIED CHINESE_CHINA.ZHS16GBK”)
USERID source, PASSWORD oracle
ddl include all
–ddloptions addtrandata,report
ddloptions report
tranlogoptions dblogreader
EXTTRAIL ./dirdat/ml
dynamicresolution
tableexclude source.AQ*;
tableexclude source.OGG*;
tableexclude source.SYS*;
table source.*;

 

GGSCI (dbsrv64.localdomain) 6> view param rep01
replicat rep01

 

–checkparams
–SETENV (ORACLE_SID = “oragbk”)
SETENV (ORACLE_SID = “orautf”)
SETENV (NLS_LANG = “SIMPLIFIED CHINESE_CHINA.ZHS16GBK”)
–SETENV (NLS_LANG = “AMERICAN_AMERICA.AL32UTF8″)
userid target, password oracle
ddl include mapped
ddlerror 24344,ignore
ddlerror 955,ignore
–DDLOPTIONS REPORT
reperror default,discard
–reperror default, abend
discardfile ./dirrpt/repia.dsc,append
assumetargetdefs
map source.*, target target.*;
6) 确认进程启动后,在源端添加中文表,并增加和更新记录,成功。
注意:
使用整合模式会在extract 用户下建立一些AQ$/SYS/OGG$开头的表,建议使用一个专门用户。本例子中直接抽取了source 自己的表,需要将这些系统表排除掉

 

 

GGSCI (mlab2.oracle.com) 2>  ADD SCHEMATRANDATA ogg_maclean

2013-11-24 00:42:48  ERROR   OGG-01790  Failed to ADD SCHEMATRANDATA on schema ogg_maclean because of the following SQL error: ORA-26947: Oracle GoldenGate replication is not enabled.
ORA-06512: at “SYS.DBMS_CAPTURE_ADM_INTERNAL”, line 1565
ORA-06512: at “SYS.DBMS_CAPTURE_ADM_INTERNAL”, line 1074
ORA-06512: at “SYS.DBMS_CAPTURE_ADM”, line 722
ORA-06512: at line 1 SQL BEGIN sys.dbms_capture_adm.PREPARE_SCHEMA_INSTANTIATION(‘ogg_maclean’,’ALLKEYS_ON’); END;.

GGSCI (mlab2.oracle.com) 3> ADD SCHEMATRANDATA  goldengate;

2013-11-24 00:43:29  ERROR   OGG-01796  Schema: goldengate;, does not exist.

GGSCI (mlab2.oracle.com) 4> ADD SCHEMATRANDATA  goldengate

2013-11-24 00:43:38  ERROR   OGG-01790  Failed to ADD SCHEMATRANDATA on schema goldengate because of the following SQL error: ORA-26947: Oracle GoldenGate replication is not enabled.
ORA-06512: at “SYS.DBMS_CAPTURE_ADM_INTERNAL”, line 1565
ORA-06512: at “SYS.DBMS_CAPTURE_ADM_INTERNAL”, line 1074
ORA-06512: at “SYS.DBMS_CAPTURE_ADM”, line 722
ORA-06512: at line 1 SQL BEGIN sys.dbms_capture_adm.PREPARE_SCHEMA_INSTANTIATION(‘goldengate’,’ALLKEYS_ON’); END;.

11.2.0.4中需要设置

SQL> alter system set enable_goldengate_replication=true;

System altered.

 

 

WARNING OGG-02051 Not enough database memory to service Extract in integrated capture.

==>Streams: resolve low memory condition+LogMiner reader: buffer+LogMiner builder: memory=> Streams Pool Size 不足 增大streams_pool_size即可

 

 

 

source: HP-IA 192.168.174.201/202    root/roothp sys/sys or oracle
target: Linux  192.168.110.200/116.180 root/roothp 
安装目录:/app/ogg/va

复制表: unipostdb.
T_PUB_JNL_SAV
T_PUB_UNREG
T_CDM_LEDGER

TMP_CARD_LEDGER                TABLE
TMP_LEDGER                     TABLE
TRIG_CDM_LEDGER                TABLE
T_CARD_ACC                     TABLE
T_CARD_ATM                     TABLE
T_CARD_LEDGER                  TABLE
T_CDM_LEDGER                   TABLE
T_PUB_JNL_SAV                  TABLE
T_PUB_UNREG                    TABLE
UNIPOSTDB_TEST                 TABLE
UNREG_BAK                      TABLE

conn unipostdb/unipostdb

select count(*) from unipostdb.T_PUB_JNL_SAV;
select count(*) from unipostdb.T_PUB_UNREG;
select count(*) from unipostdb.T_CDM_LEDGER;

 truncate table unipostdb.T_PUB_JNL_SAV;

truncate table unipostdb.T_PUB_UNREG;

alter extga, begin now
alter extgb, begin now
alter extgc, begin now
alter extgd, begin now

Set pages 999
Set long 4000
Select dbms_metadata.get_ddl('TABLE', 'T_PUB_JNL_SAV', 'UNIPOSTDB') from dual;
Select dbms_metadata.get_ddl('TABLE', 'T_PUB_UNREG', 'UNIPOSTDB') from dual;
Select dbms_metadata.get_ddl('TABLE', 'T_CDM_LEDGER', 'UNIPOSTDB') from dual;

 ,CLT_SEQNO 

col table_name for a20
col constraint_name for a20
col column_name for a20
select cols.table_name,cols.constraint_name,cols.column_name from user_cons_columns cols, user_constraints cons 
where  cons.table_name = 'T_PUB_UNREG'  and cols.constraint_name=cons.constraint_name;

GLOBALS:
CHECKPOINTTABLE ogg.chkpt
GGSCHEMA ogg

ogg source:
add checkpointtab 

add ext extga, tranlog, begin now, threads 2
add rmttrail ./dirdat/ra, ext extga, megabytes 100

add ext extgb, tranlog, begin now, threads 2
add rmttrail ./dirdat/rb, ext extgb, megabytes 100

add ext extgc, tranlog, begin now, threads 2
add rmttrail ./dirdat/rc, ext extgc, megabytes 100

EXTRACT extga
USERID ogg, PASSWORD ogg
rmthost  192.168.110.200, mgrport 7809
rmttrail ./dirdat/ra 
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS BUFSIZE 10000000, DBLOGREADERBUFSIZE 4194304
REPORTCOUNT EVERY 2 MINUTES, RATE
--dynamicresolution
--GETTRUNCATES
table unipostdb.T_PUB_JNL_SAV;

EXTRACT extgb
USERID ogg, PASSWORD ogg
rmthost  192.168.110.200, mgrport 7809
rmttrail ./dirdat/rb 
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS BUFSIZE 10000000, DBLOGREADERBUFSIZE 4194304
REPORTCOUNT EVERY 2 MINUTES, RATE
--dynamicresolution
--GETTRUNCATES
table unipostdb.T_PUB_UNREG;

EXTRACT extgc
USERID ogg, PASSWORD ogg
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS BUFSIZE 10000000, DBLOGREADERBUFSIZE 4194304
rmthost  192.168.110.200, mgrport 7809
rmttrail ./dirdat/rc 
REPORTCOUNT EVERY 2 MINUTES, RATE
--dynamicresolution
--GETTRUNCATES
table unipostdb.T_CDM_LEDGER;

ogg target:
-----------------------------------------------------
add rep repaa, exttrail ./dirdat/ra

replicat repaa
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repaa.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_PUB_JNL_SAV, FILTER ( @RANGE (1, 3) );

add rep repab, exttrail ./dirdat/ra

replicat repab
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repab.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_PUB_JNL_SAV, FILTER ( @RANGE (2, 3) );

add rep repac, exttrail ./dirdat/ra

replicat repac
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repac.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_PUB_JNL_SAV, FILTER ( @RANGE (3, 3) );
-------------------------------------------

add rep repba, exttrail ./dirdat/rb
edit param repba

replicat repba
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repba.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_PUB_UNREG, FILTER ( @RANGE (1, 3) );

add rep repbb, exttrail ./dirdat/rb
edit param repbb

replicat repbb
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repbb.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_PUB_UNREG, FILTER ( @RANGE (2, 3) );

add rep repbc, exttrail ./dirdat/rb

replicat repbc
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repbc.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_PUB_UNREG, FILTER ( @RANGE (3, 3) );

-------------------------------------------------------------------

add rep repca, exttrail ./dirdat/rc
edit param repca

replicat repca
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repca.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_CDM_LEDGER, FILTER ( @RANGE (1, 3) );

add rep repcb, exttrail ./dirdat/rc
edit param repcb

replicat repcb
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repcb.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_CDM_LEDGER, FILTER ( @RANGE (2, 3) );

add rep repcc, exttrail ./dirdat/rc
edit param repcc

replicat repcc
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repcc.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_CDM_LEDGER, FILTER ( @RANGE (3, 3) );

----------------------------------

alter repba, extseqno 0, extrba 0
alter repbb, extseqno 0, extrba 0
alter repbc, extseqno 0, extrba 0

alter repca, extseqno 0, extrba 0
alter repcb, extseqno 0, extrba 0
alter repcc, extseqno 0, extrba 0

-----------------------------------------
linux:
processor       : 63
vendor_id       : GenuineIntel
cpu family      : 6
model           : 46
model name      : Intel(R) Xeon(R) CPU           X7560  @ 2.27GHz
stepping        : 6
cpu MHz         : 2261.119
cache size      : 24576 KB
physical id     : 7
siblings        : 8
core id         : 11
cpu cores       : 4
apicid          : 247
fpu             : yes
fpu_exception   : yes
cpuid level     : 11
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm 
syscall nx rdtscp lm constant_tsc ida nonstop_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm
bogomips        : 4522.22
clflush size    : 64
cache_alignment : 64
address sizes   : 44 bits physical, 48 bits virtual
power management: [8]

hp-unix
32c X 2 

------------------------------

PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS

35 + 40 G日志,产生24G队列

Log Read Checkpoint  Oracle Redo Logs
                     2011-12-30 14:49:21  Thread 1, Seqno 1956, RBA 397949752
Log Read Checkpoint  Oracle Redo Logs
                     2011-12-30 14:49:21  Thread 2, Seqno 1339, RBA 293856

----------------------------------------------------------------------

add ext extgd, tranlog, begin now, threads 2
add rmttrail ./dirdat/rd, ext extgd, megabytes 100

EXTRACT extgd
USERID ogg, PASSWORD ogg
rmthost  192.168.110.200, mgrport 7809
rmttrail ./dirdat/rd
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS BUFSIZE 10000000, DBLOGREADERBUFSIZE 4194304
REPORTCOUNT EVERY 2 MINUTES, RATE
--dynamicresolution
GETTRUNCATES
table unipostdb.T_PUB_JNL_SAV, FILTER ( @RANGE (2, 2,  CLT_SEQNO) );

-----------

add rep repda, exttrail ./dirdat/rd

replicat repda
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repda.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_PUB_JNL_SAV, FILTER ( @RANGE (1, 3) );

add rep repdb, exttrail ./dirdat/rd

replicat repdb
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repdb.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_PUB_JNL_SAV, FILTER ( @RANGE (2, 3) );

add rep repdc, exttrail ./dirdat/rd

replicat repdc
userid ogg, password ogg
reperror default, abend
discardfile ./dirrpt/repdc.dsc,append, megabytes 10
REPORTCOUNT EVERY 2 MINUTES, RATE
GROUPTRANSOPS 2000
BATCHSQL
assumetargetdefs
--allownoopupdates
--dynamicresolution
map unipostdb.T_PUB_JNL_SAV, target unipostdb.T_PUB_JNL_SAV, FILTER ( @RANGE (3, 3) );

-----------------------------------
session_cached_cursors 50 -> 200

 

 

 

very_large_table.sql