Blog

In this article, I will provide a detailed guide on How to find alert log file location in Oracle database. We’ll walk you through each step of the process in my VirtualBox.

1. If the database is open:

You can use the below query after connecting with sqlplus / as sysdba to check alert log file location in oracle.

COLUMN INST_ID FORMAT 9999 HEADING 'INST_ID'
COLUMN CON_ID FORMAT 9999 HEADING 'CON_ID'
COLUMN VALUE FORMAT A50 WRAP HEADING 'VALUE'
SELECT INST_ID, CON_ID, VALUE FROM v$diag_info WHERE NAME = 'Diag Trace';

OUTPUT:

Now go to the trace file directory and use the tail command to get the last N lines of the file. The -f parameter refreshes the screen whenever the file is updated.

[oracle@srv1 ~]$ cd /u01/app/oracle/diag/rdbms/oradb/oradb/trace
[oracle@srv1 trace]$ ls -ltr alert*
-rw-r----- 1 oracle oinstall 262412 Jul  4 01:02 alert_oradb.log
[oracle@srv1 trace]$ tail -200f alert_oradb.log

2. If the database is not open:

You can use the ADRCI utility, which stands for Automatic Diagnostic Repository Command Interface. To find alert log file in Oracle, first use the ADR base followed by the ADR home, and after that add the trace folder at the end.

[oracle@srv1 ~]$ adrci
ADRCI: Release 19.0.0.0.0 - Production on Wed Jul 3 18:07:37 2024
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
ADR base = "/u01/app/oracle"

adrci> show homes
ADR Homes:
diag/rdbms/oradb/oradb
diag/tnslsnr/srv1/listener

adrci> exit

[oracle@srv1 ~]$ cd /u01/app/oracle/diag/rdbms/oradb/oradb/trace

[oracle@srv1 trace]$ ls -ltr alert*
-rw-r----- 1 oracle oinstall 215280 Jul  3 17:59 alert_oradb.log

[oracle@srv1 trace]$ vi alert_oradb.log
OR,
[oracle@srv1 trace]$ tail -100f alert_oradb.log

I hope you found this article helpful. Feel free to reach out if you have any questions. Explore related topics on our site to deepen your understanding.