Recently, while troubleshooting an Oracle issue on a Windows server, I realized how many DBAs still depend only on alert logs and trace file to find out issues in database.
Finding the right trace file manually can become painful,
especially when multiple Oracle homes, listener logs, and incidents are
involved.
This is where ADRCI (Automatic Diagnostic Repository Command
Interpreter) becomes extremely useful.
I will show a handful commands which is very useful
1.
To know the current base location:-
|
C:\Windows\system32>adrci ADRCI:
Release 19.0.0.0.0 - Production on Fri May 22 19:27:21 2026 Copyright (c)
1982, 2024, Oracle and/or its affiliates.
All rights reserved. ADR base =
"D:\oracle" adrci>
show base ADR base is
"D:\oracle" |
2.
List out homes available
|
adrci> show homes ADR Homes: diag\clients\user_svc_concepts\host_3809360924_110 diag\clients\user_svc_concepts\RMAN_3809360924_110 diag\clients\user_SYSTEM\host_3809360924_110 diag\rdbms\orcl\orcl diag\tnslsnr\VSCSDB\listener |
3.
To set a specific home
|
adrci> set
home diag\rdbms\orcl\orcl |
4.
View alert log
|
adrci> show alert ADR Home =
D:\oracle\diag\rdbms\orcl\orcl: ************************************************************************* Output the results to file:
C:\Users\SVC_CO~1\AppData\Local\Temp\alert_16312_16036_orcl_1.ado Following command will tail the alert
log adrci> show alert -tail 100 -f 2026-05-22 20:04:31.421000 +04:00
nt secondary err code: 0
nt OS err code: 0
Client address:
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.60.85)(PORT=57204)) WARNING: inbound connection timed out
(ORA-3136) 2026-05-22 20:05:35.659000 +04:00 ….. adrci> show alert -p
"message_text like '%ORA-%'" ADR Home =
D:\oracle\diag\rdbms\orcl\orcl: ************************************************************************* Output the results to file:
C:\Users\SVC_CO~1\AppData\Local\Temp\alert_11648_5432_orcl_4.ado |
5.
Purge alerts and trace files
|
adrci> purge -age 1200 -type ALERT (This purges all alerts older than 1200
minutes) adrci> purge -age 43200 -type
INCIDENT(This purges all INCIDENT older than 43200 minutes OR 30 days) adrci> purge -age 21600 (This
purges everything older than 21600 minutes OR 15 days) adrci> purge -age 10080 -type cdump(This
purges cdump older than 10800 minutes OR 7 days) |
ADR automatically purges information from the repository.
You can see the defaults using the SHOW CONTROL command.
|
adrci>
show control ADR Home =
D:\oracle\diag\rdbms\orcl\orcl: ************************************************************************* ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME
LAST_AUTOPRG_TIME
LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME SIZEP_POLICY PURGE_PERIOD FLAGS PURGE_THRESHOLD --------------------
-------------------- --------------------
----------------------------------------
----------------------------------------
---------------------------------------- --------------------
-------------------- -------------------- --------------------
---------------------------------------- --------------------
-------------------- -------------------- -------------------- 1335663986 720
8760
2022-11-01 13:32:30.314000 +04:00 2026-05-21 23:51:14.096000
+04:00 2026-05-22
20:18:33.106000 +04:00 1 2 110 1 2022-11-01
13:32:30.314000 +04:00
18446744073709551615 0 0 95 1 row fetched |
The SHORTP_POLICY is of 720 hours (30 days)
applies to:
- Trace
files, including those files stored in the cdmp_timestamp sub-directories
- Core
dump files
- Packaging
information
The LONGP_POLICY of 8760 hours (365 days) applies to:
- Incident
information
- Incident
dumps
- Alert
logs
We can alter the default value using set control command
|
adrci> set
control (SHORTP_POLICY =240) adrci> set
control (LONGP_POLICY=4320) |
6.
Viewing Trace Files contents
|
adrci> show trace
D:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_7860.trc Output the results to file:
C:\Users\SVC_CO~1\AppData\Local\Temp\utsout_11648_5432_7.ado adrci> |
7.
Create incident package:
|
adrci>
show incident adrci>IPS
CREATE PACKAGE INCIDENT |
No comments:
Post a Comment