DBA:在生産環境中創建監控表DML的觸發器

在生産環境中,總是可能出現這樣的情況:某張或者某些表的數據被莫名其妙的修改了,但是很難定位出是哪個用戶、哪個過程修改的。這是一個很讓DBA頭痛的事情(往往DBA對于整個代碼邏輯並不是非常了解)。要定位出“問題”語句,有幾種方法可以選擇:log miner;細節粒度審計;觸發器。Log miner要求要有歸檔日志(這個並非所有系統都可以做),而且需要有相當的磁盤空間,好處就是可以離線做;細節粒度升級能夠根據條件記錄下表的DML操作(9i及之前只能記錄SELECT語句),比較複雜的FGA需要較高權限的用戶來實現;觸發器比較靈活,能夠按照比較複雜的條件來記錄需要的信息。下面介紹觸發器如何實現。

要建立這樣的觸發器,需要利用到幾張系統視圖:v$session, v$sql, v$cursor,(10g, 9.2.0.1中可以,9.2.0.5, 9.2.0.之前存在bug)

SQL> connect "/ as sysdba"

grant select on SYS.V_$SQL to demo;

grant select on SYS.V_$SQL_BIND_DATA to demo;

grant select on SYS.V_$SQL_CURSOR to demo;

grant select on SYS.V_$SESSION to demo;

grant create trigger to demo;

CREATE TABLE trig_sql(lt DATE, sid NUMBER, SERIAL# NUMBER,

USERNAME VARCHAR2(30), OSUSER VARCHAR2(64),

MACHINE VARCHAR2(32), TERMINAL VARCHAR2(16),

PROGRAM VARCHAR2(64), sqlText VARCHAR2(2000),

status VARCHAR2(30));

方法1:

create or replace trigger ttt_trig

after insert or update on pga_ttt

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

begin

INSERT INTO trig_sql

select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,

s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,

'NONE'

from 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;

end;

方法2:

create or replace trigger ttt_trig

after insert or update on pga_ttt

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

begin

for cr in (select s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,

s.MACHINE, s.TERMINAL, s.PROGRAM,

q.sql_text line, c.status stat

from v$sql q, v$sql_cursor c, v$session s

where s.audsid=(select userenv('SESSIONID') from dual)

and s.prev_sql_addr=q.address

AND c.STATUS = 'CURFETCH')

loop

INSERT INTO trig_sql VALUES(SYSDATE, cr.sid, cr.SERIAL#,

cr.USERNAME, cr.OSUSER, cr.MACHINE,

cr.TERMINAL, cr.PROGRAM, cr.line,

cr.stat);

end loop;

COMMIT;

end;

第一種方法是通過前一SQL的地址(pre_sql_addr)和HASH(prev_hash_value)值來定位出發trigger的語句的,不能用sql_address和hash_value來定位,否則獲取到是觸發器裏面向日志表插入記錄數據的語句本身了。

第二個方法是通過通過地址加遊標的方法,按照視圖各個字段的解釋,應該是可以通過v$sql_cursor.parent_handle來定位的。但是通過測試發現,只有當前一條語句和查找前一條語句的語句在一個PLSQL塊中的時候才有效,

SQL> set serveroutput on

SQL> declare

2 v_date date;

3 v_sql varchar2(2000);

4 begin

5 select sysdate into v_date from dual;

6

7 select q.sql_text into v_sql

8 from v$sql q, v$sql_cursor c, v$session s

9 where s.audsid=(select userenv('SESSIONID') from dual)

10 and s.prev_sql_addr=q.address and q.address=c.parent_handle;

11

12 dbms_output.put_line(v_sql);

13 end;

14 /

SELECT SYSDATE FROM DUAL

PL/SQL procedure successfully completed.

因爲觸發器本身是一個PLSQL塊,所以總是無法獲得正確語句,最後只有通過cursor的狀態來獲取。下面簡單了解一下CURSOR各個狀態的含義:

·CURNULL:遊標已經存在,但沒有任何SQL語句在使用它(即cache在每個session內存中的遊標)

