Tuesday, April 14, 2015

Server error triggers for sql chasing

Very recently we had a production scenario where the client application produced ORA 22163 in the application logs, but doesn’t report the sql causing this issue. Application developers were asking can this sql causing ORA 22163 be identified in the database?
We took an approach of schema level server error triggers (again completely transparent to applications). Just created the below error_table along with the servererror trigger, with the help of DBA on Application schema. Then ran the application workload/scenario that is causing this error.  Once the error ORA 22163 occurs in the database, automatically the sqltext along with the error message will be stored into this error_Table.
rajesh@ORA10G> create table errors_table
  2  (dt date default sysdate,
  3   logon_user varchar2(30) default user,
  4   err_no number,
  5   err_msg varchar2(50),
  6   sql_txt varchar2(500) );
Table created.
Elapsed: 00:00:00.06
rajesh@ORA10G> create or replace trigger catch_errors
  2  after servererror on rajesh.schema
  3  declare
  4     pragma autonomous_transaction ;
  5     sql_txt ora_name_list_t;
  6     l_stmt long;
  7  begin
  8     for i in 1..ora_sql_txt(sql_txt)
  9     loop
 10             l_stmt := l_stmt || sql_txt(i);
 11     end loop;
 13     for i in 1..ora_server_error_depth
 14     loop
 15             insert into errors_table
 16             (logon_user, err_no, err_msg,sql_txt)
 17             values(ora_login_user, ora_server_error(i),
 18                     ora_server_error_msg(i), l_stmt );
 19     end loop;
 20     commit;
 21  end;
 22  /
Trigger created.
Elapsed: 00:00:00.09
rajesh@ORA10G> select * from errors_table;
no rows selected
Elapsed: 00:00:00.01
rajesh@ORA10G> select * from emp55;
select * from emp55
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.07
rajesh@ORA10G> column dt noprint
rajesh@ORA10G> select * from errors_table;
LOGON_USER     ERR_NO ERR_MSG                                  SQL_TXT
---------- ---------- ---------------------------------------- ----------------------
RAJESH            942 ORA-00942: table or view does not exist  select * from emp55
1 row selected.
Elapsed: 00:00:00.01

No comments:

Post a Comment