需求确认：

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')<'2011-04-01 10:30:00' order by thread#,SEQUENCE#;

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博客

dbms_logmnr 在线挖归档恢复数据

最新文章

ITPub博客

dbms_logmnr 在线挖归档 恢复数据

最新文章

dbms_logmnr 在线挖归档恢复数据