歸檔模式，恢復沒有備份的資料檔案

場景：

1.資料庫開啟歸檔；

2.建立資料檔案之後的所有歸檔日誌都線上；

3.資料檔案或者表空間沒有進行過備份，資料庫也沒有全庫備份,資料檔案異常丟失；

步驟：

建立測試用的表空間：
SQL> create tablespace bbb datafile '/opt/oracle/oradata/R11203/bbb.dbf' size 100m;

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