Oracle Automatic Diagnostic Repository (ADR)
and ADRCI (ADR Command Interpreter)
Version 19.3

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose The ADR and ADRCI form an essential component of the database and its ability to trace issues, report them to Oracle Support, and manage complex environments.

If you are not proficient with ADR and ADRCI you are as much a "Senior" DBA as you would be if you couldn't deploy RAC and Data Guard.
Product/Component Type Subdirectories Under ADR Base
ADR Directory Structure
Client Structure (OEL) /home/oracle/oradiag_oracle/diag/clients/user_oracle/host_2309071977_76/

[oracle@perrito4 host_2309071977_76]$ ls
alert cdump incident incpkg lck metadata stage sweep trace

Note: "When client side OCI or NET have an incident, an ADR directory is created in the user's home and the incident and its associated dump files are located within. The root is named oradiag_<username>. So, finding this directory structure indicates some failure on the client side. The reason the ADR is created in the user's home directory and not in the Client Oracle home: permissions.

I was puzzled that my db server host had this directory even though I never installed the Client there. Dev explained that "Every DB interaction uses OCI (SQLPLUS, for exampl) is 'from a client side'." Thus, you use it during an install, for example, and often without knowing it.
Queries show parameter diagnostic_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      /u01/orabase

set linesize 121
col value format a85

SELECT name, value FROM v$diag_info ORDER BY 2;

NAME                   VALUE
---------------------- ----------------------------------------------------------
Active Problem Count   4
Active Incident Count  5
ADR Base               /u01/orabase
ADR Home               /u01/orabase/diag/rdbms/orabasexx/orabasexx
Diag Alert             /u01/orabase/diag/rdbms/orabasexx/orabasexx/alert
Health Monitor         /u01/orabase/diag/rdbms/orabasexx/orabasexx/hm
Diag Incident          /u01/orabase/diag/rdbms/orabasexx/orabasexx/incident
Diag Trace             /u01/orabase/diag/rdbms/orabasexx/orabasexx/trace
Default Trace File     /u01/orabase/diag/rdbms/orabasexx/orabasexx/trace/
                       orabasexx_ora_20936.trc
Diag Cdump             /u01/orabase/diag/rdbms/orabasexx/orabasexx/cdump
ORACLE_HOME            /u01/orahome/db_home_1
Diag Enabled           TRUE
Change the ADR Repository  Location -- database
ALTER SYSTEM SET diagnostic_dest = <new location>;

-- listener
Add the parameter ADR_BASE_listener=<location> to the listener.ora file
 
ADRCI Comands
Purpose The ADR Command Interpreter (ADRCI) is a command-line tool that you use to manage Oracle Database diagnostic data. ADRCI is a command-line tool that is part of the fault diagnosability infrastructure introduced in Oracle Database Release 11g. ADRCI enables:
  • Viewing diagnostic data within the Automatic Diagnostic Repository (ADR)
  • Viewing Health Monitor reports
  • Packaging of incident and problem information into a zip file for transmission to Oracle Support
Diagnostic data includes incident and problem descriptions, trace files, dumps, health monitor reports, alert log entries, and more.

ADRCI has a rich command set, and can be used in interactive mode or within scripts. In addition, ADRCI can execute scripts of ADRCI commands in the same way that SQL*Plus executes scripts of SQL and PL/SQL commands.

ADR data is secured by operating system permissions on the ADR directories, hence there is no need to log in to ADRCI.
CREATE REPORT create report <report_type> <report_id>
col check_name format a30

SELECT run_id, name, check_name, run_mode, status, error_number
FROM v$hm_run
ORDER BY TO_NUMBER(run_id);

RUN_ID NAME        CHECK_NAME                    RUN_MODE STATUS     ERROR_NUMBER
------ ----------- ----------------------------- -------- ---------- ------------
   501 HM_RUN_501  DB Structure Integrity Check  REACTIVE COMPLETED             0
   521 HM_RUN_521  DB Structure Integrity Check  REACTIVE COMPLETED             0
   ..
  5041 HM_RUN_5041 DB Structure Integrity Check  REACTIVE COMPLETED             0
  5061 HM_RUN_5061 DB Structure Integrity Check  REACTIVE COMPLETED             0

