A-A+

无备份情况下的数据文件恢复一例

2013年07月16日 Backup&Recovery 暂无评论 阅读 1,500 次

下边的例子是数据库始终在启动状态下,通过操作系统文件描述符恢复误删除的数据库的数据文件。
恢复条件:数据库始终处于启动状态。
注意:通过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!

标签:

给我留言

Copyright © YallonKing 保留所有权利.   Theme  Ality

用户登录

分享到: