昨天整理磁盘，误删除了ad.dbf 数据库文件。结果今天使用数据库的时候发现问题如下：
1 SQL> startup open 2 ORACLE 例程已经启动。 3 4 Total System Global Area 612368384 bytes 5 Fixed Size 1250428 bytes 6 Variable Size 234883972 bytes 7 Database Buffers 369098752 bytes 8 Redo Buffers 7135232 bytes 9 数据库装载完毕。 10 ORA-01157: 无法标识/锁定数据文件 6 - 请参阅 DBWR 跟踪文件 11 ORA-01110: 数据文件 6: 'D:\AD.DBF'
分析如下：
在oracle 11g的官方文档中，Oracle® Database Administrator's Guide 一篇中有如下章节：
Altering Datafile Availability
You can alter the availability of individual datafiles or tempfiles by taking them offline or bringing them online. Offline datafiles are unavailable to the database and cannot be accessed until they are brought back online.
Reasons for altering datafile availability include the following:
-
You want to perform an offline backup of a datafile.
-
You want to rename or relocate a datafile. You must first take it offline or take the tablespace offline.
-
The database has problems writing to a datafile and automatically takes the datafile offline. Later, after resolving the problem, you can bring the datafile back online manually.
-
A datafile becomes missing or corrupted. You must take it offline before you can open the database.
Taking Datafiles Offline in NOARCHIVELOG Mode
To take a datafile offline when the database is in NOARCHIVELOG mode, use the ALTER DATABASE statement with both the DATAFILE and OFFLINE FOR DROP clauses.
-
The
OFFLINEkeyword causes the database to mark the datafileOFFLINE, whether or not it is corrupted, so that you can open the database. -
The
FORDROPkeywords mark the datafile for subsequent dropping. Such a datafile can no longer be brought back online.
The following statement takes the specified datafile offline and marks it to be dropped:
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE FOR DROP;
解决方法如下：
1 SQL> alter database datafile 'd:\ad.dbf' offline drop 2 2 ; 3 4 数据库已更改。 5 6 SQL> alter database open 7 2 ;
参考资料：
- http://blog.ywxyn.com/index.php/archives/324
- Oracle Database Document library <<Administrator's Guide>>