logminer初体验

--1.开启supply log
 select supplemental_log_data_min from v$database;  
 alter database add supplemental log data;
 
 
 
 --2.测试
 create table t1 as select * from dba_tables;  
 select count(*) from t1;  
 select sysdate from dual; ----1632
 truncate table t1; 
 create table t2 (id number);
 insert into t2 values (1); 
 insert into t2 values (2);  
 commit;
 
 archive log list;---577
 alter system switch logfile; --688
 
 --3.恢复
 show parameter utl_file; 
 alter system set utl_file_dir='/home/oracle/utl_file_dir' scope=spfile;  
 mkdir -p /home/oracle/utl_file_dir 
 
 
 @?/rdbms/admin/dbmslms.sql;
 @?/rdbms/admin/dbmslm.sql;
  @?/rdbms/admin/dbmslmd.sql; 
 
 
  exec dbms_logmnr_d.build(dictionary_filename => 'V0715dict.ora',dictionary_location => '/home/oracle/utl_file_dir');  
  !file /home/oracle/utl_file_dir/V0715dict.ora
  !du -sh /home/oracle/utl_file_dir/V0715dict.ora  
  
  
  select member from v$logfile where type='ONLINE';  
  
begin
dbms_logmnr.add_logfile(logfilename => '/u01/oracle/app/oradata/rmsdb/redo01a.log',options => dbms_logmnr.new);  
end;


begin
dbms_logmnr.start_logmnr(dictfilename => '/home/oracle/utl_file_dir/V0715dict.ora',options => dbms_logmnr.ddl_dict_tracking);  
end;


--注意必须为同一个session下，可以通过创建表来保存出来
 select * from v$logmnr_contents; 
  create    table    system.logmnr_temp01   as    select    *    from GV$LOGMNR_CONTENTS; 
  
  drop table   system.logmnr_temp01
  select    *    from GV$LOGMNR_CONTENTS
   where table_name='t1'
  

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21302630/viewspace-1734326/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21302630/viewspace-1734326/