一、实验说明：本实验内容转载于luocs大牛的只读数据文件损坏恢复实验记录，非原创，下面的操作属于模拟实验。

      操作系统：rhel 5.4 x32

      数据库：oracle 11g r2

二、只读表空间概念

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

    我们可以通过以下命令在reado only和read write之间进行更改

    read write -> read only

    alter tablespace [tablespace_name] read only;

    read only -> read write

    alter tablespace [tablespace_name] read write;

    查看系统里的只读表空间及其只读数据文件：

1 SQL> col tablespace_name for a10;2 SQL> col file_name for a45;3 SQL> select t.tablespace_name,d.file_id,d.file_name from dba_tablespaces t,dba_data_files d4   2    where t.tablespace_name=d.tablespace_name5   3     and t.status='READ ONLY';6 7 TABLESPACE    FILE_ID FILE_NAME8 ---------- ---------- ---------------------------------------------9 LTB            7 /u01/app/oracle/oradata/ltb01.dbf

三、恢复场景

  1)、控制文件无损，数据库运行时丢失了只读数据文件，访问其内数据报错；

  2)、控制文件无损，只读数据文件丢失，数据库无法OPEN

  3)、控制文件无损，只读数据文件头部损坏；

  4)、控制文件无损，有读写状态时候的备份，后来更改为只读状态，只读状态时数据文件损坏；

  5)、控制文件无损，有只读状态时候的备份，后来更改为读写状态，读写状态时数据文件损坏；

  6)、控制文件损坏，只读数据文件损坏；

  7)、控制文件损坏，有只读数据文件状态时候的控制文件备份，后来数据文件改为读写，读写状态时数据文件损坏；

  8)、控制文件损坏，在线日志文件没有损坏，归档日志丢失，有旧的跟踪控制文件trace，数据库里有只读数据文件。

----前提条件，有相应的备份。

   准备一个实验对象只读表空间：

 

1 SQL> create tablespace ltb datafile '/u01/app/oracle/oradata/ltb01.dbf' size 50m; 2  3 Tablespace created. 4  5 SQL> create user l identified by oracle default tablespace ltb; 6  7 User created. 8  9 SQL> grant resource,connect to l;10 11 Grant succeeded.12 13 SQL> create table l.luocs(domain varchar2(100));14 15 Table created.16 17 SQL> insert into l.luocs values('www.luocs.com');18 19 1 row created.20 21 SQL> commit;22 23 Commit complete.24 25 SQL> alter tablespace ltb read only;26 27 Tablespace altered.

 

四、场景1：控制文件无损，数据库运行时只读数据文件损坏，访问其内数据报错

