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，不影响源端目标端的同步.