某個資料檔案損壞完全恢復（三）

三、環境：某個資料檔案損壞，有rman備份，具體恢復過程如下：

在這種恢復方式中，資料庫處於OPEN狀態，適合除了system和undo以外的普通資料檔案的恢復。

[oracle@dbserv ~]$ export ORACLE_SID=test

[oracle@dbserv ~]$ sqlplus / as sysdba

SQL> select count(*) from tt;

COUNT(*)

----------

56

SQL> insert into tt select * from dba_users;

8 rows created.

SQL> /

8 rows created.

SQL> commit;

Commit complete.

[@more@]

SQL> shutdown abort;

ORACLE instance shut down.

SQL> host rm -f /opt/oracle/oradata/test/users01.dbf

SQL> startup

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size 1220432 bytes

Variable Size 486539440 bytes

Database Buffers 1644167168 bytes

Redo Buffers 15556608 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: '/opt/oracle/oradata/test/users01.dbf'

--為了提高資料庫中其他資料的可用性，可以把損壞的資料檔案的狀態改為offline，這樣資料庫可以開啟：

SQL> alter database datafile '/opt/oracle/oradata/test/users01.dbf' offline;

Database altered.

SQL> alter database open;

Database altered.

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_

---------- ------- -------

ERROR CHANGE#

----------------------------------------------------------------- ----------

TIME

------------

4 OFFLINE OFFLINE

FILE NOT FOUND 0

SQL> exit

[oracle@dbserv ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jun 21 12:05:44 2012

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

connected to target database: TEST (DBID=2083742440)

RMAN> restore datafile '/opt/oracle/oradata/test/users01.dbf';

Starting restore at 21-JUN-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=139 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 /opt/oracle/oradata/test/users01.dbf

channel ORA_DISK_1: reading from backup piece /opt/backup/full/testfull_TEST_20120610_11

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/backup/full/testfull_TEST_20120610_11 tag=TESTDB

channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

Finished restore at 21-JUN-12

RMAN> recover tablespace users;

Starting recover at 21-JUN-12

using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 22 is already on disk as file /opt/backup/1_22_785596008.dbf

archive log thread 1 sequence 23 is already on disk as file /opt/backup/1_23_785596008.dbf

archive log thread 1 sequence 24 is already on disk as file /opt/backup/1_24_785596008.dbf

archive log thread 1 sequence 25 is already on disk as file /opt/backup/1_25_785596008.dbf

archive log thread 1 sequence 26 is already on disk as file /opt/backup/1_26_785596008.dbf

archive log thread 1 sequence 1 is already on disk as file /opt/backup/1_1_786046849.dbf

archive log thread 1 sequence 2 is already on disk as file /opt/backup/1_2_786046849.dbf

archive log thread 1 sequence 1 is already on disk as file /opt/backup/1_1_786052580.dbf

archive log thread 1 sequence 2 is already on disk as file /opt/backup/1_2_786052580.dbf

archive log thread 1 sequence 3 is already on disk as file /opt/backup/1_3_786052580.dbf

archive log thread 1 sequence 4 is already on disk as file /opt/backup/1_4_786052580.dbf

archive log thread 1 sequence 5 is already on disk as file /opt/backup/1_5_786052580.dbf

archive log thread 1 sequence 6 is already on disk as file /opt/backup/1_6_786052580.dbf

archive log thread 1 sequence 7 is already on disk as file /opt/backup/1_7_786052580.dbf

archive log thread 1 sequence 8 is already on disk as file /opt/backup/1_8_786052580.dbf

archive log thread 1 sequence 9 is already on disk as file /opt/backup/1_9_786052580.dbf

archive log thread 1 sequence 10 is already on disk as file /opt/backup/1_10_786052580.dbf

archive log thread 1 sequence 1 is already on disk as file /opt/backup/1_1_786419885.dbf

archive log thread 1 sequence 2 is already on disk as file /opt/backup/1_2_786419885.dbf

archive log thread 1 sequence 3 is already on disk as file /opt/backup/1_3_786419885.dbf

archive log thread 1 sequence 4 is already on disk as file /opt/backup/1_4_786419885.dbf

archive log thread 1 sequence 5 is already on disk as file /opt/backup/1_5_786419885.dbf

archive log thread 1 sequence 6 is already on disk as file /opt/backup/1_6_786419885.dbf

archive log filename=/opt/backup/1_22_785596008.dbf thread=1 sequence=22

archive log filename=/opt/backup/1_23_785596008.dbf thread=1 sequence=23

archive log filename=/opt/backup/1_24_785596008.dbf thread=1 sequence=24

archive log filename=/opt/backup/1_25_785596008.dbf thread=1 sequence=25

archive log filename=/opt/backup/1_26_785596008.dbf thread=1 sequence=26

archive log filename=/opt/backup/1_1_786046849.dbf thread=1 sequence=1

archive log filename=/opt/backup/1_2_786046849.dbf thread=1 sequence=2

archive log filename=/opt/backup/1_1_786052580.dbf thread=1 sequence=1

archive log filename=/opt/backup/1_2_786052580.dbf thread=1 sequence=2

archive log filename=/opt/backup/1_3_786052580.dbf thread=1 sequence=3

archive log filename=/opt/backup/1_4_786052580.dbf thread=1 sequence=4

archive log filename=/opt/backup/1_5_786052580.dbf thread=1 sequence=5

archive log filename=/opt/backup/1_6_786052580.dbf thread=1 sequence=6

archive log filename=/opt/backup/1_7_786052580.dbf thread=1 sequence=7

archive log filename=/opt/backup/1_8_786052580.dbf thread=1 sequence=8

archive log filename=/opt/backup/1_9_786052580.dbf thread=1 sequence=9

archive log filename=/opt/backup/1_10_786052580.dbf thread=1 sequence=10

archive log filename=/opt/backup/1_1_786419885.dbf thread=1 sequence=1

archive log filename=/opt/backup/1_2_786419885.dbf thread=1 sequence=2

archive log filename=/opt/backup/1_3_786419885.dbf thread=1 sequence=3

archive log filename=/opt/backup/1_4_786419885.dbf thread=1 sequence=4

media recovery complete, elapsed time: 00:00:03

Finished recover at 21-JUN-12

RMAN> exit

Recovery Manager complete.

[oracle@dbserv ~]$ sqlplus / as sysdba

SQL> select count(*) from tt;

select count(*) from tt

*

ERROR at line 1:

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

ORA-01110: data file 4: '/opt/oracle/oradata/test/users01.dbf'

SQL> alter database datafile '/opt/oracle/oradata/test/users01.dbf' online;

Database altered.

SQL> select count(*) from tt;

COUNT(*)

----------

72

SQL>