How to Enable Unified Auditing in Oracle 12c database

Unified Auditing:
Oracle 12c introduced the consolidated way of auditing Oracle database. It introduces the Simplicity with little or minimal overhead to database performance.

It comes with the following features.

• Simplicity
• Consolidation
• Security 
• It rely on read only audit trail table
• It audit all configuration related operations
• Seperation of duties 
• Performance
• Implemented using queue in Oracle SGA, leaving very overhead in database performance

Unified Auditing Architecture

1. User perform auditable action
2. Audit records in SGA based Queue in memory
3. either GEN0 process flush queue to disk on regular interval or you can perform manual flush on demand (EXECUTE SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL)
4. once data flushed to disk, its available via SYS.UNIFIED_AUDIT_TRAIL 

There two mode of setup Queued or

How to Enable :

You need outage to enable unified auditing as it will be done by relinking the Oracle Library.  Shutdown all oracle process before relinking.

oracle@dbserver01:~$. oraenv

ORACLE_SID = [CDB2] ? CDB2

The Oracle base remains unchanged with value /u01/app/oracle

oracle@dbserver01:~$    

oracle@dbserver01:~$  lsnrctl stop 

oracle@dbserver01:~$  sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 22 06:15:47 2017

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 and Real Application Testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.

ORACLE instance shut down.

oracle@dbserver01:~$ cd $ORACLE_HOME/rdbms/lib

oracle@dbserver01:/u01/app/oracle/product/12.1.0.2/rdbms/lib$ make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

/usr/bin/ar d /u01/app/oracle/product/12.1.0.2/rdbms/lib/libknlopt.a kzanang.o

/usr/bin/ar cr /u01/app/oracle/product/12.1.0.2/rdbms/lib/libknlopt.a /u01/app/oracle/product/12.1.0.2/rdbms/lib/kzaian

g.

o

ch

mod 755 /u01/app/oracle/product/12.1.0.2/bin

 - Linking Oracle

rm -f /u01/app/oracle/product/12.1.0.2/rdbms/lib/oracle

/u01/app/oracle/product/12.1.0.2/bin/orald  -o /u01/app/oracle/product/12.1.0.2/rdbms/lib/oracle -m64 -z noexecstack -Wl,--disable-new-dtags -L/u01/app/oracle/product/12.1.0.2/rdbms/lib/ -L/u01/app/oracle/product/12.1.0.2/lib/ -L/u01/app/oracle/product/12.1.0.2/lib/stubs/   -Wl,-E /u01/app/oracle/product/12.1.0.2/rdbms/lib/opimai.o /u01/app/oracle/product/12.1.0.2/rdbms/lib/ssoraed.o /u01/app/oracle/product/12.1.0.2/rdbms/lib/ttcsoi.o -Wl,--whole-archive -lperfsrv12 -Wl,--no-whole-archive /u01/app/oracle/product/12.1.0.2/lib/nautab.o /u01/app/oracle/product/12.1.0.2/lib/naeet.o /u01/app/oracle/product/12.1.0.2/lib/naect.o /u01/app/oracle/product/12.1.0.2/lib/naedhs.o /u01/app/oracle/product/12.1.0.2/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 /u01/app/oracle/product/12.1.0.2/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 /u01/app/oracle/product/12.1.0.2/lib/libavserver12.a ] ; then echo "-lavserver12" ; else echo "-lavstub12"; fi` `if [ -f /u01/app/oracle/product/12.1.0.2/lib/libavclient12.a ] ; then echo "-lavclient12" ; fi` -lknlopt -lslax12 -lpls12  -lrt -lplp12 -ljavavm12 -lserver12  -lwwg  `cat /u01/app/oracle/product/12.1.0.2/lib/ldflags`    -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnro12 `cat /u01/app/oracle/product/12.1.0.2/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 /u01/app/oracle/product/12.1.0.2/lib/ldflags`    -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnro12 `cat /u01/app/oracle/product/12.1.0.2/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 /u01/app/oracle/product/12.1.0.2/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo12 -lserver12"; fi` -L/u01/app/oracle/product/12.1.0.2/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 /u01/app/oracle/product/12.1.0.2/lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/12.1.0.2/lib -lm    `cat /u01/app/oracle/product/12.1.0.2/lib/sysliblist` -ldl -lm   -L/u01/app/oracle/product/12.1.0.2/lib

test ! -f /u01/app/oracle/product/12.1.0.2/bin/oracle ||\

           mv -f /u01/app/oracle/product/12.1.0.2/bin/oracle /u01/app/oracle/product/12.1.0.2/bin/oracleO

mv /u01/app/oracle/product/12.1.0.2/rdbms/lib/oracle /u01/app/oracle/product/12.1.0.2/bin/oracle

chmod 6751 /u01/app/oracle/product/12.1.0.2/bin/oracle

By Default oracle Defined Two policies (ORA_SECURECONFIG and ORA_LOGON_FAILURES)  get enabled. 

Check Current Enabled policy in database by default.

oracle@dbserver01:~$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 22 06:50:28 2017

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> select * from audit_unified_enabled_policies;

USER_NAME                      POLICY_NAME                    ENABLED_ SUC FAI

------------------------------ ------------------------------ -------- --- ---

ALL USERS                      ORA_SECURECONFIG               BY       YES YES

ALL USERS                      ORA_LOGON_FAILURES             BY       NO  YES

Once unified auditing is enable all audit_xx parameters will be ignored and will have no impact.

Auditing can be enabled in two modes

• Queued Write mode (Default ) - In this mode you might loose some audit data in case of instance crash (data which was not flushed to disk at time of instance crash).
• Immediate Write mode - this will ensure no audit data is lost. The audit records are written immediately.

Unified auditing is enabled in Queued Write mode by default to ensure minimal performance overhead.

How to switch mode:

.• Immediate Write mode:

SQL> EXECUTE  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);


• Queued Write mode:

SQL> EXECUTE  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_QUEUED_WRITE);