----备份只读表空间ltb----   1 RMAN> backup tablespace ltb;  2   3 Starting backup at 09-JAN-13  4 using channel ORA_DISK_1  5 channel ORA_DISK_1: starting full datafile backup set  6 channel ORA_DISK_1: specifying datafile(s) in backup set  7 input datafile file number=00007 name=/u01/app/oracle/oradata/ltb01.dbf  8 channel ORA_DISK_1: starting piece 1 at 09-JAN-13  9 channel ORA_DISK_1: finished piece 1 at 09-JAN-13 10 piece handle=/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T131015_8gsyxqp7_.bkp tag=TAG20130109T131015 comment=NONE 11 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 12 Finished backup at 09-JAN-13 13   ----数据库运行状态下，破坏只读数据文件以模拟损坏----  14 [oracle@yft oradata]$ dd if=/dev/zero of=ltb01.dbf bs=10M count=5 15 5+0 records in 16 5+0 records out 17 52428800 bytes (52 MB) copied, 0.13502 seconds, 388 MB/s 18   ----清空缓存----  19 SQL> alter system flush buffer_cache; 20  21 System altered. 22   ----查看v$datafile内容时，返回正常----  23 SQL> select checkpoint_change# from v$datafile where file#=7; 24  25 CHECKPOINT_CHANGE# 26 ------------------ 27         969631 28   ----但是日志内有报错----  29 Hex dump of (file 7, block 1) in trace file /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_4229.trc 30 Corrupt block relative dba: 0x01c00001 (file 7, block 1) 31 Completely zero block found during kcvxfh v8 32 Reading datafile '/u01/app/oracle/oradata/ltb01.dbf' for corruption at rdba: 0x01c00001 (file 7, block 1) 33 Reread (file 7, block 1) found different corrupt data 34 Hex dump of (file 7, block 1) in trace file /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_4229.trc 35 Corrupt block relative dba: 0x01c00001 (file 7, block 1) 36 Completely zero block found during reread 37   ----访问该数据文件的数据：----  38 SQL> select * from l.luocs; 39 select * from l.luocs 40                 * 41 ERROR at line 1: 42 ORA-01578: ORACLE data block corrupted (file # 7, block # 130) 43 ORA-01110: data file 7: '/u01/app/oracle/oradata/ltb01.dbf' 44   ----告警日志也会抛出相应错误信息：----  45 Hex dump of (file 7, block 2) in trace file /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_4229.trc 46 Corrupt block relative dba: 0x01c00002 (file 7, block 2) 47 Completely zero block found during buffer read 48 Reading datafile '/u01/app/oracle/oradata/ltb01.dbf' for corruption at rdba: 0x01c00002 (file 7, block 2) 49 Reread (file 7, block 2) found same corrupt data 50 Wed Jan 09 13:13:15 2013 51 Corrupt Block Found 52          TSN = 8, TSNAME = LTB 53          RFN = 7, BLK = 2, RDBA = 29360130 54          OBJN = -1, OBJD = -1, OBJECT = , SUBOBJECT =  55          SEGMENT OWNER = , SEGMENT TYPE =  56 Errors in file /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_4229.trc  (incident=22960): 57 ORA-01578: ORACLE data block corrupted (file # 7, block # 2) 58 ORA-01110: data file 7: '/u01/app/oracle/oradata/ltb01.dbf' 59 Incident details in: /u01/app/oracle/diag/rdbms/yft/yft/incident/incdir_22960/yft_ora_4229_i22960.trc 60 Hex dump of (file 7, block 1) in trace file /u01/app/oracle/diag/rdbms/yft/yft/incident/incdir_22960/yft_ora_4229_i22960.trc 61 Corrupt block relative dba: 0x00000001 (file 7, block 1) 62 Completely zero block found during validating datafile for block range 63 Reread of blocknum=1, file=/u01/app/oracle/oradata/ltb01.dbf. found same corrupt data 64 Reread of blocknum=1, file=/u01/app/oracle/oradata/ltb01.dbf. found same corrupt data 65 Reread of blocknum=1, file=/u01/app/oracle/oradata/ltb01.dbf. found same corrupt data 66 Reread of blocknum=1, file=/u01/app/oracle/oradata/ltb01.dbf. found same corrupt data 67 Reread of blocknum=1, file=/u01/app/oracle/oradata/ltb01.dbf. found same corrupt data 68 Errors in file /u01/app/oracle/diag/rdbms/yft/yft/incident/incdir_22960/yft_ora_4229_i22960.trc: 69 ORA-19563: datafile header validation failed for file /u01/app/oracle/oradata/ltb01.dbf 70 ORA-01251: Unknown File Header Version read for file number 7 71 ORA-01578: ORACLE data block corrupted (file # 7, block # 2) 72 ORA-01110: data file 7: '/u01/app/oracle/oradata/ltb01.dbf' 73 Wed Jan 09 13:13:18 2013 74 Trace dumping is performing id=[cdmp_20130109131318] 75 Wed Jan 09 13:13:22 2013 76 Sweep [inc][22960]: completed 77 Sweep [inc2][22960]: completed 78   ----但此时只读数据文件状态还是ONLINE----  79 SQL> col file_name for a45; 80 SQL> select file_id, file_name, online_status from dba_data_files where tablespace_name='LTB'; 81  82    FILE_ID FILE_NAME                     ONLINE_ 83 ---------- --------------------------------------------- ------- 84      7 /u01/app/oracle/oradata/ltb01.dbf         ONLINE 85   ----这里恢复需要注意，如果只是有数据讹误块导致数据访问报错，那可以简单通过RMAN的块恢复命令进行块级别恢复。   ----但这里是将整个数据文件破坏了，因此需要RMAN来还原数据文件，恢复可以在数据库OPEN模式进行   ----这个时候直接RESTORE DATAFILE是不可行的，我们需要先将只读数据文件脱机处理，还原之后再ONLINE   ----在这之前必须要删除该数据文件或更名：[oracle@yft oradata]$ mv ltb01.dbf ltb01.dbf.bak，否则将有错误。  86 RMAN> restore tablespace ltb; 87  88 Starting restore at 09-JAN-13 89 using channel ORA_DISK_1 90  91 channel ORA_DISK_1: starting datafile backup set restore 92 channel ORA_DISK_1: specifying datafile(s) to restore from backup set 93 channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ltb01.dbf 94 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T131015_8gsyxqp7_.bkp 95 RMAN-00571: =========================================================== 96 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== 97 RMAN-00571: =========================================================== 98 RMAN-03002: failure of restore command at 01/09/2013 13:13:38 99 ORA-19870: error while restoring backup piece /u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T131015_8gsyxqp7_.bkp100 ORA-19573: cannot obtain exclusive enqueue for datafile 7101   ----正确的做法---- 102 RMAN> run{103 2> sql 'alter database datafile 7 offline';104 3> restore tablespace ltb;105 4> sql 'alter database datafile 7 online';106 5> }107 108 sql statement: alter database datafile 7 offline109 110 Starting restore at 09-JAN-13111 using channel ORA_DISK_1112 113 channel ORA_DISK_1: starting datafile backup set restore114 channel ORA_DISK_1: specifying datafile(s) to restore from backup set115 channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ltb01.dbf116 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T131015_8gsyxqp7_.bkp117 channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T131015_8gsyxqp7_.bkp tag=TAG20130109T131015118 channel ORA_DISK_1: restored backup piece 1119 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03120 Finished restore at 09-JAN-13121 122 sql statement: alter database datafile 7 online123   ---恢复后数据访问正常---- 124 SQL> select * from l.luocs;125 126 DOMAIN127 --------------------------------------------------------------------------------128 www.luocs.com

