Pages

Wednesday, June 1, 2016

About Alert Log File In Oracle

About Alert Log File In Oracle

The alert log file is a chronological log of messages and errors written out by an Oracle Database. Typical messages found in this file is: database startup, shutdown, log switches, space errors, etc. This file should constantly be monitored to detect unexpected messages and corruptions.Oracle will automatically create a new alert log file whenever the old one is deleted.

When an internal error is detected by a process, it dumps information about the error to its trace file. Some of the information written to a trace file is intended for the database administrator, while other information is for Oracle Worldwide Support. Trace file information is also used to tune applications and instances.

The alert log of a database includes the following information :

1) All internal errors (ORA-00600), block corruption errors (ORA-01578), and deadlock errors (ORA-00060) that occur.

2) Administrative operations, such as CREATE, ALTER, and DROP statements and STARTUP,
SHUTDOWN, and ARCHIVELOG statements.

3) Messages and errors relating to the functions of shared server and dispatcher processes.

4) Errors occurring during the automatic refresh of a materialized view.

5) The values of all initialization parameters that had non-default values at the time the database and instance startup .

which process writes to alert log file:

Not "one" but all the background processes can/do write to it. The archiver writes to it. LogWriter can write (if we have log_checkpoints_to_alert). When a background process detects that another has died, the former writes to the alert log before panicking the instance and killing it.  Similarly an ALTER SYSTEM command issued by the server process for our database session will also write to the alert.log .

To find the location of alert log file we can find by below command

SQL> select value from v$parameter where name = 'background_dump_dest' ;   OR
SQL> show parameter background
SQL> select * from v$diag_info;

If the background_dump_dest parameter is not specified, Oracle will write the alert.log into the $ORACLE_HOME/RDBMS/trace directory.