logmnr 的使用

来源：互联网发布：霍建华林心如形婚知乎编辑：程序博客网时间：2024/06/06 04:17

请先参照doc.1504755,上面有详细的英文介绍

今天在生产库上动刀了，说下生产库的状态，网优10g的RAC，归档切换的很快，大概4分钟一次，导致厂商备份很慢。然后日志又被他们的烂脚本给阉割了，咨询了下oracle原厂支持，说使用下logmnr。下面贴详细步骤：

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> @?/rdbms/admin/dbmslm.sql
SQL> desc dbms_logmnr
SQL> show parameter log_archive_dest
SQL> show parameter db_recovery_file_dest

SQL> execute dbms_logmnr.add_logfile( logfilename =>'/tmp/arch/1_62376_708033235.dbf',options => dbms_logmnr.new);
SQL> execute dbms_logmnr.add_logfile( logfilename =>'/tmp/arch/1_62377_708033235.dbf',options =>dbms_logmnr.addfile);
SQL> execute dbms_logmnr.start_logmnr( options =>dbms_logmnr.dict_from_online_catalog);
select sql_redo,sql_undo,username from v$LOGMNR_CONTENTS where rownum <=2;
select count(*) from v$LOGMNR_CONTENTS;

SQL>select username AS USR,(XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,
operation,
SQL_REDO,
SQL_UNDO
from v$LOGMNR_CONTENTS
where username in ('sys');

SQL>create table logmnr_contents_n1_20k tablespace FMDBS as select * from v$logmnr_contents where rownum <=20000;

SQL> execute dbms_logmnr.end_logmnr();

execute dbms_logmnr.add_logfile( options =>dbms_logmnr.removefile,logfilename =>'/tmp/arch/1_62376_708033235.dbf');
execute dbms_logmnr.add_logfile( options =>dbms_logmnr.removefile,logfilename =>'/tmp/arch/1_62377_708033235.dbf');

select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL from gv$database;

SQL> select name from v$archived_log where dictionary_begin='YES';
SQL> select name from v$archived_log where dictionary_end='YES';

0 0