Oracle 11g 監控單張表的增刪改查操作
前言:
線上Oracle資料庫有張表的資料有些亂,根據應用db的log和應用的log也沒有檢查出來誰修改了,所以決定把這張單表做個詳細的insert、update、delete監控。
一:使用資料庫內建的審計功能
1,查看審計功能是否啟動
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /oracle/app/oracle/admin/power
des/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string NONE
SQL>
沒有開啟審計功能,需要自己去開啟一下。
2,開啟審計功能
需要用sysdba,注意audit_trail要為DB_EXTENDED才記錄執行的具體語句...
alter system set audit_sys_operations=TRUE scope=spfile;
SQL> alter system set audit_sys_operations=TRUE scope=spfile;
System altered.
SQL>
再次查看審計功能是否啟動
SQL> show parameter audit;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /oracle/app/oracle/admin/power
des/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string NONE
SQL>
需要重啟執行個體才能看到狀態。
3,關閉審計功能
SQL> alter system set audit_trail = none scope=spfile;
4,針對某張表的審計功能
AUDIT UPDATE,DELETE,INSERT ON T_TEST by access;
5,對該張表進行各種DML操作測試
6,查詢審計的資訊
select EXTENDED_TIMESTAMP,SESSION_ID,SQL_TEXT from DBA_COMMON_AUDIT_TRAIL ORDER BY EXTENDED_TIMESTAMP DESC;
二,採用觸發器
看到線上資料庫load比以前增加蠻多的,為了單張表的監控開啟審計比較消耗資源,有些不划算,所以可以採用另外一種辦法來做,就是在表上建立觸發器。
1,先建立建立測試表:
查看已經建立的表 aaa_test與trig_sql。
SQL> describe plas.aaa_test;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(100)
LOGIN_TIME DATE
SQL>
SQL> describe plas.trig_sql;
Name Null? Type
----------------------------------------- -------- ----------------------------
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)
CLIENT_IP VARCHAR2(60)
SQL>
2,並且在 trig_sql表上面添加索引:
create index idx_time on plas.trig_sql (LT);
3,建立觸發器
create or replace trigger pri_test
after insert or update or delete on plas.aaa_test
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF inserting THEN
INSERT INTO plas.trig_sql
select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
'INSERT',
sys_context('userenv','ip_address')
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;
ELSIF deleting then
INSERT INTO plas.trig_sql
select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
'DELETE',
sys_context('userenv','ip_address')
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;
ELSIF updating then
INSERT INTO plas.trig_sql
select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
'UPDATE',
sys_context('userenv','ip_address')
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 IF;
END;
4,開始進行資料操作測試:
insert into plas.aaa_test1 select 2,'tom',sysdate from dual;
update plas.aaa_test1 a set a.name='tom_up' where a.id=2;
update plas.aaa_test a set a.name='tom_up1' where a.id=1;
......
commit;
5,去查看錶記錄,會發現如下
SQL> select * from plas.trig_sql;
LT SID SERIAL# USERNAME OSUSER MACHINE TERMINAL PROGRAM SQLTEXT STATUS CLIENT_IP
----------- ---------- ---------- ------------------------------ ---------------------------------------------------------------- -------------------------------- ---------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------------------------------------
2014/10/29 1273 33297 POWERDESK Administrator WORKGROUP\WIN-18P5Q5AREH9 WIN-18P5Q5AREH9 plsqldev.exe begin :id := sys.dbms_transaction.local_transaction_id; end; INSERT 192.168.170.180
2014/10/29 1273 33297 POWERDESK Administrator WORKGROUP\WIN-18P5Q5AREH9 WIN-18P5Q5AREH9 plsqldev.exe begin :id := sys.dbms_transaction.local_transaction_id; end; DELETE 192.168.170.180
2014/10/29 1273 33297 POWERDESK Administrator WORKGROUP\WIN-18P5Q5AREH9 WIN-18P5Q5AREH9 plsqldev.exe begin :id := sys.dbms_transaction.local_transaction_id; end; INSERT 192.168.170.180
2014/10/29 1352 40155 POWERDESK Administrator WORKGROUP\WIN-18P5Q5AREH9 WIN-18P5Q5AREH9 plsqldev.exe begin :id := sys.dbms_transaction.local_transaction_id; end; INSERT 192.168.170.180
2014/10/29 1273 33297 POWERDESK Administrator WORKGROUP\WIN-18P5Q5AREH9 WIN-18P5Q5AREH9 plsqldev.exe begin :id := sys.dbms_transaction.local_transaction_id; end; UPDATE 192.168.170.180
2014/10/29 1273 33297 POWERDESK Administrator WORKGROUP\WIN-18P5Q5AREH9 WIN-18P5Q5AREH9 plsqldev.exe begin :id := sys.dbms_transaction.local_transaction_id; end; UPDATE 192.168.170.110
2014/10/29 25 39527 SYS oracle localhost.localdomain pts/1 sqlplus@localhost.localdomain (TNS V1-V3) update plas.aaa_test a set a.name='tom_update' where id=2 UPDATE
2014/10/29 25 39527 SYS oracle localhost.localdomain pts/1 sqlplus@localhost.localdomain (TNS V1-V3) update plas.aaa_test a set a.name='tom_update3' where id=3 UPDATE
8 rows selected
SQL>
PS:看到SQLTEXT有些都為 begin :id := sys.dbms_transaction.local_transaction_id; end; 的,是因為我執行的insert、delete、update語句在plsqldev.exe用戶端執行的,所以沒有記錄下執行的sql語句。而有些通過sqlplus@localhost.localdomain (TNS V1-V3)用戶端串連執行的,會記錄下執行過的update語句。
6,統計下當前都有哪些使用者以及ip執行了dml操作。
SQL> select username,client_ip from plas.trig_sql group by username,client_ip;
USERNAME CLIENT_IP
------------------------------ ------------------------------------------------------------
PLAS 192.168.170.180
DESKER 192.168.170.110
SQL>
在CentOS 6.4下安裝Oracle 11gR2(x64)
Oracle 11gR2 在VMWare虛擬機器中安裝步驟
Debian 下 安裝 Oracle 11g XE R2