有人可能会奇怪，如果直接删除掉只读数据文件，那不是也是一样的效果吗？但其实不然

----删除只读数据文件以模拟丢失----  1 [oracle@yft ~]$ rm -rf /u01/app/oracle/oradata/ltb01.dbf 2  3 SQL> alter system checkpoint; 4  5 System altered. 6  7 SQL> alter system flush buffer_cache; 8  9 System altered.10 11 SQL> select * from l.luocs;12 13 DOMAIN14 --------------------------------------------------------------------------------15 www.luocs.com

可见数据依然正常访问，这里需要了解文件描述符的知识。可以参考《误操作删除数据文件恢复案例讨论》，该案例发表于EYGLE的《数据安全警示录》，在本博客中有转载：

五、场景2：控制文件无损，只读数据文件丢失，数据库无法OPEN

----删除只读数据文件以模拟丢失----  1 [oracle@yft ~]$ rm -rf /u01/app/oracle/oradata/ltb01.dbf  2  ----数据库启动时报错----  3 SQL> startup force; 4 ORACLE instance started. 5  6 Total System Global Area  330600448 bytes 7 Fixed Size            1336344 bytes 8 Variable Size          260049896 bytes 9 Database Buffers       62914560 bytes10 Redo Buffers            6299648 bytes11 Database mounted.12 ORA-01157: cannot identify/lock data file 7 - see DBWR trace file13 ORA-01110: data file 7: '/u01/app/oracle/oradata/ltb01.dbf'14  ----只读数据文件恢复时我们不需要recover过程，数据库启动时已经到MOUNT状态，通过RMAN还原数据文件---- 15 RMAN> restore tablespace ltb;16 17 Starting restore at 09-JAN-1318 using target database control file instead of recovery catalog19 allocated channel: ORA_DISK_120 channel ORA_DISK_1: SID=17 device type=DISK21 22 channel ORA_DISK_1: starting datafile backup set restore23 channel ORA_DISK_1: specifying datafile(s) to restore from backup set24 channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ltb01.dbf25 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T063717_8gs7wxlr_.bkp26 channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T063717_8gs7wxlr_.bkp tag=TAG20130109T06371727 channel ORA_DISK_1: restored backup piece 128 channel ORA_DISK_1: restore complete, elapsed time: 00:00:0329 Finished restore at 09-JAN-1330  ----打开数据库---- 31 RMAN> alter database open;32 33 database opened34  ----查看该表空间的数据有没有丢失---- 35 SQL> select * from l.luocs;36 37 DOMAIN38 --------------------------------------------------------------------------------39 www.luocs.com

 六、场景3：控制文件无损，只读数据文件头部损坏

