logminer实战：查找删除数据的元凶
从上一篇https://www.modb.pro/db/387650 中我们得知，logminer 其中一个重要的作用就是：执行事后审计。
刚好，今天就有一例，某客户反馈，有张核心表的数据无缘无故没有之前的数据了，但是多方询问，没有人动过这张表，这就有很大隐患，虽然客户可以自行恢复表的数据，但是要追责，查看到底是谁删除了这个表的数据。此时，客户现场没有任何审计软件存在，想要我们通过数据库层面，能不能找到元凶。那么，此时，logminer就出场了，它可以完美的进行时候审计。
下面我们先通过验证一些理论，然后进行查看。
数据库没开supplemental log是否能挖掘
测试环境：
[oracle@19crac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 18 23:03:20 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATADG
Oldest online log sequence 26
Next log sequence to archive 27
Current log sequence 27
SQL> SELECT supplemental_log_data_min min,
supplemental_log_data_pk pk,
supplemental_log_data_ui ui,
2 3 4 supplemental_log_data_fk fk,
5 supplemental_log_data_all allc
6 FROM v$database;
MIN PK UI FK ALL
-------- --- --- --- ---
NO NO NO NO NO
SQL> select FORCE_LOGGING from v$database;
FORCE_LOGGING
---------------------------------------
NO
2节点oracle 19c RAC pdb环境，开归档。任何附加日志都没有开。而且也没开启force logging。就是模拟正常的正常环境。
session 1：
模拟普通用户，普通表，进行的操作
SQL> alter session set container=ORAPDB;
Session altered.
SQL> create tablespace test1 datafile size 100M;
Tablespace created.
SQL> create user test1 identified by test1 default tablespace test1 quota unlimited on test1;
User created.
SQL> grant connect,resource to test1;
Grant succeeded.
SQL> conn test1/test1@10.1.11.27:1521/ORAPDB
Connected.
SQL> show user;
USER is "TEST1"
SQL> create table t2(cons_no varchar2(10),cons_name varchar2(10));
Table created.
SQL> begin
2 for i in 1..10 loop
3 insert into t2 values(i,'aaaaa');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
session 2:
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
SQL> set line 200
SQL> col name for a80
SQL> SELECT NAME, SEQUENCE# FROM V$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
NAME SEQUENCE#
-------------------------------------------------------------------------------- ----------
+DATADG/ORA19C/ARCHIVELOG/2022_08_18/thread_1_seq_24.281.1113087455 24
SQL> alter system switch logfile;
System altered.
SQL> SELECT NAME, SEQUENCE# FROM V$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
NAME SEQUENCE#
-------------------------------------------------------------------------------- ----------
+DATADG/ORA19C/ARCHIVELOG/2022_08_18/thread_1_seq_25.282.1113087477 25
注意：
SQL> alter session set container=ORAPDB;
Session altered.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATADG/ORA19C/ARCHIVELOG/2022_08_18/thread_1_seq_26.283.1113087601',OPTIONS => DBMS_LOGMNR.NEW);
BEGIN DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATADG/ORA19C/ARCHIVELOG/2022_08_18/thread_1_seq_26.283.1113087601',OPTIONS => DBMS_LOGMNR.NEW); END;
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
ORA-06512: at "SYS.DBMS_LOGMNR", line 82
ORA-06512: at line 1
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
在pdb 中，无法切换日志，和启动logminer。所有日志相关的操作必须在cdb下进行。
session 3:
[oracle@19crac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 18 22:58:31 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATADG/ORA19C/ARCHIVELOG/2022_08_18/thread_1_seq_25.282.1113087477',OPTIONS => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(-
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
> DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
> DBMS_LOGMNR.PRINT_PRETTY_SQL);
PL/SQL procedure successfully completed.
SQL> set linesize 300 pagesize 9999
SQL> col seg_owner for a10
SQL> col seg_name for a20
SQL> col table_name for a20
SQL> col USR for a10
SQL> col sql_redo for a35
SQL> col SQL_UNDO for a35
SQL> SELECT SEG_OWNER,
2 SEG_NAME,
3 TABLE_NAME, USERNAME AS usr,SQL_REDO,SQL_UNDO,operation FROM
4 V$LOGMNR_CONTENTS WHERE SEG_OWNER ='TEST1';
SEG_OWNER SEG_NAME TABLE_NAME USR SQL_REDO SQL_UNDO OPERATION
---------- -------------------- -------------------- ---------- ----------------------------------- ----------------------------------- --------------------------------
TEST1 T2 T2 UNKNOWN create table test1.t2(cons_no varch DDL
ar2(10),cons_name varchar2(10));
TEST1 T2 T2 UNKNOWN insert into "TEST1"."T2" delete from "TEST1"."T2" INSERT
values where
"CONS_NO" = '1', "CONS_NO" = '1' and
"CONS_NAME" = 'aaaaa'; "CONS_NAME" = 'aaaaa' and
ROWID = 'AAAFr+AAOAAAACHAAA';
TEST1 T2 T2 UNKNOWN insert into "TEST1"."T2" delete from "TEST1"."T2" INSERT
values where
"CONS_NO" = '2', "CONS_NO" = '2' and
"CONS_NAME" = 'aaaaa'; "CONS_NAME" = 'aaaaa' and
ROWID = 'AAAFr+AAOAAAACHAAB';
TEST1 T2 T2 UNKNOWN insert into "TEST1"."T2" delete from "TEST1"."T2" INSERT
values where
"CONS_NO" = '3', "CONS_NO" = '3' and
"CONS_NAME" = 'aaaaa'; "CONS_NAME" = 'aaaaa' and
ROWID = 'AAAFr+AAOAAAACHAAC';
TEST1 T2 T2 UNKNOWN insert into "TEST1"."T2" delete from "TEST1"."T2" INSERT
values where
"CONS_NO" = '4', "CONS_NO" = '4' and
"CONS_NAME" = 'aaaaa'; "CONS_NAME" = 'aaaaa' and
ROWID = 'AAAFr+AAOAAAACHAAD';
TEST1 T2 T2 UNKNOWN insert into "TEST1"."T2" delete from "TEST1"."T2" INSERT
values where
"CONS_NO" = '5', "CONS_NO" = '5' and
"CONS_NAME" = 'aaaaa'; "CONS_NAME" = 'aaaaa' and
ROWID = 'AAAFr+AAOAAAACHAAE';
TEST1 T2 T2 UNKNOWN insert into "TEST1"."T2" delete from "TEST1"."T2" INSERT
values where
"CONS_NO" = '6', "CONS_NO" = '6' and
"CONS_NAME" = 'aaaaa'; "CONS_NAME" = 'aaaaa' and
ROWID = 'AAAFr+AAOAAAACHAAF';
TEST1 T2 T2 UNKNOWN insert into "TEST1"."T2" delete from "TEST1"."T2" INSERT
values where
"CONS_NO" = '7', "CONS_NO" = '7' and
"CONS_NAME" = 'aaaaa'; "CONS_NAME" = 'aaaaa' and
ROWID = 'AAAFr+AAOAAAACHAAG';
TEST1 T2 T2 UNKNOWN insert into "TEST1"."T2" delete from "TEST1"."T2" INSERT
values where
"CONS_NO" = '8', "CONS_NO" = '8' and
"CONS_NAME" = 'aaaaa'; "CONS_NAME" = 'aaaaa' and
ROWID = 'AAAFr+AAOAAAACHAAH';
TEST1 T2 T2 UNKNOWN insert into "TEST1"."T2" delete from "TEST1"."T2" INSERT
values where
"CONS_NO" = '9', "CONS_NO" = '9' and
"CONS_NAME" = 'aaaaa'; "CONS_NAME" = 'aaaaa' and
ROWID = 'AAAFr+AAOAAAACHAAI';
TEST1 T2 T2 UNKNOWN insert into "TEST1"."T2" delete from "TEST1"."T2" INSERT
values where
"CONS_NO" = '10', "CONS_NO" = '10' and
"CONS_NAME" = 'aaaaa'; "CONS_NAME" = 'aaaaa' and
ROWID = 'AAAFr+AAOAAAACHAAJ';
11 rows selected.
解释下这个字段：
USERNAME VARCHAR2(30) Name of the user who executed the transaction
可以看出username这个字段，就是标示谁执行了这条语句，也就是经常说的误操作的用户。但是一般该字段都为unkown。
从挖掘的结果可以看出，虽然没开附加日志，但是依旧能挖掘出create table 的DDL,和insert 的DML语句。而且会全部记录，总共11行记录。
delete 一条数据
session 1：
SQL> delete from t2 where cons_no='3';
1 row deleted.
SQL> commit;
Commit complete.
session 2:
SQL> alter system switch logfile;
System altered.
SQL> SELECT NAME, SEQUENCE# FROM V$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
NAME SEQUENCE#
-------------------------------------------------------------------------------- ----------
+DATADG/ORA19C/ARCHIVELOG/2022_08_18/thread_1_seq_26.283.1113087601 26
session 4:
[[A[oracle@19crac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 18 23:00:26 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATADG/ORA19C/ARCHIVELOG/2022_08_18/thread_1_seq_26.283.1113087601',OPTIONS => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(-
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
DBMS_LOGMNR.PRINT_PRETTY_SQL);
> > >
PL/SQL procedure successfully completed.
SQL>
SQL> set linesize 300 pagesize 9999
col seg_owner for a10
SQL> SQL> col seg_name for a20
SQL> col table_name for a20
SQL> col USR for a10
SQL> col sql_redo for a35
SQL> col SQL_UNDO for a35
SQL> SELECT SEG_OWNER,
2 SEG_NAME,
3 TABLE_NAME, USERNAME AS usr,SQL_REDO,SQL_UNDO,operation FROM
4 V$LOGMNR_CONTENTS WHERE SEG_OWNER ='TEST1';
SEG_OWNER SEG_NAME TABLE_NAME USR SQL_REDO SQL_UNDO OPERATION
---------- -------------------- -------------------- ---------- ----------------------------------- ----------------------------------- --------------------------------
TEST1 T2 T2 UNKNOWN delete from "TEST1"."T2" insert into "TEST1"."T2" DELETE
where values
"CONS_NO" = '3' and "CONS_NO" = '3',
"CONS_NAME" = 'aaaaa' and "CONS_NAME" = 'aaaaa';
ROWID = 'AAAFr+AAOAAAACHAAC';
也可以挖掘到。
下面delete整张表，truncate 和 drop 表同时进行。
session 1：
SQL> delete from t2 ;
9 rows deleted.
SQL> commit;
Commit complete.
SQL> truncate table t2;
Table truncated.
SQL> drop table t2;
Table dropped.
session 2:
SQL> alter system switch logfile;
System altered.
SQL> SELECT NAME, SEQUENCE# FROM V$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
NAME SEQUENCE#
-------------------------------------------------------------------------------- ----------
+DATADG/ORA19C/ARCHIVELOG/2022_08_18/thread_1_seq_27.284.1113088503 27
session 5:
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATADG/ORA19C/ARCHIVELOG/2022_08_18/thread_1_seq_27.284.1113088503',OPTIONS => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(-
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
> DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
> DBMS_LOGMNR.PRINT_PRETTY_SQL);
PL/SQL procedure successfully completed.
SQL> set linesize 300 pagesize 9999
SQL> col seg_owner for a10
SQL> col seg_name for a20
SQL> col table_name for a20
SQL> col USR for a10
SQL> col sql_redo for a35
SQL> col SQL_UNDO for a35
SQL> SELECT SEG_OWNER,
2 SEG_NAME,
3 TABLE_NAME, USERNAME AS usr,SQL_REDO,SQL_UNDO,operation FROM
4 V$LOGMNR_CONTENTS WHERE SEG_OWNER ='TEST1';
SEG_OWNER SEG_NAME TABLE_NAME USR SQL_REDO SQL_UNDO OPERATION
---------- -------------------- -------------------- ---------- ----------------------------------- ----------------------------------- --------------------------------
TEST1 BIN$5oZxrqzphHPgUxYL BIN$5oZxrqzphHPgUxYL UNKNOWN delete from "TEST1"."BIN$5oZxrqzphH insert into "TEST1"."BIN$5oZxrqzphH DELETE
AQqSig==$0 AQqSig==$0 PgUxYLAQqSig==$0" PgUxYLAQqSig==$0"
where values
"CONS_NO" = '1' and "CONS_NO" = '1',
"CONS_NAME" = 'aaaaa' and "CONS_NAME" = 'aaaaa';
ROWID = 'AAAFr+AAOAAAACHAAA';
TEST1 BIN$5oZxrqzphHPgUxYL BIN$5oZxrqzphHPgUxYL UNKNOWN delete from "TEST1"."BIN$5oZxrqzphH insert into "TEST1"."BIN$5oZxrqzphH DELETE
AQqSig==$0 AQqSig==$0 PgUxYLAQqSig==$0" PgUxYLAQqSig==$0"
where values
"CONS_NO" = '2' and "CONS_NO" = '2',
"CONS_NAME" = 'aaaaa' and "CONS_NAME" = 'aaaaa';
ROWID = 'AAAFr+AAOAAAACHAAB';
TEST1 BIN$5oZxrqzphHPgUxYL BIN$5oZxrqzphHPgUxYL UNKNOWN delete from "TEST1"."BIN$5oZxrqzphH insert into "TEST1"."BIN$5oZxrqzphH DELETE
AQqSig==$0 AQqSig==$0 PgUxYLAQqSig==$0" PgUxYLAQqSig==$0"
where values
"CONS_NO" = '4' and "CONS_NO" = '4',
"CONS_NAME" = 'aaaaa' and "CONS_NAME" = 'aaaaa';
ROWID = 'AAAFr+AAOAAAACHAAD';
TEST1 BIN$5oZxrqzphHPgUxYL BIN$5oZxrqzphHPgUxYL UNKNOWN delete from "TEST1"."BIN$5oZxrqzphH insert into "TEST1"."BIN$5oZxrqzphH DELETE
AQqSig==$0 AQqSig==$0 PgUxYLAQqSig==$0" PgUxYLAQqSig==$0"
where values
"CONS_NO" = '5' and "CONS_NO" = '5',
"CONS_NAME" = 'aaaaa' and "CONS_NAME" = 'aaaaa';
ROWID = 'AAAFr+AAOAAAACHAAE';
TEST1 BIN$5oZxrqzphHPgUxYL BIN$5oZxrqzphHPgUxYL UNKNOWN delete from "TEST1"."BIN$5oZxrqzphH insert into "TEST1"."BIN$5oZxrqzphH DELETE
AQqSig==$0 AQqSig==$0 PgUxYLAQqSig==$0" PgUxYLAQqSig==$0"
where values
"CONS_NO" = '6' and "CONS_NO" = '6',
"CONS_NAME" = 'aaaaa' and "CONS_NAME" = 'aaaaa';
ROWID = 'AAAFr+AAOAAAACHAAF';
TEST1 BIN$5oZxrqzphHPgUxYL BIN$5oZxrqzphHPgUxYL UNKNOWN delete from "TEST1"."BIN$5oZxrqzphH insert into "TEST1"."BIN$5oZxrqzphH DELETE
AQqSig==$0 AQqSig==$0 PgUxYLAQqSig==$0" PgUxYLAQqSig==$0"
where values
"CONS_NO" = '7' and "CONS_NO" = '7',
"CONS_NAME" = 'aaaaa' and "CONS_NAME" = 'aaaaa';
ROWID = 'AAAFr+AAOAAAACHAAG';
TEST1 BIN$5oZxrqzphHPgUxYL BIN$5oZxrqzphHPgUxYL UNKNOWN delete from "TEST1"."BIN$5oZxrqzphH insert into "TEST1"."BIN$5oZxrqzphH DELETE
AQqSig==$0 AQqSig==$0 PgUxYLAQqSig==$0" PgUxYLAQqSig==$0"
where values
"CONS_NO" = '8' and "CONS_NO" = '8',
"CONS_NAME" = 'aaaaa' and "CONS_NAME" = 'aaaaa';
ROWID = 'AAAFr+AAOAAAACHAAH';
TEST1 BIN$5oZxrqzphHPgUxYL BIN$5oZxrqzphHPgUxYL UNKNOWN delete from "TEST1"."BIN$5oZxrqzphH insert into "TEST1"."BIN$5oZxrqzphH DELETE
AQqSig==$0 AQqSig==$0 PgUxYLAQqSig==$0" PgUxYLAQqSig==$0"
where values
"CONS_NO" = '9' and "CONS_NO" = '9',
"CONS_NAME" = 'aaaaa' and "CONS_NAME" = 'aaaaa';
ROWID = 'AAAFr+AAOAAAACHAAI';
TEST1 BIN$5oZxrqzphHPgUxYL BIN$5oZxrqzphHPgUxYL UNKNOWN delete from "TEST1"."BIN$5oZxrqzphH insert into "TEST1"."BIN$5oZxrqzphH DELETE
AQqSig==$0 AQqSig==$0 PgUxYLAQqSig==$0" PgUxYLAQqSig==$0"
where values
"CONS_NO" = '10' and "CONS_NO" = '10',
"CONS_NAME" = 'aaaaa' and "CONS_NAME" = 'aaaaa';
ROWID = 'AAAFr+AAOAAAACHAAJ';
TEST1 T2 T2 UNKNOWN truncate table t2; DDL
TEST1 T2 T2 UNKNOWN ALTER TABLE "TEST1"."T2" RENAME TO DDL
"BIN$5oZxrqzphHPgUxYLAQqSig==$0" ;
TEST1 T2 T2 UNKNOWN drop table t2 AS "BIN$5oZxrqzphHPgU DDL
xYLAQqSig==$0" ;
12 rows selected.
可以看出，delete整张表，truncate，drop都可以在不开附件日志的情况下，挖掘出执行过的业务sql。drop 的时候，数据库对应2个步骤，先把原表重命名到回收站的对象，所以数据库在做了备份，我们可以从回收站中闪回对象，也可以从这看出。
在挖掘的过程中，数据库alert 日志如下：
2022-08-18T23:15:39.992995+08:00 ---start启动logminer 的时候的输出 LOGMINER: summary for session# = 2147486721 LOGMINER: StartScn: 3490316 (0x000000000035420c) LOGMINER: EndScn: 0 (0x0000000000000000) LOGMINER: HighConsumedScn: 0 LOGMINER: PSR flags: 0x0 LOGMINER: Session Flags: 0x7000449 LOGMINER: Session Flags2: 0x0 LOGMINER: Read buffers: 4 LOGMINER: Region Queue size: 256 LOGMINER: Redo Queue size: 4096 LOGMINER: Memory LWM: limit 10M, LWM 12M, 80% LOGMINER: Memory Release Limit: 0M LOGMINER: Max Decomp Region Memory: 1M LOGMINER: Transaction Queue Size: 1024 2022-08-18T23:15:51.541083+08:00 ---select 查询V$LOGMNR_CONTENTS时候的输出 LOGMINER: Begin mining logfile for session -2147480575 thread 1 sequence 27, +DATADG/ORA19C/ARCHIVELOG/2022_08_18/thread_1_seq_27.284.1113088503 2022-08-18T23:15:51.926827+08:00 LOGMINER: End mining logfile for session -2147480575 thread 1 sequence 27, +DATADG/ORA19C/ARCHIVELOG/2022_08_18/thread_1_seq_27.284.1113088503
对应挖掘时候的视图输出：
SQL> select * from v$logmnr_logs;
LOG_ID FILENAME LOW_TIME HIGH_TIME DB_ID DB_NAME RESET_SCN RESET_SCN COMPATIBLE THREAD_ID THREAD_SQN LOW_SCN NEXT_SCN DIC DIC TYPE BLOCKSIZE FILESIZE INFO STATUS CON_ID
---------- ------------------------------------------------------------ --------- --------- ---------- -------- ---------- --------- ----------------- ---------- ---------- ---------- ---------- --- --- ------- ---------- ---------- -------------------------------- ---------- ----------
27 +DATADG/ORA19C/ARCHIVELOG/2022_08_18/thread_1_seq_27.284.111 18-AUG-22 18-AUG-22 1103779547 ORA19C 1 08-SEP-20 19.0.0.0.0 1 27 3490316 3492615 NO NO ARCHIVE 512 5496320 0 1
3088503
可以看出视图的字段值是和alert日志的输出相对应的。
上述为啥session 3，4，5.启用了3个会话来挖掘，主要是因为涉及的logmnr对象，都是会话临时表和临时对象，会话退出，内容就都没了，避免相互之间影响。
综上，在数据库不开启附加日志的情况下，logminer 是可以正常挖掘出一些误删除数据的，可以用作事后审计。
这里需要说明下，我在11204 的单机文件系统，和11204 asm standalone 环境中，确实没有测出效果，也就是不开最小附加日志，不会挖掘出delete 相关的内容。（应该是和文件系统和asm standalone 有关，RAC就不涉及）。
关于supplemental log的相关内容，我们在另一篇文章中再详细描述。
关于update 操作：
session 1:
SQL> update t2 set CONS_NAME='bbbbb' where CONS_NO='4';
1 row updated.
SQL> commit;
Commit complete.
session 2:
SQL> alter system switch logfile;
System altered.
SQL> SQL> SELECT NAME, SEQUENCE# FROM V$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
NAME SEQUENCE#
-------------------------------------------------------------------------------- ----------
+DATADG/ORA19C/ARCHIVELOG/2022_08_19/thread_1_seq_34.291.1113134765 34
session 6:
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATADG/ORA19C/ARCHIVELOG/2022_08_19/thread_1_seq_34.291.1113134765',OPTIONS => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(-
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
> DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
> DBMS_LOGMNR.PRINT_PRETTY_SQL);
PL/SQL procedure successfully completed.
SQL> set linesize 300 pagesize 9999
SQL> col seg_owner for a10
SQL> col seg_name for a20
SQL> col table_name for a20
SQL> col USR for a10
SQL> col sql_redo for a35
SQL> col SQL_UNDO for a35
SQL> SELECT SEG_OWNER,
2 SEG_NAME,
3 TABLE_NAME, USERNAME AS usr,SQL_REDO,SQL_UNDO,operation FROM
4 V$LOGMNR_CONTENTS WHERE SEG_OWNER ='TEST1';
SEG_OWNER SEG_NAME TABLE_NAME USR SQL_REDO SQL_UNDO OPERATION
---------- -------------------- -------------------- ---------- ----------------------------------- ----------------------------------- --------------------------------
TEST1 T2 T2 UNKNOWN Unsupported Unsupported UNSUPPORTED
SQL> set linesize 300 pagesize 9999
col seg_owner for a10
col seg_name for a20
SQL> SQL> SQL> col table_name for a20
SQL> col USR for a10
SQL> col sql_redo for a35
SQL> col SQL_UNDO for a35
SQL> SELECT SEG_OWNER,
2 SEG_NAME,
3 TABLE_NAME, USERNAME AS usr,SQL_REDO,SQL_UNDO,operation FROM
4 V$LOGMNR_CONTENTS
5 ;
SEG_OWNER SEG_NAME TABLE_NAME USR SQL_REDO SQL_UNDO OPERATION
---------- -------------------- -------------------- ---------- ----------------------------------- ----------------------------------- --------------------------------
UNKNOWN set transaction read write; START
SYS SVCOBJ_ACCESS_ATTR$ SVCOBJ_ACCESS_ATTR$ UNKNOWN Unsupported Unsupported UNSUPPORTED
UNKNOWN commit; COMMIT
UNKNOWN set transaction read write; START
TEST1 T2 T2 UNKNOWN Unsupported Unsupported UNSUPPORTED
UNKNOWN commit; COMMIT
6 rows selected.
可以看出再没开启最小附加日志的情况下，在日志中挖不出来update 相关的操作。
session 2
SQL> alter database add supplemental log data;
Database altered.
session 1:
SQL> update t2 set CONS_NAME='ccccc' where CONS_NO='5';
1 row updated.
SQL> commit;
Commit complete.
session 2
SQL> alter system switch logfile;
System altered.
SQL> SELECT NAME, SEQUENCE# FROM V$ARCHIVED_LOG WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
NAME SEQUENCE#
-------------------------------------------------------------------------------- ----------
+DATADG/ORA19C/ARCHIVELOG/2022_08_19/thread_1_seq_35.292.1113135051 35
session 7:
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DATADG/ORA19C/ARCHIVELOG/2022_08_19/thread_1_seq_35.292.1113135051',OPTIONS => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(-
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
> > DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
> DBMS_LOGMNR.PRINT_PRETTY_SQL);
PL/SQL procedure successfully completed.
SQL> set linesize 300 pagesize 9999
SQL> col seg_owner for a10
SQL> col seg_name for a20
SQL> col table_name for a20
SQL> col USR for a10
SQL> col sql_redo for a35
SQL> col SQL_UNDO for a35
SQL> SELECT SEG_OWNER,
2 SEG_NAME,
3 TABLE_NAME, USERNAME AS usr,SQL_REDO,SQL_UNDO,operation FROM
4 V$LOGMNR_CONTENTS WHERE SEG_OWNER ='TEST1';
SEG_OWNER SEG_NAME TABLE_NAME USR SQL_REDO SQL_UNDO OPERATION
---------- -------------------- -------------------- ---------- ----------------------------------- ----------------------------------- --------------------------------
TEST1 T2 T2 TEST1 update "TEST1"."T2" update "TEST1"."T2" UPDATE
set set
"CONS_NAME" = 'ccccc' "CONS_NAME" = 'aaaaa'
where where
"CONS_NAME" = 'aaaaa' and "CONS_NAME" = 'ccccc' and
ROWID = 'AAAFsAAAOAAAACLAAE'; ROWID = 'AAAFsAAAOAAAACLAAE';
开启最小附加日志后，可以挖掘出update 相关操作。
综上所述，在19c oracle rac环境中，开启了归档，logminer使用在线数据字典的情况下：
不开启附加日志（生产库大多数情况下，除非ogg，dsg等逻辑同步软件使用），delete，insert，truncate，drop，create 操作都能挖掘到，唯一挖掘不到的就是update。
在开启附加日志的时候，所有DML和DDL操作都能挖掘到。
至于真的能不能挖掘到，在实际当中，还是要实际测试下才算结果。
事后审计
先来张3种数据字典适用场景：
故障现象
oracle 19c rac 2节点，客户早上反馈，有人清除了某张核心表的数据，现在需要追责。客户本身也懂一些数据库知识，告诉了他可以用logminer 来进行一些挖掘，他就进行了一系列的操作，但是最终结果是没有挖掘出任何有用的信息，所以来求助。他挖掘的一些基本操作如下：
问题分析
1、为何没有挖掘出来？
客户环境和我们上面的模拟环境类似，都是没打开附加日志。但是我们已经证明，是可以挖掘出来的。
添加的日志不够完整，不包含误操作时的日期？据客户反馈，已经添加了误操作前后近一天的日志，应该是添加完全了。
注意上面客户写的查询语句，它里面只包含了delete和truncate。没包含其他drop 等ddl操作。
2、分析到底是drop了数据？truncate了数据？还是delete了数据？
这里用到了一个技巧，之前我也没有想到。
查询一些对象信息和一些关于删除的sql。
1. check dict info
set line 200
col segment_name for a30
col owner for a10
select obj#,dataobj#,owner#,name from obj$ where name ='&tab_name';
select owner,segment_name,partition_name,segment_type,file_id,BLOCKS from dba_extents where segment_name='&tab_name';
2. check sqltext info
select sql_id,dbms_lob.substr(s.sql_text)
from dba_hist_sqltext s
where upper(s.sql_text) like '%TEST%'
and (
upper(sql_text) like '%DROP%'
or upper(sql_text) like '%TRUNCATE%'
or upper(sql_text) like '%DELETE%'
);
select distinct sql_id,sql_text from v$sqltext t where upper(t.sql_text) like '%tab_name%'
and (
upper(sql_text) like '%DROP%'
or upper(sql_text) like '%TRUNCATE%'
or upper(sql_text) like '%DELETE%'
);
obj.obj# 和obj.dataobj# 字段的值相同，所以不会是truncate table。因为truncate table，dataobj#的值会改变。
obj# 和dataobj#值是一致的，所以这个表数据被清是以下两种情况
情况一 DDL清空数据： DROP后重建的表
情况二 dml清空数据：所有数据被delete掉了。
从历史awr和内存中查询相关删除的sql语句，根本就没有。
3、查询ddl 信息
select ts.OWNER,ts.OBJECT_NAME,ts.LAST_DDL_TIME from dba_objects ts where ts.object_name='' and ts.owner='';
这个表是15-AUG-2022 09：26：24新创建的。以前的数据应该是被drop掉了。用LOGMNR挖这个时间段，前后的几个日志，查找对这张表的所有操作，应该能找到操作记录。
再次确认：
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SET LINESIZE 300 PAGESIZE 9999
COL owner for a10
col object_name for a20
col SUBOBJECT_NAME for a20
col OBJECT_TYPE for a20
select ts.OWNER,ts.OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,ts.LAST_DDL_TIME,CREATED from dba_objects ts where ts.object_name='TEST' and ts.owner='HSQL';
分析结果：
- 这个表是15-AUG-2022 09:26:24新创建的。
- 然后在15-AUG-2022 09:31:41 做过ddl操作，应该是添加列操作。
问题1、加列的操作是在服务器上执行的，还是在pl/sql上执行的。
问题2、需要查看添加列的脚本是否有错误。
问题1 客户反馈是用PLSQL执行的，直接右键编辑表，添加字段，然后应用。
4、客户再次挖掘了一遍。添加了drop过滤条件。
这就显示出了有人重建了表。由于logminer的缺陷，username，machine， 这里还是unkown。
5、借助ASH或者AWR的信息，确定实际会话的信息，看能否找到相关执行会话的信息，它里面有详细的信息。
ASH:
select
to_char(sample_time,'yyyymmdd hh24:mi:ss') s_date,
y.* from v$active_session_history y
where y.sample_time > to_date('202208150920','yyyymmddhh24mi')
and y.sample_time < to_date('202208150940','yyyymmddhh24mi');
AWR:
select
to_char(sample_time,'yyyymmdd hh24:mi:ss') s_date,
y.* from dba_hist_active_sess_history y
where y.sample_time > to_date('202208150920','yyyymmddhh24mi')
and y.sample_time < to_date('202208150940','yyyymmddhh24mi');
首选ASH的信息，因为里面10s采样，比较详细，但是客户经查询，已经冲刷掉了。AWR就不叫粗了，但是只能选择他了，让客户导出来，查找15-AUG-2022 09:26:24时间前后信息，
过滤session_type=forgroud,前台会话。发现如下信息：
结论：这个时间刚好能对应上。MACHINE：WorkGroup\WIN-G1ULLBAK31P，这台机器 20220815 09:26:21 使用 plsqldev.exe登陆数据库执行drop table操作。询问他人或者在监听日志中，也记录了，对应主机名的IP地址。
还有一点注意的，到底是哪个用户执行的，在awr中，只有用户id字段，必须自己和dba_users.username 对应。
DBA_HIST_ACTIVE_SESS_HISTORY.user_id: Oracle user identifier
v$active_session_histroy.user_id: USER_ID Oracle user identifier; maps to V$SESSION.USER#
v$session.user#: USER# Oracle user identifier
dba_users.user_id:USER_ID ID number of the user
$ORACLE_HOME/rdbms/admindcore.bsq
create table user$ /* user table */
( user# number not null, /* user identifier number */
上面的几个都表示用户id，在数据库中唯一。通常sys的id为0。如下，其他库的查询：
SQL> select user#,name from user$;
USER# NAME
---------- ------------------------------
0 SYS
尽然为sys用户。这个操作是用sys用户执行的，应该检查下，没有特殊的管理工作，不要用sys/system登陆。
6、据维护人员回忆
它是用pl/sql添加列的操作，应该是误点了重建表这个选项。导致在添加列的维护操作时，导致重建了表。
问题根因
- 这个表是15-AUG-2022 09:26:24新创建的。
- 然后在15-AUG-2022 09:31:41 做过ddl操作，应该是添加列操作。
3.由于维护人员自己的失误，选项没有看清楚，直接保存了。
问题建议
1、对数据库做维护操作一定要再三核对，挑选业务低峰期；
2、现场应尽早上线专门审计软件。
最后修改时间：2022-08-19 22:15:09
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
