修复处于recover状态的oracle数据文件
来源:网络收集 点击: 时间:2024-03-101:查询某张表报错
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/32:检测发现部分数据文件处于recover状态
select * from dba_data_files;

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:关闭数据库
shutdown immediate
2/72:卸载挂载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/73:确定网络正常后激活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/74:动挂载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:启动数据库
这时候我们发现/zxx_orcl的数据文件还是处于recover,再确定存储正常之后,就可以进行恢复工作了!
6/76:恢复处于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:online之前恢复后的数据文件
ALTER DATABASE DATAFILE 11 online;

版权声明:
1、本文系转载,版权归原作者所有,旨在传递信息,不代表看本站的观点和立场。
2、本站仅提供信息发布平台,不承担相关法律责任。
3、若侵犯您的版权或隐私,请联系本站管理员删除。
4、文章链接:http://www.1haoku.cn/art_308084.html