【DB筆試面試449】如何監控數據庫的登陸登出、DDL語句等內容？
- 2019 年 10 月 11 日
- 筆記
如何監控數據庫的登陸登出、DDL語句等內容？
答案部分
系統中一些常用的監控都可以使用DDL觸發器和系統觸發器來實現。可以先創建一張記錄DDL語句的表XB_AUDIT_DDL_LHR（由於該表記錄數會很大，所以，需創建成按月自動分區的分區表），並創建合適的索引，然後創建存儲過程用於插入DDL信息到該日誌表中。最後再創建系統觸發器就可以將DDL語句或系統事件的信息插入日誌表中。下面詳細說明DDL觸發器和系統觸發器的使用。
首先創建一張記錄DDL語句的表XB_AUDIT_DDL_LHR，由於該表記錄數很大，所以，創建成按月自動分區的分區表，代碼如下所示：
CREATE TABLE XB_AUDIT_DDL_LHR( ID NUMBER PRIMARY KEY, INST_ID NUMBER, OPER_DATE DATE, OPERATION VARCHAR2(30), OBJECT_OWNER VARCHAR2(255), OBJECT_TYPE VARCHAR2(30), OBJECT_NAME VARCHAR2(30), SQL_TEXT VARCHAR2(4000), SQL_FULLTEXT CLOB, OS_USER VARCHAR2(255), CLIENT_IP VARCHAR2(20), CLIENT_HOSTNAME VARCHAR2(30), DB_SCHEMA VARCHAR2(30), SID NUMBER, SERIAL# NUMBER, SPID NUMBER, SESSION_TYPE VARCHAR2(1000), DATABASE_NAME VARCHAR2(255), ERRORS_INFOR VARCHAR2(4000), SQL_ID VARCHAR2(13), PREV_SQL_ID VARCHAR2(13) ) NOLOGGING PARTITION BY RANGE(OPER_DATE) INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) SUBPARTITION BY HASH(OPERATION) SUBPARTITION TEMPLATE ( SUBPARTITION SP1 , SUBPARTITION SP2 , SUBPARTITION SP3) (PARTITION P201610 VALUES LESS THAN(TO_DATE('201610','YYYYMM'))); ------創建主鍵列的用到的序列及其它常用列的索引 CREATE SEQUENCE S_XB_AUDIT_DDL_LHR START WITH 1 INCREMENT BY 1 CACHE 2000; SELECT S_XB_AUDIT_DDL_LHR.NEXTVAL FROM DUAL; CREATE INDEX IND_AUDIT_DDL_OBJECT_NAME ON XB_AUDIT_DDL_LHR(OBJECT_NAME,OPERATION) LOCAL NOLOGGING ; CREATE INDEX IND_AUDIT_DDL_OS_USER ON XB_AUDIT_DDL_LHR(OS_USER) LOCAL NOLOGGING; CREATE INDEX IND_AUDIT_DDL_SID ON XB_AUDIT_DDL_LHR(SID,SERIAL#) LOCAL NOLOGGING; CREATE INDEX IND_AUDIT_DDL_OBJECT_DATE ON XB_AUDIT_DDL_LHR(OPER_DATE,OPERATION,OS_USER) LOCAL NOLOGGING ; GRANT SELECT ON XB_AUDIT_DDL_LHR TO PUBLIC; 創建存儲過程，用於插入DDL信息到日誌表中，如下所示： CREATE OR REPLACE PROCEDURE PRO_TRI_DDL_INSET_LHR(P_SQL_FULLTEXT VARCHAR2) AUTHID CURRENT_USER AS SP_XB_AUDIT_DDL_LHR XB_AUDIT_DDL_LHR%ROWTYPE; V_TMP VARCHAR2(255); BEGIN SELECT A.SID, A.SERIAL#, (SELECT B.SPID FROM GV$PROCESS B WHERE B.ADDR = A.PADDR AND B.INST_ID = USERENV('INSTANCE')) SPID, UPPER(A.OSUSER) OSUSER, A.MACHINE || '--' || A.PROGRAM || '--' || A.MODULE || '--' || A.ACTION SESSION_TYPE, SUBSTR(P_SQL_FULLTEXT, 1, 3900), A.SQL_ID, A.PREV_SQL_ID, A.USERNAME, A.INST_ID INTO SP_XB_AUDIT_DDL_LHR.SID, SP_XB_AUDIT_DDL_LHR.SERIAL#, SP_XB_AUDIT_DDL_LHR.SPID, SP_XB_AUDIT_DDL_LHR.OS_USER, SP_XB_AUDIT_DDL_LHR.SESSION_TYPE, SP_XB_AUDIT_DDL_LHR.SQL_TEXT, SP_XB_AUDIT_DDL_LHR.SQL_ID, SP_XB_AUDIT_DDL_LHR.PREV_SQL_ID, SP_XB_AUDIT_DDL_LHR.DB_SCHEMA, SP_XB_AUDIT_DDL_LHR.INST_ID FROM GV$SESSION A WHERE A.AUDSID = USERENV('SESSIONID') AND A.INST_ID = USERENV('INSTANCE'); INSERT INTO XB_AUDIT_DDL_LHR (ID, INST_ID, OPER_DATE, OPERATION, OBJECT_TYPE, OBJECT_NAME, SQL_TEXT, SQL_FULLTEXT, OS_USER, CLIENT_IP, CLIENT_HOSTNAME, DB_SCHEMA, SID, SERIAL#, SPID, SESSION_TYPE, DATABASE_NAME, OBJECT_OWNER, ERRORS_INFOR, SQL_ID, PREV_SQL_ID) VALUES (S_XB_AUDIT_DDL_LHR.NEXTVAL, USERENV('INSTANCE'), -- sp_xb_audit_ddl_lhr.INST_ID ora_instance_num SYSDATE, ORA_SYSEVENT, --sys.sysevent ORA_DICT_OBJ_TYPE, ORA_DICT_OBJ_NAME, SP_XB_AUDIT_DDL_LHR.SQL_TEXT, P_SQL_FULLTEXT, UPPER(SYS_CONTEXT('USERENV', 'OS_USER')), -- sp_xb_audit_ddl_lhr.os_user SYS_CONTEXT('userenv', 'ip_address'), --ora_client_ip_address SYS_CONTEXT('userenv', 'terminal'), --sys_context('userenv', 'host') NVL2(ORA_LOGIN_USER, SYS_CONTEXT('USERENV', 'SESSION_USER'), SP_XB_AUDIT_DDL_LHR.DB_SCHEMA), -- SYS_CONTEXT('USERENV', 'SESSION_USER') sys.login_user SP_XB_AUDIT_DDL_LHR.SID, ---- SYS_CONTEXT('USERENV', 'SID'), SP_XB_AUDIT_DDL_LHR.SERIAL#, SP_XB_AUDIT_DDL_LHR.SPID, SP_XB_AUDIT_DDL_LHR.SESSION_TYPE || V_TMP, ORA_DATABASE_NAME, --sys_context('USERENV', 'DB_NAME') ORA_DICT_OBJ_OWNER, DBMS_UTILITY.FORMAT_ERROR_STACK, SP_XB_AUDIT_DDL_LHR.SQL_ID, SP_XB_AUDIT_DDL_LHR.PREV_SQL_ID); COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END PRO_TRI_DDL_INSET_LHR; 下面的觸發器將會話退出和數據庫關閉的信息加入日誌表XB_AUDIT_DDL_LHR中： CREATE OR REPLACE TRIGGER TRI_AUDIT_LOGOFF_LHR BEFORE LOGOFF OR SHUTDOWN ON DATABASE --database 、 SCHEMA --退出、數據庫關閉 DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN -- 調用存過 PRO_TRI_DDL_INSET_LHR(''); EXCEPTION WHEN OTHERS THEN ROLLBACK; END; 此時若有會話退出的話，查詢日誌表XB_AUDIT_DDL_LHR可以看到如下的信息： 下面再創建登錄和DDL觸發器，腳本如下所示： CREATE OR REPLACE TRIGGER TRI_AUDIT_DDL_LHR -- AFTER DDL OR servererror OR logon OR STARTUP ON DATABASE --database 、 SCHEMA --ddl語句、服務器報錯、用戶登陸、用戶退出、數據庫啟動、數據庫關閉 AFTER DDL OR SERVERERROR OR LOGON OR STARTUP ON DATABASE /* WHEN (ora_dict_obj_name NOT IN ('XB_AUDIT_DDL_LHR', 'PRO_TRI_DDL_INSET_LHR') OR ora_dict_obj_name IS NULL)*/ DECLARE PRAGMA AUTONOMOUS_TRANSACTION; N NUMBER; V_SQL_FULLTEXT VARCHAR2(32767) := NULL; SQL_TEXT ORA_NAME_LIST_T; BEGIN IF ORA_SYSEVENT = 'LOGON' THEN ----------- 填充 v$session 的 CLIENT_INFO 和 CLIENT_IDENTIFIER 列 DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('userenv', 'ip_address')); DBMS_SESSION.SET_IDENTIFIER(SYS_CONTEXT('userenv', 'HOST')); ELSE --得到執行的DDL語句 BEGIN N := ORA_SQL_TXT(SQL_TEXT); FOR I IN 1 .. N LOOP V_SQL_FULLTEXT := V_SQL_FULLTEXT || SQL_TEXT(I); END LOOP; EXCEPTION WHEN OTHERS THEN ROLLBACK; END; END IF; --插入日誌 PRO_TRI_DDL_INSET_LHR(V_SQL_FULLTEXT); EXCEPTION WHEN OTHERS THEN ROLLBACK; END TRI_AUDIT_DDL_LHR; 測試DDL觸發器，創建表、TRUNCATE表、刪除表，如下所示： CREATE TABLE TEST_DDL AS SELECT * FROM DUAL; TRUNCATE TABLE TEST_DDL; DROP TABLE TEST_DDL; SELECT * FROM XB_AUDIT_DDL_LHR T WHERE T.OBJECT_NAME='TEST_DDL'; 若有服務器的錯誤，也可以被記錄下來，如下所示： SELECT * FROM XB_AUDIT_DDL_LHR T WHERE T.OPERATION='SERVERERROR'; 本小節的監控內容也解決了前面提出的一個問題，「如何監控會話的登錄登出情況？」，可以通過查詢日誌表XB_AUDIT_DDL_LHR來解決，如下所示： SELECT T.OS_USER, T.CLIENT_IP, COUNT(1) FROM XB_AUDIT_DDL_LHR T GROUP BY T.OS_USER, T.CLIENT_IP;
About Me：小麥苗
● 本文作者：小麥苗，只專註於數據庫的技術，更注重技術的運用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列題目來源於作者的學習筆記，部分整理自網絡，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
● 題目解答若有不當之處，還望各位朋友批評指正，共同進步
