Skip to Main Content
  • Questions

  • POUR performance on CLEANUP/PURGE SYS.$AUD

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Alireza.

Asked: September 29, 2023 - 4:15 pm UTC

Last updated: December 04, 2024 - 6:51 am UTC

Version: 19.17.0.0.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,
we need to purge, ARCHIVE and cleanup our SYS.$AUD table.
WE do have a RAC x 2 node as an Active DG too.

We have a self-defined procedure , which is working appropriately (using DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL).
However the table is >700GB and >700 Mil rows.
The procedure runs everyday and retrieves the data from $AUD -> makes a CTAS and inserts the data in regular table based on the date range --> then the appropriate date range would be purged and cleaned up.


SQL> select /*+ PARALLEL(64) */ to_char(ntimestamp#,'YYYYMMDD') as date_aud , COUNT(0) row_num from sys.aud$ group by to_char(ntimestamp#,'YYYYMMDD') ORDER BY to_char(ntimestamp#,'YYYYMMDD');

a partial selection of AUD table:
DATE_AUD ROW_NUM
-------- ----------
20230712 4722355
20230713 4748860
20230714 4231487
20230715 4360975
20230716 4173837
20230717 5148050
20230718 5414122
20230719 4281115
20230720 4599951
20230721 4002760
20230722 3982341
..................
..................
78 rows selected

We use also the max bacth size (1M) for cleanup
.
SELECT * FROM dba_audit_mgmt_config_params;
DB AUDIT CLEAN BATCH SIZE 1000000 STANDARD AUDIT TRAIL

The issue is that the CLEANUP/ARCHIVE is very I/O intensive and takes 4-5 hours to cleanup/archive the data for a single date range, and we can run max 2 times a day (from 12pm to 12am) as some very I/O intensive ETL jobs run before and after this time period.
We also tried to MOVE the TBS to a new ONE , to have new segment optimized TBS for a better performance using:

SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$
audit_trail_location_value => 'NEW_TBS');
END;
/

But the MOVE process (in a down time) takes more than 20hrs , which is not affordable in a PROD system.
WE opened also 2 SRs with no success on an acceptable result in terms of I/O.

Also the AUDIT data is getting produced every day , so that we are running behind, but with every daily CLEANUP we get new data so that it takes forever to catch up.

Unless you recommend any other approach to achieve this challenge? we do have also a snapshot DG in case you have an idea.
Pls let me know if any info missing or need more INPUT.

Thank you very much!
Ali



and Connor said...

I'm going to propose a different route here - I would stop working with AUD$ and migrate to unified auditing.

Some good info here

https://docs.oracle.com/en/database/oracle/oracle-database/21/upgrd/recommended-and-best-practices-complete-upgrading-oracle-database.html#GUID-21FE7F97-DE79-43D4-A8DD-D66035C17608

and

https://mikedietrichde.com/2015/02/03/how-to-migrate-to-unified-auditing/

The reason for my recommendation is that the base tables for *unified* auditing are partitioned, and thus when you come to do cleanup activities the DBMS_AUDIT_MGMT routine can take advantage of partition-wise operations to remove old data.

So during your next maintenance cycle, activate unified auditing, take a final archival of all of the data in AUD$ and then truncate it, and move forward with unified auditing.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sr. oracle dba

Alireza Mirbakhtiar, October 03, 2023 - 4:15 pm UTC

Hi Connor,
thank you for you hint, however (just in case our customer prefers to remain by standard auditing) , can the $AUD table get truncated after the data is archived?
the purpose is to TRUNCATE the table and resize the Tablespace (not SYSAUX) ,, thus the tablespaces would be set with optimized segments and the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL operations run much faster. The reason i am asking is, because support DOES not recommend the TRUNCATE on this table.

Connor McDonald
October 04, 2023 - 1:21 am UTC

In normal operation we always recommend the use of DBMS_AUDIT_MGMT (as per Support), but for specific issues you should be able to get their blessing for a one-off use of truncate (most probably during an outage)

Sr. oracle dba

Alireza Mirbakhtiar, October 03, 2023 - 4:54 pm UTC

... also need to know if unified auditing requires any advanced license? or it is included feature of EE 19c?

Thank you!
Ali
Connor McDonald
October 04, 2023 - 1:22 am UTC

Unified auditing is not part of a separate license


For future reference - a nifty tool

https://apex.oracle.com/database-features/

Oracle DBA

alireza, November 21, 2024 - 7:28 pm UTC

Hi ,
the ONLINE MOVE on the cloned system (from PROD) took forever to do:

SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$
audit_trail_location_value => 'NEW_TBS');
END;
/

As it created high I/O and set an exclusive LOCK on the AUD$ for MOVE - we had to kill the process after 3 days and complains form the customer.
How can i skip EXCL. LOCK on the AUD$ by doing ONLINE MOVE or optimize the MOVE procedure (any DOP option ?), as we are not allowed to disable auditing and need to take the ONLINE option by our compliance policy.

And we NEED to use "STANDARD AUDIT TRAIL" and thsi is our config view:

SELECT * FROM dba_audit_mgmt_config_params where PARAMETER_NAME='DB AUDIT CLEAN BATCH SIZE';

DB AUDIT CLEAN BATCH SIZE 1000000 STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL

We the AUD$ table is over 6TB big :(

Thank you!
Alireza
Connor McDonald
December 04, 2024 - 6:51 am UTC

Well...if you're staying with standard audit, then you have a 6TB table that you need to maintain, which is the same as any other 6TB table - its going to be a challenge.

I'd suspect you'll need to do this manually as outlined below BUT (!!!) log an SR to get Support's blessing first!

You can tackle it in stages

1) Create a table NEW_AUD$ in the new tablespace being the same structure as AUD$. Same for FGA$
2) Copy everything except (say) this month to to NEW_AUD$. This can be done without service interruption and in batches over a few days. Same for FGA$. Add indexes to FGA$
3) Take an outage, and copy the remaining data over.
4) truncate AUD$ / FGA$
5) Use DBMS_AUDIT_MGMT to relocate the now empty AUD$ / FGA$ tables to the new tablespace to ensure all the correct metadata is in place.
6) rename AUD$ / FGA$ to "old", rename NEW_AUD$/FGA$ to the correct names
7) system comes online again

Now you are in a new tablespace without too much downtime.....You still have a 6TB problem to deal with in future

BUT (!!!) log an SR to get Support's blessing first!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.