宁化网站建设,网站数据分析工具有哪些,名片设计模板,手机壳定制网站制作置顶#xff01;#xff01;#xff01;主页禁言提示原因#xff1a;在自己论坛发动态误带敏感词#xff0c;在自己论坛禁止评论90天 置顶#xff01;#xff01;#xff01;主页禁言提示原因#xff1a;在自己论坛发动态误带敏感词#xff0c;在自己论坛禁止评论90天…置顶主页禁言提示原因在自己论坛发动态误带敏感词在自己论坛禁止评论90天 置顶主页禁言提示原因在自己论坛发动态误带敏感词在自己论坛禁止评论90天 置顶主页禁言提示原因在自己论坛发动态误带敏感词在自己论坛禁止评论90天 置顶主页禁言提示原因在自己论坛发动态误带敏感词在自己论坛禁止评论90天 置顶主页禁言提示原因在自己论坛发动态误带敏感词在自己论坛禁止评论90天 置顶主页禁言提示原因在自己论坛发动态误带敏感词在自己论坛禁止评论90天 置顶主页禁言提示原因在自己论坛发动态误带敏感词在自己论坛禁止评论90天 置顶主页禁言提示原因在自己论坛发动态误带敏感词在自己论坛禁止评论90天 置顶主页禁言提示原因在自己论坛发动态误带敏感词在自己论坛禁止评论90天 置顶主页禁言提示原因在自己论坛发动态误带敏感词在自己论坛禁止评论90天 置顶主页禁言提示原因在自己论坛发动态误带敏感词在自己论坛禁止评论90天 置顶主页禁言提示原因在自己论坛发动态误带敏感词在自己论坛禁止评论90天 置顶主页禁言提示原因在自己论坛发动态误带敏感词在自己论坛禁止评论90天 置顶主页禁言提示原因在自己论坛发动态误带敏感词在自己论坛禁止评论90天 置顶主页禁言提示原因在自己论坛发动态误带敏感词在自己论坛禁止评论90天 置顶主页禁言提示原因在自己论坛发动态误带敏感词在自己论坛禁止评论90天
博主所在单位目前使用Oracle GoldenGate将各个业务生产库汇聚到一起做数仓实时ODS平台源端库可能涉及Oracle、Mysql、达梦、Guassdb库。为了做增量etl以及记录每条数据的变更历史
我们采用异构同步即源端同步过来的表在ODS新增了一个etltime字段用来记录当前数据变更时间。为了记录数据的事务变更历史记录我们将数据的变更记录映射同步到一张tab_name_audit表中。为了防止源端业务库误删数据我们将被删除的数据映射同步到一张tab_name_his表中。原表映射到ods后还是正常的映射同步dml操作。
至于以上方式是怎么实现的我这里给大家在本地电脑做一次模拟。至于GoldenGate如何安装请看博主其他相关文章这里就不赘述了 一、GoldenGate简介 GoldenGate公司成立于1995年的美国加州旧金山 它的名称源自旧金山闻名于世的金门大桥。 该公司于2009年9月被Oracle公司收购在此之前Oracle和GoldGate公司就有了长达超过10年的合作关系。 GoldenGate几乎支持市面上流行的所有主流的操作系统平台和数据库。 在不同的平台生成的core文件有不同的本地堆栈跟踪工具来分析。
GoldenGate的模式包括下图中描述的几种其中“一对一”是GoldenGate最简单的一种模式也是最常用的模式。 GoldenGate的大致工作原理
GoldenGate能够解决如下不同的需求 好了大致介绍就说到这里相信需要本篇文章帮助的网友们对以上肯定都很熟悉了直接奔主题 二、环境简介
1、本实验用到的OGG版本
[oracleOGG ogg11]$ ./ggsci -vOracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.2、实验中使用的重点参数
下面仅列出来两个重要参数其他参数请参考官方文档或本博主的另一篇介绍ogg三大进程参数的博客 --------------------NOCOMPRESSDELETES COMPRESSDELETES | NOCOMPRESSDELETES Valid for Extract Use the COMPRESSDELETES and NOCOMPRESSDELETES parameters to control the way columns are written to the trail record for delete operations. COMPRESSDELETES, the default, causes Extract to write only the primary key to the trail for delete operations. The key provides enough information to delete the correct target record, while restricting the amount of data that must be processed. NOCOMPRESSDELETES sends all of the columns to the trail. This becomes the default when a table definition does not include a primary key or unique index. If a substitute key was defined with the KEYCOLS option of TABLE, then those columns are written to the trail, whether or not a real key was defined. NOCOMPRESSDELETES also is required when using the Conflict Detection and Resolution (CDR) feature for a DB2 database on any of the platforms that are supported by Oracle GoldenGate. For more information about CDR, see the Oracle GoldenGate Windows and UNIX Administrator’s Guide. COMPRESSDELETES and NOCOMPRESSDELETES can be used globally for all TABLE statements in the parameter file, or they can be used as on-off switches for individual TABLE statements. These parameters do not affect data pumps. Default COMPRESSDELETES Syntax COMPRESSDELETES | NOCOMPRESSDELETES ------------------------INSERTDELETES INSERTDELETES | NOINSERTDELETES Valid for Replicat Use the INSERTDELETES and NOINSERTDELETES parameters to control whether or not Oracle GoldenGate converts source delete operations to insert operations on the target database. The parameters are table-specific. One parameter remains in effect for all subsequent MAP statements, until the other parameter is encountered. When using INSERTDELETES, use the NOCOMPRESSDELETES parameter so that Extract does not compress deletes. Default NOINSERTDELETES Syntax INSERTDELETES | NOINSERTDELETES 测试开始前使用根据生产配置提前配置好的正常同步的ogg环境环境部署忽略目的端配置在原有的一对三模式下增加一个对只存被删除的整条记录的 历史表tablename_DEL被删除记录的完整记录操作类型和被删除时间以及是前映像还是后映像来测试
二、源库创建测试用表
1、创建有物理主键表
CREATE TABLE DEPT
(DEPTNO NUMBER(2) NOT NULL,DNAME VARCHAR2(14),LOC VARCHAR2(13)
)
TABLESPACE USERSPCTFREE 10INITRANS 1MAXTRANS 255STORAGE(INITIAL 64KNEXT 1MMINEXTENTS 1MAXEXTENTS UNLIMITED);
-- CREATE/RECREATE PRIMARY, UNIQUE AND FOREIGN KEY CONSTRAINTS
ALTER TABLE DEPTADD CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)USING INDEX TABLESPACE USERSPCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 64KNEXT 1MMINEXTENTS 1MAXEXTENTS UNLIMITED);
BEGIN
FOR I IN 1..10
LOOP
INSERT INTO DEPT VALUES(I,A,B);
END LOOP;
COMMIT;
END;
/2、创建无物理主键有逻辑主键表逻辑主键EMPNO,ENAME
CREATE TABLE NOPK_TAB
(EMPNO NUMBER(6),
ENAME VARCHAR2(20),
SAL NUMBER(7,2),
DEPTNO NUMBER(4)
);INSERT INTO NOPK_TAB VALUES(1110,ZHAO,100,10);
INSERT INTO NOPK_TAB VALUES(1110,YAN,200,20);
INSERT INTO NOPK_TAB VALUES(1111,YAN,100,10);
INSERT INTO NOPK_TAB VALUES(1111,DONG,100,30);三、目标端创建审计表和只存被删除记录历史表
1、创建审计表
CREATE TABLE SCOTT.NOPK_TAB_AUDIT
(
EMPNO NUMBER(6),
ENAME VARCHAR2(20),CSN NUMBER,OPTIME TIMESTAMP(6),OPTYPE VARCHAR2(20),INSERTTIME TIMESTAMP(6),CURDATE TIMESTAMP(6)
);
CREATE TABLE DEPT_AUDIT
(DEPTNO NUMBER(2),CSN NUMBER,OPTIME TIMESTAMP(6),OPTYPE VARCHAR2(20),INSERTTIME TIMESTAMP(6),CURDATE TIMESTAMP(6)
)2、创建只存删除记录的历史表
CREATE TABLE DEPT_DEL
(DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13),BEFORE_AFTER VARCHAR2(20),DEL_DATE TIMESTAMP,OP_FLAG VARCHAR2(20)
);
CREATE TABLE NOPK_TAB_DEL
(EMPNO NUMBER(6),ENAME VARCHAR2(20),SAL NUMBER(7,2),DEPTNO NUMBER(4),BEFORE_AFTER VARCHAR2(20),DEL_DATE TIMESTAMP,OP_FLAG VARCHAR2(20)
);四、停止抽取进程和投递进程
stop er *五、添加附加日志
dblogin userid ogg password ogg
add trandata SCOTT.dept
add trandata SCOTT.NOPK_TAB,nokey,cols(EMPNO,ENAME)六、配置抽取进程
extract e_sc
userid ogg,password ogg
setenv(NLS_LANGAMERICAN_AMERICA.AL32UTF8)
setenv(ORACLE_SIDorcl)
reportcount every 10 minutes,rate
numfiles 5000
discardfile ./dirrpt/e_sc.dsc,append,megabytes 1000
warnlongtrans 2h,checkinterval 3m
exttrail ./dirdat/sc
threadoptions maxcommitpropagationdelay 60000
dboptions allowunusedcolumn
tranlogoptions archivedlogonly
tranlogoptions altarchivelogdest primary /u01/archivelog
TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT %t_%s_%r.dbf
dynamicresolution
ddl include mapped
ddloptions addtrandata,report
notcpsourcetimer
nocompressupdates
--fetchoptions nousesnapshot
fetchoptions USEROWID
--GETUPDATEBEFORES
NOCOMPRESSDELETES
----------scott.EMP
table SCOTT.EMP,tokens(
TKN-CSN GETENV(TRANSACTION, CSN),
TKN-COMMIT-TS GETENV (GGHEADER, COMMITTIMESTAMP),
TKN-OP-TYPE GETENV (GGHEADER, OPTYPE)
);
----------SCOTT.DEPT
table SCOTT.DEPT,tokens(
TKN-CSN GETENV(TRANSACTION, CSN),
TKN-COMMIT-TS GETENV (GGHEADER, COMMITTIMESTAMP),
TKN-OP-TYPE GETENV (GGHEADER, OPTYPE)
);
----------SCOTT.DEL
table SCOTT.DEL,tokens(
TKN-CSN GETENV(TRANSACTION, CSN),
TKN-COMMIT-TS GETENV (GGHEADER, COMMITTIMESTAMP),
TKN-OP-TYPE GETENV (GGHEADER, OPTYPE)
);
----------SCOTT.NOPK_TAB
TABLE SCOTT.NOPK_TAB_TAB,KEYCOLS(EMPNO,ENAME),TOKENS(
TKN-CSN GETENV(TRANSACTION, CSN),
TKN-COMMIT-TS GETENV (GGHEADER, COMMITTIMESTAMP),
TKN-OP-TYPE GETENV (GGHEADER, OPTYPE)
);七、目标库应用进程配置
replicat r_sc2
setenv(NLS_LANGAMERICAN_AMERICA.AL32UTF8)
userid ogg,password ogg
reportcount every 30 minutes,rate
reperror default,abend
--reperror default,DISCARD
numfiles 50000
checkpointsecs 40
assumetargetdefs
discardfile ./dirrpt/r_sc2.dsc,append,megabytes 1000
allownoopupdates
ddl
include mapped
exclude objname scott.*_audit
exclude optype create objtype table
exclude optype drop objtype table
exclude objtype index objname SCOTT.*_his
exclude instr constraint
---exclude instr null
exclude instr trigger
exclude instr rename to
exclude instr grant
exclude instr revoke
exclude instr analyze
DDLOPTIONS MAPSESSIONSCHEMA XUM target ogg
ddloptions report
allowduptargetmap
----------DEPT
getinserts
getupdates
getdeletes
noupdatedeletes
NOINSERTDELETES
map SCOTT.DEPT,target SCOTT.DEPT;
updatedeletes
--INSERTDELETES
map SCOTT.DEPT,target SCOTT.DEPT_HIS,keycols(DEPTNO);
getinserts
ignoreupdates
ignoredeletes
map SCOTT.DEPT,target SCOTT.DEPT_AUDIT,keycols(DEPTNO),colmap(
usedefaults,
csntoken(TKN-CSN),
optimetoken(TKN-COMMIT-TS),
optypetoken(TKN-OP-TYPE),
inserttimetoken(TKN-COMMIT-TS),
curdateDATENOW()
);
ignoreinserts
getupdates
getdeletes
map SCOTT.DEPT,target SCOTT.DEPT_AUDIT,keycols(DEPTNO),colmap(
usedefaults,
csntoken(TKN-CSN),
optimetoken(TKN-COMMIT-TS),
optypetoken(TKN-OP-TYPE),
curdateDATENOW()
),filter(strfind(token(TKN-OP-TYPE),PK UPDATE) 0);
ignoreinserts
getupdates
getdeletes
map SCOTT.DEPT,target SCOTT.DEPT_AUDIT,keycols(DEPTNO),colmap(
usedefaults,
csntoken(TKN-CSN),
optimetoken(TKN-COMMIT-TS),
optypecase(token(TKN-OP-TYPE),PK UPDATE,INSERT),
inserttimetoken(TKN-COMMIT-TS),
curdateDATENOW()
),filter(strfind(token(TKN-OP-TYPE),PK UPDATE) 0),insertallrecords;
ignoreinserts
getupdates
getdeletes
map SCOTT.DEPT,target SCOTT.DEPT_AUDIT,keycols(DEPTNO),colmap(
DEPTNObefore.DEPTNO,
csntoken(TKN-CSN),
optimetoken(TKN-COMMIT-TS),
optypecase(token(TKN-OP-TYPE),PK UPDATE,DELETE),
curdateDATENOW()
),filter(strfind(token(TKN-OP-TYPE),PK UPDATE) 0);
ignoreinserts
ignoreupdates
getdeletes
noupdatedeletes
INSERTDELETES
MAP SCOTT.DEPT, TARGET SCOTT.DEPT_DEL,keycols(DEPTNO),colmap(
usedefaults
BEFORE_AFTERgetenv(GGHEADER, BEFOREAFTERINDICATOR),
DEL_DATEgetenv (GGHEADER, COMMITTIMESTAMP),
OP_FLAGgetenv (GGHEADER, OPTYPE)
);
----------NOPK_TAB
getinserts
getupdates
getdeletes
noupdatedeletes
NOINSERTDELETES
map SCOTT.NOPK_TAB,target SCOTT.NOPK_TAB,keycols(EMPNO,ENAME);
updatedeletes
map SCOTT.NOPK_TAB,target SCOTT.NOPK_TAB_HIS,keycols(EMPNO,ENAME);
getinserts
getupdates
getdeletes
map SCOTT.NOPK_TAB,target SCOTT.NOPK_TAB_AUDIT,keycols(EMPNO,ENAME),colmap(
EMPNOif(strfind(token(tkn-op-type),PK UPDATE)0,before.EMPNO,EMPNO),
ENAMEif(strfind(token(tkn-op-type),PK UPDATE)0,before.ENAME,ENAME)
csntoken(tkn-csn),
optimetoken(tkn-commit-ts),
optypeif(strfind(token(tkn-op-type),PK UPDATE)0,DELETE,token(tkn-op-type)),
inserttimeeval(strfind(token(tkn-op-type),INSERT)0,token(tkn-commit-ts)),
curdateDATENOW()
);
ignoreinserts
getupdates
ignoredeletes
map SCOTT.NOPK_TAB,target SCOTT.NOPK_TAB_AUDIT,keycols(EMPNO,ENAME),colmap(
usedefaults,
csntoken(tkn-csn),
optimetoken(tkn-commit-ts),
optypecase(token(tkn-op-type),PK UPDATE,INSERT),
inserttimetoken(tkn-commit-ts),
curdateDATENOW()
),filter(strfind(token(tkn-op-type),PK UPDATE) 0),insertallrecords;
ignoreinserts
ignoreupdates
getdeletes
noupdatedeletes
INSERTDELETES
MAP SCOTT.NOPK_TAB, TARGET SCOTT.NOPK_TAB_DEL,keycols(EMPNO,ENAME),colmap(
usedefaults,
BEFORE_AFTERgetenv(GGHEADER, BEFOREAFTERINDICATOR),
DEL_DATEgetenv (GGHEADER, COMMITTIMESTAMP),
OP_FLAGgetenv (GGHEADER, OPTYPE)
);八、启动抽取进程和投递进程
start er *九、从源端倒数到目的端并启动应用进程
省略
十、验证是否能够只存被删除的数据完整记录
验证数据是否能够正常同步 源端分别进行插入更新删除操作
------------------------------------------------有物理主键表
SQL select * from dept;DEPTNO DNAME LOC
------ -------------- -------------1 a b2 a b3 a b4 a b5 a b6 a b7 a b8 a b9 a b10 a b10 rows selectedSQL insert into dept values(11,a,b);1 row insertedSQLdelete from dept where deptno10;1 row deletedSQL update dept set dnameaaawhere deptno9;1 row updatedSQL commit;Commit completeSQL alter system switch logfile;System alteredSQL /System alteredSQL select * from dept;DEPTNO DNAME LOC
------ -------------- -------------1 a b2 a b3 a b4 a b5 a b6 a b7 a b8 a b9 aaa b11 a b10 rows selected-----------------------------在目的端查看
SQL select * from dept;DEPTNO DNAME LOC
------ -------------- -------------1 A B2 A B3 A B4 A B5 A B6 A B7 A B8 A B9 aaa B11 a b10 rows selectedSQL select * from dept_audit;DEPTNO CSN OPTIME OPTYPE INSERTTIME CURDATE
------ ---------- -------------------------------------------------------------------------------- -------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------1 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 2 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 3 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 4 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 5 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 6 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 7 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 8 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 9 1603672 11-2月 -18 02.30.35.000000 下午 SQL COMPUPDATE 11-2月 -18 02.24.46.000000 下午 11-2月 -18 02.30.56.000000 下午10 1603672 11-2月 -18 02.30.35.000000 下午 DELETE 11-2月 -18 02.24.46.000000 下午 11-2月 -18 02.30.56.000000 下午11 1603672 11-2月 -18 02.30.35.000000 下午 INSERT 11-2月 -18 02.30.35.000000 下午 11-2月 -18 02.30.56.000000 下午11 rows selectedSQL select * from dept_his;DEPTNO DNAME LOC
------ -------------- -------------1 A B2 A B3 A B4 A B5 A B6 A B7 A B8 A B9 aaa B10 a b11 a b11 rows selectedSQL select * from dept_del;DEPTNO DNAME LOC BEFORE_AFTER DEL_DATE OP_FLAG
------ -------------- ------------- -------------------- -------------------------------------------------------------------------------- --------------------10 a b BEFORE 11-2月 -18 02.30.35.015854 下午 DELETE发现在源端执行的delete操作在目的端原来的一对三配置的表中表现正常在dept_del表中只保留了delete操作对应的那条记录的完整记录以及操作类型和时间。 无物理主键有逻辑主键表验证是否能够正常同步
------源端操作
SQL select * from nopk_tab;EMPNO ENAME SAL DEPTNO
------- -------------------- --------- ------1110 zhao 100.00 101110 yan 200.00 201111 yan 100.00 101111 dong 100.00 30SQL insert into nopk_tab values(1112,test,300,20);1 row insertedSQL delete from nopk_tab where empno1111 and enamedong;1 row deletedSQL update nopk_tab set sal400 where empno1110 and enamezhao;1 row updatedSQL commit;Commit completeSQL alter system switch logfile;System alteredSQL select * from nopk_tab;EMPNO ENAME SAL DEPTNO
------- -------------------- --------- ------1110 zhao 400.00 101110 yan 200.00 201111 yan 100.00 101112 test 300.00 20----------------------------------------------------在目标端查看SQL select * from nopk_tab;EMPNO ENAME SAL DEPTNO
------- -------------------- --------- ------1110 yan 200.00 201111 yan 100.00 101110 zhao 400.00 101112 test 300.00 20SQL select * from nopk_tab_audit;EMPNO ENAME CSN OPTIME OPTYPE INSERTTIME CURDATE
------- -------------------- ---------- -------------------------------------------------------------------------------- -------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------1110 yan 1521438 11-2月 -18 03.01.24.000000 下午 INITIAL 11-2月 -18 03.01.24.000000 下午 1111 yan 1521438 11-2月 -18 03.01.24.000000 下午 INITIAL 11-2月 -18 03.01.24.000000 下午 1111 dong 1606066 11-2月 -18 03.05.59.000000 下午 DELETE 11-2月 -18 03.01.24.000000 下午 11-2月 -18 03.06.11.000000 下午1110 zhao 1606066 11-2月 -18 03.05.59.000000 下午 SQL COMPUPDATE 11-2月 -18 03.01.24.000000 下午 11-2月 -18 03.06.11.000000 下午1112 test 1606066 11-2月 -18 03.05.59.000000 下午 INSERT 11-2月 -18 03.05.59.000000 下午 11-2月 -18 03.06.11.000000 下午SQL select * from nopk_tab_HIS;EMPNO ENAME SAL DEPTNO
------- -------------------- --------- ------1110 zhao 400.00 101110 yan 200.00 201111 yan 100.00 101111 dong 100.00 301112 test 300.00 20SQL select * from nopk_tab_del;EMPNO ENAME SAL DEPTNO BEFORE_AFTER DEL_DATE OP_FLAG
------- -------------------- --------- ------ -------------------- -------------------------------------------------------------------------------- --------------------1111 dong 100.00 30 BEFORE 11-2月 -18 03.05.59.015810 下午 DELETE
发生pkupdate操作时
-----------修改主键某个列有物理主键表
SQL select * from dept;DEPTNO DNAME LOC
------ -------------- -------------1 a b2 a b3 a b4 a b5 a b6 a b7 a b8 a b9 aaa b11 a b10 rows selectedSQL update dept set deptno22, dnameb where deptno2;1 row updatedSQL commit;Commit completeSQL alter system switch logfile;System alteredSQL alter system switch logfile;System altered
在目的端查看
SQL select * from dept;DEPTNO DNAME LOC
------ -------------- -------------1 A B22 b B3 A B4 A B5 A B6 A B7 A B8 A B9 aaa B11 a b10 rows selectedSQL select * from dept_audit;DEPTNO CSN OPTIME OPTYPE INSERTTIME CURDATE
------ ---------- -------------------------------------------------------------------------------- -------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------1 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 2 1606224 11-2月 -18 03.10.39.000000 下午 DELETE 11-2月 -18 02.24.46.000000 下午 11-2月 -18 03.11.09.000000 下午3 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 4 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 5 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 6 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 7 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 8 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 9 1603672 11-2月 -18 02.30.35.000000 下午 SQL COMPUPDATE 11-2月 -18 02.24.46.000000 下午 11-2月 -18 02.30.56.000000 下午10 1603672 11-2月 -18 02.30.35.000000 下午 DELETE 11-2月 -18 02.24.46.000000 下午 11-2月 -18 02.30.56.000000 下午11 1603672 11-2月 -18 02.30.35.000000 下午 INSERT 11-2月 -18 02.30.35.000000 下午 11-2月 -18 02.30.56.000000 下午22 1606224 11-2月 -18 03.10.39.000000 下午 INSERT 11-2月 -18 03.10.39.000000 下午 11-2月 -18 03.11.09.000000 下午12 rows selectedSQL select * from dept_his;DEPTNO DNAME LOC
------ -------------- -------------1 A B22 b B3 A B4 A B5 A B6 A B7 A B8 A B9 aaa B10 a b11 a b11 rows selectedSQL select * from dept_del;DEPTNO DNAME LOC BEFORE_AFTER DEL_DATE OP_FLAG
------ -------------- ------------- -------------------- -------------------------------------------------------------------------------- --------------------10 a b BEFORE 11-2月 -18 02.30.35.015854 下午 DELETE挖掘trail日志确认是pkupdate操作
Logdump 301 n
TokenID x47 G Record Header Info x01 Length 235
TokenID x48 H GHDR Info x00 Length 45
4504 0041 0027 73ff 02f2 8b1d ea2d c765 0000 0000 | E..A.s......-.e....
0000 9010 0000 0000 0352 0000 0001 5343 4f54 542e | .........R....SCOTT.
4445 5054 00 | DEPT.
TokenID x44 D Data Info x00 Length 39
TokenID x54 T GGS Tokens Info x00 Length 48
TokenID x55 U User Tokens Info x00 Length 79
TokenID x5a Z Record Trailer Info x01 Length 235
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 39 (x0027) IO Time : 2018/02/11 15:10:39.015.781
IOType : 115 (x73) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 0 AuditPos : 36880
Continued : N (x00) RecCount : 1 (x01) 2018/02/11 15:10:39.015.781 GGSPKUpdate Len 39 RBA 6284
Name: SCOTT.DEPT
After Image: Partition 4 GU s
000e 0000 000a 0000 0000 0000 0000 0002 0000 000a | ....................
0000 0000 0000 0000 0016 0001 0005 0000 0001 62 | ..................b
Before Image Len 16 (x00000010)
KeyLen 14 (x0000000e)
KeyCol 0 (x0000), Len 10 (x000a)
0000 0000 0000 0000 0002 | .......... After Image Len 23 (x00000017)
Column 0 (x0000), Len 10 (x000a)
0000 0000 0000 0000 0016 | ..........
Column 1 (x0001), Len 5 (x0005)
0000 0001 62 | ....b User tokens: 79 bytes
TKN-CSN : 1606224
TKN-COMMIT-TS : 2018-02-11 15:10:39.000000
TKN-OP-TYPE : PK UPDATE GGS tokens:
TokenID x52 R ORAROWID Info x00 Length 20
4141 4156 5243 4141 4541 4141 4143 4441 4142 0001 | AAAVRCAAEAAAACDAAB..
TokenID x4c L LOGCSN Info x00 Length 7
3136 3036 3232 34 | 1606224
TokenID x36 6 TRANID Info x00 Length 9
332e 3333 2e31 3335 35 | 3.33.1355 只修改主键列同上pkupdate操作结果一致。
-------------------无物理主键表修改逻辑主键
SQL select * from nopk_tab;EMPNO ENAME SAL DEPTNO
------- -------------------- --------- ------1110 zhao 400.00 101110 yan 200.00 201111 yan 100.00 101112 test 300.00 20SQL update nopk_tab set empno1222,enamezhaozhao where empno1110 and enamezhao;1 row updatedSQL commit;Commit completeSQL alter system switch logfile;System altered
SQL select * from nopk_tab;EMPNO ENAME SAL DEPTNO
------- -------------------- --------- ------1222 zhaozhao 400.00 101110 yan 200.00 201111 yan 100.00 101112 test 300.00 20-----------查看目的端
SQL select * from nopk_tab;EMPNO ENAME SAL DEPTNO
------- -------------------- --------- ------1110 yan 200.00 201111 yan 100.00 101222 zhaozhao 400.00 101112 test 300.00 20SQL select * from nopk_tab_audit;EMPNO ENAME CSN OPTIME OPTYPE INSERTTIME CURDATE
------- -------------------- ---------- -------------------------------------------------------------------------------- -------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------1110 yan 1521438 11-2月 -18 03.01.24.000000 下午 INITIAL 11-2月 -18 03.01.24.000000 下午 1111 yan 1521438 11-2月 -18 03.01.24.000000 下午 INITIAL 11-2月 -18 03.01.24.000000 下午 1111 dong 1606066 11-2月 -18 03.05.59.000000 下午 DELETE 11-2月 -18 03.01.24.000000 下午 11-2月 -18 03.06.11.000000 下午1110 zhao 1606635 11-2月 -18 03.20.04.000000 下午 DELETE 11-2月 -18 03.01.24.000000 下午 11-2月 -18 03.20.10.000000 下午1112 test 1606066 11-2月 -18 03.05.59.000000 下午 INSERT 11-2月 -18 03.05.59.000000 下午 11-2月 -18 03.06.11.000000 下午1222 zhaozhao 1606635 11-2月 -18 03.20.04.000000 下午 INSERT 11-2月 -18 03.20.04.000000 下午 11-2月 -18 03.20.10.000000 下午6 rows selectedSQL select * from nopk_tab_HIS;EMPNO ENAME SAL DEPTNO
------- -------------------- --------- ------1222 zhaozhao 400.00 101110 yan 200.00 201111 yan 100.00 101111 dong 100.00 301112 test 300.00 20SQL select * from nopk_tab_DEL;EMPNO ENAME SAL DEPTNO BEFORE_AFTER DEL_DATE OP_FLAG
------- -------------------- --------- ------ -------------------- -------------------------------------------------------------------------------- --------------------1111 dong 100.00 30 BEFORE 11-2月 -18 03.05.59.015810 下午 DELETE挖掘trail日志
Logdump 306 n
TokenID x47 G Record Header Info x01 Length 258
TokenID x48 H GHDR Info x00 Length 49
4504 0041 003a 73ff 02f2 8b1e 0bda fed5 0000 0000 | E..A.:s.............
0000 9410 0000 0000 0352 0000 0001 5343 4f54 542e | .........R....SCOTT.
4e4f 504b 5f54 4142 00 | NOPK_TAB.
TokenID x44 D Data Info x00 Length 58
TokenID x54 T GGS Tokens Info x00 Length 48
TokenID x55 U User Tokens Info x00 Length 79
TokenID x5a Z Record Trailer Info x01 Length 258
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 58 (x003a) IO Time : 2018/02/11 15:20:04.015.829
IOType : 115 (x73) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 0 AuditPos : 37904
Continued : N (x00) RecCount : 1 (x01) 2018/02/11 15:20:04.015.829 GGSPKUpdate Len 58 RBA 6758
Name: SCOTT.NOPK_TAB
After Image: Partition 4 GU s
001a 0000 000a 0000 0000 0000 0000 0456 0001 0008 | ...............V....
0000 0004 7a68 616f 0000 000a 0000 0000 0000 0000 | ....zhao............
04c6 0001 000c 0000 0008 7a68 616f 7a68 616f | ..........zhaozhao
Before Image Len 28 (x0000001c)
KeyLen 26 (x0000001a)
KeyCol 0 (x0000), Len 10 (x000a)
0000 0000 0000 0000 0456 | .........V
KeyCol 1 (x0001), Len 8 (x0008)
0000 0004 7a68 616f | ....zhao After Image Len 30 (x0000001e)
Column 0 (x0000), Len 10 (x000a)
0000 0000 0000 0000 04c6 | ..........
Column 1 (x0001), Len 12 (x000c)
0000 0008 7a68 616f 7a68 616f | ....zhaozhao User tokens: 79 bytes
TKN-CSN : 1606635
TKN-COMMIT-TS : 2018-02-11 15:20:04.000000
TKN-OP-TYPE : PK UPDATE GGS tokens:
TokenID x52 R ORAROWID Info x00 Length 20
4141 4156 7379 4141 4541 4141 4149 6c41 4141 0001 | AAAVsyAAEAAAAIlAAA..
TokenID x4c L LOGCSN Info x00 Length 7
3136 3036 3633 35 | 1606635
TokenID x36 6 TRANID Info x00 Length 9
322e 3230 2e31 3335 39 | 2.20.1359 -------------------无物理主键表修改逻辑主键非逻辑主键列SQL select * from nopk_tab;EMPNO ENAME SAL DEPTNO
------- -------------------- --------- ------1222 zhaozhao 400.00 101110 yan 200.00 201111 yan 100.00 101112 test 300.00 20SQL update nopk_tab set empno3333,enameyanyan,sal500 where empno1110 and enameyan;1 row updatedSQL commit;Commit completeSQL alter system switch logfile;System alteredSQL alter system switch logfile;System alteredSQL select * from nopk_tab;EMPNO ENAME SAL DEPTNO
------- -------------------- --------- ------1222 zhaozhao 400.00 103333 yanyan 500.00 201111 yan 100.00 101112 test 300.00 20
目的端查看SQL select * from nopk_tab;EMPNO ENAME SAL DEPTNO
------- -------------------- --------- ------3333 yanyan 500.00 201111 yan 100.00 101222 zhaozhao 400.00 101112 test 300.00 20SQL select * from nopk_tab_audit;EMPNO ENAME CSN OPTIME OPTYPE INSERTTIME CURDATE
------- -------------------- ---------- -------------------------------------------------------------------------------- -------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------1110 yan 1606931 11-2月 -18 03.24.32.000000 下午 DELETE 11-2月 -18 03.01.24.000000 下午 11-2月 -18 03.24.38.000000 下午1111 yan 1521438 11-2月 -18 03.01.24.000000 下午 INITIAL 11-2月 -18 03.01.24.000000 下午 1111 dong 1606066 11-2月 -18 03.05.59.000000 下午 DELETE 11-2月 -18 03.01.24.000000 下午 11-2月 -18 03.06.11.000000 下午1110 zhao 1606635 11-2月 -18 03.20.04.000000 下午 DELETE 11-2月 -18 03.01.24.000000 下午 11-2月 -18 03.20.10.000000 下午3333 yanyan 1606931 11-2月 -18 03.24.32.000000 下午 INSERT 11-2月 -18 03.24.32.000000 下午 11-2月 -18 03.24.38.000000 下午1112 test 1606066 11-2月 -18 03.05.59.000000 下午 INSERT 11-2月 -18 03.05.59.000000 下午 11-2月 -18 03.06.11.000000 下午1222 zhaozhao 1606635 11-2月 -18 03.20.04.000000 下午 INSERT 11-2月 -18 03.20.04.000000 下午 11-2月 -18 03.20.10.000000 下午7 rows selectedSQL select * from nopk_tab_HIS;EMPNO ENAME SAL DEPTNO
------- -------------------- --------- ------1222 zhaozhao 400.00 103333 yanyan 500.00 201111 yan 100.00 101111 dong 100.00 301112 test 300.00 20SQL select * from nopk_tab_DEL;EMPNO ENAME SAL DEPTNO BEFORE_AFTER DEL_DATE OP_FLAG
------- -------------------- --------- ------ -------------------- -------------------------------------------------------------------------------- --------------------1111 dong 100.00 30 BEFORE 11-2月 -18 03.05.59.015810 下午 DELETE
挖掘trail日志确实是pkupdate操作
Logdump 308 n
TokenID x47 G Record Header Info x01 Length 268
TokenID x48 H GHDR Info x00 Length 49
4504 0041 0045 73ff 02f2 8b1e 1bd4 59ab 0000 0000 | E..A.Es.......Y.....
0003 c610 0000 0000 0352 0000 0001 5343 4f54 542e | .........R....SCOTT.
4e4f 504b 5f54 4142 00 | NOPK_TAB.
TokenID x44 D Data Info x00 Length 69
TokenID x54 T GGS Tokens Info x00 Length 47
TokenID x55 U User Tokens Info x00 Length 79
TokenID x5a Z Record Trailer Info x01 Length 268
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 69 (x0045) IO Time : 2018/02/11 15:24:32.015.787
IOType : 115 (x73) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 0 AuditPos : 247312
Continued : N (x00) RecCount : 1 (x01) 2018/02/11 15:24:32.015.787 GGSPKUpdate Len 69 RBA 7016
Name: SCOTT.NOPK_TAB
After Image: Partition 4 GU s
0019 0000 000a 0000 0000 0000 0000 0456 0001 0007 | ...............V....
0000 0003 7961 6e00 0000 0a00 0000 0000 0000 000d | ....yan.............
0500 0100 0a00 0000 0679 616e 7961 6e00 0200 0a00 | .........yanyan.....
0000 0000 0000 00c3 50 | ........P
Before Image Len 27 (x0000001b)
KeyLen 25 (x00000019)
KeyCol 0 (x0000), Len 10 (x000a)
0000 0000 0000 0000 0456 | .........V
KeyCol 1 (x0001), Len 7 (x0007)
0000 0003 7961 6e | ....yan After Image Len 42 (x0000002a)
Column 0 (x0000), Len 10 (x000a)
0000 0000 0000 0000 0d05 | ..........
Column 1 (x0001), Len 10 (x000a)
0000 0006 7961 6e79 616e | ....yanyan
Column 2 (x0002), Len 10 (x000a)
0000 0000 0000 0000 c350 | .........P User tokens: 79 bytes
TKN-CSN : 1606931
TKN-COMMIT-TS : 2018-02-11 15:24:32.000000
TKN-OP-TYPE : PK UPDATE GGS tokens:
TokenID x52 R ORAROWID Info x00 Length 20
4141 4156 7379 4141 4541 4141 4149 6c41 4142 0001 | AAAVsyAAEAAAAIlAAB..
TokenID x4c L LOGCSN Info x00 Length 7
3136 3036 3933 31 | 1606931
TokenID x36 6 TRANID Info x00 Length 8
322e 362e 3133 3630 | 2.6.1360
上面操作证明对DEL表的配置确实可以捕获到源端delelte操作的记录对应的完整记录并插入表中接下来测试当同一个主键号多次插入删除DEL表会不会累积历史操作
-----------------------------------------------------测试同一条记录连续插入删除多次在DEL表中是否会存在多条相同记录
----------------在有物理主键表测试
SQL insert into dept values(33,a,a);1 row insertedSQL select * from dept;DEPTNO DNAME LOC
------ -------------- -------------1 a b23 bbbbb b3 a b4 a b5 a b6 a b7 a b8 a b9 aaa b11 a b33 a a11 rows selectedSQL commit;Commit completeSQL alter system switch logfile;System altered
目的端查看
SQL select * from dept;DEPTNO DNAME LOC
------ -------------- -------------1 A B23 bbbbb B3 A B4 A B5 A B6 A B7 A B8 A B9 aaa B11 a b33 a a11 rows selected
同步过来了源端再执行删除SQL delete from dept where deptno33;1 row deletedSQL commit;Commit completeSQL alter system switch logfile;System altered
再来目的端查看
SQL select * from dept;DEPTNO DNAME LOC
------ -------------- -------------1 A B23 bbbbb B3 A B4 A B5 A B6 A B7 A B8 A B9 aaa B11 a b10 rows selectedSQL select * from dept_audit;DEPTNO CSN OPTIME OPTYPE INSERTTIME CURDATE
------ ---------- -------------------------------------------------------------------------------- -------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------1 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 2 1606224 11-2月 -18 03.10.39.000000 下午 DELETE 11-2月 -18 02.24.46.000000 下午 11-2月 -18 03.11.09.000000 下午3 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 4 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 5 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 6 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 7 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 8 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 9 1603672 11-2月 -18 02.30.35.000000 下午 SQL COMPUPDATE 11-2月 -18 02.24.46.000000 下午 11-2月 -18 02.30.56.000000 下午10 1603672 11-2月 -18 02.30.35.000000 下午 DELETE 11-2月 -18 02.24.46.000000 下午 11-2月 -18 02.30.56.000000 下午11 1603672 11-2月 -18 02.30.35.000000 下午 INSERT 11-2月 -18 02.30.35.000000 下午 11-2月 -18 02.30.56.000000 下午22 1606528 11-2月 -18 03.17.25.000000 下午 DELETE 11-2月 -18 03.10.39.000000 下午 11-2月 -18 03.17.31.000000 下午23 1606528 11-2月 -18 03.17.25.000000 下午 INSERT 11-2月 -18 03.17.25.000000 下午 11-2月 -18 03.17.31.000000 下午33 1607183 11-2月 -18 03.29.38.000000 下午 DELETE 11-2月 -18 03.28.15.000000 下午 11-2月 -18 03.29.45.000000 下午14 rows selectedSQL select * from dept_his;DEPTNO DNAME LOC
------ -------------- -------------1 A B23 bbbbb B3 A B4 A B5 A B6 A B7 A B8 A B9 aaa B10 a b11 a b33 a a12 rows selectedSQL select * from dept_del;DEPTNO DNAME LOC BEFORE_AFTER DEL_DATE OP_FLAG
------ -------------- ------------- -------------------- -------------------------------------------------------------------------------- --------------------10 a b BEFORE 11-2月 -18 02.30.35.015854 下午 DELETE33 a a BEFORE 11-2月 -18 03.29.38.015754 下午 DELETE
再在源端进行插入同一记录在删除
SQL insert into dept values(33,a,a);1 row insertedSQL commit;Commit completeSQL delete from dept where deptno33;1 row deletedSQL commit;Commit completeSQL alter system switch logfile;System altered在目睹端查看
SQL select * from dept;DEPTNO DNAME LOC
------ -------------- -------------1 A B23 bbbbb B3 A B4 A B5 A B6 A B7 A B8 A B9 aaa B11 a b10 rows selectedSQL select * from dept_audit;DEPTNO CSN OPTIME OPTYPE INSERTTIME CURDATE
------ ---------- -------------------------------------------------------------------------------- -------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------1 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 2 1606224 11-2月 -18 03.10.39.000000 下午 DELETE 11-2月 -18 02.24.46.000000 下午 11-2月 -18 03.11.09.000000 下午3 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 4 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 5 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 6 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 7 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 8 1521438 11-2月 -18 02.24.46.000000 下午 INITIAL 11-2月 -18 02.24.46.000000 下午 9 1603672 11-2月 -18 02.30.35.000000 下午 SQL COMPUPDATE 11-2月 -18 02.24.46.000000 下午 11-2月 -18 02.30.56.000000 下午10 1603672 11-2月 -18 02.30.35.000000 下午 DELETE 11-2月 -18 02.24.46.000000 下午 11-2月 -18 02.30.56.000000 下午11 1603672 11-2月 -18 02.30.35.000000 下午 INSERT 11-2月 -18 02.30.35.000000 下午 11-2月 -18 02.30.56.000000 下午22 1606528 11-2月 -18 03.17.25.000000 下午 DELETE 11-2月 -18 03.10.39.000000 下午 11-2月 -18 03.17.31.000000 下午23 1606528 11-2月 -18 03.17.25.000000 下午 INSERT 11-2月 -18 03.17.25.000000 下午 11-2月 -18 03.17.31.000000 下午33 1607249 11-2月 -18 03.31.06.000000 下午 DELETE 11-2月 -18 03.28.15.000000 下午 11-2月 -18 03.31.14.000000 下午33 1607246 11-2月 -18 03.31.01.000000 下午 INSERT 11-2月 -18 03.31.01.000000 下午 11-2月 -18 03.31.14.000000 下午15 rows selectedSQL select * from dept_his;DEPTNO DNAME LOC
------ -------------- -------------1 A B23 bbbbb B3 A B4 A B5 A B6 A B7 A B8 A B9 aaa B10 a b11 a b33 a a33 a a13 rows selectedSQL select * from dept_del;DEPTNO DNAME LOC BEFORE_AFTER DEL_DATE OP_FLAG
------ -------------- ------------- -------------------- -------------------------------------------------------------------------------- --------------------10 a b BEFORE 11-2月 -18 02.30.35.015854 下午 DELETE33 a a BEFORE 11-2月 -18 03.29.38.015754 下午 DELETE33 a a BEFORE 11-2月 -18 03.31.06.015751 下午 DELETE发现是会正常保存的。接下来测试无物理主键的
-----源端进行如下操作
SQL select * from nopk_tab;EMPNO ENAME SAL DEPTNO
------- -------------------- --------- ------1222 zhaozhao 400.00 103333 yanyan 500.00 201111 yan 100.00 101112 test 300.00 20SQL insert into nopk_tab values(4444,a,300,10);1 row insertedSQL commit;Commit completeSQL delete from nopk_tab where empno4444;1 row deletedSQL commit;Commit completeSQL insert into nopk_tab values(4444,a,300,10);1 row insertedSQL commit;Commit completeSQL delete from nopk_tab where empno4444;1 row deletedSQL commit;Commit completeSQL alter system switch logfile;System altered----------目的端查看
SQL select * from nopk_tab;EMPNO ENAME SAL DEPTNO
------- -------------------- --------- ------3333 yanyan 500.00 201111 yan 100.00 101222 zhaozhao 400.00 101112 test 300.00 20SQL select * from nopk_tab_audit;EMPNO ENAME CSN OPTIME OPTYPE INSERTTIME CURDATE
------- -------------------- ---------- -------------------------------------------------------------------------------- -------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------1110 yan 1606931 11-2月 -18 03.24.32.000000 下午 DELETE 11-2月 -18 03.01.24.000000 下午 11-2月 -18 03.24.38.000000 下午1111 yan 1521438 11-2月 -18 03.01.24.000000 下午 INITIAL 11-2月 -18 03.01.24.000000 下午 1111 dong 1606066 11-2月 -18 03.05.59.000000 下午 DELETE 11-2月 -18 03.01.24.000000 下午 11-2月 -18 03.06.11.000000 下午1110 zhao 1606635 11-2月 -18 03.20.04.000000 下午 DELETE 11-2月 -18 03.01.24.000000 下午 11-2月 -18 03.20.10.000000 下午3333 yanyan 1606931 11-2月 -18 03.24.32.000000 下午 INSERT 11-2月 -18 03.24.32.000000 下午 11-2月 -18 03.24.38.000000 下午4444 a 1607467 11-2月 -18 03.35.50.000000 下午 DELETE 11-2月 -18 03.35.13.000000 下午 11-2月 -18 03.36.02.000000 下午1112 test 1606066 11-2月 -18 03.05.59.000000 下午 INSERT 11-2月 -18 03.05.59.000000 下午 11-2月 -18 03.06.11.000000 下午1222 zhaozhao 1606635 11-2月 -18 03.20.04.000000 下午 INSERT 11-2月 -18 03.20.04.000000 下午 11-2月 -18 03.20.10.000000 下午4444 a 1607464 11-2月 -18 03.35.46.000000 下午 INSERT 11-2月 -18 03.35.46.000000 下午 11-2月 -18 03.36.02.000000 下午9 rows selectedSQL select * from nopk_tab_HIS;EMPNO ENAME SAL DEPTNO
------- -------------------- --------- ------1222 zhaozhao 400.00 103333 yanyan 500.00 201111 yan 100.00 101111 dong 100.00 304444 a 300.00 101112 test 300.00 204444 a 300.00 107 rows selectedSQL select * from nopk_tab_DEL;EMPNO ENAME SAL DEPTNO BEFORE_AFTER DEL_DATE OP_FLAG
------- -------------------- --------- ------ -------------------- -------------------------------------------------------------------------------- --------------------4444 a 300.00 10 BEFORE 11-2月 -18 03.35.50.016297 下午 DELETE1111 dong 100.00 30 BEFORE 11-2月 -18 03.05.59.015810 下午 DELETE4444 a 300.00 10 BEFORE 11-2月 -18 03.35.43.016297 下午 DELETE也是正常的通过上面验证证明DEL可以累积同一主键号的删除历史纪录证明参数NOCOMPRESSDELETES 的确如文档所说能够完整的捕获到被删除记录的完整记录并写到trail文件中| 和参数NSERTDELETES一起使用的确可以实现历史表中只存放被删除的记录。
接下来再测试一下修改表结构在进行增删改del表能否正常捕获: 1.实验开始前的表结构以及表信息还是跟着上面过程来的期间自己又做过别的测试数据与上面上次做完的实验会有所不同但是表结构还是已经改为一致了
-----------源端表结构以及数据
SQL desc dept;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14) Y
LOC VARCHAR2(13) Y
SQL select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------1 a b23 bbbbb b3 cccc b4 a b5 a b6 a b12 ada b13 a b15 a b16 a b
10 rows selected
SQL desc nopk_tab;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
EMPNO NUMBER(6) Y
ENAME VARCHAR2(20) Y
SAL NUMBER(7,2) Y
DEPTNO NUMBER(4) Y
SQL select * from nopk_tab;EMPNO ENAME SAL DEPTNO
------- -------------------- --------- ------1222 zhaozhao 1000.00 103333 yanyan 500.00 201111 yan 100.00 10-------------目的端表结构和表数据
SQL desc dept;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14) Y
LOC VARCHAR2(13) Y
SQL desc dept_his;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(2) Y
DNAME VARCHAR2(14) Y
LOC VARCHAR2(13) Y
SQL desc dept_audit;
Name Type Nullable Default Comments
---------- ------------ -------- ------- --------
DEPTNO NUMBER(2) Y
CSN NUMBER Y
OPTIME TIMESTAMP(6) Y
OPTYPE VARCHAR2(20) Y
INSERTTIME TIMESTAMP(6) Y
CURDATE TIMESTAMP(6) Y
SQL desc dept_del;
Name Type Nullable Default Comments
------------ ------------ -------- ------- --------
DEPTNO NUMBER(2) Y
DNAME VARCHAR2(14) Y
LOC VARCHAR2(13) Y
BEFORE_AFTER VARCHAR2(20) Y
DEL_DATE TIMESTAMP(6) Y
OP_FLAG VARCHAR2(20) Y SQL select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------1 A B23 bbbbb B3 cccc B4 A B5 A B6 A B12 ada b13 a b15 a b16 a b
10 rows selected
SQL select * from dept_HIS;
DEPTNO DNAME LOC
------ -------------- -------------1 A B23 bbbbb B3 cccc B4 A B5 A B6 A B7 A B8 a b9 aaa b10 a b12 ada b13 a b14 a b15 a b16 a b14 a b14 a b14 a b14 a b41 a a
DEPTNO DNAME LOC
------ -------------- -------------11 a b33 a a33 a a
23 rows selectedSQL desc nopk_tab;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
EMPNO NUMBER(6) Y
ENAME VARCHAR2(20) Y
SAL NUMBER(7,2) Y
DEPTNO NUMBER(4) Y
SQL desc nopk_tab_audit;
Name Type Nullable Default Comments
---------- ------------ -------- ------- --------
EMPNO NUMBER(6) Y
ENAME VARCHAR2(20) Y
CSN NUMBER Y
OPTIME TIMESTAMP(6) Y
OPTYPE VARCHAR2(20) Y
INSERTTIME TIMESTAMP(6) Y
CURDATE TIMESTAMP(6) Y
SQL desc nopk_tab_del;
Name Type Nullable Default Comments
------------ ------------ -------- ------- --------
EMPNO NUMBER(6) Y
ENAME VARCHAR2(20) Y
SAL NUMBER(7,2) Y
DEPTNO NUMBER(4) Y
BEFORE_AFTER VARCHAR2(20) Y
DEL_DATE TIMESTAMP(6) Y
OP_FLAG VARCHAR2(20) Y
SQL desc nopk_tab_his;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
EMPNO NUMBER(6) Y
ENAME VARCHAR2(20) Y
SAL NUMBER(7,2) Y
DEPTNO NUMBER(4) Y SQL select * from nopk_tab;EMPNO ENAME SAL DEPTNO
------- -------------------- --------- ------3333 yanyan 500.00 201111 yan 100.00 101222 zhaozhao 1000.00 10
SQL select * from nopk_tab_HIS;EMPNO ENAME SAL DEPTNO
------- -------------------- --------- ------1222 zhaozhao 1000.00 103333 yanyan 500.00 201111 yan 100.00 101111 dong 100.00 301001 aa 22.00 14444 a 300.00 101112 test 300.00 204444 a 300.00 10
8 rows selected源端和目的端表结构和数据均已经知道。现在在源端分别给dept表和nopk_tab 表增加一列 test number;
alter table dept add test number;
alter table nopk_tab add test number;然后切换一下归档日志
alter system switch logfile;
alter system switch logfile;然后去目的端分别查看这两张表的源表和其他映射表的表结构
SQL desc dept;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14) Y
LOC VARCHAR2(13) Y
TEST NUMBER Y
SQL desc dept_his;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(2) Y
DNAME VARCHAR2(14) Y
LOC VARCHAR2(13) Y
TEST NUMBER Y
SQL desc dept_audit;
Name Type Nullable Default Comments
---------- ------------ -------- ------- --------
DEPTNO NUMBER(2) Y
CSN NUMBER Y
OPTIME TIMESTAMP(6) Y
OPTYPE VARCHAR2(20) Y
INSERTTIME TIMESTAMP(6) Y
CURDATE TIMESTAMP(6) Y
SQL desc dept_del;
Name Type Nullable Default Comments
------------ ------------ -------- ------- --------
DEPTNO NUMBER(2) Y
DNAME VARCHAR2(14) Y
LOC VARCHAR2(13) Y
BEFORE_AFTER VARCHAR2(20) Y
DEL_DATE TIMESTAMP(6) Y
OP_FLAG VARCHAR2(20) Y
TEST NUMBER Y SQL desc nopk_tab;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
EMPNO NUMBER(6) Y
ENAME VARCHAR2(20) Y
SAL NUMBER(7,2) Y
DEPTNO NUMBER(4) Y
TEST NUMBER Y
SQL desc nopk_tab_audit;
Name Type Nullable Default Comments
---------- ------------ -------- ------- --------
EMPNO NUMBER(6) Y
ENAME VARCHAR2(20) Y
CSN NUMBER Y
OPTIME TIMESTAMP(6) Y
OPTYPE VARCHAR2(20) Y
INSERTTIME TIMESTAMP(6) Y
CURDATE TIMESTAMP(6) Y
SQL desc nopk_tab_del;
Name Type Nullable Default Comments
------------ ------------ -------- ------- --------
EMPNO NUMBER(6) Y
ENAME VARCHAR2(20) Y
SAL NUMBER(7,2) Y
DEPTNO NUMBER(4) Y
BEFORE_AFTER VARCHAR2(20) Y
DEL_DATE TIMESTAMP(6) Y
OP_FLAG VARCHAR2(20) Y
TEST NUMBER Y
SQL desc nopk_tab_his;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
EMPNO NUMBER(6) Y
ENAME VARCHAR2(20) Y
SAL NUMBER(7,2) Y
DEPTNO NUMBER(4) Y
TEST NUMBER Y 从上面ddl操作结果来看源端的ddl操作的确应用过来了。而且在report中也有记录
GGSCI (OGG) 4 view report r_sc2
最后面内容如下
2018-02-24 17:21:22 INFO OGG-00482 DDL found, operation [alter table dept add test number(size 34)].2018-02-24 17:21:22 INFO OGG-00489 DDL is of mapped scope, after mapping new operation [alter table SCOTT.DEPT add test number(size 40)].2018-02-24 17:21:22 INFO OGG-00487 DDL operation included [include mapped], optype [ALTER], objtype [TABLE], objowner [SCOTT], objname [DEPT].2018-02-24 17:21:22 INFO OGG-01407 Setting current schema for DDL operation to [SCOTT].2018-02-24 17:21:22 INFO OGG-00484 Executing DDL operation.2018-02-24 17:21:22 INFO OGG-00483 DDL operation successful.2018-02-24 17:21:22 INFO OGG-01408 Restoring current schema for DDL operation to [ogg].2018-02-24 17:21:22 INFO OGG-00489 DDL is of mapped scope, after mapping new operation [alter table SCOTT.DEPT_HIS add test number(size 44)].2018-02-24 17:21:22 INFO OGG-00487 DDL operation included [include mapped], optype [ALTER], objtype [TABLE], objowner [SCOTT], objname [DEPT_HIS].2018-02-24 17:21:22 INFO OGG-01407 Setting current schema for DDL operation to [SCOTT].2018-02-24 17:21:22 INFO OGG-00484 Executing DDL operation.2018-02-24 17:21:22 INFO OGG-00483 DDL operation successful.2018-02-24 17:21:22 INFO OGG-01408 Restoring current schema for DDL operation to [ogg].2018-02-24 17:21:22 INFO OGG-00489 DDL is of mapped scope, after mapping new operation [alter table SCOTT.DEPT_AUDIT add test number(size 46)].2018-02-24 17:21:22 INFO OGG-00488 DDL operation excluded [exclude objname scott.*_audit], optype [ALTER], objtype [TABLE], objowner [SCOTT], objname [DEPT_AUDIT].2018-02-24 17:21:22 INFO OGG-00489 DDL is of mapped scope, after mapping new operation [alter table SCOTT.DEPT_DEL add test number(size 44)].2018-02-24 17:21:22 INFO OGG-00487 DDL operation included [include mapped], optype [ALTER], objtype [TABLE], objowner [SCOTT], objname [DEPT_DEL].2018-02-24 17:21:22 INFO OGG-01407 Setting current schema for DDL operation to [SCOTT].2018-02-24 17:21:22 INFO OGG-00484 Executing DDL operation.2018-02-24 17:21:22 INFO OGG-00483 DDL operation successful.2018-02-24 17:21:22 INFO OGG-01408 Restoring current schema for DDL operation to [ogg].2018-02-24 17:21:22 INFO OGG-00482 DDL found, operation [alter table nopk_tab add test number(size 38)].2018-02-24 17:21:22 INFO OGG-00489 DDL is of mapped scope, after mapping new operation [alter table SCOTT.NOPK_TAB add test number(size 44)].2018-02-24 17:21:22 INFO OGG-00487 DDL operation included [include mapped], optype [ALTER], objtype [TABLE], objowner [SCOTT], objname [NOPK_TAB].2018-02-24 17:21:22 INFO OGG-01407 Setting current schema for DDL operation to [SCOTT].2018-02-24 17:21:22 INFO OGG-00484 Executing DDL operation.2018-02-24 17:21:22 INFO OGG-00483 DDL operation successful.2018-02-24 17:21:22 INFO OGG-01408 Restoring current schema for DDL operation to [ogg].2018-02-24 17:21:22 INFO OGG-00489 DDL is of mapped scope, after mapping new operation [alter table SCOTT.NOPK_TAB_HIS add test number(size 48)].2018-02-24 17:21:22 INFO OGG-00487 DDL operation included [include mapped], optype [ALTER], objtype [TABLE], objowner [SCOTT], objname [NOPK_TAB_HIS].2018-02-24 17:21:22 INFO OGG-01407 Setting current schema for DDL operation to [SCOTT].2018-02-24 17:21:22 INFO OGG-00484 Executing DDL operation.2018-02-24 17:21:22 INFO OGG-00483 DDL operation successful.2018-02-24 17:21:22 INFO OGG-01408 Restoring current schema for DDL operation to [ogg].2018-02-24 17:21:22 INFO OGG-00489 DDL is of mapped scope, after mapping new operation [alter table SCOTT.NOPK_TAB_AUDIT add test number(size 50)].2018-02-24 17:21:22 INFO OGG-00488 DDL operation excluded [exclude objname scott.*_audit], optype [ALTER], objtype [TABLE], objowner [SCOTT], objname [NOPK_TAB_AUDIT].2018-02-24 17:21:22 INFO OGG-00489 DDL is of mapped scope, after mapping new operation [alter table SCOTT.NOPK_TAB_DEL add test number(size 48)].2018-02-24 17:21:22 INFO OGG-00487 DDL operation included [include mapped], optype [ALTER], objtype [TABLE], objowner [SCOTT], objname [NOPK_TAB_DEL].2018-02-24 17:21:22 INFO OGG-01407 Setting current schema for DDL operation to [SCOTT].2018-02-24 17:21:22 INFO OGG-00484 Executing DDL operation.2018-02-24 17:21:22 INFO OGG-00483 DDL operation successful.2018-02-24 17:21:22 INFO OGG-01408 Restoring current schema for DDL operation to [ogg].
但是我们也能发现新增加的一列会放到表列的最后面在XXXXDEL表中显示的不友好笔者这边需求是这个DEL表的表结构要与源表一致本人DEL表多出来的那几列是 本人为了测试方便以及为后面update和PKUPDATE 前映像测试显示方便而加的所以并不影响原来需求。 现在分别在源端对这两个表进行增删改操作
insert into dept values(43,a,a,1);
insert into nopk_tab values(1003,aa,22,1,12);
SQL select * from dept;
DEPTNO DNAME LOC TEST
------ -------------- ------------- ----------1 a b 23 bbbbb b 3 cccc b 4 a b 5 a b 6 a b 43 a a 112 ada b 13 a b 15 a b 16 a b
11 rows selectedSQL select * from nopk_tab;EMPNO ENAME SAL DEPTNO TEST
------- -------------------- --------- ------ ----------1003 aa 22.00 1 121222 zhaozhao 1000.00 10 3333 yanyan 500.00 20 1111 yan 100.00 10 update dept set test111 where deptno1;
update nopk_tab set test122 where empno1222;EMPNO ENAME SAL DEPTNO TEST
------- -------------------- --------- ------ ----------1003 aa 22.00 1 121222 zhaozhao 1000.00 10 1223333 yanyan 500.00 20 1111 yan 100.00 10
SQL select * from dept;
DEPTNO DNAME LOC TEST
------ -------------- ------------- ----------1 a b 11123 bbbbb b 3 cccc b 4 a b 5 a b 6 a b 43 a a 112 ada b 13 a b 15 a b 16 a b
11 rows selecteddelete from dept where deptno43;
delete from nopk_tab where empno1003;SQL select * from dept;
DEPTNO DNAME LOC TEST
------ -------------- ------------- ----------1 a b 11123 bbbbb b 3 cccc b 4 a b 5 a b 6 a b 12 ada b 13 a b 15 a b 16 a b
10 rows selected
SQL select * from nopk_tab;EMPNO ENAME SAL DEPTNO TEST
------- -------------------- --------- ------ ----------1222 zhaozhao 1000.00 10 1223333 yanyan 500.00 20 1111 yan 100.00 10 好了现在去目的端查看表内容
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;SQL select * from dept;
DEPTNO DNAME LOC TEST
------ -------------- ------------- ----------1 A B 11123 bbbbb B 3 cccc B 4 A B 5 A B 6 A B 12 ada b 13 a b 15 a b 16 a b
10 rows selected
SQL select * from dept_his;
DEPTNO DNAME LOC TEST
------ -------------- ------------- ----------1 A B 11123 bbbbb B 3 cccc B 4 A B 5 A B 6 A B 7 A B 8 a b 9 aaa b 10 a b 12 ada b 13 a b 14 a b 15 a b 16 a b 14 a b 14 a b 14 a b 14 a b 41 a a
DEPTNO DNAME LOC TEST
------ -------------- ------------- ----------43 a a 111 a b 33 a a 33 a a
24 rows selectedSQL select * from nopk_tab;EMPNO ENAME SAL DEPTNO TEST
------- -------------------- --------- ------ ----------3333 yanyan 500.00 20 1111 yan 100.00 10 1222 zhaozhao 1000.00 10 122
SQL select * from nopk_tab_his;EMPNO ENAME SAL DEPTNO TEST
------- -------------------- --------- ------ ----------1222 zhaozhao 1000.00 10 1223333 yanyan 500.00 20 1111 yan 100.00 10 1111 dong 100.00 30 1001 aa 22.00 1 1003 aa 22.00 1 124444 a 300.00 10 1112 test 300.00 20 4444 a 300.00 10 发现仍然可以成功捕获这是应当的。在测试一下多次插入删除同一条sql -----源端操作
insert into dept values(43,a,a,1);
insert into nopk_tab values(1003,aa,22,1,12);
delete from dept where deptno41;
delete from nopk_tab where empno1001;
alter system switch logfile;—目的端查看
SQL select * from dept_his;
DEPTNO DNAME LOC TEST
------ -------------- ------------- ----------1 A B 11123 bbbbb B 3 cccc B 4 A B 5 A B 6 A B 7 A B 8 a b 9 aaa b 10 a b 12 ada b 13 a b 14 a b 15 a b 16 a b 14 a b 14 a b 14 a b 14 a b 41 a a
DEPTNO DNAME LOC TEST
------ -------------- ------------- ----------43 a a 143 a a 111 a b 33 a a 33 a a
25 rows selected
SQL select * from nopk_tab_his;EMPNO ENAME SAL DEPTNO TEST
------- -------------------- --------- ------ ----------1222 zhaozhao 1000.00 10 1223333 yanyan 500.00 20 1111 yan 100.00 10 1111 dong 100.00 30 1001 aa 22.00 1 1003 aa 22.00 1 121003 aa 22.00 1 124444 a 300.00 10 1112 test 300.00 20 4444 a 300.00 10
10 rows selectedSQL select * from dept;
DEPTNO DNAME LOC TEST
------ -------------- ------------- ----------1 A B 11123 bbbbb B 3 cccc B 4 A B 5 A B 6 A B 12 ada b 13 a b 15 a b 16 a b
10 rows selected
SQL select * from nopk_tab;EMPNO ENAME SAL DEPTNO TEST
------- -------------------- --------- ------ ----------3333 yanyan 500.00 20 1111 yan 100.00 10 1222 zhaozhao 1000.00 10 122发现结果仍然是正常的。本来就应该是正常的但是为了保险起见才做了一下多余的测试毕竟应用到生产全公司的ODS平台都应用此配置好了现在再再源端那两张表中 删除新加的test列
alter table dept drop column test;
alter table nopk_tab drop column test;
alter system switch logfile;接下来在目的端查看 先看report文件
view report r_sc2
2018-02-26 09:19:29 INFO OGG-00482 DDL found, operation [alter table dept drop column test (size 34)].2018-02-26 09:19:29 INFO OGG-00489 DDL is of mapped scope, after mapping new operation [alter table SCOTT.DEPT drop column test (size 40)].2018-02-26 09:19:29 INFO OGG-00487 DDL operation included [include mapped], optype [ALTER], objtype [TABLE], objowner [SCOTT], objname [DEPT].2018-02-26 09:19:29 INFO OGG-01407 Setting current schema for DDL operation to [SCOTT].2018-02-26 09:19:29 INFO OGG-00484 Executing DDL operation.2018-02-26 09:19:30 INFO OGG-00483 DDL operation successful.2018-02-26 09:19:30 INFO OGG-01408 Restoring current schema for DDL operation to [ogg].2018-02-26 09:19:30 INFO OGG-00489 DDL is of mapped scope, after mapping new operation [alter table SCOTT.DEPT_HIS drop column test (size 44)].2018-02-26 09:19:30 INFO OGG-00487 DDL operation included [include mapped], optype [ALTER], objtype [TABLE], objowner [SCOTT], objname [DEPT_HIS].2018-02-26 09:19:30 INFO OGG-01407 Setting current schema for DDL operation to [SCOTT].2018-02-26 09:19:30 INFO OGG-00484 Executing DDL operation.2018-02-26 09:19:30 INFO OGG-00483 DDL operation successful.2018-02-26 09:19:30 INFO OGG-01408 Restoring current schema for DDL operation to [ogg].2018-02-26 09:19:30 INFO OGG-00489 DDL is of mapped scope, after mapping new operation [alter table SCOTT.DEPT_AUDIT drop column test (size 46)].2018-02-26 09:19:30 INFO OGG-00488 DDL operation excluded [exclude objname scott.*_audit], optype [ALTER], objtype [TABLE], objowner [SCOTT], objname [DEPT_AUDIT].2018-02-26 09:19:30 INFO OGG-00489 DDL is of mapped scope, after mapping new operation [alter table SCOTT.DEPT_DEL drop column test (size 44)].2018-02-26 09:19:30 INFO OGG-00487 DDL operation included [include mapped], optype [ALTER], objtype [TABLE], objowner [SCOTT], objname [DEPT_DEL].2018-02-26 09:19:30 INFO OGG-01407 Setting current schema for DDL operation to [SCOTT].2018-02-26 09:19:30 INFO OGG-00484 Executing DDL operation.2018-02-26 09:19:30 INFO OGG-00483 DDL operation successful.2018-02-26 09:19:30 INFO OGG-01408 Restoring current schema for DDL operation to [ogg].2018-02-26 09:19:30 INFO OGG-00482 DDL found, operation [alter table nopk_tab drop column test (size 38)].2018-02-26 09:19:30 INFO OGG-00489 DDL is of mapped scope, after mapping new operation [alter table SCOTT.NOPK_TAB drop column test (size 44)].2018-02-26 09:19:30 INFO OGG-00487 DDL operation included [include mapped], optype [ALTER], objtype [TABLE], objowner [SCOTT], objname [NOPK_TAB].2018-02-26 09:19:30 INFO OGG-01407 Setting current schema for DDL operation to [SCOTT].2018-02-26 09:19:30 INFO OGG-00484 Executing DDL operation.2018-02-26 09:19:30 INFO OGG-00483 DDL operation successful.2018-02-26 09:19:30 INFO OGG-01408 Restoring current schema for DDL operation to [ogg].2018-02-26 09:19:30 INFO OGG-00489 DDL is of mapped scope, after mapping new operation [alter table SCOTT.NOPK_TAB_HIS drop column test (size 48)].2018-02-26 09:19:30 INFO OGG-00487 DDL operation included [include mapped], optype [ALTER], objtype [TABLE], objowner [SCOTT], objname [NOPK_TAB_HIS].2018-02-26 09:19:30 INFO OGG-01407 Setting current schema for DDL operation to [SCOTT].2018-02-26 09:19:30 INFO OGG-00484 Executing DDL operation.2018-02-26 09:19:30 INFO OGG-00483 DDL operation successful.2018-02-26 09:19:30 INFO OGG-01408 Restoring current schema for DDL operation to [ogg].2018-02-26 09:19:30 INFO OGG-00489 DDL is of mapped scope, after mapping new operation [alter table SCOTT.NOPK_TAB_AUDIT drop column test (size 50)].2018-02-26 09:19:30 INFO OGG-00488 DDL operation excluded [exclude objname scott.*_audit], optype [ALTER], objtype [TABLE], objowner [SCOTT], objname [NOPK_TAB_AUDIT].2018-02-26 09:19:30 INFO OGG-00489 DDL is of mapped scope, after mapping new operation [alter table SCOTT.NOPK_TAB_DEL drop column test (size 48)].2018-02-26 09:19:30 INFO OGG-00487 DDL operation included [include mapped], optype [ALTER], objtype [TABLE], objowner [SCOTT], objname [NOPK_TAB_DEL].2018-02-26 09:19:30 INFO OGG-01407 Setting current schema for DDL operation to [SCOTT].2018-02-26 09:19:30 INFO OGG-00484 Executing DDL operation.2018-02-26 09:19:30 INFO OGG-00483 DDL operation successful.2018-02-26 09:19:30 INFO OGG-01408 Restoring current schema for DDL operation to [ogg].
提示成功捕获到了ddl看一下表结构
SQL desc dept;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)SQL desc dept_his;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)SQL desc dept_del;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
BEFORE_AFTER VARCHAR2(20)
DEL_DATE TIMESTAMP(6)
OP_FLAG VARCHAR2(20)
接下来再看一下nopk_tab的:
SQL desc nopk_tab;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(6)
ENAME VARCHAR2(20)
SAL NUMBER(7,2)
DEPTNO NUMBER(4)SQL desc nopk_tab_his;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(6)
ENAME VARCHAR2(20)
SAL NUMBER(7,2)
DEPTNO NUMBER(4)SQL desc nopk_tab_del;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(6)
ENAME VARCHAR2(20)
SAL NUMBER(7,2)
DEPTNO NUMBER(4)
BEFORE_AFTER VARCHAR2(20)
DEL_DATE TIMESTAMP(6)
OP_FLAG VARCHAR2(20)
十一、在另一个文章【大数据实时数据同步】OGG多路映射同步原表审计表只存删除数据表实现方案(二)
