Do you know “adrci”?

Every Oracle DBA must know adrci.

adrci is not-so-new diagnostic/ reporting utility provided by Oracle for its databases. “adrci” or Automatic Diagnostic Repository Command Interpreter,  is a utility to read the alert log file, in a more informative manner. Remember, it is an command-line utility, and was introduced in Oracle 11gR1. It is a new and easier way to deal with alert log and trace file information. As we know that the errors and other incidents are constantly recorded in a alert log file (here both the problems and the incidents are recorded). So to distinguish a problem or a normal incident, this simple utility comes very handy.

As we know during the course of its service an Oracle database, keeps on changing constantly. Many incidents like switching a log file or starting/ shutdown of databases are normal incidents, and does not amount to any problem or error. This utility identifies and distinguishes these events and can be used to report them as desired.

This utility is used:

  • not only to view the alert.log file
  • but also to find the the relation between incident & problem
  • and to create the packages & zip files to send to Oracle Support
  • and also even to manage, especially purging the tracefiles.

Please notice that we now have two different formats of the alert.log files.

One is present in the conventional text format, and is found at location
$ORACLE_BASE/diag/rdbms/<dbname>/<instance_name>/trace

This location is determined by the new initialization parameter DIAGNOSTIC_DEST, while the earlier location BACKGROUND_DUMP_DEST is deprecated in 11gR1 onwards.

The second one is in XML format and is placed in $ORACLE_BASE/diag/rdbms/<dbname>/<instance_name>/alert  This version of the alert.log is read and written to, by adrci utility.

Please notice that ORACLE_BASE variable is already defined here (generally its, u01/app/oracle). A new set of variables were introduced in 11g onwards called ADR base and ADR Home or the Automatic Diagnostic Repository base/ home variables. The ADR base is generally same as ORACLE_BASE. There is one ADR base and multiple ADR homes (see below).

The usage is very simple, from the command line type adrci as:

[oracle@sandb ~]$ adrci

ADRCI: Release 11.2.0.2.0 – Production on Wed May 14 16:20:08 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ADR base = “/u01/app/oracle”

 

Here, you could see ADR base variable value, and you would be taken to adrci prompt. From this prompt, you can see ADR home variables:

adrci> show home
ADR Homes:
diag/tnslsnr/sandb/listener
diag/rdbms/orcl/orcl

 

Another good thing is for this command is that even on windows, you could do a tail -f your alert.log file.

Usage:

adrci> show problem
adrci> show incident

The problem and incident has some specific relationship. The relation between incident and problem: An incident is the concrete occurrence of a problem. In other words: The same problem may have multiple incidents. Refer: http://uhesse.com/2011/06/01/adrci-a-survival-guide-for-the-dba/)

when you do a show problem look under the column called LAST_INCIDENT and note the LAST_INCIDENT, see that its 6201, now do a show incident and under the column INCIDENT_ID look for this value 6201
and further to look more details about this incident do as:

adrci> show incident -mode detail -p “incident_id=6201″

look at the details generated, the lines specially to look for are:

first few lines, especially 1st, 3rd and 4th, which will give you the information on
INCIDENT_ID                   6201
CREATE_TIME                   2014-05-14 16:23:04.524000 +05:30
PROBLEM_ID                    1

and also look at the line near end you will find the new trace file generated as
INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc

 

next, to look at the incident tracefile mentioned above:
adrci> show trace /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc

 

Next, to look at this new incident tracefile mentioned above for further investigation do as:

adrci> show trace /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc

look at the last few lines, and you will see the problem statements (SQLs) issued as:

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0×3, level=3, mask=0×0)
3>       ***** Current SQL Statement for this session (sql_id=8r222qucmawdt) *****          select * from t where n=42
3<       ***** current_sql_statement ***** 3

 

Now suppose you cannot solve this problem yourself and need to send this incident file to Oracle support. To do this you need to create a package by gathering all the required information with a method called “Incident Packaging Service” (IPS).

adrci> ips create package problem 1 correlate all
Created package 2 based on problem id 1, correlation level all

 

At the ADRCI prompt, enter the following command:
IPS CREATE PACKAGE INCIDENT incident_number

For example, the following command creates a package based on incident 3:
IPS CREATE PACKAGE INCIDENT 3

 

and further compress this package (also called as Logical Package) into a zip file, ready to be shipped to Oracle support. The ZIP file is generated from the Logical Package that was created.

adrci> ips generate package 2 in “/home/oracle”
Generated package 2 in file /home/oracle/ORA7445qc_20110601112533_COM_1.zip, mode complete

The compressed package is created at the specified location, for shipping/ emailing to Oracle support.

 

Managing, especially purging of tracefiles

The management of tracefiles. You may notice that 11g creates lots of tracefiles that need to be purged from time to time. In fact, this is done automatically, but you may want to change the default purge policy:

adrci> show tracefile -rt

adrci> show control

Look under the columns SHORTP_POLICY it says 720 and LONGP_POLICY which says 8760

