Unified Auditing in Oracle ASM 26ai

By -

 In Oracle AI Database 26ai, Oracle has introduced three new initialization parameters specifically for controlling unified auditing in Automatic Storage Management (ASM) instances.

These parameters provide control over what ASM operations are audited and where the audit records are sent.

The three parameters are:

• ASM_UNIFIED_AUDIT_DESTINATION — controls the destination of ASM audit records.

• ASM_UNIFIED_AUDIT_LEVEL — controls the level of ASM auditing.

• UNIFIED_AUDIT_SYSTEMLOG — defines the SYSLOG facility and severity used for ASM audit records.

We can verify these parameters in an ASM instance with:

SQL> SHOW PARAMETER unified

NAME                                 TYPE        VALUE
---
asm_unified_audit_destination        string      SYSTEMLOG
asm_unified_audit_level              string      BASIC
unified_audit_systemlog              string      LOCAL0.INFO

In this article, I will demonstrate how these parameters work in Oracle ASM 26ai and show how ASM audit records are written to the operating system.

1. ASM_UNIFIED_AUDIT_DESTINATION

The ASM_UNIFIED_AUDIT_DESTINATION parameter determines where ASM sends its unified audit records.

It supports two values: SYSTEMLOG and FILES

SYSTEMLOG

When this parameter is set to SYSTEMLOG, ASM sends audit records to the operating system’s SYSLOG mechanism.

The actual destination of the log messages is determined by the Linux SYSLOG configuration.

For example, in this demonstration, ASM audit messages are written to:

/var/log/oraasmaudit.log

FILES

The second option is FILES.

When this value is configured, ASM writes the generated audit records to OS spillover audit files.

These files use the .bin format and are stored under the Oracle audit directory:

$ORACLE_BASE/audit/$ORACLE_SID

For an ASM instance such as +ASM2, this would typically be:

$ORACLE_BASE/audit/+ASM2

We will examine an actual spillover audit file later in this article.

2. ASM_UNIFIED_AUDIT_LEVEL

ASM_UNIFIED_AUDIT_LEVEL controls the amount of ASM activity that is audited.

It supports two values:

BASIC

BASIC is the default auditing level and audits important ASM events such as:

• CONNECT

• STARTUP

• SHUTDOWN

ALL

ALL provides comprehensive auditing for actions performed on the ASM instance.

With this setting, all auditable actions performed on the ASM instance are recorded.

This is particularly useful when detailed auditing is required, for example, when monitoring ASM administration or investigating unexpected changes to ASM disk groups.

Because ALL can generate a significantly larger number of audit records than BASIC, the resulting audit volume should be considered when using it in a production environment.

3. UNIFIED_AUDIT_SYSTEMLOG

This parameter specifies the SYSLOG facility and severity that Oracle uses when sending unified audit records to the operating system.

In this environment, it is configured as:

LOCAL0.INFO


The exact log file is determined by the Linux SYSLOG configuration.

Verifying the Audit Records

Before working with the audit records, it is useful to understand the ACTION field. ASM unified audit records contain an action code that identifies the operation being audited.

Some useful action codes are:

ACTION_CODE ACTION_NAME
 - - - - - - - - - - - -
3 SELECT
42 ALTER SESSION
43 ALTER USER
47 PL/SQL EXECUTE
49 ALTER SYSTEM
51 CREATE USER
53 DROP USER
100 LOGON
101 LOGOFF
114 GRANT ROLE
115 REVOKE ROLE
138 DATABASE STARTUP
139 DATABASE SHUTDOWN
187 CREATE SPFILE
188 CREATE PFILE
193 ALTER DISKGROUP
194 CREATE DISKGROUP
195 DROP DISKGROUP

First, we can verify the current configuration:

asm_unified_audit_destination string SYSTEMLOG
asm_unified_audit_level string BASIC
unified_audit_systemlog string LOCAL0.INFO

The configured SYSLOG file can also be checked:

[oracle@vahiddb ~]$ ll /var/log/oraasmaudit.log
-rw-r - r - . 1 grid asmadmin 496005 Aug 8 05:11 /var/log/oraasmaudit.log

After verifying the parameters, I restarted the ASM instance:

