只读数据文件损坏恢复实验记录

1、只读表空间的数据文件指为只读数据文件(read only)，当一个表空间从read write更改为read only时，该数据文件里已经产生的脏块儿都会由DBWn写到磁盘，完成一次不完整的完全检查点。从这一刻起，该数据文件数据块和文件头信息都不再更新，包括检查点。在以后open数据库时实例也忽略该只读数据文件文件头的检查点SCN与其他数据文件或联机REDO日志的同步。我们可以通过以下命令在read only和read write之间进行更改read write > read only:alter tablespace tablespace_name read only;read onl

2、y > read write:alter tablespace tablespace_name read write;查看系统里的只读表空间及其只读数据文件：sysMAA> col TABLESPACE_NAME for a20sysMAA> col FILE_NAME for a65sysMAA> select t.tablespace_name, d.file_id, d.file_name from dba_tablespaces t, dba_data_files d 2 where t.tablespace_name=d.tablespace_name 3 a

3、nd t.status='READ ONLY' 4 /TABLESPACE_NAME FILE_ID FILE_NAME- - -LTB 12 /u01/app/oracle/oradata/ltb01.dbf下面我们针对以下几种场景进行恢复：1）控制文件无损，数据库运行时丢失了只读数据文件，访问其内数据报错2）控制文件无损，只读数据文件丢失，数据库无法OPEN3）控制文件无损，只读数据文件头部损坏4）控制文件无损，有读写状态时候的备份，后来更改为只读状态，只读状态时数据文件损坏5）控制文件无损，有只读状态时候的备份，后来更改为读写状态，读写状态时数据文件损坏6）控制文件损坏，

4、只读数据文件损坏7）控制文件损坏，有只读数据文件状态时候的控制文件备份，后来数据文件改为读写，读写状态时数据文件损坏8）控制文件损坏，在线日志文件没有损坏，归档日志丢失，有旧的跟踪控制文件trace，数据库里有只读数据文件前提条件，有相应的备份。操作环境：· OS : Oracle Enterprise Linux 5.5 64Bit· DB Type : Oracle Restart· DB Version : 11.2.0.3准备一个实验对象只读表空间sysMAA> create tablespace ltb datafile '/u01/app

5、/oracle/oradata/ltb01.dbf' size 50M;sysMAA> create user l identified by oracle default tablespace ltb;sysMAA> grant resource, connect to l;sysMAA> create table l.luocs(domain varchar2(100);sysMAA> insert into l.luocs values('');sysMAA> commit;sysMAA> alter tablespace lt

6、b read only;场景1：控制文件无损，数据库运行时只读数据文件损坏，访问其内数据报错备份只读表空间ltbRMAN> backup tablespace ltb;Starting backup at 23-DEC-2012 18:29:30using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=44 device type=DISKchannel ORA_DISK_1: starting full datafi

7、le backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00012 name=/u01/app/oracle/oradata/ltb01.dbfchannel ORA_DISK_1: starting piece 1 at 23-DEC-2012 18:29:31channel ORA_DISK_1: finished piece 1 at 23-DEC-2012 18:29:32piece handle=/u01/recovery/MAA/backupset

8、/2012_12_23/o1_mf_nnndf_TAG20121223T182931_8ffq8cbz_.bkp tag=TAG20121223T182931 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 23-DEC-2012 18:29:32数据库运行状态下，破坏只读数据文件以模拟损坏oraclemaa3 $ dd if=/dev/zero of=/u01/app/oracle/oradata/ltb01.dbf bs=10M count=55+0

9、records in5+0 records out52428800 bytes (52 MB) copied, 0.04918 seconds, 1.1 GB/s查看v$datafile内容时，返回正常sysMAA> col NAME for a65sysMAA> select checkpoint_change# from v$datafile where file#=12;CHECKPOINT_CHANGE# - 3567837 但日志里将报错：Hex dump of (file 12, block 1) in trace file /u01/app/oracle/diag/r

