備份與恢復系列 九 丟失表空間資料檔案的還原與恢復

snowdba發表於2014-08-27
如果個別的資料檔案發生丟失或損壞，在資料庫處於OPEN狀態時可以採用RMAN輕鬆恢復（只要該檔案不屬於系統表空間也不屬於undo表空間）RAMN可以對一個或多個表空間中的資料檔案進行還原與恢復。需要注意的是恢復表空間的資料檔案需要把包含該資料檔案的表空間先離線（offline）然後再還原並恢復資料檔案，最後再將表空間聯機（online），使用的命令如下
ALTER TABLESPACE OFFLINE immediate；
restore
recover
ALTER TABLESPACE 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

記錄還在，此次恢復資料檔案實驗成功。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29047826/viewspace-1259088/，如需轉載，請註明出處，否則將追究法律責任。

相關文章