Is an "after servererror on database trigger" is a good idea?

Question

For several failures issues, I wrote this trigger - audit_failed_trg

as an after servererror on database trigger.

My first thinking was to check only specific exception\user\table according to the need.

But I just wondering - the database should fire this trigger for any failure.

Is that a good idea to enable it in production environment?

Is this can cause any performance problems or others?

I'm using Oracle 11g.

create or replace trigger audit_failed_trg
after servererror on database
declare
l_sql_text ora_name_list_t;
l_n        number;
  begin

insert into T values ( S.NEXTVAL, 1, 'ora_sysevent = ' || ORA_SYSEVENT ,sysdate);
insert into T values ( S.CURRVAL, 2, 'ora_login_user = ' || ORA_LOGIN_USER,sysdate );
insert into T values ( S.CURRVAL, 3, 'ora_server_error = ' || ORA_SERVER_ERROR(1),sysdate );
insert into T values ( S.CURRVAL, 4, 'SID = ' || SYS_CONTEXT ('USERENV','SID'),sysdate);
insert into T values ( S.CURRVAL, 5, 'host = ' || SYS_CONTEXT ('USERENV','HOST') ,sysdate);
insert into T values ( S.CURRVAL, 6, 'ip = ' || SYS_CONTEXT ('USERENV','IP_ADDRESS') ,sysdate);
insert into T values ( S.CURRVAL, 7, 'module = ' || SYS_CONTEXT ('USERENV','MODULE') ,sysdate);
insert into T values ( S.CURRVAL, 8, 'serverhost = ' || SYS_CONTEXT ('USERENV','SERVER_HOST') ,sysdate);


 l_n := ora_sql_txt( l_sql_text );
for i in 1 .. l_n
 LOOP
insert into t values ( s.CURRVAL,8+i, 'l_sql_text(' || i || ') = ' || l_sql_text(i),sysdate );
 end loop;

 end;

Answer 1

Logging all server errors is a good idea and I've seen it work well in production environments.

In theory, error logging is handled by applications. In practice, most applications do not catch all database errors. It's useful to have a single table that contains all errors generated by a database.

However, this kind of trigger has particular challenges that need to be carefully considered:

1. Sensitive information - If a query have a hard-coded social security number, and the query fails, that number will be in the error log. Make sure your organization is OK with that risk. Don't grant everyone access to the table. (And don't think that application bind variables will avoid this problem. The queries most likely to fail are ad hoc queries run outside of the application, and they will use hard coded literals.)
2. Don't blame people - You'll probably be surprised by the number of errors. Resist the urge blame people for generating too many error messages. You'll anger a lot of developers if you always pester them about irrelevant error messages. (This seems obvious, but I've met so many DBAs who just love to complain about anybody generating any error. This is one of the reasons why Oracle is the most hated database.)
3. Test extra carefully - Poorly built system event triggers can really trash a database. The most obvious problems are with LOGON triggers, that can effectively break the entire database. In some ways, AFTER SERVERERROR is a safer event, because something has already broken before it was called. But weird things can still happen. For example, you'll notice the exception when others then null; code. That code is usually an anti-pattern, but this is one of the few places where you truly want to suppress all exceptions to avoid an infinite loop.
4. Performance - The extra overhead on errors shouldn't matter. If your system has to optimize for errors, then you've got worse problems. But you may need to worry about the size of the table. If some process goes rogue and issues a million invalid queries in a day, you don't want it to consume a huge amount of space. (This is why Oracle will not log every execution, or every error, by default. There are too many opportunities for denial of service attacks.)

Sample Schema

Spend some time on a better table design. Ignore the key-value pair type of table used in the question. Instead, create a table that will store one row per error, and use meaningful names. A table like this will be much, much easier to meaningfully query:

--drop trigger audit_failed_trg;
--drop table server_errors;
--drop sequence server_error_seq;

create sequence server_error_seq;

create table server_errors
(
    id               number not null,
    error_date       date not null,
    ora_sysevent     varchar2(128),
    ora_login_user   varchar2(128),
    ora_server_error varchar2(4000),
    sid              number,
    host             varchar2(256),
    ip               varchar2(15),
    module           varchar2(4000),
    serverhost       varchar2(256),
    sql              clob,
    constraint server_errors_pk primary key(id)
);

Trigger

create or replace trigger audit_failed_trg
after servererror on database
declare
    v_sql_text ora_name_list_t;
    v_sql      clob;
    v_n        number;
begin
    v_n := ora_sql_txt(v_sql_text);
    for i in 1 .. v_n loop
        v_sql := v_sql || v_sql_text(i);
    end loop;

    --If you find a huge number of irrelevant errors, you might want to filter them out here.

    insert into server_errors
    values
    (
        server_error_seq.nextval,
        sysdate,
        ora_sysevent,
        ora_login_user,
        ora_server_error(1),
        sys_context ('USERENV','SID'),
        sys_context ('USERENV','HOST'),
        sys_context ('USERENV','IP_ADDRESS'),
        sys_context ('USERENV','MODULE'),
        sys_context ('USERENV','SERVER_HOST'),
        v_sql
    );
    commit;

--Never raise an exception from this trigger.
--No matter what happens we don't want recursive errors.
exception when others then
    null;
end;
/