A sample how to handle ASM (or Database) Audit Files

I have seen on many Oracle Database Servers that the audit files will fill up a filesystem. There are several approaches how to handle this on the RDBMS Installation but these options would maybe not work on the ASM side.

There is also a nasty situation when you get this error when logging on to the database.

ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 28: No space left on device
Additional information: 9925
ORA-01075: you are currently logged on

One Option is to handle this with OS Script like find (find /u01/app/12.1.0/grid/rdbms/audit/. -type f  -delete) or logrotate, but i will show you here an other nice option with syslog. Note that this configuration must be done on any cluster node (in case of Real Application Cluster or One Node RAC).

Step 1: Configure your ASM to send Audit Data to the OS syslog Daemon

[oracle@server1 audit]$ sqlplus / as sysdba

SQL*Plus: Release Production on Sat Sep 7 14:56:36 2013

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

Connected to:
Oracle Database 12c Enterprise Edition Release - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> alter system set AUDIT_SYS_OPERATIONS=TRUE scope=spfile sid='*';

System altered.

SQL> alter system set AUDIT_SYSLOG_LEVEL='local0.info' scope=spfile sid='*';

System altered.

Step 2: Configure syslog for ASM auditing

Configure your /etc/rsyslog.conf like this example

# Save boot messages also to boot.log
local7.*                                                /var/log/boot.log

# ASM Audit Files
local0.info                                             /var/log/asmaudit.log

Change the line which controls the „/var/log/message“ file to stop logging audit information in the standard message file.

# Log anything (except mail) of level info or higher.
# Don't log private authentication messages!
*.info;mail.none;authpriv.none;cron.none,local0.none   /var/log/messages

Step 3: Configure logrotate to mange you Audit Log

Create a new File oracle in the directory /etc/logrotate.d and add the following lines.

/var/log/asmaudit.log {
  rotate 4

Step 4: Restart all depended services

Remember to do this on all Nodes in your cluster.

$ORACLE_HOME/bin/crsctl stop cluster
$ORACLE_HOME/bin/crsctl start cluster
[root@server1 logrotate.d]# /etc/init.d/rsyslog restart
System-Protokollierdienst beenden:                         [  OK  ]
System-Protokollierdienst starten:                         [  OK  ]

Step 5: Check your configuration

You should now see the new log file.

[root@server1 log]# ls -ltr /var/log/asmaudit.log 
-rw------- 1 root root 4008  7. Sep 15:16 /var/log/asmaudit.log

And a short look into that file

Sep  7 15:16:21 server1 Oracle Audit[16071]: LENGTH : '143' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[0] '' STATUS:[1] '0' DBID:[0] '' 
Sep  7 15:16:21 server1 Oracle Audit[16071]: LENGTH : '360' ACTION :[222] 'SELECT g.group_number, g.name, g.total_mb, g.usable_file_mb, g.type, i.instance_name, i.host_name, g.database_compatibility, g.compatibility FROM V$ASM_DISKGROUP g, V$INSTANCE i where   g.state = 'MOUNTED' ORDER BY g.name

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:


Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Google Foto

Du kommentierst mit Deinem Google-Konto. Abmelden /  Ändern )


Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )


Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s