The Default Oracle Alert Log Location
Oracle alert log location may vary from version to version, and sometimes it's not easily found. However, it usually locates at 3 locations.
For 11g and later releases (12c, 18c, 19c)
As we can notice, Oracle software divides alert logs to their own dedicated directories according to their database unique names, which is based on directory structure of diagnostic repository.
For 9i and 10g
It's in bdump.
For rest of cases without any clues
Which is the same way to determine DIAGNOSTIC_DEST.
Filename Format of Alert Log
Even though its location may vary from version to version, the alert log file name is always:
Occasionally, you cannot find the alert log file anywhere. Next, let's see how we can find it.
3 Ways to Find Oracle Alert Log Location
Here I introduce three ways to find the right Oracle alert log location.
1. Background Dump Destination
If you are a DBA, you can query the value of BACKGROUND_DUMP_DEST, an initialization parameter, where you can find Oracle alert log.
SQL> show parameter background_dump_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /u01/app/oracle/diag/rdbms/orc
l/ORCL/trace
Or in this way:
SQL> select value from v$parameter where name = 'background_dump_dest';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/ORCL/trace
In case that you cannot query the database, you can still find the location of alert log at OS-level.
2. Using Find Command
Almost all UNIX and Linux have find command, so using it to find some files is a very common way to do it.
[oracle@test ~]$ find $ORACLE_BASE -type f -name alert_$ORACLE_SID.log
/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/alert_ORCL.log
We find a file named alert_$ORACLE_SID.log starting from $ORACLE_BASE. As we can see, the file was found.
3. Using Locate Command
Sometimes, using locate command will be faster than find to find the path of alert log file.
[oracle@test ~]$ locate alert_$ORACLE_SID.log
/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/alert_ORCL.log
In some databases, the alert logs are big, maybe too big to make room for other files. We have to know ways to reduce the size of alert logs.
For your reference, the ways to find the location of listener log are slightly different from the ways we find Oracle alert log. You can take a look.