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.