The ordinary tracefiles will stay for 30 days (720 hours), while files like incident files stay one year (8760 hours) by default. We can change that policy with for example:

adrci> set control (SHORTP_POLICY = 360)       /* changing the SHORTP_POLICY to new value of 15 days as= 360 */
adrci> set control (LONGP_POLICY = 2190)        /* changing the LONGP_POLICY to new value of 90 days as= 2190 */
adrci> show control

 

Also, we may want to purge tracefiles manually. Following command will manually purge all tracefiles older than 2 days (2880 minutes):

adrci> purge -age 2880 -type trace
adrci> show tracefile -rt

Back to TOC.


Relativity explained from none other than Albert  Einstein, himself:

When you are courting a nice girl an hour seems like a second. When you sit on a red-hot cinder a second seems like an hour. That’s relativity.
Albert Einstein
If the facts don’t fit the theory, change the facts.
Albert Einstein

To submit ADRCI commands on the command line:

  •     Enter the following command at the operating system command prompt:
    ADRCI EXEC=”COMMAND[; COMMAND]…”
  •     For example, to run the SHOW HOMES command in batch mode, enter the following command at the operating system command prompt:
    ADRCI EXEC=”SHOW HOMES”
  •     To run the SHOW HOMES command followed by the SHOW INCIDENT command, enter the following:
    ADRCI EXEC=”SHOW HOMES; SHOW INCIDENT”

To run ADRCI scripts:

  •    Enter the following command at the operating system command prompt:
    ADRCI SCRIPT=SCRIPT_FILE_NAME
  •     For example, to run a script file named adrci_script.txt, enter the following command at the operating system command prompt:
    ADRCI SCRIPT=adrci_script.txt
  •     A script file contains a series of commands separated by semicolons (;) or line breaks, such as:
    SET HOMEPATH diag/rdbms/orcl/orcl; SHOW ALERT -term

 

Important definitions:
Refers Oracle documentation: http://docs.oracle.com/cd/B28359_01/server.111/b28319/adrci.htm#BGBFBHAF

 

Automatic Diagnostic Repository (ADR)

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. Beginning with Release 11g, the database, Automatic Storage Management (ASM), and other Oracle products or components store all diagnostic data in the ADR. Each instance of each product stores diagnostic data underneath its own ADR home directory (see “ADR Home”). For example, in an Oracle Real Application Clusters environment with shared storage and ASM, each database instance and each ASM instance has a home directory within the ADR. The ADR’s unified directory structure enables customers and Oracle Support to correlate and analyze diagnostic data across multiple instances and multiple products.

 

Problem

A problem is a critical error in the database. Critical errors include internal errors such as ORA-00600 and other severe errors such as ORA-07445 (operating system exception) or ORA-04031 (out of memory in the shared pool). Problems are tracked in the ADR. Each problem has a problem key and a unique problem ID. (See “Problem Key”.)

 

Incident

An incident is a single occurrence of a problem. When a problem occurs multiple times, an incident is created for each occurrence. Incidents are tracked in the ADR. Each incident is identified by a numeric incident ID, which is unique within the ADR. When an incident occurs, the database makes an entry in the alert log, sends an incident alert to Oracle Enterprise Manager, gathers diagnostic data about the incident in the form of dump files (incident dumps), tags the incident dumps with the incident ID, and stores the incident dumps in an ADR subdirectory created for that incident.

Diagnosis and resolution of a critical error usually starts with an incident alert. You can obtain a list of all incidents in the ADR with an ADRCI command. Each incident is mapped to a single problem only.

Incidents are flood-controlled so that a single problem does not generate too many incidents and incident dumps. See Oracle Database Administrator’s Guide for more information on incident flood control.

 

Problem Key

Every problem has a problem key, which is a text string that includes an error code (such as ORA 600) and in some cases, one or more error parameters. Two incidents are considered to have the same root cause if their problem keys match.

 

Incident Package

An incident package (package) is a collection of data about incidents for one or more problems. Before sending incident data to Oracle Support it must be collected into a package using the Incident Packaging Service (IPS). After a package is created, you can add external files to the package, remove selected files from the package, or scrub (edit) selected files in the package to remove sensitive data.

A package is a logical construct only, until you create a physical file from the package contents. That is, an incident package starts out as a collection of metadata in the Automatic Diagnostic Repository (ADR). As you add and remove package contents, only the metadata is modified. When you are ready to upload the data to Oracle Support, you create a physical package using ADRCI, which saves the data into a zip file.

 

Finalizing

Before ADRCI can generate a physical package from a logical package, the package must be finalized. This means that other components are called to add any correlated diagnostic data files to the incidents already in this package. Finalizing also adds recent trace files, alert log entries, Health Monitor reports, SQL test cases, and configuration information. This step is run automatically when a physical package is generated, and can also be run manually using the ADRCI utility. After manually finalizing a package, you can review the files that were added and then remove or edit any that contain sensitive information.


 

ADR Base and ADR Home

When using the ADRCI you will need to understand the concept of an ADR home and the ADR Base. When ADR is started it will list the current ADR Base setting. The ADR Base is part of a predefined set of ADR structures seen in the following graphic:

 

