目录
1、配置源数据库
1.1检查归档
select para_name, para_value from v$dm_ini where
para_name in ('ARCH_INI','RLOG_APPEND_LOGIC');
1.1.1开启归档
alter database mount;
alter database add archivelog 'dest=/dmarch,type=local,file_size=500,space_limit=10240';
alter database archivelog;
alter database open;
1.2.开启记录逻辑操作
修改dm.ini参数
设置RLOG_APPEND_LOGIC=1
1.3源端模拟数据操作:
create user DIAG identified by "DIAG1234567";
grant dba to DIAG;
CREATE TABLE DIAG.T_LOG_TEST (ID INT,NAME VARCHAR(50)) ;
INSERT INTO DIAG.T_LOG_TEST VALUES(1,'LISyu');
INSERT INTO DIAG.T_LOG_TEST VALUES(2,'BAbY');
INSERT INTO DIAG.T_LOG_TEST VALUES(3,'liscj');
INSERT INTO DIAG.T_LOG_TEST VALUES(4,'ksiIMEI');
INSERT INTO DIAG.T_LOG_TEST VALUES(5,'wacakI');
INSERT INTO DIAG.T_LOG_TEST VALUES(6,'vff');
UPDATE DIAG.T_LOG_TEST SET ID = ID + 10 WHERE ID <10;
COMMIT;
执行日志刷盘
alter system switch logfile;
2.配置本地数据库
2.1在本机上新建实例
/dm8/bin/dminit PATH='/dm8/TEST' INSTANCE_NAME='TEST' DB_NAME='TEST' PORT_NUM=5238
2.2前台启动数据库
[dmdba@DM8 bin]$ dmserver /dm8/TEST/TEST/dm.ini
2.3检查并配置归档
select para_name, para_value from v$dm_ini where para_name in ('ARCH_INI','RLOG_APPEND_LOGIC');
alter database mount;
alter database add archivelog 'dest=/dm8/TEST/arch,type=local,
file_size=500,space_limit=10240';
alter database archivelog;
alter database open;
2.4开启记录逻辑操作
修改dm.ini参数
设置RLOG_APPEND_LOGIC=1
2.5查询本地魔数
select db_magic from v$ARCH_FILE;
SELECT DB_MAGIC FROM V$RLOG;
3.分析归档
3.1将源端归档日志放到本地
3.2修改归档日志魔数
./dmmdf TYPE=2 FILE="/dm8/TEST/ARCHIVE_LOCAL2_0x3D2F9188[0]_2021-09-15_17-57-55.log"
./dmmdf TYPE=2 FILE="/dm8/TEST/ARCHIVE_LOCAL2_0x3D2F9188[0]_2021-09-15_18-00-52.log";
./dmmdf TYPE=2 FILE="/dm8/TEST/ARCHIVE_LOCAL2_0x3D2F9188[0]_2021-09-15_18-10-37.log"
Please input the num which one you want to change, q to quit: 6
Input the new value: 1129007586
3.3添加归档日志文件到本地库
SQL> DBMS_LOGMNR.ADD_LOGFILE('/dm8/TEST/ARCHIVE_LOCAL2_0x3D2F9188[0]_2021-09-15_17-57-55.log');
DMSQL 过程已成功完成
已用时间: 4.275(毫秒). 执行号:502.
SQL> DBMS_LOGMNR.ADD_LOGFILE('/dm8/TEST/ARCHIVE_LOCAL2_0x3D2F9188[0]_2021-09-15_18-00-52.log');
DMSQL 过程已成功完成
已用时间: 0.851(毫秒). 执行号:503.
SQL> DBMS_LOGMNR.ADD_LOGFILE('/dm8/TEST/ARCHIVE_LOCAL2_0x3D2F9188[0]_2021-09-15_18-10-37.log');
DMSQL 过程已成功完成
已用时间: 0.704(毫秒). 执行号:504.
3.4查询当前已添加到分析列表的所有归档日志
select low_scn, next_scn, low_time, high_time, log_id, filename from v$logmnr_logs;
3.5启动归档日志文件分析
DBMS_LOGMNR.START_LOGMNR(OPTIONS=>2130)
执行ADD_LOGFILE添加日志文件后,需要调用START_LOGMNR过程启动归档日志分析,在调用时可指定START_SCN, END_SCN, START_DATE, END_DATE, OPTIONS等参数。
3.6查看分析结果
select * from v$logmnr_contents ;
SELECT OPERATION_CODE , OPERATION, SCN, SQL_REDO, TIMESTAMP ,SEG_OWNER, TABLE_NAME FROM V$LOGMNR_CONTENTS WHERE TABLE_NAME is not null;
停止分析
DBMS_LOGMNR.END_LOGMNR();
.关闭会话窗口或执行 DBMS_LOGMNR.END_LOGMNR () 即停止数据挖掘。
更多达梦咨讯请关注