In this post, I will not show you all advantages related to Oracle Unified Auditing. For that, read the oracle documentation.

You can also read the blogs written by my colleagues :

I just want to share with you the steps needed to move from Standard Auditing to Unified Auditing in a mutlitenant environment.

Steps 1 : Check if Unified Auditing is disabled

oracle@*****:/u01/app/oracle/product/rdbms19_1/rdbms/lib/ [MYCDB] sqh
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 4 10:45:35 2021
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

SQL> SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

VALUE
----------------------------------------------------------------
FALSE

 

FALSE means unified audit trail and audit policy functionality is enabled in mixed mode

Step 2 : As we want to disabled Standard Auditing and enabled Unifed Auditing, let’s check the audit_file_dest parameter

SQL> sho parameter audit_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/xxxx/adump

Step 4 : Let’s check the folder /u01/app/oracle/admin/xxxx/adump

ls -ltr /u01/app/oracle/admin/CSYR01I/adump
...
-rw-r-----. 1 oracle dba     1090 Oct  4 10:55 MYCDB_m001_19948_202105041945FHDFJDHF888912.aud
-rw-r-----. 1 oracle dba     1090 Oct  4 10:55 MYCDB_m001_19948_202FàDéFàDéDFéàD0527767763.aud
-rw-r-----. 1 oracle dba     1090 oct  4 10:55 MYCDB_m001_19948_20210504FHDJFHDJHFD9851253.aud
-rw-r-----. 1 oracle dba     1090 Oct  4 10:55 MYCDB_m001_19948_202105041FLDKFLDKDL4960612.aud
-rw-r-----. 1 oracle dba    40985 Oct  4 10:56 MYCDB_m003_20296_2021050FDJFDKJD54384117793.aud
-rw-r-----. 1 oracle dba     2504 Oct  4 10:59 MYCDB_ora_32211_202105041FKDJFDDKJFD9350382.aud

 

Step 5 : Stop all listeners and databases sharing the ORACLE_HOME

oracle@xxxxxxxxx:/u01/app/oracle/product/rdbms19_1/rdbms/lib/ [MYCDB] lsnrctl stop LISTENER
oracle@xxxxxxxxx:/u01/app/oracle/product/rdbms19_1/rdbms/lib/ [MYCDB] sqh
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 04 14:11:37 2021
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

 

Step 6 : To enable the Unified Auditng, we have to relink the file $ORACLE_HOME/rdbms/lib/ins_rdbms.mk