----使用BBED工具破坏头部信息----   1 [oracle@yft lib]$ cat /home/oracle/bbed.par   2 7 /u01/app/oracle/oradata/ltb01.dbf  3   4 [oracle@yft ~]$ cd /u01/app/oracle/product/11.2.0/db_1/rdbms/lib/  5   6 [oracle@yft lib]$ ./bbed listfile=/home/oracle/bbed.par blocksize=8192 mode=edit  7 Password:   8   9 BBED: Release 2.0.0.0.0 - Limited Production on Wed Jan 9 08:19:11 2013 10  11 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. 12  13 ************* !!! For Oracle Internal Use only !!! *************** 14  15 BBED> info 16  File#  Name                                                        Size(blks) 17  -----  ----                                                        ---------- 18      7  /u01/app/oracle/oradata/ltb01.dbf                                    0 19  20 BBED> m /c www.luocs.com 21 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y   22  File: /u01/app/oracle/oradata/ltb01.dbf (7) 23  Block: 1                Offsets:    0 to  511           Dba:0x01c00001 24 ------------------------------------------------------------------------ 25  7777772e 6c756f63 732e636f 6d000104 ec040000 00000000 0000200b e7db3cb0  26  59465400 00000000 04040000 00190000 00200000 07000300 00000000 00000000  27  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  28  00000000 51630d00 00000000 98a5ef2f eaaeed2f 38830b00 00000000 00000000  29  00000000 00000000 00000000 03000000 69c0ef2f 02000000 00000000 00000000  30  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  31  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  32  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  33  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  34  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  35  00000000 00000000 00000000 08000000 03004c54 42000000 00000000 00000000  36  00000000 00000000 00000000 00000000 07000000 00000000 00000000 00000000  37  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  38  20316a29 01000000 00000000 00000000 00000000 00000000 00000000 00000000  39  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  40  00000000 73640d00 00006708 9aa5ef2f 0100e7bf 09000000 be1c0000 1000e7bf  41  42  <32 bytes per line> 43  44 BBED> sum apply 45 Check value for File 7, Block 1: 46 current = 0xc7e5, required = 0xc7e5 47  48 BBED> verify 49 DBVERIFY - Verification starting 50 FILE = /u01/app/oracle/oradata/ltb01.dbf 51 BLOCK = 1 52  53 Block 1 is corrupt 54 Corrupt block relative dba: 0x63400001 (file 0, block 1) 55 Bad header found during verification 56 Data in bad block: 57  type: 119 format: 7 rdba: 0x636f756c 58  last change scn: 0x006d.6f632e73 seq: 0x1 flg: 0x04 59  spare1: 0x77 spare2: 0x2e spare3: 0x0 60  consistency value in tail: 0x00000b01 61  check value in block header: 0xc7e5 62  computed block checksum: 0x0 63  64  65 DBVERIFY - Verification complete 66  67 Total Blocks Examined         : 1 68 Total Blocks Processed (Data) : 0 69 Total Blocks Failing   (Data) : 0 70 Total Blocks Processed (Index): 0 71 Total Blocks Failing   (Index): 0 72 Total Blocks Empty            : 0 73 Total Blocks Marked Corrupt   : 1 74 Total Blocks Influx           : 0 75 Message 531 not found;  product=RDBMS; facility=BBED 76   ----访问v$datafile里查看检查点----  77 SQL> select checkpoint_change# from v$datafile where file#=7; 78  79 CHECKPOINT_CHANGE# 80 ------------------ 81         877683      ----能够正常返回值  82   ----alert告警日志里面有报错：----  83 Hex dump of (file 7, block 1) in trace file /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_5757.trc 84 Corrupt block relative dba: 0x01c00001 (file 7, block 1) 85 Bad header found during kcvxfh v10 86 Data in bad block: 87  type: 119 format: 7 rdba: 0x636f756c 88  last change scn: 0x006d.6f632e73 seq: 0x1 flg: 0x04 89  spare1: 0x77 spare2: 0x2e spare3: 0x0 90  consistency value in tail: 0x00000b01 91  check value in block header: 0xa958 92  computed block checksum: 0x0 93 Reading datafile '/u01/app/oracle/oradata/ltb01.dbf' for corruption at rdba: 0x01c00001 (file 7, block 1) 94 Reread (file 7, block 1) found same corrupt data 95   ----这时候数据库启动，会收到头部损坏错误----  96 SQL> startup force; 97 ORACLE instance started. 98  99 Total System Global Area  330600448 bytes100 Fixed Size            1336344 bytes101 Variable Size          260049896 bytes102 Database Buffers       62914560 bytes103 Redo Buffers            6299648 bytes104 Database mounted.105 ORA-01122: database file 7 failed verification check106 ORA-01110: data file 7: '/u01/app/oracle/oradata/ltb01.dbf'107 ORA-01210: data file header is media corrupt  ----进行恢复---- 108 RMAN> run{109 2> startup force mount;110 3> restore tablespace ltb;111 4> alter database open;112 5> }113 114 Oracle instance started115 database mounted116 117 Total System Global Area     330600448 bytes118 119 Fixed Size                     1336344 bytes120 Variable Size                260049896 bytes121 Database Buffers              62914560 bytes122 Redo Buffers                   6299648 bytes123 124 Starting restore at 09-JAN-13125 using target database control file instead of recovery catalog126 allocated channel: ORA_DISK_1127 channel ORA_DISK_1: SID=18 device type=DISK128 129 channel ORA_DISK_1: starting datafile backup set restore130 channel ORA_DISK_1: specifying datafile(s) to restore from backup set131 channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ltb01.dbf132 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T063717_8gs7wxlr_.bkp133 channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T063717_8gs7wxlr_.bkp tag=TAG20130109T063717134 channel ORA_DISK_1: restored backup piece 1135 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03136 Finished restore at 09-JAN-13137 138 database opened139   ----查看恢复后表空间内表的信息---- 140 SQL> select * from l.luocs;141 142 DOMAIN143 --------------------------------------------------------------------------------144 www.luocs.com

备注：关于bbed的使用可以参考：

七、场景4：控制文件无损，有读写状态时候的备份，后来更改为只读状态，只读状态时数据文件损坏

