OGG复制进程能不指定SCN复制表吗？ 参数ENABLE_INSTANTIATION_FILTERING

2023-12-29 16:28:04

一、疑问

Oracle OGG是如何正确的进行数据同步呢？初始化一个表,完成一个数据的同步？

1)假设同步的表有静态时间2小时;

2小时内，通过db link,exp,expdp 数据导入在目标库新建表,完成第一次的数据初始化;

随后，源端目标端使用OGG追加这个表进行数据同步；

2)假设同步的表数据随时可能修改；

一般选择，源端对表开启最小补充日志,源端指定scn导出，目标端导入后,完成数据初始化;

随后，目标端OGG指定scn,复制进程级别或者表级别进行csn过滤,过滤ogg复制进程trail csn ~ 源端导出scn之间的数据变换！

如下是MOS说明，通过csn完成数据同步，比使用参数handlecollision可靠！

Oracle GoldenGate Best Practices: Instantiation from an Oracle Source Database (Doc ID 1276058.1)    
This document is an introduction to Oracle GoldenGate’s best practices and guidelines for instantiation of a target database from
 an Oracle Source Database.  This method will allow the customer to start Replicat at a specific point in the trail file created by
 Extract by using CSN number so the Replicat will not hit any "duplicate" records.  This is a much better initial load methodology than
 the traditional "handlecollision" solution.

3）我们认为数据都可能改变，但是不想使用方法二？ 咋整？

Oracle对OGG12.2 又来新特性了，使用参数，Oracle自动追踪数据泵导出的scn，OGG启动时查询目标库的信息自动进行过滤！

DBOPTIONS
参考
https://blog.csdn.net/diaokan1920/article/details/101762900
https://blog.dbi-services.com/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate/
ENABLE_INSTANTIATION_FILTERING
Valid for Oracle. Enables automatic per table instantiation CSN filtering on tables imported using Oracle data pump or manually instantiated
 using the SET_INSTANTIATION_CSN command.

SOURCE_DB_NAME src_dbase_global_name
Valid for Oracle. Indicates the Global Name of the Trail Source Database. It is used to query the relevant instantiation information when
 DBOPTIONS ENABLE_INSTANTIATION_FILTERING is enabled. This option is optional for instantiation filtering in a 12.2. trail file with
 metadata enabled.
When the source has no DOMAIN, do not specify a DOMAIN for the downstream database.

 

 

二、学习测试

2.1 测试链路数据同步测试

1）源端测试表新建、最小补充日志开启

备注：源端目标端DB 11.2.0.4,OGG19.1

Source
SQL> conn yz/yz
SQL> create table a as select * from dba_objects;
SQL> create table b as select * from dba_objects;

GGSCI (t1) 1> dblogin USERID ogg,PASSWORD OGG
> add trandata yz.a
> add trandata yz.b

alter table a rename to test00a1;
alter table b rename to test00a2;
info trandata yz.test00a2
Prepared CSN for table YZ.TEST00A1: 1937981
Prepared CSN for table YZ.TEST00A2: 1938904
本次测试附带测试了一波rename 最小补充日志是否生效,oracle给力，自动内部进行重命名，还是有效的！

2）视图说明

DBA_CAPTURE_PREPARED_TABLES displays information about all tables prepared for instantiation at the local database
DBA_APPLY_INSTANTIATED_OBJECTS displays information about objects for which an instantiation SCN has been set.
INSTANTIATION_SCN
Instantiation SCN for the object. Only changes committed after this SCN are applied by an apply process.
IGNORE_SCN
SCN below which the instantiation SCN cannot be set. This value corresponds to the SCN value at the source database at the time
 when the object was prepared for instantiation.

SQL> select table_name, scn from dba_capture_prepared_tables where table_owner = 'YZ' ;
TABLE_NAME SCN
------------------------------ ----------
TEST00A1 1937981
GBK1     1799996
TEST00A2 1938904

Target
SQL> select source_object_name, instantiation_scn, ignore_scn from dba_apply_instantiated_objects where source_object_owner = 'YZ' ;
null

以上可知，最小补充日志的添加，对应的SCN=>dba_capture_prepared_tables=>SCN

3)OGG进程链路关系

>ext_gbk
EXTTRAIL /u01/ogg/base/dirdat/ext_gbk/cc
> view param ext_gbk
TABLE yz.gbk1,cols(id);
--add
TABLE YZ.TEST00A1;
TABLE YZ.TEST00A2;

> info D_GBK_A,detail
Trail Name 
/u01/ogg/base/dirdat/gbk/aa
/u01/ogg/base/dirdat/ext_gbk/cc000000021
--add
TABLE YZ.TEST00A1;
TABLE YZ.TEST00A2;

4）数据初始化同步，观察视图SCN变换

--expdp
select * from dba_directories;
create directory dump as '/home/oracle';
grant read,write on directory dump to sys;
expdp \'/ as sysdba\' directory=dump dumpfile=TEST00A1.dmp logfile=TEST00A1.log tables=yz.TEST00A1
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    1943827
expdp \'/ as sysdba\' directory=dump dumpfile=TEST00A2.dmp logfile=TEST00A2.log tables=yz.TEST00A2 FLASHBACK_SCN=1943827
$ scp TEST00A*.dmp oracle@10.0.0.32:/home/oracle/.
impdp \'/ as sysdba\' directory=dump dumpfile=TEST00A1.dmp logfile=TEST00A1.log REMAP_SCHEMA=YZ:BAK_YZ
impdp \'/ as sysdba\' directory=dump dumpfile=TEST00A2.dmp logfile=TEST00A2.log REMAP_SCHEMA=YZ:BAK_YZ
select source_object_name, instantiation_scn, ignore_scn from dba_apply_instantiated_objects where source_object_owner = 'YZ'
SOURCE_OBJECT_NAME             INSTANTIATION_SCN IGNORE_SCN
------------------------------ ----------------- ----------
TEST00A2                                 1943827          0
TEST00A1                                 1944375    1937981
我们可以发现当源端指定了SCN，则目标端OGG INSTANTIATION_SCN  = 指定的导出SCN
没有指定SCN则INSTANTIATION_SCN 暂时无法判定！IGNORE_SCN则=源端对表开启最小补充日志的SCN

