在有些情况下,我们仅有一份rman备份,而这个时候rman 备份有出现坏块,使得我们的还原/恢复工作无法继续下去,导致数据大量丢失.我们可以通过设置event 19548/19549来跳过坏块,最大程度抢救数据
rman备份数据文件
C:\Users\XIFENFEI>rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jun 6 20:31:19 2013
Copyright (c) 1982, 2011, Oracle and/orits affiliates. All rights reserved.
connected to target database: XIFENFEI (DBID=1422012639)
RMAN> backup tablespace usersformat 'f:/users_bak.rman';
Starting backup at 06-JUN-13 using target database control fileinstead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=197 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) inbackup set input datafile filenumber=00004 name=E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF channel ORA_DISK_1: starting piece 1 at 06-JUN-13 channel ORA_DISK_1: finished piece 1 at 06-JUN-13 piece handle=F:\USERS_BAK.RMAN tag=TAG20130606T203154 comment=NONE channel ORA_DISK_1: backup setcomplete, elapsed time: 00:00:03 Finished backup at 06-JUN-13 |
切换归档日志
SQL>altersystem switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> archive log list; Databaselog mode Archive Mode Automatic archival Enabled Archive destination E:\oracle\product\11.2.0\dbhome_1\RDBMS Oldest online log sequence 95 Nextlog sequenceto archive 97 Currentlog sequence 97 |
重命名数据文件
SQL> shutdown immediate Databaseclosed. Databasedismounted. ORACLE instance shut down.
-------------------------------------- e:\oracle\oradata\XIFENFEI>moveUSERS01.DBF USERS01_bak.DBF 移动了 1 个文件。 --------------------------------------
SQL> startup ORACLE instance started.
Total System GlobalArea 418484224 bytes FixedSize 1385052 bytes VariableSize 327159204 bytes DatabaseBuffers 83886080 bytes Redo Buffers 6053888 bytes Databasemounted. ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: 'E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF' |
破坏备份集
破坏前
破坏后
这里很明显,我通过ue把rman备份集中的T修改为了A,肯定破坏了文件,使之出现坏块
rman还原数据文件
C:\Users\XIFENFEI>rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jun 6 21:02:41 2013
Copyright (c) 1982, 2011, Oracle and/orits affiliates. All rights reserved.
connected to target database: XIFENFEI (DBID=1422012639, not open)
RMAN> restore datafile 4;
Starting restore at 06-JUN-13 using target database control fileinstead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=63 device type=DISK
channel ORA_DISK_1: starting datafile backup setrestore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00004 to E:\ORACLE\ORADATA\XIFENFEI\USERS 01.DBF channel ORA_DISK_1: reading from backup piece F:\USERS_BAK.RMAN channel ORA_DISK_1: ORA-19870: error whilerestoring backup piece F:\USERS_BAK.R MAN ORA-19612: datafile 4 not restored due to missing or corrupt data
failover to previous backup
creating datafile filenumber=4 name=E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF Finished restore at 06-JUN-13 |
这里可以清晰的看到rman报ORA-19612错误,restore 失败,alert日志为:
Thu Jun 06 21:02:31 2013 ALTER DATABASE OPEN Errorsinfile E:\ORACLE\diag\rdbms\xifenfei\xff\trace\xff_dbw0_7400.trc: ORA-01157: ????/?????? 4 - ??? DBWR ???? ORA-01110: ???? 4: 'E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF' ORA-27041: ?????? OSD-04002: unable to openfile O/S-Error: (OS 2) 系统找不到指定的文件。 Errorsinfile E:\ORACLE\diag\rdbms\xifenfei\xff\trace\xff_ora_4272.trc: ORA-01157: cannot identify/lockdata file4 - see DBWR trace file ORA-01110: data file4: 'E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF' ORA-1157 signalled during: ALTER DATABASE OPEN... Thu Jun 06 21:02:33 2013 Checker run found 1 new persistent data failures Thu Jun 06 21:03:23 2013 Corrupt block 101 found during reading backup piece, file=F:\USERS_BAK.RMAN, corr_type=3 Reread of blocknum=101, file=F:\USERS_BAK.RMAN, found same corrupt data Reread of blocknum=101, file=F:\USERS_BAK.RMAN, found same corrupt data Reread of blocknum=101, file=F:\USERS_BAK.RMAN, found same corrupt data Reread of blocknum=101, file=F:\USERS_BAK.RMAN, found same corrupt data Reread of blocknum=101, file=F:\USERS_BAK.RMAN, found same corrupt data Continuing reading piece F:\USERS_BAK.RMAN, no other copies available. |
rman备份集有坏块,导致rman还原无法正常进行下去,还原后的数据文件大小
观察已经正常还原出来数据文件情况
SQL>selectCHECKPOINT_CHANGE#,file# fromv$datafile_header;
CHECKPOINT_CHANGE# FILE# ------------------ ---------- 1571582 1 1571582 2 1571582 3 18379 4 1571582 5 1571582 6 1571582 7
SQL> recover databasedatafile 4 ; ORA-00274: illegal recovery optionDATAFILE
SQL> recover datafile 4; ORA-00279: change 18379 generated at01/20/2013 17:13:56 needed forthread 1 ORA-00289: suggestion : E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000001_0805223583.0001 ORA-00280: change 18379 forthread 1 isin sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} |
rman只是还原了很小的一部分文件,做恢复提示需要从归档日志seq1开始(某些情况可能需要其他归档,总之不是正常情况),证明rman还原异常
设置event事件还原
SQL> shutdown abort; ORACLE instance shut down. SQL> startup pfile='e:/pfile.txt'mount; ORACLE instance started.
Total System GlobalArea 418484224 bytes FixedSize 1385052 bytes VariableSize 327159204 bytes DatabaseBuffers 83886080 bytes Redo Buffers 6053888 bytes Databasemounted. SQL> show parameter event;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ event string 19548 trace namecontext forev er, 19549 trace namecontext f orever | |
Event 19548:This will attempt to restore content of the corrupted block ifit is possible. Event 19549:This will suppress erroring out during restore |
|
rman还原数据文件
RMAN> restore datafile 4;
Starting restore at 06-JUN-13 using target database control fileinstead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=63 device type=DISK
channel ORA_DISK_1: starting datafile backup setrestore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00004 to E:\ORACLE\ORADATA\XIFENFEI\USERS 01.DBF channel ORA_DISK_1: reading from backup piece F:\USERS_BAK.RMAN channel ORA_DISK_1: piece handle=F:\USERS_BAK.RMAN tag=TAG20130606T203154 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:35 Finished restore at 06-JUN-13 |
这里证明数据库rman有坏块通过rman还原成功,alert日志提示如下
Thu Jun 06 21:29:53 2013 WARNING: The block that appears to be block number 100 infile 4 is corrupt inbackup piece F:\USERS_BAK.RMAN. Such blocks would usually be formatted as empty inthe restored file, but event 19548 has been setto include the block as-is inthe restored file. Corrupt block 102 found during reading backup piece, file=F:\USERS_BAK.RMAN, corr_type=-2 Reread of blocknum=102, file=F:\USERS_BAK.RMAN, found same corrupt data Reread of blocknum=102, file=F:\USERS_BAK.RMAN, found same corrupt data Reread of blocknum=102, file=F:\USERS_BAK.RMAN, found same corrupt data Reread of blocknum=102, file=F:\USERS_BAK.RMAN, found same corrupt data Reread of blocknum=102, file=F:\USERS_BAK.RMAN, found same corrupt data Continuing reading piece F:\USERS_BAK.RMAN, no other copies available. ………… Corrupt block 258 found during reading backup piece, file=F:\USERS_BAK.RMAN, corr_type=-2 Reread of blocknum=258, file=F:\USERS_BAK.RMAN, found same corrupt data Reread of blocknum=258, file=F:\USERS_BAK.RMAN, found same corrupt data Reread of blocknum=258, file=F:\USERS_BAK.RMAN, found same corrupt data Reread of blocknum=258, file=F:\USERS_BAK.RMAN, found same corrupt data Reread of blocknum=258, file=F:\USERS_BAK.RMAN, found same corrupt data Continuing reading piece F:\USERS_BAK.RMAN, no other copies available. WARNING: some data inthe backup of file4 was missing or corrupt. Event 19549 has been setto allow thefileto be restored anyway. backup header block count: 5369 backup actual block count: 5212 backup header checksum: -218250743 backup actual checksum: 1442665538 Full restore complete of datafile 4 E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF. Elapsed time: 0:00:25 checkpoint is 1570136 last deallocation scn is 1508457 |
这里rman还原依然遇到很多坏块,但是均跳过坏块,还是完整的恢复出来的数据文件(大小)
rman还原数据文件
RMAN> recover datafile 4;
Starting recover at06-JUN-13 using channel ORA_DISK_1
starting media recovery
archived log forthread 1 withsequence 94 isalready ondisk asfile E:\ORACLE\ PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000094_0805223583.0001 archived log forthread 1 withsequence 95 isalready ondisk asfile E:\ORACLE\ PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000095_0805223583.0001 archived log forthread 1 withsequence 96 isalready ondisk asfile E:\ORACLE\ PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000096_0805223583.0001 archived log file name=E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000094_080 5223583.0001 thread=1 sequence=94 media recovery complete, elapsed time: 00:00:00 Finished recover at06-JUN-13 |
这里可以明显的看到在recover过程中数据库应用的是备份后的所有归档,数据文件是正常被还原出来(坏块除外)
查询对象
SQL>alterdatabase open;
Databasealtered.
SQL> conn test/test Connected. SQL>select* fromtab;
TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- STB101 TABLE
SQL>selectcount(*)fromstb101; selectcount(*)fromstb101 * ERRORatline 1: ORA-08103: object nolonger exists |
dbv检查坏块
e:\oracle\oradata\XIFENFEI>dbv file=USERS01.DBF
DBVERIFY: Release 11.2.0.3.0 - Production onThu Jun 6 23:59:49 2013
Copyright (c) 1982, 2011, Oracle and/orits affiliates. Allrights reserved.
DBVERIFY - Verification starting : FILE = E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF
Page 100 ismarked corrupt Corrupt block relativedba: 0x01000064 (file 4, block 100) Badcheckvalue found during dbv: Datainbad block: type: 30 format: 2 rdba: 0x01000064 lastchange scn: 0x0000.00004890 seq: 0x1 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value intail: 0x48901e01 checkvalue inblock header: 0x8311 computed block checksum: 0x20
DBVERIFY - Verification complete
Total Pages Examined : 12320 Total Pages Processed (Data) : 4952 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 7069 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 298 |
证明设置了event之后,rman确实跳过了备份集中的坏块,而且是直接还原了坏块内容,证明了event 19548和19549作用
补充说明
在非特殊情况下强烈不建议设置相关event跳过rman中的坏块来还原/恢复数据库,这样将对数据的丢失,甚至数据库是否可以正常open不好评估,rman备份重要,确保rman备份可用也很重要.