shutdown后丢失某个数据文件的恢复

网上的朋友不小心删除了物理文件，发现startup不起来，我们怎么进行数据恢复？在archive模式下，这其实是一个很简单的过程：

SQL> create tablespace "TS_DATA_TEST" logging datafile 'D:oracleoradataoralocalTS_DATA_TEST.dbf' size 5M;


表空间已创建。


SQL> select tablespace_name from dba_tables where TABLE_NAME='HJM_TB_TEST04';


TABLESPACE_NAME

------------------------------

TS_DATA_TEST


SQL> shutdown immediate;

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> exit

从Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production中断开


D:oracleoradataoralocal>dir

驱动器 D 中的卷是 SOFTWARE

卷的序列号是 A099-1506


D:oracleoradataoralocal 的目录


2006-09-26 16:28  .

2006-09-26 16:28  ..

2006-09-26 16:29 2,072,576 CONTROL01.CTL

2006-09-26 16:29 2,072,576 CONTROL02.CTL

2006-09-26 16:29 2,072,576 CONTROL03.CTL

2006-09-26 16:29 15,736,832 CWMLITE01.DBF

2006-09-26 16:29 15,736,832 DRSYS01.DBF

2006-09-26 16:29 156,639,232 EXAMPLE01.DBF

2006-09-26 16:29 52,436,992 INDX01.DBF

2006-09-26 16:29 15,736,832 ODM01.DBF

2006-09-26 16:29 209,723,392 PERFSTAT.DBF

2006-09-26 16:28 104,858,112 REDO01.LOG

2006-09-26 16:29 104,858,112 REDO02.LOG

2006-09-26 16:28 104,858,112 REDO03.LOG

2006-09-26 16:29 426,778,624 SYSTEM01.DBF

2006-09-26 16:29 5,251,072 TS_DATA_TEST.DBF

2006-09-26 16:29 26,222,592 TS_DATA_USER01.DBF

2006-09-26 16:29 52,436,992 TS_RMAN_01.DBF

2006-09-12 23:40 104,865,792 TS_TEMP_01.DBF

2006-09-26 16:29 28,319,744 TS_UNDO_01.DBF

2006-09-26 16:29 39,985,152 XDB01.DBF

19 个文件 1,470,662,144 字节

2 个目录 3,622,273,024 可用字节


D:oracleoradataoralocal>del TS_DATA_TEST.DBF


D:oracleoradataoralocal>dir

驱动器 D 中的卷是 SOFTWARE

卷的序列号是 A099-1506


D:oracleoradataoralocal 的目录


2006-09-26 16:29  .

2006-09-26 16:29  ..

2006-09-26 16:29 2,072,576 CONTROL01.CTL

2006-09-26 16:29 2,072,576 CONTROL02.CTL

2006-09-26 16:29 2,072,576 CONTROL03.CTL

2006-09-26 16:29 15,736,832 CWMLITE01.DBF

2006-09-26 16:29 15,736,832 DRSYS01.DBF

2006-09-26 16:29 156,639,232 EXAMPLE01.DBF

2006-09-26 16:29 52,436,992 INDX01.DBF

2006-09-26 16:29 15,736,832 ODM01.DBF

2006-09-26 16:29 209,723,392 PERFSTAT.DBF

2006-09-26 16:28 104,858,112 REDO01.LOG

2006-09-26 16:29 104,858,112 REDO02.LOG

2006-09-26 16:28 104,858,112 REDO03.LOG

2006-09-26 16:29 426,778,624 SYSTEM01.DBF

2006-09-26 16:29 26,222,592 TS_DATA_USER01.DBF

2006-09-26 16:29 52,436,992 TS_RMAN_01.DBF

2006-09-12 23:40 104,865,792 TS_TEMP_01.DBF

2006-09-26 16:29 28,319,744 TS_UNDO_01.DBF

2006-09-26 16:29 39,985,152 XDB01.DBF

18 个文件 1,465,411,072 字节

2 个目录 3,627,524,096 可用字节


D:oracleoradataoralocal>sqlplus "/ as sysdba"


SQL*Plus: Release 9.2.0.1.0 - Production on 星期二 9月 26 16:29:36 2006


Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


已连接到空闲例程。


SQL> startup nomount;

ORACLE 例程已经启动。


Total System Global Area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL> alter database mount;


数据库已更改。


SQL> alter database open;

alter database open

*

ERROR 位于第 1 行:

ORA-01157: ????/?????? 8 - ??? DBWR ????

ORA-01110: ???? 8: 'D:ORACLEORADATAORALOCALTS_DATA_TEST.DBF'



SQL> alter database create datafile 8 as 'D:ORACLEORADATAORALOCALTS_DATA_TEST.DBF';


数据库已更改。


SQL> shutdown

ORA-01109: ??????



已经卸载数据库。

ORACLE 例程已经关闭。

SQL> startup

ORACLE 例程已经启动。


Total System Global Area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

数据库装载完毕。

ORA-01113: 文件 8 需要介质恢复

ORA-01110: 数据文件 8: 'D:ORACLEORADATAORALOCALTS_DATA_TEST.DBF'



SQL> recover datafile 8;

完成介质恢复。

SQL> alter database open;


数据库已更改。



SQL> select tablespace_name from dba_tables where TABLE_NAME='HJM_TB_TEST04';


TABLESPACE_NAME

------------------------------

TS_DATA_TEST