10、dbms/maa/maa/trace/maa_m000_5343.trcCorrupt block relative dba: 0x03000001 (file 12, block 1)Completely zero block found during kcvxfh v8Reading datafile '/u01/app/oracle/oradata/ltb01.dbf' for corruption at rdba: 0x03000001 (file 12, block 1)Reread (file 12, block 1) found different corrupt

11、 data (no logical check)Hex dump of (file 12, block 1) in trace file /u01/app/oracle/diag/rdbms/maa/maa/trace/maa_m000_5343.trcCorrupt block relative dba: 0x03000001 (file 12, block 1)Completely zero block found during reread访问该数据文件里的数据：sysMAA> select * from l.luocs;select * from l.luocs*ERROR at

12、 line 1:ORA-01578: ORACLE data block corrupted (file # 12, block # 138)ORA-01110: data file 12: '/u01/app/oracle/oradata/ltb01.dbf'告警日志也会抛出相应错误信息：Hex dump of (file 12, block 2) in trace file /u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_5220.trcCorrupt block relative dba: 0x03000002 (file

13、 12, block 2)Completely zero block found during buffer readReading datafile '/u01/app/oracle/oradata/ltb01.dbf' for corruption at rdba: 0x03000002 (file 12, block 2)Reread (file 12, block 2) found same corrupt data (no logical check)Sun Dec 23 19:56:00 2012Corrupt Block Found TSN = 7, TSNAME

