记一次Oracle数据恢复过程


事情的起因是,一个应用升级后,某一个操作导致一个表的几个列全部被更新为同一值(忍不住又要唠叨测试的重要性)。这样的错误居然出现在应用代码中,显然是重大的BUG。那个是罪魁祸首的SQL,UPDATE语句,其WHERE条件仅仅只有一个where 1=1。
系统的维护人员称是星期五出的错,发现出错是在星期天,也就是我恢复数据的日期,与声称的出错时间已经隔了将近2天。开始尝试用flashback query恢复数据,报ORA-01555错误,此路不通。维护人员说,星期五之前的RMAN备份已经被删除了(又是一个备份恢复策略不当地例子),使用基于时间点的恢复也不可能了。剩下的一条路,只有使用log miner。还好归档文件还在数据库服务器上。
这套库是一套RAC数据库,由于没有人能确认操作发生在哪个节点,因此需要将一个节点下所有的归档复制到另一个节点上(如果没有足够的空间,可以使用NFS)。然后需要找到我们用于数据恢复的归档日志:

set linesize 170 pagesize 10000   
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';   
  
col name for a30   
col first_change for a10   
col next_change for a10   
  
select max(first_time) from v$archived_log   
where first_time < to_date('200909251900','yyyymmddhh24mi'); --这里的时间为错误发生时估计的最早时间。   
  
