dbms_logmnr 線上挖歸檔 恢復資料

xiexingzhi發表於2012-11-05
需求確認:
A.刪除資料的表:USERNAME.TABLE_NAME
   
B.執行刪除操作的使用者:USERNAME

C.執行操作的時間範圍:
   4月1日10:11左右

操作步驟:
1.查詢指定時間點上的歸檔
select thread#,SEQUENCE#,FIRST_TIME, NEXT_TIME from v$archived_log where to_char(first_time,'yyyy-mm-dd hh24:mi:ss')>'2011-04-01 10:00:00' and to_char(first_time,'yyyy-mm-dd hh24:mi:ss')
         1      41242 2011-04-01 10:07:16 2011-04-01 10:08:42
         1      41243 2011-04-01 10:08:42 2011-04-01 10:11:24
         1      41244 2011-04-01 10:11:24 2011-04-01 10:14:21
         1      41245 2011-04-01 10:14:21 2011-04-01 10:21:33
         1      41246 2011-04-01 10:21:33 2011-04-01 10:35:45
         2      58741 2011-04-01 10:04:55 2011-04-01 10:11:24
         2      58742 2011-04-01 10:11:24 2011-04-01 10:24:36
         2      58743 2011-04-01 10:24:36 2011-04-01 10:30:48

2.歸檔恢復,分別在兩個例項上進行
run{
  allocate channel t1 type sbt;
  restore archivelog from sequence 28692  until sequence 28693 thread=1;
  release channel t1;
}


run{
  allocate channel t1 type sbt;
  restore archivelog from sequence 79531  until sequence 79537 thread=2;
  release channel t1;

3.複製恢復出來的歸檔
  考慮到歸檔定期執行,建議將恢復出來的歸檔複製到其它檔案系統下

4.logmnr操作
execute dbms_logmnr.add_logfile(LOGFILENAME=>'/tmp/1_41242_707251512.arc',OPTIONS=>dbms_logmnr.new);
execute dbms_logmnr.add_logfile(LOGFILENAME=>'/tmp/1_41243_707251512.arc',OPTIONS=>dbms_logmnr.addfile);
.
.
.
DBMS_LOGMNR.START_LOGMNR(OPTIONS=>dbms_logmnr.dict_from_online_catalog);
為方便後面的查詢,建議按查詢結果生成表:
create table logmnr 
     as select a.seg_owner,a.seg_name,a.operation,a.TIMESTAMP,a.sql_redo,a.sql_undo 
       from v$logmnr_contents a where a.SEG_OWNER='XXX' and a.SEG_NAME='XXX' and a.OPERATION='DELETE';
create table logmnr 
     as select  *
       from v$logmnr_contents a where a.SEG_OWNER='XXX' and a.SEG_NAME='XXX' and a.OPERATION='DELETE';

建表方法二:
create table xxz_logmnr nologging as select * from v$logmnr_contents where 1=2;
create table xxz_logmnr nologging  TABLESPACE XXX as select * from v$logmnr_contents where 1=2;
insert /*+ append */ into xxz_logmnr select * from v$logmnr_contents;
做這一步之前注意將nls_date_format改成'yyyy-mm-dd hh24:mi:ss',不然中文亂碼,時間會變成問號。

查詢語句:

set lines 200
col seg_owner format a20
col seg_name format a30
set pages 400
select operation,count(operation),seg_owner,seg_name from  wudd_logmnr group by seg_owner,seg_name,operation order by count(operation);

Select  SQL_REDO, SQL_UNDO from wudd_logmnr where seg_owner=’XXX’;


5.生成臨時表
利用logmnr表中sql_undo中的記錄,生成臨時表,並交應用確認是否為刪除記錄。
create  table XXX as select * from  USERNAME.TABLE_NAME  where 1=2;    =>只要表結構。
執行SQL_UNDO到相應的表

6.結束logmnr
SQL> execute dbms_logmnr.end_logmnr;

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22531473/viewspace-748394/,如需轉載,請註明出處,否則將追究法律責任。

相關文章