環境：RedHat Enterprise Server
數據庫：11.2.0.1.0
歸檔模式：非歸檔
提示：如果不小心通過操作系統命令誤刪除了數據文件，一定不要輕易的關閉實例或者offline數據文件.
在user表空間中新增一個數據文件/data/Oracle/oradata/orcl/user03.dbf
orcl@ SYS> selectfile#,status,name from v$datafile;
FILE# STATUSNAME
---------- ------------------------------------------------------------
1 SYSTEM/data/oracle/oradata/orcl/system01.dbf
2 ONLINE/data/oracle/oradata/orcl/sysaux01.dbf
3 ONLINE/data/oracle/oradata/orcl/undotbs01.dbf
4 RECOVER/data/oracle/oradata/orcl/users01.dbf
5 ONLINE/data/oracle/oradata/orcl/test.dbf
6 ONLINE/data/oracle/oradata/orcl/user02.dbf
7 ONLINE/data/oracle/oradata/orcl/t1.dbf
7 rows selected.
Elapsed: 00:00:00.01
orcl@ SYS> altertablespace users add datafile'/data/oracle/oradata/orcl/user03.dbf' size 10M;
Tablespace altered.
Elapsed: 00:00:08.78
orcl@ SYS>
orcl@ SYS> select file#,status,name from v$datafile;
FILE# STATUSNAME
---------- ------------------------------------------------------------
1 SYSTEM /data/oracle/oradata/orcl/system01.dbf
2 ONLINE /data/oracle/oradata/orcl/sysaux01.dbf
3 ONLINE /data/oracle/oradata/orcl/undotbs01.dbf
4 RECOVER /data/oracle/oradata/orcl/users01.dbf
5 ONLINE /data/oracle/oradata/orcl/test.dbf
6 ONLINE /data/oracle/oradata/orcl/user02.dbf
7 ONLINE /data/oracle/oradata/orcl/t1.dbf
8 ONLINE /data/oracle/oradata/orcl/user03.dbf
8 rows selected.
orcl@ SYS>
------------------------在操作系統用root用戶刪除新增數據文件user03.dbf--------
[root@linux128 dbs]#cd /data/oracle/oradata/orcl
[root@linux128orcl]# ll
總計 1599388
-rw-r----- 1 oracleoinstall10076160 05-11 10:54control01.ctl
-rw-r----- 1 oracleoinstall9748480 12-30 14:30control01.ctl.bk
-rw-r----- 1 oracleoinstall52429312 05-11 10:54 redo01.log
-rw-r----- 1 oracleoinstall52429312 05-11 10:09 redo02.log
-rw-r----- 1 oracleoinstall52429312 05-11 10:09 redo03.log
-rw-r----- 1 oracleoinstall 566239232 05-11 10:53 sysaux01.dbf
-rw-r----- 1 oracleoinstall 734011392 05-11 10:50 system01.dbf
-rw-r----- 1 oracleoinstall5251072 05-11 10:09 t1.dbf
-rw-r----- 1 oracleoinstall30416896 12-30 13:20 temp01.dbf
-rw-r----- 1 oracleoinstall5251072 12-30 14:00 temp02.dbf
-rw-r----- 1 oracleoinstall5251072 2012-10-22 temp03.dbf
-rw-r----- 1 oracleoinstall5251072 05-11 10:09 test.dbf
-rw-r----- 1 oracleoinstall78651392 05-11 10:50undotbs01.dbf
-rw-r----- 1 oracleoinstall31465472 05-11 10:09 user02.dbf
-rw-r----- 1 oracleoinstall10493952 05-11 10:50 user03.dbf
-rw-r----- 1 oracleoinstall5251072 05-11 10:18users01.dbf
[root@linux128orcl]# rm user03.dbf
rm：是否刪除 一般文件“user03.dbf”? y
[root@linux128orcl]# ll
總計 1589124
-rw-r----- 1 oracleoinstall10076160 05-11 10:54control01.ctl
-rw-r----- 1 oracleoinstall9748480 12-30 14:30control01.ctl.bk
-rw-r----- 1 oracleoinstall52429312 05-11 10:54 redo01.log
-rw-r----- 1 oracleoinstall52429312 05-11 10:09 redo02.log
-rw-r----- 1 oracleoinstall52429312 05-11 10:09 redo03.log
-rw-r----- 1 oracleoinstall 566239232 05-11 10:53 sysaux01.dbf
-rw-r----- 1 oracleoinstall 734011392 05-11 10:54 system01.dbf
-rw-r----- 1 oracleoinstall5251072 05-11 10:09 t1.dbf
-rw-r----- 1 oracleoinstall30416896 12-30 13:20 temp01.dbf
-rw-r----- 1 oracleoinstall5251072 12-30 14:00 temp02.dbf
-rw-r----- 1 oracleoinstall5251072 2012-10-22 temp03.dbf
-rw-r----- 1 oracleoinstall5251072 05-11 10:09 test.dbf
-rw-r----- 1 oracleoinstall78651392 05-11 10:54undotbs01.dbf
-rw-r----- 1 oracleoinstall31465472 05-11 10:09 user02.dbf
-rw-r----- 1 oracleoinstall5251072 05-11 10:18users01.dbf
[root@linux128orcl]#
-------我們通過過濾dbw進程，找出進程號，並查找到對應的文件句柄-------
此時務必不能做數據庫級別的offline或者關閉實例，否則將無法定位刪除文件。
[root@linux128 test]#ps -ef| grep dbw
oracle356710 09:58 ?00:00:01 ora_dbw0_orcl
root528744050 10:50 pts/700:00:00 grep dbw
---------------------------
[root@linux128 fd]# cd /proc/3567/fd
[root@linux128 fd]#ll
總計 0
lr-x------ 1 oracleoinstall 64 05-11 10:19 0 -> /dev/null
l-wx------ 1 oracleoinstall 64 05-11 10:19 1 -> /dev/null
l-wx------ 1 oracleoinstall 64 05-11 10:19 10 ->/data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3546.trc
l-wx------ 1 oracleoinstall 64 05-11 10:19 11 ->/data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3546.trm
lr-x------ 1 oracleoinstall 64 05-11 10:19 12 ->/data/oracle/product/11.2/db_1/rdbms/mesg/orazhs.msb
lr-x------ 1 oracleoinstall 64 05-11 10:19 13 -> /dev/zero
lr-x------ 1 oracleoinstall 64 05-11 10:19 14 -> /proc/3567/fd
lr-x------ 1 oracleoinstall 64 05-11 10:19 15 -> /dev/zero
lr-x------ 1 oracleoinstall 64 05-11 10:19 16 ->/data/oracle/product/11.2/db_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracleoinstall 64 05-11 10:19 17 -> /data/oracle/product/11.2/db_1/dbs/hc_orcl.dat
lrwx------ 1 oracleoinstall 64 05-11 10:19 18 -> /data/oracle/product/11.2/db_1/dbs/lkORCL
lrwx------ 1 oracleoinstall 64 05-11 10:19 19 -> /data/oracle/oradata/orcl/control01.ctl
l-wx------ 1 oracleoinstall 64 05-11 10:19 2 -> /dev/null
lrwx------ 1 oracleoinstall 64 05-11 10:19 20 ->/data/oracle/flash_recovery_area/orcl/control02.ctl
lrwx------ 1 oracleoinstall 64 05-11 10:19 21 -> socket:[80458]
lrwx------ 1 oracleoinstall 64 05-11 10:19 22 -> /data/oracle/oradata/orcl/system01.dbf
lrwx------ 1 oracleoinstall 64 05-11 10:19 23 -> /data/oracle/oradata/orcl/sysaux01.dbf
lrwx------ 1 oracleoinstall 64 05-11 10:19 24 -> /data/oracle/oradata/orcl/undotbs01.dbf
lrwx------ 1 oracleoinstall 64 05-11 10:19 25 -> /data/oracle/oradata/orcl/test.dbf
lrwx------ 1 oracleoinstall 64 05-11 10:19 26 -> /data/oracle/oradata/orcl/user02.dbf
lrwx------ 1 oracleoinstall 64 05-11 10:19 27 -> /data/oracle/oradata/orcl/t1.dbf
lr-x------ 1 oracleoinstall 64 05-11 10:19 28 ->/data/oracle/product/11.2/db_1/rdbms/mesg/orazhs.msb
lr-x------ 1 oracleoinstall 64 05-11 10:19 29 ->/data/oracle/product/11.2/db_1/rdbms/mesg/oraus.msb
l-wx------ 1 oracleoinstall 64 05-11 10:19 3 ->/data/oracle/product/11.2/db_1/rdbms/log/orcl_ora_3546.trc
lrwx------ 1 oracleoinstall 64 05-11 10:52 30 -> /data/oracle/oradata/orcl/user03.dbf (deleted)
lr-x------ 1 oracleoinstall 64 05-11 10:19 4 -> /dev/null
lr-x------ 1 oracleoinstall 64 05-11 10:19 5 -> /dev/null
lr-x------ 1 oracleoinstall 64 05-11 10:19 6 -> /dev/null
lrwx------ 1 oracleoinstall 64 05-11 10:19 7 -> /data/oracle/product/11.2/db_1/dbs/hc_orcl.dat
lrwx------ 1 oracleoinstall 64 05-11 10:19 8 -> /data/oracle/product/11.2/db_1/dbs/lkinstorcl(deleted)
lr-x------ 1 oracleoinstall 64 05-11 10:19 9 -> /proc/3567/fd
---------進入fd目錄後，可以看到user03.dbf已經被標示為delete，我們將查找到的文件拷貝到新目錄下，並改名----
[root@linux128 fd]# cp -p /proc/3567/fd/30 /data/user03.dbf
[root@linux128 test]# ll/data/user03.dbf
總計 215276
-rw-r--r-- 1rootroot104858053 05-11 10:45 test2.file
-rw-r--r-- 1rootroot104857600 05-11 10:43 test.file
-rw-r----- 1 oracleoinstall10493952 05-11 10:50 user03.dbf
[root@linux128 test]#
---因為是用的操作系統命令刪除文件，oracle此時並無異常，但是在offline後嘗試online時是會提示文件無法鎖定----
orcl@ SYS> alterdatabase datafile 8 offline drop;
Database altered.
Elapsed: 00:00:00.19
orcl@ SYS>
orcl@ SYS>
orcl@ SYS> selectfile#,status,name from v$datafile;
FILE# STATUSNAME
---------- ------------------------------------------------------------
1 SYSTEM/data/oracle/oradata/orcl/system01.dbf
2 ONLINE/data/oracle/oradata/orcl/sysaux01.dbf
3 ONLINE/data/oracle/oradata/orcl/undotbs01.dbf
4 RECOVER/data/oracle/oradata/orcl/users01.dbf
5 ONLINE/data/oracle/oradata/orcl/test.dbf
6 ONLINE/data/oracle/oradata/orcl/user02.dbf
7 ONLINE/data/oracle/oradata/orcl/t1.dbf
8 RECOVER/data/oracle/oradata/orcl/user03.dbf
8 rows selected.
Elapsed: 00:00:00.03
orcl@ SYS>
orcl@ SYS>
orcl@ SYS> alterdatabase datafile 8 online;
alter databasedatafile 8 online
*
ERROR at line 1:
ORA-01157: cannotidentify/lock data file 8 - see DBWR trace file
ORA-01110: data file8: '/data/oracle/oradata/orcl/user03.dbf'
Elapsed: 00:00:00.03
orcl@ SYS>
--------------我們對刪除的user03.dbf重命名執行新路徑----
orcl@ SYS> alterdatabase rename file '/data/oracle/oradata/orcl/user03.dbf' to'/data/user03.dbf';
Database altered.
Elapsed: 00:00:00.07
orcl@ SYS>
orcl@ SYS>
orcl@ SYS> alterdatabase datafile'/data/user03.dbf'online;
alter databasedatafile'/data/user03.dbf' online
*
ERROR at line 1:
ORA-01113: file 8needs media recovery
ORA-01110: data file8: '/data/user03.dbf'
Elapsed: 00:00:00.04
orcl@ SYS>
orcl@ SYS>
orcl@ SYS> recoverdatafile 8;
Media recoverycomplete.
orcl@ SYS>
orcl@ SYS>
orcl@ SYS> alterdatabase datafile'/data/user03.dbf'online;
Database altered.
Elapsed: 00:00:00.03
orcl@ SYS>
orcl@ SYS>
orcl@ SYS>selectfile#,status,name fromv$datafile;
FILE# STATUSNAME
---------- ------------------------------------------------------------
1 SYSTEM/data/oracle/oradata/orcl/system01.dbf
2 ONLINE/data/oracle/oradata/orcl/sysaux01.dbf
3 ONLINE/data/oracle/oradata/orcl/undotbs01.dbf
4 RECOVER/data/oracle/oradata/orcl/users01.dbf
5 ONLINE/data/oracle/oradata/orcl/test.dbf
6 ONLINE/data/oracle/oradata/orcl/user02.dbf
7 ONLINE/data/oracle/oradata/orcl/t1.dbf
8 ONLINE/data/user03.dbf
8 rows selected.
Elapsed: 00:00:00.04
orcl@ SYS>
----------數據文件成功恢復------
需要注意的，如果數據庫沒有歸檔，那麼操作一定要盡快，防止redo中關於user03.dbf的日志被沖掉。
更多RedHat相關信息見RedHat 專題頁面 http://www.linuxidc.com/topicnews.aspx?tid=10
