日志挖掘
1. 引入
| 1. 引入 |
数据库日志挖掘的主要用途是通过归档日志挖掘有关某个时间点或某个LSN的SQL的操作，重构DDL或DML语句。同时引申出对故障跟踪，定位某些用户的误操作信息。
达梦数据库 目前只支持归档日志挖掘，并且日志挖掘的前提是数据库开启日志归档和物理逻辑日志。
物理逻辑操作日志：RLOG_APPEND_LOGIC 动态，系统级
按照特定格式存储的服务器的逻辑操作，专门用于DBMS_LOGMNR包挖掘获取数据库系统的历史执行语句。
当开启记录物理逻辑日志的功能时，这部分日志内容会被存储在重做日志文件中。取值：
0：不启用；
1：如果有主键列，记录UPDATE和DELETE操作时只包含主键列信息，若没有主键列则包含所有列信息；
2：不论是否有主键列，记录UPDATE和DELETE 操作时都包含所有列的信息；
3：记录UPDATE时包含更新列的信息以及ROWID，记录DELETE时只有ROWID。
系统表逻辑操作日志记录：RLOG_APPEND_SYSTAB_LOGIC 动态，系统级
启用RLOG_APPEND_LOGIC 后有效，取值范围 0、1。
记录所有表操作：RLOG_IGNORE_TABLE_SET
1: 开启记录所有表的物理逻辑日志。
0: 关闭记录所有表的物理逻辑日志。
记录某些表的物理逻辑日志，结合建表或修改表子选项ADD LOGIC
LOG。 例如： alter table fstest add logic log;
2. DBMS_LOGMNR包介绍
| 2. DBMS_LOGMNR包介绍 |
小贴士
日志挖掘过程中，V$LOGMNR_LOGS、V$LOGMNR_CONTENTS等视图上会产生分析数据，
这些数据存储在TEMP临时表空间上，会话断开或终止归档日志文件分析后，数据自动被清除。
START_LOGMNR
根据指定的模式和条件来开始某个会话上的 LOGMNR，一个会话上仅能 START 一个LOGMNR。
PROCEDURE START_LOGMNR (
STARTSCN IN BIGINT DEFAULT 0, -- 日志起始序列号。默认值0表示无限制。
ENDSCN IN BIGINT DEFAULT 0, -- 日志结束序列号。默认值0表示无限制。
STARTTIME IN DATETIME DEFAULT '1988/1/1', -- 日志起始时间。
ENDTIME IN DATETIME DEFAULT '2110/12/31', -- 日志结束时间。
DICTFILENAME IN VARCHAR DEFAULT '', -- 离线字典路径名，默认为空。
OPTIONS IN INTDEFAULT 0 -- 日志分析模式，按位或组合，即累加有效值。 -- 例： 2+16+64+2048=2130
);
# 注意：OPTIONS数被配置成其他模式（在线字典或者 REDO 日志抽取字典信息），此项不会生效。
END_LOGMNR
结束日志加载分析。
PROCEDURE END_LOGMNR();
ADD_LOGFILE
在日志列表中增加日志文件。
PROCEDURE ADD_LOGFILE (
LOGFILENAME IN VARCHAR, -- 增加的文件的全路径
OPTIONS IN INT DEFAULT ADDFILE
);
/*
OPTIONS可选配置参数：
NEW 结束当前LOGMNR（调用LOGMNR_END），并增加指定文件。
ADDFILE 在当前LOGMNR中增加日志文件。
REMOVE 从当前LOGMNR中去除一个日志文件（如果已经START，则不可移除）。
*/
REMOVE_LOGFILE
从日志列表中移除某个日志文件。
PROCEDURE REMOVE_LOGFILE (
LOGFILENAME IN VARCHAR -- 待移除的日志文件名
);
COLUMN_PRESENT
判断某列是否被包含在指定的一行逻辑记录中。
FUNCTION COLUMN_PRESENT (
SQL_REDO_UNDO IN BIGINT, --redo/undo记录唯一标识号。
COLUMN_NAME IN VARCHAR DEFAULT '' -- 模式名.表名.列名组成的字符串
) RETURN INT;
# 返回值：0表示该值不可以挖掘，1表示可以挖掘。
MINE_VALUE
以字符串的格式来获取某一条日志中包含的指定列的值。
FUNCTION MINE_VALUE(
SQL_REDO_UNDO IN BIGINT, --redo/undo记录唯一标识号。
-- 对应视图V$LOGMNR_CONTENTS的REDO_VALUE和UNDO_VALUE字段值。
COLUMN_NAME IN VARCHAR DEFAULT '') -- 模式名.表名.列名组成的字符串
) RETURN VARCHAR;
日志挖掘过程中涉及的视图：
V$LOGMNR_CONTENTS 日志挖掘后产生的分析日志内容。
V$ARCHIVED_LOG 归档日志文件列表。
V$ARCH_FILE 归档日志文件列表。
V$LOGMNR_LOGS 待分析的日志文件列表。
V$LOGMNR_PARAMETERS 显示当前会话START_LOGMNR启动日志文件分析的参数。
V$LOGMNR_DICTIONARY 数据字典
3. 实战案例
| 3. 实战案例 |
准备测试数据
drop table if exists t_tab;
CREATE TABLE t_tab(id int primary key, name varchar(20), num smallint);
insert into t_tab select level, dbms_random.string('X',10),dbms_random.value(0,100)
connect by level <=20;
commit;
select * from t_tab;
call checkpoint(100);
alter system switch logfile;
//
1 UE5T8R9HX7 95
2 BOFDR4KJH7 29
3 718IF7VBM1 30
4 USE6BDW3VH 75
5 C92AL9BTRY 6
6 OAPPMLJ9S2 73
7 7CK0R5C2EE 47
8 7NV2GU5U31 57
9 NAQYHKRJKJ 75
10 5LJ6B3DWYJ 24
11 F41HERH33U 70
12 VFB19ML51C 63
13 XB9NPF2THK 38
14 A9CDC8TJA3 33
15 3CC5V4445S 16
16 0BQS1SE8FU 7
17 76EJYEF3IV 44
18 V0HP7H9YY3 3
19 RAF1XA36NL 12
20 TKJAN77OXF 33
//
-- 备份数据库
backup database full to "db_full_20210615" backupset '/dmdata/bak/db_full_20210615';
select sf_bakset_backup_dir_add('disk', '/dmdata/bak');
select * from v$backupset_search_dirs;
select parent_id,backup_id,backup_name,backup_path,begin_lsn from v$backupset;
select * from v$backupset;
//
-- 模拟多次修改t_tab表内容
-- 第一版本
set time zone '+8:00';
select current_timestamp;
update t_tab set num=100 where id <= 10;
commit;
select db_magic,next_seq,ckpt_lsn,file_lsn,cur_lsn,flush_lsn from v$rlog;
alter system switch logfile;
-- 第二版本
set time zone '+8:00';
select current_timestamp;
insert into t_tab(id,name,num) values(21, 'AAAAAAA', 88);
insert into t_tab(id,name,num) values(22, 'BBBBBBB', 88);
commit;
select db_magic,next_seq,ckpt_lsn,file_lsn,cur_lsn,flush_lsn from v$rlog;
alter system switch logfile;
-- 第三版本
set time zone '+8:00';
select current_timestamp;
delete from t_tab where id > 10;
commit;
select db_magic,next_seq,ckpt_lsn,file_lsn,cur_lsn,flush_lsn from v$rlog;
alter system switch logfile;
-- 第四版本
set time zone '+8:00';
select current_timestamp;
drop table t_tab;
select db_magic,next_seq,ckpt_lsn,file_lsn,cur_lsn,flush_lsn from v$rlog;
alter system switch logfile;
-- 报错：无效表或视图
select * from t_tab;
3.1 开启归档日志和逻辑日志
-- 归档日志
ALTER DATABASE MOUNT;
ALTER DATABASE ADD ARCHIVELOG 'TYPE=LOCAL, DEST=/dmdata/arch, FILE_SIZE=256, SPACE_LIMIT=20480';
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
SELECT ARCH_MODE FROM V$DATABASE;
-- 逻辑日志
-- 0 表示修改内存中的动态的配置参数值。
-- 1 修改内存和 INI 文件中动态的配置参数值。
-- 2 修改INI文件中的静态配置参数值和动态配置参数值。
CALL SF_SET_SYSTEM_PARA_VALUE('RLOG_APPEND_LOGIC',1,0,1)
SELECT * FROM V$PARAMETER WHERE NAME IN ('RLOG_APPEND_LOGIC', 'ARCH_INI');
ID NAME TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION
385 RLOG_APPEND_LOGIC SYS 1 1 1 Type of logic records in redo logs
564 ARCH_INI SYS 1 1 1 dmarch.ini
3.2 创建DBMS_LOGMNR包
begin
if SF_CHECK_SYSTEM_PACKAGE('DBMS_LOGMNR') = 0 then
SP_CREATE_SYSTEM_PACKAGES (1, 'DBMS_LOGMNR');
end if;
end;
/
sp_set_para_value(2,'CALC_AS_DECIMAL',1); -- 除法运算保留小数。 重启库生效
3.3 估计限定获取某个时间段内的归档日志文件
-- 简化操作：批量产生待分析的日志文件列表
create or replace procedure sp_load_logmnr_list (
start_time in timestamp default sysdate - 60/1440,
end_time in timestamp default sysdate
)
as
v_stime timestamp default sysdate;
v_etime timestamp default sysdate;
begin
for i in (select name,first_time,next_time from v$archived_log order by sequence#)
loop
select i.first_time,i.next_time into v_stime,v_etime;
if v_stime >= start_time and v_stime < end_time then
call dbms_logmnr.add_logfile(i.name);
elseif v_etime >= start_time and v_stime < end_time then
call dbms_logmnr.add_logfile(i.name);
end if;
end loop;
end;
/
3.4 日志挖掘过程
### 1. 添加待分析的归档日志文件
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
set time zone '+8:00';
call sp_load_logmnr_list('2021-06-15 19:00:00', sysdate);
SELECT LOW_SCN,NEXT_SCN, LOW_TIME, HIGH_TIME, LOG_ID, FILENAME FROM V$LOGMNR_LOGS;
### 2. 启动日志挖掘
call dbms_logmnr.start_logmnr(OPTIONS=>2128,STARTTIME=>'2021-06-15 19:00:00' ,ENDTIME=>sysdate);
### 3. 筛选查看有效的操作记录（找出误操作的时间点）
select
TIMESTAMP ,
START_TIMESTAMP ,
COMMIT_TIMESTAMP,
OPERATION ,
OPERATION_CODE ,
ROLL_BACK ,
TABLE_NAME ,
ROW_ID ,
USERNAME ,
DATA_OBJ# ,
DATA_OBJV# ,
SQL_REDO ,
REDO_VALUE ,
UNDO_VALUE ,
SESSION_INFO
from
V$LOGMNR_CONTENTS;
### 4. 结束日志挖掘
call dbms_logmnr.end_logmnr();
浓缩3.3和3.4步骤：
create or replace procedure sp_analyze_log (
-- start_time in timestamp default sysdate - 60/1440,
start_time in timestamp default sysdate - 0.041,
end_time in timestamp default sysdate,
criteria varchar(1024) default ''
)
as
v_stime timestamp default sysdate;
v_etime timestamp default sysdate;
v_slsn bigint :=0;
v_elsn bigint :=0;
v_sql varchar(2048);
begin
for i in (select name,first_time,next_time from v$archived_log order by sequence#)
loop
select i.first_time,i.next_time into v_stime,v_etime;
if v_stime >= start_time and v_stime < end_time then
call dbms_logmnr.add_logfile(i.name);
elseif v_etime >= start_time and v_stime < end_time then
call dbms_logmnr.add_logfile(i.name);
end if;
end loop;
call dbms_logmnr.start_logmnr(options=>2128, starttime=>start_time, endtime=>end_time);
if criteria <> '' then
v_sql := 'create table tmp_logmnr nologging as select * from v$logmnr_contents where 1=1 and '||criteria||';';
execute immediate v_sql;
else
v_sql := 'create table tmp_logmnr nologging as select * from v$logmnr_contents;';
execute immediate v_sql;
end if;
print 'please check out the tmp_logmnr in current schema.';
call dbms_logmnr.end_logmnr();
end;
/
/
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
set time zone '+8:00';
drop table if exists TMP_LOGMNR;
call sp_analyze_log('2021-06-15 19:00:00', sysdate);
-- call sp_analyze_log(to_date('2021-06-15 19:00:00','YYYY-MM-DD HH24:MI:SS'), sysdate, 'OPERATION_CODE=5');
select
TIMESTAMP ,
START_TIMESTAMP ,
COMMIT_TIMESTAMP,
OPERATION ,
OPERATION_CODE ,
ROLL_BACK ,
TABLE_NAME ,
ROW_ID ,
USERNAME ,
DATA_OBJ# ,
DATA_OBJV# ,
SQL_REDO ,
REDO_VALUE ,
UNDO_VALUE ,
SESSION_INFO
from tmp_logmnr where table_name='T_TAB';
### 操作类型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
*/
3.5 找一台测试机做不完全恢复，恢复T_TAB表
-- 随便初始化一个库，做恢复的中间库
dminit path=/dmdata/data db_name=RCVDB
-- 源库查看备份集和归档日志
select sf_bakset_backup_dir_add('disk', '/dmdata/bak');
select * from v$backupset_search_dirs;
select parent_id,backup_id,backup_name,backup_path,begin_lsn,end_lsn
from v$backupset;
select status,arch_lsn,path from v$arch_file where arch_lsn between 61211 and 61525;
-- 物理磁盘上查找备份文件，进入数据库不完全恢复
[dmdba@dmdb bin]$ ls -lh /dmdata/bak
total 12K
drwxr-xr-x 2 dmdba dinstall 4.0K Jun 15 18:37 db_full_20210615
drwxr-xr-x 2 dmdba dinstall 4.0K Jun 15 23:00 DB_ZFQ_INCREMENT_2021_06_15_23_00_49
drwxr-xr-x 2 dmdba dinstall 4.0K Jun 3 20:03 full_0603
[dmdba@dmdb bin]$ dmrman ctlstmt="restore database '/dmdata/data/RCVDB/dm.ini' from backupset '/dmdata/bak/db_full_20210615'"
dmrman V8
restore database '/dmdata/data/RCVDB/dm.ini' from backupset '/dmdata/bak/db_full_20210615'
file dm.key not found, use default license!
RESTORE DATABASE CHECK......
RESTORE DATABASE,data collect......
RESTORE DATABASE,database refresh ......
RESTORE BACKUPSET [/dmdata/bak/db_full_20210615] START......
total 4 packages processed...
total 8 packages processed...
RESTORE DATABASE,UPDATE ctl file......
RESTORE DATABASE,REBUILD key file......
RESTORE DATABASE,CHECK db info......
RESTORE DATABASE,UPDATE db info......
total 8 packages processed...
total 8 packages processed!
CMD END.CODE:[0]
restore successfully.
time used: 00:00:01.000
[dmdba@dmdb bin]$ dmrman ctlstmt="recover database '/dmdata/data/RCVDB/dm.ini' with archivedir '/dmdata/arch' until lsn 61524"
dmrman V8
recover database '/dmdata/data/RCVDB/dm.ini' with archivedir '/dmdata/arch' until lsn 61524
file dm.key not found, use default license!
Database mode = 0, oguid = 0
EP[0]'s cur_lsn[61211]
EP:0 total 22 pkgs applied, percent: 10%
EP[0]'s apply_lsn[61524] >= end_lsn[61309]
recover successfully!
time used: 747.909(ms)
[dmdba@dmdb bin]$ dmrman ctlstmt="recover database '/dmdata/data/RCVDB/dm.ini' update db_magic"
dmrman V8
recover database '/dmdata/data/RCVDB/dm.ini' update db_magic
file dm.key not found, use default license!
Database mode = 0, oguid = 0
EP[0]'s cur_lsn[61524]
EP[0]'s apply_lsn[61524] >= end_lsn[61309]
recover successfully!
time used: 00:00:01.045
[dmdba@dmdb bin]$ dmserver /dmdata/data/RCVDB/dm.ini mount
file dm.key not found, use default license!
version info: develop
Use normal os_malloc instead of HugeTLB
Use normal os_malloc instead of HugeTLB
DM Database Server x64 V8 1-1-126-20.09.04-126608-ENT startup...
Database mode = 0, oguid = 0
License will expire on 2021-09-04
file lsn: 61524
ndct db load finished
ndct fill fast pool finished
nsvr_startup end.
aud sys init success.
aud rt sys init success.
systables desc init success.
ndct_db_load_info success.
SYSTEM IS READY.
[dmdba@dmdb bin]$ dmrman ctlstmt="restore database '/dmdata/data/RCVDB/dm.ini' from backupset '/dmdata/bak/db_full_20210615'"
dmrman V8
restore database '/dmdata/data/RCVDB/dm.ini' from backupset '/dmdata/bak/db_full_20210615'
file dm.key not found, use default license!
RESTORE DATABASE CHECK......
RESTORE DATABASE,data collect......
RESTORE DATABASE,database refresh ......
RESTORE BACKUPSET [/dmdata/bak/db_full_20210615] START......
total 4 packages processed...
total 8 packages processed...
RESTORE DATABASE,UPDATE ctl file......
RESTORE DATABASE,REBUILD key file......
RESTORE DATABASE,CHECK db info......
RESTORE DATABASE,UPDATE db info......
total 8 packages processed...
total 8 packages processed!
CMD END.CODE:[0]
restore successfully.
time used: 00:00:01.000
[dmdba@dmdb bin]$ dmrman ctlstmt="recover database '/dmdata/data/RCVDB/dm.ini' with archivedir '/dmdata/arch' until lsn 61524"
dmrman V8
recover database '/dmdata/data/RCVDB/dm.ini' with archivedir '/dmdata/arch' until lsn 61524
file dm.key not found, use default license!
Database mode = 0, oguid = 0
EP[0]'s cur_lsn[61211]
EP:0 total 22 pkgs applied, percent: 10%
EP[0]'s apply_lsn[61524] >= end_lsn[61309]
recover successfully!
time used: 747.909(ms)
[dmdba@dmdb bin]$ dmrman ctlstmt="recover database '/dmdata/data/RCVDB/dm.ini' update db_magic"
dmrman V8
recover database '/dmdata/data/RCVDB/dm.ini' update db_magic
file dm.key not found, use default license!
Database mode = 0, oguid = 0
EP[0]'s cur_lsn[61524]
EP[0]'s apply_lsn[61524] >= end_lsn[61309]
recover successfully!
time used: 00:00:01.045
[dmdba@dmdb bin]$ dmserver /dmdata/data/RCVDB/dm.ini mount
file dm.key not found, use default license!
version info: develop
Use normal os_malloc instead of HugeTLB
Use normal os_malloc instead of HugeTLB
DM Database Server x64 V8 1-1-126-20.09.04-126608-ENT startup...
Database mode = 0, oguid = 0
License will expire on 2021-09-04
file lsn: 61524
ndct db load finished
ndct fill fast pool finished
nsvr_startup end.
aud sys init success.
aud rt sys init success.
systables desc init success.
ndct_db_load_info success.
SYSTEM IS READY.
[dmdba@dmdb bin]$ dmrman ctlstmt="restore database '/dmdata/data/RCVDB/dm.ini' from backupset '/dmdata/bak/db_full_20210615'"
dmrman V8
restore database '/dmdata/data/RCVDB/dm.ini' from backupset '/dmdata/bak/db_full_20210615'
file dm.key not found, use default license!
RESTORE DATABASE CHECK......
RESTORE DATABASE,data collect......
RESTORE DATABASE,database refresh ......
RESTORE BACKUPSET [/dmdata/bak/db_full_20210615] START......
total 4 packages processed...
total 8 packages processed...
RESTORE DATABASE,UPDATE ctl file......
RESTORE DATABASE,REBUILD key file......
RESTORE DATABASE,CHECK db info......
RESTORE DATABASE,UPDATE db info......
total 8 packages processed...
total 8 packages processed!
CMD END.CODE:[0]
restore successfully.
time used: 00:00:01.000
[dmdba@dmdb bin]$ dmrman ctlstmt="recover database '/dmdata/data/RCVDB/dm.ini' with archivedir '/dmdata/arch' until lsn 61524"
dmrman V8
recover database '/dmdata/data/RCVDB/dm.ini' with archivedir '/dmdata/arch' until lsn 61524
file dm.key not found, use default license!
Database mode = 0, oguid = 0
EP[0]'s cur_lsn[61211]
EP:0 total 22 pkgs applied, percent: 10%
EP[0]'s apply_lsn[61524] >= end_lsn[61309]
recover successfully!
time used: 747.909(ms)
[dmdba@dmdb bin]$ dmrman ctlstmt="recover database '/dmdata/data/RCVDB/dm.ini' update db_magic"
dmrman V8
recover database '/dmdata/data/RCVDB/dm.ini' update db_magic
file dm.key not found, use default license!
Database mode = 0, oguid = 0
EP[0]'s cur_lsn[61524]
EP[0]'s apply_lsn[61524] >= end_lsn[61309]
recover successfully!
time used: 00:00:01.045
[dmdba@dmdb bin]$ dmserver /dmdata/data/RCVDB/dm.ini mount
file dm.key not found, use default license!
version info: develop
Use normal os_malloc instead of HugeTLB
Use normal os_malloc instead of HugeTLB
DM Database Server x64 V8 1-1-126-20.09.04-126608-ENT startup...
Database mode = 0, oguid = 0
License will expire on 2021-09-04
file lsn: 61524
ndct db load finished
ndct fill fast pool finished
nsvr_startup end.
aud sys init success.
aud rt sys init success.
systables desc init success.
ndct_db_load_info success.
SYSTEM IS READY.
.......
3.6 导出测试库再导入源库
[dmdba@dmdb ~]$ dexp SYSDBA/SYSDBA@LOCALHOST:5238 tables=SYSDBA.T_TAB directory=/dmdata file=T_TAB.dmp log=T_TAB.log
dexp V8
[WARNING]FILE "/dmdata/T_TAB.dmp" has already existed
whether to overwrite(y/n, 1/0):y
[WARNING]FILE "/dmdata/T_TAB.log" has already existed
whether to overwrite(y/n, 1/0):y
---- [2021-06-16 00:30:48]export table:T_TAB -----
the privilege of the object at the export mode...
table :T_TAB export terminate, total export 11 rows
all the export process spent total 0.255 s
terminate export success without warning
[dmdba@dmdb ~]$
## 导入源库
[dmdba@dmdb ~]$ dimp SYSDBA/SYSDBA@LOCALHOST:5236 tables=SYSDBA.T_TAB directory=/dmdata file=T_TAB.dmp log=T_TAB2.log
dimp V8
----- [2021-06-16 00:31:38]import table:T_TAB -----
create table T_TAB
import table T_TAB , has coped with 11 rows
all the import process spent total 0.041 s
terminate import success without warning
451
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
