ITPub博客

首页 > 数据库 > Oracle > 用触发器捕捉数据库所有报错sql

用触发器捕捉数据库所有报错sql

原创 Oracle 作者:bestpaydata 时间:2016-02-23 16:40:09 0 删除 编辑
author:zy

公司数据库因存储挂了,短时间无法恢复,需要通过读库、归档恢复到一个新的数据库。从而恢复业务
由于读库不全,从读库导过来的对象(表、sequence、procedure..)不全。致应用日志报“对象不存在”、数据库“library cache lock”、等等的大量错误及等待事件。
而开发也没法较快定位程序,不知道哪条sql报错(高并发环境下),所以暂不知道哪些对象缺失。

当时简短分析后,决定采用数据库触发器来捕获问题sql,触发类型为SERVERERROR。
最终的代码原型为:

--首先创建日志记录表,记录报错sql、主机等等一些信息
create table UTIL_ERROR_LOG
(
  username VARCHAR2(50),
  host     VARCHAR2(50),
  os_user  VARCHAR2(50),
  ip       VARCHAR2(50),
  type     VARCHAR2(50),
  time     DATE default sysdate,
  sql      VARCHAR2(4000)
)

trigger --监控oracle错误号,这里的“对象不存在”对应的是ora-0942.
    程序比较简单,但功能比较实用
create or replace trigger t_error_sql
  after SERVERERROR  on database

when (user not in('SYS') and ora_sysevent not in ('COMMENT'))
declare
  n        int; --n个的意思
  sql_text ora_name_list_t;
  --v_sql    clob; ORA_SQL_TXT读取到的sql含“/*中文*/”会乱码,clob装载这些乱码会报错
  v_sql   varchar(32767); --有可能读取到乱码
  v_error varchar2(30);
begin

  IF (IS_SERVERERROR(0942)) THEN
    v_error := '0942';
  end if;

  if v_error is not null then

    n := ORA_SQL_TXT(sql_text);
    --sql_text(i)过了51就会报错,受不了这么大的值
    if n < 51 then
      for i in 1 .. n loop
        --dbms_output.put_line(sql_text(i));
        v_sql := v_sql || sql_text(i);
      end loop;
    end if;
    insert /*+append*/
    into util_error_log
    values
      (user,
       sys_context('userenv', 'host'),
       sys_context('userenv', 'os_user'),
       sys_context('userenv', 'ip_address'),
       v_error,
       sysdate,
       v_sql); --也有补获不到sql的情况

  end if;
  /*exception --调试信息
  when others then
    dbms_output.put_line(n);
    raise_application_error(-20000,
                            sqlerrm || ' ' ||
                            dbms_utility.format_error_backtrace() || ' N:' || n);*/
end t_error_sql;


结:
Oracle trigger十分灵活,特别在生产上,不应随便创建。在经过严格的测试后,布置上去来实现一些事件前后的一些处理。
常用触发器实现的功能:
  1. ddl语句监控
  2. 特殊核心表不能删保护
  3. 报错语句的监控
  4. 登陆

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30109892/viewspace-1993180/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-01-19

  • 博文量
    126
  • 访问量
    985963