One of the great new features is unified auditing in Oracle 12c, with this feature you can start to define your own auditing, or use one of the built in policies. We don’t look into this blog about the general unified auditing.

But what will happen when you use Unified Auditing and the database is not in read/write mode? In this case the audit records are default stored in
$ORACLE_BASE/audit/<SID>/

This blog just explain how to load the audit records automatically from OS level and store it in the unified_audit_trail table when the database is change from mount into open read write mode.

Example:

$ cd /u01/app/oracle/audit/PCDB01

$ ls -ltr

drwxr-s---. 2 oracle dba   6 Nov 2 15:51 2390AC75A8C97066E05311035B0AAA42
-rw-rw----. 1 oracle dba 1536 Nov 18 11:24 ora_audit_0274.bin
-rw-rw----. 1 oracle dba 1536 Nov 18 11:41 ora_audit_0588.bin
-rw-rw----. 1 oracle dba 1536 Nov 18 12:01 ora_audit_1626.bin
-rw-rw----. 1 oracle dba 1536 Nov 18 12:01 ora_audit_0626.bin
-rw-rw----. 1 oracle dba 1536 Nov 18 12:01 ora_audit_10.bin
-rw-rw----. 1 oracle dba 3072 Nov 18 12:02 ora_audit_00.bin
-rw-rw----. 1 oracle dba 3584 Nov 18 12:02 ora_audit_1742.bin
-rw-rw----. 1 oracle dba 5120 Nov 18 12:02 ora_audit_1236.bin

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production
Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options

SQL> execute dbms_audit_mgmt.load_unified_audit_files;
PL/SQL procedure successfully completed.

SQL> exit

$ /u01/app/oracle/audit/PCDB01

$ ls -ltr

total 0
drwxr-s---. 2 oracle dba 6 Nov 2 15:51 2390AC75A8C97066E05311035B0AAA42

As you can see after executing the procedure the OS audit files are loaded and automatically removed. Which is cool! No need to clean up those files, of course you need a purge policy for the unified audit trail, but that is not part of this blog.

So what is left is to create a startup trigger, which will automatically load the unified audit OS files when an instance is started. During some additional testing we need to modify an earlier trigger to delay the execution of the job for a minute to not run into an error:

ORA-00604: error occurred at recursive SQL level 1
ORA-55950: storage not associated with the secure file log

[ name: ORA$AUDIT_NEXTGEN_LOG ] ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 5329
ORA-06512: at line 3

 

So the final trigger used look like this:
create or replace trigger cdb_after_startup
after startup ON DATABASE
declare
jobno number;
begin
-- make a one time job with a delay so no errors are given during startup
-- Load unified audit files
dbms_job.submit
( job => jobno
, what => 'dbms_audit_mgmt.load_unified_audit_files;'
, next_date => sysdate+1/24/60 -- One minute later
);
end;
/

Done!

Please be aware of bug 18743542, about cleaning of the unified audit trail “12C UNIFIED AUDIT TRAIL, CANNOT DELETE LAST_ARCHIVE_TIME (Patch)”