Oracle 11g系统级别触发器来跟踪监控drop误操作

前言：
db中有一张表的数据老是紊乱，猜猜是经历过drop、create的数据同步操作，但是现在谁也不知道在哪里操作的，所以准备做一个触发器去记录下是哪个应用服务器那个db账号操作的。

3，系统级别触发器

3.1 触发事件

包括各种DDL操作以及各种数据库事件，ddl包括create、alter、drop、rename、grant、revoke、audit、noaudit、commit、truncate、analyze、associate statistics、disassociate statistis。触发时间可以before或者after

3.2 建立触发器记录的表

-- Create tablecreate table Z_TRIG_SYS（ltDATE,sid NUMBER,serial# NUMBER,usernameVARCHAR2（30）,oSUSErVARCHAR2（64）,machine VARCHAR2（32）,terminalVARCHAR2（16）,object_name VARCHAR2（200）,ora_syseventVARCHAR2（200）,program VARCHAR2（64）,sqltext VARCHAR2（4000）,statusVARCHAR2（30）,client_ip VARCHAR2（60）,ora_dbnameVARCHAR2（60）,ora_client_ip_address VARCHAR2（60））;-- Add comments to the columns comment on column Z_TRIG_SYS.ltis "录入时间";comment on column Z_TRIG_SYS.sidis "当前session的id";comment on column Z_TRIG_SYS.serial#is "sid的序列号，顺序自增";comment on column Z_TRIG_SYS.usernameis "登录的用户名";comment on column Z_TRIG_SYS.osuseris "操作者的os系统";comment on column Z_TRIG_SYS.machineis "操作者的机器名称";comment on column Z_TRIG_SYS.object_nameis "操作对象名称";comment on column Z_TRIG_SYS.ora_syseventis "操作事件";comment on column Z_TRIG_SYS.sqltextis "执行的sql片段";comment on column Z_TRIG_SYS.client_ipis "客户端ip";comment on column Z_TRIG_SYS.ora_dbnameis "执行的数据库";comment on column Z_TRIG_SYS.ora_client_ip_addressis "客户端ip地址";

3.3 建立system级别触发器

create or replace trigger trig_systemafter drop on databasebeginif ora_login_user！="system" then insert into z_trig_sys（ lt,sid ,serial# ,username,osuser,machine ,terminal,object_name ,ora_sysevent,program ,sqltext ,status,client_ip ,ora_dbname,ora_client_ip_address ）select sysdate, s.SID,s.SERIAL#,s.USERNAME,s.OSUSER, s.MACHINE, s.TERMINAL, s.PROGRAM, ora_dict_obj_name, ora_sysevent, "drop object on database", "",sys_context（"userenv","ip_address"）,ora_database_name,ora_client_ip_addressfrom v$sql q, v$session s where s.audsid=（select userenv（"SESSIONID"） from dual） and s.prev_sql_addr=q.address AND s.PREV_HASH_VALUE = q.hash_value; -- commit; -- AND sys_context（"userenv","ip_address"） ！="192.168.180.106"; end if;end trig_system;

3.4，调试报错

ORA-00604: error occurred at recursive SQL level 1
ORA-04092: cannot COMMIT in a trigger去掉触发器中的commit;

4，查看监控结果，如下所示：

SQL> create table zz_back（id number）;Table createdSQL> insert into zz_back values（1）;1 row insertedSQL> commit;Commit completeSQL> drop table zz_back;Table droppedSQL> select * from z_trig_sys;LT SIDSERIAL# USERNAME OSUSER MACHINETERMINAL OBJECT_NAMEORA_SYSEVENT PROGRAMSQLTEXTSTATUS CLIENT_IPORA_DBNAME ORA_CLIENT_IP_ADDRESS----------- ---------- ---------- ------------------------------ ---------------------------------------------------------------- -------------------------------- ---------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------2015/11/4 178763023 POWERDESKAdministratorWORKGROUPWIN-TIMMAN WIN-TIMMAN plsqldev.exe ZZ_BACKDROP drop object on database 192.168.120.181POWERDES SQL>

看到有记录了，但是有一些没有取到值，比如ora_client_ip_address等，有待继续完善更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址