沒有備份的資料檔案恢復（五）

環境：某個資料檔案因作業系統錯誤刪除原因被刪除而且無任何備份，資料庫執行在歸檔模式下，該資料檔案建立時間晚於於控制檔案建立時間。

[oracle@dbserv ~]$ sqlplus / as sysdba

SQL> create tablespace test datafile '/opt/oracle/oradata/test/test01.dbf' size 100M;

Tablespace created.

SQL> create table ttt tablespace test as select * from dba_users;

Table created.

SQL> select count(*) from ttt;

COUNT(*)

----------

8

SQL> insert into ttt select * from dba_users;

8 rows created.

SQL> /

8 rows created.

SQL> /

8 rows created.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

[@more@]

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

因操作失誤原因test資料檔案被刪除而且沒有備份：

SQL> host rm -f /opt/oracle/oradata/test/test01.dbf

SQL> startup

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size 1220432 bytes

Variable Size 486539440 bytes

Database Buffers 1644167168 bytes

Redo Buffers 15556608 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file

ORA-01110: data file 5: '/opt/oracle/oradata/test/test01.dbf'

SQL> recover datafile;

ORA-02236: invalid file name

SQL> recover datafile 5;

ORA-00283: recovery session canceled due to errors

ORA-01110: data file 5: '/opt/oracle/oradata/test/test01.dbf'

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file

ORA-01110: data file 5: '/opt/oracle/oradata/test/test01.dbf'

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01110: data file 5: '/opt/oracle/oradata/test/test01.dbf'

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file

ORA-01110: data file 5: '/opt/oracle/oradata/test/test01.dbf'

SQL> alter database create datafile '/opt/oracle/oradata/test/test01.dbf' ;

Database altered.

SQL> recover datafile 5;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select count(*) from ttt;

COUNT(*)

----------

32

SQL>