【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/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
