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

ITPub博客

同时丢失控制文件与数据文件的恢复

最新文章