9i新特性之資料庫監控系列

 

一個是完成寫日誌，一個是完成發郵件，在以後的程式中，可能會經常用到。

-----------------------------------------------------------------------------------------------------
1、寫日誌過程
/**************************************************************************
name:sp_Write_log
parameter:textContext in varchar2 日誌內容
create date:2003-06-01
creater:chen jiping
desc: ·寫日誌,把內容記到伺服器指定目錄下
·必須配置Utl_file_dir初始化引數，並保證日誌路徑與Utl_file_dir路徑一致或者是其中一個
****************************************************************************/
create or replace PROCEDURE sp_Write_log(textContext VARCHAR2)
IS
file_handle UTL_FILE.file_type;
Write_content VARCHAR2(1024);
Write_file_name VARCHAR2(50);
BEGIN
--開啟檔案
--Write_file_name := rtrim(to_char(SYSDATE,'YYYY-MM-DD'))||'.log';
Write_file_name := 'db108_alert.log';
file_handle := UTL_FILE.FOPEN('/u01/product/admin/ora81/logs',Write_file_name,'a');
Write_content := to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'||'||textContext;
--寫檔案
IF UTL_FILE.IS_OPEN(file_handle) THEN
UTL_FILE.PUT_LINE(file_handle,Write_content);
END IF;
--關閉檔案
UTL_FILE.Fclose(file_handle);
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.IS_OPEN(file_handle) THEN
UTL_FILE.Fclose(file_handle);
END IF;
END sp_Write_log;

