Alert log file in Oracle And command to check for ORA error and startup time

What is alert log file in Oracle

The alert log file (also referred to as the ALERT.LOG) 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.

Alert log location

The location can be find out using the parameter background_dump_dest

sqlplus / as sysdba

show parameter background_dump_dest

Beginning with Release 11g, the alert log file is written as XML formatted and as a text file (like in previous releases). The default location of both these files is the new ADR home (Automatic Diagnostic Repository, yet another new dump dest in 11g).

The ADR is set by using the DIAGNOSTIC_DEST initialization parameter.

But you can still find the alert log location using parameter background_dump_dest

background_dump_dest is set like $diagnostic_dest/diag/rdbms/<db_unique_name>/<instance_name>/trace

How to find the ORA errors with date  using Unix command in alert log

Go to the bdump directory to run these shell commands
Date and errors in alert.log

cat alert*log |
awk 'BEGIN{buf=""}
/[0-9]:[0-9][0-9]:[0-9]/{buf=$0}
/ORA-/{print buf,$0}'

How to find Date of startups in the alert.log

cat alert*log |
awk 'BEGIN{buf=""}
/[0-9]:[0-9][0-9]:[0-9]/{buf=$0}
/Starting ORACLE/{print buf,$0}'

Related Articles

11g Alert log new features

How to easily find the Oracle database startup and shutdown time using sqlplus

How to Solve ORA-00942 table or view does not exist

How to resolve ORA-29913 with external tables

ORA-00257: archiver error. Connect internal only, until freed.

ORA-03113: end-of-file on communication channel

ORA-27154: post/wait create failed during startup

Leave a Reply