适用范围
源端生产库用户数据实时同步到历史归档库,只同步insert、update这类操作，所有表都不同步delete、drop、truncate操作；历史归档库保留全部数据不被修改。
实施步骤
1.源库与目标库创建相同普通表、分区表
#创建用户jinksom、enmotech，然后创建普通表T、分区表RANGE_PART_TAB
CREATE TABLE "JINKSOM"."T"
( "N1" NUMBER,
"N2" NUMBER,
"N3" NUMBER,
"N4" NUMBER,
"N5" NUMBER,
"N6" NUMBER,
"N7" NUMBER,
"N8" NUMBER,
"N9" NUMBER,
"PAD" VARCHAR2(1000)
) TABLESPACE "USERS";
CREATE TABLE "JINKSOM"."RANGE_PART_TAB"
( "ID" NUMBER,
"DEAL_DATE" DATE,
"AREA_CODE" NUMBER,
"CONTENTS" VARCHAR2(4000)
) TABLESPACE "USERS"
PARTITION BY RANGE ("DEAL_DATE")(
PARTITION "P1" VALUES LESS THAN (TO_DATE(' 2012-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) TABLESPACE "USERS" ,
PARTITION "P3" VALUES LESS THAN (TO_DATE(' 2012-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) TABLESPACE "USERS" ,
PARTITION "P4" VALUES LESS THAN (TO_DATE(' 2012-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) TABLESPACE "USERS" ,
PARTITION "P5" VALUES LESS THAN (TO_DATE(' 2012-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) TABLESPACE "USERS" ,
PARTITION "P6" VALUES LESS THAN (TO_DATE(' 2012-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) TABLESPACE "USERS" ,
PARTITION "P7" VALUES LESS THAN (TO_DATE(' 2012-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) TABLESPACE "USERS" ,
PARTITION "P8" VALUES LESS THAN (TO_DATE(' 2012-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) TABLESPACE "USERS" ,
PARTITION "P9" VALUES LESS THAN (TO_DATE(' 2012-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) TABLESPACE "USERS" ,
PARTITION "P10" VALUES LESS THAN (TO_DATE(' 2012-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) TABLESPACE "USERS" ,
PARTITION "P11" VALUES LESS THAN (TO_DATE(' 2012-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) TABLESPACE "USERS" ,
PARTITION "P12" VALUES LESS THAN (TO_DATE(' 2013-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) TABLESPACE "USERS" ,
PARTITION "P_MAX" VALUES LESS THAN (MAXVALUE) TABLESPACE "USERS" );
#数据略2.配置OGG抽取进
dblogin USERID goldengate@enmodb, password Enmo#2020
register extract pext2 database
add extract pext2, integrated tranlog, begin now
add exttrail /u01/app/extract/dirdatpext2/lt,extract pext2, megabytes 200
edit param pext2
EXTRACT pext2
USERID goldengate@enmodb, password Enmo#2020
EXTTRAIL /u01/app/extract/dirdatpext2/lt
CACHEMGR CACHESIZE 4GB
WARNLONGTRANS 1h, CHECKINTERVAL 5m, SKIPEMPTYTRANS
REPORTCOUNT EVERY 1 MINUTES, RATE
DISCARDFILE /u01/app/extract/dirrpt/pext2.dsc, APPEND, MEGABYTES 1024
DISCARDROLLOVER AT 6:00
DDL INCLUDE ALL &
EXCLUDE OPTYPE DROP &
EXCLUDE OPTYPE TRUNCATE &
EXCLUDE INSTR 'DROP PARTITION' &
EXCLUDE INSTR 'TRUNCATE PARTITION'
IGNOREDELETES
TABLE jinksom.t;
TABLE jinksom.RANGE_PART_TAB;
TABLE jinksom.NORM_TAB;3.配置应用进程
add replicat prep2, integrated exttrail /u01/app/extract/dirdatpext2/lt
edit param prep2
REPLICAT prep2
USERID goldengate@enmodb, password Enmo#2020
ASSUMETARGETDEFS
DBOPTIONS INTEGRATEDPARAMS(max_sga_size 128, parallelism 2)
DISCARDFILE /u01/app/extract/dirrpt/prep2.dsc, APPEND, MEGABYTES 1024
DISCARDROLLOVER AT 6:00
REPORTCOUNT EVERY 1 MINUTES, RATE
map jinksom.t, target enmotech.t;
map jinksom.RANGE_PART_TAB, target enmotech.RANGE_PART_TAB;
map jinksom.NORM_TAB, target enmotech.NORM_TAB;4.执行delete操作
GGSCI (enmoedu1 as goldengate@enmodb) 50> stats pext2 total
Sending STATS request to EXTRACT PEXT2 ...
Start of Statistics at 2023-06-19 01:23:57.
Output to /u01/app/extract/dirdatpext2/lt:
Extracting from JINKSOM.T to JINKSOM.T:
*** Total statistics since 2023-06-19 01:23:32 ***
Total inserts 0.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 10.00
Total operations 0.00
End of Statistics.
SYS@enmodb> select count(*) from jinksom.t;
COUNT(*)
----------
9990
SYS@enmodb> select count(*) from enmotech.t;
COUNT(*)
----------
100005.执行drop操作
SYS@enmodb> alter table jinksom.RANGE_PART_TAB drop partition p2;
Table altered.
GGSCI (enmoedu1) 17> stats pext2 total
Sending STATS request to EXTRACT PEXT2 ...
No active extraction maps.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 1.00
Mapped operations 1.00
Unmapped operations 0.00
Other operations 0.00
Excluded operations 1.00
SYS@enmodb> select count(*) from jinksom.t;
select count(*) from jinksom.t
*
ERROR at line 1:
ORA-00942: table or view does not exist
SYS@enmodb> select count(*) from enmotech.t;
COUNT(*)
----------
10010
SYS@enmodb> select count(*) from jinksom.RANGE_PART_TAB partition(p2);
select count(*) from jinksom.RANGE_PART_TAB partition(p2)
*
ERROR at line 1:
ORA-02149: Specified partition does not exist
SYS@enmodb> select count(*) from enmotech.RANGE_PART_TAB partition(p2);
COUNT(*)
----------
406.执行truncate操作
SYS@enmodb> alter table jinksom.RANGE_PART_TAB truncate partition p2;
Table truncated.
GGSCI (enmoedu1) 34> stats pext2 total
Sending STATS request to EXTRACT PEXT2 ...
Start of Statistics at 2023-06-20 00:03:44.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 2.00
Mapped operations 2.00
Unmapped operations 0.00
Other operations 0.00
Excluded operations 2.00
SYS@enmodb> select count(*) from jinksom.RANGE_PART_TAB partition(p2);
COUNT(*)
----------
0
SYS@enmodb> select count(*) from enmotech.RANGE_PART_TAB partition(p2);
COUNT(*)
----------
20