Oracle database housekeeping methods – adump purge

March 21, 2014 by 3 Comments 

Since Oracle Database 11g, all traces, incident dumps and packages, the alert log, Health Monitor reports, core dumps, and more files are stored in the ADR, a file-based Automatic Diagnostic Repository for simplified database diagnostic data management. In spite of that new 11g feature, the housekeeping or purging of those files got not considerably simplified. Unfortunately the new Oracle utility ADRCI can not purge all the files that Oracle database generates in ADR. Thus, you should use the other Unix OS methods and tools to accomplish Oracle 11g database housekeeping. I’ve already described the Unix log rotation mechanism. This time we will talk about Oracle System Audit files and how to purge them regularly.

SYS, SYSDBA or SYSOPER connections to the Oracle database are always audited. Sometimes this can lead to the creation of an excessive number of audit files. When this is considered to be a problem it cannot be solved at the database side and it must be investigated why the ‘client’ applications including Oracle Enterprise Manager (OEM) Components and agents are connecting so frequently as SYSDBA/SYSOPER. So this type of auditing mandatory in the Oracle database and can NOT be turned off. However the amount of audited information depends on AUDIT_SYS_OPERATIONS parameter that allows the addition audit of all statements issued by SYS/SYSDBA/SYSOPER in the same OS audit trail file.  In case the parameter AUDIT_SYS_OPERATIONS=TRUE Oracle audits not only SYS/SYSDBA/SYSOPER connection details but also their SQL operations. In any case the audit files with the name like <sid>_ora_<spid >_<instance#>.aud are created in audit_file_dest location on Unix.

sql*plus> show parameter audit
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /opt/app/oracle/admin/orcl/ adump
audit_sys_operations                 boolean     FALSE

I share below one of the methods of purging those audit files on Unix using a simple Unix command based on following:

– Deletion of files older than 40 days

– Default System Audit files location: $ORACLE_BASE/admin/<SID>/adump/

– .trc files can be purged by adrci command

$ find /opt/oracle/orcl/adump/ -name '*.aud' -mtime +40 -exec rm -f {} \;

The above command can be included in the Unix crontab scheduler to run regularly.

Note:
– AUDIT_FILE_DEST is invalid on Windows, all Oracle audit trail information on Windows is recorded in the Windows event viewer.
– Starting with 10gR2 the audit data produced by a database running on Unix can be written to SYSLOG

So, that was a housekeeping method for Oracle database audit files that are generated in audit_file_dest location using Unix shell scripting. Please share your own tips and methods of purging Oracle SYS audit files in comments below.

Enjoyed this article? Please share it with others using the social site of your choice:

Comments

3 Responses to “Oracle database housekeeping methods – adump purge”
  1. Chris says:

    I was doing something similar, but the find command fails if there are too many audit records. As such, I switched our scripts to use the find for older versions but moved to sys.dbms_audit_mgmt for oracle 11 & 12. Works well, even with thousands of .aud records.

    begin

    dbms_output.put_line(‘Checking if cleanup is initialized.’);
    if
    dbms_audit_mgmt.is_cleanup_initialized(dbms_audit_mgmt.audit_trail_aud_std)
    then
    dbms_output.put_line(‘Audit Management is initialized for clean-up’);
    else
    dbms_output.put_line(‘Audit Management is not initialized for clean-up. Doing it now.’);
    sys.dbms_audit_mgmt.init_cleanup(audit_trail_type => sys.dbms_audit_mgmt.audit_trail_all,default_cleanup_interval => 72 );
    –NOTE: The 72 is meaningless here. MOS Note 1243324.1
    end if;

    dbms_output.put_line(‘Setting archive timestamps. Older data will be purged.’);
    dbms_output.put_line(‘…OS Files: ${vAuditMGMTDaysOS}’);
    dbms_output.put_line(‘…DB: ${vnAuditRetention}’);

    sys.dbms_audit_mgmt.set_last_archive_timestamp(
    audit_trail_type => sys.dbms_audit_mgmt.audit_trail_aud_std,
    last_archive_time => systimestamp – ${vnAuditRetention});

    sys.dbms_audit_mgmt.set_last_archive_timestamp(
    audit_trail_type => sys.dbms_audit_mgmt.audit_trail_os,
    last_archive_time => sysdate – ${vAuditMGMTDaysOS});

    dbms_output.put_line(‘Purging audit records’);

    dbms_audit_mgmt.clean_audit_trail(
    audit_trail_type => dbms_audit_mgmt.audit_trail_os,
    use_last_arch_timestamp => TRUE );

    dbms_audit_mgmt.clean_audit_trail(
    audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
    use_last_arch_timestamp => TRUE );

    dbms_output.put_line(‘Current archive stats’);
    end;
    /
    set lines 999
    col audit_trail for a35;
    col last_archive_ts for a50;
    select audit_trail, last_archive_ts, rac_instance from DBA_AUDIT_MGMT_LAST_ARCH_TS;

    exit;

  2. Norm says:

    if you use -delete option of find to replace -exec rm -f {} \; it shouldn’t complain about too many files

  3. Hello Kirill,
    This is my 1st time in your nice blog, please keep it up , you are doing a great job !.

    We are using AIX platform for our databases and I noticed that there is an escape character at the end of your command:

    $ find /opt/oracle/orcl/adump/ -name ‘*.aud’ -mtime +40 -exec rm -f {} \;

    Definitely, this command will succeeded when you are running it directly from the shell, but I think you need to add another escape character to the end of the same command when adding it to the crontab :

    $ find /opt/oracle/orcl/adump/ -name ‘*.aud’ -mtime +40 -exec rm -f {} \\;

    This is what we are doing in our environment and it is working like wave.

    Thanks
    Mahmoud

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email