下面的示例中会演示这个特性,现在队列文件中包含有两个重要的信息DDR(数据库定义记录)和TDR(表定义记录)。
每个队列文件在某个库的任何一个DML或序列操作之前都会包含一个DDR,DDR中包含有数据库的特别信息,如字符集、数据库名、数据库类型等。
同时,在任何一个表的DML之前,队列文件中还会有一条TDR,TDR中包含有表名、字段定义,如字段数、字段类型、字段长度等。
下面在源和目标端分别创建一个结构不同的测试表。
源:
SQL> create table system.test_ogg (emp_id number, first_name varchar2(20), last_name varchar2(20));
Table created.
SQL> alter table system.test_ogg add constraint pk_test_ogg primary key (emp_id);
Table altered.
目标端:
SQL> create table system.test_ogg (emp_id number,f_name varchar(20),l_name varchar2(20));
Table created.
SQL> alter table system.test_ogg add constraint pk_test_ogg primary key (emp_id);
Table altered.
源端创建抽取和传输进程:
host1>./ggsci
GGSCI (host1 as oggsuser@DB01) 5> add extract etest integrated tranlog begin now
EXTRACT (Integrated) added.
GGSCI (host1 as oggsuser@DB01) 6> add exttrail ./dirdat/auxdit/lt extract etest
EXTTRAIL added.
GGSCI (host1 as oggsuser@DB01) 9> add extract ptest exttrailsource ./dirdat/auxdit/lt
EXTRACT added.
GGSCI (host1 as oggsuser@DB01) 11> add rmttrail ./dirdat/bsstg/rt extract ptest
RMTTRAIL added.
GGSCI (host1 as oggsuser@DB01) 10> register extract etest database
2015-12-21 05:09:33 INFO OGG-02003 Extract ETEST successfully registered with database at SCN 391450385.
抽取和传输进程参数
extract etest
USERIDALIAS oggsuser_bsstg
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
TRANLOGOPTIONS EXCLUDEUSER OGGSUSER
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 2048, parallelism 2)
EXTTRAIL ./dirdat/auxdit/lt
WARNLONGTRANS 2h, CHECKINTERVAL 30m
REPORTCOUNT EVERY 15 MINUTES, RATE
STATOPTIONS RESETREPORTSTATS
REPORT AT 23:59
REPORTROLLOVER AT 00:01 ON MONDAY
GETUPDATEBEFORES
TABLE SYSTEM.TEST_OGG;
EXTRACT ptest
USERIDALIAS oggsuser_bsstg
RMTHOST host2, MGRPORT 7809 TCPBUFSIZE 200000000, TCPFLUSHBYTES 200000000, compress
RMTTRAIL ./dirdat/bsstg/rt
PASSTHRU
REPORTCOUNT EVERY 15 MINUTES, RATE
TABLE SYSTEM.TEST_OGG;
目标端创建投递进程
GGSCI (host2) 2> add replicat rtest integrated exttrail ./dirdat/bsstg/rt
REPLICAT (Integrated) added.
投递进程参数文件,里面不再需要ASSUMETARGETDEFS
REPLICAT rtest
SETENV (ORACLE_HOME="/orasw/app/oracle/product/12.1.0/db_1")
SETENV (TNS_ADMIN="/orasw/app/oracle/product/12.1.0/db_1/network/admin")
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
USERIDALIAS oggsuser_auxdit
MAP SYSTEM.TEST_OGG, TARGET SYSTEM.TEST_OGG;
启动这三个进程
GGSCI (host1 as oggsuser@DB01) 15> start manager
Manager started.
GGSCI (host1 as oggsuser@DB01) 16> start etest
EXTRACT ETEST starting
GGSCI (host1 as oggsuser@DB01) 17> start ptest
GGSCI (host2) 3> start rtest
GGSCI (host2) 4> info rtest
REPLICAT RTEST Last Started 2015-12-21 05:21 Status RUNNING
INTEGRATED
Checkpoint Lag 00:00:00 (updated 00:08:53 ago)
Process ID 29864
Log Read Checkpoint File ./dirdat/bsstg/rt000000000
First Record RBA 0
源端db插入一行:
SQL> insert into system.test_ogg values (007, 'JAMES','BOND');
1 row created.
SQL> commit;
Commit complete.
目标端投递进程上可以看到已经有变化
GGSCI (host2) 5> stats rtest latest
Sending STATS request to REPLICAT RTEST ...
Start of Statistics at 2015-12-21 05:26:32.
Integrated Replicat Statistics:
Total transactions 1.00
Redirected 0.00
DDL operations 0.00
Stored procedures 0.00
Datatype functionality 0.00
Event actions 0.00
Direct transactions ratio 0.00%
Replicating from SYSTEM.TEST_OGG to SYSTEM.TEST_OGG:
*** Latest statistics since 2015-12-21 05:25:33 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
在投递进程的输出日志中,可以看到在队列文件中有获取到test_ogg表的定义信息
2015-12-21 05:25:22 INFO OGG-06505 MAP resolved (entry SYSTEM.TEST_OGG): MAP "SYSTEM"."TEST_OGG", TARGET SYSTEM.TEST_OGG.
2015-12-21 05:25:33 INFO OGG-02756 The definition for table SYSTEM.TEST_OGG is obtained from the trail file.
By using the logdump utility we can view the Database Definition Record (DDR) as well as Table Definition Record (TDR) information contained in the trail file.
DDR Version: 1
Database type: ORACLE
Character set ID: we8iso8859p1
National character set ID: UTF-16
Locale: neutral
Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14
TimeZone: GMT-07:00
Global name: BSSTG
2015/12/21 05:25:18.534.893 Metadata Len 277 RBA 1541
Name: SYSTEM.TEST_OGG
*
1)Name 2)Data Type 3)External Length 4)Fetch Offset 5)Scale 6)Level
7)Null 8)Bump if Odd 9)Internal Length 10)Binary Length 11)Table Length 12)Most Sig DT
13)Least Sig DT 14)High Precision 15)Low Precision 16)Elementary Item 17)Occurs 18)Key Column
19)Sub DataType 20)Native DataType 21)Character Set 22)Character Length 23)LOB Type 24)Partial Type
*
TDR version: 1
Definition for table SYSTEM.TEST_OGG
Record Length: 108
Columns: 3
EMP_ID 64 50 0 0 0 1 0 50 50 50 0 0 0 0 1 0 1 2 2 -1 0 0 0
FIRST_NAME 64 20 56 0 0 1 0 20 20 0 0 0 0 0 1 0 0 0 1 -1 0 0 0
LAST_NAME 64 20 82 0 0 1 0 20 20 0 0 0 0 0 1 0 0 0 1 -1 0 0 0
End of definition