Learning to Love the ADR - Part 3: Purging (Out With The Old)

This post is the third in a short series about Oracle 11g ADR. These posts are based on the presentations I gave at the NoCOUG Summer Conference in August 2011 and at the RMOUG Training Days in February 2012.

One of the benefits of the new ADR system is the automatic file maintenance (archiving and cleanup) that I mentioned earlier. Depending on the type of file it is, XML log and trace file maintenance is governed by either the long or short purge policy. The values of these policies can be observed from the ADRCI command "show control", e.g.:


adrci> show control

ADR Home = /home/oracle/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1335663986           720                  8760                 2012-03-28 09:46:07.813608 -05:00        2012-09-18 22:19:08.939553 -05:00                                                 1                    2                    80                   1                    2012-03-28 09:46:07.813608 -05:00

I've highlighted the column names and values in red. It doesn't format very well in the terminal either.

Let's break it down.

Learning to Love the ADR - Part 2: Diagnostic Files and ADRCI

This post is the second in a short series about Oracle 11g ADR. These posts are based on the presentations I gave at the NoCOUG Summer Conference in August 2011 and at the RMOUG Training Days in February 2012.

Wherever the DIAGNOSTIC_DEST is (remember that the default is the $ORACLE_BASE value), Oracle will create a "diag" directory underneath it. Under this you'll find directories based on instance type, for example "rdbms" or "asm" or "tnslsnr". Dig deeper to find directories per database/instance and then the alert, trace, incident, etc. files. What is new with the ADR is the use of XML files for the alert log, as "log.xml" in the "alert" directory. The human-readable alert_XXX.log that you're used to is now in the "trace" directory. One important distinction is that the XML files are maintained by Oracle, archived once they reach 10Mb in size and purged based on policy settings that you can define. The trace files, just as with previous versions, require you to set up some sort of regular rotation/archiving/purging to prevent them from growing out of control. There are other advantages to the XML files, as we'll see in future posts.

SO. MANY. FILES.

Another not-so-fun surprise for those upgrading to Oracle 11g is that the new version generates a LOT more trace data than before. If you monitor those directories for size, your thresholds are likely to be blown away. Some of this was tamed in 11gR2, thankfully. One way to help cut back on some of trace (trc/trm suffixes) file generation is to set the hidden parameter _disable_health_check to TRUE. As with any hidden/underscore parameter, do NOT set this without approval from Oracle support (at least not in production). Another way which is easier, is to run regular PURGE jobs via adrci, which we will get to in another post.

Problems and Incidents

Oracle and the ADR nicely track problems and incidents. It is important to note that they are not one and the same. Problems are critical errors in the database (eg ORA-00600, 07445, 04031, etc). An incident is a single occurrence of a problem. When an incident occurs, Oracle creates incident trace files in addition to the normal alert log message and error trace file. You'll see this noted after the "Incident details in:" line in the alert log. For example:

Incident details in: /home/oracle/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_12201/orcl_ora_18457_i12201.trc


However, there is a smarter way to look for incidents and alerts than scraping the human-readable alert log like some kind of animal. We'll use the ADRCI.