• Arun Kumar

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


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

495 views

Recent Posts

See All

Install Oracle 12c Golden Gate on Oracle Linux

In this article we will look at the steps to install oracle 12c Golden Gate on Oracle Enterprise Linux 6.5. The steps involved are: Virtual Machine Setup Install Oracle 12c Database Install Oracle 12c

Single Table Replication – Same Schema

In this article, we will be looking at single table replication using Oracle Golden Gate where the schema name is the same on both source and target database. Create Sample Table Test Replication Stop

Single Table Replication – Different Schema

In this article we will be looking at single table replication using Oracle Golden Gate where the schema name is different on source and target but table structure remains same. Create Sample Table Te