Oracle database housekeeping methods – adump purge

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: Read more »

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