Thursday, 23 June 2011

Oracle 11g R2 ASM Audit log generate a lot of files

I recently noticed, in my 2 Node RAC 11gR2(11.2.0.2) my disk usage on /u01 increase rapidly when no process are running. This mount point contains GRID_HOME and ORACLE_HOME on each node.
I manage to find which directory that causing this. It directed me to GRID_HOME under /u01/app/11.2/grid/rdbms/audit/ which generate a lot of files (+asm1_ora*.aud) every minutes and when I count just guess how many files are there ? around 200K files and keep generated.
 I remember only enable SYS auditing on database level which write directly to the OS syslog. Then I checked on my ASM instance if there is Auditing enabled and guess what? All audit parameter on all my ASM instance are disabled('FALSE' value).
 Let me see if anything written in documentation about this.
 http://oracle.su/docs/11g/network.112/e10574/auditing.htm#CEGJADDC

Activities That Are Always Audited for All Platforms

Oracle Database always audits certain database-related operations and writes them to the operating system audit files. It includes the actions of any user who is logged in with the SYSDBA or SYSOPER privilege. This is called mandatory auditing. Even if you have enabled the database audit trail (that is, setting the AUDIT_TRAIL parameter to DB), Oracle Database still writes mandatory records to operating system files.
By default, the operating system files are in the $ORACLE_HOME/admin/$ORACLE_SID/adump directory on UNIX systems. On Windows systems, Oracle Database writes this information to the Windows Event Viewer. You can change the location of this directory by setting the AUDIT_FILE_DEST initialization parameter, which is described in "Specifying a Directory for the Operating System Audit Trail".
Mandatory auditing includes the following operations:
  • Database startup. An audit record is generated that lists the operating system user starting the instance, the user terminal identifier, and the date and time stamp. This data is stored in the operating system audit trail because the database audit trail is not available until after the startup has successfully completed.
  • SYSDBA and SYSOPER logins. Oracle Database records all SYSDBA and SYSOPER connections.
  • Database shutdown. An audit record is generated that lists the operating system user shutting down the instance, the user terminal identifier, and the date and time stamp.

But in my case, it is generated too aggresive and I did trace level 12 which come up with nothing else than user SYS or SYSASM login information on those files. I open SR with oracle support about this, to check if I hit a bug. Came back with a workarround to put a cron job to delete the files periodically as per needed. For me I don't need the audit files under ASM instances, so I put a cron job to delete every hour.
Well maybe this only happen in my environment, but hope this will help someone that hit the same issue like me.
My environment :
AIX 6.1
Oracle RAC 11gR2(11.2.0.2) PSU2 - 2 Node (non shared home)
ASM
Extended Cluster

5 comments:

  1. can you share your cron job? I had exactly same issue as yours.

    ReplyDelete
  2. Hi,

    The cron job In my case I just put an rm command to delete file older than 1 week.

    ReplyDelete
  3. here is the cronjob I use :
    00 * * * * rm /u01/app/11.2/grid/rdbms/audit/+asm1_ora_*

    ReplyDelete
    Replies
    1. It was edited to delete more often due to space constrain.

      Delete
  4. Thanks for sharing.
    If you run orachk from oracle support you get a warning that there are to many audit files, with advise to Schedule a rm cron job ;-)

    ReplyDelete