Do you know what is going on in your Oracle database? Probably yes, because you have implemented auditing and check the audit trail regularly. But do you know what is going on in your Oracle ASM instance? Probably not, because not many in the Oracle wild take care of this one. However, under some circumstance it can be beneficial to know what is going on in your ASM instance. This is where auditing of the ASM instance comes into play. Oracle ASM 19c gives you 3 different methods to configure your audit trail. The first one is Oracle ASM auditing with syslog, the second one is the operating system audit trail and last but not least, you can manage Oracle ASM audit records in the unified audit trail.
In this blog post I will take a closer look at ASM with unified auditing, and how to activate and configure it. To make it more readable, I divided it into two major objectives:
- How to activate unified auditing for an ASM instance
- How to configure some basic purging policies for the unified audit trail
How to activate unified auditing for an ASM instance
To activate unified auditing for an ASM instance, we need to do a few steps, and we will need downtime too, because we need to relink the oracle binary. Please notice that unified auditing with ASM is turned off per default.
SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing'; VALUE ---------------------------------------------------------------- FALSE SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u01/grid/19c/rdbms/audit audit_sys_operations boolean TRUE audit_syslog_level string LOCAL0.INFO audit_trail string NONE unified_audit_sga_queue_size integer 1048576 SQL>
To enable the unified audit trail with ASM, we need to stop HAS and relink the ASM oracle binary afterwards.
[oracle@ocm200 ~]$ crsctl stop has CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'ocm200' CRS-2673: Attempting to stop 'ora.evmd' on 'ocm200' CRS-2673: Attempting to stop 'ora.DATA.dg' on 'ocm200' CRS-2673: Attempting to stop 'ora.FRA.dg' on 'ocm200' CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'ocm200' CRS-2677: Stop of 'ora.FRA.dg' on 'ocm200' succeeded CRS-2677: Stop of 'ora.DATA.dg' on 'ocm200' succeeded CRS-2673: Attempting to stop 'ora.asm' on 'ocm200' CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'ocm200' succeeded CRS-2677: Stop of 'ora.evmd' on 'ocm200' succeeded CRS-2677: Stop of 'ora.asm' on 'ocm200' succeeded CRS-2673: Attempting to stop 'ora.cssd' on 'ocm200' CRS-2677: Stop of 'ora.cssd' on 'ocm200' succeeded CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'ocm200' has completed CRS-4133: Oracle High Availability Services has been stopped.
But take care of the permissions of the $GI_HOME/bin directory, if you want to avoid some ugly errors.
... chmod 755 /u01/grid/19c/bin chmod: changing permissions of '/u01/grid/19c/bin': Operation not permitted make: [ins_rdbms.mk:540: preinstall] Error 1 (ignored) ...
After changing the permissions of the $GI_HOME/bin directory, you should be able to relink the oracle binary with unified auditing enabled.
[oracle@ocm200 19c]$ ls -l | grep bin
drwxr-x---. 2 root oinstall 12288 Oct 28 10:32 bin
[oracle@ocm200 19c]$ sudo chmod 770 bin/
[oracle@ocm200 19c]$ sudo chown oracle bin/
[oracle@ocm200 19c]$ ls -l | grep bin
drwxrwx---. 2 oracle oinstall 12288 Oct 28 10:32 bin
[oracle@ocm200 lib]$ make -f ins_rdbms.mk uniaud_on ioracle
/usr/bin/ar cr /u01/grid/19c/rdbms/lib/libknlopt.a /u01/grid/19c/rdbms/lib/kzaiang.o
chmod 755 /u01/grid/19c/bin
- Linking Oracle
rm -f /u01/grid/19c/rdbms/lib/oracle
/u01/grid/19c/bin/orald -o /u01/grid/19c/rdbms/lib/oracle -m64 -z noexecstack -Wl,--disable-new-dtags -L/u01/grid/19c/rdbms/lib/ -L/u01/grid/19c/lib/ -L/u01/grid/19c/lib/stubs/
-Wl,-E /u01/grid/19c/rdbms/lib/opimai.o /u01/grid/19c/rdbms/lib/ssoraed.o /u01/grid/19c/rdbms/lib/ttcsoi.o -Wl,--whole-archive -lperfsrv19 -Wl,--no-whole-archive /u01/grid/19c/lib/nautab.o
/u01/grid/19c/lib/naeet.o /u01/grid/19c/lib/naect.o /u01/grid/19c/lib/naedhs.o /u01/grid/19c/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/grid/19c/lib/libavserver19.a ] ; then echo "-lavserver19" ;
else echo "-lavstub19"; fi` `if [ -f /u01/grid/19c/lib/libavclient19.a ] ; then echo "-lavclient19" ; fi` -lknlopt -lslax19 -lpls19 -lrt -lplp19 -ljavavm19 -lserver19 -lwwg
`cat /u01/grid/19c/lib/ldflags` -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnro19 `cat /u01/grid/19c/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/grid/19c/lib/ldflags` -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnro19 `cat /u01/grid/19c/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/grid/19c/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo19 -lserver19"; fi` -L/u01/grid/19c/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/grid/19c/lib/sysliblist` -Wl,-rpath,/u01/grid/19c/lib
-lm `cat /u01/grid/19c/lib/sysliblist` -ldl -lm -L/u01/grid/19c/lib `test -x /usr/bin/hugeedit -a -r /usr/lib64/libhugetlbfs.so && test -r /u01/grid/19c/rdbms/lib/shugetlbfs.o && echo
-Wl,-zcommon-page-size=2097152 -Wl,-zmax-page-size=2097152 -lhugetlbfs`
rm -f /u01/grid/19c/bin/oracle
mv /u01/grid/19c/rdbms/lib/oracle /u01/grid/19c/bin/oracle
chmod 6751 /u01/grid/19c/bin/oracle
(if [ ! -f /u01/grid/19c/bin/crsd.bin ]; then \
getcrshome="/u01/grid/19c/srvm/admin/getcrshome" ; \
if [ -f "$getcrshome" ]; then \
crshome="`$getcrshome`"; \
if [ -n "$crshome" ]; then \
if [ $crshome != /u01/grid/19c ]; then \
oracle="/u01/grid/19c/bin/oracle"; \
$crshome/bin/setasmgidwrap oracle_binary_path=$oracle; \
fi \
fi \
fi \
fi\
);
[oracle@ocm200 19c]$ sudo chown root bin/
[oracle@ocm200 19c]$ sudo chmod 750 bin/
[oracle@ocm200 19c]$
[oracle@ocm200 19c]$ ls -l | grep bin
drwxr-x---. 2 root oinstall 12288 Nov 2 07:42 bin
[oracle@ocm200 ~]$ crsctl start has
CRS-4123: Oracle High Availability Services has been started.
Cool, now we got unified auditing enabled for our ASM instance.
SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing'; VALUE ---------------------------------------------------------------- TRUE
After the startup of ASM, you should already see the first unified audit trail entries, which are
exposed via the v$unified_audit_trail view.
SQL> select count(*) from v$unified_audit_trail;
COUNT(*)
----------
44
And you will see the audit*bin files as well.
[oracle@ocm200 +ASM]$ pwd /u01/app/oracle/audit/+ASM [oracle@ocm200 +ASM]$ ls -la total 52 drwxr-x---. 2 oracle oinstall 158 Nov 2 07:52 . drwxr-x---. 4 oracle oinstall 29 Nov 2 07:44 .. -rw-------. 1 oracle oinstall 1024 Nov 2 07:44 ora_audit_00.bin -rw-------. 1 oracle oinstall 10752 Nov 2 07:51 ora_audit_0148.bin -rw-------. 1 oracle oinstall 13312 Nov 2 07:52 ora_audit_0289.bin -rw-------. 1 oracle oinstall 9728 Nov 2 07:44 ora_audit_0424.bin -rw-------. 1 oracle oinstall 3584 Nov 2 07:44 ora_audit_0430.bin -rw-------. 1 oracle oinstall 3072 Nov 2 07:52 ora_audit_06.bin
How to configure some basic purging policies for the unified audit trail
After we have compiled the oracle binary, we can start with the unified audit configuration.
Oracle 19c delivers a whole bunch of tools for managing the audit trail. In total there are 16 tools and all of them are part of the ASMCMD command.
[table “1” not found /]
Let’s start with one of them, to set max age to 14 days and max size to 1048576 KB for unified auditing
ASMCMD> audsetproperty --file_max_age --val 14 --uni ASMCMD> audsetproperty --file_max_size --val 1048576 --uni
In case you prefer OS auditing, you simply need to switch from –uni to –os.
ASMCMD> audsetproperty --file_max_age --val 14 --os
ASMCMD> audsetproperty --file_max_size --val 1048576 --os
ASMCMD> lsaudconfigparams
Type Parameter Name Parameter Value Parameter Unit
FILE MAX SIZE 1048576 KB
FILE MAX SIZE 1048576 KB
FILE MAX AGE 14 Day(s)
FILE MAX AGE 14 Day(s)
AUDIT TRACE LEVEL ERROR
AUDFILE_DISKGROUP
ASMCMD>
To set the timestamp used by audit purge job and audcleanaudittrail use the audsettimestamp command.
ASMCMD> audsettimestamp --uni 02-NOV-2020 14:00:00 ASMCMD> ASMCMD> audshowtimestamp Type Last Archived UNI 02-NOV-20 02.00.00.000000 PM +00:00 ASMCMD>
Now we can create the audit purge job for the ASM audit files. This can be achieved by issuing the audcreatejob command, which comes with the following options.
Description
The options for audcreatejob command are described below
--name - to specify name of the purge job
<job_name> - name of the purge job
--int - to specify purge interval in hours
<purge_int> - interval used to purge
--os - audit files of OS trail type
--uni - audit files of unified audit trail type
--no_use - ignore the timestamp specified earlier
ASMCMD> audcreatejob --name ASMAUDITPURGE --int 12 --uni ASMCMD> ASMCMD> lsaudcleanupjobs Type Name Status Frequency UNI ASMAUDITPURGE ENABLED 12
Or in case of a space pressure, you can manually delete all unified audit trail records one shot.
[oracle@ocm200 +ASM]$ ls -l total 76 -rw-------. 1 oracle oinstall 1024 Nov 2 07:44 ora_audit_00.bin -rw-------. 1 oracle oinstall 20480 Nov 2 08:03 ora_audit_0148.bin -rw-------. 1 oracle oinstall 28160 Nov 2 08:04 ora_audit_0289.bin -rw-------. 1 oracle oinstall 9728 Nov 2 07:44 ora_audit_0424.bin -rw-------. 1 oracle oinstall 3584 Nov 2 07:44 ora_audit_0430.bin -rw-------. 1 oracle oinstall 6656 Nov 2 08:03 ora_audit_06.bin [oracle@ocm200 +ASM]$ ASMCMD> audcleanaudittrail --uni ASMCMD> [oracle@ocm200 +ASM]$ [oracle@ocm200 +ASM]$ ll total 4 -rw-------. 1 oracle oinstall 2560 Nov 2 08:04 ora_audit_0289.bin [oracle@ocm200 +ASM]$
As mention beforehand, the unified audit trails are exposed via the v$unified_audit_trail. Via this view you can scan for any suspicious behavior in your ASM instance.
SQL> set linesize 144
SQL> set pagesize 1024
SQL> col os_user format a8
SQL> col sql_text format a88
SQL> col EVENT_TIMESTAMP format a28
SQL> set long 10000
SQL> SELECT OS_USER, EVENT_TIMESTAMP, SQL_TEXT FROM v$unified_audit_trail ORDER BY EVENT_TIMESTAMP;
OS_USER EVENT_TIMESTAMP SQL_TEXT
-------- ---------------------------- ----------------------------------------------------------------------------------------
oracle 02-NOV-20 01.04.41.000650 PM DECLARE
bool_val BOOLEAN;
BEGIN
IF :usetimestamp = 0 THEN
bool_val := FALSE;
ELSE
bool_val := TRUE;
END IF;
dbms_audit_mgmt.clean_audit_trail(:trailtype, bool_val);
END;
oracle 02-NOV-20 01.06.50.335987 PM SELECT a.state, b.startup_time FROM v$asm_diskgroup_stat a, v$instance b WHERE a.name =
:1 /* asm agent *//* {0:0:2} */
oracle 02-NOV-20 01.06.50.387623 PM SELECT a.state, b.startup_time FROM v$asm_diskgroup_stat a, v$instance b WHERE a.name =
:1 /* asm agent *//* {0:0:2} */
oracle 02-NOV-20 01.08.30.434801 PM SELECT a.state, b.startup_time FROM v$asm_diskgroup_stat a, v$instance b WHERE a.name =
:1 /* asm agent *//* {0:0:2} */
oracle 02-NOV-20 01.08.30.472994 PM SELECT a.state, b.startup_time FROM v$asm_diskgroup_stat a, v$instance b WHERE a.name =
:1 /* asm agent *//* {0:0:2} */
Conclusion
In case you are interested in what is going on in your ASM instance, you might want to consider enabling unified auditing. It can help you in identifying suspicious behavior.
Cheers
William