online/offline 表空間和資料檔案需謹慎！

itpub上的一個案例：

http://www.itpub.net/viewthread.php?tid=1014590&extra=&page=1

表空間有4個檔案，有3個有資料，有1個是沒有資料的，很久以前是offline drop的。不小心按了表空間立即離線，即alter tablespace test offline immediate.

sql>shutdown immediate;
sql>startup mount
sql>alter database datafile '/data/a001.dbf' online;
sql>alter database datafile '/data/a002.dbf' online;
sql>alter database datafile '/data/a003.dbf' online;

以上都沒問題，但第4個檔案就不行，提示要恢復

sql>recover datafile 121;

ORA-00289: suggestion : /arch/1_2123.dbf
ORA-00280: change 1974069278 for thread 1 is in sequence #2123


Specify log: {=suggested | filename | AUTO | CANCEL}
（這裡不管選 什麼都出錯，因為根本就沒有那個檔案）

資料暫時可以正常啟動，就是這個表空間的資料不能訪問了。

分析：

主要是因為是用了offline immediate將表空間離線，所以這樣的離線方式是不等Oracle對任何資料檔案做檢查。所以當Online時，當聯機表空間時就必須需要介質恢復。否則就是離線。

sql>alter database datafile 121 offline drop;

sql>alter tablespace a001 online;

*

ERROR at line 1:

ORA-01113: file 121 needs media recovery

ORA-01110: data file 121: '/data/b_001.dbf'

沒有辦法把tablespace online，先用dba_extents或dba_segments查表在哪個資料檔案，然後用aul這樣的工具抽取表資料吧,還有極端的方法就是透過bbed和dul修改該檔案的scn號，使的在online時，oracle不恢復該資料檔案。

Restrictions for Dropping Datafiles

The following are restrictions for dropping datafiles and tempfiles:

■ The database must be open.

■ If a datafile is not empty, it cannot be dropped

If you must remove a datafile that is not empty and that cannot be made empty by

dropping schema objects, you must drop the tablespace that contains the datafile.

■ You cannot drop the first or only datafile in a tablespace.

This means that DROP DATAFILE cannot be used with a bigfile tablespace.

■ You cannot drop datafiles in a read-only tablespace.

■ You cannot drop datafiles in the SYSTEM tablespace.

■ If a datafile in a locally managed tablespace is offline, it cannot be dropped.