ADRCI

Introduction

Automatic Diagnostic Repository Command Interpreter (ADRCI) utility has been introduced in Oracle 11g to manage Oracle Trace files and diagnostic data. You can read previous article on ADR architecture here

You need to execute show homes to see current homes supported by adrci

adrci> show homes
ADR Homes: 
diag/rdbms/snowy/snowy2
diag/rdbms/tintin/tintin1

You can verify the ORACLE_BASE by issuing show base

adrci> show base
ADR base is "/home/oracle"

In case you try to set incorrect base , it errors out

adrci> set base /home/oragrid
DIA-48447: The input path [/home/oragrid] does not contain any ADR homes

Most of adrci command work on single home, so need to set home first

adrci> set home diag/rdbms/tintin/tintin1

List tracefile with show tracefile

adrci> show tracefile -rt

In case you wish to see particular file

adrci> show tracefile %lgwr% -rt
   03-JAN-13 14:28:29  diag/rdbms/tintin/tintin1/trace/tintin1_lgwr_18841.trc

Database Alert Log

To view alert log, you need to set correct home and issue show alert

adrci>set home diag/rdbms/tintin/tintin1
adrci>show alert

You can also use tail command to view last num lines or -f

show alert [-tail [num] [-f]]

Some examples

Last 100 lines

show alert -tail 100

Last 100 lines and it will wait and report any new lines (implements Linux tail -f )

show alert -tail 100 -f

Since this is XML based alert log, you can search for ORA- errors or time based alerts.e.g I am checking entries reported in last 1 hr

adrci> show alert -p "ORIGINATING_TIMESTAMP >systimestamp -1/24" -term

term option is used as this will display it on screen else this opens the output in editor

You can combine searches

adrci> show alert -p "ORIGINATING_TIMESTAMP between systimestamp -2/24 and systimestamp -1/24"

Similarly you can look for database alerts

show alert -p "MESSAGE_TEXT like '%ORA-%'"

We can also say that report ORA errors reported in last 7 days

adrci>show alert -p "MESSAGE_TEXT like '%ORA-%' and ORIGINATING_TIMESTAMP >systimestamp -7" -term

Incident and Problems

Problem - A problem is a critical error in the database

Incident - An incident is a single occurrence of a problem. When a problem occurs multiple times, an incident is created for each occurrence.Incidents are flood-controlled so that a single problem does not generate too many incidents and incident dumps.These settings are automatically defined and cannot be modified.

Problem Key - Every problem has a problem key, which is a text string that includes an error code (such as ORA 600) and in some cases, one or more error parameters. Two incidents are considered to have the same root cause if their problem keys match.Below example problem key is ORA 445

You can get information about current problems with show problem command

adrci> show problem 

ADR Home = /home/oracle/diag/rdbms/tintin/tintin1:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME                             
-------------------- ----------------------------------------------------------- -------------------- ---------------------------------------- 
1                    ORA 445                                                     28012                2012-09-24 08:24:27.920000 +00:00       
1 rows fetched

List incident detail

adrci> show incident -mode detail -p "incident_id=24129"

ADR Home = /home/oracle/diag/rdbms/tintin/tintin1:
*************************************************************************

**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
   INCIDENT_ID                   24129
   STATUS                        ready
   CREATE_TIME                   2012-09-24 03:09:56.962000 +00:00
   PROBLEM_ID                    1
   CLOSE_TIME                    
   FLOOD_CONTROLLED              none
   ERROR_FACILITY                ORA
   ERROR_NUMBER                  445
   ERROR_ARG1                    GCR0
   ERROR_ARG2                    120
   ERROR_ARG3                    
   ERROR_ARG4                    
   ERROR_ARG5                    
   ERROR_ARG6                    
   ERROR_ARG7                    
   ERROR_ARG8                    
   ERROR_ARG9                    
   ERROR_ARG10                   
   ERROR_ARG11                   
   ERROR_ARG12                   
   SIGNALLING_COMPONENT          background_proc
   SIGNALLING_SUBCOMPONENT       
   SUSPECT_COMPONENT             
   SUSPECT_SUBCOMPONENT          
   ECID                          
   IMPACTS                       0
   PROBLEM_KEY                   ORA 445
   FIRST_INCIDENT                24129
   FIRSTINC_TIME                 2012-09-24 03:09:56.962000 +00:00
   LAST_INCIDENT                 28012
   LASTINC_TIME                  2012-09-24 08:24:27.920000 +00:00
   IMPACT1                       0
   IMPACT2                       0
   IMPACT3                       0
   IMPACT4                       0
   KEY_NAME                      ProcId
   KEY_VALUE                     16.1
   KEY_NAME                      Client ProcId
   KEY_VALUE                     [email protected]_47223367536176
   KEY_NAME                      SID
   KEY_VALUE                     4.1
   OWNER_ID                      1
   INCIDENT_FILE                 /home/oracle/diag/rdbms/tintin/tintin1/trace/tintin1_lmhb_18832.trc
   OWNER_ID                      1
   INCIDENT_FILE                 /home/oracle/diag/rdbms/tintin/tintin1/incident/incdir_24129/tintin1_lmhb_18832_i24129.trc
1 rows fetched

You can create package this incident to upload to oracle support

adrci> IPS CREATE PACKAGE INCIDENT 24129
Created package 1 based on incident id 24129, correlation level typical
adrci> IPS GENERATE PACKAGE 1 in /home/oracle
Generated package 1 in file /home/oracle/ORA445_20130103054329_COM_1.zip, mode complete

