Shulou(Shulou.com)05/31 Report--

How to conduct audit in OGG, in view of this problem, this article introduces the corresponding analysis and answer in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Source table:

Create table TB11 (id number primary key,name varchar (10))

Target table:

Create table TB11 (id number primary key,name varchar (10), src_time date)

Create table TB11_audit (id number,name varchar (10), src_time date, trg_time date,op_type varchar2 (20), src_user varchar2 (10))

Source side:

GGSCI (pc6) 20 > edit params ext_s1

Extract ext_s1

Setenv (NLS_LANG='AMERICAN_AMERICA.AL32UTF8')

Setenv (ORACLE_SID='hyyk')

Userid ogg,password oracle

Gettruncates

Exttrail / u01/app/oggs/dirdat/ss

Table sender.tb11, TOKENS (TKN-USERNAME=@getenv ('TRANSACTION','CSN'))

-- Note: use TOKEN in the source extract process to define a variable in the user TOKEN section of the header of the trail file:

TKN-USERNAME, the value of this variable is to obtain the user information of the source-side database commit transaction related to the database transaction in the current Goldengate runtime environment through @ GETENV

Destination replicat process (for timestamping):

GGSCI (ogg-80 as ogg@ogg) 144 > view params rep_s1

Replicat rep_s1

Setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

Setenv (ORACLE_SID='ogg')

Userid ogg,password oracle

-- handlecollisions

ASSUMETARGETDEFS

-- SOURCEDEFS / u01/app/oggd/dirdef/test.def

DISCARDFILE. / dirrpt/rorabb.dsc,PURGE

Userid ogg,password oracle

Map sender.tb11,target receiver.tb11

COLMAP (USEDEFAULTS

SRC_TIME = @ GETENV ('GGHEADER','COMMITTIMESTAMP'))

Note: at this time, the target has two replicat processes, and the two processes read the same trail file. Here is the first process: ggs_rep1, which is responsible for normal data replication.

That is, copy the TB11 table from the source side to the target TB11 table, except that there is an additional field SRC_TIME on the target TB11 table (record the transaction commit time on the source side) because one more word is added.

Section, it's time to call COLMAP to do the mapping, using the @ GETENV function in the COLMAP sentence, which is configured with the parameter COMMITTIMESTAMP under the GGHEADER category.

Property, and this property is used to obtain the timestamp, that is, to record the time the transaction was committed, in the format YYY-MM-DD HH:MI:SS.FFFFFF.

Target replicat process (for audit):

REPLICAT ggs_rep2

USERID ogg, PASSWORD oracle

ASSUMETARGETDEFS

INSERTALLRECORDS

DISCARDFILE. / dirrpt/ggs_rep2.dsc, APPEND, MEGABYTES 1024

WILDCARDRESOLVE DYNAMIC

DYNAMICRESOLUTION

MAP SENDER.TB11, TARGET RECEVIER.TB11_AUDIT

COLMAP (USEDEFAULTS

SRC_TIME = @ GETENV ('GGHEADER','COMMITTIMESTAMP')

TRG_TIME = @ DATENOW ()

OP_TYPE = @ GETENV ('GGHEADER',' OPTYPE')

SRC_USER = @ TOKEN ('TKN-USERNAME'))

TRG_TIME = @ DATENOW ()-- DATENOW returns the current date and time

Add replicat ggs_rep2,exttrail / u01/app/oggd/dirdat/sd,checkpointtable ogg.ckpt

Note: this is the second replicat process of the target, which is responsible for the replication of the audit table, that is, from the source-side TB11 table to the target audit table TB11_AUDIT. You have also seen the contents of the requirements earlier.

There are four more fields in the audit table, which are used to record the time of each transaction operation on the source side, the commit time of the target side, the operation type of the source side, and the user name of the source side database. The process parameter file uses the

A very important parameter: INSERTALLRECORDS, which allows the replication process to insert all changes to a record on the target side, that is, to audit. At the same time, in this

In the program, use the @ GETENV function to get the COMMITTIMESTAMP (source time) and OPTYPE (operation type) under GGHEADER in the goldengate runtime environment; use @ DATENOW () to get the

Take the submission time on the target side; use the @ TOKEN function to obtain the value of the variable TKN-USERNAME defined in the user token section defined in the header of the trail file on the source side, that is, the database user information on the source side.

The following sql is executed on the source side:

SQL > insert into TB11 values (1)

SQL > commit

SQL > insert into TB11 values (2)

SQL > commit

SQL > update TB11 set name='c' where id=2

SQL > commit

SQL > delete from TB11 where id=2

SQL > commit

SQL > update TB11 set id=2 where id=1

SQL > commit

After completion, the source table query record is as follows:

SENDER@hyyk > select * from tb11

ID NAME

--

2 a

The results of the query from the target table are as follows:

RECEIVER@ogg > select * from tb11

ID NAME SRC_TIME

2 a 2018-03-16 11:41:34

SQL > set linesize 200

RECEIVER@ogg > select * from TB11_AUDIT

ID NAME SRC_TIME TRG_TIME OP_TYPE SRC_USER

--

1 a 2018-03-16 11:05:07 2018-03-16 11:27:51 INSERT 1849360

2 a 2018-03-16 11:31:00 2018-03-16 11:31:06 INSERT 1850347

C 2018-03-16 11:39:46 2018-03-16 11:39:52 SQL COMPUPDATE 1850679

2 c 2018-03-16 11:41:11 2018-03-16 11:41:19 DELETE 1850749

2 2018-03-16 11:41:34 2018-03-16 11:41:43 Competition UPDATE 1850766

Note: the data in the TB11 table of the target side is consistent with that of the source side, except that one more column indicates the last transaction commit time. The target table TB11_AUDIT is responsible for auditing all the operations of the source table, in which the last two rows of name fields are not filled with values.

Because there is a primary key on the TB11 table, for delete operations and key value update operations, there is only key value column-related information in the log. If you need to display other field information, you need to add these fields to the additional log. At the same time, OP_TYPE

There are two values in the column related to the update operation, "SQL COMPUPDATE" and "competitive UPDATE", which represent the update of the non-key column and the update of the key column, respectively.

Here, compare the columns of the audit table with the tiail from logdump

ID NAME SRC_TIME TRG_TIME OP_TYPE SRC_USER

--

1 a 2018-03-16 11:05:07 2018-03-16 11:27:51 INSERT 1849360

2018-03-16 11:05:07.932.429 Insert Len 18 RBA 1972

Name: SENDER.TB11 (TDR Index: 1)

After Image: Partition 12 GU s

0000 0005 0000 0001 3100 0100 0500 0000 0161 | .1.a

Column 0 (x0000), Len 5 (x0005)

0000 0001 31 |. 1

Column 1 (x0001), Len 5 (x0005)

0000 0001 61 |. A

User tokens: 21 bytes

544b 4e2d 5553 4552 4e41 4d45 0031 3834 3933 3630 | TKN-USERNAME.1849360

00 |.

This is the end of the answer to the audit question in OGG. I hope the above content can be of some help to you. If you still have a lot of doubts to solve, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.