Oracle Database Discussions

How was the ERP schema restored?

What all auditing options have you enabled for your ERP schema, query dba_stmt_audit_opts view (edited)...check if the user has got auditing enabled for Insert or Create table, if it has then your aud$ will increase, as information would be inserted into the aud$ table for every insert and fior every create table.

Regards,
Ajinkya

Edited by: AjinkyaSH on Mar 11, 2010 2:41 AM

It's LIVE database, the Operation Dept. executed the wrong day end procedures, before that we had taken export of ERP schema, we restored the dump file on LIVE ERP schema.

And then I enabled auditing for ERP user.

I am unable to post my result for select * from dba_stmt_audit_opts but I get 29 rows and all are for ERP user.

I enabled auditing
- AUDIT ALL BY erp BY ACCESS
- Audit Select table, update table, delete table, insert table by erp by access

PLease tell me the difference between these two, I think i should use the second statement/line only, it will audit only DML and leave the other commands..

Edited by: The_Rock on Mar 11, 2010 2:59 AM

within half an hour the aud$ table size has increased by 2 GB.

If you do audit for DML statements only, each and every time you do a insert,update,delete it will make an entry into the aud$ table, when you do audit all it ignores the DML statements. In case your ERP user does many transactions a day then (select,insert,update,delete) and the size of your aud$ increases, If possible you can opt for doing DML audits for the important tables in the ERP schema.

Regards,
Ajinkya

I agree that each and every DML, an entry willl be made in AUD$ table, but Should I do audit for DML's only?
As the user actions will be audited from ERP-APPLICATION. why should I audit indexes, MViews, sequences, triggers, etc..etc?

There are over 1,000 tables, Sir How would I lnow what tables to audit?

Okies since you have many tables, auditing all of them would create issues in increased the aud$, what other thing you can do is purging aud$ after taking an export backup of the same.

As far as auditing indexes and other objects, its not necessary, but again what if some one drops your indexes or any other objects, so you need to streamline the parameters of your auditing. Figure out what things does your application audit at its own level, things which the application skips, you can include it on the db level auditing.

Regards,
Ajinkya

Purging an AUD$ table is good idea after taking the export....
Yeah...that could be better idea to audit those things that application skips...

I was just getting calls from finance and operations departments... complaining that their ERP applications were haning taking long time to execute day end procedures and in reports...around 20 to 30 minutes.... as I recalled that my last deployment on live was enabling of auditing as I executed noaudit all and noaudit select, update, delete, insert on erp, The user got their day end procedures executed and report in less than 1 minute...

Can anybody explain me....Does auditing degrades performance..?

Regards?

Enabling auditing increases the overhead in the database, as each and every transcation gets recorded in aud$, so enabling auditing does comes with its own share of performance issue. Also the aud$ resides in system tablespace, so insert of audit records in the system tablespace also makes your system tbs file a hotspot.

Regards,
Ajinkya

P.S Mark the question as helpful/answred in case your issue is resolved.

Thanks Ajinkya,

I have enabled auditing today in the morning for ERP user to resolve the issue, How Can I exclude one specific table from auditing...as yesterday one particular table was taking excluive table level lock?

I have truncated the AUD$ table today, its size had become 13GB.

Yeah...I have marked the answers as Helpful.

Check out this link:

http://download.oracle.com/docs/cd/B13789_01/network.101/b10773/cfgaudit.htm#1006825

Regards,
Ajinkya

Thanks
Can I exclude one specific table from auditing ? Is that possible?

I guess, you can include all the tables in auditing and just ignore the table you dont want to, and again why do you need to exclude one particular table?? If there is a lock on that table, then it is not because of auditing, auditing will make entry into the aud$ table, it is not the reason for your table getting locked.

Regards,
Ajinkya

I do agree with you, but as I executed NOAUDIT commands, the locks were released and user reports and day end procedures executed instantly.