----首先重新构造一个环境----   1 SQL> select * from l.luocs;  2   3 DOMAIN  4 --------------------------------------------------------------------------------  5 www.luocs.com  6   ----将表空间恢复到读写状态----   7 SQL> alter tablespace ltb read write;  8   9 Tablespace altered. 10   ----将当前处于读写状态的数据文件备份----  11 RMAN> backup tablespace ltb; 12  13 Starting backup at 09-JAN-13 14 using channel ORA_DISK_1 15 channel ORA_DISK_1: starting full datafile backup set 16 channel ORA_DISK_1: specifying datafile(s) in backup set 17 input datafile file number=00007 name=/u01/app/oracle/oradata/ltb01.dbf 18 channel ORA_DISK_1: starting piece 1 at 09-JAN-13 19 channel ORA_DISK_1: finished piece 1 at 09-JAN-13 20 piece handle=/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T084414_8gshbyl1_.bkp tag=TAG20130109T084414 comment=NONE 21 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 22 Finished backup at 09-JAN-13 23   ----又产生一些数据----  24 SQL> insert into l.luocs values('www.jack.com'); 25  26 1 row created. 27  28 SQL> commit; 29  30 Commit complete. 31  32 SQL> select * from l.luocs; 33  34 DOMAIN 35 -------------------------------------------------------------------------------- 36 www.jack.com 37 www.luocs.com 38   ----将表空间更改为只读状态----  39 SQL> alter tablespace ltb read only; 40  41 Tablespace altered. 42   ----对只读数据文件进行损坏----  43 [oracle@yft oradata]$ dd if=/dev/zero of=ltb01.dbf bs=1M count=1 44 1+0 records in 45 1+0 records out 46 1048576 bytes (1.0 MB) copied, 0.00495065 seconds, 212 MB/s 47 [oracle@yft oradata]$ ll 48 total 1032 49 -rw-r----- 1 oracle oinstall 1048576 Jan  9 08:56 ltb01.dbf 50 drwxr-x--- 2 oracle oinstall    4096 Jan  9 06:24 yft 51   ----退出sqlplus，再进去查看只读数据文件的SCN----  52 SQL> exit 53 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production 54 With the Partitioning, OLAP, Data Mining and Real Application Testing options 55 [oracle@yft lib]$ rlwrap sqlplus /nolog 56  57 SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 9 08:56:28 2013 58  59 Copyright (c) 1982, 2009, Oracle.  All rights reserved. 60  61 SQL> conn /as sysdba 62 Connected. 63 SQL> select checkpoint_change# from v$datafile where file#=7; 64  65 CHECKPOINT_CHANGE# 66 ------------------ 67         942014 68   ----在alert日志中发现对该文件损坏的记录----  69 Wed Jan 09 09:02:22 2013 70 Hex dump of (file 7, block 1) in trace file /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_6179.trc 71 Corrupt block relative dba: 0x01c00001 (file 7, block 1) 72 Completely zero block found during kcvxfh v8 73 Reading datafile '/u01/app/oracle/oradata/ltb01.dbf' for corruption at rdba: 0x01c00001 (file 7, block 1) 74 Reread (file 7, block 1) found different corrupt data 75 Hex dump of (file 7, block 1) in trace file /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_6179.trc 76 Corrupt block relative dba: 0x01c00001 (file 7, block 1) 77 Completely zero block found during reread 78   ----清空缓存----  79 SQL> alter system flush buffer_cache; 80  81 System altered. 82   ----查询l.luocs表报错----  83 SQL> select * from l.luocs; 84 select * from l.luocs 85                 * 86 ERROR at line 1: 87 ORA-01115: IO error reading block from file  (block # ) 88 ORA-01110: data file 7: '/u01/app/oracle/oradata/ltb01.dbf' 89 ORA-27072: File I/O error 90 Linux Error: 2: No such file or directory 91 Additional information: 4 92 Additional information: 130 93   ----但是该只读数据文件依然是在线的----  94 SQL> col file_name for a35; 95 SQL> select file_id,file_name,online_status from dba_data_files where tablespace_name='LTB'; 96  97    FILE_ID FILE_NAME              ONLINE_ 98 ---------- ------------------------------ ------- 99      7 /u01/app/oracle/oradata/ltb01.dbf ONLINE  ----进行恢复过程如下：---- 100 RMAN> run{101 2> sql 'alter database datafile 7 offline';102 3> restore datafile 7 force;103 4> recover datafile 7;104 5> sql 'alter database datafile 7 online';105 6> }106 107 using target database control file instead of recovery catalog108 sql statement: alter database datafile 7 offline109 110 Starting restore at 09-JAN-13111 allocated channel: ORA_DISK_1112 channel ORA_DISK_1: SID=38 device type=DISK113 114 channel ORA_DISK_1: starting datafile backup set restore115 channel ORA_DISK_1: specifying datafile(s) to restore from backup set116 channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ltb01.dbf117 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T084414_8gshbyl1_.bkp118 channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T084414_8gshbyl1_.bkp tag=TAG20130109T084414119 channel ORA_DISK_1: restored backup piece 1120 channel ORA_DISK_1: restore complete, elapsed time: 00:00:04121 Finished restore at 09-JAN-13122 123 Starting recover at 09-JAN-13124 using channel ORA_DISK_1125 126 starting media recovery127 media recovery complete, elapsed time: 00:00:00128 129 Finished recover at 09-JAN-13130 131 sql statement: alter database datafile 7 online132   ----验证恢复---- 133 SQL> select * from l.luocs;134 135 DOMAIN136 --------------------------------------------------------------------------------137 www.jack.com138 www.luocs.com139 140

 说明：