oracle@xxxxxx:/u01/app/oracle/product/rdbms19_1/rdbms/lib/ [MYCDB] make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME
/usr/bin/ar d /u01/app/oracle/product/rdbms19_1/rdbms/lib/libknlopt.a kzanang.o
/usr/bin/ar cr /u01/app/oracle/product/rdbms19_1/rdbms/lib/libknlopt.a /u01/app/oracle/product/rdbms19_1/rdbms/lib/kzaiang.o
chmod 755 /u01/app/oracle/product/rdbms19_1/bin
- Linking Oracle
rm -f /u01/app/oracle/product/rdbms19_1/rdbms/lib/oracle
/u01/app/oracle/product/rdbms19_1/bin/orald -o /u01/app/oracle/product/rdbms19_1/rdbms/lib/oracle -m64 -z noexecstack -Wl,--disable-new-dtags -L/u01/app/oracle/product/rdbms19_1/rdbms/lib/ -L/u01/app/oracle/product/rdbms19_1/lib/ -L/u01/app/oracle/product/rdbms19_1/lib/stubs/ -Wl,-E /u01/app/oracle/product/rdbms19_1/rdbms/lib/opimai.o /u01/app/oracle/product/rdbms19_1/rdbms/lib/ssoraed.o /u01/app/oracle/product/rdbms19_1/rdbms/lib/ttcsoi.o -Wl,--whole-archive -lperfsrv19 -Wl,--no-whole-archive /u01/app/oracle/product/rdbms19_1/lib/nautab.o /u01/app/oracle/product/rdbms19_1/lib/naeet.o /u01/app/oracle/product/rdbms19_1/lib/naect.o /u01/app/oracle/product/rdbms19_1/lib/naedhs.o /u01/app/oracle/product/rdbms19_1/rdbms/lib/config.o -ldmext -lserver19 -lodm19 -lofs -lcell19 -lnnet19 -lskgxp19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lclient19 -lvsnst19 -lcommon19 -lgeneric19 -lknlopt -loraolap19 -lskjcx19 -lslax19 -lpls19 -lrt -lplp19 -ldmext -lserver19 -lclient19 -lvsnst19 -lcommon19 -lgeneric19 `if [ -f /u01/app/oracle/product/rdbms19_1/lib/libavserver19.a ] ; then echo "-lavserver19" ; else echo "-lavstub19"; fi` `if [ -f /u01/app/oracle/product/rdbms19_1/lib/libavclient19.a ] ; then echo "-lavclient19" ; fi` -lknlopt -lslax19 -lpls19 -lrt -lplp19 -ljavavm19 -lserver19 -lwwg `cat /u01/app/oracle/product/rdbms19_1/lib/ldflags` -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnro19 `cat /u01/app/oracle/product/rdbms19_1/lib/ldflags` -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnnzst19 -lzt19 -lztkg19 -lmm -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lztkg19 `cat /u01/app/oracle/product/rdbms19_1/lib/ldflags` -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnro19 `cat /u01/app/oracle/product/rdbms19_1/lib/ldflags` -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnnzst19 -lzt19 -lztkg19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 `if /usr/bin/ar tv /u01/app/oracle/product/rdbms19_1/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo19 -lserver19"; fi` -L/u01/app/oracle/product/rdbms19_1/ctx/lib/ -lctxc19 -lctx19 -lzx19 -lgx19 -lctx19 -lzx19 -lgx19 -lclscest19 -loevm -lclsra19 -ldbcfg19 -lhasgen19 -lskgxn2 -lnnzst19 -lzt19 -lxml19 -lgeneric19 -locr19 -locrb19 -locrutl19 -lhasgen19 -lskgxn2 -lnnzst19 -lzt19 -lxml19 -lgeneric19 -lgeneric19 -lorazip -loraz -llzopro5 -lorabz2 -lorazstd -loralz4 -lipp_z -lipp_bz2 -lippdc -lipps -lippcore -lippcp -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lsnls19 -lunls19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lasmclnt19 -lcommon19 -lcore19 -ledtn19 -laio -lons -lmql1 -lipc1 -lfthread19 `cat /u01/app/oracle/product/rdbms19_1/lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/rdbms19_1/lib -lm `cat /u01/app/oracle/product/rdbms19_1/lib/sysliblist` -ldl -lm -L/u01/app/oracle/product/rdbms19_1/lib `test -x /usr/bin/hugeedit -a -r /usr/lib64/libhugetlbfs.so && test -r /u01/app/oracle/product/rdbms19_1/rdbms/lib/shugetlbfs.o && echo -Wl,-zcommon-page-size=2097152 -Wl,-zmax-page-size=2097152 -lhugetlbfs`
rm -f /u01/app/oracle/product/rdbms19_1/bin/oracle
mv /u01/app/oracle/product/rdbms19_1/rdbms/lib/oracle /u01/app/oracle/product/rdbms19_1/bin/oracle
chmod 6751 /u01/app/oracle/product/rdbms19_1/bin/oracle
(if [ ! -f /u01/app/oracle/product/rdbms19_1/bin/crsd.bin ]; then \
getcrshome="/u01/app/oracle/product/rdbms19_1/srvm/admin/getcrshome" ; \
if [ -f "$getcrshome" ]; then \
crshome="`$getcrshome`"; \
if [ -n "$crshome" ]; then \
if [ $crshome != /u01/app/oracle/product/rdbms19_1 ]; then \
oracle="/u01/app/oracle/product/rdbms19_1/bin/oracle"; \
$crshome/bin/setasmgidwrap oracle_binary_path=$oracle; \
fi \
fi \
fi \
fi\
);

 

Step 7 : Restart the listener and the databases and check if Unified Auditing is enabled from the container database (CDB) and from the pluggable databases (PDB)

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
oracle@xxxxxx:/u01/app/oracle/product/rdbms19_1/rdbms/lib/ [MYCDB] sqh
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 04 14:26:04 2021
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

SQL> select value from v$option where lower(parameter)='unified auditing';

VALUE
----------------------------------------------------------------
TRUE

SQL> alter session set container=MYPDB;

Session altered.

SQL> select value from v$option where lower(parameter)='unified auditing';

VALUE
----------------------------------------------------------------
TRUE

Now we have Unified Pure Auditing without traditional auditing, than means that AUDIT* spfile parameters are no more taking account and the *.aud files are no more generated.

 

Step 8 : Set the last archive timestamp for audit records stored into the table unified_audit_trail (below we remove all rows with timestamp > 5 days)

BEGIN
     DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type     => DBMS_AUDIT_MGMT.audit_trail_unified,
    last_archive_time    => SYSTIMESTAMP-5,
    --rac_instance_number  =>  1,
    container            => DBMS_AUDIT_MGMT.container_current
  );
 END;
/

PL/SQL procedure successfully completed.

COLUMN audit_trail FORMAT A20
COLUMN last_archive_ts FORMAT A40
SELECT audit_trail,
       last_archive_ts
  FROM   dba_audit_mgmt_last_arch_ts;

AUDIT_TRAIL          LAST_ARCHIVE_TS
-------------------- ----------------------------------------
UNIFIED AUDIT TRAIL  04-OCT-21 15.24.32.000000 PM +00:00

Step 9 : Create and schedule the purge jobs PURGE_ALL_AUDIT_TRAILS

BEGIN
  DBMS_AUDIT_MGMT.create_purge_job(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    audit_trail_purge_interval => 24 /* hours */,
    audit_trail_purge_name     => 'PURGE_ALL_AUDIT_TRAILS',
    use_last_arch_timestamp    => TRUE );
END;
/

PL/SQL procedure successfully completed.

SELECT job_action
FROM   dba_scheduler_jobs
WHERE  job_name = 'PURGE_ALL_AUDIT_TRAILS';

JOB_ACTION
--------------------------------------------------------------------------------
BEGIN AUDSYS.DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(15, TRUE, 1);  END;

SQL>

My customer wanted to audit all LOGON (even FAILS LOGIN) and LOGOFF for all users including proxy users.

Create the audit policy LOG_ON_OFF for actions LOGON and LOGOFF and enable it :

conn /as sysdba
CREATE AUDIT POLICY LOG_ON_OFF ACTIONS LOGON,LOGOFF;
AUDIT POLICY LOG_ON_OFF;
SQL> alter session set container=MYPDB;

Session altered.

col user_name format A10
col policy_name format A18
select POLICY_NAME, ENABLED_OPTION, ENTITY_NAME, SUCCESS, FAILURE
from AUDIT_UNIFIED_ENABLED_POLICIES
  3  where POLICY_NAME ='LOG_ON_OFF';

POLICY_NAME		ENABLED_OPTION		ENTITY_NAME		SUCCESS FAILURE
--- ------ ------ ------ ------ ------ ------ ------ ------ ---
LOG_ON_OFF		BY USER				ALL USERS		YES 	YES

 

Let’s simulate a fails connection through a proxy users :

oracle@server:/home/oracle/ [MYCDB (CDB$ROOT)] sqlplus xfelahi[xxxxx]/"******"@server.domain.name/pdb.domain.name

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 5 16:07:37 2021
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Let’s check the Unified Auditing records into the table unified_audit_trail