datablogs

Oracle RDS Audit Log Enable – Part 3 (Audit Growth Testing, Tablespace Validation & Audit Monitoring in AWS RDS)

Selva May 31, 2026 0 Comments

In Part 1, we enabled Oracle auditing in AWS RDS.
In Part 2, we covered unified auditing, specific object auditing, and audit policy management.

In this article, we will focus on:

  • Generating audit workload using Oracle Scheduler Jobs
  • Validating audit growth
  • Monitoring audit tablespace usage
  • Testing audit performance
  • Verifying audit history after tablespace migration

This is useful for DBAs who want to validate:

  • Audit retention
  • Audit storage growth
  • Audit tablespace sizing
  • Production audit behavior 

then audit archival, and Amazon S3 integration in Oracle 19c AWS RDS. Each step below explains why the configuration is required, how it works internally, and how to validate the result.

Environment

  • Oracle Database: 19c
  • Platform: AWS RDS for Oracle
  • Audit Mode: Standard/Unified Auditing
  • Tablespace Used: AUDIT_TS 

Step 1: Create Test Table

This table will be used to generate INSERT, UPDATE, and DELETE operations so that Oracle audit records can be generated continuously during testing.

CREATE TABLE AUDIT_TRAIL.TBLAUDITTRAIL_NEW
(
    ID NUMBER,
    USERNAME VARCHAR2(100),
    CREATEDDATE DATE,
    STATUS VARCHAR2(100)
);

Step 2: Enable Table Level Auditing

Oracle object-level auditing tracks DML operations performed against a specific table. Using BY ACCESS records each individual statement execution separately.

AUDIT INSERT, UPDATE, DELETE
ON AUDIT_TRAIL.TBLAUDITTRAIL_NEW
BY ACCESS;

Step 3: Validate Audit Configuration

The DBA_OBJ_AUDIT_OPTS view confirms whether INSERT, UPDATE, and DELETE auditing is enabled successfully on the target table.

SELECT owner,
       object_name,
       ins,
       upd,
       del
FROM dba_obj_audit_opts
WHERE object_name='TBLAUDITTRAIL_NEW';

Step 4: Create Dedicated Audit Tablespace

Keeping audit data inside SYSAUX for long periods can increase database storage usage. A dedicated AUDIT_TS tablespace improves monitoring and audit storage management.

CREATE TABLESPACE AUDIT_TS
DATAFILE SIZE 1G
AUTOEXTEND ON NEXT 100M
MAXSIZE UNLIMITED;

Step 5: Initialize Audit Cleanup Infrastructure

INIT_CLEANUP prepares Oracle audit management internally. This is a one-time initialization step required before creating purge jobs.

BEGIN
  DBMS_AUDIT_MGMT.INIT_CLEANUP(
      audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
      default_cleanup_interval => 24
  );
END;

Step 6: Move Audit Trail to Dedicated Tablespace

This step moves the physical AUD$ audit segment from SYSAUX to AUDIT_TS while keeping the DBA_AUDIT_TRAIL view accessible normally.

BEGIN
  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
      audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
      audit_trail_location_value => 'AUDIT_TS'
  );
END;

Step 7: Validate Audit Tablespace Migration

The DBA_SEGMENTS view confirms whether Oracle successfully moved the AUD$ segment into the new AUDIT_TS tablespace.

SELECT segment_name,
       tablespace_name
FROM dba_segments
WHERE segment_name IN ('AUD$','FGA_LOG$');

Step 8: Capture Tablespace Usage Before Testing

Before generating audit workload, capture baseline tablespace usage so growth can be compared later.

SELECT tablespace_name,
       ROUND(SUM(bytes)/1024/1024,2) MB
FROM dba_segments
GROUP BY tablespace_name;

Step 9: Create Scheduler Job for Audit Growth Testing

This scheduler job continuously generates INSERT and UPDATE operations every minute. The objective is to simulate production audit workload and validate audit storage growth.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
      job_name        => 'AUDIT_GROWTH_TEST_JOB',
      job_type        => 'PLSQL_BLOCK',
      job_action      => q'[
      DECLARE
          v_id NUMBER;
      BEGIN
          v_id := TRUNC(DBMS_RANDOM.VALUE(1000,999999));

          INSERT INTO AUDIT_TRAIL.TBLAUDITTRAIL_NEW
          VALUES (
              v_id,
              'TESTUSER',
              SYSDATE,
              'ACTIVE'
          );

          UPDATE AUDIT_TRAIL.TBLAUDITTRAIL_NEW
          SET STATUS = 'Selva'
          WHERE ID = v_id;

          COMMIT;
      END;
      ]',
      start_date      => SYSTIMESTAMP,
      repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
      enabled         => TRUE
  );
END;

Step 10: Validate Scheduler Job