ADR Base and ADR Home diagram, refers Toad world.

The ADR architecture provides for multiple ADR homes all grouped under a common ADR base. This makes managing and using the ADR much easier. The ADR Base for each database instance is set based on the setting of the parameter diagnostic_dest. If diagnostic_dest is not set, then it defaults to the ORACLE_BASE parameter, if set. If ORACLE_BASE is not set, then the ADR is located in a directory $ORACLE_HOME/log.

Within ADR base, there are one or more ADR homes. Each ADR home is the root directory for all ADR related data for a specific instance of a specific Oracle product. To provide for ease of support each ADR home structure is similar in it’s directory structure. The location of an ADR home is given by the following path, which starts at the ADR base directory:

diag/product_type/product_id/instance_id

The diag directory is common to all ADR structures. The product_type can be any relevant Oracle Database product (for example, rdbms for the data product). The product_id is the database name and the instance_id is the instance name. For example, if we have a clustered database called DTBSE with two nodes, DTBSE1 and DTBSE2, then the ADR structures for those instances would be:

[Diagnostic_dest]/diag/rdbms/DTBSE/DTBSE1
[Diagnostic_dest]/diag/rdbms/DTBSE/DTBSE2

Typically, ADRCI will need to be configured to point to a specific ADR home. Some ADRCI commands can work with multiple ADR homes simultaneously. ADRCI will have a specific ADRCI homepath set. This ADRCI homepath will determine the ADR homes that are searched for diagnostic data when an ADRCI command is issued.

adrci in action on windows OS:

C:\Documents and Settings\San>adrci
ADRCI: Release 11.1.0.6.0 - Beta on Tue Dec 25 13:36:40 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

ADR base = "c:\oracle"
adrci>

Note that there are no passwords associated with ADRCI. It is assumed that access to ADRCI (and the underlying ADR structure that it accesses) is controlled via operating system level security. You can get help for the ADRCI command-line by using the help syntax as seen here:

C:\Documents and Settings\San>adrci -help

ADR Subdirectories

ADR homes are broken down into a set of different sub-directories. These directories are where the various files managed by the ADR are stored. Some of the directories are:

  • alert – This directory contains the alert log formatted in XML.
  • cdump – This directory contains the database core dump files.
  • incident – This directory contains a number of sub-directories. Each of the sub-directories contains files associated with a specific incident. The directories are named using the incident identifier.
  • trace – This directory contains the various trace files created by the Oracle background and server processes.

Back to TOC.


 

List of ADRCI commands

Command Description
CREATE REPORT Creates a report for the specified report type and ID.
ECHO Echoes the input string.
EXIT Exits the current ADRCI session.
HOST Executes operating system commands from ADRCI.
IPS Invokes the IPS utility. See Table 15-3 for the IPS commands available within ADRCI.
QUIT Exits the current ADRCI session.
RUN Runs an ADRCI script.
SET BASE Sets the ADR base for the current ADRCI session.
SET BROWSER Reserved for future use.
SET CONTROL Set purging policies for ADR contents.
SET ECHO Toggles command output.
SET EDITOR Sets the default editor for displaying trace and alert log contents.
SET HOMEPATH Makes current one or more ADR homes.
SET TERMOUT Toggles terminal output.
SHOW ALERT Shows alert log messages.
SHOW BASE Shows the current ADR base.
SHOW CONTROL Shows ADR information, including the current purging policy.
SHOW HM_RUN Shows Health Monitor run information.
SHOW HOMEPATH Shows the current homepath.
SHOW HOMES Lists the current ADR homes.
SHOW INCDIR Lists the trace files created for the specified incidents.
SHOW INCIDENT Outputs a list of incidents.
SHOW PROBLEM Outputs a list of problems.
SHOW REPORT Shows a report for the specified report type and ID.
SHOW TRACEFILE Lists qualified trace filenames.
SPOOL Directs output to a file.

 


 

Setting the ADR homes:

You use the SET HOMEPATH command to set one or more ADR homes to be current. If ADR base is /u01/app/oracle and you want to set the homepath to /u01/app/oracle/diag/rdbms/orclbi/orclbi2/, you use this command:

adrci> set homepath diag/rdbms/orclbi/orclbi2

When ADRCI starts, the homepath is null by default, which means that all ADR homes under ADR base are current

 

Sample useful adrci commands:

This example shows all alert messages for the current ADR home and directs the output to the terminal instead of the default editor:

show alert -term

This example shows all alert messages for the current ADR home with message text describing an incident:

show alert -p "message_text like '%incident%'"

This example shows the last twenty alert messages, and then keeps the alert log open, displaying new alert log entries as they arrive:

show alert -tail 20 -f

 

We are twice armed if we fight with faith.
- Plato


 

About these ads

One thought on “Do you know “adrci”?

  1. I was excited to discover this web site. I wanted to thank you for ones time due to this wonderful read!!
    I definitely really liked every little bit of it and
    I have you saved as a favorite to look at new things in your site.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s