达梦数据库日志挖掘（logminer）精简版

达梦数据库日志挖掘（logminer）精简版

一、基本步骤：

 SP_CREATE_SYSTEM_PACKAGES(1,'DBMS_LOGMNR');
 select para_name,para_value from v\$dm_ini where para_name like 'RLOG_APPEND_LOGIC';
 select name,first_time,next_time,first_change#,next_change# from v$archived_log;
 dbms_logmnr.add_logfile('/dm8/dmarch/ARCHIVE_LOCAL1_0x2418C4A9_EP0_2024-05-11_14-09-19.log');
 -- dbms_logmnr.remove_logfile(‘xxx’);
 dbms_logmnr.start_logmnr();
 select operation,operation_code,scn,sql_redo,sql_undo,timestamp,seg_owner,table_name from v$logmnr_contents;
 dbms_logmnr.end_logmnr();

二、简单解释

1、初始化LOGMNR包（首次挖掘需要执行）
SP_CREATE_SYSTEM_PACKAGES(1,‘DBMS_LOGMNR’);

2、 参数RLOG_APPEND_LOGIC=1 或2 （需要重启数据库）
select para_name,para_value from v$dm_ini where para_name like ‘RLOG_APPEND_LOGIC’;

3、 查找归档文件
select name,first_time,next_time,first_change#,next_change# from v$archived_log;

4、 添加需要分析的日志
dbms_logmnr.add_logfile(‘/dm8/dmarch/ARCHIVE_LOCAL1_0x2418C4A9_EP0_2024-05-11_14-09-19.log’);
移除不需要分析的日志
dbms_logmnr.remove_logfile(‘xxx’);
移除不需要分析的日志

5、 分析日志
dbms_logmnr.start_logmnr();

6、查看结果（量大加过滤条件）
select operation,operation_code,scn,sql_redo,sql_undo,timestamp,seg_owner,table_name from v$logmnr_contents;

7、结束日志挖掘
dbms_logmnr.end_logmnr();

三、执行截图

四、异库日志挖掘

生产环境需要异库挖掘日志的操作，使用dmmdf 命令行 修改欲挖掘归档日志DB_MAGIC到当前库的DB_MAGIC即可。
异库日志挖掘：
SQL> SP_CREATE_SYSTEM_PACKAGES(1,‘DBMS_LOGMNR’);
SQL>select para_name,para_value from v$dm_ini where para_name like ‘RLOG_APPEND_LOGIC’;
SQL>select name,first_time,next_time,first_change#,next_change# from v$archived_log;
SQL> dbms_logmnr.add_logfile(‘/dm8/dmarch/ARCHIVE_LOCAL1_0x2418C4A9_EP0_2024-05-11_14-09-19.log’);
SQL> dbms_logmnr.start_logmnr();
报错信息：
执行语句1:
dbms_logmnr.start_logmnr();
执行失败(语句1)
-712: 归档日志MAGIC错误
-712: DBMS_LOGMNR.START_LOGMNR line 15

1条语句执行失败

使用dmmdf 命令行 更新归档日志DB_MAGIC到当前库的DB_MAGIC：
SQL>select db_magic from v$rlog;
[dmdba@kylin101 bin]$ dmmdf TYPE=2 FILE=//dm8/dmarch/ARCHIVE_LOCAL1_0x2418C4A9_EP0_2024-05-11_14-09-19.log

SQL> dbms_logmnr.remove_logfile(‘/dm8/dmarch/ARCHIVE_LOCAL1_0x2418C4A9_EP0_2024-05-11_14-09-19.log’);
SQL> dbms_logmnr.add_logfile(‘/dm8/dmarch/ARCHIVE_LOCAL1_0x2418C4A9_EP0_2024-05-11_14-09-19.log’);
SQL>dbms_logmnr.start_logmnr();
SQL> select operation,operation_code,scn,sql_redo,sql_undo,timestamp,seg_owner,table_name from v$logmnr_contents;
SQL>-- dbms_logmnr.end_logmnr();

---

执行失败(语句1)
-712: 归档日志MAGIC错误
-712: DBMS_LOGMNR.START_LOGMNR line 15

1条语句执行失败

使用dmmdf 命令行 更新归档日志DB_MAGIC到当前库的DB_MAGIC：

[dmdba@kylin101 bin]$ disql SYSDBA/SYSDBA:1801

Server[LOCALHOST:1801]:mode is normal, state is open
login used time : 3.473(ms)
disql V8
SQL> select db_magic from v$rlog;

LINEID DB_MAGIC

---

1 1252150256

used time: 0.477(ms). Execute id is 701.
SQL>

[dmdba@kylin101 bin]$ dmmdf TYPE=2 FILE=//dm8/dmarch/ARCHIVE_LOCAL1_0x2418C4A9_EP0_2024-05-11_14-09-19.log
dmmdf V8

---

1 sig = DMALOG
2 ver = 7007
3 chksum = 964519259
4 sta = 1
5 n_magic = 0
6 db_magic = 605602985
7 len = 55918592
8 free = 55918592
9 clsn = 1674252
10 clsn_fil = 0
11 clsn_off = 4096
12 pemnt_magic = 284132058
13 fil_id = 0
15 next_seq = 15278
16 g_next_seq = 15278
17 arch_lsn = 1199821
18 arch_seq = 15130
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)
27 l_term_id = 0
28 term_id = 0
29 c_seqno = 0
30 c_lsn = 0
31 rpkg_crc_stand = 1

---

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(22) or n_apply_ep(23).
Please input the num which one you want to change, q to quit: 6
Input the new value: 1252150256

---

1 sig = DMALOG
2 ver = 7007
3 chksum = 1472719362
4 sta = 1
5 n_magic = 0
6 db_magic = 1252150256
7 len = 55918592
8 free = 55918592
9 clsn = 1674252
10 clsn_fil = 0
11 clsn_off = 4096
12 pemnt_magic = 284132058
13 fil_id = 0
15 next_seq = 15278
16 g_next_seq = 15278
17 arch_lsn = 1199821
18 arch_seq = 15130
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)
27 l_term_id = 0
28 term_id = 0
29 c_seqno = 0
30 c_lsn = 0
31 rpkg_crc_stand = 1

---

Do you want to quit and save the change to file (y/n): y
Save to file success!