1. 準備工作
1-1. 備份控制檔案, 需要在建立表空間之前備份
SQL> alter database backup controlfile to '/tmp/control01.ctl';

1-2. 建立測試表空間
SQL> create tablespace tbs datafile '/data/asmdb/tbs01.dbf' size 5m;

1-3. 刪除控制檔案與測試資料檔案

2. 恢復

2-1. 複製回備份控制檔案, 並啟動到 mount
SQL> startup mount

2-2. 利用 redo 恢復控制檔案到最新
# 恢復後, 控制檔案就有了刪除丟失的資料檔案的資訊

SQL> recover database using backup controlfile;
Specify log: {=suggested | filename | AUTO | CANCEL}
/data/asmdb/redo01.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 5: '/data/asmdb/tbs01.dbf'
ORA-01112: media recovery not started

SQL> select * from v$tablespace;
       TS# NAME   INC BIG FLA ENC
---------- ------ --- --- --- ---
         7 TBS    YES NO YES

SQL> select name from v$datafile;

NAME
--------------------------------------------
/oracle/product/10.2.0/db_1/dbs/UNNAMED00005

2-3. 手動建立丟失的資料檔案, 並再次執行恢復
SQL> alter database create datafile 5 as '/data/asmdb/tbs01.dbf' size 5m;

SQL> recover database using backup controlfile;
Specify log: {=suggested | filename | AUTO | CANCEL}
/data/asmdb/redo01.log
Log applied.
Media recovery complete.

2-4. resetlogs 開啟資料庫, 檢視恢復結果
SQL> alter database open resetlogs;

Database altered.

SQL> select * from tt;

        ID
----------
         1

同時丟失控制檔案與資料檔案的恢復

相關文章