Finding the location of the Oracle Alert Log 9i/10g/11g - updated for 12c

In this short post I'll try and see if we can provide a sensible way of establishing the location of the alert log for an oracle database, one that works for 9i, 10g, and 11g.

The Oracle Alert log tells us what major events have happened to the Oracle database.

In times gone by this was a nice text formatted file that most Oracle DBAs knew how to read.  In 11g this was changed to an XML formatted file, with the advent of the ADR  which is a first stab at automating the management of the large quantity of detailed trace file that Oracle manages.

We won't go into the detail of the ADR here except to note that this is where the alert log hangs out in recent versions of Oracle.

To cope with the backlash of complaints of people who didn't want to read an XML formatted alert log, Oracle continue to write the traditional form of the log as well simultaneously.  This is perfect for our purposes.

So this post will try and establish some ways that you can find the alert log.

Some initial problems you may run into

1:  The database is down -  if it is down, then obviously Oracle cannot tell you what the location is as SQLPLUS is down.
2. There are multiple alert logs - in a RAC database for example there are several alert logs, one for each instance
3.  you don't have access to the file location -  being a database user is no guarantee of being able to access the alert log file location (even being DBA may not be sufficient ! - depends on the sandbox the sysadmins have you in)

However it breaks down into two basic cases
Database is Up or Database is Down

Database is Up
If the database is Up we can ask it where it is writing the alert Log

• Assumptions
• User has access to SQLPLUS
• User has access to v$parameter 
• user can issue SHOW PARAMETER

The alert log location we want to see can be found in a variety of ways.

*** UPDATED - FOR 12c this doesn't work any more !!! ***

Read to the end for 12c answer..... 

Show Parameter Background_DUMP_DEST 

SQL> show parameter background_dump_dest

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

background_dump_dest                 string      /u01/app/oracle/diag/rdbms/rep

                                                 o/REPO/trace

SQL>

So that is a location of the oracle alert log, works for all recent versions, 8i,9i,10g,11g

Of course this isn't easy to capture into a variable so why not 

SQL> set linesize 300

SQL> column value format a300

SQL> select value from v$parameter where name='background_dump_dest';

VALUE

------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/repo/REPO/trace

SQL>

So that is a location of the oracle alert log, works for all recent versions, 8i,9i,10g,11g

Which is a little easier. 

Another way of getting pretty much the same thing is 

( This one actually probes the ADR for the trace directory rather than looking at the legacy BDD  - but only works in ADR databases  - 11g and later ) 

SQL> select value from v$diag_info where name='Diag Trace';

VALUE

------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/repo/REPO/trace

SQL>

So that is a location of the oracle alert log, works for 11g

These techniques work when the database is started NOMOUNT,MOUNT or OPEN .. but they do not work, of course, when the database is down, as mentioned above. 

Database is Down 

OK if the db is down how do we find the alert log ?  

There are a number of ways to find the alert log. 

Simple :  search the operating system directories for it. 

The alert log will be named alert_SID.log where SID is the SID of the instance ( thus a rac DB may have many alert logs, and a Data Guard pair will have two 

if we assume our SID=REPO then by the above convention the alert log will be named alert_REPO.log 

We will not go into how to find the SID here, we assume this is well known. 

so we know the name of the file how do  we search for it. 

Linux/Unix 

[oracle@oracloud3 /]$ find / -name alert_REPO.log 2>/dev/null

/u01/app/oracle/diag/rdbms/repo/REPO/trace/alert_REPO.log

[oracle@oracloud3 /]$

We redirect STDERR to null because we don't want to read about all the stuff our Oracle account doesn't have access to. 

Windows 

Rather than do command line ( dir alert_repo.log /s )  just use the GUI to find the file, alternatively if that's not an option download and use something like Agent Ransack 

OK so that's the simplistic method 

What about a little more intelligence than that 

If we were using an older database we used to be obliged to enter the BACKGROUND_DUMP_DEST into the initialization parameter file ( PFILE )... so we could go and find that and see if it's there. 

For this we need ORACLE_HOME envar set. 

On Linux/Unix 

cd $ORACLE_HOME/dbs 

on Windows 

cd %ORACLE_HOME%/database

if a pfile is in use we will see it as initSID.ora, so following the above example it will be called initREPO.ora 

we can then edit this using our favourite text editor to find the above variable and then we have the value. 

OK ... but the current 11g, this doesn't work because 

a)  newer databases default to using an SPFILE ( binary version of the spfile ) 

b)  The background dump dest variable is defaulted in 11g by the ADR so it does not need to be specified. 

So in order to find the alert log we can try and put together a "where Oracle thinks it might put it " from analyzing the SPFILE. 

In order to do this we take into account the following assumptions/axioms. 

ADR Top level Directory is usually  ORACLE_BASE/diag 

RDBMS Logs are usually stored in ADR_base/rdbms

This is then broken down  into DB_NAME/sid to take account of the fact that there may be a shared ADR directory tree across multiple instances of the same database ( i.e. RAC in some situations ) 

so we can complete the location of the alert log in 11g (Only )
as

ORACLE_BASE/diag/rdbms/DB_NAME/SID/trace

or
/u01/app/oracle/diag/rdbms/repo/REPO/trace

This assumes a normally put-together database. 

If you haven't bothered following an OFA architecture then you will probably be defaulted as per the manual
"Derived from the value of the $ORACLE_BASE environment variable. If $ORACLE_BASE is not set, then derived fromORACLE_BASE as set by the Oracle Universal Installer. If ORACLE_BASE is not set, then $ORACLE_HOME/rdbms/log is the default location "

PS to find DB_NAME and ORACLE_BASE we may need to look at the spfile

this is difficult to view as it is a binary file so let's look at 'strings spfileREPO.ora' to get what we want.

Hope that helps .... several different ways to get what you need.



12c (12.1.0.2)  answer :   Background_dump_dest no longer useful as they have changed what that parameter is doing

$ORACLE_BASE/diag/rdbms/DB_NAME/SID/trace is still useful to find the alert log

core_dump_dest can  be used to get close

core_dump_dest = /u01/app/oracle/diag/rdbms/repo/REPO/cdump   and then cd ../trace

It is an ongoing mystery why this is made difficult to find.

Here's a SQL script I wrote to find the alert log on Linux  for 11g and 12c databases

set heading off pages 0 trimspool on lines 120 feedback off echo off
--  Chris Slattery  November 2015
--  find the Alert Log Location in recent versions of Oracle 11g onwards
 with  DD (DDV) as (SELECT VALUE DDV FROM V$parameter WHERE NAME='diagnostic_dest'),
       LCD (LCDV)as (select LOWER(value) LCDV FROM V$parameter WHERE NAME='db_name'),
       INST (INSTV) as (select UPPER(value) INSTV FROM V$parameter WHERE NAME='instance_name')
       select * from (select DDV||'/diag/rdbms/'||LCDV||'/'||INSTV||'/trace' from DD,LCD,INST ) where rownum= 1;
quit;

/

to run it

sqlplus -S "/ as sysdba" @get_alert_loc.sql
/u01/app/oracle/diag/rdbms/repo/REPO/trace

ls -lrt /u01/app/oracle/diag/rdbms/repo/REPO/trace/alert_REPO.log

-rw-r-----. 1 oracle dba 164844 Nov 26 10:09 /u01/app/oracle/diag/rdbms/repo/REPO/trace/alert_REPO.log

so that's OK

Note will be different on Windows etc but this should cover the majority of cases.  Yes I know I am making assumptions with Lower and Upper 

Hope this helps people ..