问题8：如何在应用表空间中的数据文件损坏或者丢失的情况下进行恢复？

目的：在应用表空间的数据文件损坏、丢失时，通过RMAN和之前的备份将数据文件恢复

以下两种情况下，对应用的数据文件进行恢复

A 在启动实例时发现应用的数据文件损坏、丢失情况下的数据文件的恢复

B 在数据库正常运行状态下，发现应用的数据文件损坏、丢失情况下的恢复

情况一：

A 在启动实例时发现应用的数据文件损坏、丢失情况下的数据文件的恢复

1.启动数据库，出现如下的错误信息

[oracle@10gr2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 3 16:51:15 2014

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 599785472 bytes
Fixed Size 2085776 bytes
Variable Size 163581040 bytes
Database Buffers 427819008 bytes
Redo Buffers 6299648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/oradata/radius/users01.dbf'

2.查看alert<SID>.log即警告日志查看相关的错误信息

Completed: ALTER DATABASE MOUNT
Wed Sep 3 16:51:21 2014
ALTER DATABASE OPEN
Wed Sep 3 16:51:21 2014
Errors in file /u01/app/oracle/admin/radius/bdump/radius_dbw0_4259.trc:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/oradata/radius/users01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-1157 signalled during: ALTER DATABASE OPEN...

【说明】
根据步骤1 和 2 中的报错信息可以知道是 4 号数据文件损坏或者丢失导致的，在数据库中的数据文件丢失的情况下，只能启动数据库到mount状态，然后利用RMAN和之前的备份进行恢复，恢复的步骤如下：

步骤：

1）登陆RMAN中，执行restore命令，将数据文件从之前的备份中进行还原

[oracle@10gr2 ~]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Sep 3 16:51:37 2014

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: RADIUS (DBID=269906945, not open)

RMAN> restore datafile 4;

Starting restore at 03-SEP-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u01/oradata/radius/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/backup_full/full_RADIUS_20140903_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/backup/backup_full/full_RADIUS_20140903_1_1 tag=TAG20140903T150856
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 03-SEP-14

2）执行recover命令，利用归档和在线日志进行数据文件的追加，追平最新的scn值即达到最新的数据

RMAN> recover datafile 4;

Starting recover at 03-SEP-14
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 7 is already on disk as file /u01/backup/arch/radius/1_7_857311814.arc
archive log thread 1 sequence 8 is already on disk as file /u01/backup/arch/radius/1_8_857311814.arc
archive log thread 1 sequence 9 is already on disk as file /u01/backup/arch/radius/1_9_857311814.arc
archive log thread 1 sequence 10 is already on disk as file /u01/backup/arch/radius/1_10_857311814.arc
archive log thread 1 sequence 11 is already on disk as file /u01/backup/arch/radius/1_11_857311814.arc
archive log thread 1 sequence 12 is already on disk as file /u01/backup/arch/radius/1_12_857311814.arc
archive log thread 1 sequence 13 is already on disk as file /u01/backup/arch/radius/1_13_857311814.arc
archive log thread 1 sequence 14 is already on disk as file /u01/backup/arch/radius/1_14_857311814.arc
archive log thread 1 sequence 15 is already on disk as file /u01/backup/arch/radius/1_15_857311814.arc
archive log thread 1 sequence 16 is already on disk as file /u01/backup/arch/radius/1_16_857311814.arc
archive log thread 1 sequence 17 is already on disk as file /u01/backup/arch/radius/1_17_857311814.arc
archive log thread 1 sequence 18 is already on disk as file /u01/backup/arch/radius/1_18_857311814.arc
archive log thread 1 sequence 19 is already on disk as file /u01/backup/arch/radius/1_19_857311814.arc
archive log thread 1 sequence 20 is already on disk as file /u01/backup/arch/radius/1_20_857311814.arc
archive log thread 1 sequence 21 is already on disk as file /u01/backup/arch/radius/1_21_857311814.arc
archive log thread 1 sequence 22 is already on disk as file /u01/backup/arch/radius/1_22_857311814.arc
archive log thread 1 sequence 23 is already on disk as file /u01/backup/arch/radius/1_23_857311814.arc
archive log thread 1 sequence 24 is already on disk as file /u01/backup/arch/radius/1_24_857311814.arc
archive log thread 1 sequence 25 is already on disk as file /u01/backup/arch/radius/1_25_857311814.arc
archive log thread 1 sequence 26 is already on disk as file /u01/backup/arch/radius/1_26_857311814.arc
archive log thread 1 sequence 27 is already on disk as file /u01/backup/arch/radius/1_27_857311814.arc
archive log thread 1 sequence 28 is already on disk as file /u01/backup/arch/radius/1_28_857311814.arc
archive log thread 1 sequence 29 is already on disk as file /u01/backup/arch/radius/1_29_857311814.arc
archive log thread 1 sequence 30 is already on disk as file /u01/backup/arch/radius/1_30_857311814.arc
archive log thread 1 sequence 31 is already on disk as file /u01/backup/arch/radius/1_31_857311814.arc
archive log thread 1 sequence 32 is already on disk as file /u01/backup/arch/radius/1_32_857311814.arc
archive log thread 1 sequence 33 is already on disk as file /u01/backup/arch/radius/1_33_857311814.arc
archive log thread 1 sequence 34 is already on disk as file /u01/backup/arch/radius/1_34_857311814.arc
archive log thread 1 sequence 35 is already on disk as file /u01/backup/arch/radius/1_35_857311814.arc
archive log filename=/u01/backup/arch/radius/1_7_857311814.arc thread=1 sequence=7
archive log filename=/u01/backup/arch/radius/1_8_857311814.arc thread=1 sequence=8
archive log filename=/u01/backup/arch/radius/1_9_857311814.arc thread=1 sequence=9
archive log filename=/u01/backup/arch/radius/1_10_857311814.arc thread=1 sequence=10
archive log filename=/u01/backup/arch/radius/1_11_857311814.arc thread=1 sequence=11
archive log filename=/u01/backup/arch/radius/1_12_857311814.arc thread=1 sequence=12
archive log filename=/u01/backup/arch/radius/1_13_857311814.arc thread=1 sequence=13
archive log filename=/u01/backup/arch/radius/1_14_857311814.arc thread=1 sequence=14
archive log filename=/u01/backup/arch/radius/1_15_857311814.arc thread=1 sequence=15
archive log filename=/u01/backup/arch/radius/1_16_857311814.arc thread=1 sequence=16
archive log filename=/u01/backup/arch/radius/1_17_857311814.arc thread=1 sequence=17
archive log filename=/u01/backup/arch/radius/1_18_857311814.arc thread=1 sequence=18
archive log filename=/u01/backup/arch/radius/1_19_857311814.arc thread=1 sequence=19
archive log filename=/u01/backup/arch/radius/1_20_857311814.arc thread=1 sequence=20
archive log filename=/u01/backup/arch/radius/1_21_857311814.arc thread=1 sequence=21
archive log filename=/u01/backup/arch/radius/1_22_857311814.arc thread=1 sequence=22
archive log filename=/u01/backup/arch/radius/1_23_857311814.arc thread=1 sequence=23
archive log filename=/u01/backup/arch/radius/1_24_857311814.arc thread=1 sequence=24
archive log filename=/u01/backup/arch/radius/1_25_857311814.arc thread=1 sequence=25
archive log filename=/u01/backup/arch/radius/1_26_857311814.arc thread=1 sequence=26
archive log filename=/u01/backup/arch/radius/1_27_857311814.arc thread=1 sequence=27
archive log filename=/u01/backup/arch/radius/1_28_857311814.arc thread=1 sequence=28
archive log filename=/u01/backup/arch/radius/1_29_857311814.arc thread=1 sequence=29
archive log filename=/u01/backup/arch/radius/1_30_857311814.arc thread=1 sequence=30
archive log filename=/u01/backup/arch/radius/1_31_857311814.arc thread=1 sequence=31
archive log filename=/u01/backup/arch/radius/1_32_857311814.arc thread=1 sequence=32
archive log filename=/u01/backup/arch/radius/1_33_857311814.arc thread=1 sequence=33
media recovery complete, elapsed time: 00:05:17
Finished recover at 03-SEP-14

3）恢复完成，启动数据库到open状态

RMAN> alter database open;

database opened

情况二：

B 在数据库正常运行状态下，发现应用的数据文件损坏、丢失情况下的恢复

在数据库正常运行的状态下，出现应用表空间的数据文件丢失的情况，可以在线进行恢复，不需要关闭数据库

1.在数据库正常的运行状态下，创建一张表，出现下面的错误

SQL> create table test2 as select * from hr.employees;
create table test2 as select * from hr.employees
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktfbbssearch-9], [0], [18559], [63488], [], [], [], []