select sequence#,first_time,name,to_char(first_change#,'xxxxxxxx') first_change,   
 to_char(next_change#,'xxxxxxxx') next_change   
 from v$archived_log   
where  first_time >=to_date('200909251707','yyyymmddhh24mi')   
order by 2;--这里的时间为前一SQL的max(first_time)结果   
  
 SEQUENCE# FIRST_TIME          NAME                           FIRST_CHAN NEXT_CHANG   
---------- ------------------- ------------------------------ ---------- ----------   
      4039 2009-09-25 17:07:10 /arch/db1_1_4039.arc          88ce7eff   88d1457c   
      4040 2009-09-26 12:24:52 /arch/db1_1_4040.arc          88d1457c   88d1459f   
      4041 2009-09-26 12:25:22 /arch/db1_1_4041.arc          88d1459f   88d156a4   
      4688 2009-09-26 12:37:59 /arch/db1_2_4688.arc          88d1457f   88d1464a   
      4689 2009-09-26 12:38:27 /arch/db1_2_4689.arc          88d1464a   88d1569c   
      4042 2009-09-26 12:54:44 /arch/db1_1_4042.arc          88d156a4   88d157e7   
      4043 2009-09-26 12:54:56 /arch/db1_1_4043.arc          88d157e7   88d1ab06   
      4690 2009-09-26 13:07:47 /arch/db1_2_4690.arc          88d1569c   88d1570b   
      4691 2009-09-26 13:08:00 /arch/db1_2_4691.arc          88d1570b   88d1ab09   
      4044 2009-09-26 15:27:32 /arch/db1_1_4044.arc          88d1ab06   88d1ab0d   
      4045 2009-09-26 15:27:35 /arch/db1_1_4045.arc          88d1ab0d   88d25091   
      4692 2009-09-26 15:40:36 /arch/db1_2_4692.arc          88d1ab09   88d1ab77   
      4693 2009-09-26 15:40:39 /arch/db1_2_4693.arc          88d1ab77   88d25094   
      4046 2009-09-26 22:24:07 /arch/db1_1_4046.arc          88d25091   88d250db   
      4047 2009-09-26 22:24:19 /arch/db1_1_4047.arc          88d250db   88d2515e   
      4048 2009-09-26 22:24:29 /arch/db1_1_4048.arc          88d2515e   88d25167   
      4049 2009-09-26 22:24:41 /arch/db1_1_4049.arc          88d25167   88d25cac   
      4694 2009-09-26 22:37:13 /arch/db1_2_4694.arc          88d25094   88d25147   
      4695 2009-09-26 22:37:25 /arch/db1_2_4695.arc          88d25147   88d2515b   
      4696 2009-09-26 22:37:33 /arch/db1_2_4696.arc          88d2515b   88d2516a   
      4697 2009-09-26 22:37:47 /arch/db1_2_4697.arc          88d2516a   88d25ca9   
      4050 2009-09-26 22:41:57 /arch/db1_1_4050.arc          88d25cac   88d25cde   
      4698 2009-09-26 22:55:01 /arch/db1_2_4698.arc          88d25ca9   88d25dcf   
      4699 2009-09-26 22:55:19 /arch/db1_2_4699.arc          88d25dcf   88dbd27e 
set linesize 170 pagesize 10000
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
col name for a30
col first_change for a10
col next_change for a10
select max(first_time) from v$archived_log
where first_time < to_date('200909251900','yyyymmddhh24mi'); --这里的时间为错误发生时估计的最早时间。
select sequence#,first_time,name,to_char(first_change#,'xxxxxxxx') first_change,
 to_char(next_change#,'xxxxxxxx') next_change
 from v$archived_log
where  first_time >=to_date('200909251707','yyyymmddhh24mi')
order by 2;--这里的时间为前一SQL的max(first_time)结果
 SEQUENCE# FIRST_TIME          NAME                           FIRST_CHAN NEXT_CHANG
---------- ------------------- ------------------------------ ---------- ----------
      4039 2009-09-25 17:07:10 /arch/db1_1_4039.arc          88ce7eff   88d1457c
      4040 2009-09-26 12:24:52 /arch/db1_1_4040.arc          88d1457c   88d1459f
      4041 2009-09-26 12:25:22 /arch/db1_1_4041.arc          88d1459f   88d156a4
      4688 2009-09-26 12:37:59 /arch/db1_2_4688.arc          88d1457f   88d1464a
      4689 2009-09-26 12:38:27 /arch/db1_2_4689.arc          88d1464a   88d1569c
      4042 2009-09-26 12:54:44 /arch/db1_1_4042.arc          88d156a4   88d157e7
      4043 2009-09-26 12:54:56 /arch/db1_1_4043.arc          88d157e7   88d1ab06
      4690 2009-09-26 13:07:47 /arch/db1_2_4690.arc          88d1569c   88d1570b
      4691 2009-09-26 13:08:00 /arch/db1_2_4691.arc          88d1570b   88d1ab09
      4044 2009-09-26 15:27:32 /arch/db1_1_4044.arc          88d1ab06   88d1ab0d
      4045 2009-09-26 15:27:35 /arch/db1_1_4045.arc          88d1ab0d   88d25091
      4692 2009-09-26 15:40:36 /arch/db1_2_4692.arc          88d1ab09   88d1ab77
      4693 2009-09-26 15:40:39 /arch/db1_2_4693.arc          88d1ab77   88d25094
      4046 2009-09-26 22:24:07 /arch/db1_1_4046.arc          88d25091   88d250db
      4047 2009-09-26 22:24:19 /arch/db1_1_4047.arc          88d250db   88d2515e
      4048 2009-09-26 22:24:29 /arch/db1_1_4048.arc          88d2515e   88d25167
      4049 2009-09-26 22:24:41 /arch/db1_1_4049.arc          88d25167   88d25cac
      4694 2009-09-26 22:37:13 /arch/db1_2_4694.arc          88d25094   88d25147
      4695 2009-09-26 22:37:25 /arch/db1_2_4695.arc          88d25147   88d2515b
      4696 2009-09-26 22:37:33 /arch/db1_2_4696.arc          88d2515b   88d2516a
      4697 2009-09-26 22:37:47 /arch/db1_2_4697.arc          88d2516a   88d25ca9
      4050 2009-09-26 22:41:57 /arch/db1_1_4050.arc          88d25cac   88d25cde
      4698 2009-09-26 22:55:01 /arch/db1_2_4698.arc          88d25ca9   88d25dcf
      4699 2009-09-26 22:55:19 /arch/db1_2_4699.arc          88d25dcf   88dbd27e
尝试找到数据被错误更新的时间点:

 
exec sys.dbms_logmnr.add_logfile(logfilename=>'/arch/db1_1_4038.arc');   
exec sys.dbms_logmnr.add_logfile(logfilename=>'/arch/db1_1_4039.arc');   
  
exec sys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);   
  
col sql_redo for a50   
  
select scn,timestamp,username,sql_redo from v$logmnr_contents   
where operation='UPDATE' and upper(sql_redo) like '%TBL_FORM_FORM%'  
and sql_redo like '%SGS0900021BNc10%'  --这个值是UPDATE时某一列被更新后的值,用在这里便于查找。   
order by scn,timestamp;   
exec sys.dbms_logmnr.end_logmnr; 
exec sys.dbms_logmnr.add_logfile(logfilename=>'/arch/db1_1_4038.arc');
exec sys.dbms_logmnr.add_logfile(logfilename=>'/arch/db1_1_4039.arc');
exec sys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);
col sql_redo for a50
select scn,timestamp,username,sql_redo from v$logmnr_contents
where operation='UPDATE' and upper(sql_redo) like '%TBL_FORM_FORM%'
and sql_redo like '%SGS0900021BNc10%'  --这个值是UPDATE时某一列被更新后的值,用在这里便于查找。
order by scn,timestamp;
exec sys.dbms_logmnr.end_logmnr;
很不幸的是,没有找着需要的数据。再往后找了几个日志,也没找着。
如果一直找下去,显然会消耗比较长的时间,业务也已经停止了。不过可以用一种简单的方法来查找数据被错误更新发生的时间:一个比较大的表,通常段头后面的那个块,也就是存储那个表的数据的第1个块,通常是很少更新的,至少当时恢复的那个表是这样一种情况。我们可以通过数据块中ITL上的事务SCN来满足我们的要求。

 
SQL> select tablespace_name,extent_id,file_id,block_id,blocks   
     from dba_extents where owner='XXX'  
     and segment_name='TBL_FORM_FORM'  
     order by extent_id;   
  
