In Oracle 11g, the init.ora parameters like user_dump_dest and background_dump_dest are deprecated. They have been replaced by the single parameter DIAGNOSTIC_DEST which identifies the location of the ADR.
The ADR is the Automatic Diagnostic Repository which is a file based hierarchical respository of data and information related the the database alert, trace and dump files, health monitor reports (new in 11g), network trace and log information, diagnostic data related to the 11g Incident Packaging Service etc.
The ADR can be managed via the 11g Enterprise Manager GUI (Database Control and not Grid Control) or via the ADR command line interpreter adrci
The DIAGNOSTIC_DEST parameter identifies the location of the ADR and it defaults to the $ORACLE_HOME/log or $ORACLE_BASE if this environment variable has been set.
Let us assign this location the environment variable $ADR_DUMP.
The alert log is stored in both XML as well as standard text format in the ADR. In this example the database name is ‘apex’.
The XML alert.log is located at $ADR_DUMP/diag/rdbms/apex/apex/alert and is called ‘log.xml’. The current alert log contents will be found in the file ‘log.xml’ while the older entries are archived every 10 MB into files like ‘log_1.xml’, ‘log_2.xml’ and so on.
The standard text alert log file (alert_apex.log) is located at $ADR_DUMP/diag/rdbms/apex/apex/trace and is mantained for backward compatability purposes.
The view V$DIAG_INFO also has information on the location of the various components of the ADR.
SQL> col value format a50 SQL> SELECT NAME,VALUE FROM v$diag_info 2 WHERE NAME LIKE '%Alert%'; NAME VALUE -------------------- -------------------------------------------------- Diag Alert /u01/oracle/diag/rdbms/apex/apex/alert
Using the ADR CLI to view Alert Log Information
The command ‘adrci’ will take us to the prompt.
apex:/u01/oracle/diag/rdbms/apex/apex/alert> adrci
ADRCI: Release 11.1.0.6.0 – Beta on Mon Aug 3 09:46:52 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ADR base = “/u01/oracle”
adrci>
Many of the adrci commands are very similar to the standard Unix vi commands. For example
SHOW ALERT -TAIL (to view the last 10 entries)
SHOW ALERT -TAIL 50 (to view the last 50 entries)
SHOW ALERT -TAIL -F (keeps the alert log open to view as entries arrive until Control C is pressed)
show alert -p “message_text like ‘%ORA-07445%'” (to show all occurences of the string ‘ORA-07445’ in the alert log.)
Purging Alert Log Content
The adrci command ‘purge’ can be used to purge entries from the alert log. Note that this purge will only apply to the XML based alert log and not the text file based alert log which still has to be maintained using OS commands.
The purge command takes the input in minutes and specifies the number of minutes for which records should be retained.
So to purge all alert log entries older than 7 days the following command will be used:
adrci > purge -age 10080 -type ALERT
Gavin Soorma