$ adrci

ADRCI: Release 19.0.0.0.0 - Production on Wed Nov 4 06:59:15 2020
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

ADR base = "/u01/orabase"
adrci> set homepath diag/rdbms/orabasexx/orabasexx
adrci> set homepath diag/rdbms/orabase/orabase

adrci> create report hm_run HM_RUN_5061

adrci> show report hm_run hm_run_5061
<?xml version="1.0" encoding="US-ASCII"?>
<HM-REPORT REPORT_ID="HM_RUN_5061">
  <TITLE>HM Report: HM_RUN_5061</TITLE>
  <RUN_INFO>
    <CHECK_NAME>DB Structure Integrity Check</CHECK_NAME>
    <RUN_ID>5061</RUN_ID>
    <RUN_NAME>HM_RUN_5061</RUN_NAME>
    <RUN_MODE>REACTIVE</RUN_MODE>
    <RUN_STATUS>COMPLETED</RUN_STATUS>
    <RUN_ERROR_NUM>0</RUN_ERROR_NUM>
    <SOURCE_INCIDENT_ID>0</SOURCE_INCIDENT_ID>
    <NUM_INCIDENTS_CREATED>0</NUM_INCIDENTS_CREATED>
    <RUN_START_TIME>2020-09-03 09:31:08.318000 -05:00</RUN_START_TIME>
    <RUN_END_TIME>2020-09-03 09:31:08.479000 -05:00</RUN_END_TIME>
  </RUN_INFO>
  <RUN_PARAMETERS/>
  <RUN-FINDINGS/>
</HM-REPORT>
ECHO echo [quoted_string]
adrci> echo on
adrci> echo "Echoing ADR"
adrci> echo off
ESTIMATE

Provide an estimate for age or size of the ADR home		 estimate (<purge_policy_name>=<value>, ...)

Valid purge policy names are "SHORTP_POLICY", "LONGP_POLICY" "SIZEP_POLICY".
Valid values are expressed in hours or bytes
adrci> estimate (SHORTP_POLICY = 720)
Estimate
Short Policy Hours: 720
Long Policy Hours: 876
EXIT exit
$ adrci

adrci> exit
HELP help [<topic_name>]
adrci> help
HOST host
$C:\u01\orahome\db_home1\bin>adrci

adrci> host
Microsoft Windows [Version 10.0.18363.959]
(c) 2019 Microsoft Corporation. All rights reserved.

C:\u01\orahome\db_home1\bin>

-- return to the adrci command prompt
 exit
IPS ADD