在第三步restore datafile 7 force(注：这里加force关键字是为应付特殊环境的，比如虽然文件受损却物理上依然存在的情况等)还原之后，7号数据文件头部具有读写文件特征，但控制文件和数据字典SYS.TS$上描述7号数据文件是只读的，所以需要增加第四步recover过程，对7号数据文件应该重做日志，一直到文件头部更改为只读状态。

八、场景5：控制文件无损，有只读状态时候的备份，后来更改为读写状态，读写状态时数据文件损坏

----场景5和场景4相比，发生环境有所改变，但其恢复过程却一样，如下：----  1 SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='LTB'; 2  3 TABLESPACE_NAME            STATUS 4 ------------------------------ --------- 5 LTB                   READ ONLY 6  ----对只读状态的表空间备份----  7 RMAN> backup tablespace ltb; 8  9 Starting backup at 09-JAN-1310 using channel ORA_DISK_111 channel ORA_DISK_1: starting full datafile backup set12 channel ORA_DISK_1: specifying datafile(s) in backup set13 input datafile file number=00007 name=/u01/app/oracle/oradata/ltb01.dbf14 channel ORA_DISK_1: starting piece 1 at 09-JAN-1315 channel ORA_DISK_1: finished piece 1 at 09-JAN-1316 piece handle=/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T123951_8gsx4qrg_.bkp tag=TAG20130109T123951 comment=NONE17 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:0118 Finished backup at 09-JAN-1319  ----之后将数据文件更改为读写状态，并产生一些数据---- 20 SQL> alter tablespace ltb read write;21 22 Tablespace altered.23 24 SQL> insert into l.luocs values ('www.luocs.com');25 26 1 row created.27 28 SQL> commit;29 30 Commit complete.31 32 SQL> select * from l.luocs;33 34 DOMAIN35 --------------------------------------------------------------------------------36 www.luocs.com37 www.jack.com38 www.luocs.com39  ----破坏数据文件---- 40 [oracle@yft oradata]$ dd if=/dev/zero of=ltb01.dbf bs=1M count=5041 50+0 records in42 50+0 records out43 52428800 bytes (52 MB) copied, 0.200443 seconds, 262 MB/s44  ----清空缓冲，要不在alter日志中发现不了错误---- 45 SQL> alter system flush buffer_cache;46 47 System altered.48  ----查找该数据文件上的表时报错---- 49 SQL> select * from l.luocs;50 select * from l.luocs51 *52 ERROR at line 1:53 ORA-01578: ORACLE data block corrupted (file # 7, block # 130)54 ORA-01110: data file 7: '/u01/app/oracle/oradata/ltb01.dbf'55  ----删除或者更名数据文件，要不下面恢复操作会报错：ORA-01135和ORA-01110错误---- 56 [oracle@yft oradata]$ mv ltb01.dbf ltb01.dbf.bak57  ----恢复过程如场景4一样---- 58 RMAN> run{  59 2> sql 'alter database datafile 7 offline';60 3> restore datafile 7 force;61 4> recover datafile 7;62 5> sql 'alter database datafile 7 online';63 6> }

 说明：

在第三步restore datafile还原之后，7号数据文件头部标识着文件只读状态，但控制文件和数据字典SYS.TS$上却描述该数据文件是读写的，所以需要增加第四步recover过程，对7号数据文件应用重做日志，使其正常恢复。

九、场景6：控制文件损坏，只读数据文件损坏

----查看当前LTB表空间状态，发现是读写状态----  1 SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='LTB'; 2  3 TABLESPACE_NAME            STATUS 4 ------------------------------ --------- 5 LTB                   ONLINE 6  ----将其改为只读状态----  7 SQL> alter tablespace ltb read only; 8  9 Tablespace altered.10 ----在当前状态下做一个全备份---- 11 RMAN> backup database;12 13 Starting backup at 09-JAN-1314 using channel ORA_DISK_115 channel ORA_DISK_1: starting full datafile backup set16 channel ORA_DISK_1: specifying datafile(s) in backup set17 。。。部分内容略。。。18 piece handle=/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T193306_8gtoclvj_.bkp tag=TAG20130109T193306 comment=NONE19 channel ORA_DISK_1: backup set complete, elapsed time: 00:01:4620 channel ORA_DISK_1: starting full datafile backup set21 channel ORA_DISK_1: specifying datafile(s) in backup set22 including current control file in backup set23 including current SPFILE in backup set24 channel ORA_DISK_1: starting piece 1 at 09-JAN-1325 channel ORA_DISK_1: finished piece 1 at 09-JAN-1326 piece handle=/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_ncsnf_TAG20130109T193306_8gtogxyx_.bkp tag=TAG20130109T193306 comment=NONE27 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:0228 Finished backup at 09-JAN-1329  ----强制关闭数据库---- 30 SQL> shutdown abort;31 ORACLE instance shut down.32  ----模拟控制文件丢失---- 33 [oracle@yft yft]$ rm -rf /u01/app/oracle/oradata/yft/control01.ctl 34 [oracle@yft oradata]$ rm -rf /u01/app/oracle/flash_recovery_area/yft/control02.ctl 35 36  ----数据文件损坏---- 37 [oracle@yft oradata]$ dd if=/dev/zero of=/u01/app/oracle/oradata/ltb01.dbf bs=1M count=50;38 50+0 records in39 50+0 records out40 52428800 bytes (52 MB) copied, 0.179258 seconds, 292 MB/s41  ----启动数据库，报ORA-00205错误---- 42 SQL> startup43 ORACLE instance started.44 45 Total System Global Area  330600448 bytes46 Fixed Size            1336344 bytes47 Variable Size          255855592 bytes48 Database Buffers       67108864 bytes49 Redo Buffers            6299648 bytes50 ORA-00205: error in identifying control file, check alert log for more info

这时候恢复过程如下：

1)、将数据库启动到NOMOUNT状态(也只能如此)；

2)、从备份里还原控制文件；

3)、将数据库启动到MOUNT状态；

4)、还原只读数据文件；

5)、recover恢复整个数据库；

6)、resetlogs打开数据库。

----在刚才启动时候已经处于NOMOUNT状态----  1 RMAN> run { 2 2> restore controlfile from '/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_ncsnf_TAG20130109T193306_8gtogxyx_.bkp'; 3 3> alter database mount; 4 4> restore datafile 7 force; 5 5> recover database; 6 6> alter database open resetlogs; 7 7> } 8  9 Starting restore at 09-JAN-1310 using target database control file instead of recovery catalog11 allocated channel: ORA_DISK_112 channel ORA_DISK_1: SID=20 device type=DISK13 14 channel ORA_DISK_1: restoring control file15 channel ORA_DISK_1: restore complete, elapsed time: 00:00:0116 output file name=/u01/app/oracle/oradata/yft/control01.ctl17 output file name=/u01/app/oracle/flash_recovery_area/yft/control02.ctl18 Finished restore at 09-JAN-1319 20 database mounted21 released channel: ORA_DISK_122 23 Starting restore at 09-JAN-1324 Starting implicit crosscheck backup at 09-JAN-1325 allocated channel: ORA_DISK_126 channel ORA_DISK_1: SID=20 device type=DISK27 Crosschecked 1 objects28 Finished implicit crosscheck backup at 09-JAN-1329 30 Starting implicit crosscheck copy at 09-JAN-1331 using channel ORA_DISK_132 Finished implicit crosscheck copy at 09-JAN-1333 34 searching for all files in the recovery area35 cataloging files...36 cataloging done37 38 List of Cataloged Files39 =======================40 File Name: /u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_ncsnf_TAG20130109T193306_8gtogxyx_.bkp41 42 using channel ORA_DISK_143 44 channel ORA_DISK_1: starting datafile backup set restore45 channel ORA_DISK_1: specifying datafile(s) to restore from backup set46 channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ltb01.dbf47 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T193306_8gtoclvj_.bkp48 channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T193306_8gtoclvj_.bkp tag=TAG20130109T19330649 channel ORA_DISK_1: restored backup piece 150 channel ORA_DISK_1: restore complete, elapsed time: 00:00:0351 Finished restore at 09-JAN-1352 53 Starting recover at 09-JAN-1354 using channel ORA_DISK_155 datafile 7 not processed because file is read-only56 57 starting media recovery58 59 archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/oradata/yft/redo01.log60 archived log file name=/u01/app/oracle/oradata/yft/redo01.log thread=1 sequence=1361 media recovery complete, elapsed time: 00:00:0162 Finished recover at 09-JAN-1363 64 database opened65  ----恢复完成，数据也访问正常---- 66 SQL> select * from l.luocs;67 68 DOMAIN69 --------------------------------------------------------------------------------70 www.luocs.com71 www.jack.com72 www.luocs.com

十、场景7：控制文件损坏，有只读数据文件状态时候的控制文件备份，后来数据文件改为读写，读写状态时数据文件损坏

----这次比场景6稍微复杂点的情况：当前表空间的状态为只读----  1 SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='LTB'; 2  3 TABLESPACE_NAME            STATUS 4 ------------------------------ --------- 5 LTB                   READ ONLY 6  ----在当前状态做一个全备份----  7 RMAN> backup database; 8  ----之后只读数据文件改为读写状态，并产生一些数据：----  9 SQL> alter tablespace ltb read write;10 11 Tablespace altered.12 13 SQL> insert into l.luocs values('www.jack.com');14 15 1 row created.16 17 SQL> commit;18 19 Commit complete.20 21 SQL> select * from l.luocs;22 23 DOMAIN24 --------------------------------------------------------------------------------25 www.luocs.com26 www.jack.com27 www.luocs.com28 www.jack.com29  ----这时控制文件、数据文件都损坏---- 30 SQL> shutdown abort;31 ORACLE instance shut down.32 33 [oracle@yft ~]$ rm -rf /u01/app/oracle/flash_recovery_area/yft/control02.ctl34 [oracle@yft ~]$ rm -rf /u01/app/oracle/oradata/yft/control01.ctl35 [oracle@yft ~]$ dd if=/dev/zero of=/u01/app/oracle/oradata/ltb01.dbf bs=1M count=50;36 50+0 records in37 50+0 records out38 52428800 bytes (52 MB) copied, 0.364255 seconds, 144 MB/s39 [oracle@yft ~]$ rm -rf /u01/app/oracle/oradata/ltb01.dbf 40  ----数据库启动，报ORA-00205---- 41 SQL> startup42 ORACLE instance started.43 44 Total System Global Area  330600448 bytes45 Fixed Size            1336344 bytes46 Variable Size          260049896 bytes47 Database Buffers       62914560 bytes48 Redo Buffers            6299648 bytes49 ORA-00205: error in identifying control file, check alert log for more info50  ----先尝试按场景6的恢复方法进行恢复---- 51 RMAN> run{52 2> restore controlfile from '/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_ncsnf_TAG20130109T200931_8gtqohth_.bk;53 3> alter database mount;54 4> restore datafile 7 force;55 5> recover database;56 6> alter database open resetlogs;57 7> }58 59 。。。部分信息略。。。 Finished restore at 09-JAN-1360 61 Starting recover at 09-JAN-1362 using channel ORA_DISK_163 datafile 7 not processed because file is read-only64 65 starting media recovery66 67 archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/oradata/yft/redo01.log68 archived log file name=/u01/app/oracle/oradata/yft/redo01.log thread=1 sequence=169 Oracle Error: 70 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below71 ORA-01190: control file or data file 7 is from before the last RESETLOGS72 ORA-01110: data file 7: '/u01/app/oracle/oradata/ltb01.dbf'73 74 media recovery complete, elapsed time: 00:00:0175 Finished recover at 09-JAN-1376 77 RMAN-00571: ===========================================================78 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============79 RMAN-00571: ===========================================================80 RMAN-03002: failure of alter db command at 01/09/2013 20:16:3781 ORA-01190: control file or data file 7 is from before the last RESETLOGS82 ORA-01110: data file 7: '/u01/app/oracle/oradata/ltb01.dbf'

在上面输出信息中我们可以看到datafile 7 not processed because file is read-only一句，这是因为在还原的控制文件内7号数据文件被描述为只读文件，因此在recover database的时候被忽略。但在应用重做日志的时候，发现LTB表空间更改为读写状态的记录，因此也需要进行恢复，但此时为时已晚，因此最后报错。那这时候到什么阶段了？控制文件和数据字典上对7号数据文件的描述已改变，也就是已将其认为是读写状态了。

解决方法是，再重复一下recover database过程。

----查看一下当前数据的状态，处于mount状态下，再重启到nomount下----  1 SQL> select status from v$instance; 2  3 STATUS 4 ------------ 5 MOUNTED 6  7 SQL> shutdown immediate; 8 ORA-01109: database not open 9 10 11 Database dismounted.12 ORACLE instance shut down.13 SQL> startup nomount;14 ORACLE instance started.15 16 Total System Global Area  330600448 bytes17 Fixed Size            1336344 bytes18 Variable Size          260049896 bytes19 Database Buffers       62914560 bytes20 Redo Buffers            6299648 bytes ----进行恢复---- 21 RMAN> run {22 2> restore controlfile from '/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_ncsnf_TAG20130109T200931_8gtqohth_.bk23 3> alter database mount;24 4> restore datafile 7 force;25 5> recover database;26 6> recover database;27 7> alter database open resetlogs;28 8> }29 30 。。。部分内容略。。。31 Starting recover at 09-JAN-1332 using channel ORA_DISK_133 datafile 7 not processed because file is read-only34 35 starting media recovery36 37 archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/oradata/yft/redo01.log38 archived log file name=/u01/app/oracle/oradata/yft/redo01.log thread=1 sequence=139 Oracle Error: 40 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below41 ORA-01190: control file or data file 7 is from before the last RESETLOGS42 ORA-01110: data file 7: '/u01/app/oracle/oradata/ltb01.dbf'43 44 media recovery complete, elapsed time: 00:00:0045 Finished recover at 09-JAN-1346 47 Starting recover at 09-JAN-1348 using channel ORA_DISK_149 50 starting media recovery51 52 archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/oradata/yft/redo01.log53 archived log file name=/u01/app/oracle/oradata/yft/redo01.log thread=1 sequence=154 media recovery complete, elapsed time: 00:00:0055 Finished recover at 09-JAN-1356 57 database opened ----顺利恢复完成，数据访问正常---- 58 SQL> select * from l.luocs;59 60 DOMAIN61 --------------------------------------------------------------------------------62 www.luocs.com63 www.jack.com64 www.luocs.com65 www.jack.com

十一、场景8：控制文件损坏，在线日志文件没有损坏，归档日志丢失，有旧的跟踪控制文件trace，数据库里有只读数据文件

该实验可以查看：