测试环境:
源端SUSE10 32bit
目标端SUSE11 64bit
测试环境ogg配置信息:
源端mgr、ext、pump
GGSCI (testa) 13> view params mgr
port 7809
GGSCI (testa) 14> view params ext1
extract ext1
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid goldengate@stm01, password goldengate
DISCARDFILE
exttrail /ogg/stm01trail/lt
GETTRUNCATES;
obey ./dirsql/transdb_table.txt
GGSCI (testa) 15> view params pump1
extract pump1
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
passthru
rmthost 192.168.211.12, mgrport 7809, compress
rmttrail /ogg/transdbtrail/rt
GETTRUNCATES;
table testogg.*;
GGSCI (testa) 16> exit
ogg@testa:~> more ./dirsql/transdb_table.txt
TABLE TESTOGG.TESTA;
TABLE TESTOGG.TESTB;
目标端mgr、replicat
GGSCI (transdb) 3> view params mgr
PORT 7809
DYNAMICPORTLIST 7840-7914
PURGEOLDEXTRACTS /ogg/transdbtrail/*,usecheckpoints, minkeepdays 3
PURGEOLDEXTRACTS /ogg/targetdbtrail/*,usecheckpoints, minkeepdays 3
LAGREPORTHOURS 1
LAGINFOMINUTES 30
GGSCI (transdb) 4> view params rep1
REPLICAT rep1
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID goldengate, PASSWORD goldengate
SQLEXEC "ALTER SESSION SET CONSTRAINTS=DEFERRED"
REPORT AT 00:01
REPORTCOUNT EVERY 720 MINUTES, RATE
REPERROR DEFAULT, ABEND
assumetargetdefs
DISCARDFILE ./dirrpt/rep1.dsc, APPEND, MEGABYTES 100
DISCARDROLLOVER AT 02:30
GETTRUNCATES
ALLOWNOOPUPDATES
MAP TESTOGG.*,
源端和目标端建立的测试表语句:
create table testb (a int not null,b int not null,c int);
create unique index idx01 on testb (b);
create index idx on testb (a);
源端进行add trandata操作语句:
GGSCI (testa) 1> dblogin userid goldengate, password goldengate
Successfully logged into database.
GGSCI (testa) 2> add trandata testogg.testb
Logging of supplemental redo data enabled for table TESTOGG.TESTB.
查看源端数据库testb的补充日志字段
SQL> select * from dba_log_group_columns
OWNER
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ------
TESTOGG
可以看到ogg命令行中add trandata操作在oracle数据库中补充日志选择的是仅有的那个非空唯一索引所在列
在源端插入测试数据
SQL> insert into testb values (1,1,1);
SQL> insert into testb values (1,2,3);
SQL> insert into testb values (2,3,3);
SQL> commit;
Commit complete.
在源端更新数据
SQL> update testb set c=2 where c=1;
1 row updated.
SQL> commit;
Commit complete.
注意此时同步过去的值应该是c(改变数据)和b(补充日志记录字段)
去目标端队列文件中查看此sql的队列信息
GGSCI (transdb) 11> info rep1
REPLICAT
Checkpoint Lag
Log Read Checkpoint
查看logdump找到最新的一条记录
ogg@transdb:/ogg/transdbtrail> logdump
Oracle GoldenGate Log File Dump Utility for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
Logdump 14 >open rt000011
Current LogTrail is /ogg/transdbtrail/rt000011
Logdump 15 >ghdr on
Logdump 16 >detail on
一直n直到没消息显示
Logdump 30 >n
___________________________________________________________________
Hdr-Ind
UndoFlag
RecLength
IOType
TransInd
SyskeyLen
AuditRBA
Continued
2012/08/25 13:43:29.607.325 Insert
Name: TESTOGG.TESTB
After
Column
Column
Column
Logdump 31 >n
___________________________________________________________________
Hdr-Ind
UndoFlag
RecLength
IOType
TransInd
SyskeyLen
AuditRBA