Adds an incident to an existing package		 incident <incid> | PROBLEM <prob_id> | PROBLEMKEY <prob_key> |
SECONDS <seconds> | TIME <start_time> TO <end_time>] PACKAGE <package_id>
adrci> ips add incident 22 package 12
IPS ADD FILE
Adds a file to an existing package		 ips add file <file_spec> PACKAGE <pkgid>
TBD
IPS ADD NEW INCIDENTS
Find new incidents for the problems in the specified package, and adds the latest ones to the package		 ips add new incidents PACKAGE <package_id>
adrci> ips add new incidents package 12
IPS CHECK REMOTE KEYS
Checks for incidents matching the keys in the specified file		 ips check remote keys FILE <file_spec>
TBD
IPS COPY IN FILE
Copies an external file into the ADR, and associate it with a package and (optionally) an incident.		 ips copy in file <file> [TO <new_name>] [OVERWRITE] PACKAGE <pkgid> [INCIDENT <incid>]
TBD
IPS COPY OUT FILE
Copies an ADR file to a location outside the ADR		 ips copy out file <source> TO <target> [OVERWRITE]
TBD
IPS CREATE PACKAGE INCIDENT ips create package incident <incident_number> [CORRELATE <BASIC | TYPICAL | ALL>]
adrci> ips create package incident 22
IPS CREATE PACKAGE PROBLEM ips create package problem <problem_id>
[CORRELATE <BASIC | TYPICAL | ALL>]
adrci> ips create package problem ?
IPS CREATE PACKAGE PROBLEMKEY ips create package problemkey <'problem_key'> [CORRELATE <BASIC | TYPICAL | ALL>]
adrci> ips create package problemkey '?'
adrci> ips create package problemkey "?"
IPS CREATE PACKAGE SECONDS ips create package seconds <sec> [CORRELATE <BASIC | TYPICAL | ALL>]
adrci> ips create package seconds 600 CORRELATE ALL
IPS CREATE PACKAGE TIME ips create package time <'start_timestamp'> TO <'end_timestamp'>
[CORRELATE <BASIC | TYPICAL | ALL>]
adrci> ips create package time '2020-11-04 00:00:00 -06:00' to '2020-11-11 23.59.59 -06:00'
IPS DELETE PACKAGE
Drops a package and its contents		 ips delete package <package_id>
adrci> ips delete package time 22
IPS FINALIZE PACKAGE
Prepare a package for shipping by including correlated content		 ips finalize package <package_id> [CALLOUTS (OPATCH, CRS, OCM, OSS, RDA)]
[MANIFEST <file_spec>]
adrci> ips finalize package 12 callouts (OPATCH, CRS)
IPS GENERATE PACKAGE ips generate package <package_id> [IN <path>][<COMPLETE | INCREMENTAL>]
adrci> ips generate package 12 INCREMENTAL
IPS GET MANIFEST
Extracts and displays a package's manifest		 ips get manifest FROM FILE <file_path_and_name>
adrci> ips get manifest from file /tmp/IPSPKG_200704130121_COM_1.zip
IPS GET METADATA
Extracts and displays a package's metadata XML document		 ips get metadata [FROM FILE <file_path_and_name> | FROM ADR]
adrci> ips get metadata from file /tmp/IPSPKG_200704130121_COM_1.zip
IPS GET REMOTE KEYS
Create a file with keys matching incidents in specified package		 ips get remote keys FILE <file_spec> PACKAGE <package_id>
adrci> ips get remote keys file /tmp/key_file.txt package 12
IPS PACK
Create a package, and immediately generate the physical package		 ips pack
[INCIDENT <incid> | PROBLEM <prob_id> | PROBLEMKEY <prob_key> |
SECONDS <seconds> | TIME <start_time> TO <end_time>]
[CORRELATE BASIC | TYPICAL | ALL]
[MANIFEST <file_spec>] [KEYFILE <file_spec>] [IN <path>]
ips pack incident 861;

ips pack time '2020-10-31 23:59:59.00 -06:00' to
'2020-11-04 01:01:01.00 -06:00';
IPS REMOVE ips remove [INCIDENT <incid> | PROBLEM <prob_id> | PROBLEMKEY <prob_key> ]
PACKAGE <package_id>
adrci> ips remove incident 22 package 12
IPS REMOVE FILE ips remove file <file_spec> PACKAGE <pkgid>
TBD
IPS SET CONFIGURATION IPS SET CONFIGURATION <parameter_id> <value>
ips set configuration 6 2
IPS SHOW ips show incidents package pkg_id
adrci> show homes

ADR Homes:
diag/rdbms/orabasexx/orabasexx

adrci> set home diag\rdbms\orabasexx\orabasexx
adrci> ips show incident
IPS SHOW CONFIGURATION ips show configuration
adrci> show homes

adrci> set home diag/rdbms/orabasexx/orabasexx

adrci> ips show configuration
PARAMETER INFORMATION:
   PARAMETER_ID 1
   NAME CUTOFF_TIME
   DESCRIPTION Maximum age for an incident to be considered for inclusion
   UNIT Days
   VALUE 90
   DEFAULT_VALUE 90
   MINIMUM 1
   MAXIMUM 4294967295
   FLAGS 0

PARAMETER INFORMATION:
   PARAMETER_ID 2
   NAME NUM_EARLY_INCIDENTS
   DESCRIPTION How many incidents to get in the early part of the range
   UNIT Number
   VALUE 3
   DEFAULT_VALUE 3
   MINIMUM 1
   MAXIMUM 4294967295
   FLAGS 0

PARAMETER INFORMATION:
   PARAMETER_ID 3
   NAME NUM_LATE_INCIDENTS
   DESCRIPTION How many incidents to get in the late part of the range
   UNIT Number
   VALUE 3
   DEFAULT_VALUE 3
   MINIMUM 1
   MAXIMUM 4294967295
   FLAGS 0

