Automatic Diagnostic Repository (ADR)

References: 3-20688058921, 1283137.1, 975448.1, 951022.1

Overview

The ADR is a utility that enables you to investigate problems, view health check reports and package diagnostic data to Oracle Support. With ADR you can use the console interface (ADRCI) or SQL to query for issues or ORA- errors.

Starting in release 11g, BACKGROUND_DUMP_DEST and USER_DUMP_DEST became deprecated. Both are replaced by the new DIAGNOSTIC_DEST parameter. The location is associated with ORACLE_BASE, if set, or ORACLE_HOME/log if not.

Polices

There are two time attributes which are used to manage the retention of information in ADR:

1. LONGP_POLICY (long term) defaults to 365 days and relates to things like Incidents and Health Monitor warnings.
2. SHORTP_POLICY (short term) defaults to 30 days and relates to things like trace and core dump files.

Usage

--ADR Console Interface
OS> adrci

--Help
adrci> help

Homes

--Display
adrci> show homes

--Display OS Path (to diagnostic_dest )
sql> show parameter diag

--Set
adrci> set homepath diag/rdbms/orcl/orcl;

Query via ADRCI

adrci> show alert -P "MESSAGE_TEXT LIKE '%ORA-600%'"
adrci> show alert
adrci> show incdir

Query via SQL

SELECT inst_id, originating_timestamp, message_text
FROM TABLE(gv$(cursor(select inst_id, originating_timestamp, message_text
from v$diag_alert_ext
WHERE originating_timestamp > (sysdate - 2)
AND message_text LIKE '%ORA-%')))
ORDER BY inst_id, originating_timestamp;

SELECT inst_id, originating_timestamp, message_text
FROM TABLE(gv$(cursor(select inst_id, originating_timestamp, message_text
from v$diag_alert_ext
WHERE originating_timestamp > (sysdate - 1/24)
AND message_text LIKE '%ORA-%')))
ORDER BY inst_id, originating_timestamp;

SELECT inst_id, originating_timestamp, message_text
FROM TABLE(gv$(cursor(select inst_id, originating_timestamp, message_text
from v$diag_alert_ext
WHERE originating_timestamp > (sysdate -  15/1440)
AND message_text LIKE '%ORA-%')))
ORDER BY inst_id, originating_timestamp;

Package

--Display (Incident numbers and detail.)
adrci> set homepath diag\rdbms\MySID\MySID
adrci> show incident

--Create
adrci> ips pack incident 1234 in c:\tmp
Creates a zip file with all corresponding info in specified directory.

Maintenance

Retention

--Display
adrci> show control

Value in hours.
 10 days = 240
 30 days = 720
 45 days = 1080
120 days = 2880
365 days = 8760

adrci> show homes
adrci> set homepath diag\rdbms\MySID\MySID;
adrci> set control (SHORTP_POLICY = 240);
adrci> set control (LONGP_POLICY = 1080);

-- Purge (Immediate)
adrci> purge
adrci> commit;

Recreate Homes

If you have a myriad of unrelated homepaths sometimes its just easier to recreate them. For instance, in Windows:

1. Shutdown the database.
2. Delete the diag dir (example: C:\app\oracle\diag).
3. Start the database.
4. Observe the that a new diag dir has been created with just two sub dirs (rdbms and tnslsnr).
5. Reset your retention policies as needed.

adr.purge.sh

 #!/bin/bash
 # Version: 2019.07.24b

 # Cfg
 me=$(basename ""$0"")
 fHomePaths="/u01/app/scripts/tmp/adr.purge.HomePaths.tmp";
 nRetention=30; # In Days. Example: 30
 aAdrTypes=(ALERT CDUMP HM TRACE UTSCDMP); # ALERT CDUMP HM INCIDENT TRACE UTSCDMP

 # Start
 clear;printf "$me: Started $(date "+m-H:S")\n"
 nMinutes=$(($nRetention*1440))
 printf "nRetention: $nRetention\n\n"

 # Process
 adrci exec="show homes"|grep -v : > $fHomePaths
 while read h; do
   printf "$h\n"
   for t in ${aAdrTypes[@]}; do
      printf "   $t\n"
      adrci exec="set homepath $h; purge -age $nMinutes -type $t"
   done
 done <$fHomePaths

 # End
 printf "$me: Ended $(date "+m-H:S")\n"

cron Entry

 00 05 * * * $SCRIPTS/adr.purge.sh     > $OUT/adr.purge.out 2>&1

Purge Command

  purge [-i {id | start_id end_id} | -age mins [-type ALERT|INCIDENT|TRACE|CDUMP|HM|UTSCDMP}] ] 

Docs page here.

<- System