Oracle Audit Control part 3 – OS files

Just realized that I wrote a follow-up to Part 2 two years ago and never posted it! So here’s the stripped-out key facts to round off auditing control a bit more:

Deleting O/S audit files is, like, totally necessary. In Oracle 12C (12.1), they are created by default as AUDIT_SYS_OPERATIONS defaults to TRUE now, so you are going to be filling up your AUDIT_FILE_DEST whether you realized it or not.

[NOTE: In theory, this could end up being a performance problem as well as a space problem if you have many millions files in the AUDIT_FILE_DEST. In Oracle 11G, when allocating an AUDIT file, it would check to see if a file for that SPID already existed and add an incremental number, The act of calling vx_dirbread and vx_dirscan can get terribly slow if there are lots of files to wade through. This mechanism is different in 12C but I have not traced the kernel calls to see if the vx_ calls have gone away.]

To cleanup the O/S, we use the same commands as we did in Part 2, but changed to use an AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS

You also need to be aware of the RAC_INSTANCE_NUMBER. For database-level audit, it’s irrelevant. For single instance, it is also irrelevant, but with RAC, this parameter tells you which node to clean up as they are all individual installs with their own audit files (assuming a Grid Infrastructure install per node.)

Commands:

-- Show the BEFORE sizes

host ssh server01 du -m -s /u01/app/oracle/admin/ORCL/a*

host ssh server02 du -m -s /u01/app/oracle/admin/ORCL/a*

begin

-- Loop around every instance in the RAC cluster and run the command

for i in (select inst_id from gv$instance)

loop
-- cleanup AUDIT_FILE_DEST (operating system audit files)

-- set cleanup for 60 days

DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(

AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,

LAST_ARCHIVE_TIME => systimestamp-60,

RAC_INSTANCE_NUMBER => i.inst_id );
-- And cleanup based upon that date

DBMS_AUDIT_MGMT.clean_audit_trail(

audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,

use_last_arch_timestamp => TRUE);
end loop;
end;

/
-- And check the sizes AFTER

host ssh server01 du -m -s /u01/app/oracle/admin/ORCL/a*

host ssh server02 du -m -s /u01/app/oracle/admin/ORCL/a*
 

Output:

15:58:56 SYS @ ORCL1 > host ssh server01 du -m -s /u01/app/oracle/admin/ORCL/a*

776 /u01/app/oracle/admin/ORCL/adump

15:58:56 SYS @ ORCL1 > host ssh server02 du -m -s /u01/app/oracle/admin/ORCL/a*

694 /u01/app/oracle/admin/ORCL/adump

15:58:56 SYS @ ORCL1 >

15:58:56 SYS @ ORCL1 >

15:58:56 SYS @ ORCL1 > begin

15:58:56   2

15:58:56   3   for i in (select inst_id from gv$instance)

15:58:56   4   loop

15:58:56   5

15:58:56   6      -- cleanup AUDIT_FILE_DEST (operating system audit files)

15:58:56   7      -- set cleanup for 60 days

15:58:56   8      DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(

15:58:56   9      AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,

15:58:56  10      LAST_ARCHIVE_TIME => systimestamp-50,

15:58:56  11      RAC_INSTANCE_NUMBER => i.inst_id );

15:58:56  12

15:58:56  13      -- And cleanup based upon that date

15:58:56  14      DBMS_AUDIT_MGMT.clean_audit_trail(

15:58:56  15        audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,

15:58:56  16        use_last_arch_timestamp => TRUE);

15:58:56  17

15:58:56  18   end loop;

15:58:56  19

15:58:56  20  end;

15:58:56  21  /

PL/SQL procedure successfully completed.

15:58:56 SYS @ ORCL1 >

15:58:56 SYS @ ORCL1 >

15:58:56 SYS @ ORCL1 > host ssh server01 du -m -s /u01/app/oracle/admin/ORCL/a*

354 /u01/app/oracle/admin/ORCL/adump

15:58:57 SYS @ ORCL1 > host ssh server02 du -m -s /u01/app/oracle/admin/ORCL/a*

364 /u01/app/oracle/admin/ORCL/adump

15:58:57 SYS @ ORCL1 >

 

 
And just one final bit. If you change the default

AUDIT_TRAIL=DB to
AUDIT_TRAIL=DB, EXTENDED

the audit writes the first 2,000 characters of the SQL command to DBA_AUDIT_TRAIL.SQL_TEXT, so you get the full text of the SQL command audited, rather than just the action. Very handy!