Oracle GoldenGate 12.2 集成的datapump internal例子

在以前的OGG版本中做oracle的表的初始化工作中， 通常为了捕捉在初始化过程中的数据变化， Source 端会在expdp 前启用extract进程，然后使用expdp 配合flashback_scn参数导出，在Target 端impdp 后，replicat 配置HANDLECOLLISIONS 参数做初始化的工作丢掉“重复或已存在的”更新，或者使用AFTERCSN参数定位的trail 文件中expdp那个时间点的scn ，从那个scn后开始应用变化，  在ogg 12.2 中引入了新特性把上面的一系列工作集成到了数据库内，只需要配置个别的ogg 参数就可以实现， 首先推荐看一下Gavin soorma的这篇笔记，当然如果你的英文不好也没关系， 下面我也会描述这个过程，及背后的实现。

先简单的总结一下整个过程:

1， 在ogg中增加附加TRANDATA或SCHEMATRANDATA时会在数据库的系统启用流捕捉，并在对应的系统表记录table或schema

2， 在expdp导出指定表时，会自动启动flashback_scn, 并把scn 记录到dump file中

3， 在impdp导入后，表及scn 也会自动导入目标的系统表

4， 在replicat时如果配置了DBOPTIONS ENABLE_INSTANTIATION_FILTERING就会读取#3时的系统表，自动过滤scn操作

ggmgr是ogg 管理用户，anbob是我的业务表schema, 我的测试环境  ORACLE 11.2.0.4 FOR Solaris ,
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.160223 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_160206.1508.4_FBO
Solaris, x64, 64bit (optimized), Oracle 11g

Gavin的环境提示在增加PREPARECSN , 实测add trandata是默认带PREPARECSN

SQL> create table tobj as select * from dba_objects;
Table created.
SQL> create table tobj2 as select * from dba_objects;
Table created.

[oracle@solaris_db1:/export/home/oracle/ogg12_2]# ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.160223 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_160206.1508.4_FBO
Solaris, x64, 64bit (optimized), Oracle 11g on Feb 19 2016 18:51:21
Operating system character set identified as ISO-8859-1.
Copyright (C) 1995, 2016, Oracle and/or its affiliates. All rights reserved.

GGSCI (solaris_db1 as ggmgr@ORCL) 5> add trandata anbob.tobj
2016-08-15 03:18:31 WARNING OGG-06439 No unique key is defined for table TOBJ. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table ANBOB.TOBJ.
TRANDATA for scheduling columns has been added on table 'ANBOB.TOBJ'.
TRANDATA for instantiation CSN has been added on table 'ANBOB.TOBJ'.

GGSCI (solaris_db1 as ggmgr@ORCL) 6> add trandata anbob.tobj2 PREPARECSN
2016-08-15 06:47:37 WARNING OGG-06439 No unique key is defined for table TOBJ2. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table ANBOB.TOBJ2.
TRANDATA for scheduling columns has been added on table 'ANBOB.TOBJ2'.
TRANDATA for instantiation CSN has been disabled on table 'ANBOB.TOBJ2'

GGSCI (solaris_db1 as ggmgr@ORCL) 7> add schematrandata anbob preparecsn
ERROR: Operation not supported because enable_goldengate_replication is not set to true.

anbob@ORCL>alter system set enable_goldengate_replication=true;
System altered.
<
GGSCI (solaris_db1 as ggmgr@ORCL) 8> add schematrandata anbob preparecsn
2016-08-18 02:42:16  INFO    OGG-01788  SCHEMATRANDATA has been added on schema anbob.
2016-08-18 02:42:16  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema anbob.
2016-08-18 02:42:16  INFO    OGG-10154  Schema level PREPARECSN set to mode NOWAIT on schema anbob.

GGSCI (solaris_db1 as ggmgr@ORCL) 9> info schematrandata anbob
2016-08-18 02:42:53  INFO    OGG-06480  Schema level supplemental logging, excluding non-validated keys, is enabled on schema ANBOB.
2016-08-18 02:42:53  INFO    OGG-01980  Schema level supplemental logging is enabled on schema ANBOB for all scheduling columns.
2016-08-18 02:42:53  INFO    OGG-10462  Schema ANBOB have 10 prepared tables for instantiation.