14、 = LTB RFN = 12, BLK = 2, RDBA = 50331650 OBJN = -1, OBJD = -1, OBJECT = , SUBOBJECT = SEGMENT OWNER = , SEGMENT TYPE = Errors in file /u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_5220.trc (incident=31391):ORA-01578: ORACLE data block corrupted (file # 12, block # 2)ORA-01110: data file 12: '

15、;/u01/app/oracle/oradata/ltb01.dbf'Hex dump of (file 12, block 1) in trace file /u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_5220.trcCorrupt block relative dba: 0x00000001 (file 12, block 1)Completely zero block found during validating datafile for block rangeReread of blocknum=1, file=/u01/

16、app/oracle/oradata/ltb01.dbf. found same corrupt dataReread of blocknum=1, file=/u01/app/oracle/oradata/ltb01.dbf. found same corrupt dataReread of blocknum=1, file=/u01/app/oracle/oradata/ltb01.dbf. found same corrupt dataReread of blocknum=1, file=/u01/app/oracle/oradata/ltb01.dbf. found same corr

17、upt dataReread of blocknum=1, file=/u01/app/oracle/oradata/ltb01.dbf. found same corrupt dataErrors in file /u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_5220.trc:ORA-19563: datafile header validation failed for file /u01/app/oracle/oradata/ltb01.dbfORA-01251: Unknown File Header Version read for

18、 file number 12ORA-01578: ORACLE data block corrupted (file # 12, block # 2)ORA-01110: data file 12: '/u01/app/oracle/oradata/ltb01.dbf'Sun Dec 23 19:56:01 2012Sweep inc31391: completed但此时只读数据文件状态还是ONLINEsysMAA> col FILE_NAME for a65sysMAA> select file_id, file_name, online_status from

19、 dba_data_files where tablespace_name='LTB' FILE_ID FILE_NAME ONLINE_STATUS- - - 12 /u01/app/oracle/oradata/ltb01.dbf ONLINE这里恢复需要注意，如果只是有数据讹误块儿导致数据访问报错，那可以简单通过RMAN的块恢复命令进行块级别恢复。但这里我是将整个数据文件破坏掉了，因此需要RMAN来还原数据文件，恢复可以在数据文件OPEN模式进行这时候我们直接RESTORE DATAFILE是不可行的，我们需要先将只读数据文件脱机处理，还原之后再ONLINERMAN>

20、; restore tablespace ltb;Starting restore at 23-DEC-2012 19:46:43using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/ltb01.dbfchann

21、el ORA_DISK_1: reading from backup piece /u01/recovery/MAA/backupset/2012_12_23/o1_mf_nnndf_TAG20121223T184851_8ffrdmhg_.bkpRMAN-00571: =RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =RMAN-00571: =RMAN-03002: failure of restore command at 12/23/2012 19:46:44ORA-19870: error while restoring backup piece

22、/u01/recovery/MAA/backupset/2012_12_23/o1_mf_nnndf_TAG20121223T184851_8ffrdmhg_.bkpORA-19573: cannot obtain exclusive enqueue for datafile 12正确的做法RMAN> run2> sql 'alter database datafile 12 offline'3> restore tablespace ltb;4> sql 'alter database datafile 12 online'5>

23、sql statement: alter database datafile 12 offlineStarting restore at 23-DEC-2012 19:50:41using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracl

24、e/oradata/ltb01.dbfchannel ORA_DISK_1: reading from backup piece /u01/recovery/MAA/backupset/2012_12_23/o1_mf_nnndf_TAG20121223T184851_8ffrdmhg_.bkpchannel ORA_DISK_1: piece handle=/u01/recovery/MAA/backupset/2012_12_23/o1_mf_nnndf_TAG20121223T184851_8ffrdmhg_.bkp tag=TAG20121223T184851channel ORA_D

25、ISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:03Finished restore at 23-DEC-2012 19:50:44sql statement: alter database datafile 12 online恢复后数据访问正常：sysMAA> select * from l.luocs;DOMAIN-当然我们要是有镜像备份文件，也可以SWITCHRMAN> backup as copy tablespace ltb;Starting b

26、ackup at 23-DEC-2012 19:54:34using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copyinput datafile file number=00012 name=/u01/app/oracle/oradata/ltb01.dbfoutput file name=/u01/recovery/MAA/datafile/o1_mf_ltb_8ffw7tk0_.dbf tag=TAG20121223T195434 RECID=23 STAMP=802814075channel ORA_DISK_1:

27、 datafile copy complete, elapsed time: 00:00:01Finished backup at 23-DEC-2012 19:54:35在发现只读数据文件损坏的情况下RMAN> run2> sql 'alter database datafile 12 offline'3> switch datafile 12 to datafilecopy '/u01/recovery/MAA/datafile/o1_mf_ltb_8ffw7tk0_.dbf'4> sql 'alter database da

28、tafile 12 online'5> sql statement: alter database datafile 12 offlinedatafile 12 switched to datafile copyinput datafile copy RECID=23 STAMP=802814075 file name=/u01/recovery/MAA/datafile/o1_mf_ltb_8ffw7tk0_.dbfsql statement: alter database datafile 12 online已经成功SWITCH，可查看v$datafile进行确认sysMAA

29、> select checkpoint_change#, name from v$datafile where file#=12;CHECKPOINT_CHANGE# NAME- - 3567837 /u01/recovery/MAA/datafile/o1_mf_ltb_8ffw7tk0_.dbf查看数据也正常sysMAA> select * from l.luocs;DOMAIN-最后别忘了将损坏了SWITCH之前的数据文件进行删除oraclemaa3 $ rm -rf /u01/app/oracle/oradata/ltb01.dbf那有人可能会奇怪，如果直接删除掉只读数据文

30、件，那不也是一样的效果吗？但其实不然删除只读数据文件以模拟丢失oraclemaa3 $ rm -rf /u01/app/oracle/oradata/ltb01.dbfsysMAA> alter system checkpoint;System altered.sysMAA> alter system flush buffer_cache;System altered.sysMAA> select * from l.luocs;DOMAIN-可见我们数据依然正常访问，这里需要了解文件描述符的知识EYGLE的数据安全警示录里有通过文件描述符进行恢复的案例，其中有下面这么一段话：

31、在UNIX、Linux系统中，误删除数据文件后，虽然该文件已从操作系统中删除，但是其文件句柄仍由数据库进程打开持有，所以在数据库层面仍然不会释放其链表信息，因而也就能够从进程的地址信息中，通过复制将其直接恢复。但是请注意，这要求数据库不能中途关闭，如果关闭了数据库，则所有文件句柄被释放，文件就真的难以回归了。下面我从文件描述符中进行数据文件的恢复：oraclemaa3 $ ps -ef | grep dbw | grep -v grepgrid 4817 1 0 Dec19 ? 00:00:00 asm_dbw0_+ASMoracle 6578 1 0 20:39 ? 00:00:00 ora

32、_dbw0_maarootmaa3 # ls /proc/6578/fd0 1 10 11 12 13 14 15 16 17 2 20 21 22 23 24 25 256 257 26 27 28 3 4 5 6 7 8 9rootmaa3 # ls -l /proc/6578/fd/12 lr-x- 1 root root 64 Dec 23 21:05 /proc/6578/fd/12 -> /dev/nullrootmaa3 # ls -l /proc/6578/fd/ | grep oracle lrwx- 1 root root 64 Dec 23 21:05 11 -&g

33、t; /u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_maa.datlr-x- 1 root root 64 Dec 23 21:05 13 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msblrwx- 1 root root 64 Dec 23 21:05 15 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_maa.datlrwx- 1 root root 64 Dec 23 21:05 16 ->

34、/u01/app/oracle/product/11.2.0/dbhome_1/dbs/lkMAAlrwx- 1 root root 64 Dec 23 21:05 17 -> /dev/oracleasm/iid/000000000000000Flrwx- 1 root root 64 Dec 23 21:05 256 -> /u01/app/oracle/oradata/luocs01.dbflrwx- 1 root root 64 Dec 23 21:05 257 -> /u01/app/oracle/oradata/ltb01.dbf (deleted)lr-x- 1

35、 root root 64 Dec 23 21:05 27 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msblrwx- 1 root root 64 Dec 23 21:05 5 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_maa.dat- 可以发现/u01/app/oracle/oradata/ltb01.dbf (deleted)已删除标记我从文件描述符中将文件拷贝到原位置：rootmaa3 # cp /proc/6578/fd/257 /u01

36、/app/oracle/oradata/ltb01.dbf -poraclemaa3 $ ls /u01/app/oracle/oradata/ltb01.dbf-rw-r- 1 oracle asmadmin 52436992 Dec 23 20:50 /u01/app/oracle/oradata/ltb01.dbf重启数据库，正常sysMAA> startup forceORACLE instance started.Total System Global Area 1252663296 bytesFixed Size 2227944 bytesVariable Size 8556

37、38296 bytesDatabase Buffers 385875968 bytesRedo Buffers 8921088 bytesDatabase mounted.Database opened.sysMAA> select checkpoint_change#, name from v$datafile where file#=12;CHECKPOINT_CHANGE# NAME- - 3654318 /u01/app/oracle/oradata/ltb01.dbf数据访问正常sysMAA> select * from l.luocs;DOMAIN-这里之所以不需要RE

38、COVER数据库，是因为我们操作的对象为只读数据文件。如果是读写状态下的数据文件，将文件拷贝过来之后需要先offline datafile后recover datafile，然后再online datafile。另外，只读数据文件删除之后如果重启数据库，依然会报ORA-01110错误，这个在场景2中说明。场景2：控制文件无损，只读数据文件丢失，数据库无法OPEN删除只读数据文件以模拟丢失oraclemaa3 $ rm -rf /u01/app/oracle/oradata/ltb01.dbf数据库启动时报错sysMAA> startup forceORACLE instance star

39、ted.Total System Global Area 1252663296 bytesFixed Size 2227944 bytesVariable Size 855638296 bytesDatabase Buffers 385875968 bytesRedo Buffers 8921088 bytesDatabase mounted.ORA-01157: cannot identify/lock data file 12 - see DBWR trace fileORA-01110: data file 12: '/u01/app/oracle/oradata/ltb01.d

40、bf'只读数据文件恢复时我们不需要recover过程，数据库启动时已经到MOUNT状态，通过RMAN还原该数据文件RMAN> restore tablespace ltb;Starting restore at 23-DEC-2012 18:36:54using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=22 device type=DISKchannel ORA_DISK_1: starting data

41、file backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/ltb01.dbfchannel ORA_DISK_1: reading from backup piece /u01/recovery/MAA/backupset/2012_12_23/o1_mf_nnndf_TAG20121223T182931_8ffq8cbz_.b

42、kpchannel ORA_DISK_1: piece handle=/u01/recovery/MAA/backupset/2012_12_23/o1_mf_nnndf_TAG20121223T182931_8ffq8cbz_.bkp tag=TAG20121223T182931channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01Finished restore at 23-DEC-2012 18:36:57打开数据文件RMAN> a

43、lter database open;database openedsysMAA> select * from luocs;DOMAIN-场景3：控制文件无损，只读数据文件头部损坏我使用BBED工具破坏头部信息oraclemaa3 $ cat bbed.par 12 /u01/app/oracle/oradata/ltb01.dbforaclemaa3 $ cd $ORACLE_HOME/rdbms/liboraclemaa3 lib$ bbed listfile=/home/oracle/bbed.par blocksize=8192 mode=editPassword: BBED:

44、Release 2.0.0.0.0 - Limited Production on Sun Dec 23 21:42:22 2012Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.* ! For Oracle Internal Use only ! *BBED> info File# Name Size(blks) - - - 12 /u01/app/oracle/oradata/ltb01.dbf 0BBED> m /c Warning: contents of previou

45、s BIFILE will be lost. Proceed? (Y/N) Y File: /u01/app/oracle/oradata/ltb01.dbf (12) Block: 1 Offsets: 0 to 511 Dba:0x03000001- 7777772e 6c756f63 732e636f 6d000104 ea850000 00000000 0000200b b90df827 4d414100 00000000 fd140000 00190000 00200000 0c000300 00000000 00000000 54414732 30313231 32323354 3

46、2313234 33320000 00000000 00000000 00000000 00000000 ba163800 00000000 470dda2f 791c352f 01000000 00000000 00000000 00000000 00000000 00000000 03000000 00000000 02000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0000

47、0000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 07000000 03004c54 4200000

48、0 00000000 00000000 00000000 00000000 00000000 00000000 0c000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0

49、0000000 00000000 f5173800 00000000 780dda2f 01000020 5e000000 83010000 1000a600 <32 bytes per line>BBED> sum applyCheck value for File 12, Block 1:current = 0x2a9e, required = 0x2a9eBBED> verifyDBVERIFY - Verification startingFILE = /u01/app/oracle/oradata/ltb01.dbfBLOCK = 1Block 1 is co

50、rruptCorrupt block relative dba: 0x63400001 (file 0, block 1)Bad header found during verificationData in bad block: type: 119 format: 7 rdba: 0x636f756c last change scn: 0x006d.6f632e73 seq: 0x1 flg: 0x04 spare1: 0x77 spare2: 0x2e spare3: 0x0 consistency value in tail: 0x00000b01 check value in bloc

51、k header: 0x2a9e computed block checksum: 0x0DBVERIFY - Verification completeTotal Blocks Examined : 1Total Blocks Processed (Data) : 0Total Blocks Failing (Data) : 0Total Blocks Processed (Index): 0Total Blocks Failing (Index): 0Total Blocks Empty : 0Total Blocks Marked Corrupt : 1Total Blocks Influx : 0Message 531 not found; product=RDBMS; facility=BBED访问v$datafile里查看检查点sysMAA> select checkpoint_change#