SQL> shut abort
ASM instance shutdown

SQL> startup
ASM instance started
Total System Global Area 1976180072 bytes
Fixed Size 4857192 bytes
Variable Size 1946157056 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted

Once ASM was started, audit records appeared in the operating system log.

For example:

[oracle@vahiddb ~]$ tail -f /var/log/oraasmaudit.log
Aug 8 05:14:34 vahiddb oracle_4001924_[4001924]: Oracle Unified Audit[4001924]: LENGTH: '140' TYPE:"4" DBID:"0" SESID:"137783" CLIENTID:"" ENTRYID:"0" STMTID:"0" DBUSER:"SYS" CURUSER:"" ACTION:"139" RETCODE:"0" SCHEMA:"" OBJNAME:""
Aug 8 05:15:03 vahiddb oracle_4027215_[4027215]: Oracle Unified Audit[4027215]: LENGTH: '140' TYPE:"4" DBID:"0" SESID:"186096" CLIENTID:"" ENTRYID:"0" STMTID:"0" DBUSER:"SYS" CURUSER:"" ACTION:"138" RETCODE:"0" SCHEMA:"" OBJNAME:""
Aug 8 05:15:03 vahiddb oracle_4029986_[4029986]: Oracle Unified Audit[4029986]: LENGTH: '141' TYPE:"4" DBID:"0" SESID:"160172" CLIENTID:"" ENTRYID:"1" STMTID:"1" DBUSER:"SYS" CURUSER:"SYS" ACTION:"100" RETCODE:"0" SCHEMA:"" OBJNAME:""
Aug 8 05:15:04 vahiddb oracle_4030061_[4030061]: Oracle Unified Audit[4030061]: LENGTH: '141' TYPE:"4" DBID:"0" SESID:"170119" CLIENTID:"" ENTRYID:"1" STMTID:"1" DBUSER:"SYS" CURUSER:"SYS" ACTION:"100" RETCODE:"0" SCHEMA:"" OBJNAME:""

The RETCODE field is particularly useful because it allows us to distinguish successful and failed operations.

For example:

RETCODE:”0"

indicates a successful operation.

To test whether failed ASM operations are also recorded, I executed:

alter system set asm_unified_audit_level=ALL scope=spfile;

To test whether failed ASM operations are also recorded, I executed:

