Purging Audit Trail in RAC & ODG with ASM environment

In Oracle Database Security Guide (12c), there is a section to describe how to purge Audit Trail records. Basically you have two options to do it using DBMS_AUDIT_MGMT:

Schedule a job to do it regularly.
Manually purge it as needed.

However it’s not obvious how to do it in a RAC with ODG on ASM environment. With ODG, the standby database normally is in mount state which you cannot use Oracle scheduler. And with ASM, you need to purge Audit Trail records for ASM as well.

Here I will demonstrate how to do it on manually since using scheduler can be only done on Primary database (in open mode):

Primary database on each RAC node
Standby database on each RAC node
ASM on each RAC node and on both Primary & Standby

On Primary database, you can just use two procedures SET_LAST_ARCHIVE_TIMESTAMP and CLEAN_AUDIT_TRAIL from the package DBMS_AUDIT_MGMT. My database has the following parameters set to:

NAME                  TYPE        VALUE
--------------------- ----------- ------------------------------
audit_file_dest       string      /u01/app/oracle/admin/JOEDB/adump
audit_sys_operations  boolean     TRUE

It means Oracle database audits top-level operations, which are SQL statements directly issued by users when connecting with the SYSASM, SYSBACKUP, SYSDBA, SYSDG, SYSKM, or SYSOPER privileges. (SQL statements run from within PL/SQL procedures or functions are not considered top-level.) The audit records are written to the operating system’s audit trail.

To remove all files before a certain date, first use SET_LAST_ARCHIVE_TIMESTAMP to set a timestamp indicating when the audit records were last archived. The CLEAN_AUDIT_TRAIL Procedure uses this timestamp to decide on the audit records to be deleted. Run the following procedure as a user who has SYSDBA or AUDIT_ADMIN role. Here I used AUDIT_TRAIL_OS for Audit Trail Type because I intended to remove audit files from OS.

BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
LAST_ARCHIVE_TIME => '18-APR-2020 06:30:00.00',
RAC_INSTANCE_NUMBER => 1 );
END;
/

Then use CLEAN_AUDIT_TRAIL to clear records/files before the last archive time — the timestamp set up above.

BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
   AUDIT_TRAIL_TYPE           =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   USE_LAST_ARCH_TIMESTAMP    =>  TRUE);
END;
/

Repeate the 2 steps above on another RAC node of the Primary database, replacing the RAC_INSTANCE_NUMBER to 2 when running SET_LAST_ARCHIVE_TIMESTAMP. You can check the DBA view DBA_AUDIT_MGMT_LAST_ARCH_TS to see last archive time stamps. Each run on each node will create a seperate entry in this view.

Note: AUDIT_TRAIL value: “DB” Enables database auditing and directs standard audit records to the database audit trail (SYS.AUD$), except for records that are always written to the operating system audit trail. (This value is the default if you created the database using Database Configuration Assistant. Otherwise, the default is NONE.) So you want to check SYS.AUD$ as well (select count(*) from sys.aud$) if you have AUDIT_TRAIL set to “DB”. And you can just run

delete from sys.aud$;

to clean up Audit records from there.

Also you may see recordes in this view UNIFIED_AUDIT_TRAIL even you don’t have Unified Audit Trail enabled in 12c. That’s because there is a default Unified Audit policy called ORA_SECURECONFIG. Still you can use CLEAN_AUDIT_TRAIL to clean it up. See the article The UNIFIED_AUDIT_TRAIL is Getting Populated even if Unified Auditing was not explicitly enabled in 12c (Doc ID 1624051.1) in Oracle Support.

SQL> exec DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

PL/SQL procedure successfully completed.

SQL> BEGIN
   DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
     AUDIT_TRAIL_TYPE           =>  
     DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
     USE_LAST_ARCH_TIMESTAMP    =>  FALSE,
     CONTAINER                  => dbms_audit_mgmt.container_current);
END;
/

2. On Standby Database, because the database is not open normally, or even open in read-only mode (DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP And CLEAN_AUDIT_TRAIL Doesn’t Work On Read Only Database (Doc ID 2471658.1)), you cannot use both procedures as you do on Primary database. You will get all kinds of errors (see below) if you try to do so.

BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
	AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
	LAST_ARCHIVE_TIME => '18-APR-2020 06:30:00.00',
	RAC_INSTANCE_NUMBER => 1 );
END;
/

DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'SET_LAST_ARCHIVE_TIMESTAMP'
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored


BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
	AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
	LAST_ARCHIVE_TIME => '18-APR-2020 06:30:00.00'
	);
END;
/

ERROR at line 1:
ORA-29261: bad argument
ORA-46266: Missing value for argument 'RAC Instance Number' in Oracle RAC enabled database
ORA-06512: at "SYS.X$DBMS_AUDIT_MGMT", line 204
ORA-06512: at line 2

SQL> BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
   AUDIT_TRAIL_TYPE           =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   USE_LAST_ARCH_TIMESTAMP    =>  TRUE);
END;
/
BEGIN
*
ERROR at line 1:
ORA-55939: database is not open for attempted operation
ORA-06512: at "SYS.X$DBMS_AUDIT_MGMT", line 246
ORA-06512: at line 2

In this case, you need to use OS “find” command to manage them. For example, the following command find any files with suffix “aud” under current directory and directory depth 1 older than 30 days, then delete them. Put it as a cron tab job to delete files regularly.

find ./ -maxdepth 1 -name '*.aud' -mtime +30 -delete

3. For ASM instance, I have following settings.

NAME                   TYPE        VALUE
---------------------- ----------- ------------------------------
audit_file_dest        string      /u01/app/12.2.0.1/grid/rdbms/audit
audit_sys_operations   boolean     TRUE

Since ASM instance is different than normal database instance. It’s not in OPEN mode as well, you cannot use SET_LAST_ARCHIVE_TIMESTAMP. So it’s pretty much like purging Audit Trail files on ODG Standby database, you just use CLEAN_AUDIT_TRAIL.

BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
   AUDIT_TRAIL_TYPE           =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
   USE_LAST_ARCH_TIMESTAMP    =>  TRUE
 );
END;
/

BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
   AUDIT_TRAIL_TYPE           =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   USE_LAST_ARCH_TIMESTAMP    =>  TRUE);
END;
/

Oracle ASM Administrator’s Guide does mention how to manage Audit Trail for ASM.

Manage Audit File Directory Growth with cron (Doc ID 1298957.1)
Manage ASM Audit Files with syslog (Doc ID 1559573.1)
AUDIT_SYS_OPERATIONS Set To FALSE Yet Audit Files Are Generated (Doc ID 308066.1)
Init.ora Parameter “AUDIT_FILE_DEST” Reference Note (Doc ID 39796.1)
How to schedule UNIFIED_AUDIT_TRAIL purge job (Doc ID 2231430.1)

And for OS level Audit Trail files, you can always use OS “find” command to manage them no matter for Primary, Standby or ASM if you are lazy.