ORACLE GOLDENGATE INSERTALLRECORDS – To Maintain Transaction History

This parameter is used at the Replicat Side. When using this parameter, it causes Replicat process to insert each and every change that has been made to a record as a new record in the database. So whatever operations are performed in the source table (Insert or Delete or Update), everything will be converted in to the insert statement and will be inserted to the target table. The initial insert and subsequent updates and deletes are maintained as point-in-time snapshots.

There are two major reasons for using the parameter INSERTALLRECORDS,

1. When exception MAP statement is used in the target. The operations which causes error will be inserted in the Exception table. This is called as Error Handling.

2. When you need to maintain a transaction history table. So all the changes (deletes / updates) which are done in the source table will be inserted in the target table. Along with the regular columns, you can also add few more columns to record the changes like Operation Time, Commit Timestamp, Operation Type etc. These values will be retrieved from the Trail File Header. Combining historical data with special transaction information provides a way to create a more useful target reporting database. This is also used for the Auditing purposes.

Now let us see how the parameter INSERTALLRECORDS works.

I have a table SCOTT.EMP on both source and target sides but with some additional columns in the target like OP_TYPE (Operation Type) and OP_TIME (Operation Time). The structure of the table can be seen below,

Source Table

SQL> desc scott.emp

Name                  Null?         Type
-----------------     ------------  --------------------
EMP_ID                NOT NULL      NUMBER(2)
EMP_NAME                            VARCHAR2(15)
SALARY                              NUMBER(5)

Target Table

SQL> desc scott.emp

Name                  Null?         Type
-----------------     ------------  --------------------
EMP_ID                NOT NULL      NUMBER(2)
EMP_NAME                            VARCHAR2(15)
SALARY                              NUMBER(5)
OP_TYPE                             VARCHAR2(20)
OP_TIME                             DATE

Pre-Requisites for maintaining the History of transactions

1. All the constraints including the Primary Key constraint should be disabled at the Target table.

2. Supplemental Logging should be enabled on all the columns.

On the source side, supplemental logging should be enabled for all the columns for the table SCOTT.EMP. The command to add supplemental log as below,

ALTER TABLE SCOTT.EMP ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Here I had already enabled supplemental log for all the columns for the table SCOTT.EMP.

Login to the ggsci prompt and check the trandata information.,

GGSCI (OGG1.localdomain) 23> info trandata scott.emp

Logging of supplemental redo log data is enabled for table SCOTT.EMP.

Columns supplementally logged for table SCOTT.EMP: ALL.

Please find the parameters of the Extract, Pump and Replicat processes.,

GGSCI (OGG1.localdomain) 24> VIEW PARAMS EXT1

EXTRACT EXT1
USERID ggadmin, PASSWORD oracle
EXTTRAIL /vol3/ogg/dirdat/et
NOCOMPRESSDELETES
TABLE scott.emp;

GGSCI (OGG1.localdomain) 25> VIEW PARAMS DMP1

EXTRACT DMP1
PASSTHRU
RMTHOST OGG2, MGRPORT 7979
RMTTRAIL /vol3/ogg/dirdat/et
TABLE scott.emp;

REPLICAT REP1
USERID ggadmin, PASSWORD oracle
ASSUMETARGETDEFS
INSERTALLRECORDS
DISCARDFILE /vol3/ogg/dirrpt/REP1.DSC, PURGE
MAP scott.emp, TARGET scott.emp, COLMAP (OP_Type = @GETENV ('GGHEADER','OPTYPE'),OP_Time = @DATENOW());

INSERTALLRECORDS      is the parameter used to insert all the records independent of the operation type it receives. It means, whether the operation might delete or update, but using this parameter will
convert it to the Insert operation and inserts the record in to the table.

In the above Replicat MAP statement, you could see that I have used the COLMAP clause and functions like @GETENV and @DATENOW()

COLMAP clause is used to map the columns between source and target.

@GETENV retreives the values from the GoldenGate Trail File header.

@DATENOW to return the current date and time in the format YYYY-MM-DD HH:MI:SS. The date and time are returned in local time, including adjustments for Daylight Saving Time.

As I mentioned earlier, disable the Primary Key constraint on the target table.

SQL> select constraint_name,constraint_type from dba_constraints where table_name='EMP' and owner='SCOTT';

CONSTRAINT_NAME    C   STATUS
------------------ -   --------
SYS_C0012044       P   DISABLED

SQL> alter table scott.emp disable constraint SYS_C0012044;

