ogg的logdump之 @PRESENT函数--行过滤

源端、目标建表

SENDER@hyyk> create table tb10(col1 int primary key, col2 int, col3 int);

RECEIVER@ogg> create table tb10(col1 int primary key, col2 int, col3 int);

添加表级附加日志

GGSCI (pc6 as ogg@hyyk) 25> ADD TRANDATA sender.tb10

Logging of supplemental redo data enabled for table SENDER.TB10.

TRANDATA for scheduling columns has been added on table 'SENDER.TB10'.

TRANDATA for instantiation CSN has been added on table 'SENDER.TB10'.

GGSCI (pc6 as ogg@hyyk) 27> edit params ext_s1

extract ext_s1

setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")

setenv (ORACLE_SID="hyyk")

userid ogg,password oracle

gettruncates

--FORMATASCII, NONAMES, DELIMITER '|'

--FORMATSQL ORACLE, NONAMES

--FORMATXML

exttrail /u01/app/oggs/dirdat/ss

--table sender.tab1, TOKENS(TK-CSN=@getenv('TRANSACTION','CSN'));

table sender.tab1;

table sender.tb10, where(col3 = @PRESENT and col1 >10);

@PRESENT, @ABSENT – 源端：首先判断日志文件是否有该列的存在，如果存在，在使用where后面的条件进行

过滤，满足条件的才去写trail文件或者提交。（update默认是compressupdates的，日志只记录修改列和键值列）

GGSCI (pc6 as ogg@hyyk) 32> edit params pump_s1

extract pump_s1

passthru

userid ogg,password oracle

rmthost 192.168.1.80,mgrport 7809

rmttrail /u01/app/oggd/dirdat/sd

table sender.tab1;

table sender.tb10;

GGSCI (ogg-80) 3> edit params rep_s1

replicat rep_s1

setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

setenv(ORACLE_SID="ogg")

userid ogg,password oracle

--handlecollisions

ASSUMETARGETDEFS

DISCARDFILE ./dirrpt/rorabb.dsc,PURGE

userid ogg,password oracle

map sender.tab1,target receiver.tab1;

map sender.tb10,target receiver.tb10;

源端插入数据

SENDER@hyyk> insert into sender.tb10 values(11,1,1);

SENDER@hyyk> insert into sender.tb10 values(12,1,1);

SENDER@hyyk> commit;

源端查看数据

SENDER@hyyk> select * from tb10;

COL1 COL2 COL3

------------------------------

11 1 1

12 1 1

目标段查看数据

RECEIVER@ogg> select * from tb10;

COL1 COL2 COL3

------------------- ----------

11 1 1

12 1 1

源端update操作

SENDER@hyyk> update tb10 set col2=2 where col1=12;

SENDER@hyyk> commit;

SENDER@hyyk> select * from tb10;

COL1 COL2 COL3

------------------------------

11 1 1

12 2 1 源端已经改变

RECEIVER@ogg> select * from tb10;

COL1 COL2 COL3

-----------------------------

11 1 1

12 1 1 目标端没有改变

最终没能复制。（因为此时col3没有记日志）

这里提前查看一下trail的序号，方便后面使用logdump查看trail

GGSCI (pc6 as ogg@hyyk) 70> info ext_s1,detail

EXTRACT EXT_S1 Last Started 2018-03-13 11:38 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:07 ago)

Process ID 15885

Log Read Checkpoint Oracle Redo Logs

2018-03-13 11:46:39 Thread 1, Seqno 63, RBA 34498560

SCN 0.1819900 (1819900)

Target Extract Trails:

Trail Name Seqno RBA Max MB Trail Type

/u01/app/oggs/dirdat/ss 23 2220 100 EXTTRAIL

SENDER@hyyk> update tb10 set col2=3 where col1=12 and col3=1;

SENDER@hyyk> commit;

SENDER@hyyk> select * from tb10;

COL1 COL2 COL3

------------------------------

11 1 1

12 3 1

RECEIVER@ogg> select * from tb10;

COL1 COL2 COL3

------------------------------

