Complete POA for Purging the AUD$ Table in Oracle
In large Oracle databases, the AUD$ table can grow rapidly and impact performance and storage management.
This article provides a complete plan of action (POA) for purging the AUD$ table safely while ensuring that all critical audit data is backed up.
Overview
The provided SQL snippets illustrate the initial steps:
- Querying the current size of the
AUD$table. - Counting rows based on a timestamp filter (
NTIMESTAMP#). - Exporting the data before a specified cutoff date.
- Backing up recent rows into a separate table (
aud_bkp_07_Jun_2023).
The next step is to safely purge the unwanted historical audit records while ensuring database integrity and space reclamation.
Pre-Purge Considerations
-
Data Backup: Verify that you have a complete backup of the data to be purged. This involves both an export dump and a backup table.
nohup exp '/ as sysdba' file=/backup/EXP_AUD/EXP_AUD1_BEFORE_110724.dmp log=/backup/EXP_AUD/EXP_AUD1_BEFORE_110724.log tables=sys.aud$ & -
Data Verification: Confirm that the backup table (
aud_bkp_07_Jun_2023) contains the correct number of rows.
SELECT count(*) FROM aud_bkp_07_Jun_2023 WHERE NTIMESTAMP# >= TO_DATE('2023-05-29 22:19:00', 'YYYY-MM-DD HH24:MI:SS'); - Maintenance Window: Plan the purge during a maintenance window or during low database usage hours to minimize performance impact.
- Space Reclamation: After purging, consider using table reorganization or shrinking commands to reclaim unused space.
Step-by-Step Purge Process
Step 1: Identify and Verify Data to Purge
Count the number of rows that are older than the cutoff date.
SQL> SELECT count(*) FROM SYS.AUD$
WHERE NTIMESTAMP# <= TO_DATE('2024-07-11 16:48:00', 'YYYY-MM-DD HH24:MI:SS');
This gives you an estimate of the rows that will be purged.
Step 2: Export the Data (Backup)
Always export the data before purging so that you can restore it if needed.
nohup exp '/ as sysdba' file=/backup/EXP_AUD/EXP_AUD1_BEFORE_110724.dmp
log=/backup/EXP_AUD/EXP_AUD1_BEFORE_110724.log tables=SYS.AUD$ &
Step 3: Create a Backup Table (Optional)
For added safety, copy the recent audit data into a backup table.
CREATE TABLE aud_bkp_07_Jun_2023 AS
SELECT * FROM SYS.AUD$
WHERE NTIMESTAMP# >= TO_DATE('2023-05-29 22:19:00', 'YYYY-MM-DD HH24:MI:SS');
Step 4: Purge the Old Audit Data
With backups confirmed, delete the records older than your cutoff date.
SQL> DELETE FROM SYS.AUD$
WHERE NTIMESTAMP# <= TO_DATE('2024-07-11 16:48:00', 'YYYY-MM-DD HH24:MI:SS');
SQL> COMMIT;
Note: Depending on the size of the data, you might want to perform the deletion in batches to reduce undo generation and impact on system performance.
Step 5: Reclaim Space
After purging data, reclaim the space by shrinking the table or reorganizing the database segments.
-- For a table with segment space management enabled: SQL> ALTER TABLE SYS.AUD$ SHRINK SPACE; -- Alternatively, if using table reorganization: SQL> ALTER TABLE SYS.AUD$ MOVE;
Step 6: Verify the Purge
Confirm that the purge has completed successfully by re-counting the rows in the AUD$ table.
SQL> SELECT count(*) FROM SYS.AUD$;
Post-Purge Best Practices
- Monitoring: Continuously monitor the audit table size and performance after the purge.
- Regular Maintenance: Schedule regular purges or archiving routines to prevent the table from growing too large.
- Documentation: Update your database maintenance documentation to include the purge process and any relevant scripts.
- Testing: Test the backup and restore process periodically to ensure data integrity.
Conclusion
Purging the AUD$ table is a critical task for maintaining database performance and managing storage. By following the steps outlined above—verifying data, backing up records, performing a batch deletion, and reclaiming space—you can safely reduce the table size while ensuring that audit data is preserved for compliance and recovery purposes.
Always ensure that you work within a controlled maintenance window and monitor system performance throughout the process. Regular maintenance will help avoid performance bottlenecks and ensure a streamlined auditing environment.