场景：
1.数据库开启归档；
2.创建数据文件之后的所有归档日志都在线；
3.数据文件或者表空间没有进行过备份，数据库也没有全库备份,数据文件异常丢失；
步骤：
创建测试用的表空间：
SQL> create table test_b (id number(10)) tablespace bbb;
Table created.
SQL> insert into test_b values (1);
1 row created.
SQL> commit;
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 4
09: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
Fixed
Size
2182336 bytes
Variable
Size
285213504 bytes
Database
Buffers 37748736
bytes
Redo
Buffers
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