1. 测试数据准备
create table test(a number(5),c varchar2(20));
insert into test values(1,'lty');commit;
2. 设置数据挖掘表空间,如果挖掘日志不多可以占用users表空间:
create tablespace logmnrtps datafile '/home/dbfile/oradata/tstrisk1/logmnrtps.dbf' size 25M autoextend on next 25M maxsize unlimited;
BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE (
new_tablespace => 'logmnrtps'
);
END;
/
3. 以下与dictionary相关主要是为了映射log_contents中object id与表名的关系,可以不设置。
SELECT name, value FROM v$parameter WHERE name = 'utl_file_dir';
alter system set utl_file_dir='/home/dbfile/logmnr' scope=spfile;BEGIN
DBMS_LOGMNR_D.BUILD (
dictionary_filename => 'dictionary.ora',
dictionary_location => '/home/dbfile/logmnr',
options => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE
);
END;
/
4. 查询需要挖掘的日志:根据以下两个sql,查询需要挖掘的日志path(日志可以是redo,也可以是归档):
select GROUP#,sequence#,STATUS,FIRST_CHANGE#,to_char(FIRST_TIME,'yyyy/mm/dd:hh24:mi:ss') firsttime,NEXT_CHANGE#,to_char(NEXT_TIME,'yyyy/mm/dd:hh24:mi:ss') nexttime from V$log;
rman target /
list archivelog all;
BEGIN
DBMS_LOGMNR.ADD_LOGFILE (
LogFileName => '/home/dbfile/oradata/tstrisk1/redo05.rdo',
options => DBMS_LOGMNR.NEW
);
END;
/
BEGIN
DBMS_LOGMNR.START_LOGMNR (
DictFileName => '/home/dbfile/logmnr/dictionary.ora'
);
END;
/
SELECT
timestamp
, filename
, dictionary_scn
, NVL(info, 'VALID') as DICTIONARY_STATUS
FROM
v$logmnr_dictionary;
6. 将挖掘的内容查询,或者以create table as的形式保存下来:
SELECT
username
, operation
, sql_redo
, sql_undo
, timestamp
, scn
FROM
v$logmnr_contents
WHERE
operation='INSERT';
7. 关闭logmnr
BEGIN
DBMS_LOGMNR.END_LOGMNR();
END;
/