Managing ADR(Automatic Diagnostic Repository)

It has been quite cumbersome managing trace / log files as per retention defined manually , though shell scripting allowed us to manage it a bit but again the changing locations as per various oracle db versions/environments made it more tougher. So starting 11g ADR Command Interpreter (ADRCI) was introduced.

- It's a command-line tool that you use to manage Oracle Database diagnostic data.

- ADRCI enables:

a. Viewing diagnostic data within the Automatic Diagnostic Repository (ADR).

b. Viewing Health Monitor reports.

c. 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.

Below diagrametic representation of ADR directory structure.

Sharing practical scenario below , will surely help in clearing the doubts or queries.

I. ADR & adrci?

ADR (Automatic Diagnostic Repository) – It’s a centralized directory structure where one can find trace files, alert messages, incident dumps, core files, etc.

ADRCI is the command line utility using which ADR is accessed. It resides in

$ORACLE_HOME/bin

For eg. DHPO

ls -ltr $ORACLE_HOME/bin/adrci

-rwxr-xr-x 1 oracle dba 74064 Jul 20 2011 /oracle/product/11.2.0/db_1/bin/adrci

II. ADR Location –

Path specified in the 'diagnostic_dest' parameter defines the ADR root directory i.e. ADR BASE.

The first subdirectory inside an ADR (under the <adr_base> directory) is always named "diag".

Under ADR BASE ,the address of an <adr_home> will be similar to :
/<adr base>/diag/<product_type>/<prod_id>/<instance_id>.

i.e. For eg. in DHPO

/oracle/diag/rdbms/dhpo/DHPO/<..>

NAME

---------

DHPO

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

diagnostic_dest string /oracle

v$info_diag output below : This view gives all information regarding ADR specific to the database.

INST_ID NAME VALUE

---------- ------------------------- -----------------------------------------------------------------

1 Diag Enabled TRUE

1 ADR Base /oracle

1 ADR Home /oracle/diag/rdbms/dhpo/DHPO

1 Diag Trace /oracle/diag/rdbms/dhpo/DHPO/trace

1 Diag Alert /oracle/diag/rdbms/dhpo/DHPO/alert

1 Diag Incident /oracle/diag/rdbms/dhpo/DHPO/incident

1 Diag Cdump /oracle/diag/rdbms/dhpo/DHPO/cdump

1 Health Monitor /oracle/diag/rdbms/dhpo/DHPO/hm

1 Default Trace File /oracle/diag/rdbms/dhpo/DHPO/trace/DHPO_ora_20611288.trc

1 Active Problem Count 6

1 Active Incident Count 580

III. Usage of adrci

adrci is invoked as below –

dlp073:DHPO> adrci

ADRCI: Release 11.2.0.2.0 - Production on Fri Dec 7 01:15:56 2012

ADR base = "/oracle/diag_11g"

adrci>

IV. Checking/Setting retention Policy for ADR

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

Both attributes correspond to a number of hours after which the MMON (Memory Monitor) background process removes the expired ADR data.

1. SHORTP_POLICY : used to purge information that have a short life i.e. TRACE /CDUMP/UTSCDMP/IPS
Default is 720 = 30 days = approx. 1 month

2. LONGP_POLICY: to purge ADR contents that have a long life i.e. ALERT/INCIDENT/SWEEP/STAGE/HM

Default is 8760 = 365 days = 1 year.

To view and modify these settings, we can either use the Enterprise Manager 11g DB Control GUI or via the ADR Command Interpreter (ADRCI) started from an O/S prompt.

To check the current values of SHORTP_POLICY and LONGP_POLICY :

adrci> show control

ADR Home = /oracle/diag/rdbms/dhpo/DHPO:

*************************************************************************

ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME

-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------

2439362631 720 8760 2011-07-21 14:27:55.169783 -04:00 2012-11-30 23:27:27.340308 -05:00 1 2 80 1 2011-07-15 16:19:53.093057 -04:00

1 rows fetched

To set the retention policy values :

adrci> set control (LONGP_POLICY=720)

adrci> show control

ADR Home = /oracle/diag/rdbms/dhpo/DHPO:

*************************************************************************

ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME

2439362631 720 720 2012-12-07 01:59:14.384708 -05:00 2012-11-30 23:27:27.340308 -05:00 1 2 80 1 2011-07-15 16:19:53.093057 -04:00

1 rows fetched

To get help on the set control command :

adrci> help set control

V. Manual purging -

LAST_MANUPRG_TIME - shows the last manual purging done.

For eg. DHPO

adrci> purge -age 30240 -type TRACE {trace files deleted prior 21 days}

adrci> show control

ADR Home = /oracle/diag/rdbms/dhpo/DHPO:

*************************************************************************

ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME

2439362631 720 720 2012-12-07 01:59:14.384708 -05:00 2012-11-30 23:27:27.340308 -05:00 2012-12-07 02:12:14.931055 -05:00 1 2 80 1 2011-07-15 16:19:53.093057 -04:00

1 rows fetched

In Minutes {21 days converted to minutes 30240 (21*1440)}

References - Oracle Metalink docs [ID 422893.1] / [ID 564269.1] / [ID 975448.1]

ADRCI - Get off from manual trace/log removals

Monday, April 22, 2013

Managing ADR(Automatic Diagnostic Repository)

References - Oracle Metalink docs [ID 422893.1] / [ID 564269.1] / [ID 975448.1]