谁动了我的数据库--Oracle

57 阅读2分钟

事情的开始,是uat环境产生了一批脏数据,而且是固定每天某个时间点产生的,所以初步定位是某个定时任务。于是就去查定时任务应用日志,然并未发现这批数据的相关痕迹,那基本说明是别的应用影响了这张表,于是呼又去看其它应用的日志,依旧没有发现。于是又问运维,会不会有其它机器或者应用连到了uat数据库,结果是只有我们这个应用会用到那张表,那是谁动了数据库呢?

如果是应用出了问题,去查数据库,那链路会很清晰,排查起来so easy,但现在是应用没问题,数据库有脏数据,这个链路排查就复杂多了,这就只能从数据库层面查询。于是开始万能的百度,基本三种方案,

  1. 第一种是开启数据库的日志,这种应该是最详细的,但是分析起来可能也比较麻烦,还需要借助一些工具。所以暂时没考虑这种。

  2. 第二种是去查询vsqlarea、.v_session、v_$sql,会得到一些sql执行的记录,但是格式相同的sql只会记录一次,没法定位到具体某一条数据。

select 
	a.SQL_ID,a.*
from v$sqlarea a 
where (a.SQL_TEXT like '%xxx表%' 
or  a.SQL_TEXT like '%xxx表%' )
order by first_load_time desc;

select 
    osuser,
    TERMINAL,
    MACHINE,
    PROGRAM,
    USERNAME,
		PREV_EXEC_START,
    LAST_ACTIVE_TIME,
    logon_time
from sys.v_$session l,sys.v_$sql s 
where s.SQL_ID='g9j209xqwx46h' and l.USERNAME is not null
order by logon_time desc;

image.png

  1. 第三种就是写一个触发器,只要某一条数据insert或者update,就记录到某张中间表
    • 先创建一个中间表
CREATE TABLE XXX表_LOG(
      CRTAR_UNID VARCHAR2(128),
      OPERATE VARCHAR2(512),
      USERNAME VARCHAR2(512),
      HOST VARCHAR2(512),
      OS_USER VARCHAR2(512),
      IP_ADDRESS VARCHAR2(512),
      LEVEL2_UPDATE_TIME DATE,
      UPDATE_TIME DATE NOT NULL
);

COMMENT ON TABLE XXX表_LOG IS 'level2操作日志';

COMMENT ON COLUMN XXX表.OPERATE IS '操作类型';
COMMENT ON COLUMN XXX表.USERNAME IS '数据库用户';
COMMENT ON COLUMN XXX表.HOST IS '主机';
COMMENT ON COLUMN XXX表.OS_USER IS '客户端用户';
COMMENT ON COLUMN XXX表.IP_ADDRESS IS '客户端IP';
COMMENT ON COLUMN XXX表.LEVEL2_UPDATE_TIME IS 'LEVEL2表的更新时间';
COMMENT ON COLUMN XXX表.UPDATE_TIME IS '更新时间';
    • 再写一个触发器
CREATE TRIGGER "LEVEL2_CHANGE" BEFORE INSERT OR UPDATE ON "ROOT_LEVEL2" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW
BEGIN

if inserting then
insert into XXX表
(
    CRTAR_UNID,
    OPERATE,
    USERNAME,
    HOST,
    OS_USER,
    IP_ADDRESS,
    LEVEL2_UPDATE_TIME,
    UPDATE_TIME
)
values
    (
        :NEW.CRTAR_UNID,
        'insert',
        sys_context('USERENV', 'SESSION_USER'),
        sys_context('USERENV','HOST'),
        sys_context('USERENV','OS_USER'),
        sys_context('USERENV','IP_ADDRESS'),
        :NEW.CRTAR_UDATE,
        sysdate
    );
elsif updating then
insert into XXX表
(
    CRTAR_UNID,
    OPERATE,
    USERNAME,
    HOST,
    OS_USER,
    IP_ADDRESS,
    LEVEL2_UPDATE_TIME,
    UPDATE_TIME
)
values
    (
        :NEW.CRTAR_UNID,
        'update',
        sys_context('USERENV', 'SESSION_USER'),
        sys_context('USERENV','HOST'),
        sys_context('USERENV','OS_USER'),
        sys_context('USERENV','IP_ADDRESS'),
        :NEW.CRTAR_UDATE,
        sysdate
    );
end if;
END;

之后,只要业务表有数据更新,就能记录操作日志

image.png

最后发现那批脏数据,是来自另外一台机器,是之前新搭的一个环境,运维当时把uat的应用直接复制到那台新的机器上了,导致那台机器连到了uat数据库。所以简单记录一下。