记一次Oracle数据恢复过程
事情的起因是,一个应用升级后,某一个操作导致一个表的几个列全部被更新为同一值(忍不住又要唠叨测试的重要性)。这样的错误居然出现在应用代码中,显然是重大的BUG。那个是罪魁祸首的SQL,UPDATE语句,其WHERE条件仅仅只有一个where1=1。
系统的维护人员称是星期五出的错,发现出错是在星期天,也就是我恢复数据的日期,与声称的出错时间已经隔了将近2天。开始尝试用flashbackquery恢复数据,报ORA-01555错误,此路不通。维护人员说,星期五之前的RMAN备份已经被删除了(又是一个备份恢复策略不当地例子),使用基于时间点的恢复也不可能了。剩下的一条路,只有使用logminer。还好归档文件还在数据库服务器上。
这套库是一套RAC数据库,由于没有人能确认操作发生在哪个节点,因此需要将一个节点下所有的归档复制到另一个节点上(如果没有足够的空间,可以使用NFS)。然后需要找到我们用于数据恢复的归档日志:
setlinesize170pagesize10000
altersessionsetnls_date_format='yyyy-mm-ddhh24:mi:ss';
colnamefora30
colfirst_changefora10
colnext_changefora10
selectmax(first_time)fromv$archived_log
wherefirst_time<to_date('200909251900','yyyymmddhh24mi');--这里的时间为错误发生时估计的最早时间。
selectsequence#,first_time,name,to_char(first_change#,'xxxxxxxx')first_change,
to_char(next_change#,'xxxxxxxx')next_change
fromv$archived_log
where first_time>=to_date('200909251707','yyyymmddhh24mi')
orderby2;--这里的时间为前一SQL的max(first_time)结果
SEQUENCE#FIRST_TIME NAME FIRST_CHANNEXT_CHANG
-------------------------------------------------------------------------------
40392009-09-2517:07:10/arch/db1_1_4039.arc 88ce7eff 88d1457c
40402009-09-2612:24:52/arch/db1_1_4040.arc 88d1457c 88d1459f
40412009-09-2612:25:22/arch/db1_1_4041.arc 88d1459f 88d156a4
46882009-09-2612:37:59/arch/db1_2_4688.arc 88d1457f 88d1464a
46892009-09-2612:38:27/arch/db1_2_4689.arc 88d1464a 88d1569c
40422009-09-2612:54:44/arch/db1_1_4042.arc 88d156a4 88d157e7
40432009-09-2612:54:56/arch/db1_1_4043.arc 88d157e7 88d1ab06
46902009-09-2613:07:47/arch/db1_2_4690.arc 88d1569c 88d1570b
46912009-09-2613:08:00/arch/db1_2_4691.arc 88d1570b 88d1ab09
40442009-09-2615:27:32/arch/db1_1_4044.arc 88d1ab06 88d1ab0d
40452009-09-2615:27:35/arch/db1_1_4045.arc 88d1ab0d 88d25091
46922009-09-2615:40:36/arch/db1_2_4692.arc 88d1ab09 88d1ab77
46932009-09-2615:40:39/arch/db1_2_4693.arc 88d1ab77 88d25094
40462009-09-2622:24:07/arch/db1_1_4046.arc 88d25091 88d250db
40472009-09-2622:24:19/arch/db1_1_4047.arc 88d250db 88d2515e
40482009-09-2622:24:29/arch/db1_1_4048.arc 88d2515e 88d25167
40492009-09-2622:24:41/arch/db1_1_4049.arc 88d25167 88d25cac
46942009-09-2622:37:13/arch/db1_2_4694.arc 88d25094 88d25147
46952009-09-2622:37:25/arch/db1_2_4695.arc 88d25147 88d2515b
46962009-09-2622:37:33/arch/db1_2_4696.arc 88d2515b 88d2516a
46972009-09-2622:37:47/arch/db1_2_4697.arc 88d2516a 88d25ca9
40502009-09-2622:41:57/arch/db1_1_4050.arc 88d25cac 88d25cde
46982009-09-2622:55:01/arch/db1_2_4698.arc 88d25ca9 88d25dcf
46992009-09-2622:55:19/arch/db1_2_4699.arc 88d25dcf 88dbd27e
setlinesize170pagesize10000
altersessionsetnls_date_format='yyyy-mm-ddhh24:mi:ss';
colnamefora30
colfirst_changefora10
colnext_changefora10
selectmax(first_time)fromv$archived_log
wherefirst_time<to_date('200909251900','yyyymmddhh24mi');--这里的时间为错误发生时估计的最早时间。
selectsequence#,first_time,name,to_char(first_change#,'xxxxxxxx')first_change,
to_char(next_change#,'xxxxxxxx')next_change
fromv$archived_log
where first_time>=to_date('200909251707','yyyymmddhh24mi')
orderby2;--这里的时间为前一SQL的max(first_time)结果
SEQUENCE#FIRST_TIME NAME FIRST_CHANNEXT_CHANG
-------------------------------------------------------------------------------
40392009-09-2517:07:10/arch/db1_1_4039.arc 88ce7eff 88d1457c
40402009-09-2612:24:52/arch/db1_1_4040.arc 88d1457c 88d1459f
40412009-09-2612:25:22/arch/db1_1_4041.arc 88d1459f 88d156a4
46882009-09-2612:37:59/arch/db1_2_4688.arc 88d1457f 88d1464a
46892009-09-2612:38:27/arch/db1_2_4689.arc 88d1464a 88d1569c
40422009-09-2612:54:44/arch/db1_1_4042.arc 88d156a4 88d157e7
40432009-09-2612:54:56/arch/db1_1_4043.arc 88d157e7 88d1ab06
46902009-09-2613:07:47/arch/db1_2_4690.arc 88d1569c 88d1570b
46912009-09-2613:08:00/arch/db1_2_4691.arc 88d1570b 88d1ab09
40442009-09-2615:27:32/arch/db1_1_4044.arc 88d1ab06 88d1ab0d
40452009-09-2615:27:35/arch/db1_1_4045.arc 88d1ab0d 88d25091
46922009-09-2615:40:36/arch/db1_2_4692.arc 88d1ab09 88d1ab77
46932009-09-2615:40:39/arch/db1_2_4693.arc 88d1ab77 88d25094
40462009-09-2622:24:07/arch/db1_1_4046.arc 88d25091 88d250db
40472009-09-2622:24:19/arch/db1_1_4047.arc 88d250db 88d2515e
40482009-09-2622:24:29/arch/db1_1_4048.arc 88d2515e 88d25167
40492009-09-2622:24:41/arch/db1_1_4049.arc 88d25167 88d25cac
46942009-09-2622:37:13/arch/db1_2_4694.arc 88d25094 88d25147
46952009-09-2622:37:25/arch/db1_2_4695.arc 88d25147 88d2515b
46962009-09-2622:37:33/arch/db1_2_4696.arc 88d2515b 88d2516a
46972009-09-2622:37:47/arch/db1_2_4697.arc 88d2516a 88d25ca9
40502009-09-2622:41:57/arch/db1_1_4050.arc 88d25cac 88d25cde
46982009-09-2622:55:01/arch/db1_2_4698.arc 88d25ca9 88d25dcf
46992009-09-2622:55:19/arch/db1_2_4699.arc 88d25dcf 88dbd27e
尝试找到数据被错误更新的时间点:
execsys.dbms_logmnr.add_logfile(logfilename=>'/arch/db1_1_4038.arc');
execsys.dbms_logmnr.add_logfile(logfilename=>'/arch/db1_1_4039.arc');
execsys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);
colsql_redofora50
selectscn,timestamp,username,sql_redofromv$logmnr_contents
whereoperation='UPDATE'andupper(sql_redo)like'%TBL_FORM_FORM%'
andsql_redolike'%SGS0900021BNc10%' --这个值是UPDATE时某一列被更新后的值,用在这里便于查找。
orderbyscn,timestamp;
execsys.dbms_logmnr.end_logmnr;
execsys.dbms_logmnr.add_logfile(logfilename=>'/arch/db1_1_4038.arc');
execsys.dbms_logmnr.add_logfile(logfilename=>'/arch/db1_1_4039.arc');
execsys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);
colsql_redofora50
selectscn,timestamp,username,sql_redofromv$logmnr_contents
whereoperation='UPDATE'andupper(sql_redo)like'%TBL_FORM_FORM%'
andsql_redolike'%SGS0900021BNc10%' --这个值是UPDATE时某一列被更新后的值,用在这里便于查找。
orderbyscn,timestamp;
execsys.dbms_logmnr.end_logmnr;
很不幸的是,没有找着需要的数据。再往后找了几个日志,也没找着。
如果一直找下去,显然会消耗比较长的时间,业务也已经停止了。不过可以用一种简单的方法来查找数据被错误更新发生的时间:一个比较大的表,通常段头后面的那个块,也就是存储那个表的数据的第1个块,通常是很少更新的,至少当时恢复的那个表是这样一种情况。我们可以通过数据块中ITL上的事务SCN来满足我们的要求。
SQL>selecttablespace_name,extent_id,file_id,block_id,blocks
fromdba_extentswhereowner='XXX'
andsegment_name='TBL_FORM_FORM'
orderbyextent_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>altersystemdumpdatafile16block25482;
Systemaltered.
Startdumpdatablockstsn:4file#:16minblk25482maxblk25482
buffertsn:4rdba:0x0400638a(16/25482)
scn:0x0000.88e21027seq:0x02flg:0x00tail:0x10270602
frmt:0x02chkval:0x0000type:0x06=transdata
Blockheaderdump: 0x0400638a
ObjectidonBlock?Y
seg/obj:0x40d8 csc:0x00.88e20c40 itc:2 flg:- typ:1-DATA
fsl:0 fnx:0x0ver:0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0010.011.0006ed74 0x03c002a0.2f48.07 C--- 0 scn0x0000.88d7af30
0x02 0x0012.019.000027e0 0x03c00ede.05de.42 C--- 0 scn0x0000.44e2ee39
SQL>selecttablespace_name,extent_id,file_id,block_id,blocks
fromdba_extentswhereowner='XXX'
andsegment_name='TBL_FORM_FORM'
orderbyextent_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>altersystemdumpdatafile16block25482;
Systemaltered.
Startdumpdatablockstsn:4file#:16minblk25482maxblk25482
buffertsn:4rdba:0x0400638a(16/25482)
scn:0x0000.88e21027seq:0x02flg:0x00tail:0x10270602
frmt:0x02chkval:0x0000type:0x06=transdata
Blockheaderdump: 0x0400638a
ObjectidonBlock?Y
seg/obj:0x40d8 csc:0x00.88e20c40 itc:2 flg:- typ:1-DATA
fsl:0 fnx:0x0ver:0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0010.011.0006ed74 0x03c002a0.2f48.07 C--- 0 scn0x0000.88d7af30
0x02 0x0012.019.000027e0 0x03c00ede.05de.42 C--- 0 scn0x0000.44e2ee39
从上面的结果可以看到,数据块的ITL中,最新的事务其SCN为88d7af30,正处于最后一个归档日志的first_change#和last_change#之间,即88d25dcf和88dbd27e之间,难不成这个错误是今天早上才发生的?于是我挖掘最后1个归档日志,结果发生错误的确是发生在早上,也就是我开始进行恢复操作之前半个小时。
既然错误并没有发生太久,同时这个系统也允许一定的数据丢失,那就使用flashbackquery,得到UPDATE操作之前的数据即可。
createtabletbl_form_form_new
asselect*fromtbl_form_form
asoftimestampto_date('2009-09-2709:08:00','yyyy-mm-ddhh24:mi:ss');
--当然这里也可以按SCN进行闪回。
createtabletbl_form_form_new
asselect*fromtbl_form_form
asoftimestampto_date('2009-09-2709:08:00','yyyy-mm-ddhh24:mi:ss');
--当然这里也可以按SCN进行闪回。
幸运的是,这次闪回查询成功了。看起来足够大的UNDO表空间还是有好处,至少我已经有数次用闪回查询来恢复数据。