Linux环境利用恢复被rm意外删除数据文件

淩亂°似流年 2022-08-12 01:18 30阅读 0赞

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