What is Alert Log File ?

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

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


In Oracle 12c Version : 

ora122> desc v$diag_info  Name                                                                Null?    Type  ------------------------------------------------------------------- -------- ---------------  INST_ID                                                                      NUMBER  NAME                                                                         VARCHAR2(64)  VALUE                                                                        VARCHAR2(512)  CON_ID                                                                       NUMBER

Quick sql*plus script to get it out:

-- diag_info -- quick check of the new v$diag_info view that came in with 11 col inst_id form 9999 head inst col name form a25 col value form a60 wrap spool diag_info.lst set lines 120 select * from v$diag_info order by name / spool off

Contents:

INST_ID NAME                 VALUE                                                            CON_ID -------- -------------------- ---------------------------------------------------------------- -------        1 Diag Enabled          TRUE                                                                  0        1 ADR Base              D:\APP\ORACLE                                                         0        1 ADR Home              D:\APP\ORACLE\diag\rdbms\ora122\ora122                                0        1 Diag Trace            D:\APP\ORACLE\diag\rdbms\ora122\ora122\trace                          0        1 Diag Alert            D:\APP\ORACLE\diag\rdbms\ora122\ora122\alert                          0        1 Diag Incident         D:\APP\ORACLE\diag\rdbms\ora122\ora122\incident                       0        1 Diag Cdump            D:\app\oracle\diag\rdbms\ora122\ora122\cdump                          0        1 Health Monitor        D:\APP\ORACLE\diag\rdbms\ora122\ora122\hm                             0        1 Default Trace File    D:\APP\ORACLE\diag\rdbms\ora122\ora122\trace\ora122_ora_7416.trc      0        1 Active Problem Count  0                                                                     0        1 Active Incident Count 0                                                                     0

I should add some notes later about setting the trace file identifier…
Ohhh, OK, I’ll do it now. To make it easier to identify your trace file, set tracefile_identifier

alter session set tracefile_identifier = 'mdw151019'   --Now if I create a quick trace file alter session set sql_trace=true   @test_code   alter session set sql_trace=false

I now go to the Diag trace directory I identified via V$DIAG_INFO and look for my trace files. I could just look for the latest ones or do a wilcard search on my tracefile_identifier string and, pop, there we are:

19/10/2015 13:59 39,751 ora122_ora_7416_mdw151019.trc
19/10/2015 13:59 426 ora122_ora_7416_mdw151019.trm

If you want a taste of the numerous ways of initiating a 10046 trace, sometimes called a SQL trace, see Tim Hall’s excellent post on his Oracle Base website:

https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof