Database Administrator: The Alert Log

The alert log is a very important file within the Oracle Database. Though Oracle recommends the use of Enterprise Manager for monitoring, most DBAs (including the author) still prefer using the Alert Log. Alert log is a text/XML file that keeps record of messages and errors from the database in chronological order. The file also contains information abut trace and dump files created on the server.

Alert log entries are created when;

Parameter changes are made on the database.
Adding/removing datafiles from tablespaces.
Instance startup/shutdown.
Messages to the operator console.
Errors causing trace files.
Errors when a materialized view is refreshed.
Archive log switches
ORA-00600 (internal) errors.
ORA-01578 errors (block corruption)
ORA-00060 errors (deadlocks)

How to find the Alert Log
The location of the alert log is specified by the background_dump_dest initialization parameter. You can get this from sqlplus using the 'show parameter' command.

SQL> set lines 400

SQL>

SQL> show parameter dump_dest

NAME TYPE VALUE

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

background_dump_dest string /oracle/app/oracle/diag/rdbms/kPROD/trace

core_dump_dest string /oracle/app/oracle/diag/rdbms/kPROD/trace

user_dump_dest string /oracle/app/oracle/diag/rdbms/kPROD/trace

SQL>

SQL> !

testsvr:/home/oracle>

testsvr:/oracle/app/oracle/diag/rdbms/kPROD/trace>ls -lrt alert*.log

-rw-r----- 1 oracle dba 178214 Oct 27 01:07 alert_kPROD.log

testsvr:/home/oracle>

testsvr:/oracle/app/oracle/diag/rdbms/kPROD/trace>tail -5l alert_kPROD.log

Archived Log entry 2 added for thread 1 sequence 268 ID 0x1c3da76c dest 1:

Tue Oct 27 01:07:50 2015

Starting background process SMCO

Tue Oct 27 01:07:51 2015

SMCO started with pid=32, OS id=2248

testsvr:/home/oracle>

Viewing the Alert Log
On windows you can easily view the alert log file using any text editor. You can also install Tail for Windows, a desktop application that updates the file as it is being updated by the database engine.

On UNIX systems use either of the commands below;

tail alert.log

view alert.log

Check the links below for more ways to use the tail and view UNIX commands http://www.tutorialspoint.com/unix_commands/tail.htm
http://www.tutorialspoint.com/unix/unix-vi-editor.htm

Truncating Alert log
This can be done in two ways;

1. The alert log can be renamed like shown below. After this the system will automatically create a new alert log and start writing to it.

testsvr:/oracle/app/oracle/diag/rdbms/kPROD/trace>ls -lrt alert_kPROD.log

-rw-r----- 1 oracle dba 164763517 Oct 26 06:59 alert_kPROD.log