·CURSYNTAX:解析SQL語句過程的一個遊標狀態,說明調用遊標的SQL語句語法正確,但是沒有解析完成。

·CURPARSE:調用遊標的語句解析完畢

·CURBOUND:遊標使用了幫定變量,並定義好了幫定變量

·CURFETCH:遊標執行完畢,並fetch了數據

·CURROW:遊標正指向某一行

·ERROR:遊標錯誤,一般是有BUG了。

當一條INSERT或者UPDATE語句執行以後才會觸發觸發器,所以這時候的遊標狀態是CURFETCH,我們這就通過狀態爲CURFETCH來定位。

Oracle DBA 創建數據庫試題選
注:答案在選項後面 Q. 1 : This symbol, When you put infront of a line in the parameter file, signifies a comment   1. $   2. @   3. # ...查看完整版>>Oracle DBA 創建數據庫試題選
 
如何創建DBA ROLE
本人一次碰到有人將數據庫DBA ROLE刪除掉了,驚奇之余 ,只好想辦法恢複,經查找資料,終于完全恢複了DBA ROLE,現將恢複過程與大家共享: 用 sys/xxxx as sysdba登錄sqlplus,然後執行下列語句: ...查看完整版>>如何創建DBA ROLE
 
Oracle DBA創建數據庫試題精選
  Q. 1 : This symbol, When you put infront of a line in the parameter file, signifies a comment     1. $   2. @   3. #   4. !       3      ...查看完整版>>Oracle DBA創建數據庫試題精選
 
SQLServer 創建觸發器,更新表
在SQLServer,觸發器,插入、更新、刪除狀態: CREATE TRIGGER t_inms_alarmsON ..FOR INSERT, DELETEASDECLARE @rows intSELECT @rows = @@rowcountIF @rows = 0 return--如果表是插入,則同步更新AlarmsMiddl...查看完整版>>SQLServer 創建觸發器,更新表
 
flash中通過XMLSocket監控生産
二、技術介紹2、XMLSocket 對象  實現了客戶端socket(套接字),允許包含Flash應用的浏覽器與服務端建立socket連接,之後Flash應用與服務端就可以相互發送XML數據,而且在一個socket連接建立之後,在該連接上傳送...查看完整版>>flash中通過XMLSocket監控生産
 
電信機房環境視頻監控系統解決方案 信息化解決方案/案例資料中心
  一、綜述  大型電信運營商如:中國移動、中國電信、中國網通、中國聯通,有大量的通信機房,包括中心機房、模塊局機房、接入網機房,對這些機房的監控是運營商的運維部門最主要的工作之一,經過多年的建設,中...查看完整版>>電信機房環境視頻監控系統解決方案 信息化解決方案/案例資料中心
 
電信動力及環境集中監控系統解決方案
電信動力及環境集中監控系統解決方案
[系統概述] 動力設備及環境集中監控系統是用現代電子監測、控制裝置代替人工,對分布的通信局(站)的電源、空調、油機、蓄電池、高低壓配電等多種設備和環境的各種參數、圖象、聲音等進行遙測、遙信和遙控,實時...查看完整版>>電信動力及環境集中監控系統解決方案
 
環境自動監控系統--技術與管理|報價¥70.60|圖書,科學與自然,環境科學,環境質量評價與環境質量監測,周發武
目錄:圖書,科學與自然,環境科學,環境質量評價與環境質量監測,品牌:周發武基本信息·出版社:中國環境科學出版社·頁碼:566 頁碼·出版日:2007年·ISBN:9787802096332·條碼:9787802096332·版次:2007年10月第...查看完整版>>環境自動監控系統--技術與管理|報價¥70.60|圖書,科學與自然,環境科學,環境質量評價與環境質量監測,周發武
 
通信電源、機房環境集中監控系統優化和升級
中國網通集團有限公司沈陽市分公司 鄭寅波課題的提出電源、機房環境集中監控系統的出現,將原來相對分散的各個機房的電源、空調設備的運行狀態和環境數據進行了集中,方便了監控。然而隨著我國通信事業的發展,通信布...查看完整版>>通信電源、機房環境集中監控系統優化和升級
 
 
回到王朝網路移動版首頁