ogg目标端表比源端多一个字段的同步测试

文档课题:目标端表比源端多一个字段的同步测试.
源 端:192.168.133.108 数据库oracle 10.2.0.4 64位,实例名:orcl
目 标 端:192.168.133.109 数据库oracle 19.16.0.0 64位,实例名:simdb
ogg安装包:
源 端:fbo_ggs_Linux_x64_ora10g_64bit.tar
目 标 端:191004_fbo_ggs_Linux_x64_shiphome.zip
说明:源端与目标端的ogg安装包不同.
1、ogg配置
ogg进程配置如下:
--源端抓取进程.
GGSCI (leo-10g-ogg) 5> view param exta

EXTRACT exta
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
setenv (ORACLE_SID=orcl)
setenv (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
userid ogg@ORCL,password ogg
GETTRUNCATES
REPORTCOUNT EVERY 30 MINUTES,RATE
numfiles 5000
DISCARDFILE ./dirrpt/exta_ss.dsc, APPEND, MEGABYTES 1024
DISCARDROLLOVER AT 3:00
WARNLONGTRANS 2h,CHECKINTERVAL 3m
EXTTRAIL ./dirdat/ra,MEGABYTES 1024
DYNAMICRESOLUTION
NOCOMPRESSUPDATES
NOCOMPRESSDELETES
STATOPTIONS REPORTFETCH
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA
DDLOPTIONS REPORT
TRANLOGOPTIONS EXCLUDEUSER ogg
table scott.*;
--源端投递进程.
GGSCI (leo-10g-ogg) 6> view param dp1

extract dp1
userid ogg@ORCL,password ogg
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
passthru
rmthost 192.168.133.109,mgrport 7809
rmttrail ./dirdat/ra
table SCOTT.*;
--目标端应用进程.
GGSCI (leo-19c-ogg) 5> view param rep1

REPLICAT rep1
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
--setenv (ORACLE_SID=)
userid ogg@SIMDB,password ogg
REPORT AT 08:59
REPORTCOUNT EVERY 30 MINUTES, RATE
CACHEMGR CACHESIZE 2048MB,CACHEDIRECTORY ./dirtmp
REPERROR DEFAULT, ABEND
DISCARDFILE ./dirrpt/rep1.dsc, APPEND, MEGABYTES 1024
DISCARDROLLOVER AT 3:00
GETTRUNCATES
ALLOWNOOPUPDATES
APPLYNOOPUPDATES
ASSUMETARGETDEFS
DBOPTIONS DEFERREFCONST
DBOPTIONS SUPPRESSTRIGGERS
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
DDLERROR DEFAULT IGNORE RETRYOP
MAP SCOTT.*, TARGET SCOTT.*;
2、ogg状态
--源端
GGSCI (leo-10g-ogg) 1> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 117:40:20
EXTRACT RUNNING EXTA 117:39:52 00:00:02
--目标端
GGSCI (leo-19c-ogg) 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:00
3、环境准备
3.1、建表
--源端创建ot表.
SYS@orcl> conn scott/tiger
Connected.
SYS@orcl> create table ot (id number,name varchar2(20));

Table created.

SYS@orcl> desc ot
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(20)
SYS@orcl> alter table ot add constraint pk_ot_id primary key (id);

Table altered.

SYS@orcl> select owner,constraint_name,constraint_type,table_name from user_constraints where table_name='OT'

OWNER CONSTRAINT_NAME C TABLE_NAME
--------------- ------------------------------ - --------------------
SCOTT PK_OT_ID P OT
--目标端查询.
SYS@simdb> conn scott/tiger
Connected.

Session altered.

SCOTT@simdb> desc ot
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(20)

SCOTT@simdb> select owner,constraint_name,constraint_type,table_name from user_constraints where table_name='OT'

OWNER CONSTRAINT_NAME C TABLE_NAME
--------------- --------------- - --------------------
SCOTT PK_OT_ID P OT
说明:源端创建的ot表以及主键均同步到目标端.
3.2、增加字段
--目标端增加time字段.
SCOTT@simdb> alter table ot add (time timestamp(6));

Table altered.

SCOTT@simdb> desc ot
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(20)
TIME TIMESTAMP(6)
--此时源端表结构无变化.
SYS@orcl> conn scott/tiger
Connected.

Session altered.

SCOTT@orcl> desc ot
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(20)
4、开始测试
4.1、dml测试
--源端向ot表插入数据.
SQL> insert into ot values(1,'Miss');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from ot;

ID NAME
---------- --------------------
1 Miss
--目标端查询数据已同步.
SCOTT@simdb> select * from ot

ID NAME TIME
---------- -------------------- --------------------
1 Miss
--源端update数据
SCOTT@orcl> update ot set name='leo' where id=1;

1 row updated.

SCOTT@orcl> commit;

Commit complete.

SCOTT@orcl> select * from ot;

ID NAME
---------- --------------------
1 leo
--目标端查询数据已更新
SCOTT@simdb> select * from ot;

ID NAME TIME
---------- -------------------- --------------------
1 leo
说明:如上所示,目标端表比源端多出一个字段的情况下,源端的dml操作依然会同步到目标端.
4.2、隐藏列测试
--将目标端ot表字段time设置为隐藏.
SCOTT@simdb> alter table ot modify time invisible;

Table altered.
SCOTT@simdb> desc ot
Name Null? Type
----------------------- -------- ----------------
ID NOT NULL NUMBER
NAME VARCHAR2(20)
SCOTT@simdb> select table_name,column_name,hidden_column from user_tab_cols where table_name='OT'

TABLE_NAME COLUMN_NAME HID
-------------------- -------------------- ---
OT ID NO
OT NAME NO
OT TIME YES
--源端update数据.
SCOTT@orcl> select * from ot;

ID NAME
---------- --------------------
1 leo

SCOTT@orcl> update ot set name='alina' where id=1;

1 row updated.
SCOTT@orcl> insert into ot values (2,'paul');

1 row created.

SCOTT@orcl> commit;

Commit complete.

SCOTT@orcl> select * from ot;

ID NAME
---------- --------------------
1 alina
2 paul
--目标端查询数据
SCOTT@simdb> select * from ot;

ID NAME
---------- --------------------
1 alina
2 paul

说明:将目标端多出的字段设置为invisible,不影响源端目标端的同步.