Complete POA for Purging the AUD$ Table in Oracle

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.


0 comments:

Post a Comment