只读数据文件损坏恢复
一、实验说明：本实验内容转载于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 d 4 2 where t.tablespace_name=d.tablespace_name 5 3 and t.status='READ ONLY'; 6 7 TABLESPACE FILE_ID FILE_NAME 8 ---------- ---------- --------------------------------------------- 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_.bkp 100 ORA-19573: cannot obtain exclusive enqueue for datafile 7 101 ----正确的做法----
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 offline 109 110 Starting restore at 09-JAN-13 111 using channel ORA_DISK_1 112 113 channel ORA_DISK_1: starting datafile backup set restore 114 channel ORA_DISK_1: specifying datafile(s) to restore from backup set 115 channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ltb01.dbf 116 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 117 channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T131015_8gsyxqp7_.bkp tag=TAG20130109T131015 118 channel ORA_DISK_1: restored backup piece 1 119 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 120 Finished restore at 09-JAN-13 121 122 sql statement: alter database datafile 7 online 123 ---恢复后数据访问正常----
124 SQL> select * from l.luocs; 125 126 DOMAIN 127 -------------------------------------------------------------------------------- 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 DOMAIN 14 -------------------------------------------------------------------------------- 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 bytes 10 Redo Buffers 6299648 bytes 11 Database mounted. 12 ORA-01157: cannot identify/lock data file 7 - see DBWR trace file 13 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-13 18 using target database control file instead of recovery catalog 19 allocated channel: ORA_DISK_1 20 channel ORA_DISK_1: SID=17 device type=DISK 21 22 channel ORA_DISK_1: starting datafile backup set restore 23 channel ORA_DISK_1: specifying datafile(s) to restore from backup set 24 channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ltb01.dbf 25 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T063717_8gs7wxlr_.bkp 26 channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T063717_8gs7wxlr_.bkp tag=TAG20130109T063717 27 channel ORA_DISK_1: restored backup piece 1 28 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 29 Finished restore at 09-JAN-13 30 ----打开数据库----
31 RMAN> alter database open; 32 33 database opened 34 ----查看该表空间的数据有没有丢失----
35 SQL> select * from l.luocs; 36 37 DOMAIN 38 -------------------------------------------------------------------------------- 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 bytes 100 Fixed Size 1336344 bytes 101 Variable Size 260049896 bytes 102 Database Buffers 62914560 bytes 103 Redo Buffers 6299648 bytes 104 Database mounted. 105 ORA-01122: database file 7 failed verification check 106 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 started 115 database mounted 116 117 Total System Global Area 330600448 bytes 118 119 Fixed Size 1336344 bytes 120 Variable Size 260049896 bytes 121 Database Buffers 62914560 bytes 122 Redo Buffers 6299648 bytes 123 124 Starting restore at 09-JAN-13 125 using target database control file instead of recovery catalog 126 allocated channel: ORA_DISK_1 127 channel ORA_DISK_1: SID=18 device type=DISK 128 129 channel ORA_DISK_1: starting datafile backup set restore 130 channel ORA_DISK_1: specifying datafile(s) to restore from backup set 131 channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ltb01.dbf 132 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T063717_8gs7wxlr_.bkp 133 channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T063717_8gs7wxlr_.bkp tag=TAG20130109T063717 134 channel ORA_DISK_1: restored backup piece 1 135 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 136 Finished restore at 09-JAN-13 137 138 database opened 139 ----查看恢复后表空间内表的信息----
140 SQL> select * from l.luocs; 141 142 DOMAIN 143 -------------------------------------------------------------------------------- 144 www.luocs.com
备注：关于bbed的使用可以参考： 《Oracle 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 catalog 108 sql statement: alter database datafile 7 offline 109 110 Starting restore at 09-JAN-13 111 allocated channel: ORA_DISK_1 112 channel ORA_DISK_1: SID=38 device type=DISK 113 114 channel ORA_DISK_1: starting datafile backup set restore 115 channel ORA_DISK_1: specifying datafile(s) to restore from backup set 116 channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ltb01.dbf 117 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T084414_8gshbyl1_.bkp 118 channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T084414_8gshbyl1_.bkp tag=TAG20130109T084414 119 channel ORA_DISK_1: restored backup piece 1 120 channel ORA_DISK_1: restore complete, elapsed time: 00:00:04 121 Finished restore at 09-JAN-13 122 123 Starting recover at 09-JAN-13 124 using channel ORA_DISK_1 125 126 starting media recovery 127 media recovery complete, elapsed time: 00:00:00 128 129 Finished recover at 09-JAN-13 130 131 sql statement: alter database datafile 7 online 132 ----验证恢复----
133 SQL> select * from l.luocs; 134 135 DOMAIN 136 -------------------------------------------------------------------------------- 137 www.jack.com 138 www.luocs.com 139 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-13 10 using channel ORA_DISK_1 11 channel ORA_DISK_1: starting full datafile backup set 12 channel ORA_DISK_1: specifying datafile(s) in backup set 13 input datafile file number=00007 name=/u01/app/oracle/oradata/ltb01.dbf 14 channel ORA_DISK_1: starting piece 1 at 09-JAN-13 15 channel ORA_DISK_1: finished piece 1 at 09-JAN-13 16 piece handle=/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T123951_8gsx4qrg_.bkp tag=TAG20130109T123951 comment=NONE 17 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 18 Finished backup at 09-JAN-13 19 ----之后将数据文件更改为读写状态，并产生一些数据----
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 DOMAIN 35 -------------------------------------------------------------------------------- 36 www.luocs.com 37 www.jack.com 38 www.luocs.com 39 ----破坏数据文件----
40 [oracle@yft oradata]$ dd if=/dev/zero of=ltb01.dbf bs=1M count=50 41 50+0 records in 42 50+0 records out 43 52428800 bytes (52 MB) copied, 0.200443 seconds, 262 MB/s 44 ----清空缓冲，要不在alter日志中发现不了错误----
45 SQL> alter system flush buffer_cache; 46 47 System altered. 48 ----查找该数据文件上的表时报错----
49 SQL> select * from l.luocs; 50 select * from l.luocs 51 * 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.bak 57 ----恢复过程如场景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-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 。。。部分内容略。。。 18 piece handle=/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T193306_8gtoclvj_.bkp tag=TAG20130109T193306 comment=NONE 19 channel ORA_DISK_1: backup set complete, elapsed time: 00:01:46 20 channel ORA_DISK_1: starting full datafile backup set 21 channel ORA_DISK_1: specifying datafile(s) in backup set 22 including current control file in backup set 23 including current SPFILE in backup set 24 channel ORA_DISK_1: starting piece 1 at 09-JAN-13 25 channel ORA_DISK_1: finished piece 1 at 09-JAN-13 26 piece handle=/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_ncsnf_TAG20130109T193306_8gtogxyx_.bkp tag=TAG20130109T193306 comment=NONE 27 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 28 Finished backup at 09-JAN-13 29 ----强制关闭数据库----
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 in 39 50+0 records out 40 52428800 bytes (52 MB) copied, 0.179258 seconds, 292 MB/s 41 ----启动数据库，报ORA-00205错误----
42 SQL> startup 43 ORACLE instance started. 44 45 Total System Global Area 330600448 bytes 46 Fixed Size 1336344 bytes 47 Variable Size 255855592 bytes 48 Database Buffers 67108864 bytes 49 Redo Buffers 6299648 bytes 50 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-13 10 using target database control file instead of recovery catalog 11 allocated channel: ORA_DISK_1 12 channel ORA_DISK_1: SID=20 device type=DISK 13 14 channel ORA_DISK_1: restoring control file 15 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 16 output file name=/u01/app/oracle/oradata/yft/control01.ctl 17 output file name=/u01/app/oracle/flash_recovery_area/yft/control02.ctl 18 Finished restore at 09-JAN-13 19 20 database mounted 21 released channel: ORA_DISK_1 22 23 Starting restore at 09-JAN-13 24 Starting implicit crosscheck backup at 09-JAN-13 25 allocated channel: ORA_DISK_1 26 channel ORA_DISK_1: SID=20 device type=DISK 27 Crosschecked 1 objects 28 Finished implicit crosscheck backup at 09-JAN-13 29 30 Starting implicit crosscheck copy at 09-JAN-13 31 using channel ORA_DISK_1 32 Finished implicit crosscheck copy at 09-JAN-13 33 34 searching for all files in the recovery area 35 cataloging files... 36 cataloging done 37 38 List of Cataloged Files 39 ======================= 40 File Name: /u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_ncsnf_TAG20130109T193306_8gtogxyx_.bkp 41 42 using channel ORA_DISK_1 43 44 channel ORA_DISK_1: starting datafile backup set restore 45 channel ORA_DISK_1: specifying datafile(s) to restore from backup set 46 channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ltb01.dbf 47 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T193306_8gtoclvj_.bkp 48 channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_09/o1_mf_nnndf_TAG20130109T193306_8gtoclvj_.bkp tag=TAG20130109T193306 49 channel ORA_DISK_1: restored backup piece 1 50 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 51 Finished restore at 09-JAN-13 52 53 Starting recover at 09-JAN-13 54 using channel ORA_DISK_1 55 datafile 7 not processed because file is read-only 56 57 starting media recovery 58 59 archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/oradata/yft/redo01.log 60 archived log file name=/u01/app/oracle/oradata/yft/redo01.log thread=1 sequence=13 61 media recovery complete, elapsed time: 00:00:01 62 Finished recover at 09-JAN-13 63 64 database opened 65 ----恢复完成，数据也访问正常----
66 SQL> select * from l.luocs; 67 68 DOMAIN 69 -------------------------------------------------------------------------------- 70 www.luocs.com 71 www.jack.com 72 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 DOMAIN 24 -------------------------------------------------------------------------------- 25 www.luocs.com 26 www.jack.com 27 www.luocs.com 28 www.jack.com 29 ----这时控制文件、数据文件都损坏----
30 SQL> shutdown abort; 31 ORACLE instance shut down. 32 33 [oracle@yft ~]$ rm -rf /u01/app/oracle/flash_recovery_area/yft/control02.ctl 34 [oracle@yft ~]$ rm -rf /u01/app/oracle/oradata/yft/control01.ctl 35 [oracle@yft ~]$ dd if=/dev/zero of=/u01/app/oracle/oradata/ltb01.dbf bs=1M count=50; 36 50+0 records in 37 50+0 records out 38 52428800 bytes (52 MB) copied, 0.364255 seconds, 144 MB/s 39 [oracle@yft ~]$ rm -rf /u01/app/oracle/oradata/ltb01.dbf 40 ----数据库启动，报ORA-00205----
41 SQL> startup 42 ORACLE instance started. 43 44 Total System Global Area 330600448 bytes 45 Fixed Size 1336344 bytes 46 Variable Size 260049896 bytes 47 Database Buffers 62914560 bytes 48 Redo Buffers 6299648 bytes 49 ORA-00205: error in identifying control file, check alert log for more info 50 ----先尝试按场景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-13 60 61 Starting recover at 09-JAN-13 62 using channel ORA_DISK_1 63 datafile 7 not processed because file is read-only 64 65 starting media recovery 66 67 archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/oradata/yft/redo01.log 68 archived log file name=/u01/app/oracle/oradata/yft/redo01.log thread=1 sequence=1 69 Oracle Error: 70 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below 71 ORA-01190: control file or data file 7 is from before the last RESETLOGS 72 ORA-01110: data file 7: '/u01/app/oracle/oradata/ltb01.dbf' 73 74 media recovery complete, elapsed time: 00:00:01 75 Finished recover at 09-JAN-13 76 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:37 81 ORA-01190: control file or data file 7 is from before the last RESETLOGS 82 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 bytes 17 Fixed Size 1336344 bytes 18 Variable Size 260049896 bytes 19 Database Buffers 62914560 bytes 20 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_.bk 23 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-13 32 using channel ORA_DISK_1 33 datafile 7 not processed because file is read-only 34 35 starting media recovery 36 37 archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/oradata/yft/redo01.log 38 archived log file name=/u01/app/oracle/oradata/yft/redo01.log thread=1 sequence=1 39 Oracle Error: 40 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below 41 ORA-01190: control file or data file 7 is from before the last RESETLOGS 42 ORA-01110: data file 7: '/u01/app/oracle/oradata/ltb01.dbf' 43 44 media recovery complete, elapsed time: 00:00:00 45 Finished recover at 09-JAN-13 46 47 Starting recover at 09-JAN-13 48 using channel ORA_DISK_1 49 50 starting media recovery 51 52 archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/oradata/yft/redo01.log 53 archived log file name=/u01/app/oracle/oradata/yft/redo01.log thread=1 sequence=1 54 media recovery complete, elapsed time: 00:00:00 55 Finished recover at 09-JAN-13 56 57 database opened ----顺利恢复完成，数据访问正常----
58 SQL> select * from l.luocs; 59 60 DOMAIN 61 -------------------------------------------------------------------------------- 62 www.luocs.com 63 www.jack.com 64 www.luocs.com 65 www.jack.com
十一、场景8：控制文件损坏，在线日志文件没有损坏，归档日志丢失，有旧的跟踪控制文件trace，数据库里有只读数据文件
该实验可以查看： 《丢失控制文件恢复实验记录--6(实验4的基础上，如果luocs表空间为只读表空间的情况)》
