RMAN恢復案例:丟失非系統資料檔案恢復

                  RMAN恢復案例:丟失非系統資料檔案恢復
一、資料庫執行過程中丟失一個資料檔案恢復
1、模擬介質損壞：
SQL> host rm /u01/oracle/oradata/XUE/users01.dbf[@more@]

2、建立測試表驗證介質損壞：
SQL> create table t tablespace users as select * from all_objects;
create table t tablespace users as select * from all_objects
                                                 *
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/oracle/oradata/XUE/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
3、將表空間離線、檢視資料檔案狀態：
SQL> alter tablespace users offline ;  

Tablespace altered.

SQL> select name,status,enabled from v$datafile;

NAME                                     STATUS  ENABLED
---------------------------------------- ------- ----------
/u01/oracle/oradata/XUE/system01.dbf     SYSTEM  READ WRITE
/u01/oracle/oradata/XUE/undotbs01.dbf    ONLINE  READ WRITE
/u01/oracle/oradata/XUE/indx01.dbf       ONLINE  READ WRITE
/u01/oracle/oradata/XUE/users01.dbf      OFFLINE DISABLED

SQL> alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/oracle/oradata/XUE/users01.dbf'
4、檢視恢復檔案資訊：
SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_
---------- ------- -------
ERROR                                                                CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
         4 OFFLINE OFFLINE
OFFLINE NORMAL                                                             0

SQL> host rman target /

Recovery Manager: Release 9.2.0.4.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: XUE (DBID=285819149)
5、執行命令恢復表空間：
RMAN> run {
2> allocate channel c1 type disk;
3> restore tablespace users;
4> recover tablespace users;
5> sql 'alter tablespace users online';
6> release channel c1;
7> }

allocated channel: c1
channel c1: sid=16 devtype=DISK

Starting restore at 05-JAN-05

channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u01/oracle/oradata/XUE/users01.dbf
channel c1: restored backup piece 1
piece handle=/opt/rman_backup/df_546708956_8_1 tag=TAG20050104T153556 params=NULL
channel c1: restore complete
Finished restore at 05-JAN-05

Starting recover at 05-JAN-05

starting media recovery

archive log thread 1 sequence 66 is already on disk as file /u01/oracle/oradata/XUE/archive/66.arc
archive log thread 1 sequence 67 is already on disk as file /u01/oracle/oradata/XUE/archive/67.arc
archive log thread 1 sequence 68 is already on disk as file /u01/oracle/oradata/XUE/archive/68.arc
archive log thread 1 sequence 69 is already on disk as file /u01/oracle/oradata/XUE/archive/69.arc
archive log thread 1 sequence 70 is already on disk as file /u01/oracle/oradata/XUE/archive/70.arc
archive log thread 1 sequence 71 is already on disk as file /u01/oracle/oradata/XUE/archive/71.arc
archive log thread 1 sequence 72 is already on disk as file /u01/oracle/oradata/XUE/archive/72.arc
archive log filename=/u01/oracle/oradata/XUE/archive/66.arc thread=1 sequence=66
archive log filename=/u01/oracle/oradata/XUE/archive/67.arc thread=1 sequence=67
archive log filename=/u01/oracle/oradata/XUE/archive/68.arc thread=1 sequence=68
archive log filename=/u01/oracle/oradata/XUE/archive/69.arc thread=1 sequence=69
archive log filename=/u01/oracle/oradata/XUE/archive/70.arc thread=1 sequence=70
archive log filename=/u01/oracle/oradata/XUE/archive/71.arc thread=1 sequence=71
media recovery complete
Finished recover at 05-JAN-05

sql statement: alter tablespace users online

released channel: c1

RMAN> exit

Recovery Manager complete.
6、驗證恢復成功：
SQL> select name,status,enabled from v$datafile;

NAME                                     STATUS  ENABLED
---------------------------------------- ------- ----------
/u01/oracle/oradata/XUE/system01.dbf     SYSTEM  READ WRITE
/u01/oracle/oradata/XUE/undotbs01.dbf    ONLINE  READ WRITE
/u01/oracle/oradata/XUE/indx01.dbf       ONLINE  READ WRITE
/u01/oracle/oradata/XUE/users01.dbf      ONLINE  READ WRITE

SQL> create table t tablespace users as select * from all_objects;

Table created.

SQL> select count(*) from t;

  COUNT(*)
----------
      6143