2、傳送Email的過程
/**************************************************************************
name:sp_Send_mail
parameter: Rcpter in varchar2 接收者郵箱
Mail_Content in Varchar2 郵件內容
create date:2003-06-01
creater:chen jiping
desc: ·傳送郵件到指定郵箱
·只能指定一個郵箱，如果需要傳送到多個郵箱，需要另外的輔助程式
****************************************************************************/
create or replace procedure sp_send_mail(
Rcpter IN VARCHAR2,
Mail_Content IN VARCHAR2)
IS
conn utl_smtp.connection;
PROCEDURE send_header(NAME IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
utl_smtp.write_data(conn, NAME || ': ' || header || utl_tcp.CRLF);
END;

BEGIN
conn := utl_smtp.open_connection('smtp.ur.net.cn');
utl_smtp.helo(conn, 'oracle');
utl_smtp.mail(conn, 'oracle info');
utl_smtp.rcpt(conn, Rcpter);
utl_smtp.open_data(conn);
send_header('From', 'Oracle Database');
send_header('To', '"Recipient" <'||Rcpter||'>');
send_header('Subject', 'Hello');
utl_smtp.write_data(conn, utl_tcp.CRLF || Mail_Content);
utl_smtp.close_data(conn);
utl_smtp.quit(conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
utl_smtp.quit(conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
NULL; -- When the SMTP server is down or unavailable, we don't have
-- a connection to the server. The quit call will raise an
-- exception that we can ignore.
END;
raise_application_error(-20000,
'Failed to send mail due to the following error: ' || SQLERRM);
END sp_send_mail;

 

 

3、監控資料庫關閉/啟動的觸發器
create or replace trigger TR_DB_SHUTDOWN
before shutdown ON DATABASE
DECLARE
msMsg VARCHAR2(500);
BEGIN
msMsg :='user '||ora_login_user||' in '||ora_client_ip_address||' ready shutdown database '||ora_database_name|| ' now';
sp_send_mail('urmail@mail.com',msMsg);
EXCEPTION
WHEN OTHERS THEN
sp_send_mail(' urmail@mail.com ',ora_database_name||' shutdown error');
END;

 

 

 

 

5、監控所有DDL的觸發器
當然，在此之前我們需要建立一張表，用來記錄所有的DDL操作的資訊。

create table DDL$TRACE
(
LOGIN_USER VARCHAR2(30),
AUDSID NUMBER,
IPADDRESS VARCHAR2(20),
SCHEMA_USER VARCHAR2(30),
SCHEMA_OBJECT VARCHAR2(30),
DDL_TIME DATE,
DDL_SQL VARCHAR2(4000)
)

下面就是觸發器的主體，用來記錄審計所有的DDL操作。

CREATE OR REPLACE TRIGGER tr_trace_ddl
AFTER ddl
ON database
DECLARE
sql_text ora_name_list_t;
state_sql ddl$trace.ddl_sql%TYPE;
BEGIN
FOR i IN 1..ora_sql_txt(sql_text) LOOP
state_sql := state_sql||sql_text(i);
END LOOP;

INSERT INTO ddl$trace(login_user,audsid,ipaddress,
schema_user,schema_object,ddl_time,ddl_sql)
VALUES(ora_login_user,userenv('SESSIONID'),sys_context('userenv','ip_address'),
ora_dict_obj_owner,ora_dict_obj_name,SYSDATE,state_sql);
EXCEPTION
WHEN OTHERS THEN
sp_write_log('捕獲DDL語句異常錯誤：'||SQLERRM);
END tr_trace_ddl;

說明：以上語句是監控整個資料庫的DDL語句，如果只想監控一個使用者的話，需要修改

ON database
為
ON uruser.schema

 

 

 

 

7、監控表的Select語句
表的查詢用觸發器是檢測不到的，除非開啟資料庫審計，這樣又顯得過於麻煩，從ORACLE9i開始，提供了一個DBMS_FGA包，可以對單個的表進行審計並查詢審計資料。但是這個包的審計過程要求資料庫執行在CBO優化模式下，如果不是，可能會有意想不到的結果，這就要求對審計的表進行分析。

先簡單介紹一下該包中的第一個過程

PROCEDURE add_policy(object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2,
audit_condition IN VARCHAR2 := '1=1',
audit_column IN VARCHAR2 := NULL,
handler_schema IN VARCHAR2 := NULL,
handler_module IN VARCHAR2 := NULL,
enable IN BOOLEAN := TRUE);

object_schema：要審計的使用者的名稱，預設是本使用者
object_name：要審計的物件的名稱
policy_name：這次審計的策略名稱，每次審計都有一個名稱，來與別的審計策略區分
注：以上三部分唯一確定一個審計策略

audit_condition：審計條件（謂語動詞），預設全部，如規定SID=10，那麼當返回的結果集中包含SID=10的行或者SID在where條件中被使用的時候，該查詢語句將被審計。但是還有一種情況是如果顯式或隱式指定了audit_column，where條件中也沒有SID=10，但是結果集參考到了SID=10，這個語句也被審計。
audit_column：表示審計那些列，預設全部，如果指定列，那麼只有select（顯式指定）或者是where（隱式指定） 指定該列的時候才被審計，如果同時指定了audit_column與audit_condition，那麼必須滿足audit_column，才能被審計

如果同時執行了兩個條件，需要兩個條件同時滿足

handler_schema：我們還可以規定一個管理者來管理這個策略並在策略執行的時候，執行一個指定的過程
handler_module：指定這個策略執行的時候，執行的儲存過程的名稱
enable：確定審計策略是否馬上生效。
比如，我們現在增加一個策略

BEGIN
dbms_fga.add_policy( object_schema => 'MANAGER',
object_name => 'my_table,
policy_name => 'chk_table',
--audit_condition => 'sid like ''1%''',
--audit_column => 'sid',
--handler_schema => 'manager',
--handler_module => 'sp_chk_mytable',
enable => TRUE );
END;

以上的意思是，開始審計MANAGER使用者的表my_table，策略的名稱叫chk_table。
如果開啟audit_condition，則只對謂語條件隱式或顯式包含SID LIKE '1%' 的語句審計
如果開啟audit_column，則只對查詢列或條件列中有SID的語句審計

如果開啟了handler_schema與handler_module，則需要一個handler_schema指定的使用者manager下的儲存過程叫sp_chk_mytable，這個過程可以類似為：

CREATE PROCEDURE sp_chk_mytable (
p_object_schema VARCHAR2,
p_object_name VARCHAR2,
p_policy_name VARCHAR2) AS
BEGIN
INSERT INTO audit$proc (audtime,loguser,audsid, clientip
object_schema, object_name, policy_name )
VALUES (sysdate,ora_login_user,userenv('SESSIONID'),
sys_context('userenv','ip_address'),p_object_schema,p_object_name, p_policy_name );
EXCEPTION
WHEN OTHERS THEN
pkgsys_manage.sp_write_log('審計語句異常錯誤：'||SQLERRM);
END sp_chk_mytable;

當然，這裡還需要建立一個audit$proc的表，這個就不多說了，儲存過程可以根據自己的要求來改寫，獲取更多的使用者資訊或直接與登入資訊表關聯查詢。
注意：如果指定了過程而沒有過程的時候，審計不會失敗，但是被審計的資料將不能被普通查詢獲取

好，我們就執行一個簡單的例子，這個例子不包含執行本地過程。

SQL> select * from test;
A B
--------------------------------------- ---------------------------------------
1 2
3 4
2 6
4 5
6 7

SQL> analyze table test compute statistics;
Table analyzed

SQL> BEGIN
2 dbms_fga.add_policy( object_schema => 'MANAGER',
3 object_name => 'test',
4 policy_name => 'chk_test',
5 audit_condition => 'a = 1',
6 audit_column => 'b',
7 enable => TRUE );
8 END;
9 /

PL/SQL procedure successfully completed

SQL>

我們查詢如下檢視（或者是基表sys.fga$）

SQL> select t.object_schema,t.object_name,t.policy_name,t.enabled from dba_audit_policies t;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME ENABLED
------------------------------ ------------------------------ ------------------------------ -------
MANAGER TEST CHK_TEST YES

就可以看到我們的定製的策略資訊。

現在我們看看該審計策略怎麼生效
先需要了解sys.fga_log$（基表）與Dba_Fga_Audit_Trail（檢視）
fga_log$存放審計資訊的表，而Dba_Fga_Audit_Trail是對應的檢視

SQL> select count(*) from Dba_Fga_Audit_Trail ;

COUNT(*)
----------
0

可以看到，是沒有記錄的
我們現在來執行查詢

SQL> select b from test where a=1;

B
---------------------------------------
2

該語句肯定被審計，這個是最典型的情況了，顯式包含審計列b，謂語條件正好是a=1。

SQL> select b from test where b=2;

B
---------------------------------------
2

這個語句也被審計，為什麼呢？這個語句包含審計列，而且隱式包含了a=1（因為a=1與b=2是同一行）

SQL> select a from test where a=2;

A
---------------------------------------
2

這個語句就不被審計了，典型的什麼都不滿足

SQL> select b from test where b=4;

B
---------------------------------------
4

這個語句沒有被審計，因為雖然滿足了審計列的條件，但是沒有顯式或隱式包含a=1

SQL> select rownum from test where a=1 and b=2;

ROWNUM
---------------------------------------
1

這個語句也被審計，因為隱式包含B，謂語條件也滿足a=1

所以以上被審計的語句應當是3個，檢查一下了

SQL> select OBJECT_SCHEMA,OBJECT_NAME,POLICY_NAME,SQL_TEXT from Dba_Fga_Audit_Trail;

OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SQL_TEXT
-------------------------------- ------------------ ----------------------------------------------------
MANAGER TEST CHK_TEST select rownum from test where a=1 and b=2
MANAGER TEST CHK_TEST select b from test where a=1
MANAGER TEST CHK_TEST select b from test where b=2

對於已經定製的審計策略，我們可以利用dbms_fga.drop_policy來刪除。
如

BEGIN
dbms_fga.drop_policy( object_schema => 'MANAGER',
object_name => 'test',
policy_name => 'chk_test');
END;

就是刪除我們剛才定製的審計策略，當然我們還可以利用enable_policy與disable_policy來開啟與禁止相應的審計策略

最後，對於審計的記錄會越來越多，我們必須手工維護，刪除沒有參考價值的記錄，我們可以執行如下查詢來刪除審計記錄（需要delete any table的許可權或在sys下執行）：

delete from sys.fga_log$