修复处于recover状态的oracle数据文件问题情景：oracle数据库部分数据文件存放在存储上，由于网络问题，导致存储处于read only 状态，从而导致数据库部分文件处于recover状态！问题报错情况：1：查询某张表报错2：检测发现部分数据文件处于recover状态3：最终原因是存储处于read only状态，网络引起的解决步骤 ：1：关闭数据库2：卸载挂载lvm3：确定网络正常后激活VG4：动挂载LVM5：启动数据库6：恢复处于recover状态的数据文件7：online之前恢复后的数据文件注意：lvm具体参看可以参考我的百度经验：lvm inactive问题定位及解决http://jingyan.baidu.com/article/e4d08ffd8f0c0d0fd3f60d55.html问题报错情况：1/3分步阅读

1：查询某张表报错

SQL select max(jgsj) from sa.clxsgj;

select max(jgsj) from sa.clxsgj

*

ERROR at line 1:

ORA-00376: file 11 cannot be read at this time

ORA-01110: data file 11: /zxx_orcl/oradata/320db005.dbf

ORA-00376: file 11 cannot be read at this time

2/3

2：检测发现部分数据文件处于recover状态

select * from dba_data_files;

3/3

3：最终原因是存储处于read only状态

# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda1 897G 771G 80G 91% /

tmpfs 16G 0 16G 0% /dev/shm

/dev/mapper/OraBack-backupone

6.9T 3.8T 2.8T 58% /backup/oracle/orcl/fullbackup

/dev/mapper/OraBack-backuptwo

6.9T 1.5T 5.1T 23% /backup/oracle/orcl/archbackup

/dev/mapper/OraBack-backupthree

1008G 8.5G 949G 1% /backup/oracle/orcl/ctlbackup

/dev/mapper/OraBack-orcl

3.0T 1001G 1.9T 35% /zxx_orcl

$ mkdir aaa

mkdir: cannot create directory `aaa: Read-only file system

解决步骤：1/7

1：关闭数据库

shutdown immediate

2/7

2：卸载挂载lvm

存储是通过iscsi映射到服务器上，然后通过创建vg ，再创建逻辑卷，挂载逻辑卷，将数据文件存放在逻辑卷上。

# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda1 897G 771G 80G 91% /

tmpfs 16G 0 16G 0% /dev/shm

/dev/mapper/OraBack-backupone

6.9T 3.8T 2.8T 58% /backup/oracle/orcl/fullbackup

/dev/mapper/OraBack-backuptwo

6.9T 1.5T 5.1T 23% /backup/oracle/orcl/archbackup

/dev/mapper/OraBack-backupthree

1008G 8.5G 949G 1% /backup/oracle/orcl/ctlbackup

/dev/mapper/OraBack-orcl

3.0T 1001G 1.9T 35% /zxx_orcl

# umount /backup/oracle/orcl/fullbackup

# umount /backup/oracle/orcl/archbackup

# umount /backup/oracle/orcl/ctlbackup

# umount /zxx_orcl

3/7

3：确定网络正常后激活VG并手动挂载逻辑卷

确定服务器到存储的网络正常后，重启服务器

# reboot

Broadcast message from root (pts/2) (Thu Aug 27 10:26:17 2015):

The system is going down for reboot NOW!

重启服务器后可以看到：

# fdisk -l

Disk /dev/sda: 997.9 GB, 997998985216 bytes

255 heads, 63 sectors/track, 121333 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

/dev/sda1 * 1 120810 970406293+ 83 Linux

/dev/sda2 120811 121332 4192965 82 Linux swap / Solaris

Note: sector size is 4096 (not 512)

Disk /dev/sdb: 19993.0 GB, 19993039208448 bytes

255 heads, 63 sectors/track, 303835 cylinders

Units = cylinders of 16065 * 4096 = 65802240 bytes

存储挂载过来了。lvscan扫描逻辑卷，如果逻辑卷处于失活状态，这时候需要激活VG：

vgchange -ay /dev/OraBack

激活之后，lvscan扫描发现逻辑卷都处于active状态了。可以手动挂载了

4/7

4：动挂载LVM

mount lvm名称 挂载目录（一定要和之前的一一对应）

lvm名称通过lvscan扫描获取。挂载成功之后需要验证挂载目录是否可以正常读写

mount /dev/mapper/OraBack-backupone /backup/oracle/orcl/fullbackup

mount /dev/mapper/OraBack-backuptwo /backup/oracle/orcl/archbackup/

mount /dev/mapper/OraBack-backupthree /backup/oracle/orcl/ctlbackup/

mount /dev/mapper/OraBack-orcl /zxx_orcl/

# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda1 897G 768G 83G 91% /

tmpfs 16G 0 16G 0% /dev/shm

/dev/mapper/OraBack-backupone

6.9T 3.8T 2.8T 58% /backup/oracle/orcl/fullbackup

/dev/mapper/OraBack-backuptwo

6.9T 1.5T 5.1T 23% /backup/oracle/orcl/archbackup

/dev/mapper/OraBack-backupthree

1008G 8.5G 949G 1% /backup/oracle/orcl/ctlbackup

/dev/mapper/OraBack-orcl

3.0T 1001G 1.9T 35% /zxx_orcl

# cd /zxx_orcl/

# ls

aaaa awrrpt_1_28687_28688.html backup lost+found oradata

# rm -rf awrrpt_1_28687_28688.html

# cd aaaa/

挂载目录能够正常读写。

5/7

5：启动数据库

这时候我们发现/zxx_orcl的数据文件还是处于recover，再确定存储正常之后，就可以进行恢复工作了！

6/7

6：恢复处于recover状态的数据文件

recover datafile 11 ;

recover datafile 10 ;

recover datafile 9 ;

recover datafile 8 ;

recover datafile 7 ;

recover datafile 6 ;

recover datafile 31 ;

recover datafile 32 ;

recover datafile 33 ;

recover datafile 34 ;

recover datafile 35 ;

recover datafile 36 ;

recover datafile 37 ;

recover datafile 38 ;

recover datafile 39 ;

recover datafile 40 ;

recover datafile 41 ;

recover datafile 42 ;

recover datafile 43 ;

recover datafile 44 ;

recover datafile 45 ;

recover datafile 46 ;

recover datafile 47 ;

recover datafile 48 ;

数据文件恢复之后，会变成offline状态，这时候需要将数据文件online

7/7

7：online之前恢复后的数据文件

ALTER DATABASE DATAFILE 11 online;

oracle