场景：
1.数据库开启归档；
2.创建数据文件之后的所有归档日志都在线；
3.数据文件或者表空间没有进行过备份，数据库也没有全库备份,数据文件异常丢失；
步骤：
SQL> create table test_b (id number(10)) tablespace bbb;
SQL> insert into test_b values (1);
SQL> commit;
SQL>select name,file# from v$datafile;
NAME FILE#
--------------------------------------------------------------------------------
/opt/oracle/oradata/R11203/aaa.dbf 10
/opt/oracle/oradata/R11203/bbb.dbf 11
11 rows selected.
SQL> host
删除数据文件,模拟异常丢失
bash-4.2$ ls -al /opt/oracle/oradata/R11203/bbb.dbf
-rw-rw---- 1 oracle dba 10493952 Apr 4 09:53 /opt/oracle/oradata/R11203/bbb.dbf
bash-4.2$ mv /opt/oracle/oradata/R11203/bbb.dbf /opt/oracle/oradata/R11203/bbb.dbf.bak
bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 409:55:03 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 -64bit Production
With the Partitioning, OLAP, Data Mining and Real
Application Testing options
SQL> alter tablespace bbb read only;
alter tablespace bbb read only
*
ERROR at line 1:
ORA-01116: error in opening database file 11
ORA-01110: data file 11:
'/opt/oracle/oradata/R11203/bbb.dbf'
ORA-27041: unable to open file
HPUX-ia64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown immediate;
ORA-01116: error in opening database file 11
ORA-01110: data file 11:
'/opt/oracle/oradata/R11203/bbb.dbf'
ORA-27041: unable to open file
HPUX-ia64 Error: 2: No such file or directory
Additional information: 3
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL>/
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL>
停机
SQL> shutdown immediate;
ORA-01116: error in opening database file 11
ORA-01110: data file 11:
'/opt/oracle/oradata/R11203/bbb.dbf'
ORA-27041: unable to open file
HPUX-ia64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
把数据库启动到mount状态
SQL> startup mount;
ORACLE instance started.
Total System Global Area 329859072 bytes
FixedSize 2182336 bytes
VariableSize 285213504 bytes
DatabaseBuffers 37748736bytes
RedoBuffers 4714496 bytes
Database mounted.
使用alter database create datafile <> as ....的方式，重建这个丢失的数据文件：
SQL> alter database create datafile 11;
Database altered.
通过归档日志和redo log对数据文件进行恢复
SQL> recover datafile 11;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select * from test_b;
ID
----------
1