Alert Log and how to check alert log errors in oracle

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 are database startup, shutdown, log switches, space errors, etc. This file should constantly be monitored to detect unexpected messages and corruption.

Oracle will automatically create a new alert log file whenever the old one is deleted.

Alert log location

The location can 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 the parameter background_dump_dest.
background_dump_dest is set like

$diagnostic_dest/diag/rdbms/<db_unique_name>/<instance_name>/trace

11g Alert log new features

Beginning with Release 11g of Oracle Database, the alert log is written as both an XML-formatted file and as a text file, as in earlier releases. Both these log files are stored inside the ADR home. The ADR root directory is known as ADR BASE. The Automatic Diagnostic Repository (ADR) is a directory structure that is stored outside of the database. This parameter is set by DIAGNOSTIC_DEST initialization parameter.

SQL> show parameter diagno
NAME TYPE VALUE
--------------------------- ----------- ------------------------------
diagnostic_dest string /u001/oracle/product/XPROD11g/diag

The location of an ADR home is given by the following path, which starts at the ADR base directory:

ADR_BASE/diag/product_type/product_id/instance_id

For example,
So for RDBMS oracle Home of Database name XPROD

ADR_base/diag/rdbms/XPROD/XPROD

Within the ADR home directory are subdirectories where the database instance stores diagnostic data.
alert Log file,The XML-formatted alert log, trace Background and server process trace files and SQL trace files , text alert.log file , cdump Core files

XML formatted alert.log
The alert log is named log.xml and is stored in the alert subdirectory of ADR home.
To get the log.xml path

ADR_BASE/diag/product_type/product_id/instance_id/alert

From Sqlplus

SQL> select value from v$diag_info where name ='Diag Alert';

ADRCI utility to view a text version of the alert log (with XML tags stripped)

Text formatted alert.log
The alert.log is named alertSID.log and is stored in the trace subdirectory of ADR home.
To view the text-only alert.log file

ADR_BASE/diag/product_type/product_id/instance_id/trace

from sqlplus

SQL> select value from v$diag_info where name ='Diag Trace';
or
SQL > Show parameter background_dump_dest

Open file alert_SID.log with a text editor

With 11g ,Oracle provides a way to look the alert log file from the database also. There is a fixed table X$DBGALERTEXT, when you query it, Oracle reads the log.xml from alert directory (which contains all the data what alert.log does), parses it and returns the details back as rows:

SQL> select message_text from X$DBGALERTEXT where rownum desc X$DBGALERTEXT
Name Null? Type
------------------------------- -------- ----------------------------
1 ADDR RAW(4)
2 INDX NUMBER
3 INST_ID NUMBER
4 ORIGINATING_TIMESTAMP TIMESTAMP(3) WITH TIME ZONE
5 NORMALIZED_TIMESTAMP TIMESTAMP(3) WITH TIME ZONE
6 ORGANIZATION_ID VARCHAR2(64)
7 COMPONENT_ID VARCHAR2(64)
8 HOST_ID VARCHAR2(64)
9 HOST_ADDRESS VARCHAR2(16)
10 MESSAGE_TYPE NUMBER
11 MESSAGE_LEVEL NUMBER
12 MESSAGE_ID VARCHAR2(64)
13 MESSAGE_GROUP VARCHAR2(64)
14 CLIENT_ID VARCHAR2(64)
15 MODULE_ID VARCHAR2(64)
16 PROCESS_ID VARCHAR2(32)
17 THREAD_ID VARCHAR2(64)
18 USER_ID VARCHAR2(64)
19 INSTANCE_ID VARCHAR2(64)
20 DETAILED_LOCATION VARCHAR2(160)
21 PROBLEM_KEY VARCHAR2(64)
22 UPSTREAM_COMP_ID VARCHAR2(100)
23 DOWNSTREAM_COMP_ID VARCHAR2(100)
24 EXECUTION_CONTEXT_ID VARCHAR2(100)
25 EXECUTION_CONTEXT_SEQUENCE NUMBER
26 ERROR_INSTANCE_ID NUMBER
27 ERROR_INSTANCE_SEQUENCE NUMBER
28 VERSION NUMBER
29 MESSAGE_TEXT VARCHAR2(2048)
30 MESSAGE_ARGUMENTS VARCHAR2(128)
31 SUPPLEMENTAL_ATTRIBUTES VARCHAR2(128)
32 SUPPLEMENTAL_DETAILS VARCHAR2(128)
33 PARTITION NUMBER
34 RECORD_ID NUMBER

There’s also a fixed table X$DBGDIREXT, which returns all file and directory names under [diagnostic_dest]/diag directory:

SQL> select lpad(' ',lvl,' ')||logical_file file_name
from X$DBGDIREXT
where rownum < 2;

12c or above Alert log new features

With 12c and above, background_dump_dest is depreciated. We can find the alert log location using below also

adrci
adrci> show alert
adrci> show alert -tail 100

how to check alert log errors in oracle using Unix Command

Go to the background dump directory to run these unix 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 the 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}'

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

Here are the steps required on How to easily find the Oracle database startup and shutdown time using sqlplus
step 1)  Create a database directory object

 create or replace directory data_dir as 'Specify the Backgound dump Dest location'
 /
 Directory created.
 CREATE TABLE alert_log
 ( text_line varchar2(255))
 ORGANIZATION EXTERNAL
 (
 TYPE ORACLE_LOADER
 DEFAULT DIRECTORY data_dir
 ACCESS PARAMETERS
 (
 records delimited by newline fields
 REJECT ROWS WITH ALL NULL FIELDS
 )
 LOCATION
 (
 'alert_.log'
 )
 )
 REJECT LIMIT unlimited
 /
 Table created.

step 2)  Use the below query to find out the timing

select to_char(last_time,'dd-mon-yyyy hh24:mi') shutdown,
 to_char(start_time,'dd-mon-yyyy hh24:mi') startup,
 round((start_time-last_time)2460,2) mins_down,
 round((last_time-lag(start_time) over (order by r)),2) days_up,
 case when (lead(r) over (order by r) is null )
 then round((sysdate-start_time),2)
 end days_still_up
 from (
 select r,
 to_date(last_time, 'Dy Mon DD HH24:MI:SS YYYY') last_time,
 to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY') start_time
 from (
 select r,
 text_line,
 lag(text_line,1) over (order by r) start_time,
 lag(text_line,2) over (order by r) last_time
 from (
 select rownum r, text_line
 from alert_log
 where text_line like '_  _ ::_ 20_'
 or text_line like 'Starting ORACLE instance %'
 )
 )
 where text_line like 'Starting ORACLE instance %'
 )
 /

Related Articles
ORA-00942 table or view does not exist
ora-29913: error in executing odciexttableopen callout
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
how to find archive log sequence number in oracle
https://docs.oracle.com/cd/B28359_01/server.111/b28310/diag005.htm#ADMIN11267