New Feature for Oracle 11g: ADR monitoring tools

Call (800) 766-1884 for Oracle support & training

Donald K. Burleson
Advanced Oracle Tips and Tricks for the Oracle DBA

Debugging Oracle 11g Applications
by Ben Prusinski

New Feature for Oracle 11g: ADR monitoring tools

Oracle 11g has introduced a new monitoring tool called the Automatic Diagnostic Repository (ADR). This new tool which is available via both command line and from within Enterprise Manager/Grid Control provides comprehensive reporting and monitoring functions for Oracle trace files, alert log reporting and other features for monitoring the Oracle 11g database environment. Prior to Oracle 11g, trace files were stored by default under the user_dump_dest directory. Before Oracle 11g, the alert.log text based file which contains details on the status of the Oracle database were stored by default under the background dump destination directory before 11g and now is referenced by a single initialization parameter called diagnostic_dest . The previous release parameters for background_dump_dest, core_dump_dest, and user_dump_dest have been deprecated and replaced by the single parameter diagnostic_dest in 11g. Now with Oracle 11g, these files are managed under the ADR directory structure and can be viewed by the ADR tool. The base monitoring file is stored in XML format in a file called log.xml. By default the diagnostic directory structure is tied to the ORACLE_HOME, ORACLE_BASE and ORACLE_SID environment variables.

SQL> show parameter diagnostic_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest string /u01/app/oracle

Figure 1.18: diagnostic_dest parameter with ADR and Oracle 11g

The Oracle 11g ADR uses a home directory to centralize log file maintenance operations. By default, the ADR home directory is located under the directory structure:

<ADR_BASE>/diag/rdbms/<db_name>/<instance_id>.

To show the home directory details for ADR, we can logon to the command line tool adrci as follows.

[oracle@raclinux1 ~]$ adrci

ADRCI: Release 11.1.0.6.0 - Beta on Tue Jul 29 00:07:57 2008

ADR base = "/u01/app/oracle"
adrci> show home
ADR Homes:
diag/rdbms/ora11g/ora11g
diag/rdbms/ora11g/ORA11G
diag/rdbms/default/ORA11G
diag/rdbms/unknown/ORA11G
diag/rdbms/stdby1/stdby1
diag/clients/user_oracle/host_3681296775_11
diag/clients/user_unknown/host_411310321_11
diag/tnslsnr/raclinux1/listener
diag/tnslsnr/raclinux1/listener1
diag/tnslsnr/raclinux1/listener_stdby1

Figure 1.19: Using adrci to display ADR home with Oracle 11g

In addition to using the adrci show home command, the location for the ADR configuration can be displayed using the V$DIAG_INFO view as shown in the below figure example.

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> col name format a20
SQL> col value format a50 word_wrapped
SQL> select name, value
2 from
3 v$diag_info;

NAME                 VALUE
-------------------- --------------------------------------------------
Diag Enabled         TRUE
ADR Base             /u01/app/oracle
ADR Home             /u01/app/oracle/diag/rdbms/ora11g/ORA11G
Diag Trace           /u01/app/oracle/diag/rdbms/ora11g/ORA11G/trace
Diag Alert           /u01/app/oracle/diag/rdbms/ora11g/ORA11G/alert
Diag Incident        /u01/app/oracle/diag/rdbms/ora11g/ORA11G/incident
Diag Cdump           /u01/app/oracle/diag/rdbms/ora11g/ORA11G/cdump
Health Monitor       /u01/app/oracle/diag/rdbms/ora11g/ORA11G/hm
Default Trace File   /u01/app/oracle/diag/rdbms/ora11g/ORA11G/trace/ORA
                     11G_ora_28603.trc

Figure 1.20: Location for diagnostic files in Oracle 11g for ADR

The above query against the V$DIAG_INFO view provides us with the default locations for the diagnostic trace, alert, incident, core dump, ADR home, ADR base, and health monitor files for Oracle 11g.

If we examine the log.xml file we notice that the default xml alert log file contains all of the entries that are also contained in the text based alert.log file which is available for backward compatibility with previous releases.