PARAMETER INFORMATION:
   PARAMETER_ID 4
   NAME INCIDENT_TIME_WINDOW
   DESCRIPTION Incidents this close to each other are considered correlated
   UNIT Minutes
   VALUE 5
   DEFAULT_VALUE 5
   MINIMUM 1
   MAXIMUM 4294967295
   FLAGS 0

PARAMETER INFORMATION:
   PARAMETER_ID 5
   NAME PACKAGE_TIME_WINDOW
   DESCRIPTION Time window for content inclusion is from x hours before first included    incident to x hours after last incident
   UNIT Hours
   VALUE 24
   DEFAULT_VALUE 24
   MINIMUM 1
   MAXIMUM 4294967295
   FLAGS 0

PARAMETER INFORMATION:
   PARAMETER_ID 6
   NAME DEFAULT_CORRELATION_LEVEL
   DESCRIPTION Default correlation level for packages
   UNIT Number
   VALUE 2
   DEFAULT_VALUE 2
   MINIMUM 1
   MAXIMUM 4
   FLAGS 0
IPS SHOW FILES ips show files PACKAGE <package_id>
adrci> ips show files package 12
IPS SHOW INCIDENTS ips show incidents PACKAGE <package_id>
adrci> ips show incidents package 12
IPS SHOW PACKAGE ips show package <package_id> [BASIC | BRIEF | DETAIL]
adrci> ips show package