GGSCI (solaris_db1 as ggmgr@ORCL) 10> delete schematrandata anbob preparecsn
2016-08-18 03:02:44  INFO    OGG-01792  SCHEMATRANDATA has been deleted on schema anbob.
2016-08-18 03:02:44  INFO    OGG-01979  SCHEMATRANDATA for scheduling columns has been deleted on schema anbob.

GGSCI (solaris_db1 as ggmgr@ORCL) 11> add schematrandata anbob
2016-08-18 03:02:53  INFO    OGG-01788  SCHEMATRANDATA has been added on schema anbob.
2016-08-18 03:02:53  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema anbob.

GGSCI (solaris_db1 as ggmgr@ORCL) 12> info schematrandata anbob
2016-08-18 03:03:00  INFO    OGG-06480  Schema level supplemental logging, excluding non-validated keys, is enabled on schema ANBOB.
2016-08-18 03:03:00  INFO    OGG-01980  Schema level supplemental logging is enabled on schema ANBOB for all scheduling columns.
2016-08-18 03:03:00  INFO    OGG-10462  Schema ANBOB have 10 prepared tables for instantiation.

SQL> select * from sys.streams$_prepare_object;
      OBJ#   CAP_TYPE IGNORE_SCN TIMESTAMP              FLAGS     SPARE1 SPARE2
---------- ---------- ---------- ----------------- ---------- ---------- ----------
     87494          0    1092932 20160815 03:18:32          0
...
SQL> @oid 87494
owner                     object_name                    object_type        SUBOBJECT_NAME                 CREATED           LAST_DDL_TIME     status    DATA_OBJECT_ID
------------------------- ------------------------------ ------------------ ------------------------------ ----------------- ----------------- --------- --------------
ANBOB                     TOBJ                           TABLE                                             20160815 03:09:46 20160815 03:18:31 VALID              87494

Note:
在我的测试版本中preparecsn是默认值，带不带功能是一样的，但是对于add schematrandata 要求数据库参数enable_goldengate_replication必须为true. add schematrandata调用的是dbms_capture_adm.prepare_schema_instantiation 对应的数据可以查询DBA_CAPTURE_PREPARED_SCHEMAS view,  add trandata 调用的是DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION对应的数据可以查询sys.streams$_prepare_object 表。这是可以做10046 trace 跟踪ogg sid 的操作得到的。

SQL> @usid 29
USERNAME                SID                 AUDSID OSUSER           MACHINE            PROGRAM              SPID             OPID CPID                     SQL_ID         HASH_VALUE   LASTCALL STATUS   SADDR            PADDR            TADDR            LOGON_TIME
----------------------- -------------- ----------- ---------------- ------------------ -------------------- -------------- ------ ------------------------ ------------- ----------- ---------- -------- ---------------- ---------------- ---------------- -----------------
GGMGR                    '29,65'            130359 oracle           solaris_db1        (TNS V1-V3)          1943               25 1938                                             0         83 INACTIVE 00000000A45E7B70 00000000A4F901E0                  20160815 03:11:39

SQL> oradebug setorapid 25
Oracle pid: 25, Unix process pid: 1943, image: oracle@solaris_db1 (TNS V1-V3)

SQL> oradebug event 10046 trace name context forever, level 12;
Statement processed.

SQL> oradebug event 10046 trace name context off;

下面看第2步expdp， 当然可以使用TRACE=400301 , 生成expdp时的sql trace.

