How To Check Alert Log File in Oracle 11g

Each database has an alert log file, which contains a chronological(sequential order in which they occurred) log of database messages and errors. Oracle will automatically create a new alert log file whenever the old one is deleted.

The alert log includes the following errors and log messages:

• All internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60)
• Administrative operations such as DDL statements and the SQL*Plus commands STARTUP, SHUTDOWN, ARCHIVE LOG, and RECOVER
• Several messages and errors relating to the functions of shared server and dispatcher processes
• Errors during the automatic refresh of a materialized view

How to Find the Location of alert log file in Oracle 11g

SQL> select name from v$database;

NAME
---------
TEST11

alert.log file is written to the directory specified by the background_dump_dest parameter. To view alert log, first you need to find it’s location using show parameter command.

SQL> show parameter background_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /u01/orahow/diag/rdbms/test11/TEST11/trace

You can also get the same result using this sql:

SQL> show parameter background

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      /u01/orahow/diag/rdbms/test11/TEST11/trace

 Copy the above value to find the exact name and location of alert log using ls command:
                                              

[oracle@orahow ~]$ ls -ltr /u01/orahow/diag/rdbms/test11/TEST11/trace/al*
-rw-r----- 1 oracle dba 1901154 Nov 24 04:00 //u01/orahow/diag/rdbms/test11/TEST11/trace/alert_TEST11.log

To view alert log you can use tail command with options and filename.

[oracle@orahow ~]$ tail -20f /u01/orahow/diag/rdbms/test11/TEST11/trace/ alert_TEST11.log

In the following output you will see the last 20 lines of the alert log, but if you want to see more number of lines (ex: 100 line) you can use 100f like that.