重庆思庄oracle技术分享-ogg目标端表比源端多一个字段的同步测试

文档课题：ogg目标端表比源端多一个字段的同步测试.

源端：192.168.133.108 数据库oracle 10.2.0.4 64位，实例名：orcl

目标端：192.168.133.109数据库oracle19.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, MEGABYTES1024

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,namevarchar2(20));

Table created.

SYS@orcl> desc ot

Name Null? Type

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

ID NUMBER

NAME VARCHAR2(20)

SYS@orcl> alter table ot add constraintpk_ot_id primary key (id);

Table altered.

SYS@orcl> selectowner,constraint_name,constraint_type,table_name from user_constraints wheretable_name='OT'

OWNER CONSTRAINT_NAME CTABLE_NAME

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

SCOTT PK_OT_ID POT

--目标端查询.

SYS@simdb> conn scott/tiger

Connected.

Session altered.

SCOTT@simdb> desc ot

Name Null? Type

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

ID NUMBER

NAME VARCHAR2(20)

SCOTT@simdb> selectowner,constraint_name,constraint_type,table_name from user_constraints wheretable_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 NOTNULL 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;

IDNAME

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

1Miss

--目标端查询数据已同步.

SCOTT@simdb> select * from ot

IDNAME TIME

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

1Miss

--源端update数据

SCOTT@orcl> update ot set name='leo' whereid=1;

1 row updated.

SCOTT@orcl> commit;

Commit complete.

SCOTT@orcl> select * from ot;

IDNAME

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

1leo

--目标端查询数据已更新

SCOTT@simdb> select * from ot;

IDNAME TIME

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

1leo

说明：如上所示，目标端表比源端多出一个字段的情况下，源端的dml操作依然会同步到目标端.

4.2、隐藏列测试

--将目标端ot表字段time设置为隐藏.

SCOTT@simdb> alter table ot modify timeinvisible;

Table altered.

SCOTT@simdb> desc ot

Name Null? Type

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

ID NOT NULL NUMBER

NAME VARCHAR2(20)

SCOTT@simdb> selecttable_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;

IDNAME

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

1leo

SCOTT@orcl> update ot set name='alina' whereid=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;

IDNAME

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

1alina

2 paul

--目标端查询数据

SCOTT@simdb> select * from ot;

IDNAME

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

1alina

2paul

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