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----------------------
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----------------------