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

Change Sysman Password in OEM repository

Recently I come across the issue when sysman password was changed as it was expired based on password policy.

We have changed the password for sysman user in database using the following command .

alter user sysmand identified by new_password;

then we check the status of OMS and it failed with following error , which is genuine.

[oracle@exadata-an-ora-oem middleware]$ emctl status oms
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
WebTier is Up
Oracle Management Server is not functioning because of the following reason:
Connection to the repository failed. Verify that the repository connection information provided is correct.
Check EM Server log file for details: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/logs/EMGC_OMS1.out
JVMD Engine is Down
BI Publisher Server is Down



Error message above itself is explanatory  what the issue is with OMS.  Even if you haven't changed the sysman password itself and you come across this issue, you can easily figure out OMS is not able to connect to repository and so something wrong with either database/listener/ or password.

i checked database was running, so was listener, so we pretty much knew the it was database for sure.

Fix:
update the new password in repository.

emctl config oms -list_repos_details

You have two option.

Option1: if you don't know the old sysman password.

[oracle@oemserver middleware]$ emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd sys_user_password -new_pwd new_password_4_sysman

Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.

Changing passwords in backend ...
Passwords changed in backend successfully.
Updating repository password in Credential Store...
Successfully updated Repository password in Credential Store.
Restart all the OMSs using 'emctl stop oms -all' and 'emctl start oms'.
Successfully changed repository password.

OR

Option2: You know sysman old password

[oracle@oemserver middleware]$ emctl config oms -change_repos_pwd -old_pwd sysmanoldpassword -new_pwd mynewpassword
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.

Changing passwords in backend ...
Passwords changed in backend successfully.
Updating repository password in Credential Store...
Successfully updated Repository password in Credential Store.
Restart all the OMSs using 'emctl stop oms -all' and 'emctl start oms'.
Successfully changed repository password.

Reference :
       emctl config oms -change_repos_pwd [-old_pwd ] [-new_pwd ] [-use_sys_pwd [-sys_pwd ]]
          Note: Steps in changing Enterprise Manager Root (SYSMAN) password are:
                1) Stop all the OMSs using 'emctl stop oms'
                2) Run 'emctl config oms -change_repos_pwd' on one of the OMSs
                3) Restart all the OMSs using 'emctl stop oms -all' and 'emctl start oms'