或者

SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-08103: object no longer exists

2.数据文件损坏，将损坏的数据文件offline

SQL> alter database datafile '/u01/oradata/radius/users01.dbf' offline;

Database altered.

3.确认该数据文件offline成功

SQL> select file#, status from v$datafile;

FILE# STATUS
---------- -------
1 SYSTEM
2 ONLINE
3 ONLINE
4 RECOVER
5 ONLINE

4.在RMAN中，进行该损坏数据文件的还原和恢复

[oracle@10gr2 ~]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Sep 3 17:21:36 2014

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: RADIUS (DBID=269906945)

RMAN> restore datafile 4;

Starting restore at 03-SEP-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u01/oradata/radius/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/backup_full/full_RADIUS_20140903_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/backup/backup_full/full_RADIUS_20140903_1_1 tag=TAG20140903T150856
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 03-SEP-14

RMAN> recover datafile 4;

Starting recover at 03-SEP-14
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 7 is already on disk as file /u01/backup/arch/radius/1_7_857311814.arc
archive log thread 1 sequence 8 is already on disk as file /u01/backup/arch/radius/1_8_857311814.arc
archive log thread 1 sequence 9 is already on disk as file /u01/backup/arch/radius/1_9_857311814.arc
archive log thread 1 sequence 10 is already on disk as file /u01/backup/arch/radius/1_10_857311814.arc
archive log thread 1 sequence 11 is already on disk as file /u01/backup/arch/radius/1_11_857311814.arc
archive log thread 1 sequence 12 is already on disk as file /u01/backup/arch/radius/1_12_857311814.arc
archive log thread 1 sequence 13 is already on disk as file /u01/backup/arch/radius/1_13_857311814.arc
archive log thread 1 sequence 14 is already on disk as file /u01/backup/arch/radius/1_14_857311814.arc
archive log thread 1 sequence 15 is already on disk as file /u01/backup/arch/radius/1_15_857311814.arc
archive log thread 1 sequence 16 is already on disk as file /u01/backup/arch/radius/1_16_857311814.arc
archive log thread 1 sequence 17 is already on disk as file /u01/backup/arch/radius/1_17_857311814.arc
archive log thread 1 sequence 18 is already on disk as file /u01/backup/arch/radius/1_18_857311814.arc
archive log thread 1 sequence 19 is already on disk as file /u01/backup/arch/radius/1_19_857311814.arc
archive log thread 1 sequence 20 is already on disk as file /u01/backup/arch/radius/1_20_857311814.arc
archive log thread 1 sequence 21 is already on disk as file /u01/backup/arch/radius/1_21_857311814.arc
archive log thread 1 sequence 22 is already on disk as file /u01/backup/arch/radius/1_22_857311814.arc
archive log thread 1 sequence 23 is already on disk as file /u01/backup/arch/radius/1_23_857311814.arc
archive log thread 1 sequence 24 is already on disk as file /u01/backup/arch/radius/1_24_857311814.arc
archive log thread 1 sequence 25 is already on disk as file /u01/backup/arch/radius/1_25_857311814.arc
archive log thread 1 sequence 26 is already on disk as file /u01/backup/arch/radius/1_26_857311814.arc
archive log thread 1 sequence 27 is already on disk as file /u01/backup/arch/radius/1_27_857311814.arc
archive log thread 1 sequence 28 is already on disk as file /u01/backup/arch/radius/1_28_857311814.arc
archive log thread 1 sequence 29 is already on disk as file /u01/backup/arch/radius/1_29_857311814.arc
archive log thread 1 sequence 30 is already on disk as file /u01/backup/arch/radius/1_30_857311814.arc
archive log thread 1 sequence 31 is already on disk as file /u01/backup/arch/radius/1_31_857311814.arc
archive log thread 1 sequence 32 is already on disk as file /u01/backup/arch/radius/1_32_857311814.arc
archive log thread 1 sequence 33 is already on disk as file /u01/backup/arch/radius/1_33_857311814.arc
archive log thread 1 sequence 34 is already on disk as file /u01/backup/arch/radius/1_34_857311814.arc
archive log thread 1 sequence 35 is already on disk as file /u01/backup/arch/radius/1_35_857311814.arc
archive log filename=/u01/backup/arch/radius/1_7_857311814.arc thread=1 sequence=7
archive log filename=/u01/backup/arch/radius/1_8_857311814.arc thread=1 sequence=8
archive log filename=/u01/backup/arch/radius/1_9_857311814.arc thread=1 sequence=9
archive log filename=/u01/backup/arch/radius/1_10_857311814.arc thread=1 sequence=10
archive log filename=/u01/backup/arch/radius/1_11_857311814.arc thread=1 sequence=11
archive log filename=/u01/backup/arch/radius/1_12_857311814.arc thread=1 sequence=12
archive log filename=/u01/backup/arch/radius/1_13_857311814.arc thread=1 sequence=13
archive log filename=/u01/backup/arch/radius/1_14_857311814.arc thread=1 sequence=14
archive log filename=/u01/backup/arch/radius/1_15_857311814.arc thread=1 sequence=15
archive log filename=/u01/backup/arch/radius/1_16_857311814.arc thread=1 sequence=16
archive log filename=/u01/backup/arch/radius/1_17_857311814.arc thread=1 sequence=17
archive log filename=/u01/backup/arch/radius/1_18_857311814.arc thread=1 sequence=18
archive log filename=/u01/backup/arch/radius/1_19_857311814.arc thread=1 sequence=19
archive log filename=/u01/backup/arch/radius/1_20_857311814.arc thread=1 sequence=20
archive log filename=/u01/backup/arch/radius/1_21_857311814.arc thread=1 sequence=21
archive log filename=/u01/backup/arch/radius/1_22_857311814.arc thread=1 sequence=22
archive log filename=/u01/backup/arch/radius/1_23_857311814.arc thread=1 sequence=23
archive log filename=/u01/backup/arch/radius/1_24_857311814.arc thread=1 sequence=24
archive log filename=/u01/backup/arch/radius/1_25_857311814.arc thread=1 sequence=25
archive log filename=/u01/backup/arch/radius/1_26_857311814.arc thread=1 sequence=26
archive log filename=/u01/backup/arch/radius/1_27_857311814.arc thread=1 sequence=27
archive log filename=/u01/backup/arch/radius/1_28_857311814.arc thread=1 sequence=28
archive log filename=/u01/backup/arch/radius/1_29_857311814.arc thread=1 sequence=29
archive log filename=/u01/backup/arch/radius/1_30_857311814.arc thread=1 sequence=30
archive log filename=/u01/backup/arch/radius/1_31_857311814.arc thread=1 sequence=31
archive log filename=/u01/backup/arch/radius/1_32_857311814.arc thread=1 sequence=32
archive log filename=/u01/backup/arch/radius/1_33_857311814.arc thread=1 sequence=33
media recovery complete, elapsed time: 00:05:04
Finished recover at 03-SEP-14

5.将该数据文件重新置于online状态

SQL> alter database datafile '/u01/oradata/radius/users01.dbf' online;

Database altered.

6.确认该数据文件的状态

SQL> select file#, status from v$datafile;

FILE# STATUS
---------- -------
1 SYSTEM
2 ONLINE
3 ONLINE
4 ONLINE
5 ONLINE

【说明】
该数据文件现在的状态时ONLINE状态，数据库运行正常，恢复成功。

--END--