SQL> !ls /u01/app/oracle/diag/rdbms/ora11g/ORA11G/alert
log.xml

SQL> !view /u01/app/oracle/diag/rdbms/ora11g/ORA11G/alert/log.xml

<msg time='2008-06-09T18:34:25.057-04:00' org_id='oracle' comp_id='rdbms'
msg_id='opistr_real:871:3971575317' type='NOTIFICATION' group='startup'
level='16' pid='7457' version='1'>
<txt>Starting ORACLE instance (normal)
</txt>
</msg>
<msg time='2008-06-09T18:34:25.840-04:00' org_id='oracle' comp_id='rdbms'
msg_id='ksunfy:13399:2937430291' type='NOTIFICATION' group='startup'
level='16' pid='7457'>
<txt>LICENSE_MAX_SESSION = 0
</txt>
</msg>
<msg time='2008-06-09T18:34:25.840-04:00' org_id='oracle' comp_id='rdbms'
msg_id='ksunfy:13400:4207019197' type='NOTIFICATION' group='startup'
level='16' pid='7457'>
<txt>LICENSE_SESSIONS_WARNING = 0
</txt>
</msg>
<msg time='2008-06-09T18:34:25.918-04:00' org_id='oracle' comp_id='rdbms'
msg_id='kcsnfy:323:968333812' type='NOTIFICATION' group='startup'
level='16' pid='7457'>
<txt>Picked latch-free SCN scheme 2
</txt>
</msg>
<msg time='2008-06-09T18:34:26.494-04:00' org_id='oracle' comp_id='rdbms'
msg_id='kcrrdini:15230:1211400554' type='NOTIFICATION' group='startup'
level='16' pid='7457'>

Figure 1.21: Reviewing the log.xml file for ADR and Oracle 11g

Since the format of the log.xml file is not intended for viewing in xml format, it is advised to use the adrci interface to view log files. Returning to the ADR interface via the adrci command tool, we can dump the help contents to provide us with a list of commands available with ADR as shown in the below figure. We can also use the HELP EXTENDED command to display more details.

[oracle@raclinux1 ~]$ adrci

ADRCI: Release 11.1.0.6.0 - Beta on Tue Jul 29 00:29:52 2008

ADR base = "/u01/app/oracle"
adrci> help extended

HELP [topic]
   Available Topics:
        BEGIN BACKUP
        CD
        DDE
        DEFINE
        DESCRIBE
        END BACKUP
        LIST DEFINE
        MERGE ALERT
        MERGE FILE
        QUERY
        SET COLUMN
        SHOW CATALOG
        SHOW DUMP
        SHOW SECTION
        SHOW TRACE
        SHOW TRACEMAP
        SWEEP
        UNDEFINE
        VIEW
adrci> help

HELP [topic]
   Available Topics:
        CREATE REPORT
        ECHO
        EXIT
        HELP
        HOST
        IPS
        PURGE
        RUN
        SET BASE
        SET BROWSER
        SET CONTROL
        SET ECHO
        SET EDITOR
       SET HOMES | HOME | HOMEPATH
        SET TERMOUT
        SHOW ALERT
        SHOW BASE
        SHOW CONTROL
        SHOW HM_RUN
        SHOW HOMES | HOME | HOMEPATH
        SHOW INCDIR
        SHOW INCIDENT
        SHOW PROBLEM
        SHOW REPORT
        SHOW TRACEFILE
        SPOOL

There are other commands intended to be used directly by Oracle, type

"HELP EXTENDED" to see the list

adrci>

Figure 1.22: Using help commands with adrci for ADR in Oracle 11g

If we wish to find specific help on a particular topic with ADR, we can issue the command help followed by the topic as shown in the following example.

adrci> help dde create incident

Usage: DDE CREATE INCIDENT TYPE <type>

Purpose: Create an incident of specified type. If the incident type is associated with an action, the action will be automatically recommended for the new incident.

Arguments:

<type>: Incident type

Notes:

The relation DDE_USER_INCIDENT_TYPE shows available incident types.

Example:

dde create incident type wrong_results

Figure 1.23: Using help on specific topic for ADR and Oracle 11g

Oracle Consulting