一、日志挖掘作用：
- 跟踪数据库的变化，可以离线地跟踪数据库的变化，而不会影响在线系统的性能
- 回退数据库的变化，回退特定的变化数据
- 优化和扩容计划，可通过分析日志文件中的数据以分析数据的增长模式
- 确定数据库的逻辑损坏时间，准确定位操作执行的时间和SCN
- 数据库审计
注意：反向恢复操作 sql_undo 暂不支持，需要通过 sql_redo 信息推断出 undo_sql
二、操作步骤
1. 确认原库开启归档
SQL> alter database mount;
executed successfully
used time: 00:00:03.193. Execute id is 0.
SQL> alter database add archivelog 'dest=/dmarch,type=local,file_size=1024,space_limit=30720';
executed successfully
used time: 40.660(ms). Execute id is 0.
SQL> alter database archivelog;
executed successfully
used time: 44.705(ms). Execute id is 0.
SQL> alter database open;
executed successfully
used time: 00:00:02.290. Execute id is 0.
SQL> select arch_mode from v$database;
LINEID ARCH_MODE
---------- ---------
1 Y
2. 配置参数
开启LOGMNR系统包
SQL> SP_CREATE_SYSTEM_PACKAGES(1,'DBMS_LOGMNR');
修改参数
SQL> alter system set 'RLOG_APPEND_LOGIC'=1 both;
SQL> select para_name,para_value from v$dm_ini where para_name='RLOG_APPEND_LOGIC';
LINEID PARA_NAME PARA_VALUE
---------- ----------------- ----------
1 RLOG_APPEND_LOGIC 1
参数说明
RLOG_APPEND_LOGIC：动态，系统级
是否启用在日志中记录逻辑操作的功能，取值范围 0、1、2、3
0：不启用；1、2、3 启用，默认值。
1：如果有主键列，记录 UPDATE 和 DELETE操作时只包含主键列信息，若没有主键列则包含所有列信息；
2：不论是否有主键列，记录UPDATE 和 DELETE 操作时都包含所有列的信息；
3：记录 UPDATE 时包含更新列的信息以及ROWID，记录 DELETE 时只有 ROWID；
注意：RLOG_APPEND_LOGIC 设置成 2，可以挖掘到更多的SQL结果，但是会造成日志量成倍增长，生产环境慎用
3. 模拟误操作场景
创建测试表，插入数据
SQL> create table t (id number,name varchar2(200),num number);
executed successfully
used time: 55.037(ms). Execute id is 18627.
SQL> begin
for i in 1..1000 loop
insert into t values (i,dbms_random.string('l',5),trunc(dbms_random.value(10000,99999),0));
end loop;
commit;
end;
/
误删除数据
SQL> delete from t where id=5;
affect rows 1
used time: 36.677(ms). Execute id is 18608.
SQL> delete from t where id=200;
affect rows 1
used time: 10.045(ms). Execute id is 18609.
SQL> delete from t where id=3;
affect rows 1
used time: 52.565(ms). Execute id is 18610.
SQL> commit;
executed successfully
used time: 85.977(ms). Execute id is 18611.
记录db_magic
SQL> select db_magic;
LINEID DB_MAGIC
---------- --------------------
1 1788273644
查看归档日志文件
SQL> select path,create_time,close_time,status from v$arch_file;
LINEID PATH CREATE_TIME CLOSE_TIME STATUS
---------- ----------------------------------------------------------- -------------------------- -------------------------- --------
1 /dmarch/ARCHIVE_LOCAL_0x6A96E3EC[0]_2021-06-17_11-02-18.log 2021-06-23 15:22:25.000000 2021-06-23 15:22:25.880765 INACTIVE
2 /dmarch/ARCHIVE_LOCAL_0x6A96E3EC[0]_2021-06-23_15-22-41.log 2021-06-23 15:22:25.880765 2021-06-24 14:15:08.964166 INACTIVE
3 /dmarch/ARCHIVE_LOCAL_0x6A96E3EC[0]_2021-06-24_14-15-08.log 2021-06-24 14:15:08.964166 2021-06-26 00:11:42.814000 INACTIVE
4 /dmarch/ARCHIVE_LOCAL_0x6A96E3EC[0]_2021-06-26_11-17-21.log 2021-06-26 00:11:42.814000 2021-06-26 11:17:21.079130 ACTIVE
used time: 67.198(ms). Execute id is 18619.
实际场景中可以根据误操作大概的时间段，选取原库的归档日志进行挖掘
4. 创建用于日志挖掘的实例
注意：此操作尽量不要在原库进行操作，会影响原库性能
[dmdba@gd01 ~]$ cd /dm8/bin
[dmdba@gd01 bin]$ ./dminit PATH=/dmdata/5236 CASE_SENSITIVE=1 PAGE_SIZE=32 EXTENT_SIZE=32 CHARSET=1 PORT_NUM=5236 DB_NAME=LOGMNRDB
使用dmmdf工具修改新实例SYSTEM.DBF和REDO日志的db_magic为原库的值：1788273644
[dmdba@gd01 bin]$ ./dmmdf TYPE=1 FILE=/dmdata/5236/LOGMNRDB/SYSTEM.DBF
dmmdf V8
**********************************************************
1 db_magic=319095845
2 next_trxid=1
3 pemnt_magic=1344460062
**********************************************************
Please input which parameter you want to change(1-3), q to quit: 1
Please input new value: 1788273644
**********************************************************
1 db_magic=1788273644
2 next_trxid=1
3 pemnt_magic=1344460062
**********************************************************
Do you want to save the change to file (y/n): y
Save to file success!
[dmdba@gd01 bin]$ ./dmmdf TYPE=2 FILE=/dmdata/5236/LOGMNRDB/LOGMNRDB01.log
dmmdf V8
**********************************************************
1 sig = DMRLOG
2 ver = 7006
3 chksum = -551362259
4 sta = 1
5 n_magic = 7
6 db_magic = 319095845
7 len = 268435456
8 free = 4096
9 clsn = 0
10 clsn_fil = 0
11 clsn_off = 4096
12 pemnt_magic = 1344460062
13 fil_id = 0
15 next_seq = 2457
16 g_next_seq = 2457
17 arch_lsn = 0
18 arch_seq = 0
19 dbversion = 0x7000c
20 min_exec_version = V8.1.1.1
21 min_dct_version = 4
22 p_db_magic = 0
23 n_apply_ep = 0
24 apply_info_lsn = 0
pkg_seq_arr: (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
apply_lsn_arr: (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
25 recv_p_db_magic = 0
26 recv_n_apply_ep = 0
recv_pkg_seq_arr: (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
recv_apply_lsn_arr: (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
**********************************************************
You can only reset sta(4) or db_magic (6) or clsn (9) or clsn_fil(10) or clsn_off(11) or pemnt_magic(12) or fil_id(13) or next_seq(15) or g_next_seq(16) or p_db_magic(20) or n_apply_ep(21).
Please input the num which one you want to change, q to quit: 6
Input the new value: 1788273644
**********************************************************
[dmdba@gd01 bin]$ ./dmmdf TYPE=2 FILE=/dmdata/5236/LOGMNRDB/LOGMNRDB02.log
dmmdf V8
**********************************************************
1 sig = DMRLOG
2 ver = 7006
3 chksum = -1071455960
4 sta = 0
5 n_magic = 7
6 db_magic = 319095845
7 len = 268435456
8 free = 4096
9 clsn = 0
10 clsn_fil = 0
11 clsn_off = 0
12 pemnt_magic = 1344460062
13 fil_id = 1
15 next_seq = 0
16 g_next_seq = 0
17 arch_lsn = 0
18 arch_seq = 0
19 dbversion = 0x7000c
20 min_exec_version = V8.1.1.1
21 min_dct_version = 4
22 p_db_magic = 0
23 n_apply_ep = 0
24 apply_info_lsn = 0
pkg_seq_arr: (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
apply_lsn_arr: (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
25 recv_p_db_magic = 0
26 recv_n_apply_ep = 0
recv_pkg_seq_arr: (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
recv_apply_lsn_arr: (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
**********************************************************
You can only reset sta(4) or db_magic (6) or clsn (9) or clsn_fil(10) or clsn_off(11) or pemnt_magic(12) or fil_id(13) or next_seq(15) or g_next_seq(16) or p_db_magic(20) or n_apply_ep(21).
Please input the num which one you want to change, q to quit: 6
Input the new value: 1788273644
**********************************************************
注意：新版达梦8除了修改数据文件和日志文件的db_magic，还需更新参数文件，否则启动实例会失败
[dmdba@gd01 bin]$ ./dmmdf TYPE=8 FILE=/dmdata/5236/LOGMNRDB/dm.ini
dmmdf V8
file /dmdata/5236/LOGMNRDB/LOGMNRDB01.log degrade success.
file /dmdata/5236/LOGMNRDB/LOGMNRDB02.log degrade success.
degrade rlog version to 0x7005 success.
degrade rlog version success!
启动实例
[dmdba@gd01 bin]$ ./dmserver /dmdata/5236/LOGMNRDB/dm.ini
5. 开始日志挖掘
添加归档日志文件
SQL> DBMS_LOGMNR.ADD_LOGFILE('/dmarch/ARCHIVE_LOCAL_0x6A96E3EC[0]_2021-06-24_14-15-08.log');
指定相关参数，启动日志挖掘
SQL> DBMS_LOGMNR.START_LOGMNR(OPTIONS=>2128 , STARTTIME=>TO_DATE('2021-06-26 00:00:00','YYYY-MM-DD HH24:MI:SS') , ENDTIME=>TO_DATE('2021-06-26 00:30:00','YYYY-MM-DD HH24:MI:SS'));
关于OPTIONS 参数：
提供如下表所列的可选模式，各模式可以通过 + 或者按位或来进行组合。
其它位的值，如 1、4、8 等目前不支持，配置后不会报错，但是没有效果。
例如，组合全部模式，则取值计算方法为 2+16+64+2048=2130，那么 OPTIONS 值取就是 2130。
查看日志挖掘结果，LogMiner通过V$LOGMNR_CONTENTS视图显示归档日志中的信息
SQL> select START_TIMESTAMP,COMMIT_TIMESTAMP,OPERATION,ROLL_BACK,TABLE_NAME,USERNAME,SQL_REDO,REDO_VALUE,UNDO_VALUE from V$LOGMNR_CONTENTS where operation = 'DELETE';
操作类型 OPERATION 和 OPERATION_CODE 分别为：
INTERNAL 0
INSERT 1
DELETE 2
UPDATE 3
BATCH_UPDATE 4
DDL 5
START 6
COMMIT 7
SEL_LOB_LOCATOR 9
LOB_WRITE 10
LOB_TRIM 11
SELECT_FOR_UPDATE 25
LOB_ERASE 28
MISSING_SCN 34
ROLLBACK 36
UNSUPPORTED 255
SEQ MODIFY 37
可以看到根据查询条件，已将模拟的误操作SQL查询到，这里的table是以ID形式出现，可以根据ID在原库中进行查询，再根据 REDO_SQL 反向推断出 UNDO_SQL，进行数据恢复
SQL> select object_name from dba_objects where object_id='1120';
LINEID OBJECT_NAME
---------- -----------
1 T
6. 停止归档日志挖掘
SQL> DBMS_LOGMNR.END_LOGMNR();
三、DBMS_LOGMNR包注意事项
- 目前 DBMS_LOGMNR 只支持对归档日志进行分析；
- DM MPP 环境下不支持 DBMS_LOGMNR 包；
更多达梦资讯请上达梦社区了解 https://eco.dameng.com
1314
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
