It's a file-based repository for database diagnotic data such as traces, incident dumps and packages, the alert logs, Health Monitor reports, core dumps, and more.
You can run select * from v$diag_info to list some important ADR locations
INST_ID NAME VALUE
---------- ------------------------------ ----------------------------------------------------------------------------
2 Diag Enabled TRUE
2 ADR Base /apps/oracle/grid
2 ADR Home /apps/oracle/grid/diag/rdbms/DBADG/DBADG2
2 Diag Trace /apps/oracle/grid/diag/rdbms/DBADG/DBADG2/trace
2 Diag Alert /apps/oracle/grid/diag/rdbms/DBADG/DBADG2/alert
2 Diag Incident /apps/oracle/grid/diag/rdbms/DBADG/DBADG2/incident
2 Diag Cdump /apps/oracle/grid/diag/rdbms/DBADG/DBADG2/cdump
2 Health Monitor /apps/oracle/grid/diag/rdbms/DBADG/DBADG2/hm
2 Default Trace File /apps/oracle/grid/diag/rdbms/DBADG/DBADG2/trace/DBADG2_ora_11022.trc
2 Active Problem Count 15
2 Active Incident Count 92
ADRCI - The ADR Command-Line Tool
adrci - help
help set control
help show tracefile
help purge
help extended
set homepath /diag/rdbms/dbadb/DBDBA1
adrci> ips show configurations
show home
show base
show homes
show control (SHORTP_POLICY: purge information 720 - 30 days, LONGP_POLICY: Default is 8760 = 365 days = 1 year)
set control (SHORTP_POLICY = 1440)
show alert
show alert -tail
show alert -tail 50
show alert -tail -F
show alert -tail 20 -f
show alert -TERM
show alert -P "MESSAGE_TEXT lik '%ORA-600%'"
adrci> set homepath diag/rdbms/dbadb/DBDBA1
adrci> show problem
adrci> show incident
show tracefile
show tracefile -RT
show tracefile %lmd%
host "ls -ltr diag/asm/+asm/+ASM1/trace/+ASM1_ping_19024.trc"
host "cat diag/asm/+asm/+ASM1/trace/+ASM1_ping_19024.trc | more "
show incident
show problem
show incident -MODE DETAIL
SHOW INCIDENT -MODE BRIEF
show incident
show incident
ips create package incident 7
ips create package (empty package)
ips create package problemkey "ORA 1578"
ips create package time '2010-09-12 12:00:00' to '2010-09-12 17:00:00' in /tmp
ips create package 6 in /tmp ==> upload this file to oracle support
ips show configuration
ips show configuration 7
ips show files package
adrci> purge -age 6 -type CDUMP
adrci> purge -age 0 -type alert
adrci> purge -age 120 -type alert (delete parts of the alert.log everything except for the last 120 minutes)
adrci> query (PACKAGE_ID,NAME)IPS_PACKAGE
adrci> query (PACKAGE_ID, FILE_ID, EXCLUDE) IPS_PACKAGE_FILE -p "PACKAGE_ID =6"
adrci> ips show files package 6
adrci> ips generate package 6
adrci> ips generate package 6
cp: omitting directory `/apps/oracle/product/11.2.0/grid/log/lltcind01/racg/racgeut'
cp: omitting directory `/apps/oracle/product/11.2.0/grid/log/lltcind01/racg/racgmain'
Generated package 6 in file /apps/oracle/grid/IPSPKG_20100914215639_COM_2.zip, mode complete
How to create files and update for Oracle Support
adrci> show problem
ADR Home = /apps/oracle/grid/diag/rdbms/dbadb/DBADG2:
*************************************************************************
PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
5 ORA 600 [17114] 37572 2010-09-22 21:48:56.147000 -05:00
6 ORA 600 [17182] 37571 2010-09-22 21:48:54.646000 -05:00
4 ORA 7445 [kghprmalo()+224] 37609 2010-09-22 21:15:34.639000 -05:00
3 ORA 7445 [qerandvRop()+228] 37569 2010-09-22 18:58:05.133000 -05:00
1 ORA 600 [kghGetHpSz1] 37602 2010-09-22 18:38:19.239000 -05:00
2 ORA 7445 [rworofprFastUnpackRow()+91] 37601 2010-09-22 18:38:15.839000 -05:00
6 rows fetched
adrci> show tracefile -I 37601
diag/rdbms/dbadb/DBADG2/incident/incdir_37601/DBADG2_ora_25720_i37601.trc
adrci> show incident
ADR Home = /apps/oracle/grid/diag/rdbms/dbadb/DBADG2:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
37601 ORA 7445 [rworofprFastUnpackRow()+91] 2010-09-22 18:38:15.839000 -05:00
37602 ORA 600 [kghGetHpSz1] 2010-09-22 18:38:19.239000 -05:00
37521 ORA 7445 [qerandvRop()+228] 2010-09-22 18:57:59.301000 -05:00
37505 ORA 7445 [qerandvRop()+228] 2010-09-22 18:58:00.573000 -05:00
37553 ORA 7445 [qerandvRop()+228] 2010-09-22 18:58:02.896000 -05:00
37569 ORA 7445 [qerandvRop()+228] 2010-09-22 18:58:05.133000 -05:00
37609 ORA 7445 [kghprmalo()+224] 2010-09-22 21:15:34.639000 -05:00
37603 ORA 7445 [kghprmalo()+224] 2010-09-22 21:15:34.639000 -05:00
37570 ORA 600 [17114] 2010-09-22 21:48:30.610000 -05:00
37577 ORA 600 [17182] 2010-09-22 21:48:48.455000 -05:00
37593 ORA 600 [17182] 2010-09-22 21:48:48.519000 -05:00
37585 ORA 600 [17182] 2010-09-22 21:48:48.565000 -05:00
37586 ORA 600 [17114] 2010-09-22 21:48:51.209000 -05:00
37594 ORA 600 [17114] 2010-09-22 21:48:51.218000 -05:00
37578 ORA 600 [17114] 2010-09-22 21:48:51.341000 -05:00
37571 ORA 600 [17182] 2010-09-22 21:48:54.646000 -05:00
37572 ORA 600 [17114] 2010-09-22 21:48:56.147000 -05:00
adrci > show incident -mode brief
adrci > show incident -mode detail
=================================================================================================================================
PACKAGE INCIDENTS for transmission to Oracle Support for Analysis
===================================================================
adrci> show incident
ADR Home = /apps/oracle/grid/diag/rdbms/dbadb/DBADG2:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
37601 ORA 7445 [rworofprFastUnpackRow()+91] 2010-09-22 18:38:15.839000 -05:00
37602 ORA 600 [kghGetHpSz1] 2010-09-22 18:38:19.239000 -05:00
37521 ORA 7445 [qerandvRop()+228] 2010-09-22 18:57:59.301000 -05:00
37505 ORA 7445 [qerandvRop()+228] 2010-09-22 18:58:00.573000 -05:00
37553 ORA 7445 [qerandvRop()+228] 2010-09-22 18:58:02.896000 -05:00
37569 ORA 7445 [qerandvRop()+228] 2010-09-22 18:58:05.133000 -05:00
37609 ORA 7445 [kghprmalo()+224] 2010-09-22 21:15:34.639000 -05:00
37603 ORA 7445 [kghprmalo()+224] 2010-09-22 21:15:34.639000 -05:00
37570 ORA 600 [17114] 2010-09-22 21:48:30.610000 -05:00
37577 ORA 600 [17182] 2010-09-22 21:48:48.455000 -05:00
37593 ORA 600 [17182] 2010-09-22 21:48:48.519000 -05:00
37585 ORA 600 [17182] 2010-09-22 21:48:48.565000 -05:00
37586 ORA 600 [17114] 2010-09-22 21:48:51.209000 -05:00
37594 ORA 600 [17114] 2010-09-22 21:48:51.218000 -05:00
37578 ORA 600 [17114] 2010-09-22 21:48:51.341000 -05:00
37571 ORA 600 [17182] 2010-09-22 21:48:54.646000 -05:00
37572 ORA 600 [17114] 2010-09-22 21:48:56.147000 -05:00
adrci> ips create package incident 37572
Created package 1 based on incident id 37572, correlation level typical
adrci> show problem
adrci > show incident
adrci> ips create package problemkey "ORA 7445 [kghfrf()+265]"
Created package 7 based on problem key ORA 7445 [kghfrf()+265], correlation level typical ==> create a logical package, package 2
adrci> ips generate package 7 in /data/oracle/bkup1
adrci> ips create package problemkey "ORA 7445 [qerandvRop()+228]"
Created package 7 based on problem key ORA 7445 [qerandvRop()+228], correlation level typical
adrci> ips generate package 7 in /data/oracle/bkup1
cp: omitting directory `/apps/oracle/product/11.2.0/grid/log/lltcind01/racg/racgeut'
cp: omitting directory `/apps/oracle/product/11.2.0/grid/log/lltcind01/racg/racgmain'
Generated package 7 in file /data/oracle/bkup1/ORA7445qe_20100922223329_COM_1.zip, mode complete ==> this file upload for Oracle suppport.
*******************************
Purging trace and dump files
*******************************
LONGP_POLICY (long term) defaults to 365 (8760 hrs) days and relates to things like Incidents and Health Monitor warnings.
SHORTP_POLICY (short term) defaults to 30 (720 hrs) days and relates to things like trace and core dump files
adrci> show control
ADR Home = /apps/oracle/grid/diag/rdbms/dbadb/DBDBA1:
*************************************************************************
ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
3667195108 720 8760 2010-09-17 01:08:43.999867 -05:00 2010-09-25 00:28:01.040078 -05:00 1 2 76 1 2010-07-22 16:17:20.848469 -05:00
adrci> set control (SHORTP_POLICY = 360)
adrci > purge -age 8640 -type TRACE (purging all trace files other than 6 days)
Great information. The most useful summary of ADR I have read yet. Thank you for your detail.
ReplyDelete-
Howard