Oracle database housekeeping methods – ADR files 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. In previous posts I described the Unix log rotation mechanism and SYS auditing files purge in adump directory. This time we will talk exactly about ADR purge functionality. But before, let’s look at some basics.

Location for Diagnostic Traces

Location of Diagnostic Traces of Oracle ADR

Location of Diagnostic Traces in Oracle 11g ADR

The table shown above in the slide describes the different classes of trace data and dumps that reside both in Oracle Database 10g and 11g.
With Oracle Database 11g, there is no distinction between foreground and background trace files. Both types of files go into the $ADR_HOME/trace directory.
All non-incident traces are stored inside the trace subdirectory. This is the main difference compared with previous releases where critical error information is dumped into the corresponding process trace files instead of incident dumps. Incident dumps are placed in files separated from the normal process trace files starting with Oracle Database 11g. We can also check V$DIAG_INFO view lists all important ADR locations including.

ADR retention policy – automatic purge

A retention policy allows you to specify how long to keep the diagnostic data. ADR incidents are controlled by two different policies:

LONGP_POLICY:
The incident metadata retention policy controls how long the metadata is kept around. This policy has a default setting of one year (default is 8760 = 365 days = 1 year). Here are the components that currently get purged by LONGP_POLICY:

ALERT — files in the ./alert directory
INCIDENT – files in the ./incident/incdir_ directory.
SWEEP — files in the ./sweep directory
STAGE — files in the ./stage directory
HM — files in the ./hm directory and metadata in the HM schema

SHORTP_POLICY
The incident files and dumps retention policy controls how long generated dump files are kept around. This policy has Read more »

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 »

Oracle database housekeeping methods – Unix log rotation

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 the housekeeping or purging of those files got not 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 OS methods and tools to accomplish Oracle database housekeeping.

This time I share a method of rotating Oracle database log files using Unix logrotate mechanism. The below example will rotate the Oracle alert.log and listener.log files in the following way:

– Rotation is based on the file size (you can use “M” or “k” for Mb and Kb)
– Unix Logrotate utility creates a copy of the original log file, then truncates it
– The rotated files will be compressed with Unix gzip utility with the following name <logfile>-yyyymmdd.gz
– Only recent 20 rotated log files are kept
– Rotation frequency is default = weekly
– No errors generated if the log file is missing
Read more »

Oracle 11g files housekeeping methods

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. However housekeeping or purging of those files got not simplified. Unfortunately the new Oracle utility ADRCI can not purge all the files Oracle database generates in ADR. Thus, you should use the other OS tools to accomplish the same. Below I list common Oracle files that must be cleaned periodically with associated housekeeping methods I usually use on Linux servers.

Logs rotation

– Linux log rotate mechanism used
– alert.log, listener.log, backup logs and any other log files affected
– Files are purged and zipped for archiving purpose

Read more about Oracle database housekeeping using Unix logrotate mechanism.
Read more »

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