Table altered.

SQL> select constraint_name,constraint_type,status from dba_constraints where table_name='EMP' and owner='SCOTT';

CONSTRAINT_NAME    C   STATUS
------------------ -   --------
SYS_C0012044       P   DISABLED

Now all the Pre-requisites are done. The Oracle GoldenGate processes are already started and let us start the test and check how the parameter INSERTALLRECORDS behave,

On the Source Side, I am going to insert a row

Source Side

SQL> insert into scott.emp values(1,'veera',5000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scott.emp;

 EMP_ID    EMP_NAME        SALARY
---------- --------------- ----------
 1         veera           5000

You could see a row is inserted in the source table scott.emp. Let us check it on the Target side,

SQL> select * from scott.emp;

 EMP_ID    EMP_NAME        SALARY     OP_TYPE    OP_TIME
---------- --------------- ---------- ---------  ---------------------
 1         veera           5000       INSERT     2015-12-21 20:54:08

Now I am inserting the second row in the source table scott.emp

SQL> insert into scott.emp values (2,'mannoj',5000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scott.emp;

 EMP_ID    EMP_NAME        SALARY
---------- --------------- ----------
 1         veera           5000
 2         mannoj          5000

On the Target Side

SQL> select * from scott.emp;

 EMP_ID    EMP_NAME        SALARY     OP_TYPE    OP_TIME
---------- --------------- ---------- ---------  ---------------------
 1         veera           5000       INSERT     2015-12-21 20:54:08
 2         mannoj          5000       INSERT     2015-12-21 20:59:40

Let us check how delete operation is handled. Now we are going to delete a row in the Source table,

SQL> delete from scott.emp where emp_id=1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from scott.emp;

 EMP_ID    EMP_NAME        SALARY
---------- --------------- ----------
 2         mannoj          5000

On the Target Side

SQL> select * from scott.emp;

 EMP_ID    EMP_NAME        SALARY     OP_TYPE    OP_TIME
---------- --------------- ---------- ---------  ---------------------
 1         veera           5000       DELETE     2015-12-21 21:01:47
 1         veera           5000       INSERT     2015-12-21 20:54:08
 2         mannoj          5000       INSERT     2015-12-21 20:59:40

In the above output, you can see the Operation Type as DELETE and the Time of Operation captured in the column OP_TIME.

Let us check how the Primary Key Update is handled by this parameter,

Source Side

SQL> update scott.emp set emp_id=1 where emp_id=2;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from scott.emp;

 EMP_ID    EMP_NAME        SALARY
---------- --------------- ----------
 1         mannoj          5000

On the Target Side

SQL> select * from scott.emp;

 EMP_ID    EMP_NAME        SALARY     OP_TYPE    OP_TIME
---------- --------------- ---------- ---------  ---------------------
 1         veera           5000       DELETE     2015-12-21 21:01:47
 1         mannoj          5000       PK UPDATE  2015-12-21 21:03:23
 1         veera           5000       INSERT     2015-12-21 20:54:08
 2         mannoj          5000       INSERT     2015-12-21 20:59:40

From the above output, you can see the OP_TYPE as PK UPDATE. I had updated the Primary Key column in the Source and this has been replicated to the
target.

Finally let us do a Normal Update operation and check what is the Operation Type it records,

SQL> select * from scott.emp;

 EMP_ID    EMP_NAME        SALARY
---------- --------------- ----------
 1         mannoj          5000

SQL> update scott.emp set emp_name='VEERA' where emp_id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from scott.emp;

 EMP_ID    EMP_NAME        SALARY
---------- --------------- ----------
 1         VEERA           5000

On the Target Side

SQL> select * from scott.emp;

 EMP_ID    EMP_NAME        SALARY     OP_TYPE         OP_TIME
---------- --------------- ---------- ---------       ---------------------
 1         veera           5000       DELETE          2015-12-21 21:01:47
 1         mannoj          5000       PK UPDATE       2015-12-21 21:03:23
 1         VEERA           5000       SQL COMPUPDATE  2015-12-21 21:04:39
 1         veera           5000       INSERT          2015-12-21 20:54:08
 2         mannoj          5000       INSERT          2015-12-21 20:59:40

Here the Operation Type is “SQL COMPUPDATE“.

Hope this clearly explains about the usage of the Oracle GoldenGate parameter INSERTALLRECORDS and how to maintain the history of the transactions on the Target Side.

Thanks for your support 🙂