只读数据文件损坏恢复

一、实验说明：本实验内容转载于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表空间为只读表空间的情况)》

posted @ 2013-01-09 09:35 I’m Me! 阅读(2766) 评论(0) 编辑收藏举报

刷新页面返回顶部

I’m Me!

Work hard,Learn hard!

只读数据文件损坏恢复

公告