TABLESPACE_NAME   EXTENT_ID    FILE_ID   BLOCK_ID  BLOCKS   
---------------- ---------- ---------- ---------- -------   
XXXX                      0         16      25481     128   
XXXX                      1         17      23433     128   
XXXX                      2         18      21385     128   
XXXX                      3         19      19977     128   
XXXX                      4         16      23945     128   
XXXX                      5         17       8585     128   
XXXX                      6         18      14217     128   
XXXX                      7         19      18825     128   
  
SQL> alter system dump datafile 16 block 25482;   
  
System altered.   
  
Start dump data blocks tsn: 4 file#: 16 minblk 25482 maxblk 25482   
buffer tsn: 4 rdba: 0x0400638a (16/25482)   
scn: 0x0000.88e21027 seq: 0x02 flg: 0x00 tail: 0x10270602   
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data   
Block header dump:  0x0400638a   
 Object id on Block? Y   
 seg/obj: 0x40d8  csc: 0x00.88e20c40  itc: 2  flg: -  typ: 1 - DATA   
     fsl: 0  fnx: 0x0 ver: 0x01   
  
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc   
0x01   0x0010.011.0006ed74  0x03c002a0.2f48.07  C---    0  scn 0x0000.88d7af30   
0x02   0x0012.019.000027e0  0x03c00ede.05de.42  C---    0  scn 0x0000.44e2ee39 
SQL> select tablespace_name,extent_id,file_id,block_id,blocks
     from dba_extents where owner='XXX'
     and segment_name='TBL_FORM_FORM'
     order by extent_id;
TABLESPACE_NAME   EXTENT_ID    FILE_ID   BLOCK_ID  BLOCKS
---------------- ---------- ---------- ---------- -------
XXXX                      0         16      25481     128
XXXX                      1         17      23433     128
XXXX                      2         18      21385     128
XXXX                      3         19      19977     128
XXXX                      4         16      23945     128
XXXX                      5         17       8585     128
XXXX                      6         18      14217     128
XXXX                      7         19      18825     128
SQL> alter system dump datafile 16 block 25482;
System altered.
Start dump data blocks tsn: 4 file#: 16 minblk 25482 maxblk 25482
buffer tsn: 4 rdba: 0x0400638a (16/25482)
scn: 0x0000.88e21027 seq: 0x02 flg: 0x00 tail: 0x10270602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump:  0x0400638a
 Object id on Block? Y
 seg/obj: 0x40d8  csc: 0x00.88e20c40  itc: 2  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0010.011.0006ed74  0x03c002a0.2f48.07  C---    0  scn 0x0000.88d7af30
