利用歸檔來做資料檔案的恢復

資料庫是歸檔狀態，但是沒有做過全庫備份。歸檔檔案全部都在。利用歸檔檔案來進行恢復操作，操作過程如下：

啟庫時，提示如下錯誤資訊

SYS@testdb>alter database open;
alter database open
*
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 8 is offline
ORA-01110: data file 8: '/oracle/ora10g/oradata/users02.dbf'

檢查資料檔案，發現datafile 8 丟失。

SYS@testdb>select ts#,file#,name,status from v$datafile;

       TS#      FILE# NAME                                     STATUS
---------- ---------- ---------------------------------------- -------
         0          1 /oracle/ora10g/oradata/system01.dbf      SYSTEM
         1          2 /oracle/ora10g/oradata/undotbs01.dbf     ONLINE
         2          3 /oracle/ora10g/oradata/sysaux01.dbf      ONLINE
         4          4 /oracle/ora10g/oradata/users01.dbf       ONLINE
         5          5 /oracle/ora10g/oradata/leo1_01.dbf       ONLINE
         6          6 /oracle/ora10g/oradata/mssm01.dbf        ONLINE
         7          7 /oracle/ora10g/oradata/assm01.dbf        ONLINE
         0          8 /oracle/ora10g/oradata/users02.dbf       SYSOFF
         8          9 /oracle/ora10g/product/10.2.0/db_1/dbs/o ONLINE
                      racleora10goradatatest01.dbf

       TS#      FILE# NAME                                     STATUS
---------- ---------- ---------------------------------------- -------
         9         10 /oracle/ora10g/product/10.2.0/db_1/dbs/w ONLINE
                      angche.bdf

        10         11 /ora_data/wangche1.bdf                   ONLINE

11 rows selected.

我們使用下面的SQL進行資料檔案的重建

alter">SYS@testdb>alter database create datafile '/oracle/ora10g/oradata/users02.dbf';

Database altered.

SYS@testdb>select ts#,file#,name,status from v$datafile;

       TS#      FILE# NAME                                               STATUS
---------- ---------- -------------------------------------------------- -------
         0          1 /oracle/ora10g/oradata/system01.dbf                SYSTEM
         1          2 /oracle/ora10g/oradata/undotbs01.dbf               ONLINE
         2          3 /oracle/ora10g/oradata/sysaux01.dbf                ONLINE
         4          4 /oracle/ora10g/oradata/users01.dbf                 ONLINE
         5          5 /oracle/ora10g/oradata/leo1_01.dbf                 ONLINE
         6          6 /oracle/ora10g/oradata/mssm01.dbf                  ONLINE
         7          7 /oracle/ora10g/oradata/assm01.dbf                  ONLINE
         0          8 /oracle/ora10g/oradata/users02.dbf                 SYSOFF
         8          9 /oracle/ora10g/product/10.2.0/db_1/dbs/oracleora10 ONLINE
                      goradatatest01.dbf

       TS#      FILE# NAME                                               STATUS
---------- ---------- -------------------------------------------------- -------
         9         10 /oracle/ora10g/product/10.2.0/db_1/dbs/wangche.bdf ONLINE
        10         11 /ora_data/wangche1.bdf                             ONLINE

11 rows selected.

利用歸檔恢復資料檔案

SYS@testdb>recover datafile 8;
Media recovery complete.
SYS@testdb>

將資料檔案狀態修改為online
SYS@testdb>alter database datafile 8 online;

Database altered.

SYS@testdb>select ts#,file#,name,status from v$datafile;

       TS#      FILE# NAME                                               STATUS
---------- ---------- -------------------------------------------------- -------
         0          1 /oracle/ora10g/oradata/system01.dbf                SYSTEM
         1          2 /oracle/ora10g/oradata/undotbs01.dbf               ONLINE
         2          3 /oracle/ora10g/oradata/sysaux01.dbf                ONLINE
         4          4 /oracle/ora10g/oradata/users01.dbf                 ONLINE
         5          5 /oracle/ora10g/oradata/leo1_01.dbf                 ONLINE
         6          6 /oracle/ora10g/oradata/mssm01.dbf                  ONLINE
         7          7 /oracle/ora10g/oradata/assm01.dbf                  ONLINE
         0          8 /oracle/ora10g/oradata/users02.dbf                 SYSTEM
         8          9 /oracle/ora10g/product/10.2.0/db_1/dbs/oracleora10 ONLINE
                      goradatatest01.dbf

       TS#      FILE# NAME                                               STATUS
---------- ---------- -------------------------------------------------- -------
         9         10 /oracle/ora10g/product/10.2.0/db_1/dbs/wangche.bdf ONLINE
        10         11 /ora_data/wangche1.bdf                             ONLINE

11 rows selected.

再次開啟資料庫。

SYS@testdb>alter database open;

Database altered.

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