Listener Home

Starting Oracle 11g, architecture for trace file and log files have changed. Alert.log and listener.log files are now XML based.

To check listener log in 11g, following steps need to be done

$adrci

ADR base = "/home/oragrid/gridbase"
adrci> show homes
ADR Homes: 
diag/tnslsnr/oradbdev01/listener
diag/asm/+asm/+ASM1
adrci> set home diag/tnslsnr/oradbdev01/listener

Check location of listener log with below command

adrci> show tracefile
     diag/tnslsnr/oradbdev01/listener/trace/listener.log

There are various options to check listener log. Try anyone of following

adrci> show trace listener.log
adrci> view listener.log
adrci>show alert
adrci>show alert -tail

"show alert" can be used to checks last 200 lines for TNS-12523 errors

adrci> show alert -p "message_text like '%TNS-12523%'" -tail 200

ADR Maintenance

ADR metadata is purged automatically as per default policies. To check current policy for home, use show control command

adrci> show control

ADR Home = /home/oracle/diag/rdbms/tintin/tintin1:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME                              
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- 
1489850811           720                  8760                 2012-01-13 06:49:37.719445 +00:00        2013-01-03 23:17:09.351760 +00:00                                                 1                    2                    80                   1                    2012-01-13 06:49:37.719445 +00:00       
1 rows fetched

You can directly query the metadata table to easily read the information

adrci> select SHORTP_POLICY,LONGP_POLICY,LAST_AUTOPRG_TIME,LAST_MANUPRG_TIME from ADR_CONTROL;

ADR Home = /home/oracle/diag/rdbms/tintin/tintin1:
*************************************************************************
SHORTP_POLICY        LONGP_POLICY         LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        
-------------------- -------------------- ---------------------------------------- ---------------------------------------- 
720                  8760                 2013-01-03 23:17:09.351760 +00:00                                                
1 rows fetched

SHORTP_POLICY Number of hours after which to purge ADR contents that have a short life. Default is 720 (30 days).
LONGP_POLICY Number of hours after which to purge ADR contents that have a long life. Default is 8760 (365 days).

As per Oracle docs, information about incidents and problems is subject to purging after one year, whereas the associated dump files (dumps) are subject to purging after only 30 days.

So shortp_policy determines retention of dump files whereas longp_policy is for metadata information for incidents and problems

Set policy to 7 days (7*24=168 hours)

adrci> set control (SHORTP_POLICY=168)
adrci> select SHORTP_POLICY,LONGP_POLICY,LAST_AUTOPRG_TIME,LAST_MANUPRG_TIME from ADR_CONTROL;

ADR Home = /home/oracle/diag/rdbms/tintin/tintin1:
*************************************************************************
SHORTP_POLICY        LONGP_POLICY         LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        
-------------------- -------------------- ---------------------------------------- ---------------------------------------- 
168                  8760                 2013-01-03 23:17:09.351760 +00:00                                                
1 rows fetched

You can manually purge the ADR repository by using purge command. You can use help purge to know all options

Below command will purge all files under alert directory older then 14400 mins i.e 10 days

adrci> purge -age 14400 -type ALERT

Before purge

oradbdev01:tintin1:/home/oracle/diag/rdbms/tintin/tintin1/alert>ls -ltr|head
total 305976
-rw-r----- 1 oracle dba 10485833 Mar 10  2012 log_1.xml
-rw-r----- 1 oracle dba 10486021 Mar 17  2012 log_2.xml
-rw-r----- 1 oracle dba 10485975 Mar 25  2012 log_3.xml
-rw-r----- 1 oracle dba 10485979 Apr  1  2012 log_4.xml
-rw-r----- 1 oracle dba 10485945 Apr  9  2012 log_5.xml
-rw-r----- 1 oracle dba 10485931 Apr 16  2012 log_6.xml
-rw-r----- 1 oracle dba 10485988 Apr 24  2012 log_7.xml
-rw-r----- 1 oracle dba 10485883 May  1  2012 log_8.xml
-rw-r----- 1 oracle dba 10485990 May  8  2012 log_9.xml

Post purge

oradbdev01:tintin1:/home/oracle/diag/rdbms/tintin/tintin1/alert>ls -ltr|head
total 8436
-rw-r----- 1 oracle dba 8620402 Jan  4 06:28 log.xml

This updates LAST_MANUPRG_TIME with current manual purge time

adrci> select SHORTP_POLICY,LONGP_POLICY,LAST_AUTOPRG_TIME,LAST_MANUPRG_TIME from ADR_CONTROL;

ADR Home = /home/oracle/diag/rdbms/tintin/tintin1:
*************************************************************************
SHORTP_POLICY        LONGP_POLICY         LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        
-------------------- -------------------- ---------------------------------------- ---------------------------------------- 
168                  8760                 2013-01-03 23:17:09.351760 +00:00        2013-01-04 06:31:08.229272 +00:00

Note that purge command doesn't take care of purging text based alert_SID.log and listener.log. This still needs to be done manually or cron.

Using Help Commands

You can use help command to know the various commands

adrci>help

You can check additional commands by using help extended

adrci>help extended

To know more detail about command, type help command e.g

adrci>help show incident

Shortcuts

Below shortcut can be used to show alert log for current ORACLE_SID. I use this as Mac shortcut key 🙂

adrci exec="show homes"| egrep $ORACLE_SID | sed 's/^/ exec='set home / ; s/$/; show alert -tail -f '/" |xargs adrci

Related Articles

Leave a Reply