log miner 19c最常用过程-凯发app官方网站

生产库上：
找到所需的归档日志
col name for a50
set pages 100 lin 120
select thread#,sequence#,name,to_char(completion_time,'yyyy-mm-dd hh24:mi:ss')riqi from v$archived_log where dest_id=1 and completion_time>sysdate-1 order by 1,2;
创建数据字典
create directory my_dictionary_dir as '/u01/app/oracle/oradata';
execute dbms_logmnr_d.build( dictionary_filename=>'dictionary.ora', dictionary_location=>'my_dictionary_dir', options => dbms_logmnr_d.store_in_flat_file);
将归档文件和数据字典scp到测试库上/home/oracle目录下
测试库上：
execute dbms_logmnr.add_logfile( logfilename => '/home/oracle/1_42_1088407163.dbf', options => dbms_logmnr.new);
execute dbms_logmnr.add_logfile( logfilename => '/home/oracle/1_43_1088407163.dbf', options => dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile( logfilename => '/home/oracle/2_35_1088407163.dbf', options => dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile( logfilename => '/home/oracle/2_36_1088407163.dbf', options => dbms_logmnr.addfile);
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
execute dbms_logmnr.start_logmnr( -
dictfilename => '/home/oracle/dictionary.ora', -
starttime => '2021-11-24 00:59:53', -
endtime => '2021-11-24 06:59:53'-
);

-- create table logminer1 tablespace users as

select username as usr, (xidusn || '.' || xidslt || '.' || xidsqn) as xid, sql_redo, sql_undo from v$logmnr_contents where seg_owner='a';
execute dbms_logmnr.end_logmnr;

如果需要的话可以将日志挖掘的结果放到一个表中，以便后期分析。

经初步测试，不开启最小附加日志，有可能无法挖掘出来。
所以，检查一下所有生产库：
select supplemental_log_data_min from v$database;
如果为 no
赶紧 alter database add supplemental log data;

参考： https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-logminer-utility.html#guid-3417b738-374c-4ee3-b15c-3a66e01ae2b5

阅读(880) | 评论(0) | 转发(0) |

上一篇：windows tns-12560 最大占用端口数

下一篇：dm8 达梦安装，简洁步骤版

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们