Where is Oracle Alert Log Location

  • by
Finding Oracle Alert Log Location

The Default Oracle Alert Log Location

Oracle alert log location 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.

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

Even though Oracle alert log location may vary from version to version, the file name is always alert_$ORACLE_SID.log. That’s why we can make use of 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 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.

[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 Oracle alert log. You can take a look.

Leave a Reply

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