11 1 1

12 1 1

没能复制。（因为此时col3没有记日志）

SENDER@hyyk> update tb10 set col2=4,col3=4 where col1=12;

SENDER@hyyk> commit;

SENDER@hyyk> select * from tb10;

COL1 COL2 COL3

---------- ---------- ----------

11 1 1

12 4 4

RECEIVER@ogg> select * from tb10;

COL1 COL2 COL3

---------- ---------- ----------

11 1 1

12 4 4

可以复制 这里再次强调update默认是compressupdates的，日志只记录修改列和键值列）

logdump看trial文件，前2条未写trail文件。只有第三条写了

Logdump 3 >open dirdat/ss000000023

Current LogTrail is /u01/app/oggs/dirdat/ss000000023

Logdump 4 >ghdr on

Logdump 5 >detail on

Logdump 6 >detail date

Logdump 7 >usertoken on

Logdump 8 >pos 0

Reading forward from RBA 0

Logdump 9 >n

2018/03/13 11:38:36.000.000 Insert Len 28 RBA 1980

Name: SENDER.TB10 (TDR Index: 1)

After Image: Partition 12 G b

0000 0006 0000 0002 3131 0001 0005 0000 0001 3100 | ........11........1.

0200 0500 0000 0131 | .......1

Column 0 (x0000), Len 6 (x0006)

0000 0002 3131 | ....11

Column 1 (x0001), Len 5 (x0005)

0000 0001 31 | ....1

Column 2 (x0002), Len 5 (x0005)

0000 0001 31 | ....1

Logdump 15 >n

___________________________________________________________________

Hdr-Ind : E (x45) Partition : . (x0c)

UndoFlag : . (x00) BeforeAfter: A (x41)

RecLength : 28 (x001c) IO Time : 2018/03/13 11:38:36.000.000

IOType : 5 (x05) OrigNode : 255 (xff)

TransInd : . (x02) FormatType : R (x52)

SyskeyLen : 0 (x00) Incomplete : . (x00)

AuditRBA : 63 AuditPos : 34433040

Continued : N (x00) RecCount : 1 (x01)

2018/03/13 11:38:36.000.000 Insert Len 28 RBA 2112

Name: SENDER.TB10 (TDR Index: 1)

After Image: Partition 12 G e

0000 0006 0000 0002 3132 0001 0005 0000 0001 3100 | ........12........1.

0200 0500 0000 0131 | .......1

Column 0 (x0000), Len 6 (x0006)

0000 0002 3132 | ....12

Column 1 (x0001), Len 5 (x0005)

0000 0001 31 | ....1

Column 2 (x0002), Len 5 (x0005)

0000 0001 31 | ....1

---------------------------------------------------------------------------------------------------------------

以上trail 显示的insert 的两条记录

2018/03/13 11:50:44.000.000 GGSUnified Update Len 60 RBA 2220

Name: SENDER.TB10 (TDR Index: 1)

After Image: Partition 12 G s

0000 001c 0000 0006 0000 0002 3132 0001 0005 0000 | ............12......

0001 3300 0200 0500 0000 0131 0000 0006 0000 0002 | ..3........1........

3132 0001 0005 0000 0001 3400 0200 0500 0000 0134 | 12........4........4

Before Image Len 32 (x00000020)

BeforeColumnLen 28 (x0000001c)

Column 0 (x0000), Len 6 (x0006)

0000 0002 3132 | .... 12

Column 1 (x0001), Len 5 (x0005)

0000 0001 33 | .... 3

Column 2 (x0002), Len 5 (x0005)

0000 0001 31 | ... .1

After Image Len 28 (x0000001c)

Column 0 (x0000), Len 6 (x0006)

0000 0002 3132 | .... 12

Column 1 (x0001), Len 5 (x0005)

0000 0001 34 | .... 4

Column 2 (x0002), Len 5 (x0005)

0000 0001 34 | .... 4

---------------------------------------------------------------------------------------------------------------------------

以上是第三条update的trail

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31485142/viewspace-2151796/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31485142/viewspace-2151796/