I am starting to investigate a similar need.
So far i have come up with the following REPLICAT parameters that will transform UPDATE and DELETE behavior. The documentation is sparse and I just set up an extract/replicat to see if it works for my needs, so far it seems a little funky. Here are the parameters, if I find out more I will follow up:
INSERTUPDATES - Use the INSERTUPDATES and NOINSERTUPDATES parameters to control whether or not GoldenGate converts uncompressed update operations to insert operations. The parameters are table-specific. One parameter remains in effect for all subsequent MAP statements, until the other parameter is encountered.
INSERTDELETES - Use the INSERTDELETES and NOINSERTDELETES parameters to control whether or not GoldenGate converts source delete operations to insert operations on the target database. The parameters are table-specific. One parameter remains in effect for all subsequent MAP
statements, until the other parameter is encountered.
I played with it a little today and had some success getting it to accumulate records on the target side after I made sure to ADD TRANDATA. Before I had added TRANDATA for the source table it would accumulate records but only with the changed fields.
Looking into the delete now, it does write a new record upon delete, but you may need to set a delete_flag or deleted_date or some logical delete in conjunction with the physical delete to single out the delete operation in the audit table.
The parameter that should help you is INSERTALLRECORDS. If you want the complete image of the record for auditing purposes or something like that, then you can enable supplemental logging for all columns and use the parameters GETUPDATEBEFORES, NOCOMPRESSUPDATES, NOCOMPRESSDELETES. This will force the entire before and after image of updates into the trails and the entire before image of records into the trails.
On the target, you'll get 1 record for inserts. 2 records for updates, one will be the before image, and one will be the after image, and then a single record for deletes.
Thanks a lot Nick_W and DMann99 for your prompt response.
I am having an primary key column ID on source table so in this case I should not have any primary key constraint on ID column at target.
Can we add columns on target to get any kind of incremental sequence no., time stamp like when the DML operation has been done on source & the type of operation performed?
our scope is not auditing but we need to keep track on target that what are the operations performed on particular record (ID) in a specific day and need to find the last updated value at the end of day.
I have also gone through the GGHEADER OPTYPE option but currently not so sure how to use it in this scenario as I am newbie in GoldenGate.
As per what understood,
1) Need to enable supplemental logging on all columns on source
2) use GETUPDATEBEFORES, NOCOMPRESSUPDATES, NOCOMPRESSDELETES options in extract.
3) use INSERTALLRECORDS option in replicat.
Please do guide.
Correct, you would disable the primary key on the target. Since the table will only contain inserts, the performance of Replicat would not be effected. you can add any additional columns you want, and you could have those populated via a trigger or through the use of the GETENV functions in GoldenGate if you wanted record, or transactional information to be attached. This would include things like the original user that modified the record on the source.
There is an example of this in this MOS note: Oracle GoldenGate Best Practice - Oracle GoldenGate for ETL Tools [ID 1371706.1]