SQL> drop diskgroup dat;
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15001: disk group "DAT" does not exist or is not mounted
Help: [https://docs.oracle.com/error-help/db/ora-15039/](https://docs.oracle.com/error-help/db/ora-15039/)

Immediately afterward, a corresponding unified audit record appeared in the SYSLOG:

Aug 8 05:18:46 vahiddb oracle_4045176_[4045176]: Oracle Unified Audit[4045176]: LENGTH: '145' TYPE:"4" DBID:"0" SESID:"160172" CLIENTID:"" ENTRYID:"4" STMTID:"4" DBUSER:"SYS" CURUSER:"SYS" ACTION:"195" RETCODE:"15039" SCHEMA:"" OBJNAME:""

The important parts are:

ACTION:"195"
RETCODE:"15039"

According to the action-code mapping, 195 represents DROP DISKGROUP.

The return code:
RETCODE:"15039"
corresponds to:
ORA-15039

This demonstrates that ASM auditing can capture failed administrative operations as well as successful ones.

The other important part of ASM auditing is the FILES destination and the OS spillover mechanism.

When:

asm_unified_audit_destination=files

audit records are written to OS spillover audit files.

These files use a .bin extension.

In my environment, I found the following directory:

cd $ORACLE_BASE/audit/+ASM2/
-rw - - - -. 1 grid oinstall 4096 Aug 8 05:53 ora_audit_0240.bin

Although it is a binary audit file, we can inspect some of its readable contents with:

strings -10 ora_audit_0240.bin

The output included:

ANG Spillover Audit File
(TYPE=(OS));(CLIENT ADDRESS=((PROTOCOL=beq)(HOST=192.168.122.1)));
sqlplus@vahiddb (TNS V1-V3)
ORA$MANDATORY
(TYPE=(OS));(CLIENT ADDRESS=((PROTOCOL=beq)(HOST=192.168.122.1)));
sqlplus@vahiddb (TNS V1-V3)
ORA$MANDATORY
(TYPE=(OS));(CLIENT ADDRESS=((PROTOCOL=beq)(HOST=192.168.122.1)));
sqlplus@vahiddb (TNS V1-V3)
drop diskgroup dat
ORA$MANDATORY
(TYPE=(OS));(CLIENT ADDRESS=((PRO
TOCOL=beq)(HOST=192.168.122.1)));
sqlplus@vahiddb (TNS V1-V3)
drop diskgroup data
ORA$MANDATORY

The file also contained the ASM commands executed during the test:

drop diskgroup dat

and:

drop diskgroup data

This demonstrates that the spillover file can contain valuable information about ASM activity.

Vahid Yousefzadeh

Written by Vahid Yousefzadeh


I have been a DBA since 2011 and I work with Oracle technology. Linkdin: linkedin.com/in/vahidusefzadeh telegram channel ID:@oracledb vahidusefzadeh@gmail.com



Comments

Post a Comment

Popular posts from this blog

Oracle 21c Enhancements for TTS Export/Import

By -
  In Oracle version 21c, new features have been introduced for transportable tablespace (TTS) export/import. This article explores these capabilities in detail. Improvement 1: Parallel Execution of TTS Export/Import In Oracle 19c, TTS export/import operations could not be executed in parallel, as demonstrated by the following: Connected to: Oracle Database 19c Enterprise Edition Release 19.0 .0 .0 .0 – Production ORA-39002: invalid operation ORA-39047: Jobs of type TRANSPORTABLE cannot use multiple execution streams. Oracle addressed this limitation in version 21c, enabling parallel execution for TTS export/import: [oracle@oLinux7 ~]$ expdp directory=drm dumpfile=TTS_DUMP%U.dmp transport_tablespaces=tbs1,tbs2 transport_full_check= y parallel= 2 Export: Release 21.0 . 0 . 0 . 0 – Production on Tue Oct 12 06 : 48 : 54 2021 Version 21.3 . 0 . 0 . 0 Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPOR...
Read more

Oracle 23ai — error_message_details Parameter for Displaying Error Details

By -
  error_message_details   is another new parameter introduced in Oracle version 23ai, which allows displaying detailed information about data value-related errors. By setting this parameter to   ON , we can see the exact value that caused errors such as   ORA-00001: unique constraint violated   within the error message itself. For example, in the following scenario, enabling the  error_message_details  parameter helps identify the value that triggered the error: SQL > create table tbl1(id number primary key); Table created. SQL > variable B number; SQL > exec :B: = 2547 ; PL / SQL procedure successfully completed. SQL > insert into tbl1 values (:B); 1 row created. SQL > insert into tbl1 values (:B); ERROR at line 1 : 'ORA-00001: unique constraint (USEF.SYS_C008328) violated on table USEF.TBL1 columns (ID)' 'ORA-03301: (ORA-00001 details) row with column values (ID:2547) already exists' Help: https: / / docs.oracle.com ...
Read more

Oracle 21c — Setting Compression Attribute for Indexes During Import

By -
  In Oracle 12cR1, a new feature was introduced that allowed us to enable or disable the   compression   property for tables during a dump import. This was done using the   TRANSFORM   parameter in the   impdp   command: SQL * Plus: Release 12.2 .0 .1 .0 Production SQL > select compression from dba_tables where table_name = 'TBL1' ; COMPRESS -------- DISABLED SQL > drop table usef.tbl1; Table dropped. [oracle @oshost ~ ]$ impdp usef / a directory = drm dumpfile = test.dmp tables = usef.tbl1 TRANSFORM = TABLE_COMPRESSION_CLAUSE:\"ROW STORE COMPRESS ADVANCED\" SQL > select compression,COMPRESS_FOR from dba_tables where table_name = 'TBL1' ; COMPRESS COMPRESS_FOR -------- ------------------------------ ENABLED ADVANCED This feature was only available at the  table level , and there was no way to control index compression during import (in Oracle 12c). However, in  Oracle 21c , as a new feature, you can now enable (...
Read more