0x02   0x0012.019.000027e0  0x03c00ede.05de.42  C---    0  scn 0x0000.44e2ee39
从上面的结果可以看到,数据块的ITL中,最新的事务其SCN为88d7af30,正处于最后一个归档日志的first_change#和last_change#之间,即88d25dcf和88dbd27e之间,难不成这个错误是今天早上才发生的?于是我挖掘最后1个归档日志,结果发生错误的确是发生在早上,也就是我开始进行恢复操作之前半个小时。

既然错误并没有发生太久,同时这个系统也允许一定的数据丢失,那就使用flashback query,得到UPDATE操作之前的数据即可。

create table tbl_form_form_new   
as select * from tbl_form_form   
as of timestamp to_date('2009-09-27 09:08:00','yyyy-mm-dd hh24:mi:ss');   
--当然这里也可以按SCN进行闪回。 
create table tbl_form_form_new
as select * from tbl_form_form
as of timestamp to_date('2009-09-27 09:08:00','yyyy-mm-dd hh24:mi:ss');
--当然这里也可以按SCN进行闪回。
幸运的是,这次闪回查询成功了。看起来足够大的UNDO表空间还是有好处,至少我已经有数次用闪回查询来恢复数据。



相关阅读:
win10中安装的软件不兼容该怎么办?
Macbook Pro/Air合上盖子无法正常睡眠/唤醒的解决方法
js实现目录定位正文示例
Javascript中With语句用法实例
快速把Win10任务栏应用图标底色透明的技巧
Win10操作系统分发商 为什么是这四家?
基于mysq字段选择的详解
Ajax 无刷新在注册用户名时判断是否为空是否被使用
jquery获取div距离窗口和父级dv的距离示例
.NET MVC中ViewData,ViewBag和TempData的区别浅析
JavaScript中Date对象的常用方法示例
基于C#实现的三层架构实例
Linux下创建nginx脚本-start、stop、reload…
javascript限制用户只能输汉字中文的方法
快速导航
PHP MySQL HTML CSS JavaScript MSSQL AJAX .NET JSP Linux Mac ASP 服务器 SQL jQuery C# C++ java Android IOS oracle MongoDB SQLite wamp 交通频道 作文范文 一次有意义的活动 李潇键 粤教版必修二同步测试题-知能巩固提升10 风电公司签约仪式主持词 农村部分计划生育家庭奖励扶助制度工作实施方案 秋天的赞美 关于捐款的感谢信范文 诗画畲乡,浪漫景宁 党员干部学规矩懂规矩才能守规矩 职员离职证明范文 元旦节作文150字 广电精神 值得收藏的幽默短信30 母亲的手作文450字 节水口号标语 高中高三作文1200字:魔力之学院——郊游喽!(2) 俺这个人向来比较厚道,做事也比较低调 雨中接妈妈 沁园春(为老人书南堂壁) 我会一辈子把你放在心里,不管你喜欢谁,无论你在何处,真的不想放弃! 从医药微观层面管窥医药卫生体制改革 金融专业学生银行实习报告 远处飘来一朵云作文400字 这就是我眼中的色彩700字 小学五年级作文400字:一家三条“虫” 八年 小学生最美孝心少年事迹材料 2011最新原创版的英文求职简历范文 蹲点某村的调查报告 深入开展两学一做推动全面从严治党 AftertheExamination-考试之后 原来是美男经典台词 2011年市县加快依法行政发展计划 书斋谩兴二首 借盘子100字 假如我是科学家300字 2013年09月23日 21时56分02秒 花儿,绽放在旭日东升之时〈2〉作文1000字 【玄幻小说范文】灵蛇传说 “作风建设年”和民主评议基层站所活动实施办法 台阶 感情如刀,刻着幸福和痛苦【陌夕原创】 郭明义事件学习体会 盼望(2) 关于优化企业经营环境的十项承诺 那巧合,使得傍晚的云端,紫霞闪过 男人最易忽略女人这5个心思 大舍大得,小舍小得,不舍不得! 岁暮风卷地(文、白水先生) 那扇窗作文150字 年度党建工作总结

Copyright © 2016 phpStudy |