What is Unified Auditing? says:
The unified audit trail, which resides in a read-only table in the AUDSYS schema in the SYSAUX tablespace, makes this information available in a uniform format in the UNIFIED_AUDIT_TRAIL data dictionary view, and is available in both single-instance and Oracle Database Real Application Clusters environments. In addition to the user SYS, users who have been granted the AUDIT_ADMIN and AUDIT_VIEWER roles can query these views. If your users only need to query the views but not create audit policies, then grant them the AUDIT_VIEWER role.
When the database is writeable, audit records are written to the unified audit trail. If the database is not writable, then audit records are written to new format operating system files in the $ORACLE_BASE/audit/$ORACLE_SID directory.
Moving Operating System Audit Records into the Unified Audit Trail recommends:
From the previous release, the following modes, deprecated but retained for backward compatibility, are available. However, Oracle recommends that you use the DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS procedure instead, as described in Oracle Database Upgrade Guide.
This is no mention of DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS in either Oracle® 12.1 Database Upgrade Guide or Oracle® 12.1 Database PL/SQL Packages and Types Reference.
This procedure does not exist in Oracle RDBMS 12.1:
SQL>c DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS BEGIN DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS; END; * ERROR at line 1: ORA-06550: line 1, column 23: PLS-00302: component 'TRANSFER_UNIFIED_AUDIT_RECORDS' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored
According to How To Transfer Unified Audit Records To An Internal Relational Table (Doc ID 2212196.1),
The procedure is available out of the box in the Oracle RDBMS 12.2 database and in the Oracle RDBMS 12.1.0.2 databases that have been patched with Patch 25985768.
Followed the procedure in 4.6.13.2 Migrating to Unified Auditing for Oracle Database:
[oracle@personal ~]$ lsnrctl stop
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 15-APR-2018 21:30:19
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
The command completed successfully
[oracle@personal ~]$ . oraenv
ORACLE_SID = [personal] ? jar
The Oracle base remains unchanged with value /opt/app/oracle
[oracle@personal ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 15 21:31:07 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1543503872 bytes
Fixed Size 2924736 bytes
Variable Size 939528000 bytes
Database Buffers 587202560 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
VALUE
----------------------------------------------------------------
FALSE
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@personal ~]$ cd ${ORACLE_HOME}/rdbms/lib
[oracle@personal lib]$ make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME
/usr/bin/ar d /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/libknlopt.a kzanang.o
/usr/bin/ar cr /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/libknlopt.a /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/kzaiang.o
chmod 755 /opt/app/oracle/product/12.1.0/dbhome_1/bin
- Linking Oracle
rm -f /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/oracle
/opt/app/oracle/product/12.1.0/dbhome_1/bin/orald -o /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/oracle -m64 -z noexecstack -Wl,--disable-new-dtags -L/opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/ -L/opt/app/oracle/product/12.1.0/dbhome_1/lib/ -L/opt/app/oracle/product/12.1.0/dbhome_1/lib/stubs/ -Wl,-E /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/opimai.o /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/ssoraed.o /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/ttcsoi.o -Wl,--whole-archive -lperfsrv12 -Wl,--no-whole-archive /opt/app/oracle/product/12.1.0/dbhome_1/lib/nautab.o /opt/app/oracle/product/12.1.0/dbhome_1/lib/naeet.o /opt/app/oracle/product/12.1.0/dbhome_1/lib/naect.o /opt/app/oracle/product/12.1.0/dbhome_1/lib/naedhs.o /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/config.o -lserver12 -lodm12 -lcell12 -lnnet12 -lskgxp12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lclient12 -lvsn12 -lcommon12 -lgeneric12 -lknlopt `if /usr/bin/ar tv /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap12" ; fi` -lskjcx12 -lslax12 -lpls12 -lrt -lplp12 -lserver12 -lclient12 -lvsn12 -lcommon12 -lgeneric12 `if [ -f /opt/app/oracle/product/12.1.0/dbhome_1/lib/libavserver12.a ] ; then echo "-lavserver12" ; else echo "-lavstub12"; fi` `if [ -f /opt/app/oracle/product/12.1.0/dbhome_1/lib/libavclient12.a ] ; then echo "-lavclient12" ; fi` -lknlopt -lslax12 -lpls12 -lrt -lplp12 -ljavavm12 -lserver12 -lwwg `cat /opt/app/oracle/product/12.1.0/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnro12 `cat /opt/app/oracle/product/12.1.0/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnnzst12 -lzt12 -lztkg12 -lmm -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lztkg12 `cat /opt/app/oracle/product/12.1.0/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnro12 `cat /opt/app/oracle/product/12.1.0/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnnzst12 -lzt12 -lztkg12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 `if /usr/bin/ar tv /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo12 -lserver12"; fi` -L/opt/app/oracle/product/12.1.0/dbhome_1/ctx/lib/ -lctxc12 -lctx12 -lzx12 -lgx12 -lctx12 -lzx12 -lgx12 -lordimt12 -lclsra12 -ldbcfg12 -lhasgen12 -lskgxn2 -lnnzst12 -lzt12 -lxml12 -locr12 -locrb12 -locrutl12 -lhasgen12 -lskgxn2 -lnnzst12 -lzt12 -lxml12 -lgeneric12 -loraz -llzopro -lorabz2 -lipp_z -lipp_bz2 -lippdcemerged -lippsemerged -lippdcmerged -lippsmerged -lippcore -lippcpemerged -lippcpmerged -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lsnls12 -lunls12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lasmclnt12 -lcommon12 -lcore12 -laio -lons `cat /opt/app/oracle/product/12.1.0/dbhome_1/lib/sysliblist` -Wl,-rpath,/opt/app/oracle/product/12.1.0/dbhome_1/lib -lm `cat /opt/app/oracle/product/12.1.0/dbhome_1/lib/sysliblist` -ldl -lm -L/opt/app/oracle/product/12.1.0/dbhome_1/lib
test ! -f /opt/app/oracle/product/12.1.0/dbhome_1/bin/oracle ||\
mv -f /opt/app/oracle/product/12.1.0/dbhome_1/bin/oracle /opt/app/oracle/product/12.1.0/dbhome_1/bin/oracleO
mv /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/oracle /opt/app/oracle/product/12.1.0/dbhome_1/bin/oracle
chmod 6751 /opt/app/oracle/product/12.1.0/dbhome_1/bin/oracle
[oracle@personal lib]$ lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 15-APR-2018 21:40:51
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /opt/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /opt/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /opt/app/oracle/diag/tnslsnr/personal/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=personal)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 15-APR-2018 21:40:52
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /opt/app/oracle/diag/tnslsnr/personal/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=personal)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@personal lib]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 15 21:41:02 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1543503872 bytes
Fixed Size 2924736 bytes
Variable Size 939528000 bytes
Database Buffers 587202560 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
VALUE
----------------------------------------------------------------
TRUE
SQL>
Summary:
Stop all listeners and database instances
Rebuild the kernel using the uniaud_on option
Start all listeners and database instances
This scenario is based upon Moving Operating System Audit Records into the Unified Audit Trail.
Open Database as Read-Only
First, I open the database as read-only:
[oracle@personal ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 19 12:23:38 2018
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> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open read only
ORACLE instance started.
Total System Global Area 1543503872 bytes
Fixed Size 2924736 bytes
Variable Size 939528000 bytes
Database Buffers 587202560 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
Generate OS Audit Records
Next, I generate some audit records:
SQL> connect system
Enter password:
ERROR:
ORA-28032: Your password has expired and the database is set to read-only
Warning: You are no longer connected to ORACLE.
SQL> connect / as sysdba
Connected.
SQL> connect demo
Enter password:
Connected.
Next, I check for the generation of OS Audit records:
SQL> !ls -lth /opt/app/oracle/audit/personal/
total 104K
-rw-r-----. 1 oracle oinstall 10K Apr 19 12:24 ora_audit_11.bin
-rw-r-----. 1 oracle oinstall 6.0K Apr 19 12:24 ora_audit_00.bin
-rw-r-----. 1 oracle oinstall 11K Apr 19 12:24 ora_audit_10.bin
-rw-r-----. 1 oracle oinstall 1.5K Apr 19 12:24 ora_audit_092.bin
-rw-r-----. 1 oracle oinstall 1.5K Apr 19 12:24 ora_audit_192.bin
-rw-r-----. 1 oracle oinstall 8.5K Apr 19 08:44 ora_audit_141.bin
-rw-r-----. 1 oracle oinstall 1.5K Apr 18 23:12 ora_audit_082.bin
-rw-r-----. 1 oracle oinstall 1.5K Apr 18 23:12 ora_audit_182.bin
-rw-r-----. 1 oracle oinstall 2.0K Apr 17 21:28 ora_audit_087.bin
-rw-r-----. 1 oracle oinstall 2.5K Apr 17 21:28 ora_audit_187.bin
-rw-r-----. 1 oracle oinstall 3.5K Apr 17 18:13 ora_audit_973707194_3058604836.bin
-rw-r-----. 1 oracle oinstall 1.5K Apr 17 12:46 ora_audit_070.bin
-rw-r-----. 1 oracle oinstall 1.5K Apr 17 12:45 ora_audit_170.bin
-rw-r-----. 1 oracle oinstall 3.5K Apr 17 07:27 ora_audit_973668475_2422469920.bin
-rw-r-----. 1 oracle oinstall 4.0K Apr 16 19:22 ora_audit_143.bin
-rw-r-----. 1 oracle oinstall 8.5K Apr 16 19:21 ora_audit_973624908_1453341488.bin
-rw-r-----. 1 oracle oinstall 1.5K Oct 4 2015 ora_audit_159.bin
SQL> !file /opt/app/oracle/audit/personal/ora_audit_11.bin
/opt/app/oracle/audit/personal/ora_audit_11.bin: data
Open Database as Read-Write
Next, open the database as read-write:
SQL> connect / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open read write
ORACLE instance started.
Total System Global Area 1543503872 bytes
Fixed Size 2924736 bytes
Variable Size 939528000 bytes
Database Buffers 587202560 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
Load OS Audit Records into Audit Table
Now, load the OS audit records into the audit table using the DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES procedure:
SQL> select count(*) from unified_audit_trail;
COUNT(*)
----------
26393
SQL> exec DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES
PL/SQL procedure successfully completed.
SQL> select count(*) from unified_audit_trail;
COUNT(*)
----------
26410
SQL> !ls -lth /opt/app/oracle/audit/personal/
total 0
Display Audit Log
I ran the following SQL to get the audit entries for the above scenario:
SELECT event_timestamp,
dbusername,
audit_type,
action_name,
sql_text,
unified_audit_policies
FROM unified_audit_trail
WHERE event_timestamp > systimestamp - interval '6' hour
ORDER BY event_timestamp
The result was:
Followed the procedure in Manually Flushing Audit Records to the Audit Trail in Queued-Write Mode.
Flushing audit records is only applicable if the Audit Write Mode is set to Queued.
Check Audit Write Mode
The following snippet of code shows that the Audit Write Mode is set to Queued:
COLUMN parameter_name FORMAT A30
COLUMN parameter_value FORMAT A30
SELECT parameter_name, parameter_value
FROM DBA_AUDIT_MGMT_CONFIG_PARAMS
WHERE parameter_name = 'AUDIT WRITE MODE'
AND audit_trail = 'UNIFIED AUDIT TRAIL'
/
The output is:
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ------------------------------
AUDIT WRITE MODE QUEUED WRITE MODE
Manually Flush Audit Records
The DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL procedure is called to manually flush the audit records to disk:
EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
The output is simply:
PL/SQL procedure successfully completed.
Check Audit Write Mode
The following snippet of code shows that the Audit Write Mode is set to Queued:
COLUMN parameter_name FORMAT A30
COLUMN parameter_value FORMAT A30
SELECT parameter_name, parameter_value
FROM DBA_AUDIT_MGMT_CONFIG_PARAMS
WHERE parameter_name = 'AUDIT WRITE MODE'
AND audit_trail = 'UNIFIED AUDIT TRAIL'
/
The output is:
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ------------------------------
AUDIT WRITE MODE QUEUED WRITE MODE
Switch Audit Write Mode to Immediate
The DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure is called to set the Audit Write Mode to Immediate:
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE,
DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE);
END;
/
The output is simply:
PL/SQL procedure successfully completed.
Check Audit Write Mode
The following snippet of code shows that the Audit Write Mode is now set to Immediate:
COLUMN parameter_name FORMAT A30
COLUMN parameter_value FORMAT A30
SELECT parameter_name, parameter_value
FROM DBA_AUDIT_MGMT_CONFIG_PARAMS
WHERE parameter_name = 'AUDIT WRITE MODE'
AND audit_trail = 'UNIFIED AUDIT TRAIL'
/
The output is:
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ------------------------------
AUDIT WRITE MODE IMMEDIATE WRITE MODE
Switch Audit Write Mode to Queued
The DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure is called to set the Audit Write Mode to Queued:
COLUMN parameter_name FORMAT A30
COLUMN parameter_value FORMAT A30
SELECT parameter_name, parameter_value
FROM DBA_AUDIT_MGMT_CONFIG_PARAMS
WHERE parameter_name = 'AUDIT WRITE MODE'
AND audit_trail = 'UNIFIED AUDIT TRAIL'
/
The output is simply:
PL/SQL procedure successfully completed.
Check Audit Write Mode
The following snippet of code shows that the Audit Write Mode is now set to Queued:
COLUMN parameter_name FORMAT A30
COLUMN parameter_value FORMAT A30
SELECT parameter_name, parameter_value
FROM DBA_AUDIT_MGMT_CONFIG_PARAMS
WHERE parameter_name = 'AUDIT WRITE MODE'
AND audit_trail = 'UNIFIED AUDIT TRAIL'
/
The output is:
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ------------------------------
AUDIT WRITE MODE QUEUED WRITE MODE
Alternative Method to Get Audit Write Mode
The following snippet of code shows that the Audit Write Mode can be gotten by a PL/SQL procedure call to DBMS_AUDIT_MGMT.GET_AUDIT_TRAIL_PROPERTY_VALUE:
VARIABLE write_mode NUMBER
BEGIN
:write_mode
:= dbms_audit_mgmt.get_audit_trail_property_value(
audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
audit_trail_property => dbms_audit_mgmt.audit_trail_write_mode
);
END;
/
PRINT write_mode
The output is:
PL/SQL procedure successfully completed.
WRITE_MODE
----------
1
These values are not in the online documentation. However, by examining the package definition, one finds the following snippet of code:
--
-- NG Audit Trail write mode configuration
AUDIT_TRAIL_WRITE_MODE CONSTANT NUMBER := 33;
-- Values for Write mode
AUDIT_TRAIL_QUEUED_WRITE CONSTANT NUMBER := 1;
AUDIT_TRAIL_IMMEDIATE_WRITE CONSTANT NUMBER := 2;
--AUDIT_TRAIL_COMMIT_WRITE CONSTANT NUMBER := 3;
Thus, 1 corresponds to Queued Write.