The goal for every database administrator (DBA) is to be
proactive with identifying any issues or potential issues. By being proactive a DBA can potentially
prevent damage to the database and its data, reduce problem/issue diagnosis
time leading to a reducing in resolution time.
With the introduction with Oracle Database 11g Oracle has
provided a file based repository that is automatically maintained by the
database called the Automatic Diagnostic Repository (ADR). The ADR captures data at the time of an event
and holds the data on the events allowing for review and packaging.
The location of the Automatic Diagnostic Repository (ADR) is
set via the diagnostic_dest database instance initialization parameter. When the diagnostic_dest instance
initialization parameter is not set the location defaults to
ORACLE_HOME/log. The diagnostic_dest
database initialization parameter replaces the background_dump_dest, core_dump_dest
and user_dump_dest database initialization parameter.
The structure of the automatic diagnostic being a file based
repository the structure is made up of directories laid out in a common
structure based on the component.
ORACLE_BASE/diag or ORACLE_HOME/log/diag
asm
+asm
alert
cdump
hm
incident
incpkg
ir
lck
metadata
stage
sweep
trace
clients
crs
diagtool
lsnrctl
netcman
ofm
rdbms
incpkg
hm
metadata
ir
alert
sweep
stage
lck
incident
trace
cdump
tnslsnr
The automatic diagnostic repository can be viewed via a
command line interface or Oracle Enterprise Manager.
The automatic diagnostic repository command line interface
(adrci) is a command line utility that will allow the viewing of the data
collected in the Automatic Diagnostic Repository.
1. run the adrci utility
bash-3.00$ adrci
ADRCI: Release 11.2.0.1.0 - Production on Thu Feb 25 07:45:29 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/oracle"
2. Show ADR Homes
adrci> show homes
ADR Homes:
diag/rdbms/dbua0/DBUA0
diag/rdbms/rman/rman
diag/rdbms/oemgc/oemgc
diag/clients/user_oracle/host_2129719189_11
diag/clients/user_oracle/host_2129719189_76
diag/tnslsnr/grid/listener
3. Set ADR Home
adrci> set home diag/rdbms/rman/rman
4. Show Incidents in the ADR Home
adrci> show incidents
ADR Home = /u01/app/oracle/diag/rdbms/rman/rman:
*************************************************************************
INCIDENT_ID
PROBLEM_KEY
CREATE_TIME
--------------------
-----------------------------------------------------------
----------------------------------------
3889 ORA 7445 [strcpy()+28]
2010-02-04 09:27:39.861000 -06:00
3881
ORA 7445 [strcpy()+28]
2010-02-04 09:27:39.953000 -06:00
3897
ORA 7445 [strcpy()+28]
2010-02-04 09:34:21.672000 -06:00
3857
ORA 7445 [strcpy()+28]
2010-02-04 09:34:21.772000 -06:00
3882
ORA 7445 [strcpy()+60]
2010-02-04 09:46:05.110000 -06:00
…. …. ….
5109
ORA 7445 [strcpy()+60]
2010-02-10 00:30:48.458000 -06:00
6034
ORA 7445 [strcpy()+108] 2010-02-10
00:30:48.559000 -06:00
adrci> show incident -mode detail -p
"incident_id=6183"
ADR Home = /u01/app/oracle/diag/rdbms/rman/rman:
*************************************************************************
**********************************************************
INCIDENT INFO
RECORD 1
**********************************************************
INCIDENT_ID 6183
STATUS ready
CREATE_TIME 2010-02-24 06:36:54.797000
-06:00
PROBLEM_ID 3
CLOSE_TIME
FLOOD_CONTROLLED none
ERROR_FACILITY ORA
ERROR_NUMBER 7445
ERROR_ARG1 strcpy()+108
ERROR_ARG2 SIGSEGV
ERROR_ARG3 ADDR:0xC
ERROR_ARG4 PC:0xFFFFFFFF7C53B52C
ERROR_ARG5 Address not mapped to
object
ERROR_ARG6
ERROR_ARG7
ERROR_ARG8
ERROR_ARG9
ERROR_ARG10
ERROR_ARG11
ERROR_ARG12
SIGNALLING_COMPONENT
SIGNALLING_SUBCOMPONENT
SUSPECT_COMPONENT
SUSPECT_SUBCOMPONENT
ECID
IMPACTS 0
PROBLEM_KEY ORA 7445 [strcpy()+108]
FIRST_INCIDENT 3885
FIRSTINC_TIME 2010-02-04 10:55:39.842000
-06:00
LAST_INCIDENT 6771
LASTINC_TIME 2010-02-25 07:36:37.179000
-06:00
IMPACT1 0
IMPACT2 0
IMPACT3 0
IMPACT4 0
KEY_NAME SID
KEY_VALUE 149.53052
KEY_NAME ProcId
KEY_VALUE 31.11
KEY_NAME Client ProcId
KEY_VALUE oracle@grid (TNS
V1-V3).10992_1
OWNER_ID 1
INCIDENT_FILE
/u01/app/oracle/diag/rdbms/rman/rman/incident/incdir_6183/rman_ora_10992_i6183.trc
OWNER_ID 1
INCIDENT_FILE
/u01/app/oracle/diag/rdbms/rman/rman/trace/rman_ora_10992.trc
1 rows fetched\
adrci> purge -age 999
** Where 999 is number of days older then to purge
1. run the adrci utility
bash-3.00$ adrci
ADRCI: Release 11.2.0.1.0 - Production on Thu Feb 25 07:45:29 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/oracle"
2. Show ADR Homes
adrci> show homes
ADR Homes:
diag/rdbms/dbua0/DBUA0
diag/rdbms/rman/rman
diag/rdbms/oemgc/oemgc
diag/clients/user_oracle/host_2129719189_11
diag/clients/user_oracle/host_2129719189_76
diag/tnslsnr/grid/listener
3. Set ADR Home
adrci> set home diag/rdbms/rman/rman
4. Show Problems in ADR Home
adrci> show problem
ADR Home = /u01/app/oracle/diag/rdbms/rman/rman:
*************************************************************************
PROBLEM_ID
PROBLEM_KEY LAST_INCIDENT LASTINC_TIME
--------------------
-----------------------------------------------------------
-------------------- ----------------------------------------
2 ORA 7445 [strcpy()+60] 6774 2010-02-25 07:37:35.224000 -06:00
3
ORA 7445 [strcpy()+108] 6771 2010-02-25 07:36:37.179000 -06:00
4
ORA 445
6706 2010-02-25
06:36:34.685000 -06:00
1
ORA 7445 [strcpy()+28] 3857 2010-02-04 09:34:21.772000 -06:00
4 rows fetched
adrci> show problem -p "problem_id=1"
ADR Home = /u01/app/oracle/diag/rdbms/rman/rman:
*************************************************************************
PROBLEM_ID
PROBLEM_KEY
LAST_INCIDENT
LASTINC_TIME
--------------------
-----------------------------------------------------------
-------------------- ----------------------------------------
1
ORA 7445 [strcpy()+28] 3857 2010-02-04 09:34:21.772000 -06:00
1 rows fetched
adci> delete from problem where problem_id=1
1
Row Deleted
The incident packaging service provides a great way to
gather the information such as traces, alert log, etc needed to provide to
Oracle support when opening a Service Request on a particular problem.
To be able to send the information on the incident to Oracle
support we must first package the incident.
From the list of incidents from the show incident command we can package
the specific incident we want to send to Oracle Support.
Using the ips create package command we can package an
incident.
Example:
adrci>
ips create package incident 6183
Created
package 3 based on incident id 6183, correlation level typical
adrci>
ips generate package 3
Generated
package 3 in file /export/home/oracle/ORA7445st_20100225091554_COM_1.zip, mode
complete
To be able to send the information on the problem to Oracle
support we must first package the incident.
From the list of problems from the show problem command we can package
the specific problem we want to send to Oracle Support.
Using the ips create package command we can package a
problem.
Example:
adrci>
ips create package problem 1
Created
package 4 based on problem id 1, correlation level typical
adrci>
ips generate package 4
Generated
package 4 in file /export/home/oracle/ORA7445st_20100225091628_COM_1.zip, mode
complete
