备份与恢复系列 九 丢失表空间数据文件的还原与恢复

如果个别的数据文件发生丢失或损坏，在数据库处于OPEN状态时可以采用RMAN轻松恢复（只要该文件不属于系统表空间也不属于undo表空间）RAMN可以对一个或多个表空间中的数据文件进行还原与恢复。需要注意的是恢复表空间的数据文件需要把包含该数据文件的表空间先脱机（offline）然后再还原并恢复数据文件，最后再将表空间联机（online），使用的命令如下
ALTER TABLESPACE <tablespace name> OFFLINE immediate；
restore
recover
ALTER TABLESPACE <tablespace name> OFFLINE/ONLINE;

1，向测试对象SCOTT的dept表添加数据, 实验的结尾要核对该数据是否被成功恢复
SYS@PRACTICE >select * from scott.dept;

    DEPTNO DNAME                                      LOC
---------- ------------------------------------------ ---------------------------------------
        10 ACCOUNTING                                 NEW YORK
        20 RESEARCH                                   DALLAS
        30 SALES                                      CHICAGO
        40 OPERATIONS                                 BOSTON

SYS@PRACTICE >INSERT INTO scott.dept (deptno,dname,loc) VALUES ( 50,'SUPPORT','DaLian' );

SYS@PRACTICE >commit;

SYS@PRACTICE >ALTER SYSTEM SWITCH LOGFILE;
SYS@PRACTICE >ALTER SYSTEM SWITCH LOGFILE;
SYS@PRACTICE >ALTER SYSTEM SWITCH LOGFILE;

通过查看DBA_SEGMENTS, 我们可以确认SCOTT用户的表DEPT属于表空间USERS
SYS@PRACTICE >col OWNER for a10
SYS@PRACTICE >col SEGMENT_NAME for a10
SYS@PRACTICE >col TABLESPACE_NAME for a10
SYS@PRACTICE >SELECT OWNER,SEGMENT_NAME,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE OWNER='SCOTT';

OWNER      SEGMENT_NA TABLESPACE
---------- ---------- ----------
SCOTT      DEPT       USERS
SCOTT      EMP        USERS
SCOTT      PK_DEPT    USERS
SCOTT      PK_EMP     USERS
SCOTT      SALGRADE   USERS

2，删除表空间USERS的数据文件users01.dbf
SYS@PRACTICE >host
[oracle@practice3 ~]$ rm /oradata/PRACTICE/users01.dbf
[oracle@practice3 ~]$ exit

3，通过ALTER TABLESPACE USERS ONLINE来触发检查点事件
系统提示4号数据文件不存在
SYS@PRACTICE >alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110:  data file 4: '/oradata/PRACTICE/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

通过dba_data_files查询确认了4号文件为USERS表空间数据文件
SYS@PRACTICE >col FILE_NAME for a50
SYS@PRACTICE >col TABLESPACE_NAME for a20
SYS@PRACTICE >select FILE_ID,FILE_NAME,TABLESPACE_NAME from dba_data_files;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
          4 /oradata/PRACTICE/users01.dbf                      USERS
         3 /oradata/PRACTICE/undotbs01.dbf                    UNDOTBS1
         2 /oradata/PRACTICE/sysaux01.dbf                     SYSAUX
         1 /oradata/PRACTICE/system01.dbf                     SYSTEM
         5 /oradata/PRACTICE/example01.dbf                    EXAMPLE
         6 /oradata/tools01.dbf                               TOOLS
         7 /oradata/indx01.dbf                                INDX

4，恢复USERS表空间的4号数据文件
用vi编辑恢复脚本，注意开头的offline语句和结尾的online语句。

vi /home/oracle/recover_datafile4.sql

sql 'alter tablespace users offline immediate';
run{
allocate channel c1 type disk;
restore datafile 4;
recover tablespace users;
sql 'alter tablespace users online';
}

执行恢复脚本
RMAN> @recover_datafile4.sql

allocated channel: c1
channel c1: SID=19 device type=DISK

Starting restore at 2014/08/27 13:41:02

channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00004 to /oradata/PRACTICE/users01.dbf
channel c1: reading from backup piece /backup/PRACTICE_in0_47_1_855800878
channel c1: piece handle=/backup/PRACTICE_in0_47_1_855800878 tag=WHOLE_IN0
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
Finished restore at 2014/08/27 13:41:03

Starting recover at 2014/08/27 13:41:04
channel c1: starting incremental datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /oradata/PRACTICE/users01.dbf
channel c1: reading from backup piece /backup/db_in1_PRACTICE_55_1_856690214
channel c1: piece handle=/backup/db_in1_PRACTICE_55_1_856690214 tag=WHOLE_INC1
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01

starting media recovery

archived log for thread 1 with sequence 38 is already on disk as file /archive/1_38_855750293.arc
archived log for thread 1 with sequence 39 is already on disk as file /archive/1_39_855750293.arc
archived log for thread 1 with sequence 40 is already on disk as file /archive/1_40_855750293.arc
archived log for thread 1 with sequence 41 is already on disk as file /archive/1_41_855750293.arc
archived log for thread 1 with sequence 42 is already on disk as file /archive/1_42_855750293.arc
archived log file name=/archive/1_38_855750293.arc thread=1 sequence=38
archived log file name=/archive/1_39_855750293.arc thread=1 sequence=39
archived log file name=/archive/1_40_855750293.arc thread=1 sequence=40
media recovery complete, elapsed time: 00:00:00
Finished recover at 2014/08/27 13:41:05

sql statement: alter tablespace users online
released channel: c1

RMAN> **end-of-file**


5，验证4号数据文件恢复效果
SCOTT@PRACTICE >select * from dept;

    DEPTNO DNAME                                      LOC
---------- ------------------------------------------ ---------------------------------------
         50 SUPPORT                                    DaLian
        10 ACCOUNTING                                 NEW YORK
        20 RESEARCH                                   DALLAS
        30 SALES                                      CHICAGO
        40 OPERATIONS                                 BOSTON

记录还在，此次恢复数据文件实验成功。