Home > oracle > 使用logmnr找回数据

使用logmnr找回数据

November 23rd, 2010 Leave a comment Go to comments

应用程序的误删除了线上的一条数据,需要恢复

删除时间:2010-11-08 00:11:21

1.首先考虑使用oracle的flashback query

select *
from users

as of scn
timestamp_to_scn(to_timestamp(‘22010-11-08 00:11:21’,
‘yyyy-mm-dd hh24:mi:ss’)
where tadget_id = 91742
and user_id = 224529394;

发现该数据提示已经找不到了(undo_retention: 1800)

2.使用logmnr,分析归档日志,来找出数据:

根据删除的大致时间,从V$archived_log中找出那个时间段的归档日志:
17:25:35 SYS@ misc> select * from (select name, FIRST_TIME
17:29:06 2 from V$archived_log
17:29:06 3 where FIRST_TIME >
17:29:06 4 to_timestamp(‘2010-11-08 00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’)
17:29:06 5 order by FIRST_TIME) where rownum<10;

NAME FIRST_TIME
——————– ——————-
misc_stb_cm3 2010-11-08 00:03:35
misc_stb_second 2010-11-08 00:03:35
/arc/archive/misc/1_ 2010-11-08 00:03:35
149148_663475111.arc

misc_stb_cm3 2010-11-08 00:13:36
misc_stb_second 2010-11-08 00:13:36
/arc/archive/misc/1_ 2010-11-08 00:13:36
149149_663475111.arc

misc_stb_cm3 2010-11-08 00:23:36
misc_stb_second 2010-11-08 00:23:36
/arc/archive/misc/1_ 2010-11-08 00:23
/arc/archive/misc/1_149148_663475111.arc

/arc/archive/misc/1_149149_663475111.arc

3.使用logmnr分析归档日志:

col scn for a20
col timestamp for a20
col owner for a20
col oper for a20
col redo for a20
col undo for a20

添加日志:exec sys.dbms_logmnr.add_logfile(LogFileName => ‘/arc/archive/misc/1_149149_663475111.arc’,Options => dbms_logmnr.new);
分析日志:exec sys.dbms_logmnr.start_logmnr(Options => sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG);

分析结果中信息不准确的话,添加其他日志进行分析,继续添加:exec sys.dbms_logmnr.add_logfile(LogFileName => ‘/arc/archive/misc/1_149148_663475111.arc’);
查询分析结果:
select t.scn scn, t.timestamp timestamp, t.seg_owner owner, t.operation oper, t.sql_redo redo, t.sql_undo undo
from V$logmnr_Contents t
where t.seg_name = upper(‘users’)
and sql_redo like ‘%91742%’;


9411510185912 2010-11-08 00:11:21 TOP
DELETE
delete from “USERS” where “ID” = ‘106889195’ and “TADGET_ID” =
‘91742’ and “USER_ID” = ‘224529394’ and “STATUS” = ‘1’ and “EXPIRY_DATE” = TO_DA
TE(‘2010-11-08 00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’) and “GMT_CREATE” = TO_DATE(‘2
010-10-08 16:54:39’, ‘yyyy-mm-dd hh24:mi:ss’) and “GMT_MODIFIED” = TO_DATE(‘2010
-10-08 16:54:39’, ‘yyyy-mm-dd hh24:mi:ss’) and “INSTANCE_ID” = ‘21937’ and “USER
_NICK” = ‘营店’ and ROWID = ‘AAAdGiAAKAAACyzABr’;
insert into  “USERS”(“ID”,”TADGET_ID”,”USER_ID”,”STATUS”,”EXPIRY
_DATE”,”GMT_CREATE”,”GMT_MODIFIED”,”INSTANCE_ID”,”USER_NICK”) values (‘106889195
‘,’91742′,’224529394′,’1’,TO_DATE(‘2010-11-08 00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’
),TO_DATE(‘2010-10-08 16:54:39’, ‘yyyy-mm-dd hh24:mi:ss’),TO_DATE(‘2010-10-08 16
:54:39’, ‘yyyy-mm-dd hh24:mi:ss’),’21937′,’营店’);

9411510185916 2010-11-08 00:11:21 TOP
INSERT
insert into  “USERS”(“ID”,”TADGET_ID”,”USER_ID”,”STATUS”,”EXPIRY
_DATE”,”GMT_CREATE”,”GMT_MODIFIED”,”INSTANCE_ID”,”USER_NICK”) values (‘109752386
‘,’91742′,’224529394′,’1’,TO_DATE(‘2010-12-08 00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’
),TO_DATE(‘2010-11-08 00:11:21’, ‘yyyy-mm-dd hh24:mi:ss’),TO_DATE(‘2010-11-08 00
:11:21’, ‘yyyy-mm-dd hh24:mi:ss’),’21937′,’一道关怀保健专营店’);
delete from  “USERS” where “ID” = ‘109752386’ and “TADGET_ID” =
‘91742’ and “USER_ID” = ‘224529394’ and “STATUS” = ‘1’ and “EXPIRY_DATE” = TO_DA
TE(‘2010-12-08 00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’) and “GMT_CREATE” = TO_DATE(‘2
010-11-08 00:11:21’, ‘yyyy-mm-dd hh24:mi:ss’) and “GMT_MODIFIED” = TO_DATE(‘2010
-11-08 00:11:21’, ‘yyyy-mm-dd hh24:mi:ss’) and “INSTANCE_ID” = ‘21937’ and “USER
_NICK” = ‘营店’ and ROWID = ‘AAAdGiAATAAACIjAA1’;

关闭分析的日志:exec sys.dbms_logmnr.end_logmnr;

从分析出的的日中,可以看到相应sql的undo和redo sql;

运行相应的sql,既可以恢复数据。

Categories: oracle Tags:
  1. No comments yet.
  1. No trackbacks yet.
You must be logged in to post a comment.