Detecting an ORA-nnnn Event via SQL 1

[jtanner]

Hello,

I know an alert log is a physical text file. I was hoping, as in many things in 10g, there are SQL routes to get to the information.

Is is possible to use SQL (v$ or dba views etc.) to obtain the information in a alert log?

If not, is there a way I can use pure SQL to tell me if there has been an ORA-nnnn message in the last n hours or n days?

Thanks,

JT

 

[taupirho]

Hi, throught the medium of external tables you can access data in the alert text file as if it was a table. Do selects from it etc ...

I'm sure others (Santa?) can fill you in on the detailed implementation.

 

[SantaMufasa]

Taupirho articulates the correct strategy to achieve what you want, JT.

What you are asking for, JT, represents excellent utility for any DBA. Since I didn't have such a script in my toolbox already, I built one for you (and the rest of us) this evening.

Here are the prerequisites for this script to work properly:

1) User must have either DBA privileges or at least SELECT privileges on the V$INSTANCE, V$PARAMETER, and DBA_DIRECTORIES data dictionary objects.

2) Alert log resides in the standard "background_dump_dest" path, and has a standard name in the format "alert_<SID>.log".

Here is a listing of the script's features:

1) Prompts user for how many days prior to the present time to list errors. A value of "1" prints out all errors in the last 24 hours; a values of ".01" prints out all errors in the last 14 minutes 24 seconds.

2) If you have not yet created an Oracle DIRECTORY entry for the path to "background_dump_dest", then this script creates one for you.

3) If you have not yet created an external table named "ALERT_LOG_TABLE" that points to the flat file, "alert_<SID>.log" in "background_dump_dest", then this script creates it for you.

4) Displays to the screen the Date, Time, and Content of each alert-log line that contains the prefix, "ORA-" that is more recent than the time factor that you specify.

5) Spools the screen output to a file in the default client-side SQL*Plus path named, 'Alert_Log_Errors.txt'.

Known issue/problem:
The first time you run the following script, expect this error:

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 65

Simply re-run the script, and this one-time error should disappear for good. (If you can isolate what causes this one-time error, please let me know. I haven't troubleshot the problem because it's approaching 2:00 a.m. local time, so my brain is mush.)


Following is the code content of that script, which I named, "ShowLogErrors.sql". (Since the script contains an "ACCEPT...PROMPT" SQL*Plus command, you must store the code to a script and run the script; you cannot just copy and paste the code to a SQL*Plus prompt.):

set serveroutput on
set feedback off
set echo off
begin
    execute immediate 'create table alert_log_table (msg varchar2(10))';
    -- The above is a first-time, dummy table create to allow compile
exception
    when others then null;
end;
/
set verify off
accept days_ago prompt "Enter the number of days ago to begin error display ['.01' = '14.4 mins.'] (<Enter> for all): "
spool Alert_Log_Errors.txt
declare
    log_dir                 varchar2(1000);
    read_dir                varchar2(30);
    rec_count               number;
    alert_log_file_name     varchar2(30);
    hold_instance_name      varchar2(30);
    stm                     varchar2(2000);
    current_date            date;
    current_date_str        varchar2(100);
    procedure prt (x varchar2) is
        begin
            dbms_output.put_line(substr(x,1,250));
        end;
    procedure create_alert_log_table is
        begin
            select value into log_dir
              from v$parameter
             where name = 'background_dump_dest';
            select count(*) into rec_count
              from dba_directories
             where directory_path = log_dir;
            if rec_count = 0 then
                begin
                    stm := 'create directory bdump_dest as '''||log_dir||'''';
                    execute immediate stm;
                exception
                    when others then prt('Abend: '||sqlerrm);
                end;
                read_dir := 'bdump_dest';
            else
                select min(directory_name) into read_dir
                  from dba_directories
                 where directory_path = log_dir;
            end if;
            select instance_name into hold_instance_name
              from v$instance;
            alert_log_file_name := 'alert_'||hold_instance_name||'.log';
            stm := 'drop table alert_log_table';
            execute immediate stm;
            stm :=      'create table alert_log_table';
            stm := stm||'	(msg varchar2(200))';
            stm := stm||'        organization external';
            stm := stm||'        (type oracle_loader';
            stm := stm||'         default directory '||read_dir;
            stm := stm||'         access parameters';
            stm := stm||'             (records delimited by newline';
            stm := stm||'              NOBADFILE NODISCARDFILE NOLOGFILE)';
            stm := stm||'             location ('''||alert_log_file_name||''')';
            stm := stm||'        )';
            stm := stm||'        reject limit unlimited';
            execute immediate stm;
        end;
    procedure Confirm_Alert_table_exists is
        begin
            select count(*) into rec_count
              from user_external_tables
             where table_name = 'ALERT_LOG_TABLE';
            if rec_count = 0 then
                create_alert_log_table;
            end if;
        end;
begin
    dbms_output.enable(1000000);
    Confirm_Alert_table_exists;
    for x in (select * from alert_log_table) loop
        if substr(x.msg,1,4) in ('Mon ','Tue ','Wed ','Thu ','Fri ','Sat ','Sun ') then
            current_date     := to_date(x.msg,'Dy Mon dd hh24:mi:ss yyyy');
            current_date_str := x.msg;
        end if;
        if substr(x.msg,1,4) = 'ORA-' then
            if current_date > sysdate - nvl('&days_ago',9999) then
                prt(current_date_str||' -- '||x.msg);
            end if;
        end if;
    end loop;
    prt ('*** End of Alert-log Error listing ***');
end;
/
spool off
prompt
prompt Wrote spool file, 'Alert_Log_Errors.txt'
prompt

Here is a sample invocation of "ShowLogErrors.sql" and its output for the previous hour on my Oracle instance:

@ShowLogErrors.sql
Enter the number of days ago to begin error display ['.01' = '14.4 mins.'] (<Enter> for all): .04

Sat Jun 09 01:41:10 2007 -- ORA-28546: connection initialization failed
Sat Jun 09 01:41:10 2007 -- ORA-02068: following severe error from RIV
Sat Jun 09 01:41:10 2007 -- ORA-03113: end-of-file on communication channel
*** End of Alert-log Error listing ***

Wrote spool file, 'Alert_Log_Errors.txt'

If you have any questions about the purpose/behaviour of any of the above code, please post a follow-up question.

Let us know if this is useful.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jtanner]

SantaMufasa, Taupirho,

Thanks very much. That is perfect.

JT

 

[taupirho]

JT, quite a lot of work from Santa there for you, I think it more than deserves a star, don't you?