14.1. The Alert Log File

The alert log file is a grab bag of messages about the state of the database instance. It contains entries about significant database events, such as database startup and shutdown, nondefault initialization parameters, ALTER SYSTEM commands, and various errors.

alert log file

A text file that contains entries about significant database events, such as database startup and shutdown, nondefault initialization parameters, and various errors. The alert log file is stored in the directory specified by the system parameter BACKGROUND_DUMP_DEST.

14.1.1. Locating the Alert Log File

At Scott's widget company, Janice, the DBA, doesn't remember when she made the changes to the redo log files. She wanted to increase the redundancy of the redo log files, so she added a second set of redo logs on a different disk. She can find information about the redo logs in the alert log file.

Janice's first step is to locate the alert log file itself; since she recently converted one of the Oracle databases from a Windows server to a Linux server, she hasn't yet memorized the locations of the Oracle-related directories, so she needs to check one of the initialization parameters. This log file is a text file in the directory specified by the initialization parameter BACKGROUND_DUMP_DEST:

show parameter background_dump_dest

NAME             TYPE     VALUE
---------------- -------- -------------------------------
background_dump  string    /u01/app/oracle/admin/ord/bdump
_dest

From a Linux operating system command-line session, Janice locates the alert log file:

[oracle@oltp oracle]$ cd /u01/app/oracle/admin/ord/bdump
[oracle@oltp bdump]$ ls -l alert_*.log
-rw-r--r--    1 oracle   oinstall     4006 Jun 22 22:23
alert_ord.log
[oracle@oltp bdump]$ vi alert_ord.log

The alert log file's name on Linux is alert_, followed by the instance's connection identifier and an extension of .log.

14.1.2. Viewing the Alert Log File

Now that Janice knows where to find the alert log file, she opens it using the Linux vi text editor.

It appears that the new redo logs were created on June 22, 2004, at about 10:23 PM You can also see that the control file was automatically backed up by EM Database Control when the new redo log files were created.

14.1.3. Maintaining the Alert Log File

The alert log file grows in size slowly but without limit. After a few weeks, it can become cumbersome to review the file, so it's a good idea to archive or delete the file on a periodic basis.

The alert log file can be safely renamed or deleted, even when the database is up and running. The next time an entry needs to be written to the alert log file and the alert log file is not there, a new one is created.

Janice, the DBA, reviews the alert log file every Friday and renames it with a name containing the date it was renamed:

[oracle@oltp oracle]$ cd /u01/app/oracle/admin/ord/bdump
[oracle@oltp bdump]$ ls -l alert_*.log
-rw-r--r-- 1 oracle oinstall 4006 Jun 22 22:23 alert_ord.log

[oracle@oltp bdump]$ mv alert_ord.log
alert_ord_2004-06-21.log
[oracle@oltp bdump]$ ls -l alert_*.log
-rw-r--r-- 1 oracle oinstall 4006 Jun 22 22:23
alert_ord_2004-06-21.log

[oracle@oltp bdump]$ sqlplus / as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Tue Jun 22
23:13:49 2004

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 -
Production
With the Partitioning, OLAP and Data Mining options

SQL> alter system switch logfile;
System altered.

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition
Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

[oracle@oltp bdump]$ ls -l alert_*.log
-rw-r--r-- 1 oracle oinstall 4006 Jun 22 22:23
alert_ord_2004-06-21.log
-rw-r--r-- 1 oracle oinstall 1163 Jun 22 23:14
alert_ord.log
[oracle@oltp bdump]$

Notice in the example that as soon as a system event occurred, in this case a forced log switch, the new alert log file was created automatically.