Maintain Source Table Transaction History Using Golden Gate

Sometimes you want to have a history table that can store all the transactions performed on a source table. This kind of history tables will help you in the future for auditing purpose.

• INSERTALLRECORDS Parameter

• Requirements

• Setup Golden Gate

INSERTALLRECORDS Parameter

Valid for: Replicat

This parameter is used by Replicat to insert every change that has been made on a source record into target database. Every insert, update, delete on source will be converted to an insert statement on target. You can add timestamp on target table to get history details as to when changes were made in source table.

Importance of using History tables

• To maintain transaction history of source table

• To create a more robust reporting database – You get control on every transaction

• Also used for auditing purpose

Requirements

• Disable all key columns including Primary Key on target table

• Supplemental logging must be enabled on all the columns

Setup Golden Gate

Let us create one table in source as fox user

On proddb:
==========
SQL> create table fox.emp
(
EMP_ID number primary key,
EMP_Name varchar2(20),
Salary Number
);

As sys user on source, enable supplemental column logging for all columns on fox.emp

SQL> ALTER TABLE fox.emp add supplemental log data (ALL) columns;
Table altered.

On target, create below table as tom user

On devdb:
=========
SQL> create table tom.emp_trnx_hist
(
EMP_ID number primary key,
EMP_Name varchar2(20),
Salary number,
Trnx_Type varchar2(20)
Trnx_time Date
);

On target, we need to disable the key constraint as per the requirements

SQL> select constraint_name,constraint_type, status from user_constraints where table_name='EMP_TRNX_HIST';

CONSTRAINT_NAME C STATUS
--------------- - --------
SYS_C0010106    P ENABLED

SQL> alter table EMP_TRNX_HIST disable constraint SYS_C0010106;
Table altered.

SQL> select constraint_name,constraint_type, status from user_constraints where table_name='EMP_TRNX_HIST';

CONSTRAINT_NAME C STATUS
--------------- - --------
SYS_C0010106    P DISABLED

Below are the parameters used of Extract

GGSCI> add trandata fox.emp
Logging of supplemental redo data enabled for table FOX.EMP.
TRANDATA for scheduling columns has been added on table 'FOX.EMP'.
GGSCI> add extract EXT11, integrated tranlog, begin now
GGSCI> register extract EXT11 database
GGSCI> add exttrail ./dirdat/el, extract EXT11
GGSCI> EDIT PARAMS EXT11
EXTRACT EXT11
USERID ogg, PASSWORD ogg
EXTTRAIL ./dirdat/el
TABLE fox.emp;

Below are the parameters used for pump

GGSCI> Add extract DMP11, EXTTRAILSOURCE ./dirdat/el
GGSCI> Add rmttrail /u01/app/oracle/product/gg/dirdat/er, extract DMP11
GGSCI> EDIT PARAMS DMP11
EXTRACT DMP11
USERID ogg, PASSWORD ogg
RMTHOST ggdev, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/er
TABLE fox.emp;

Below are the parameters used for replicat

On Devdb:
==========
GGSCI> dblogin userid ogg, password ogg
GGSCI> add replicat REP11, integrated exttrail /u01/app/oracle/product/gg/dirdat/er
GGSCI> edit param REP11
REPLICAT REP11
USERID ogg, PASSWORD ogg
ASSUMETARGETDEFS
INSERTALLRECORDS
MAP fox.emp, TARGET tom.emp_trnx_hist, &
COLMAP (USEDEFAULTS, Trnx_type = @GETENV('GGHEADER','OPTYPE'), Trnx_Time = @DATENOW());

Note: We are using below parameter here: @GETENV retrieves the values from the Golden Gate Trail File header. @DATENOW returns the system time when the operation is executed.

Let us insert some rows in source EMP table

insert into fox.emp values(1,'John',5000);
insert into fox.emp values(2,'Dan',7000);
insert into fox.emp values(3,'Lee',10000);
commit;

On target you will see

Let us update one row from source

SQL> update emp set salary=15000 where emp_id=2;
SQL> commit;

On target you will see SQL COMPUPDATE

Let us delete one record from source

SQL> delete from emp where emp_id=3;
SQL> commit;

On target, you will see

Note: In order to track transactions on a particular record, we can user sorting on target table. For example, I want to know all the transactions executed on EMP_ID 3.

Further, by using golden gate functions, you can even capture the before delete image of transactions OR you can even capture before and after images of updates as well