Error Triggers

Once every few weeks this customer encountered an ORA-1031 error – insufficient privileges. We saw the error in the alert log, and the customer complained about getting the error, but for some reason we never had enough information on what the customer was actually doing when the error occurred. We needed to know what he was doing to figure out how we can fix the issue.

We tried tracing, but the issue occurred so rarely that we were never able to actually capture it on time.

I thought we can solve the issue with error triggers. My suggestion was rejected by my team lead who prefers not to use triggers on production, but I still think it could be useful.

create table caught_errors (
dt        date,
username  varchar2( 30), -- value from ora_login_user
msg       varchar2(512),
stmt      varchar2(512)
);
CREATE TRIGGER log_errors AFTER SERVERERROR ON DATABASE
declare
sql_text ora_name_list_t;
msg_     varchar2(2000) := null;
stmt_    varchar2(2000) := null;
BEGIN
IF (IS_SERVERERROR (1031)) THEN
    for depth in 1 .. ora_server_error_depth loop
         msg_ := msg_ || ora_server_error_msg(depth);
    end loop;
    for i in 1 .. ora_sql_txt(sql_text) loop
         stmt_ := stmt_ || sql_text(i);
    end loop;
    insert into
    caught_errors (dt     , username      ,msg ,stmt )
    values (sysdate, ora_login_user,msg_,stmt_);
END IF;
END;
Advertisements

5 Comments on “Error Triggers”

  1. poststop says:

    you can set an event in the init file to dump a trace file to udump whenever the error occurs.

  2. for the performance side this database trigger may cause problems since for each error it will be firing and also check if the insert is hard-parsed each time – http://tonguc.wordpress.com/2007/01/28/somethings-never-change-be-carefull/

    If so as advised by Kyte you may want to move sql into plsql and call it from trigger.

  3. Gary says:

    The size of the table columns and the variables used for inserts don’t match, so it may error on the logging which could mean the logging fails. You’ll want a grant insert on caught_errors to public and either a public synonym for it or specify the schema in the trigger. You don’t want your error logging trigger erroring 🙂
    I use something similar in dev/test environments. Its nice and easy to switch on an off as required.
    Most applications shouldn’t have enough errors raised from the database to the client application for this to be a major performance issue.
    Did your Team Lead come up with a better suggestion ?

  4. prodlife says:

    Hi Tonguç,

    I assume that we have very few errors, so the performance issue shouldn’t come up.
    In any case, I liked the suggestion to move the sql from the trigger to pl/sql, its a small thing that can be useful.

  5. prodlife says:

    Hi Gary,

    Thanks for pointing out the mismatch in the variable sizes. I copied the exact code I had on my test system, and never noticed this bug…

    We tried catching the issue with traces and using fine grained auditing, but it didn’t work too well. The errors are very rare (last seen two weeks ago) and the system is very active so the amount of logs we have to go through to catch the issue is unrealistic.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s