5）测试OGG链路同步OK

数据初始化同步时，OGG进程是源端抽取、投递正常，目标端复制进程未新建、未处理！
新增复制进程
>info REP_TA,detai
> add replicat REP_GBKA,exttrail /u01/ogg/base/dirdat/gbk/aa
>edit param REP_GBKA
replicat REP_GBKA
userid ogg,password ogg
assumetargetdefs 
discardfile  /u01/ogg/base/dirrpt/REP_GBKA.dsc
MAP YZ.TEST00A1 ,TARGET BAK_YZ.TEST00A1;
MAP YZ.TEST00A2 ,TARGET BAK_YZ.TEST00A2;

alter replicat REP_GBKA,extseqno 9 extrba 0 
start rep_gbka
确认数据同步正常！

 

 

2.2新增参数测试

1）环境清理准备

目标端清理数据，停止复制进程！
GGSCI (t2) 9> stop rep_gbka
Log Read Checkpoint  File /u01/ogg/base/dirdat/gbk/aa000000013
                     2021-07-01 10:56:08.685040  RBA 4034
                     
SQL> drop table TEST00A1;
SQL> drop table TEST00A2;

 

2）数据泵重新数据初始化

Source
expdp \'/ as sysdba\' directory=dump dumpfile=TEST00A1.dmp logfile=TEST00A1.log tables=yz.TEST00A1
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    1948959
expdp \'/ as sysdba\' directory=dump dumpfile=TEST00A2.dmp logfile=TEST00A2.log tables=yz.TEST00A2 FLASHBACK_SCN=1948959
$ scp TEST00A*.dmp oracle@10.0.0.32:/home/oracle/.
SQL> select table_name, scn from dba_capture_prepared_tables where table_owner = 'YZ' ;
TABLE_NAME                            SCN
------------------------------ ----------
TEST00A1                          1937981
GBK1                              1799996
TEST00A2                          1938904

 

Target
impdp 之前
select source_object_name, instantiation_scn, ignore_scn from dba_apply_instantiated_objects where source_object_owner = 'YZ';
SOURCE_OBJECT_NAME             INSTANTIATION_SCN IGNORE_SCN
------------------------------ ----------------- ----------
TEST00A2                                 1943827          0
TEST00A1                                 1944375    1937981

impdp \'/ as sysdba\' directory=dump dumpfile=TEST00A1.dmp logfile=TEST00A1.log REMAP_SCHEMA=YZ:BAK_YZ
impdp \'/ as sysdba\' directory=dump dumpfile=TEST00A2.dmp logfile=TEST00A2.log REMAP_SCHEMA=YZ:BAK_YZ
select source_object_name, instantiation_scn, ignore_scn from dba_apply_instantiated_objects where source_object_owner = 'YZ';
SOURCE_OBJECT_NAME             INSTANTIATION_SCN IGNORE_SCN
------------------------------ ----------------- ----------
GBK1                                     1949541    1799996
TEST00A2                                 1948959          0
TEST00A1                                 1949541    1937981

 

3）复制进程添加参数，观察效果！

source db dml
删除一条记录后source
TEST00A1 86583
TEST00A2 86587

target 
ogg
启动！
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
GGSCI (t2) 12> start rep_gbka
2021-07-01 11:05:41  INFO    OGG-10155  Instantiation CSN filtering is enabled on table YZ.TEST00A1 at CSN 1949541.
2021-07-01 11:05:41  INFO    OGG-10155  Instantiation CSN filtering is enabled on table YZ.TEST00A2 at CSN 1948959.
其实没必要确认测试数据，读取日志我们可以清晰的发现，是等同于最开始说的方法二、表或进程级别filtering csn一样的效果
这个scn选择的是INSTANTIATION_SCN
Export: Release 11.2.0.4.0 - Production on Thu Jul 1 10:59:03 2021

 select timestamp_to_scn(to_date('2021-07-01 10:59:03','yyyy-mm-dd hh24:mi:ss')) from v$database;
 TIMESTAMP_TO_SCN(TO_DATE('2021-07-0110:59:03','YYYY-MM-DDHH24:MI:SS'))
 ---------------------------------------------------------------------- 
1947745  --因为数据泵导出的日志时间不一定完全准确！ 但是可以认为是相近的了。

 

三、结论

复制进程使用如下参数
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
本质上是使用dba_apply_instantiated_objects INSTANTIATION_SCN 进行过滤；

建议：1.源端指定scn进行导出这个scn= dba_apply_instantiated_objects INSTANTIATION_SCN 
2.源端导出不知道scn ，根据导出日志的时间与dba_apply_instantiated_objects INSTANTIATION_SCN 基本吻合，但是不知道Oracle内部如何实现的不敢确认。
建议生产环境源端导出还是使用统一scn导出，目标端导入可以自动处理。

 

上一篇：PHP介绍及安装


下一篇：修改为无线流程