Using ADRCI to Review Recurrent Issues

Reviewing recurrent issues for database systems is generally a monthly task. If there were only one system and one database, it would be a simple exercise, however, there are many systems and many databases per system.

This is where ADRCI is very useful.

From Oracle’s documentation, Automatic Diagnostic Repository Command Interpreter (ADRCI) utility is a command-line tool you can use to manage Oracle Database diagnostic data.

Please note, it’s important to use the highest installed version for ADRCI; otherwise you face failure because a lower version can’t read data from a higher version. 

DEMO:

### Start adrci:

$ /home/oracle>adrci

ADRCI: Release 18.0.0.0.0 - Production on Mon Dec 28 08:36:41 2020

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/opt/oracle"

### Find homes - in this example, there is only on database (xxxxxdb):

adrci> show homes
ADR Homes:
diag/clients/user_oracle/host_1556917096_110
diag/crs/xxxxxx/crs
diag/kfod/xxxxxx/kfod
diag/rdbms/xxxxxdb/xxxxxdb
diag/asmcmd/user_oracle/xxxxxx.dr
diag/asmcmd/user_pythian/xxxxxx.dr
diag/tnslsnr/xxxxxx/listener
diag/asmtool/user_oracle/host_1556917096_110
diag/asm/+asm/+ASM
adrci>

### Show incidents for all ADR Homes:

adrci> show incident

ADR Home = /opt/oracle/diag/clients/user_oracle/host_1556917096_110:
*************************************************************************
0 rows fetched

ADR Home = /opt/oracle/diag/crs/xxxxxx/crs:
*************************************************************************
0 rows fetched

ADR Home = /opt/oracle/diag/kfod/xxxxxx/kfod:
*************************************************************************
0 rows fetched

ADR Home = /opt/oracle/diag/rdbms/xxxxxdb/xxxxxdb:
*************************************************************************
0 rows fetched

ADR Home = /opt/oracle/diag/asmcmd/user_oracle/xxxxxx.dr:
*************************************************************************
0 rows fetched

ADR Home = /opt/oracle/diag/asmcmd/user_pythian/xxxxxx.dr:
*************************************************************************
0 rows fetched

ADR Home = /opt/oracle/diag/tnslsnr/xxxxxx/listener:
*************************************************************************
0 rows fetched

ADR Home = /opt/oracle/diag/asmtool/user_oracle/host_1556917096_110:
*************************************************************************
0 rows fetched

ADR Home = /opt/oracle/diag/asm/+asm/+ASM:
*************************************************************************
0 rows fetched

adrci>

### Show problem for all ADR Homes:

adrci> show problem

ADR Home = /opt/oracle/diag/clients/user_oracle/host_1556917096_110:
*************************************************************************
0 rows fetched

ADR Home = /opt/oracle/diag/crs/xxxxxx/crs:
*************************************************************************
0 rows fetched

ADR Home = /opt/oracle/diag/kfod/xxxxxx/kfod:
*************************************************************************
0 rows fetched

ADR Home = /opt/oracle/diag/rdbms/xxxxxdb/xxxxxdb:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
3                    ORA 4030                                                    195688               2020-11-11 22:02:59.404000 -08:00

ADR Home = /opt/oracle/diag/asmcmd/user_oracle/xxxxxx.dr:
*************************************************************************
0 rows fetched

ADR Home = /opt/oracle/diag/asmcmd/user_pythian/xxxxxx.dr:
*************************************************************************
0 rows fetched

ADR Home = /opt/oracle/diag/tnslsnr/xxxxxx/listener:
*************************************************************************
0 rows fetched

ADR Home = /opt/oracle/diag/asmtool/user_oracle/host_1556917096_110:
*************************************************************************
0 rows fetched

ADR Home = /opt/oracle/diag/asm/+asm/+ASM:
*************************************************************************
0 rows fetched

adrci>

### Check logs for any ORA- errors for all ADR Homes:

adrci> show alert -p "message_text like '%ORA-%' and originating_timestamp > systimestamp-90" -term

ADR Home = /opt/oracle/diag/clients/user_oracle/host_1556917096_110:
*************************************************************************

ADR Home = /opt/oracle/diag/crs/xxxxxx/crs:
*************************************************************************

ADR Home = /opt/oracle/diag/kfod/xxxxxx/kfod:
*************************************************************************

ADR Home = /opt/oracle/diag/rdbms/xxxxxdb/xxxxxdb:
*************************************************************************
2020-12-01 05:43:13.305000 -08:00
ORA-3297 signalled during: alter database tempfile '+DATA/xxxxxdb/tempfile/temp.733.1005878735' resize 1g...
2020-12-01 05:47:52.208000 -08:00
ORA-3297 signalled during: alter database tempfile '+DATA/xxxxxdb/tempfile/temp.733.1005878735' resize 10g...
2020-12-07 08:51:50.037000 -08:00
Errors in file /opt/oracle/diag/rdbms/xxxxxdb/xxxxxdb/trace/xxxxxdb_nsa2_27234.trc:
ORA-03135: connection lost contact
2020-12-10 07:00:06.983000 -08:00
ORA-00060: Deadlock detected. More info in file /opt/oracle/diag/rdbms/xxxxxdb/xxxxxdb/trace/xxxxxdb_ora_106445.trc.
2020-12-11 13:00:07.439000 -08:00
ORA-00060: Deadlock detected. More info in file /opt/oracle/diag/rdbms/xxxxxdb/xxxxxdb/trace/xxxxxb_ora_106393.trc.

ADR Home = /opt/oracle/diag/asmcmd/user_oracle/xxxxxx.dr:
*************************************************************************

ADR Home = /opt/oracle/diag/asmcmd/user_pythian/xxxxxx.dr:
*************************************************************************

ADR Home = /opt/oracle/diag/tnslsnr/xxxxxx/listener:
*************************************************************************

ADR Home = /opt/oracle/diag/asmtool/user_oracle/host_1556917096_110:
*************************************************************************

ADR Home = /opt/oracle/diag/asm/+asm/+ASM:
*************************************************************************
adrci>

In conclusion, it’s much simpler to use ADRCI utility to review recurrent issues. It will save time compared to manually finding all the directories, and will prevent any directories from being missed out.

Please try it and let me know what you think.