Linux環境利用恢復被rm意外刪除資料檔案

Linux環境下不小心在作業系統誤rm刪除資料檔案後，在沒有重啟資料庫或者作業系統的情況下可以利用作業系統控制程式碼恢復。[@more@]1、資料庫版本資訊：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
2、刪除資料檔案
SQL> select name from v$datafile
NAME
------------------------------------------------------------
/u01/app/oracle/oradata/dgdb02/system01.dbf
/u01/app/oracle/oradata/dgdb02/undotbs01.dbf
/u01/app/oracle/oradata/dgdb02/sysaux01.dbf
/u01/app/oracle/oradata/dgdb02/users01.dbf
/soft/oradata/jm_im_data.dbf
/soft/oradata/jm_his_ind.dbf
/soft/oradata/jm_im_ind.dbf
/soft/oradata/jm_his_data.dbf
8 rows selected.
SQL> !rm /soft/oradata/jm_his_data.dbf
SQL> !ls -l /soft/oradata/jm_his_data.dbf
ls: /soft/oradata/jm_his_data.dbf: No such file or directory
SQL> select status,instance_name from v$instance;
STATUS INSTANCE_NAME
------------ ----------------
OPEN dgdb02
SQL> create table tangyun tablespace jm_his_data as select * from dba_objects;
create table tangyun tablespace jm_his_data as select * from dba_objects
*
ERROR at line 1:
ORA-01565: error in identifying file '/soft/oradata/jm_his_data.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
3、在作業系統查詢並恢復資料檔案
[oracle@ty102g ~]$ ps -ef|grep dbw |grep -v grep
oracle 3641 1 0 16:24 ? 00:00:01 ora_dbw0_dgdb02
[oracle@ty102g ~]$ ll /proc/3641/fd
total 0
lr-x------ 1 oracle oinstall 64 Jun 12 17:42 0 -> /dev/null
lr-x------ 1 oracle oinstall 64 Jun 12 17:42 1 -> /dev/null
lr-x------ 1 oracle oinstall 64 Jun 12 17:42 10 -> /dev/zero
lr-x------ 1 oracle oinstall 64 Jun 12 17:42 11 -> /dev/zero
lr-x------ 1 oracle oinstall 64 Jun 12 17:42 12 -> /u01/app/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 13 -> /u01/app/oracle/product/10.2.0/db_1/dbs/hc_dgdb02.dat
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 14 -> /u01/app/oracle/product/10.2.0/db_1/dbs/lkDGDB02
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 15 -> /u01/app/oracle/oradata/dgdb02/control01.ctl
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 16 -> /u01/app/oracle/oradata/dgdb02/control02.ctl
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 17 -> /u01/app/oracle/oradata/dgdb02/control03.ctl
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 18 -> /u01/app/oracle/oradata/dgdb02/system01.dbf
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 19 -> /u01/app/oracle/oradata/dgdb02/undotbs01.dbf
l-wx------ 1 oracle oinstall 64 Jun 12 17:42 2 -> /u01/app/oracle/admin/dgdb02/bdump/dgdb02_dbw0_3641.trc
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 20 -> /u01/app/oracle/oradata/dgdb02/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 21 -> /u01/app/oracle/oradata/dgdb02/users01.dbf
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 22 -> /soft/oradata/jm_im_data.dbf
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 23 -> /soft/oradata/gd_com_data.dbf
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 24 -> /soft/oradata/jm_his_ind.dbf
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 25 -> /soft/oradata/jm_om_data.dbf
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 26 -> /soft/oradata/jm_cm_data.dbf
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 27 -> /soft/oradata/jm_cm_ind.dbf
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 28 -> /soft/oradata/gd_com_ind.dbf
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 29 -> /soft/oradata/jm_im_ind.dbf
lr-x------ 1 oracle oinstall 64 Jun 12 17:42 3 -> /dev/null
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 30 -> /soft/oradata/jm_his_data.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 31 -> /soft/oradata/jm_om_ind.dbf
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 32 -> /soft/oradata/zh_his_data.dbf
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 33 -> /soft/oradata/zh_om_ind.dbf
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 34 -> /soft/oradata/zh_om_data.dbf
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 35 -> /soft/oradata/zh_his_ind.dbf
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 36 -> /soft/oradata/zh_cm_data.dbf
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 37 -> /soft/oradata/zh_cm_ind.dbf
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 38 -> /soft/oradata/zh_im_data.dbf
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 39 -> /soft/oradata/zh_im_ind.dbf
lr-x------ 1 oracle oinstall 64 Jun 12 17:42 4 -> /dev/null
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 40 -> /soft/oradata/tbs_data01.dbf
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 41 -> /u01/app/oracle/oradata/dgdb02/temp01.dbf
lr-x------ 1 oracle oinstall 64 Jun 12 17:42 42 -> /u01/app/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb
l-wx------ 1 oracle oinstall 64 Jun 12 17:42 5 -> /u01/app/oracle/admin/dgdb02/udump/dgdb02_ora_3532.trc
l-wx------ 1 oracle oinstall 64 Jun 12 17:42 6 -> /u01/app/oracle/admin/dgdb02/bdump/alert_dgdb02.log
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 7 -> /u01/app/oracle/product/10.2.0/db_1/dbs/lkinstdgdb02 (deleted)
l-wx------ 1 oracle oinstall 64 Jun 12 17:42 8 -> /u01/app/oracle/admin/dgdb02/bdump/alert_dgdb02.log
lrwx------ 1 oracle oinstall 64 Jun 12 17:42 9 -> /u01/app/oracle/product/10.2.0/db_1/dbs/hc_dgdb02.dat
[oracle@ty102g ~]$ cp /proc/3641/fd/30 /soft/oradata/jm_his_data.dbf
[oracle@ty102g ~]$ ll /soft/oradata/jm_his_data.dbf
-rw-r----- 1 oracle oinstall 93724672 Jun 12 17:44 /soft/oradata/jm_his_data.dbf
[oracle@ty102g ~]$ ora si
SQL*Plus: Release 10.2.0.4.0 - Production on 星期二 6月 12 17:45:03 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
----操作1，直接online資料檔案
SQL> alter database datafile '/soft/oradata/jm_his_data.dbf' online;
Database altered.
SQL> create table tangyun tablespace jm_his_data as select * from dba_objects;
Table created.
------操作2，先offline再recover再online，結果失敗
SQL> alter database datafile '/soft/oradata/jm_his_data.dbf' offline;
Database altered.
SQL> recover datafile '/soft/oradata/jm_his_data.dbf';
Media recovery complete.
SQL> alter database datafile '/soft/oradata/jm_his_data.dbf' online;
Database altered.
SQL> create table tangyun1 tablespace jm_his_data as select * from dba_objects;
create table tangyun1 tablespace jm_his_data as select * from dba_objects
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [3688], [11440], [1], [12720],
[12848], [], [], []
/*******************Oracle Support 743078.1**************************************************/
The ORA-600 [3688] means the the current file size in the file header is less than, or not equal to either the old size or the new size. It is not possible to update file headers to correct this. This would imply the datafile is corrupted or the datafile header is corrupted.
To resolve a corrupted datafile means a restore from backup and recovery if needed.
/**********************************************************************/
SQL> shutdown immediate
ORA-03113: end-of-file on communication channel
SQL> select status from v$instance;
ERROR:
ORA-03114: not connected to ORACLE
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ty102g ~]$ ora si
SQL*Plus: Release 10.2.0.4.0 - Production on 星期二 6月 12 18:24:10 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 2083592 bytes
Variable Size 138413304 bytes
Database Buffers 167772160 bytes
Redo Buffers 6303744 bytes
Database mounted.
ORA-01113: file 13 needs media recovery
ORA-01110: data file 8: '/soft/oradata/jm_his_data.dbf'
SQL> recover datafile 8;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> create table tangyun1 tablespace jm_his_data as select * from dba_objects;
Table created.
SQL> drop table tangyun1 purge;
Table dropped.
不小心在作業系統刪除資料檔案時，千萬不要慌張重啟資料庫或者作業系統，可以透過dbwn程式相關控制程式碼找回資料檔案。
----------------End----------------------