The Automatic Diagnostics Repository
(ADR) is a hierarchical file-based repository for handling diagnostic
information.
- Organization
- Setting the ADR Location
- ADR Command Interpreter (ADRCI)
- Viewing the Alert Log
- Viewing Trace Files
- Managing Diagnostic Information (Purging Trace Files)
- Problems and Incidents
- Creating Packages to Send to Oracle Support
- Cloud Control Integration
Organization
The Automatic Diagnostics Repository
(ADR) is a hierarchical file-based repository for diagnostic information, made
up of a directory structure like the following.
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/alert
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/cdump
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/hm
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/incident
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/trace
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/{others}
Typical installations will have the ADR_BASE set to the ORACLE_BASE. For example, a database called "orcl" might have
a structure like the following.
/u01/app/oracle/diag/rdbms/orcl/orcl/alert
/u01/app/oracle/diag/rdbms/orcl/orcl/cdump
/u01/app/oracle/diag/rdbms/orcl/orcl/hm
/u01/app/oracle/diag/rdbms/orcl/orcl/incident
/u01/app/oracle/diag/rdbms/orcl/orcl/trace
The locations of the various
diagnostics directories can be displayed using the V$DIAG_INFO view.
COLUMN
name FORMAT A25
COLUMN
value FORMAT A65
SELECT
name, value FROM v$diag_info;
NAME VALUE
-------------------------
-----------------------------------------------------------------
Diag
Enabled TRUE
ADR
Base /u01/app/oracle
ADR
Home
/u01/app/oracle/diag/rdbms/orcl/orcl
Diag
Trace
/u01/app/oracle/diag/rdbms/orcl/orcl/trace
Diag
Alert
/u01/app/oracle/diag/rdbms/orcl/orcl/alert
Diag
Incident
/u01/app/oracle/diag/rdbms/orcl/orcl/incident
Diag
Cdump
/u01/app/oracle/diag/rdbms/orcl/orcl/cdump
Health
Monitor /u01/app/oracle/diag/rdbms/orcl/orcl/hm
Default
Trace File
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_19975.trc
Active
Problem Count 0
Active
Incident Count 0
11
rows selected.
Setting the ADR Location
For the database, the ADR location
is specified using the diagnostic_dest initialisation parameter.
SQL>
ALTER SYSTEM SET diagnostic_dest='/u01/app/oracle';
System
altered.
SQL>
show parameter diagnostic_dest
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
diagnostic_dest string /u01/app/oracle/
SQL>
For the listener, the ADR location
is set by editing the "$ORACLE_HOME/network/admin/listener.ora" file
to include the following entry.
ADR_BASE_LISTENER
= /u01/app/oracle
The listener must be restarted for
the change to take effect.
$
lsnrctl stop
$
lsnrctl start
If the "diag" directory is
not already present, it will be created when the listener or database are
started with the new setting.
ADR Command Interpreter (ADRCI)
You can navigate the contents of ADR
using operating systems command line or file browsing tools, but Oracle also
provide the ADR Command Interpreter (ADRCI), which is probably a better solution
for many tasks.
Assuming your environment is set
correctly, you should be able to start ADRCI by issuing the "adrci"
command.
$
adrci
ADRCI:
Release 11.2.0.3.0 - Production on Mon Jun 9 13:41:29 2014
Copyright
(c) 1982, 2011, Oracle and/or its affiliates.
All rights reserved.
ADR
base = "/u01/app/oracle"
adrci>
When started, you should see the
"ADR base =" message. If instead you get a message saying no base is
set, you can manually set it using the SET BASE command.
adrci>
set base /u01/app/oracle
Typing the HELP command provides a list of help topics related to available
commands.
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>
As the help suggests, you can drill
down by asking for help on specific commands.
adrci>
help show alert
Usage: SHOW ALERT [-p
<predicate_string>] [-term]
[ [-tail [num] [-f]] |
[-file <alert_file_name>] ]
Purpose: Show alert messages.
Options:
[-p <predicate_string>]: The
predicate string must be double-quoted.
The fields in the predicate are the fields:
ORIGINATING_TIMESTAMP timestamp
NORMALIZED_TIMESTAMP timestamp
ORGANIZATION_ID text(65)
COMPONENT_ID text(65)
HOST_ID text(65)
HOST_ADDRESS text(17)
MESSAGE_TYPE number
MESSAGE_LEVEL number
MESSAGE_ID text(65)
MESSAGE_GROUP text(65)
CLIENT_ID text(65)
MODULE_ID text(65)
PROCESS_ID text(33)
THREAD_ID text(65)
USER_ID text(65)
INSTANCE_ID text(65)
DETAILED_LOCATION text(161)
UPSTREAM_COMP_ID text(101)
DOWNSTREAM_COMP_ID text(101)
EXECUTION_CONTEXT_ID text(101)
EXECUTION_CONTEXT_SEQUENCE number
ERROR_INSTANCE_ID number
ERROR_INSTANCE_SEQUENCE number
MESSAGE_TEXT text(2049)
MESSAGE_ARGUMENTS text(129)
SUPPLEMENTAL_ATTRIBUTES text(129)
SUPPLEMENTAL_DETAILS text(129)
PROBLEM_KEY text(65)
[-tail [num] [-f]]: Output last part of the
alert messages and
output latest messages as the alert log
grows. If num is not specified,
the last 10 messages are displayed. If
"-f" is specified, new data
will append at the end as new alert
messages are generated.
[-term]: Direct results to terminal. If
this option is not specified,
the results will be open in an editor.
By default, it will open in emacs, but
"set editor" can be used
to set other editors.
[-file <alert_file_name>]: Allow
users to specify an alert file which
may not be in ADR. <alert_file_name>
must be specified with full path.
Note that this option cannot be used with
the -tail option
Examples:
show alert
show alert -p "message_text like
'%incident%'"
show alert -tail 20
adrci>
Viewing the Alert Log
Depending on your setup, you may
have multiple homes (Database, Listener, Grid Infrastructure etc.). It is
important you point to the correct home before issuing any commands.
adrci>
show home
ADR
Homes:
diag/rdbms/orcl/orcl
diag/tnslsnr/ol6-112/listener
adrci>
set home diag/rdbms/orcl/orcl
adrci>
With the home set, we can look at
the contents of the alert log. The help text for the SHOW
ALERT command provides some examples.
Examples:
show alert
show alert -p "message_text like
'%incident%'"
show alert -tail 20
We might decide to look for errors
in the alert log with a command like the following.
adrci>
show alert -p "message_text like '%ORA-%'"
ADR
Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
Output
the results to file: /tmp/alert_1781_13990_orcl_1.ado
adrci>
We could also do a UNIX-style
"tail -f" of the alert log using the following command, even on
Windows.
adrci>
show alert -tail -f
Viewing Trace Files
The alert log will often make
reference to trace files. These can be viewed directly using the SHOW
TRACE command.
adrci>
show trace
/u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_72697/orcl_ora_18310_i72697.trc
Managing Diagnostic Information (Purging Trace Files)
The PURGE command can be used to remove some or all of the diagnostic
information from the repository. The HELP PURGE command describes the usage.
adrci>
help purge
Usage: PURGE [[-i <id1> | <id1>
<id2>] |
[-age <mins> [-type
ALERT|INCIDENT|TRACE|CDUMP|HM|UTSCDMP]]]:
Purpose: Purge the diagnostic data in the
current ADR home. If no
option is specified, the default
purging policy will be used.
Options:
[-i id1 | id1 id2]: Users can input a
single incident ID, or a
range of incidents to purge.
[-age <mins>]: Users can specify the
purging policy either to all
the diagnostic data or the specified type.
The data older than <mins>
ago will be purged
[-type
ALERT|INCIDENT|TRACE|CDUMP|HM|UTSCDMP]: Users can specify what type of
data to be purged.
Examples:
purge
purge -i 123 456
purge -age 60 -type incident
adrci>
So to purge all diagnostic
information, including trace files, older than 1 month you would issue the
following.
adrci>
purge -age 43200
adrci>
Problems and Incidents
When a problem occurs on the
database, it is logged in the alert log and an incident is created. Doing a
search of the alert log with the following command will reveal some information
about the incident.
adrci>
show alert -p "message_text like '%incident%'"
2013-10-09
10:05:17.154000 +01:00
Errors
in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_18310.trc (incident=72697):
ORA-07445:
exception encountered: core dump [kghalo()] [SIGSEGV] [ADDR:0x9FFFFFFFBFFFF000]
[PC:0x40000000065AE680] [Address not mapped
to object] []
Incident
details in:
/u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_72697/orcl_ora_18310_i72697.trc
Use
ADRCI or Support Workbench to package the incident.
See
Note 411.1 at My Oracle Support for error and packaging details.
2013-10-09
10:05:36.501000 +01:00
Dumping
diagnostic data in directory=[cdmp_20131009100536], requested by (instance=1,
osid=18310), summary=[incident=72697].
A simpler solution is to display the
problem from the command line directly using the SHOW
PROBLEM command.
adrci>
show problem
ADR
Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME
--------------------
-----------------------------------------------------------
-------------------- ----------------------------------------
1 ORA 3137 [3120] 71593 2013-08-09 10:16:43.714000
+01:00
2 ORA 7445 [kghalo()]
72697 2013-10-09
10:05:17.154000 +01:00
2
rows fetched
adrci>
The same problem can occur multiple
times, so a single problem may result in multiple incidents. Incidents are
displayed using the SHOW INCIDENT command.
adrci>
show incident
ADR
Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
INCIDENT_ID PROBLEM_KEY
CREATE_TIME
--------------------
----------------------------------------------------------- ----------------------------------------
71593 ORA 3137 [3120]
2013-08-09 10:16:43.714000 +01:00
72697 ORA 7445 [kghalo()]
2013-10-09 10:05:17.154000 +01:00
2
rows fetched
adrci>
Once you've identified an incident
of interest, you can look at it in more detail by altering the mode in the SHOW
INCIDENT command.
adrci>
show incident -mode detail -p "incident_id=72697"
ADR
Home = /u01/app/oracle/diag/rdbms/lstu/lstu:
*************************************************************************
**********************************************************
INCIDENT
INFO RECORD 1
**********************************************************
INCIDENT_ID 72697
STATUS ready
CREATE_TIME 2013-10-09 10:05:17.154000
+01:00
PROBLEM_ID 2
CLOSE_TIME <NULL>
FLOOD_CONTROLLED none
ERROR_FACILITY ORA
ERROR_NUMBER 7445
ERROR_ARG1 kghalo()
ERROR_ARG2 SIGSEGV
ERROR_ARG3 ADDR:0x9FFFFFFFBFFFF000
ERROR_ARG4 PC:0x40000000065AE680
ERROR_ARG5 Address not mapped to
object
ERROR_ARG6 <NULL>
ERROR_ARG7 <NULL>
ERROR_ARG8 <NULL>
ERROR_ARG9 <NULL>
ERROR_ARG10 <NULL>
ERROR_ARG11 <NULL>
ERROR_ARG12 <NULL>
SIGNALLING_COMPONENT <NULL>
SIGNALLING_SUBCOMPONENT <NULL>
SUSPECT_COMPONENT <NULL>
SUSPECT_SUBCOMPONENT <NULL>
ECID
<NULL>
IMPACTS 0
PROBLEM_KEY ORA 7445 [kghalo()]
FIRST_INCIDENT 72697
FIRSTINC_TIME 2013-10-09 10:05:17.154000
+01:00
LAST_INCIDENT 72697
LASTINC_TIME 2013-10-09 10:05:17.154000
+01:00
IMPACT1 0
IMPACT2 0
IMPACT3 0
IMPACT4 0
KEY_NAME ProcId
KEY_VALUE 287.6
KEY_NAME Client ProcId
KEY_VALUE oracle@biro01 (TNS
V1-V3).18310_1
KEY_NAME ECID
KEY_VALUE 55774dc6ecfa57a3:2a5146fe:13ffc25ce34:-8000-0000000000055601.1
KEY_NAME PQ
KEY_VALUE (0, 1381309514)
KEY_NAME SID
KEY_VALUE 1125.45841
OWNER_ID 1
INCIDENT_FILE
/u01/app/oracle/diag/rdbms/orcl/lstu/trace/orcl_ora_18310.trc
OWNER_ID 1
INCIDENT_FILE
/u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_72697/orcl_ora_18310_i72697.trc
1
rows fetched
adrci>
Creating Packages to Send to Oracle Support
If you can't solve the problem
yourself, you can use the Incident Packaging Service (IPS) to gather all
pertinent information so it can be sent to Oracle Support. This should reduce
the amount of time you waste trying to identify what information is necessary
for them to identify and solve the problem.
First, create the package using the
problem ID displayed by the SHOW PROBLEM command.
adrci>
ips create package problem 2 correlate all
Created
package 1 based on problem id 2, correlation level all
adrci>
Next, create a zip to send to Oracle
Support by specifying the package number displayed by the above command.
adrci>
ips generate package 1 in "/tmp"
Generated
package 1 in file /tmp/IPSPKG_20140610100342_COM_1.zip, mode complete
adrci>
The package is now zipped and ready
to upload to Oracle Support.
$
ls /tmp/IPSPKG*.zip
/tmp/IPSPKG_20140610100342_COM_1.zip
$