[oracle@solaris_db1:/export/home/oracle/ogg12_2]# expdp anbob/anbob tables=tobj directory=DATA_PUMP_DIR file=obj3.dump TRACE=400301
Export: Release 11.2.0.4.0 - Production on Mon Aug 15 06:52:25 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=obj3.dump" Location: Command Line, Replaced with: "dumpfile=obj3.dump"
Legacy Mode has set reuse_dumpfiles=true parameter.
FLASHBACK automatically enabled to preserve database integrity.
Starting "ANBOB"."SYS_EXPORT_TABLE_01":  anbob/******** tables=tobj directory=DATA_PUMP_DIR dumpfile=obj3.dump reuse_dumpfiles=true
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 10 MB
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "ANBOB"."TOBJ"                              8.368 MB   86294 rows
NOTE:
会看到”FLASHBACK automatically enabled to preserve database integrity “字样在expdp时，但是同时会发现如果这个库里做过一次add trandata， expdp其它的表无论有没有在该表上加add trandata，在导出时都会显示FLASHBACK 自动启动， 我对streams不是很了解，数据库中执行EXECUTE DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION 清理了streams的配置后，expdp就不会再显示了flashback auto enable(前提是你不再使用ogg抽取数据)。

下面第3步impdp 时也不需要特殊操作，如果捕捉impdp时的sql trace， 同样在impdp 使用TRACE=400301 , 会从trace file中发现其实impdp时更新的就是这个表sys.apply$_source_obj （or sys.apply$_source_schema）实现的传递scn.

然后查看trace文件

PARSING IN CURSOR #139897537690904 len=153 dep=3 uid=0 oct=6 lid=0 tim=1471425319506574 hv=1022069042 ad='7f8f1128' sqlid='fuxjz0nyfr29k'
update sys.apply$_source_obj  set inst_scn = :1, ignore_scn = :2  where owner = :3 and name = :4 and type = 2 and source_db_name = :5  and dblink is null
END OF STMT
PARSE #139897537690904:c=0,e=85,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=4,plh=3247253098,tim=1471425319506574
BINDS #139897537690904: Bind#0
 oacdty=02 mxl=22(05) mxlc=00 mal=00 scl=00 pre=00
 oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
 kxsbbbfp=7fff2a0eaad0 bln=22 avl=05 flg=09
 value=22440483
 Bind#1
 oacdty=02 mxl=22(01) mxlc=00 mal=00 scl=00 pre=00
 oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
 kxsbbbfp=7fff2a0eaae8 bln=22 avl=01 flg=09
 value=0
 Bind#2
 oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
 oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
 kxsbbbfp=7fb680cf bln=32 avl=04 flg=09
 value="ANBOB"
 Bind#3
 oacdty=01 mxl=32(07) mxlc=00 mal=00 scl=00 pre=00
 oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
 kxsbbbfp=7fb680c8 bln=32 avl=07 flg=09
 value="TOBJ"
 Bind#4
 oacdty=01 mxl=32(07) mxlc=00 mal=00 scl=00 pre=00
 oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
 kxsbbbfp=7fb680d3 bln=32 avl=07 flg=09
 value="ORCL11G"
下面是第4步 配置replicat, 需要配置DBOPTIONS ENABLE_INSTANTIATION_FILTERING 过滤SCN, 如果对rep进程做sql trace的话，会发现，其实rep启动时读取DBA_APPLY_INSTANTIATED_OBJECTS(DBA_APPLY_INSTANTIATED_SCHEMAS)视图基于的就是impdp 导入的表

GGSCI (db2 as ogg@orcl11g) 57> edit param r_test1
handlecollisions
replicat r_test1
setenv (LANG = en_us.utf8)
--ASSUMETARGETDEFS
GETTRUNCATES
REPORTCOUNT EVERY 30 MINUTES, RATE
grouptransops 100
USERID ogg, PASSWORD AACAAAAAAAAAAADAHBLDCCIIOIRFNEPB,ENCRYPTKEY DEFAULT
reperror default,discard
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
discardfile dirrpt/r_test1.dsc, append,megabytes 100

map ANBOB.TOBJ, target ANBOB.TOBJ;

启动replicat后跟踪日志
[oracle@db2 ggs]$ tail -1000 ggserr.log
    /home/oracle/ggs/dirtmp.
...

    /home/oracle/ggs/dirtmp.
2016-08-17 15:37:47  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, r_test1.prm:  REPLICAT R_TEST1 started.
2016-08-17 15:37:47  INFO    OGG-02243  Oracle GoldenGate Delivery for Oracle, r_test1.prm:  Opened trail file dirdat/r1000000000 at 2016-08-17 15:37:47.038656.
2016-08-17 15:37:47  INFO    OGG-03522  Oracle GoldenGate Delivery for Oracle, r_test1.prm:  Setting session time zone to source database time zone 'GMT'.
2016-08-17 15:37:47  INFO    OGG-03506  Oracle GoldenGate Delivery for Oracle, r_test1.prm:  The source database character set, as determined from the trail file, is zhs16gbk.
2016-08-17 15:37:47  INFO    OGG-06505  Oracle GoldenGate Delivery for Oracle, r_test1.prm:  MAP resolved (entry ANBOB.TOBJ): map "ANBOB"."TOBJ", target ANBOB.TOBJ.
2016-08-17 15:37:49  WARNING OGG-06439  Oracle GoldenGate Delivery for Oracle, r_test1.prm:  No unique key is defined for table TOBJ. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2016-08-17 15:37:49  INFO    OGG-02756  Oracle GoldenGate Delivery for Oracle, r_test1.prm:  The definition for table ANBOB.TOBJ is obtained from the trail file.
2016-08-17 15:37:49  INFO    OGG-06511  Oracle GoldenGate Delivery for Oracle, r_test1.prm:  Using following columns in default map by name: ID, STR.
2016-08-17 15:37:49  INFO    OGG-10155  Oracle GoldenGate Delivery for Oracle, r_test1.prm:  Instantiation CSN filtering is enabled on table ANBOB.TOBJ at CSN 22,440,483.
2016-08-17 15:37:49  INFO    OGG-06510  Oracle GoldenGate Delivery for Oracle, r_test1.prm:  Using the following key columns for target table ANBOB.TOBJ: ID, STR.
2016-08-17 15:37:51  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  REPLICAT R_TEST1 starting.
2016-08-17 15:37:54  INFO    OGG-01021  Oracle GoldenGate Delivery for Oracle, r_test1.prm:  Command received from GGSCI: STATS.
2016-08-17 15:38:24  INFO    OGG-01971  Oracle GoldenGate Delivery for Oracle, r_test1.prm:  The previous message, 'INFO OGG-01021', repeated 1 times.
2016-08-17 16:03:50  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): info all.

SQL> SELECT * FROM DBA_APPLY_INSTANTIATED_OBJECTS;
SOURCE_DAT SOURCE_OBJECT_OWNER            SOURCE_OBJECT_NAME             SOURCE_OBJE INSTANTIATION_SCN IGNORE_SCN APPLY_DATA
---------- ------------------------------ ------------------------------ ----------- ----------------- ---------- ----------
ORCL11G    ANBOB                           TOBJ                        TABLE                22440483          0
因为replicat不是和数据库建立的长连接，可以使用DDL trigger在replicat 创建会话时在会话级启用sql trace， 如下:

CREATE OR REPLACE TRIGGER trace_test_user AFTER LOGON ON DATABASE
BEGIN
  IF USER ='[OGG REP USER]' THEN
    EXECUTE IMMEDIATE 'ALTER SESSION SET timed_statistics=true';
    EXECUTE IMMEDIATE 'ALTER SESSION SET max_dump_file_size=unlimited';
    EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 8'' ';
  END IF;
END;
/

# search scn in trace
SELECT   instantiation_scn   FROM DBA_APPLY_INSTANTIATED_OBJECTS  WHERE SOURCE_DATABASE = :1     AND        SOURCE_OBJECT_OWNER = :2 AND        SOURCE_OBJECT_NAME = :3  AND        SOURCE_OBJECT_TYPE = 'TABLE'
SELECT   instantiation_scn   FROM DBA_APPLY_INSTANTIATED_SCHEMAS  WHERE SOURCE_DATABASE = :1 AND        SOURCE_SCHEMA = :2

总结：
OGG的该特性就是利用datapump 传送的scn, 在导出时自动存入dump file,在导入时再传入目标库的系统表中， 启动ogg的应用进程时读取该系统表，自动过滤并应用数据库之后的修改。但是目前这个版本发现的只要add trandata 过一个表，然后该库的所有expdp都会自带flashback ，且导入时也都会在目标库传入导出时的scn, 暂时还不确认是不是bug？ 后续会继续更新。