Oracle GoldenGates flexible architecture provides built-in functionality for the transformation of captured transactional data. This data manipulation may take place anywhere in the GoldenGate stream; but is most commonly performed in the Replicat during data apply to the target. Transaction history tables provide customers an audit trail of activity on production tables for regulatory purposes.
A Typical History Table
The typical history table maintains a record of all data changes made to the source production table. Varying levels of information may be gathered and recorded. We have a regulatory requirement to maintain a history off all changes to the production table that includes the type of database operation executed, the date and time the change was made, and a snapshot of the row before the change occurred.
Table Schema
To demonstrate this concept, we shall be using two test environments: Test1 is the simulated production GoldenGate source and Test2 is the GoldenGate target. A very simple source table will be used:
CREATE TABLE Test1.BEFOREAFTER (
COLNMBR NUMBER(11),
COLCH VARCHAR2(20),
COLTS TIMESTAMP(6)
);
Note that the above table does not contain primary keys, or any indexes. For update operations, GoldenGate Extract will capture all columns for the modified row and Replicat shall use all of the columns when building the update where clause. This needs to be taken into consideration when configuring the Replicat.
For our target table, we need to add additional columns not present in the source:
CREATE TABLE Test2.BEFOREAFTER (
COLNMBR NUMBER(11),
COLCH VARCHAR2(20),
COLTS TIMESTAMP(6),
DB_OP_TYPE VARCHAR2(20),
SRC_COMMIT_TS TIMESTAMP(6),
BFR_AFT_IND VARCHAR2(30)
);
Configure Source GoldenGate Groups
On our source database server (Test1), we need to create a Change Data Capture Extract that reads the transaction logs, and an Extract Data Pump to move the captured data to the target database server.
The Change Data Capture Extract configuration is:
— Demonstration extract. Capture data used to build target history table.
EXTRACT eb4aftr
— For updates, capture the row before image and write it to the trail
GETUPDATEBEFORES
USERID gguser, PASSWORD Oracle1
EXTTRAIL ./dirdat/eb
TABLE Test1.beforeafter;
The Extract Data Pump configuration is:
— Extract Data Pump to move captured data to Test2 server
EXTRACT pb4aftr RMTHOST Test2, MGRPORT 15500, COMPRESS R
MTTRAIL ./dirdat/pb
PASSTHRU
TABLE Test1.*;
Configure Target GoldenGate Groups
On the target server (Test2), we configure a Change Data Apply Replicat that reads the GoldenGate Trail transmitted via the Extract Data Pump, builds SQL DML statements, and submits the DML to the database.
The Replicat configuration is:
REPLICAT rb4aftr
USERID gguser, PASSWORD Oracle1
— Because the source and target tables are different, we need to use source
— table defines generated by DEFGEN.
SOURCEDEFS ./dirdef/b4aftr.defs
DISCARDFILE ./dirrpt/rb4aftr.dsc , PURGE
— Apply the before images
GETUPDATEBEFORES
— Map the source and target table. For the target table:
— INSERTALLRECORDS: No matter the source operation, turn it into an insert.
— COLMAP: Do specific column mapping.
— USEDEFAULTS: If the target column is the same as the source
— (data type and name), automatically map it.
MAP Test1.beforeafter, TARGET Test2.beforeafter,
INSERTALLRECORDS,
COLMAP ( USEDEFAULTS,
— Map columns in the target that do no exist in the source.
— from the Replicat’s operating environment:
— (a) The source operation from the GoldenGate Trail Header
— (b) The source transaction commit timestamp from the GoldenGate trail header.
— (c) The BEFORE or AFTER image indicator from the GoldenGate trail header.
db_op_type = @GETENV (“GGHEADER”, “OPTYPE”),
src_commit_ts = @GETENV (“GGHEADER”,”COMMITTIMESTAMP”),
bfr_aft_ind = @GETENV (“GGHEADER”,”BEFOREAFTERINDICATOR”)
);
Generate Data and Validate
After stating the GoldenGate groups, generate source transactions.
Insert Operations at Source
SQL> insert into beforeafter values (1, ‘Row 1 insert’, current_timestamp);
1 row created.
SQL> insert into beforeafter values (2, ‘Row 2 insert’, current_timestamp);
1 row created.
SQL> insert into beforeafter values (3, ‘Row 3 insert’, current_timestamp);
1 row created.
SQL> insert into beforeafter values (4, ‘Row 4 insert’, current_timestamp);
1 row created.
SQL> insert into beforeafter values (5, ‘Row 5 insert’, current_timestamp);
1 row created.
SQL> commit;
Commit complete.
Results at Target
SQL> select colnmbr, colch, colts, db_op_type, bfr_aft_ind from beforeafter;
| COLNMBR | COLCH | COLTS | DB_OP_TYPE | BFR_AFT_IND |
| 1 | Row 1 insert | 27-MAR-14 11.44.58.820840 AM | INSERT | AFTER |
| 2 | Row 2 insert | 27-MAR-14 11.45.21.403243 AM | INSERT | AFTER |
| 3 | Row 3 insert | 27-MAR-14 11.45.46.846418 AM | INSERT | AFTER |
| 4 | Row 4 insert | 27-MAR-14 11.46.20.025874 AM | INSERT | AFTER |
| 5 | Row 5 insert | 27-MAR-14 11.46.43.672204 AM | INSERT | AFTER |
Update Operations at Source
SQL> update beforeafter set colch = ‘Line 2 Update’, colts = current_timestamp where colnmbr = 2;
1 row updated.
SQL> commit;
Commit complete.
SQL> update beforeafter set colch = ‘Line 2 update again’, colts = current_timestamp where colnmbr = 2;
1 row updated.
SQL> update beforeafter set colch = ‘Line 5 update’, colts = current_timestamp where colnmbr = 5;
1 row updated.
SQL> commit;
Commit complete.
Results at Target
SQL> select colnmbr, colch, colts, db_op_type, bfr_aft_ind from beforeafter;
| COLNMBR | COLCH | COLTS | DB_OP_TYPE | BFR_AFT_IND |
| 1 | Row 1 insert | 27-MAR-14 11.44.58.820840 AM | INSERT | AFTER |
| 2 | Row 2 insert | 27-MAR-14 11.45.21.403243 AM | INSERT | AFTER |
| 3 | Row 3 insert | 27-MAR-14 11.45.46.846418 AM | INSERT | AFTER |
| 4 | Row 4 insert | 27-MAR-14 11.46.20.025874 AM | INSERT | AFTER |
| 5 | Row 5 insert | 27-MAR-14 11.46.43.672204 AM | INSERT | AFTER |
| 2 | Row 2 insert | 27-MAR-14 11.45.21.403243 AM | SQL COMPUPDATE | BEFORE |
| 2 | Line 2 Update | 27-MAR-14 12.37.05.112332 PM | PK UPDATE | AFTER |
| 2 | Line 2 Update | 27-MAR-14 12.37.05.112332 PM | SQL COMPUPDATE | BEFORE |
| 2 | Line 2 update again | 27-MAR-14 12.37.44.761266 PM | PK UPDATE | AFTER |
| 5 | Row 5 insert | 27-MAR-14 11.46.43.672204 AM | SQL COMPUPDATE | BEFORE |
| 5 | Line 5 update | 27-MAR-14 12.38.16.976272 PM | PK UPDATE | AFTER |
Mixed Workload Operations at Source
SQL> insert into beforeafter values (6, ‘Row 6 Insert’, current_timestamp);
1 row created.
SQL> insert into beforeafter values (7, ‘Row 7 Insert’, current_timestamp);
1 row created.
SQL> update beforeafter set colch = ‘Line 4 update’, colts = current_timestamp where colnmbr = 4;
1 row updated.
SQL> insert into beforeafter values (8, ‘Row 8 insert’, current_timestamp);
1 row created.
SQL> delete from beforeafter where colnmbr < 4;
3 rows deleted.
SQL> commit;
Commit complete.
Results at Target
SQL> select colnmbr, colch, colts, db_op_type, bfr_aft_ind from beforeafter;
| COLNMBR | COLCH | COLTS | DB_OP_TYPE | BFR_AFT_IND |
| 1 | Row 1 insert | 27-MAR-14 11.44.58.820840 AM | INSERT | AFTER |
| 2 | Row 2 insert | 27-MAR-14 11.45.21.403243 AM | INSERT | AFTER |
| 3 | Row 3 insert | 27-MAR-14 11.45.46.846418 AM | INSERT | AFTER |
| 4 | Row 4 insert | 27-MAR-14 11.46.20.025874 AM | INSERT | AFTER |
| 5 | Row 5 insert | 27-MAR-14 11.46.43.672204 AM | INSERT | AFTER |
| 2 | Row 2 insert | 27-MAR-14 11.45.21.403243 AM | SQL COMPUPDATE | BEFORE |
| 2 | Line 2 Update | 27-MAR-14 12.37.05.112332 PM | PK UPDATE | AFTER |
| 2 | Line 2 Update | 27-MAR-14 12.37.05.112332 PM | SQL COMPUPDATE | BEFORE |
| 2 | Line 2 update again | 27-MAR-14 12.37.44.761266 PM | PK UPDATE | AFTER |
| 5 | Row 5 insert | 27-MAR-14 11.46.43.672204 AM | SQL COMPUPDATE | BEFORE |
| 5 | Line 5 update | 27-MAR-14 12.38.16.976272 PM | PK UPDATE | AFTER |
| 6 | Row 6 Insert | 27-MAR-14 01.14.45.055846 PM | INSERT | AFTER |
| 7 | Row 7 Insert | 27-MAR-14 01.15.07.283838 PM | INSERT | AFTER |
| 4 | Row 4 insert | 27-MAR-14 11.46.20.025874 AM | SQL COMPUPDATE | BEFORE |
| 4 | Line 4 update | 27-MAR-14 01.16.18.179970 PM | PK UPDATE | AFTER |
| 8 | Row 8 insert | 27-MAR-14 01.16.48.746611 PM | INSERT | AFTER |
| 1 | Row 1 insert | 27-MAR-14 11.44.58.820840 AM | DELETE | BEFORE |
| 2 | Line 2 update again | 27-MAR-14 12.37.44.761266 PM | DELETE | BEFORE |
| 3 | Row 3 insert | 27-MAR-14 11.45.46.846418 AM | DELETE | BEFORE |
Maintaining A Modification History
We have a requirement to maintain a snapshot of the last row modifications; for update operations record the before and after image of the row only, and for deletes record the before image only.
Table Schema
We shall be using the same schema created in the previous section.
Configure Source GoldenGate Groups
No changes are required for the Change Data Capture Extract and Extract Data Pump; in fact, we will not be using them. Instead we shall replay data queued in the Extract Trail being read by Replicat.
Configure Target GoldenGate Groups
Several changes need to be made to Replicat. The new Replicat configuration is:
REPLICAT rb4aftr
USERID gguser, PASSWORD Oracle1
SOURCEDEFS ./dirdef/b4aftr.defs
DISCARDFILE ./dirrpt/rb4aftr.dsc, purge
— Allow multiple map statements for the same source and target tables
ALLOWDUPTARGETMAP
— For all MAP statements that follow, ignore source delete records
IGNOREDELETES
— This map statement will perform insert and update operations
— only. Use the target columns COLNMBR and COLTS are pseudo
— primary keys.
MAP Test1.beforeafter, TARGET Test2.beforeafter,
KEYCOLS (COLNMBR, COLTS),
COLMAP (USEDEFAULTS,
db_op_type = @GETENV (“GGHEADER”, “OPTYPE”),
src_csn = @GETENV (“TRANSACTION”,”CSN”),
src_tranid = @GETENV (“TRANSACTION”,”TRANSACTIONID”),
src_commit_ts = @GETENV (“GGHEADER”,”COMMITTIMESTAMP”),
bfr_aft_ind = @GETENV (“GGHEADER”,”BEFOREAFTERINDICATOR”)
);
— For all MAP statements that follow: (a) ignore source delete records (still
— in affect from above), (b) ignore source insert records, (c) ignore source
— update after image records, and (d) process update before image records.
IGNOREINSERTS
IGNOREUPDATEAFTERS
GETUPDATEBEFORES
MAP Test1.beforeafter, TARGET Test2.beforeafter,
— No matter the source operation, do an insert on the target
INSERTALLRECORDS,
COLMAP (USEDEFAULTS,
db_op_type = @GETENV (“GGHEADER”, “OPTYPE”),
src_csn = @GETENV (“TRANSACTION”,”CSN”),
src_tranid = @GETENV (“TRANSACTION”,”TRANSACTIONID”),
src_commit_ts = @GETENV (“GGHEADER”,”COMMITTIMESTAMP”),
bfr_aft_ind = @GETENV (“GGHEADER”,”BEFOREAFTERINDICATOR”)
);
— For all MAP statements that follow: (a) ignore source insert records (still in affect
— from above), (b) ignore source update after image records (still in affect from above),
— (c) ignore source update before image records, and (d) process source delete records.
IGNOREUPDATEBEFORES
GETDELETES
MAP Test1.beforeafter, TARGET Test2.beforeafter,
— Replicat will execute a DML statement on the target to delete all rows that match
— the value of the source column COLNMBR
SQLEXEC (ID delcolnmbr,
QUERY “delete from Test2.beforeafter where colnmbr = :cnmbr”,
PARAMS (cnmbr = colnmbr), DBOP),
— No matter the source operation, do an insert
INSERTALLRECORDS,
COLMAP (USEDEFAULTS,
db_op_type = @GETENV (“GGHEADER”, “OPTYPE”),
src_csn = @GETENV (“TRANSACTION”,”CSN”),
src_tranid = @GETENV (“TRANSACTION”,”TRANSACTIONID”),
src_commit_ts = @GETENV (“GGHEADER”,”COMMITTIMESTAMP”),
bfr_aft_ind = @GETENV (“GGHEADER”,”BEFOREAFTERINDICATOR”)
);
Replay Source Operations
We shall replay source operations already captured in our modified Replicat.
- With PL/SQL, truncate the target table
- In GGSCI
- STOP rb4aftr
- ALTER r4b4aftr, extseqno 0, extrba 0
- START rb4aftr
Results at Target
SQL> select colnmbr, colch, colts, db_op_type, bfr_aft_ind from beforeafter order by colnmbr, colts;
| COLNMBR | COLCH | COLTS | DB_OP_TYPE | BFR_AFT_IND |
| 1 | Row 1 insert | 27-MAR-14 11.44.58.820840 AM | DELETE | BEFORE |
| 2 | Line 2 update again | 27-MAR-14 12.37.44.761266 PM | DELETE | BEFORE |
| 3 | Row 3 insert | 27-MAR-14 11.45.46.846418 AM | DELETE | BEFORE |
| 4 | Row 4 insert | 27-MAR-14 11.46.20.025874 AM | SQL COMPUPDATE | BEFORE |
| 4 | Line 4 update | 27-MAR-14 01.16.18.179970 PM | PK UPDATE | AFTER |
| 5 | Row 5 insert | 27-MAR-14 11.46.43.672204 AM | SQL COMPUPDATE | BEFORE |
| 5 | Line 5 update | 27-MAR-14 12.38.16.976272 PM | PK UPDATE | AFTER |
| 6 | Row 6 Insert | 27-MAR-14 01.14.45.055846 PM | INSERT | AFTER |
| 7 | Row 7 Insert | 27-MAR-14 01.15.07.283838 PM | INSERT | INSERT |
| 8 | Row 8 insert | 27-MAR-14 01.16.48.746611 PM | INSERT | AFTER |