谁动了我的数据库--Oracle

事情的开始，是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;

3. 第三种就是写一个触发器，只要某一条数据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;

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

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