Extract Generating huge trail files due to JAI_TAX_DET_FACTORS table

[kvmishra]

Hello Sir,

We are extracting before/after image of certain tables from Oracle EBS database using OGG in XML trail files  and collecting data in AVDF. We observed that there is a single table JAI_TAX_DET_FACTORS which is generating huge volumn for trail files around 80-100GB per day. Could you please help me to identify whether this usual or not even from EBS side?

We have created a seprate extract for the table as below:
EXTRACT EXT4
USERIDALIAS ggadminCDB DOMAIN OracleGoldenGate
OUTPUTFORMAT XML _AUDIT_VAULT
EXTTRAIL audit_trail/EXT4/tr
SOURCECATALOG E110P
IGNOREINSERTS
TABLE JA.JAI_TAX_DET_FACTORS , KEYCOLS (ENTITY_CODE,TRX_NUMBER,TRX_LINE_NUMBER,DET_FACTOR_ID);

Due to huge volumn of data, AVDF collection is lagging and storage is also getting full. If this will go as it is, it will be a mess to have that much storage in next few years.

Thanks in Advance.

[ErmanArslansOracleBlog]

That table is related with Oracle India Localization.
It is definitely not usual for a single table like JAI_TAX_DET_FACTORS to generate 80–100GB of trail files daily.

Check that table with queries and try to understand the update frequency? Speak with your functional admins..

btw, you are sending this to Audit Vault and Database Firewall (AVDF). Do you really need every single column for auditing purposes.. Check that and eliminate some of the overhead from there..

Also consider tuning OGG config for dealing with that kind of a load.

[kvmishra]

Hello Erman,

As per below screenshot, we could see that there is more than 1crore updates are done on this table (JAI_TAX_DET_FACTORS) to till now and more are coming rapidly. Due to this, we could believe that the GG is capturing the data faithfully.



What can we do next? Please share any work around for this.

Thanks in advance.

[ErmanArslansOracleBlog]

That may be normal. I mean processes may use delete and re-insert or frequent massive updates during the tax calculation phase. Maybe a massive update is done there. You should investigate it with your Functional Administrators.

Since you are using OGG for populating the target data store for auditing purposes, you may consider decreasing the scope, I mean :Identify the 5–10 columns that actually matter for your security/audit and make OGG listen to the changes of those ones. This may be helpful in your case.

consider using COMPRESSUPDATES &

COMPRESSUPDATES, the default, causes Extract to write only the primary key and the changed columns of a row to the trail for update operations. This provides enough information to update the correct target record (unless conflict resolution is required), while restricting the amount of data that must be processed.

Also check /consider-> GETUPDATEBEFORES | IGNOREUPDATEBEFORES parameters.

That output format may also bring some overhead ->  OUTPUTFORMAT XML _AUDIT_VAULT

Ensure your EXTRACT/EXTRAIL related filesystem / file organization-related configurations are in place and properly set..

[kvmishra]

Hello Erman,

I asked to functional team and they have analyzed and found as the data in JA.JAI_TAX_DET_FACTORS and JAI_TAX_LINES_ALL is in sync with each other and fulfill the purpose of Audit requirements. Hence, we have excluded the JA.JAI_TAX_DET_FACTORS from OGG as JAI_TAX_LINES_ALL was already in place.

Thanks for your support.

[ErmanArslansOracleBlog]

That's good!. If you don't lose any information and if all the curucial data that you need in the detail table I mean the one with the _ALL suffix, then OK.. Good! Make your functional team work :)