Oracle Database -- Quering the Alert Log FIle as External Table

Problem

You want to query the Alert Log file using SQL and easily find entries by keywords and begin restricted by dates. 

Solutions found on the Internet don't consider alert log entries as a whole and just extract lines matched to the specified keywords without the ability to specify the date range

Solution

Use the below guidance to setup an external table to have access to the alert log file from SQL. Below you will also find a sample query to retrieve alert log entries

Find the location of the alert log file

SQL> select value from v$system_parameter
     where name = 'background_dump_dest'; 

VALUE
------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace

Create a DIRECTORY object for the path you found in the previous step

drop directory bdump_dir;

create directory bdump_dir as '/u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace';


Find the SID of your instance. Alert log file name format is alert_<SID>.log

SQL> select instance_name  from v$instance;

INSTANCE_NAME
----------------
orcl11g


Create the external table to access the alert log file

drop table alert_log_t purge;

create table alert_log_t (text varchar2(4000))
organization external
(type oracle_loader
 default directory bdump_dir
 access parameters (
  records delimited by newline nologfile nobadfile nodiscardfile
  fields terminated by '!@#$%' lrtrim missing field values are null
 )
 location ('alert_orcl11g.log')
);


Check that the alert log file is accessible

SQL> select * from alert_log_t where rownum < 15;

TEXT
--------------------------------------------------------------------------------
Fri Apr 04 21:28:48 2014
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

14 rows selected.


Now when your alert log file is accessible and readable from SQL, create an auxiliary view to be able to perform easy-by-entry search. The view contains a DATE column to restrict entries by date

create or replace view alert_log
as
select
  row_number
, last_value(group_id ignore nulls) over ( order by row_number) entry_id
, to_date(last_value(this_date ignore nulls) over ( order by row_number),'Dy Mon dd hh24:mi:ss yyyy') entry_date
, msg entry_text
from (
select
  rownum row_number
, case when regexp_like(text,'^\w\w\w \w\w\w \d\d [0-9][0-9]:[0-9][0-9]:[0-9][0-9] .*\d\d\d\d$')
       then rownum else null end group_id
, case when regexp_like(text,'^\w\w\w \w\w\w \d\d [0-9][0-9]:[0-9][0-9]:[0-9][0-9] .*\d\d\d\d$')
       then regexp_replace(text,'^(\w\w\w \w\w\w \d\d [0-9][0-9]:[0-9][0-9]:[0-9][0-9]) .*(\d\d\d\d)$','\1 \2')
       else null end this_date
, text msg
from alert_log_t where text is not null and rownum>0  )
order by row_number;


Check that the view is accessible and works as expected

SQL> column ENTRY_TEXT format a40
SQL> select * from alert_log where rownum<15;

ROW_NUMBER   ENTRY_ID ENTRY_DAT ENTRY_TEXT
---------- ---------- --------- ----------------------------------------
         1           1 04-APR-14 Fri Apr 04 21:28:48 2014
         2           1 04-APR-14 Starting ORACLE instance (normal)
         3           1 04-APR-14 LICENSE_MAX_SESSION = 0
         4           1 04-APR-14 LICENSE_SESSIONS_WARNING = 0
         5           1 04-APR-14 Shared memory segment for instance monit
                                 oring created
         6           1 04-APR-14 Picked latch-free SCN scheme 3
         7           1 04-APR-14 Using LOG_ARCHIVE_DEST_1 parameter defau
                                 lt value as USE_DB_RECOVERY_FILE_DEST
         8           1 04-APR-14 Autotune of undo retention is turned on.
         9           1 04-APR-14 IMODE=BR
        10           1 04-APR-14 ILAT =27
        11           1 04-APR-14 LICENSE_MAX_USERS = 0
        12           1 04-APR-14 SYS auditing is disabled
        13           1 04-APR-14 Starting up:
        14           1 04-APR-14 Oracle Database 11g Enterprise Edition R
                                 elease 11.2.0.3.0 - 64bit Production


14 rows selected.


Now use the below query to easily find alert log entries by keywords

SQL> with t as
     (
      select distinct entry_id from alert_log
      where entry_text like '%ORA-%'
      and entry_date >= sysdate-2
      and rownum > 0 order by entry_id
     )
     select /*+ no_merge(t) */
     entry_id
     , to_char(entry_date, 'Dy Mon dd hh24:mi:ss yyyy') entry_date
     , entry_text
     from alert_log
     where entry_id in (select entry_id from t)
     ;


  ENTRY_ID ENTRY_DATE                ENTRY_TEXT
---------- ------------------------ ----------------------------------------
    558452 Wed Oct 29 11:24:04 2014 Wed Oct 29 11:24:04 2014
    558452 Wed Oct 29 11:24:04 2014 flashback database to before resetlogs
    558452 Wed Oct 29 11:24:04 2014 ORA-38726 signalled during: flashback da
                                    tabase to before resetlogs...

Note how the whole alert log entry is retrieved.