Alert Log File

Where is Oracle Alert Log Location

The Default Location of Oracle Alert Log

The location of Oracle alert log may not be easily found, but it’s usually at 3 possible locations:

For 11g and Above

$ORACLE_BASE/diag/rdbms/<dbname_in_lower_case>/$ORACLE_SID/trace/

As we can notice, Oracle software divides alert logs to their own dedicated directories according to their database names, which is based on directory structure of diagnostic repository.

For 9i and 10g

$ORACLE_BASE/admin/$ORACLE_SID/bdump/

It’s in bdump.

For rest of cases without any clues

$ORACLE_HOME/rdbms/log/

Which is the same way to determine DIAGNOSTIC_DEST.

Occasionally, you might find the alert log file nowhere. Next, let’s see how we can find it.

Find Oracle Alert Log Location

Here I introduce three ways to find the right alert log file.

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 the 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 alert log at OS-level.

Even though the location of alert log may vary from version to version, the file name is always alert_$ORACLE_SID.log. That’s why we can use this feature to find the right file like the following ways.

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 started from $ORACLE_BASE to find a file named alert_$ORACLE_SID.log. As we can see, the file was found.

3. Using Locate Command

Sometimes, using locate command will be faster than find.

[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 further reading, the ways to find the location of listener log are slightly different from the ways we find the alert log. You can take a look.

Leave a Reply

Your email address will not be published. Required fields are marked *