一、疑问
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导出，目标端导入可以自动处理。