DBA_SCHEDULER_JOBS and DBA_SCHEDULER_JOB_RUN_DETAILS help verify whether the scheduler job is running successfully.

SELECT job_name,
       enabled,
       state,
       repeat_interval
FROM dba_scheduler_jobs
WHERE job_name='AUDIT_GROWTH_TEST_JOB';

SELECT job_name,
       status,
       actual_start_date,
       run_duration
FROM dba_scheduler_job_run_details
WHERE job_name='AUDIT_GROWTH_TEST_JOB'
ORDER BY log_date DESC;

Step 11: Validate Audit Growth

After the scheduler job runs for several minutes, recheck tablespace growth to validate audit storage consumption.

SELECT tablespace_name,
       ROUND(SUM(bytes)/1024/1024,2) MB
FROM dba_segments
GROUP BY tablespace_name;

Step 12: Validate Audit Records

DBA_AUDIT_TRAIL and UNIFIED_AUDIT_TRAIL can be queried to validate audit record generation.

SELECT username,
       action_name,
       obj_name,
       timestamp,
       sql_text
FROM dba_audit_trail
WHERE obj_name='TBLAUDITTRAIL_NEW'
ORDER BY timestamp DESC;

SELECT event_timestamp,
       dbusername,
       action_name,
       object_name
FROM unified_audit_trail
ORDER BY event_timestamp DESC;

Step 13: Configure Audit Retention

SET_LAST_ARCHIVE_TIMESTAMP defines how long audit data should remain inside the database before purge jobs remove older records.

BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
      audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
      last_archive_time => SYSTIMESTAMP - 30
  );
END;

Step 14: Create Purge Job

CREATE_PURGE_JOB automatically removes old audit records periodically. This prevents uncontrolled audit growth inside SYSAUX or AUDIT_TS.

BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
      audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
      audit_trail_purge_interval => 3,
      audit_trail_purge_name     => 'STD_AUDIT_PURGE_JOB',
      use_last_arch_timestamp    => TRUE
  );
END;

Step 15: Create Dedicated Audit User

Using dedicated audit users instead of ADMIN improves security isolation and avoids duplicate privilege-based audit entries.

CREATE USER AUDIT_APP
IDENTIFIED BY StrongPassword123
DEFAULT TABLESPACE AUDIT_TS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON AUDIT_TS;

GRANT CREATE SESSION TO AUDIT_APP;
GRANT CREATE TABLE TO AUDIT_APP;

Step 16: Create Audit Archive Table

This table stores historical audit records before they are archived externally or purged from the database.

CREATE TABLE AUDIT_ARCHIVE
(
    USERNAME        VARCHAR2(100),
    ACTION_NAME     VARCHAR2(100),
    OBJ_NAME        VARCHAR2(200),
    EVENT_TIME      TIMESTAMP,
    SQL_TEXT        CLOB
);

Step 17: Archive Audit Records

Move older audit records into the custom archive table for long-term retention.

INSERT INTO AUDIT_ARCHIVE
SELECT
    USERNAME,
    ACTION_NAME,
    OBJ_NAME,
    EXTENDED_TIMESTAMP,
    SQL_TEXT
FROM DBA_AUDIT_TRAIL;

Step 18: Upload Audit Archive to Amazon S3

Oracle RDS supports direct S3 integration using rdsadmin.rdsadmin_s3_tasks. This allows long-term audit archival outside the database.

SELECT rdsadmin.rdsadmin_s3_tasks.upload_to_s3(
    p_bucket_name    => 'my-audit-bucket',
    p_directory_name => 'DATA_PUMP_DIR',
    p_s3_prefix      => 'oracle-audit/',
    p_file_name      => 'audit_export.csv'
) AS TASK_ID
FROM dual;

Step 19: Stop and Remove Scheduler Job

After testing completes, disable and remove the scheduler job to avoid unnecessary audit generation.

BEGIN
  DBMS_SCHEDULER.DISABLE('AUDIT_GROWTH_TEST_JOB');
END;

BEGIN
  DBMS_SCHEDULER.DROP_JOB('AUDIT_GROWTH_TEST_JOB');
END;

Audit object configuration validation

Audit tablespace migration validation

Tablespace usage before audit growth test

Tablespace usage after audit growth test

Important Production Considerations

• Oracle SE2 does not support Fine Grained Auditing (FGA).
• ADMIN user may generate duplicate audit rows because of ANY TABLE privileges.
• Purge jobs clean audit trail records, not tablespaces directly.
• Purging audit records does not automatically shrink datafiles.
• Archive old audit records regularly to Amazon S3.
• Monitor AUDIT_TS growth periodically in production environments.

Conclusion

This end-to-end Oracle RDS auditing implementation provides a complete enterprise-ready solution for audit monitoring, growth management, automated cleanup, archival, and long-term compliance reporting.

0 Comments