Oracle Database diagnostics with ADR

May 30, 2011  |  dba

In 2007 Oracle Database 11g was released with dozens of new features for developers and DBA’s. One of the most important for DBA’s was Diagnostics Repository. DR is an advanced fault diagnosability infrastructure that collects and manage diagnostic data. Such data includes dumps, core files, trace files that are also present in previous releases, plus new types of diagnostic data that enable DBA’s and Support to identify and resolve problems quickly and effectively.

Automatic Diagnostic Repository (ADR) is a file-based repository for database diagnostic that data some more such as health monitor reports. It has a unified directory structure across multiple instances and multiple products. Several Oracle products and components store diagnostic data in the ADR unified directory structure using consistent diagnostic data formats.

In the old days before ADR, DBA’s use to check  different log/trace files to find errors or incidents (or use EM), spending precious time analysing them and finding a solution. Those days ended in 2007, but some DBA’s, by habit or lack of knowledge, still use that old slow way. Today I’ll show how easy is to track, find and report a problem with the ADR command utility (ARDCI). Sure you can use Enterprise Manager (if you have it licensed) to grab that kind of info, but sometimes a good command line utility is faster and simpler to get the info that matters.

ADRCI is a command-line utility that is part of the fault diagnosability infrastructure. ADRCI enables you to:

  • View the diagnostic data within the ADR.
  • View Health reports.
  • Package problems information into a compressed file to send to Oracle Support.

ADRCI can be used in interactive or script mode (such as sqlplus and rman). In the following examples I’ll only use the interactive mode.

[oracle@bizhost ~]$ adrci
ADRCI: Release 11.2.0.2.0 - Production on Fri May 27 15:39:12 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
ADR base = "/u01/app/oracle"
adrci> show homes
ADR Homes:
diag/tnslsnr/bizhost/listener
diag/rdbms/bizdb/bizdb

Now let’s set the diagnostics home to the RDBMS and show some lines of the alert.log file.

adrci> set home diag/rdbms/
adrci> show alert -TAIL
(...)
Thread 1 advanced to log sequence 1596 (LGWR switch)
Current log# 3 seq# 1596 mem# 0: /u01/app/oracle/oradata/bizdb/redo03.log
2011-05-27 14:37:22.487000 +01:00
Errors in file /u01/app/oracle/diag/rdbms/bizdb/bizdb/trace/bizdb_ora_1302.trc  (incident=52316):
ORA-00600: internal error code, arguments: [kkdlReadOnDiskDefVal: bad defValLen], [19], [7], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/bizdb/bizdb/incident/incdir_52316/bizdb_ora_1302_i52316.trc
(...)
Current log# 1 seq# 1597 mem# 0: /u01/app/oracle/oradata/bizdb/redo01.log

As you see we have some problems logged on alert log. An easy way to check the problems without going to alert.log is to show incidents. Take a look:

adrci> show incident
ADR Home = /u01/app/oracle/diag/rdbms/bizdb/bizdb:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
47737                ORA 600 [kkdlReadOnDiskDefVal: bad defValLen]               2011-05-27 11:39:48.950000 +01:00
52316                ORA 600 [kkdlReadOnDiskDefVal: bad defValLen]               2011-05-27 14:37:22.487000 +01:00 
 2 rows fetched

We can quickly check what’s the tracefile associated with an incident and quickly check the detail info too:

adrci> show tracefile -I 47737
 diag/rdbms/bizdb/bizdb/incident/incdir_47737/bizdb_ora_20828_i47737.trc
adrci> show incident -mode detail -p "INCIDENT_ID=47737"
ADR Home = /u01/app/oracle/diag/rdbms/bizdb/bizdb:
**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
 INCIDENT_ID                   47737
 STATUS                        ready
 CREATE_TIME                   2011-05-27 11:39:48.950000 +01:00
 PROBLEM_ID                    1
 CLOSE_TIME                    
 FLOOD_CONTROLLED              none
 ERROR_FACILITY                ORA
 ERROR_NUMBER                  600
 ERROR_ARG1                    kkdlReadOnDiskDefVal: bad defValLen
(...)
 ECID                          
 IMPACTS                       0
 PROBLEM_KEY                   ORA 600 [kkdlReadOnDiskDefVal: bad defValLen]
 FIRST_INCIDENT                47737
 FIRSTINC_TIME                 2011-05-27 11:39:48.950000 +01:00
 LAST_INCIDENT                 52316
(...)
 OWNER_ID                      1
 INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/bizdb/bizdb/incident/incdir_47737/bizdb_ora_20828_i47737.trc

A quick easy way to submit that info to Oracle Support is to create a package. A package can have one or more incidents and any file you want to add to it, such as init.ora, a trace file, a config file, etc… Let’s create a package for an incident, add another incident and check the content of that package. Then we’ll add a trace file to that package and generate the compressed zip file to send to support. Take a look:

adrci> ips create package incident 47737;
Created package 1 based on incident id 47737, correlation level typical

adrci> ips add incident 52316 package 1;
Added incident 52316 to package 1

adrci> ips show incidents package 1;
MAIN INCIDENTS FOR PACKAGE 1:
 INCIDENT_ID            47737
 PROBLEM_ID             1
 EXCLUDE                Included
 INCIDENT_ID            52316
 PROBLEM_ID             1
 EXCLUDE                Included
adrci> ips add file /u01/app/oracle/diag/rdbms/bizdb/bizdb/trace/bizdb_ora_20828.trc package 1;
Added file /u01/app/oracle/diag/rdbms/bizdb/bizdb/trace/bizdb_ora_20828.trc to package 1
adrci> ips generate package 1 in /home/oracle
Generated package 1 in file /home/oracle/ORA600kkd_20110527154904_COM_1.zip, mode complete

Finally, let’s find a specific message in the alert.log and output it to VIM text editor … Be sure to have vim installed.

adrci> show alert -p "message_text like '%ORA-00600%'";
ADR Home = /u01/app/oracle/diag/rdbms/bizdb/bizdb:
*************************************************************************
Output the results to file: /tmp/alert_13084_3086_bizdb_1.ado

This is a pretty straighforward and simple example, but as you surely agree it’s much simpler and faster to get the job done with ADRCI.

Have a nice week…

Joao


Leave a Reply