Automatic Diagnostic Repository (ADR)

Automatic Diagnostic Repository (ADR)

Oracle introduces automatic diagnostic repository (ADR) with command interface (ADRCI) tool for DBAs to quickly investigate diagnostic information. The ADR is a file-based repository for database diagnostic data such as traces, dumps, the alert log, health monitor reports, and more. It has a unified directory structure across multiple instances and multiple products.

The V$DIAG_INFO view lists all ADR locations for the current Oracle Database instance:

SELECT * FROM V$DIAG_INFO;

INST_ID   NAME                           VALUE

----------   ------------------------  -------------------------------------------------------------

      1          Diag Enabled                     TRUE

      1          ADR Base                        /u01/oracle

      1          ADR Home                      /u01/oracle/diag/rdbms/orclbi/orclbi

      1          Diag Trace                       /u01/oracle/diag/rdbms/orclbi/orclbi/trace

      1          Diag Alert                        /u01/oracle/diag/rdbms/orclbi/orclbi/alert

      1          Diag Incident                   /u01/oracle/diag/rdbms/orclbi/orclbi/incident

      1          Diag Cdump                   /u01/oracle/diag/rdbms/orclbi/orclbi/cdump

      1          Health Monitor               /u01/oracle/diag/rdbms/orclbi/orclbi/hm

      1          Default Trace File          /u01/oracle/diag/rdbms/orclbi/orclbi/trace/orcl_ora_22769.trc

      1         Active Problem Count     8

      1         Active Incident Count     20

From 11g, the initialization parameter's BACKGROUND_DUMP_DEST and USER_DUMP_DEST are now deprecated and replaced by DIAGNOSTIC_DEST.

ADRCI is a tool enables to:

·         View diagnostic data within the Automatic Diagnostic Repository (ADR).

·         View Health Monitor reports.

·         Package incident and problem information into a zip file for transmission to Oracle Support.

ADRCI has a rich command set, and can be used in interactive mode or within scripts. In addition, ADRCI can execute scripts of ADRCI commands in the same way that SQL*Plus executes scripts of SQL and PL/SQL commands. ADR data is secured by operating system permissions on the ADR directories hence there is no need to log in to ADRCI.

Alert Logs:

Oracle's alert.log chronologically records messages and errors arising from the daily database operation. Also, there are pointers to trace files and dump files.

These messages include's information about:

* startups and shutdowns of the instance

* Messages to the operator console

* Errors causing trace files.

* Create, alter and drop SQL statements on databases, tablespaces and rollback segments.

* Errors when a materialized view is refreshed.

* ORA-00600 (internal) errors.

* ORA-01578 errors (block corruption)

* ORA-00060 errors (deadlocks)

The alert.log is a text file that can be opened with any text editor. Users can also write messages to the alert log file using the package procedure dbms_system.ksdwrt.

The directory where it is found can be determined by the background_dump_dest initialization parameter:

SELECT value FROM V$parameter

WHERE name = 'background_dump_dest';

Trace Files:

Oracle's trace files are updated periodically over the life of the process and can contain information on the process environment, status, activities, and errors. Each process in the database associated with a trace file. When a process detects a critical error, it writes information about the error to its trace file. The SQL trace facility also creates trace files, which provide performance information on individual SQL statements. You can enable SQL tracing for a session or an instance.

Trace file names contain the Oracle SID, the background process name, and the operating system process number, while server process trace file names contain the Oracle SID, the string "ora", and the operating system process number with file extension .trc.

The trace file can be converted into text/readable format by using the TKPROF utility.

To generate the session specific trace file:

ALTER SESSION SET SQL_TRACE=TRUE;

The resulting trace file will be located in the USER_DUMP_DEST directory. You can identify the current session trace file by running the following query.

SET LINESIZE 100

COLUMN trace_file FORMAT A60

SELECT s.sid, s.serial#,

                pa.value || '/' || LOWER(SYS_CONTEXT('USERENV','INSTANCE_NAME')) ||   

                '_ora_' || p.spid || '.trc' AS trace_file

  FROM   v$session s, v$process p, v$parameter pa

WHERE  pa.name = 'user_dump_dest'

     AND    s.paddr = p.addr

     AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

Run the SQL command to turn of the trace:

ALTER SESSION SET SQL_TRACE=TRUE;

Convert the trace file to readable format using the TKROF utility, sys=no option suppress the statements and its trace ran by SYS user.

tkprof   orcl_ora_2846.trc    sess_146_trc.txt    sys=no