Skip to Main Content

GoldenGate

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Writing Deleted records to separate tables or adding an audit column in target table

LakshmideepakJun 27 2022 — edited Jun 27 2022

Hi,
What is the best practice to capture deleted records when replicating using GoldenGate.

Using an audit column in the target and using INSERTDELETES with sysdate/default value for the audit column

INSERTDELETES
MAP sogg.sample_data_1, TARGET stgg.sample_data_1, &
COLMAP (USEDEFAULTS, DATE_DELETED = @DATENOW())

Inserting deleted records to a separate table and using ALLOWDUPTARGETMAP parameter

IGNOREINSERTS
IGNOREUPDATES
INSERTDELETES
MAP sogg.sample_data_1, TARGET stgg.sample_data_1_deleted

Thanks !

Comments

SH_INT
Answer

Why won't your finance team accept the approach of logic accounts? This is the obvious solution to your problem. In FDM or FDMEE you can only load on piece of data to one target intersection. If you want to load the same piece of data to multiple target intersections then you need to have that piece of data repeated in the source data nad then you will still need something in the source that could uniquely identify each of those copies so you could map appropriately. As mentioned that is one of the primary functions of logic accounts, other approaches are likely to be messy, more prone to error and a lot more difficult to audit.

Marked as Answer by MarkSmithHFM · Sep 27 2020
MarkSmithHFM

Hi SH,

Thanks for your reply.

I agree with you 100% that logic accounts are the way to go here.  I was just trying to find out if there were any other approaches that would be relatively straightforward and easy to implement.

Other approaches would definitely not be elegant and would involve scripting and probably rewriting file or copying data records via scripts which to me are non-starters.

You reinforced my opinion.   I'll develop my solution using logic accounts.

Thanks,
Mark

1 - 2

Post Details

Added on Jun 27 2022
1 comment
163 views