1.场景描述:
工作中,OGG数据同步的时候发现目标端B库有部分update操作没有同步过去,通过logminer挖掘源端A库发现没同步的操作是ogg配置用户执行操作的,OGG抽取进程中又设置了TRANLOGOPTIONS EXCLUDEUSER参数(防止出现级联同步)排除了OGG配置用户的操作,通过查看发现该同步表未从其他库同步过来(即A库是这张同步表最原始的数据来源)。然而logminer挖掘出来的os_username,machine_name和session_info都是unkown。通过表审计可以查看,不过需要相关审计参数打开,调整审计参数需要重启db,只能转换思路通过在表上创建触发器来审计该表的DML操作。
2.实验
- 创建实验测试表,并初始化
SQL>create table crmsb(id number, create_time date,update_time date,content varchar2(50));
SQL>insert into crmsb values(1,to_date('20200820 10:00:00','YYYY/MM/DD HH24:MI:SS'),to_date('20200820 10:00:00','YYYY/MM/DD HH24:MI:SS'),'first');
SQL>commit;
SQL>select * from crmsb;
ID CREATE_TIME UPDATE_TIME CONTENT
---------- ------------------------------ ------------------------------ ------------------------------
1 2020-08-20 10:00:00 2020-08-20 10:00:00 first
- 创建审计表
SQL>create table audit_crmsb
SQL>(
SQL> id_new NUMBER,
SQL> id_old NUMBER,
SQL> create_time_new date,
SQL> create_time_old date,
SQL> update_time_new date,
SQL> update_time_old date,
SQL> content_new varchar2(50),
SQL> content_old varchar2(50),
SQL> username VARCHAR2(30),
SQL> opt_date DATE,
SQL> opt_type VARCHAR2(10),
SQL> terminal VARCHAR2(50),
SQL> session_id NUMBER(10),
SQL> hostname VARCHAR2(50)
SQL>);
- 创建审计触发器
SQL> CREATE OR REPLACE TRIGGER TRI_AUDIT_CRMSBA
SQL> BEFORE INSERT OR UPDATE OR DELETE ON CRMSB
SQL> FOR EACH ROW
SQL> BEGIN
SQL> IF INSERTING THEN
SQL> INSERT INTO AUDIT_CRMSB
SQL> VALUES
SQL> (:NEW.ID,
SQL> :OLD.ID,
SQL> :NEW.CREATE_TIME,
SQL> :OLD.CREATE_TIME,
SQL> :NEW.UPDATE_TIME,
SQL> :OLD.UPDATE_TIME,
SQL> :NEW.CONTENT,
SQL> :OLD.CONTENT,
SQL> USER,
SQL> SYSDATE,
SQL> 'INSERT',
SQL> SYS_CONTEXT('USERENV', 'TERMINAL'),
SQL> USERENV('SID'),
SQL> SYS_CONTEXT('USERENV', 'HOST'));
SQL> ELSIF UPDATING THEN
SQL> INSERT INTO AUDIT_CRMSB
SQL> VALUES
SQL> (:NEW.ID,
SQL> :OLD.ID,
SQL> :NEW.CREATE_TIME,
SQL> :OLD.CREATE_TIME,
SQL> :NEW.UPDATE_TIME,
SQL> :OLD.UPDATE_TIME,
SQL> :NEW.CONTENT,
SQL> :OLD.CONTENT,
SQL> USER,
SQL> SYSDATE,
SQL> 'UPDATE',
SQL> SYS_CONTEXT('USERENV', 'TERMINAL'),
SQL> USERENV('SID'),
SQL> SYS_CONTEXT('USERENV', 'HOST'));
SQL> ELSIF DELETING THEN
SQL> INSERT INTO AUDIT_CRMSB
SQL> VALUES
SQL> (:NEW.ID,
SQL> :OLD.ID,
SQL> :NEW.CREATE_TIME,
SQL> :OLD.CREATE_TIME,
SQL> :NEW.UPDATE_TIME,
SQL> :OLD.UPDATE_TIME,
SQL> :NEW.CONTENT,
SQL> :OLD.CONTENT,
SQL> USER,
SQL> SYSDATE,
SQL> 'DELETE',
SQL> SYS_CONTEXT('USERENV', 'TERMINAL'),
SQL> USERENV('SID'),
SQL> SYS_CONTEXT('USERENV', 'HOST'));
SQL> END IF;
SQL> END;
SQL> /
- 数据测试
SQL> insert into crmsb values(2,sysdate,sysdate,'second');
1 row created.
SQL> commit;
Commit complete.
SQL> update crmsb set update_time = sysdate where id = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> delete from crmsb where id = 2;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from audit_crmsb;
ID_NEW ID_OLD CREATE_TIME_NEW CREATE_TIME_OLD UPDATE_TIME_NEW UDATE_TIME_OLD CONTENT_NEW CONTENT_OLD USERNAME OPT_DATE OPT_TYPE TERMINAL SESSION_ID HOSTNAME
2 2020/8/26 15:11:27 2020/8/26 15:11:27 second DBMGR 2020/8/26 15:11:27 INSERT pts/0 419 cnsz92vl00060
1 1 2020/8/20 10:00:00 2020/8/20 10:00:00 2020/8/26 15:11:57 2020/8/20 10:00:00 first first DBMGR 2020/8/26 15:11:57 UPDATE pts/0 419 cnsz92vl00060
2 2020/8/26 15:11:27 2020/8/26 15:11:27 second DBMGR 2020/8/26 15:12:11 DELETE pts/0 419 cnsz92vl00060
3.总结
定位一条数据SQL操作源头可以通过表审计,logminer日志挖掘和表上创建trigger记录数据操作情况(通常在业务中也会采取这种方法定位数据是否更新写入成功)