如何监控数据库的登陆登出、DDL语句等内容?

题目部分

如何监控数据库的登陆登出、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;

---------------优质麦课------------

详细内容可以添加麦老师微信或QQ私聊。

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20181203A0ATCK00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券