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.

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. This log file is a text file in the directory specified by the system parameter BACKGROUND_DUMP_DEST:

show parameter background_dump_dest NAME                  TYPE       VALUE --------------------- ---------- -------------------------- background_dump_dest  string     d:\oracle\admin\or92\bdump

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

C:\TEMP>d: D:\>cd oracle\admin\or92\bdump D:\ORACLE\ADMIN\OR92\BDUMP>dir  Volume in drive D is DAT  Volume Serial Number is 2C2D-238A  Directory of D:\ORACLE\ADMIN\OR92\BDUMP 11/10/2002  02:24 AM    <DIR>          . 11/10/2002  02:24 AM    <DIR>          .. 11/13/2002  08:53 PM            26,708 alert_or92.log 11/13/2002  08:53 PM               885 or92_arc0_2172.trc 11/13/2002  08:53 PM               949 or92_arc1_1420.trc 11/07/2002  09:05 PM               597 or92_lgwr_2084.trc 11/07/2002  08:58 PM               597 or92_lgwr_2944.trc 11/07/2002  09:10 PM               597 or92_lgwr_3280.trc 11/07/2002  08:49 PM               597 or92_lgwr_3636.trc                7 File(s)         30,930 bytes                2 Dir(s)  39,615,973,888 bytes free D:\ORACLE\ADMIN\OR92\BDUMP>notepad alert_or92.log

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

Viewing the Alert Log File

Now that Janice knows where to find the alert log file, she opens it using the Windows Notepad text editor:

It appears that the new redo logs were created on November 13, 2002, at about 8:49 p.m. You can also see that the new redo log files were created via OEM, since OEM puts a special comment with a timestamp into the alert log file to indicate operations performed via OEM.

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:

D:\> cd \oracle\admin\or92\bdump D:\ORACLE\ADMIN\OR92\BDUMP> dir  Volume in drive D is DAT  Volume Serial Number is 2C2D-238A  Directory of D:\ORACLE\ADMIN\OR92\BDUMP 11/10/2002  02:24 AM    <DIR>          . 11/10/2002  02:24 AM    <DIR>          .. 11/15/2002  03:16 AM       27,239 alert_or92.log 11/15/2002  03:15 AM          947 or92_arc0_2172.trc 11/13/2002  08:53 PM          949 or92_arc1_1420.trc 11/07/2002  09:05 PM          597 or92_lgwr_2084.trc 11/07/2002  08:58 PM          597 or92_lgwr_2944.trc 11/07/2002  09:10 PM          597 or92_lgwr_3280.trc 11/07/2002  08:49 PM          597 or92_lgwr_3636.trc                7 File(s)         31,523 bytes                2 Dir(s)  41,803,294,208 bytes free D:\ORACLE\ADMIN\OR92\BDUMP> rename alert_or92.log      alert_or92_2002-11-15.log D:\ORACLE\ADMIN\OR92\BDUMP> dir  Volume in drive D is DAT  Volume Serial Number is 2C2D-238A  Directory of D:\ORACLE\ADMIN\OR92\BDUMP 11/15/2002  08:35 PM    <DIR>          . 11/15/2002  08:35 PM    <DIR>          .. 11/15/2002  03:16 AM       27,239 alert_or92_2002-11-15.log 11/15/2002  03:15 AM          947 or92_arc0_2172.trc 11/13/2002  08:53 PM          949 or92_arc1_1420.trc 11/07/2002  09:05 PM          597 or92_lgwr_2084.trc 11/07/2002  08:58 PM          597 or92_lgwr_2944.trc 11/07/2002  09:10 PM          597 or92_lgwr_3280.trc 11/07/2002  08:49 PM          597 or92_lgwr_3636.trc                7 File(s)         31,523 bytes                2 Dir(s)  41,803,294,208 bytes free D:\ORACLE\ADMIN\OR92\BDUMP>