adrci> ips show package 12 detail
IPS UNPACK ips unpack file file_name [into path]
adrci> ips unpack file /home/oracle/ORA603_20180206165316_COM_1.zip into /tmp/newadr
IPS UNPACK FILE
Unpackages a physical file into the specified path automatically creating an ADR_HOME		 ips unpack file <file_spec> [INTO <path>]
ips unpack file /tmp/IPSPKG_20061026010203_COM_1.zip into /tmp/newadr
IPS UNPACK PACKAGE ips unpack package <pkg_name> [INTO <path>]
adrci> ips unpack package IPSPKG_20061026010203 into /tmp/newadr
IPS USE REMOTE KEYS ips use remote keys FILE <file_spec> PACKAGE <package_id>
adrci> ips use remote keys file /tmp/key_file.txt package 12
PURGE purge [[-i {id1 | start_id end_id}] [-age mins] [-type {ALERT | INCIDENT | TRACE | CDUMP | HM}]
adrci> purge -i 1238673908 1440

adrci> purge -age 900 -type incident
QUIT quit
$ adrrci

adrci> quit
RUN run script_name

@ script_name

@@ script_name
adrci> run uwadrscript
SET BASE set base <base_path>
adrci> set base /apps/oracle/product
SET BROWSER set browser browser_program
adrci> set browser firefox
SET CONTROL set control (<SHORTP_POLICY | LONGP_POLICY> = value, ...)
adrci> set control (SHORTP_POLICY = 360)
SET ECHO set echo <ON | OFF>
adrci> set echo off
SET EDITOR set editor editor_program
adrci> set editor vi
SET HOME set home <home_location>
adrci> show homes

adrci> set home diag\rdbms\orabasexx\orabasexx

adrci> show home
SET HOMES set homes <home_location1>, <home_location2>, <...>
adrci> show homes

adrci> set homes diag\rdbms\orabase\orabase, diag\tnslsnr\perrito4\listener

adrci> show homes
SET HOMEPATH set homepath homepath_str1 [homepath_str2] [...]
adrci> show homepath

adrci> set homepath diag\rdbms\orabase\orabase

adrci> show homepath
SET TERMOUT termout <ON | OFF>
adrci> set termout on

adrci> set termout off
SHOW ALERT show alert [-p predicate_string] [-tail [num] [-f]] [-term] [-file alert_file_name]
$ adrci exec="show homes; show incident"

adrci> show alert

Choose the alert log from the following homes to view:

1. diag/client/user_unknown/host_411310321_11
2. diag/rdbms/orabasexx/orabasexx
3. diag/tnslsnr/bigdog/listener
Q: to quit

Q

$ adrci

adrci> set home diag\rdbms\orabase\orabase

adrci> show alert

 ADR Home = C:\u01\orabase\diag\rdbms\orabasexx\orabasexx:
*************************************************************************
Output the results to file: C:\Users\oracle\AppData\Local\Temp\alert_13036_23012_orabasexx_1.ado

adrci> show alert 25
Tail the Alert Log -- open a terminal window
adrci> show home

adrci> set homepath diag\rdbms\orabase\orabase

adrci> show alert -tail -f

-- to verify
SQL> ALTER SYSTEM SWITCH LOGFILE;
Show only lines with specific text -- open a terminal window
adrci> show home

adrci> set homepath diag\rdbms\orabase\orabase

adrci> show alert -P "MESSAGE_TEXT LIKE '%ORA-7445%'";
SHOW BASE show base
adrci> show base

ADR base is "c:\oracle\product"
SHOW CONTROL show control
adrci> set homepath diag\rdbms\orabase\orabase

adrci> show control
SHOW HM_RUN show hm_run [-p predicate_string]
adrci> show hm_run
SHOW HOME show home
adrci> show home
SHOW HOMES show homes
adrci> show homes
SHOW HOMEPATH show homepath
adrci> show homepath
SHOW INCDIR show incdir [id | id_low id_high]
adrci> show incdir
SHOW INCIDENT show incident -p predicate_string [-mode {BASIC|BRIEF|DETAIL}]
[-orderby field1, field2, ...] [ASC|DSC]
adrci> show incident
SHOW LOG show log [-l log_name] [-tail [number_of_lines] [-f (follow)] [-term]
adrci> show log
adrci> show log -l debug
adrci> show log -l ddl -tail 25
adrci> show log -p "message_text like '%tablespace%'"
SHOW PROBLEM show problem [-p predicate_string] [-last num | -all]
[-orderby field1, field2, ...] [ASC|DSC]]
adrci> show problem
SHOW REPORT show report report_type run_name
-- need to define a single home
conn / as sysdba

SELECT run_id, name, check_name, run_mode, status, error_number
FROM gv$hm_run;

adrci> set homepath diag\rdbms\orabase\orabase

adrci> show report hm_run hm_run_521
SET TRACEFILE show tracefile [file1 file2 ...] [-rt | -t] [-i inc1 inc2 ...]
[-path path1 path2 ...]
adrci> show tracefile %mmon% -rt

adrci> show tracefile -i 1 4 -path diag/rdbms/orabase/orabase
SPOOL SPOOL filename [[APPEND] | [OFF]]
spool file_name_and_path [<APPEND | OFF>]
adrci> spool /home/oracle/adr.log
adrci> show alert -term
adrci> spool off
 
ADR Metadata Folder
The ADR Metadata folder contains, right now, 44 files that are external tables queryable via adrci using SQL: External tables that are not visible in CDB_OBJECTS_AE. Start by making a list of the current metadata file names and then try these examples using your file names if they differ from mine.
Incidents Query adrci> show homes

adrci> set home diag\rdbms\orabasexx\orabasexx

adrci> desc dfw_config_item
Name                 Type            NULL?
-------------------- --------------- -----------
HASH_VALUE           number
CATEGORY             number
SOURCE               text(64)
ITEM_NAME            text(64)
ITEM_VALUE           text(512)

adrci> desc incident

-- the statement must be written on one line as I have done here ... you can not hit enter and continue on a new line
adrci>  SELECT create_time, status, suspect_component, impact FROM incident;

adrci> CREATE VIEW adr_test_view AS SELECT problem_id, impact1 FROM problem;

adrci> desc adr_test_view

adrci> SELECT * FROM adr_test_view;

SQL> conn / as sysdba

-- the view does not exist as a database object
SQL> SELECT owner, object_type, last_ddl_time
     FROM cdb_objects_ae
     WHERE object_name = 'ADR_TEST_VIEW';

adrci> DROP VIEW adr_test_view;
-- the view definition is stored in view.ams and viewcol.ams
 
Demo
ADRCI Demo SQL> conn / as sysdba

SQL> desc gv$hm_run

SQL> SELECT run_id, name, check_name, run_mode, status, error_number
  2  FROM gv$hm_run;

Related Topics
Built-in Functions
Built-in Packages
DBMS_ADR
DBMS_HM
DBMS_IR
DBMS_SQLDIAG
ORADEBUG
RMAN
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx