跳過rman壞塊進行數據恢復
作者：惜分飛
如果我們僅有一份rman備份，而這個時候rman備份有出現壞塊，使得我們的還原/恢復工作無法繼續下去，導致數據大量丟失。怎么才能跳過這個rman壞塊進行數據恢復？本文分享了一些技巧和方法，希望能幫到你。
在有些情況下,我們僅有一份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/or its affiliates. All rights reserved.
- connected to target database: XIFENFEI (DBID=1422012639)
- RMAN> backup tablespace users format 'f:/users_bak.rman';
- Starting backup at 06-JUN-13
- using target database control file instead 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) in backup set
- input datafile file number=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 set complete, elapsed time: 00:00:03
- Finished backup at 06-JUN-13
切換歸檔日志
- SQL> alter system switch logfile;
- System altered.
- SQL> /
- System altered.
- SQL> /
- System altered.
- SQL> archive log list;
- Database log mode Archive Mode
- Automatic archival Enabled
- Archive destination E:\oracle\product\11.2.0\dbhome_1\RDBMS
- Oldest online log sequence 95
- Next log sequence to archive 97
- Current log sequence 97
重命名數據文件
- SQL> shutdown immediate
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- --------------------------------------
- e:\oracle\oradata\XIFENFEI>move USERS01.DBF USERS01_bak.DBF
- 移動了 1 個文件。
- --------------------------------------
- SQL> startup
- ORACLE instance started.
- Total System Global Area 418484224 bytes
- Fixed Size 1385052 bytes
- Variable Size 327159204 bytes
- Database Buffers 83886080 bytes
- Redo Buffers 6053888 bytes
- Database mounted.
- ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
- ORA-01110: data file 4: 'E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF'
#p#
破壞備份集
破壞前
破壞后
這里很明顯,我通過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/or its 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 file instead of recovery catalog
- allocated channel: ORA_DISK_1
- channel ORA_DISK_1: SID=63 device type=DISK
- channel ORA_DISK_1: starting datafile backup set restore
- 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 while restoring 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 file number=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
- Errors in file 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 open file
- O/S-Error: (OS 2) 系統找不到指定的文件。
- Errors in file E:\ORACLE\diag\rdbms\xifenfei\xff\trace\xff_ora_4272.trc:
- ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
- ORA-01110: data file 4: '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還原無法正常進行下去,還原后的數據文件大小。
#p#
觀察已經正常還原出來數據文件情況
- SQL> select CHECKPOINT_CHANGE#,file# from v$datafile_header;
- CHECKPOINT_CHANGE# FILE#
- ------------------ ----------
- 1571582 1
- 1571582 2
- 1571582 3
- 18379 4
- 1571582 5
- 1571582 6
- 1571582 7
- SQL> recover database datafile 4 ;
- ORA-00274: illegal recovery option DATAFILE
- SQL> recover datafile 4;
- ORA-00279: change 18379 generated at 01/20/2013 17:13:56 needed for thread 1
- ORA-00289: suggestion :
- E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000001_0805223583.0001
- ORA-00280: change 18379 for thread 1 is in sequence #1
- Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
rman只是還原了很小的一部分文件,做恢復提示需要從歸檔日志seq 1開始(某些情況可能需要其他歸檔,總之不是正常情況),證明rman還原異常
設置event事件還原
- SQL> shutdown abort;
- ORACLE instance shut down.
- SQL> startup pfile='e:/pfile.txt' mount;
- ORACLE instance started.
- Total System Global Area 418484224 bytes
- Fixed Size 1385052 bytes
- Variable Size 327159204 bytes
- Database Buffers 83886080 bytes
- Redo Buffers 6053888 bytes
- Database mounted.
- SQL> show parameter event;
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- event string 19548 trace name context forev
- er, 19549 trace name context f
- orever
- Event 19548:This will attempt to restore content of the corrupted block if it 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 file instead of recovery catalog
- allocated channel: ORA_DISK_1
- channel ORA_DISK_1: SID=63 device type=DISK
- channel ORA_DISK_1: starting datafile backup set restore
- 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
- in file 4 is corrupt in backup piece F:\USERS_BAK.RMAN.
- Such blocks would usually be formatted as empty
- in the restored file, but event 19548 has been
- set to include the block as-is in the 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 in the backup of file 4 was missing
- or corrupt. Event 19549 has been set to allow
- the file to 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還原依然遇到很多壞塊,但是均跳過壞塊,還是完整的恢復出來的數據文件(大小)。
#p#
rman還原數據文件
- RMAN> recover datafile 4;
- Starting recover at 06-JUN-13
- using channel ORA_DISK_1
- starting media recovery
- archived log for thread 1 with sequence 94 is already on disk as file E:\ORACLE\
- PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000094_0805223583.0001
- archived log for thread 1 with sequence 95 is already on disk as file E:\ORACLE\
- PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000095_0805223583.0001
- archived log for thread 1 with sequence 96 is already on disk as file 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 at 06-JUN-13
這里可以明顯的看到在recover過程中數據庫應用的是備份后的所有歸檔,數據文件是正常被還原出來(壞塊除外)。
查詢對象
- SQL> alter database open;
- Database altered.
- SQL> conn test/test
- Connected.
- SQL> select * from tab;
- TNAME TABTYPE CLUSTERID
- ------------------------------ ------- ----------
- STB101 TABLE
- SQL> select count(*) from stb101;
- select count(*) from stb101
- *
- ERROR at line 1:
- ORA-08103: object no longer exists
dbv檢查壞塊
- e:\oracle\oradata\XIFENFEI>dbv file=USERS01.DBF
- DBVERIFY: Release 11.2.0.3.0 - Production on Thu Jun 6 23:59:49 2013
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- DBVERIFY - Verification starting : FILE = E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF
- Page 100 is marked corrupt
- Corrupt block relative dba: 0x01000064 (file 4, block 100)
- Bad check value found during dbv:
- Data in bad block:
- type: 30 format: 2 rdba: 0x01000064
- last change scn: 0x0000.00004890 seq: 0x1 flg: 0x04
- spare1: 0x0 spare2: 0x0 spare3: 0x0
- consistency value in tail: 0x48901e01
- check value in block 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備份可用也很重要。
責任編輯：黃丹
來源：
xifenfei.com
