A-A+
无备份情况下的数据文件恢复一例
下边的例子是数据库始终在启动状态下，通过操作系统文件描述符恢复误删除的数据库的数据文件。
恢复条件：数据库始终处于启动状态。
注意：通过lsof命令找操作系统文件号和数据库文件的对应关系是关键。
操作系统信息
[oracle@rhl6 ~]$ uname -a Linux rhl6.0x64 2.6.32-71.el6.x86_64 #1 SMP Wed Sep 1 01:33:01 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
数据库版本
SQL> set line 300 SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production
模拟数据
SQL> create tablespace yallonking datafile '/u01/testdir/yallonking01.dbf' size 10m;
Tablespace created.
SQL> create user yallonking identified by yallonking default tablespace yallonking quota unlimited on yallonking;
User created.
SQL> grant dba to yallonking;
Grant succeeded.
SQL> conn yallonking/yallonking
Connected.
SQL> create table yallonking (id number,name varchar2(30),my_date date);
Table created.
SQL> insert into yallonking values(1,'yallonking',sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
Session altered.
SQL> select * from yallonking;
ID NAME MY_DATE
---------- ------------------------------ -------------------
1 yallonking 2013/07/16 14:47:28
SQL> col name for a50
SQL> select FILE#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/ora11gr2/system01.dbf
2 /u01/app/oracle/oradata/ora11gr2/sysaux01.dbf
3 /u01/app/oracle/oradata/ora11gr2/undotbs01.dbf
4 /u01/app/oracle/oradata/ora11gr2/users01.dbf
5 /u01/testdir/yallonking01.dbf
从操作系统层面删除目录/testdir模拟数据文件丢失
[oracle@rhl6 ~]$ cd /u01 [oracle@rhl6 u01]$ ls -ltr total 8 drwxr-xr-x. 4 oracle oinstall 4096 Dec 24 2012 app drwxr-xr-x. 2 oracle oinstall 4096 Jul 16 14:44 testdir [oracle@rhl6 u01]$ ls -l testdir/ total 10248 -rw-r-----. 1 oracle oinstall 10493952 Jul 16 14:44 yallonking01.dbf [oracle@rhl6 u01]$ rm -rf testdir [oracle@rhl6 u01]$ ls -l total 4 drwxr-xr-x. 4 oracle oinstall 4096 Dec 24 2012 app
在清空缓存后，查询报错
SQL> select * from yallonking.yallonking;
ID NAME MY_DATE
---------- ------------------------------ ---------
1 yallonking 16-JUL-13
SQL> alter system flush BUFFER_CACHE;
System altered.
SQL> select * from yallonking.yallonking;
select * from yallonking.yallonking
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/testdir/yallonking01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
日志报错如下
Tue Jul 16 14:54:57 2013 ALTER SYSTEM: Flushing buffer cache Tue Jul 16 14:55:07 2013 Checker run found 1 new persistent data failures Tue Jul 16 14:58:26 2013 Errors in file /u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_m000_2160.trc: ORA-01116: error in opening database file 5 ORA-01110: data file 5: '/u01/testdir/yallonking01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3
----------------------开始恢复----------------------
在操作系统查看丢失文件的文件号
[root@rhl6 ~]# ps -ef | grep dbw | grep -v grep oracle 1809 1 0 14:38 ? 00:00:00 ora_dbw0_ora11gr2 [root@rhl6 ~]# lsof -p 1809 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME oracle 1809 oracle cwd DIR 8,3 4096 1046904 /u01/app/oracle/product/11.2.0/db_1/dbs oracle 1809 oracle rtd DIR 8,3 4096 2 / oracle 1809 oracle txt REG 8,3 232472284 1584882 /u01/app/oracle/product/11.2.0/db_1/bin/oracle oracle 1809 oracle mem REG 0,17 4194304 14436 /dev/shm/ora_ora11gr2_32768_0 oracle 1809 oracle mem REG 0,17 4194304 14437 /dev/shm/ora_ora11gr2_32768_1 oracle 1809 oracle mem REG 0,17 4194304 14438 /dev/shm/ora_ora11gr2_32768_2 oracle 1809 oracle mem REG 0,17 0 14442 /dev/shm/ora_ora11gr2_65537_0 oracle 1809 oracle mem REG 0,17 0 14443 /dev/shm/ora_ora11gr2_65537_1 oracle 1809 oracle mem REG 0,17 0 14444 /dev/shm/ora_ora11gr2_65537_2 oracle 1809 oracle mem REG 0,17 0 14445 /dev/shm/ora_ora11gr2_65537_3 oracle 1809 oracle mem REG 0,17 0 14446 /dev/shm/ora_ora11gr2_65537_4 oracle 1809 oracle mem REG 0,17 0 14447 /dev/shm/ora_ora11gr2_65537_5 oracle 1809 oracle mem REG 0,17 0 14448 /dev/shm/ora_ora11gr2_65537_6 oracle 1809 oracle mem REG 0,17 0 14449 /dev/shm/ora_ora11gr2_65537_7 oracle 1809 oracle mem REG 0,17 0 14450 /dev/shm/ora_ora11gr2_65537_8 oracle 1809 oracle mem REG 0,17 0 14451 /dev/shm/ora_ora11gr2_65537_9 oracle 1809 oracle mem REG 0,17 0 14452 /dev/shm/ora_ora11gr2_65537_10 oracle 1809 oracle mem REG 0,17 0 14453 /dev/shm/ora_ora11gr2_65537_11 oracle 1809 oracle mem REG 0,17 0 14454 /dev/shm/ora_ora11gr2_65537_12 oracle 1809 oracle mem REG 0,17 0 14455 /dev/shm/ora_ora11gr2_65537_13 oracle 1809 oracle mem REG 0,17 0 14456 /dev/shm/ora_ora11gr2_65537_14 oracle 1809 oracle mem REG 0,17 0 14457 /dev/shm/ora_ora11gr2_65537_15 oracle 1809 oracle mem REG 0,17 0 14458 /dev/shm/ora_ora11gr2_65537_16 oracle 1809 oracle mem REG 0,17 0 14459 /dev/shm/ora_ora11gr2_65537_17 oracle 1809 oracle mem REG 0,17 0 14460 /dev/shm/ora_ora11gr2_65537_18 oracle 1809 oracle mem REG 0,17 0 14461 /dev/shm/ora_ora11gr2_65537_19 oracle 1809 oracle mem REG 0,17 0 14462 /dev/shm/ora_ora11gr2_65537_20 oracle 1809 oracle mem REG 0,17 0 14463 /dev/shm/ora_ora11gr2_65537_21 oracle 1809 oracle mem REG 0,17 0 14464 /dev/shm/ora_ora11gr2_65537_22 oracle 1809 oracle mem REG 0,17 0 14465 /dev/shm/ora_ora11gr2_65537_23 oracle 1809 oracle mem REG 0,17 0 14466 /dev/shm/ora_ora11gr2_65537_24 oracle 1809 oracle mem REG 0,17 0 14467 /dev/shm/ora_ora11gr2_65537_25 oracle 1809 oracle mem REG 0,17 0 14468 /dev/shm/ora_ora11gr2_65537_26 oracle 1809 oracle mem REG 0,17 0 14469 /dev/shm/ora_ora11gr2_65537_27 oracle 1809 oracle mem REG 0,17 0 14470 /dev/shm/ora_ora11gr2_65537_28 oracle 1809 oracle mem REG 0,17 0 14471 /dev/shm/ora_ora11gr2_65537_29 oracle 1809 oracle mem REG 0,17 0 14472 /dev/shm/ora_ora11gr2_65537_30 oracle 1809 oracle mem REG 0,17 0 14473 /dev/shm/ora_ora11gr2_65537_31 oracle 1809 oracle mem REG 0,17 0 14474 /dev/shm/ora_ora11gr2_65537_32 oracle 1809 oracle mem REG 0,17 0 14475 /dev/shm/ora_ora11gr2_65537_33 oracle 1809 oracle mem REG 0,17 0 14476 /dev/shm/ora_ora11gr2_65537_34 oracle 1809 oracle mem REG 0,17 0 14477 /dev/shm/ora_ora11gr2_65537_35 oracle 1809 oracle mem REG 0,17 0 14478 /dev/shm/ora_ora11gr2_65537_36 oracle 1809 oracle mem REG 0,17 0 14479 /dev/shm/ora_ora11gr2_65537_37 oracle 1809 oracle mem REG 0,17 0 14480 /dev/shm/ora_ora11gr2_65537_38 oracle 1809 oracle mem REG 0,17 0 14481 /dev/shm/ora_ora11gr2_65537_39 oracle 1809 oracle mem REG 0,17 0 14482 /dev/shm/ora_ora11gr2_65537_40 oracle 1809 oracle mem REG 0,17 0 14483 /dev/shm/ora_ora11gr2_65537_41 oracle 1809 oracle mem REG 0,17 0 14484 /dev/shm/ora_ora11gr2_65537_42 oracle 1809 oracle mem REG 0,17 0 14485 /dev/shm/ora_ora11gr2_65537_43 oracle 1809 oracle mem REG 0,17 0 14486 /dev/shm/ora_ora11gr2_65537_44 oracle 1809 oracle mem REG 0,17 0 14487 /dev/shm/ora_ora11gr2_65537_45 oracle 1809 oracle mem REG 0,17 0 14488 /dev/shm/ora_ora11gr2_65537_46 oracle 1809 oracle mem REG 0,17 0 14489 /dev/shm/ora_ora11gr2_65537_47 oracle 1809 oracle mem REG 0,17 0 14490 /dev/shm/ora_ora11gr2_65537_48 oracle 1809 oracle mem REG 0,17 0 14491 /dev/shm/ora_ora11gr2_65537_49 oracle 1809 oracle mem REG 0,17 0 14492 /dev/shm/ora_ora11gr2_65537_50 oracle 1809 oracle mem REG 0,17 0 14493 /dev/shm/ora_ora11gr2_65537_51 oracle 1809 oracle mem REG 0,17 0 14494 /dev/shm/ora_ora11gr2_65537_52 oracle 1809 oracle mem REG 0,17 0 14495 /dev/shm/ora_ora11gr2_65537_53 oracle 1809 oracle mem REG 0,17 0 14496 /dev/shm/ora_ora11gr2_65537_54 oracle 1809 oracle mem REG 0,17 0 14497 /dev/shm/ora_ora11gr2_65537_55 oracle 1809 oracle mem REG 0,17 0 14498 /dev/shm/ora_ora11gr2_65537_56 oracle 1809 oracle mem REG 0,17 0 14499 /dev/shm/ora_ora11gr2_65537_57 oracle 1809 oracle mem REG 0,17 0 14500 /dev/shm/ora_ora11gr2_65537_58 oracle 1809 oracle mem REG 0,17 0 14501 /dev/shm/ora_ora11gr2_65537_59 oracle 1809 oracle mem REG 0,17 0 14502 /dev/shm/ora_ora11gr2_65537_60 oracle 1809 oracle mem REG 0,17 0 14503 /dev/shm/ora_ora11gr2_65537_61 oracle 1809 oracle mem REG 0,17 0 14504 /dev/shm/ora_ora11gr2_65537_62 oracle 1809 oracle mem REG 0,17 0 14505 /dev/shm/ora_ora11gr2_65537_63 oracle 1809 oracle mem REG 0,17 0 14506 /dev/shm/ora_ora11gr2_65537_64 oracle 1809 oracle mem REG 0,17 0 14507 /dev/shm/ora_ora11gr2_65537_65 oracle 1809 oracle mem REG 0,17 0 14508 /dev/shm/ora_ora11gr2_65537_66 oracle 1809 oracle mem REG 0,17 0 14509 /dev/shm/ora_ora11gr2_65537_67 oracle 1809 oracle mem REG 0,17 0 14510 /dev/shm/ora_ora11gr2_65537_68 oracle 1809 oracle mem REG 0,17 0 14511 /dev/shm/ora_ora11gr2_65537_69 oracle 1809 oracle mem REG 0,17 0 14512 /dev/shm/ora_ora11gr2_65537_70 oracle 1809 oracle mem REG 0,17 0 14513 /dev/shm/ora_ora11gr2_65537_71 oracle 1809 oracle mem REG 0,17 0 14514 /dev/shm/ora_ora11gr2_65537_72 oracle 1809 oracle mem REG 0,17 0 14515 /dev/shm/ora_ora11gr2_65537_73 oracle 1809 oracle mem REG 0,17 0 14516 /dev/shm/ora_ora11gr2_65537_74 oracle 1809 oracle mem REG 0,17 0 14517 /dev/shm/ora_ora11gr2_65537_75 oracle 1809 oracle mem REG 0,17 0 14518 /dev/shm/ora_ora11gr2_65537_76 oracle 1809 oracle mem REG 0,17 0 14519 /dev/shm/ora_ora11gr2_65537_77 oracle 1809 oracle mem REG 0,17 0 14520 /dev/shm/ora_ora11gr2_65537_78 oracle 1809 oracle mem REG 0,17 0 14521 /dev/shm/ora_ora11gr2_65537_79 oracle 1809 oracle mem REG 0,17 0 14522 /dev/shm/ora_ora11gr2_65537_80 oracle 1809 oracle mem REG 0,17 0 14523 /dev/shm/ora_ora11gr2_65537_81 oracle 1809 oracle mem REG 0,17 0 14524 /dev/shm/ora_ora11gr2_65537_82 oracle 1809 oracle mem REG 0,17 0 14525 /dev/shm/ora_ora11gr2_65537_83 oracle 1809 oracle mem REG 0,17 0 14526 /dev/shm/ora_ora11gr2_65537_84 oracle 1809 oracle mem REG 0,17 0 14527 /dev/shm/ora_ora11gr2_65537_85 oracle 1809 oracle mem REG 0,17 0 14528 /dev/shm/ora_ora11gr2_65537_86 oracle 1809 oracle mem REG 0,17 0 14529 /dev/shm/ora_ora11gr2_65537_87 oracle 1809 oracle mem REG 0,17 0 14530 /dev/shm/ora_ora11gr2_65537_88 oracle 1809 oracle mem REG 0,17 0 14531 /dev/shm/ora_ora11gr2_65537_89 oracle 1809 oracle mem REG 0,17 4194304 14532 /dev/shm/ora_ora11gr2_65537_90 oracle 1809 oracle mem REG 0,17 4194304 14533 /dev/shm/ora_ora11gr2_65537_91 oracle 1809 oracle mem REG 0,17 4194304 14534 /dev/shm/ora_ora11gr2_65537_92 oracle 1809 oracle mem REG 0,17 4194304 14535 /dev/shm/ora_ora11gr2_65537_93 oracle 1809 oracle mem REG 0,17 4194304 14536 /dev/shm/ora_ora11gr2_65537_94 oracle 1809 oracle mem REG 0,17 4194304 14537 /dev/shm/ora_ora11gr2_65537_95 oracle 1809 oracle mem REG 0,17 4194304 14538 /dev/shm/ora_ora11gr2_65537_96 oracle 1809 oracle mem REG 0,17 4194304 14539 /dev/shm/ora_ora11gr2_65537_97 oracle 1809 oracle mem REG 0,17 4194304 14540 /dev/shm/ora_ora11gr2_65537_98 oracle 1809 oracle mem REG 0,17 4194304 14541 /dev/shm/ora_ora11gr2_65537_99 oracle 1809 oracle mem REG 0,17 4194304 14542 /dev/shm/ora_ora11gr2_65537_100 oracle 1809 oracle mem REG 0,17 4194304 14543 /dev/shm/ora_ora11gr2_65537_101 oracle 1809 oracle mem REG 0,17 4194304 14544 /dev/shm/ora_ora11gr2_65537_102 oracle 1809 oracle mem REG 0,17 4194304 14545 /dev/shm/ora_ora11gr2_65537_103 oracle 1809 oracle mem REG 0,17 4194304 14546 /dev/shm/ora_ora11gr2_65537_104 oracle 1809 oracle mem REG 0,17 4194304 14547 /dev/shm/ora_ora11gr2_65537_105 oracle 1809 oracle mem REG 0,17 4194304 14548 /dev/shm/ora_ora11gr2_65537_106 oracle 1809 oracle mem REG 0,17 4194304 14549 /dev/shm/ora_ora11gr2_65537_107 oracle 1809 oracle mem REG 0,17 4194304 14550 /dev/shm/ora_ora11gr2_65537_108 oracle 1809 oracle mem REG 0,17 4194304 14551 /dev/shm/ora_ora11gr2_65537_109 oracle 1809 oracle mem REG 0,17 4194304 14552 /dev/shm/ora_ora11gr2_65537_110 oracle 1809 oracle mem REG 0,17 4194304 14553 /dev/shm/ora_ora11gr2_65537_111 oracle 1809 oracle mem REG 0,17 4194304 14554 /dev/shm/ora_ora11gr2_65537_112 oracle 1809 oracle mem REG 0,17 4194304 14555 /dev/shm/ora_ora11gr2_65537_113 oracle 1809 oracle mem REG 0,17 4194304 14556 /dev/shm/ora_ora11gr2_65537_114 oracle 1809 oracle mem REG 0,17 4194304 14557 /dev/shm/ora_ora11gr2_65537_115 oracle 1809 oracle mem REG 0,17 4194304 14558 /dev/shm/ora_ora11gr2_65537_116 oracle 1809 oracle mem REG 0,17 4194304 14559 /dev/shm/ora_ora11gr2_65537_117 oracle 1809 oracle mem REG 0,17 4194304 14560 /dev/shm/ora_ora11gr2_65537_118 oracle 1809 oracle mem REG 0,17 4194304 14561 /dev/shm/ora_ora11gr2_65537_119 oracle 1809 oracle mem REG 0,17 4194304 14562 /dev/shm/ora_ora11gr2_65537_120 oracle 1809 oracle mem REG 0,17 4194304 14563 /dev/shm/ora_ora11gr2_65537_121 oracle 1809 oracle mem REG 0,17 4194304 14564 /dev/shm/ora_ora11gr2_65537_122 oracle 1809 oracle mem REG 0,17 4194304 14565 /dev/shm/ora_ora11gr2_65537_123 oracle 1809 oracle mem REG 0,17 4194304 14566 /dev/shm/ora_ora11gr2_65537_124 oracle 1809 oracle mem REG 0,17 4194304 14567 /dev/shm/ora_ora11gr2_65537_125 oracle 1809 oracle mem REG 0,17 4194304 14568 /dev/shm/ora_ora11gr2_65537_126 oracle 1809 oracle mem REG 0,17 4194304 14569 /dev/shm/ora_ora11gr2_65537_127 oracle 1809 oracle mem REG 0,17 4194304 14570 /dev/shm/ora_ora11gr2_65537_128 oracle 1809 oracle mem REG 0,17 4194304 14571 /dev/shm/ora_ora11gr2_65537_129 oracle 1809 oracle mem REG 0,17 4194304 14572 /dev/shm/ora_ora11gr2_65537_130 oracle 1809 oracle mem REG 0,17 4194304 14573 /dev/shm/ora_ora11gr2_65537_131 oracle 1809 oracle mem REG 0,17 4194304 14574 /dev/shm/ora_ora11gr2_65537_132 oracle 1809 oracle mem REG 0,17 4194304 14575 /dev/shm/ora_ora11gr2_65537_133 oracle 1809 oracle mem REG 0,17 4194304 14576 /dev/shm/ora_ora11gr2_65537_134 oracle 1809 oracle mem REG 0,17 4194304 14577 /dev/shm/ora_ora11gr2_65537_135 oracle 1809 oracle mem REG 0,17 4194304 14578 /dev/shm/ora_ora11gr2_65537_136 oracle 1809 oracle mem REG 0,17 4194304 14579 /dev/shm/ora_ora11gr2_65537_137 oracle 1809 oracle mem REG 0,17 4194304 14580 /dev/shm/ora_ora11gr2_65537_138 oracle 1809 oracle mem REG 0,17 4194304 14581 /dev/shm/ora_ora11gr2_65537_139 oracle 1809 oracle mem REG 0,17 4194304 14582 /dev/shm/ora_ora11gr2_65537_140 oracle 1809 oracle mem REG 0,17 4194304 14583 /dev/shm/ora_ora11gr2_65537_141 oracle 1809 oracle mem REG 0,17 4194304 14584 /dev/shm/ora_ora11gr2_65537_142 oracle 1809 oracle mem REG 0,17 4194304 14585 /dev/shm/ora_ora11gr2_65537_143 oracle 1809 oracle mem REG 0,17 4194304 14586 /dev/shm/ora_ora11gr2_65537_144 oracle 1809 oracle mem REG 0,17 4194304 14587 /dev/shm/ora_ora11gr2_65537_145 oracle 1809 oracle mem REG 0,17 4194304 14588 /dev/shm/ora_ora11gr2_65537_146 oracle 1809 oracle mem REG 0,17 4194304 14589 /dev/shm/ora_ora11gr2_65537_147 oracle 1809 oracle mem REG 0,17 4194304 14590 /dev/shm/ora_ora11gr2_65537_148 oracle 1809 oracle mem REG 0,17 4194304 14591 /dev/shm/ora_ora11gr2_65537_149 oracle 1809 oracle mem REG 0,17 4194304 14592 /dev/shm/ora_ora11gr2_65537_150 oracle 1809 oracle mem REG 0,17 4194304 14593 /dev/shm/ora_ora11gr2_65537_151 oracle 1809 oracle mem REG 0,17 4194304 14594 /dev/shm/ora_ora11gr2_65537_152 oracle 1809 oracle mem REG 0,17 4194304 14595 /dev/shm/ora_ora11gr2_65537_153 oracle 1809 oracle mem REG 0,17 4194304 14596 /dev/shm/ora_ora11gr2_65537_154 oracle 1809 oracle mem REG 0,17 4194304 14597 /dev/shm/ora_ora11gr2_65537_155 oracle 1809 oracle mem REG 0,17 4194304 14598 /dev/shm/ora_ora11gr2_65537_156 oracle 1809 oracle mem REG 0,17 4194304 14599 /dev/shm/ora_ora11gr2_65537_157 oracle 1809 oracle mem REG 0,17 4194304 14600 /dev/shm/ora_ora11gr2_65537_158 oracle 1809 oracle mem REG 0,17 4194304 14601 /dev/shm/ora_ora11gr2_65537_159 oracle 1809 oracle mem REG 0,17 4194304 14602 /dev/shm/ora_ora11gr2_65537_160 oracle 1809 oracle mem REG 0,17 4194304 14603 /dev/shm/ora_ora11gr2_65537_161 oracle 1809 oracle mem REG 0,17 4194304 14604 /dev/shm/ora_ora11gr2_65537_162 oracle 1809 oracle mem REG 0,17 4194304 14605 /dev/shm/ora_ora11gr2_65537_163 oracle 1809 oracle mem REG 0,17 4194304 14606 /dev/shm/ora_ora11gr2_65537_164 oracle 1809 oracle mem REG 0,17 4194304 14607 /dev/shm/ora_ora11gr2_65537_165 oracle 1809 oracle mem REG 0,17 4194304 14608 /dev/shm/ora_ora11gr2_65537_166 oracle 1809 oracle mem REG 0,17 4194304 14609 /dev/shm/ora_ora11gr2_65537_167 oracle 1809 oracle mem REG 0,17 4194304 14610 /dev/shm/ora_ora11gr2_65537_168 oracle 1809 oracle mem REG 0,17 4194304 14611 /dev/shm/ora_ora11gr2_65537_169 oracle 1809 oracle mem REG 0,17 4194304 14612 /dev/shm/ora_ora11gr2_65537_170 oracle 1809 oracle mem REG 0,17 4194304 14613 /dev/shm/ora_ora11gr2_65537_171 oracle 1809 oracle mem REG 0,17 4194304 14614 /dev/shm/ora_ora11gr2_65537_172 oracle 1809 oracle mem REG 0,17 4194304 14615 /dev/shm/ora_ora11gr2_65537_173 oracle 1809 oracle mem REG 0,17 4194304 14616 /dev/shm/ora_ora11gr2_65537_174 oracle 1809 oracle mem REG 0,17 4194304 14617 /dev/shm/ora_ora11gr2_65537_175 oracle 1809 oracle mem REG 0,17 4194304 14618 /dev/shm/ora_ora11gr2_65537_176 oracle 1809 oracle mem REG 0,17 4194304 14619 /dev/shm/ora_ora11gr2_65537_177 oracle 1809 oracle mem REG 0,17 4194304 14620 /dev/shm/ora_ora11gr2_65537_178 oracle 1809 oracle mem REG 0,17 4194304 14621 /dev/shm/ora_ora11gr2_65537_179 oracle 1809 oracle mem REG 0,17 4194304 14622 /dev/shm/ora_ora11gr2_65537_180 oracle 1809 oracle mem REG 0,17 4194304 14623 /dev/shm/ora_ora11gr2_65537_181 oracle 1809 oracle mem REG 0,17 4194304 14624 /dev/shm/ora_ora11gr2_65537_182 oracle 1809 oracle mem REG 0,17 4194304 14625 /dev/shm/ora_ora11gr2_65537_183 oracle 1809 oracle mem REG 0,17 4194304 14626 /dev/shm/ora_ora11gr2_65537_184 oracle 1809 oracle mem REG 0,17 4194304 14627 /dev/shm/ora_ora11gr2_65537_185 oracle 1809 oracle mem REG 0,17 4194304 14628 /dev/shm/ora_ora11gr2_65537_186 oracle 1809 oracle mem REG 0,17 4194304 14629 /dev/shm/ora_ora11gr2_65537_187 oracle 1809 oracle mem REG 0,17 4194304 14630 /dev/shm/ora_ora11gr2_65537_188 oracle 1809 oracle mem REG 0,17 4194304 14631 /dev/shm/ora_ora11gr2_65537_189 oracle 1809 oracle mem REG 0,17 4194304 14632 /dev/shm/ora_ora11gr2_65537_190 oracle 1809 oracle mem REG 0,17 4194304 14633 /dev/shm/ora_ora11gr2_65537_191 oracle 1809 oracle mem REG 0,17 4194304 14634 /dev/shm/ora_ora11gr2_65537_192 oracle 1809 oracle mem REG 0,17 4194304 14635 /dev/shm/ora_ora11gr2_65537_193 oracle 1809 oracle mem REG 0,17 4194304 14636 /dev/shm/ora_ora11gr2_65537_194 oracle 1809 oracle mem REG 0,17 4194304 14637 /dev/shm/ora_ora11gr2_65537_195 oracle 1809 oracle mem REG 0,17 4194304 14638 /dev/shm/ora_ora11gr2_65537_196 oracle 1809 oracle mem REG 0,17 4194304 14639 /dev/shm/ora_ora11gr2_65537_197 oracle 1809 oracle mem REG 0,17 4194304 14640 /dev/shm/ora_ora11gr2_65537_198 oracle 1809 oracle mem REG 0,17 4194304 14641 /dev/shm/ora_ora11gr2_65537_199 oracle 1809 oracle mem REG 0,17 4194304 14642 /dev/shm/ora_ora11gr2_65537_200 oracle 1809 oracle mem REG 0,17 4194304 14643 /dev/shm/ora_ora11gr2_65537_201 oracle 1809 oracle mem REG 0,17 4194304 14644 /dev/shm/ora_ora11gr2_65537_202 oracle 1809 oracle mem REG 0,17 4194304 14645 /dev/shm/ora_ora11gr2_65537_203 oracle 1809 oracle mem REG 0,17 4194304 14646 /dev/shm/ora_ora11gr2_65537_204 oracle 1809 oracle mem REG 0,17 4194304 14647 /dev/shm/ora_ora11gr2_65537_205 oracle 1809 oracle mem REG 0,17 4194304 14648 /dev/shm/ora_ora11gr2_65537_206 oracle 1809 oracle mem REG 0,17 4194304 14649 /dev/shm/ora_ora11gr2_65537_207 oracle 1809 oracle mem REG 0,17 4194304 14650 /dev/shm/ora_ora11gr2_65537_208 oracle 1809 oracle mem REG 0,17 4194304 14651 /dev/shm/ora_ora11gr2_65537_209 oracle 1809 oracle mem REG 0,17 4194304 14652 /dev/shm/ora_ora11gr2_65537_210 oracle 1809 oracle mem REG 0,17 4194304 14653 /dev/shm/ora_ora11gr2_65537_211 oracle 1809 oracle mem REG 0,17 4194304 14654 /dev/shm/ora_ora11gr2_65537_212 oracle 1809 oracle mem REG 0,17 4194304 14655 /dev/shm/ora_ora11gr2_65537_213 oracle 1809 oracle mem REG 0,17 4194304 14656 /dev/shm/ora_ora11gr2_65537_214 oracle 1809 oracle mem REG 0,17 4194304 14657 /dev/shm/ora_ora11gr2_65537_215 oracle 1809 oracle mem REG 0,17 4194304 14658 /dev/shm/ora_ora11gr2_65537_216 oracle 1809 oracle mem REG 0,17 4194304 14659 /dev/shm/ora_ora11gr2_65537_217 oracle 1809 oracle mem REG 0,17 4194304 14660 /dev/shm/ora_ora11gr2_65537_218 oracle 1809 oracle mem REG 0,17 4194304 14661 /dev/shm/ora_ora11gr2_65537_219 oracle 1809 oracle mem REG 0,17 4194304 14662 /dev/shm/ora_ora11gr2_65537_220 oracle 1809 oracle mem REG 0,17 4194304 14663 /dev/shm/ora_ora11gr2_65537_221 oracle 1809 oracle mem REG 0,17 4194304 14666 /dev/shm/ora_ora11gr2_98306_0 oracle 1809 oracle mem REG 8,3 150672 654453 /lib64/ld-2.12.so oracle 1809 oracle mem REG 8,3 5624 654218 /lib64/libaio.so.1.0.1 oracle 1809 oracle mem REG 8,3 1838296 654454 /lib64/libc-2.12.so oracle 1809 oracle mem REG 8,3 145672 654462 /lib64/libpthread-2.12.so oracle 1809 oracle mem REG 8,3 598816 654469 /lib64/libm-2.12.so oracle 1809 oracle mem REG 8,3 47072 654466 /lib64/librt-2.12.so oracle 1809 oracle mem REG 8,3 35752 1453157 /usr/lib64/libnuma.so.1 oracle 1809 oracle mem REG 8,3 116136 654491 /lib64/libnsl-2.12.so oracle 1809 oracle mem CHR 1,5 3739 /dev/zero oracle 1809 oracle mem REG 8,3 1544 1050855 /u01/app/oracle/product/11.2.0/db_1/dbs/hc_ora11gr2.dat oracle 1809 oracle mem REG 8,3 61624 654110 /lib64/libnss_files-2.12.so oracle 1809 oracle mem REG 8,3 22536 654456 /lib64/libdl-2.12.so oracle 1809 oracle mem REG 8,3 153574 1066413 /u01/app/oracle/product/11.2.0/db_1/lib/libocrutl11.so oracle 1809 oracle mem REG 8,3 3319072 1066412 /u01/app/oracle/product/11.2.0/db_1/lib/libocrb11.so oracle 1809 oracle mem REG 8,3 1590995 1066411 /u01/app/oracle/product/11.2.0/db_1/lib/libocr11.so oracle 1809 oracle mem REG 8,3 12755 1051572 /u01/app/oracle/product/11.2.0/db_1/lib/libskgxn2.so oracle 1809 oracle mem REG 8,3 17319952 1066379 /u01/app/oracle/product/11.2.0/db_1/lib/libhasgen11.so oracle 1809 oracle mem REG 8,3 161764 1051337 /u01/app/oracle/product/11.2.0/db_1/lib/libdbcfg11.so oracle 1809 oracle mem REG 8,3 228765 1066381 /u01/app/oracle/product/11.2.0/db_1/lib/libclsra11.so oracle 1809 oracle mem REG 8,3 7955322 1066638 /u01/app/oracle/product/11.2.0/db_1/lib/libnnz11.so oracle 1809 oracle mem REG 8,3 1010297 1049728 /u01/app/oracle/product/11.2.0/db_1/lib/libskgxp11.so oracle 1809 oracle mem REG 8,3 589359 1066818 /u01/app/oracle/product/11.2.0/db_1/lib/libcell11.so oracle 1809 oracle mem REG 8,3 12259 1048574 /u01/app/oracle/product/11.2.0/db_1/lib/libodmd11.so oracle 1809 oracle 0r CHR 1,3 0t0 3737 /dev/null oracle 1809 oracle 1w CHR 1,3 0t0 3737 /dev/null oracle 1809 oracle 2w CHR 1,3 0t0 3737 /dev/null oracle 1809 oracle 3r CHR 1,3 0t0 3737 /dev/null oracle 1809 oracle 4r CHR 1,3 0t0 3737 /dev/null oracle 1809 oracle 5u REG 8,3 1544 1050855 /u01/app/oracle/product/11.2.0/db_1/dbs/hc_ora11gr2.dat oracle 1809 oracle 6r CHR 1,3 0t0 3737 /dev/null oracle 1809 oracle 7r CHR 1,3 0t0 3737 /dev/null oracle 1809 oracle 8r CHR 1,3 0t0 3737 /dev/null oracle 1809 oracle 9r CHR 1,3 0t0 3737 /dev/null oracle 1809 oracle 10r CHR 1,5 0t0 3739 /dev/zero oracle 1809 oracle 11r CHR 1,5 0t0 3739 /dev/zero oracle 1809 oracle 12u REG 8,3 1544 1050855 /u01/app/oracle/product/11.2.0/db_1/dbs/hc_ora11gr2.dat oracle 1809 oracle 13r REG 8,3 1101312 1584371 /u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb oracle 1809 oracle 14r DIR 0,3 0 14854 /proc/1809/fd oracle 1809 oracle 15r CHR 1,5 0t0 3739 /dev/zero oracle 1809 oracle 16u REG 8,3 1544 1050855 /u01/app/oracle/product/11.2.0/db_1/dbs/hc_ora11gr2.dat oracle 1809 oracle 17uR REG 8,3 24 1049647 /u01/app/oracle/product/11.2.0/db_1/dbs/lkORA11GR2 oracle 1809 oracle 18r REG 8,3 1101312 1584371 /u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb oracle 1809 oracle 256u REG 8,3 9748480 1177382 /u01/app/oracle/oradata/ora11gr2/control01.ctl oracle 1809 oracle 257u REG 8,3 9748480 1177383 /u01/app/oracle/fast_recovery_area/ora11gr2/control02.ctl oracle 1809 oracle 258uW REG 8,3 734011392 1177387 /u01/app/oracle/oradata/ora11gr2/system01.dbf oracle 1809 oracle 259uW REG 8,3 629153792 1177388 /u01/app/oracle/oradata/ora11gr2/sysaux01.dbf oracle 1809 oracle 260uW REG 8,3 325066752 1177389 /u01/app/oracle/oradata/ora11gr2/undotbs01.dbf oracle 1809 oracle 261uW REG 8,3 5251072 1177393 /u01/app/oracle/oradata/ora11gr2/users01.dbf oracle 1809 oracle 262uW REG 8,3 62922752 1177390 /u01/app/oracle/oradata/ora11gr2/temp01.dbf oracle 1809 oracle 263uW REG 8,3 10493952 1177433 /u01/testdir/yallonking01.dbf (deleted) <<此处为删除文件的信息
此处查到的是263，且已经被标记为删除。
将已删除的文件（在OS级别被标记为删除，但是在OS上并未真正删除，可通过文件描述符找到该文件）拷贝到新目录下进行恢复
[oracle@rhl6 ~]$ ps -ef | grep dbw | grep -v grep oracle 1809 1 0 14:38 ? 00:00:00 ora_dbw0_ora11gr2 [oracle@rhl6 ~]$ ls -l /proc/1809/fd total 0 lr-x------. 1 oracle oinstall 64 Jul 16 14:57 0 -> /dev/null l-wx------. 1 oracle oinstall 64 Jul 16 14:57 1 -> /dev/null lr-x------. 1 oracle oinstall 64 Jul 16 14:57 10 -> /dev/zero lr-x------. 1 oracle oinstall 64 Jul 16 14:57 11 -> /dev/zero lrwx------. 1 oracle oinstall 64 Jul 16 14:57 12 -> /u01/app/oracle/product/11.2.0/db_1/dbs/hc_ora11gr2.dat lr-x------. 1 oracle oinstall 64 Jul 16 14:57 13 -> /u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb lr-x------. 1 oracle oinstall 64 Jul 16 14:57 14 -> /proc/1809/fd lr-x------. 1 oracle oinstall 64 Jul 16 14:57 15 -> /dev/zero lrwx------. 1 oracle oinstall 64 Jul 16 14:57 16 -> /u01/app/oracle/product/11.2.0/db_1/dbs/hc_ora11gr2.dat lrwx------. 1 oracle oinstall 64 Jul 16 14:57 17 -> /u01/app/oracle/product/11.2.0/db_1/dbs/lkORA11GR2 lr-x------. 1 oracle oinstall 64 Jul 16 14:57 18 -> /u01/app/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb l-wx------. 1 oracle oinstall 64 Jul 16 14:57 2 -> /dev/null lrwx------. 1 oracle oinstall 64 Jul 16 14:57 256 -> /u01/app/oracle/oradata/ora11gr2/control01.ctl lrwx------. 1 oracle oinstall 64 Jul 16 14:57 257 -> /u01/app/oracle/fast_recovery_area/ora11gr2/control02.ctl lrwx------. 1 oracle oinstall 64 Jul 16 14:57 258 -> /u01/app/oracle/oradata/ora11gr2/system01.dbf lrwx------. 1 oracle oinstall 64 Jul 16 14:57 259 -> /u01/app/oracle/oradata/ora11gr2/sysaux01.dbf lrwx------. 1 oracle oinstall 64 Jul 16 14:57 260 -> /u01/app/oracle/oradata/ora11gr2/undotbs01.dbf lrwx------. 1 oracle oinstall 64 Jul 16 14:57 261 -> /u01/app/oracle/oradata/ora11gr2/users01.dbf lrwx------. 1 oracle oinstall 64 Jul 16 14:57 262 -> /u01/app/oracle/oradata/ora11gr2/temp01.dbf lrwx------. 1 oracle oinstall 64 Jul 16 14:57 263 -> /u01/testdir/yallonking01.dbf (deleted) <<该行会闪动 lr-x------. 1 oracle oinstall 64 Jul 16 14:57 3 -> /dev/null lr-x------. 1 oracle oinstall 64 Jul 16 14:57 4 -> /dev/null lrwx------. 1 oracle oinstall 64 Jul 16 14:57 5 -> /u01/app/oracle/product/11.2.0/db_1/dbs/hc_ora11gr2.dat lr-x------. 1 oracle oinstall 64 Jul 16 14:57 6 -> /dev/null lr-x------. 1 oracle oinstall 64 Jul 16 14:57 7 -> /dev/null lr-x------. 1 oracle oinstall 64 Jul 16 14:57 8 -> /dev/null lr-x------. 1 oracle oinstall 64 Jul 16 14:57 9 -> /dev/null
注意：该处也进一步说明删除的文件号是263.
根据文件号或者上边的输出将文件拷贝至新的位置
[oracle@rhl6 ~]$ cp /proc/1809/fd/263 /u01/app/oracle/oradata/ora11gr2/yallonking01.dbf SQL> conn /as sysdba Connected. SQL> alter database datafile 5 offline; alter database datafile 5 offline * ERROR at line 1: ORA-01145: offline immediate disallowed unless media recovery enabled SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 66 Current log sequence 68 SQL> alter database datafile 5 offline drop; Database altered.
注意：由于是在非归档模式下，故单一的将数据文件offline会报错。
SQL> alter database rename file '/u01/testdir/yallonking01.dbf' to '/u01/app/oracle/oradata/ora11gr2/yallonking01.dbf'; Database altered. SQL> recover datafile 5; Media recovery complete. SQL> alter database datafile 5 online; Database altered.
恢复后验证
SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
Session altered.
SQL> select * from yallonking.yallonking;
ID NAME MY_DATE
---------- -------------------------------------------------- -------------------
1 yallonking 2013/07/16 14:47:28
SQL> alter system flush BUFFER_CACHE;
System altered.
SQL> select * from yallonking.yallonking;
ID NAME MY_DATE
---------- -------------------------------------------------- -------------------
1 yallonking 2013/07/16 14:47:28
操作日志如下
Tue Jul 16 15:07:22 2013 alter database datafile 5 offline ORA-1145 signalled during: alter database datafile 5 offline... Tue Jul 16 15:07:59 2013 alter database datafile 5 offline drop Completed: alter database datafile 5 offline drop Tue Jul 16 15:08:27 2013 Errors in file /u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_m001_2249.trc: ORA-01135: file 5 accessed for DML/query is offline ORA-01110: data file 5: '/u01/testdir/yallonking01.dbf' Tue Jul 16 15:09:05 2013 alter database rename file '/u01/testdir/yallonking01.dbf' to '/u01/app/oracle/oradata/ora11gr2/yallonking01.dbf' Completed: alter database rename file '/u01/testdir/yallonking01.dbf' to '/u01/app/oracle/oradata/ora11gr2/yallonking01.dbf' Tue Jul 16 15:09:20 2013 ALTER DATABASE RECOVER datafile 5 Media Recovery Start Serial Media Recovery started Recovery of Online Redo Log: Thread 1 Group 2 Seq 68 Reading mem 0 Mem# 0: /u01/app/oracle/oradata/ora11gr2/redo02.log Media Recovery Complete (ora11gr2) Completed: ALTER DATABASE RECOVER datafile 5 Tue Jul 16 15:09:33 2013 alter database datafile 5 online Completed: alter database datafile 5 online Tue Jul 16 15:11:39 2013 ALTER SYSTEM: Flushing buffer cache
至此，恢复OK！
