wjhhjl

0 积分	0 好友	2 主题

发消息

[物理/逻辑坏块] Linux 操作系统下利用文件句柄恢复误删除的数据文件

1^#

发表于 2014-12-1 01:08:41 | 查看: 4013| 回复: 2

在操作oracle数据库时，误删除了数据文件，在没有有效备份的情况下，按正常的恢复手段往往无法恢复数据文件，
这时我们我们可以在linux操作系统层面，利用文件句柄的方式来恢复数据文件：
环境：OEL6.4 X64+Oracle 11.2.0.3 64bit
example:
1.以oracle用户Y 为例：

SQL> conn Y/oracle
Connected.
SQL> show user
USER is "Y"
SQL> select username,default_tablespace from dba_users where username='Y';

USERNAME                      DEFAULT_TABLESPACE
------------------------------ ------------------------------
Y                            TEST

SQL>  col file_name for a40;
SQL> select file_name,tablespace_name from dba_data_files where tablespace_name='TEST';

FILE_NAME                               TABLESPACE_NAME
---------------------------------------- ------------------------------
/s01/orabase/oradata/ORCL/test01.dbf    TEST

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
T

SQL> select count(*) from T;

  COUNT(*)
----------
   74519

SQL>

3.模拟误删除"test01.dbf"数据文件：
[root@dg2 ORCL]# ls
control01.ctl  redo02.log  redo04.log  redo06.log  sysaux01.dbf  temp01.dbf  undotbs01.dbf
redo01.log    redo03.log  redo05.log  redo07.log  system01.dbf  test01.dbf  users01.dbf
[root@dg2 ORCL]# rm -f test01.dbf
[root@dg2 ORCL]# ls
control01.ctl  redo02.log  redo04.log  redo06.log  sysaux01.dbf  temp01.dbf    users01.dbf
redo01.log    redo03.log  redo05.log  redo07.log  system01.dbf  undotbs01.dbf
[root@dg2 ORCL]#

SQL> create table X(id int);
Table created.
SQL> insert into X values(1);
insert into X values(1)
         *
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/s01/orabase/oradata/ORCL/test01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

4.利用文件句柄来恢复数据文件：oracle的后台进程DBWN负责数据文件的读写操作，
（1）查看dbwn进程：
   [oracle@dg2 ~]$ ps -ef|grep ora_dbw
   [oracle@dg2 ORCL]$ ps -ef|grep ora_dbw
   oracle 2867    1  0 11:24 ?       00:00:00 ora_dbw0_ORCLDG
   oracle 3264  3006  0 12:00 pts/0 00:00:00 grep ora_dbw
[oracle@dg2 ~]$
（2）查找ora_dbw0_ORCLDG进程2867对应的文件句柄：
   [oracle@dg2 /]$ cd /proc/2867/fd
   [oracle@dg2 fd]$ ls -ltr
total 0
lr-x------ 1 oracle oinstall 64 Nov 30 11:30 9 -> /dev/null
lr-x------ 1 oracle oinstall 64 Nov 30 11:30 8 -> /dev/null
lr-x------ 1 oracle oinstall 64 Nov 30 11:30 7 -> /dev/null
lr-x------ 1 oracle oinstall 64 Nov 30 11:30 6 -> /dev/null
lrwx------ 1 oracle oinstall 64 Nov 30 11:30 5 -> /s01/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/hc_ORCLDG.dat
lr-x------ 1 oracle oinstall 64 Nov 30 11:30 4 -> /dev/null
lr-x------ 1 oracle oinstall 64 Nov 30 11:30 3 -> /dev/null
lrwx------ 1 oracle oinstall 64 Nov 30 11:30 257 -> /s01/orabase/fast_recovery_area/ORCL/control02.ctl
lrwx------ 1 oracle oinstall 64 Nov 30 11:30 256 -> /s01/orabase/oradata/ORCL/control01.ctl
l-wx------ 1 oracle oinstall 64 Nov 30 11:30 2 -> /dev/null
l-wx------ 1 oracle oinstall 64 Nov 30 11:30 19 -> socket:[27781]
l-wx------ 1 oracle oinstall 64 Nov 30 11:30 18 -> /s01/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 Nov 30 11:30 17 -> /s01/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/lkORCLDG
lrwx------ 1 oracle oinstall 64 Nov 30 11:30 16 -> /s01/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/hc_ORCLDG.dat
lr-x------ 1 oracle oinstall 64 Nov 30 11:30 15 -> /dev/zero
lr-x------ 1 oracle oinstall 64 Nov 30 11:30 14 -> /proc/2867/fd
lr-x------ 1 oracle oinstall 64 Nov 30 11:30 13 -> /s01/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 Nov 30 11:30 12 -> /s01/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/hc_ORCLDG.dat
lr-x------ 1 oracle oinstall 64 Nov 30 11:30 11 -> /dev/zero
lr-x------ 1 oracle oinstall 64 Nov 30 11:30 10 -> /dev/zero
l-wx------ 1 oracle oinstall 64 Nov 30 11:30 1 -> /dev/null
lr-x------ 1 oracle oinstall 64 Nov 30 11:30 0 -> /dev/null
lrwx------ 1 oracle oinstall 64 Nov 30 12:00 263 -> /s01/orabase/oradata/ORCL/temp01.dbf
lrwx------ 1 oracle oinstall 64 Nov 30 12:00 262 -> /s01/orabase/oradata/ORCL/test01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Nov 30 12:00 261 -> /s01/orabase/oradata/ORCL/users01.dbf
lrwx------ 1 oracle oinstall 64 Nov 30 12:00 260 -> /s01/orabase/oradata/ORCL/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 Nov 30 12:00 259 -> /s01/orabase/oradata/ORCL/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 Nov 30 12:00 258 -> /s01/orabase/oradata/ORCL/system01.dbf
此时看到lrwx------ 1 oracle oinstall 64 Nov 30 12:00 262 -> /s01/orabase/oradata/ORCL/test01.dbf (deleted)一行，就为test01.dbf对应文件句柄，
这时候把262文件重新复制到/s01/orabase/oradata/ORCL/目录下，
[oracle@dg2 fd]$ cp 262 /s01/orabase/oradata/ORCL/test01.dbf
[oracle@dg2 fd]$ ls -ltr /s01/orabase/oradata/ORCL/test01.dbf
-rw-r----- 1 oracle oinstall 104865792 Nov 30 12:03 /s01/orabase/oradata/ORCL/test01.dbf
可以看到test01.dbf文件又回来了，并且数据库正常：
SQL> select count(*) from T;

  COUNT(*)
----------
   74519

SQL> desc Y;
ERROR:
ORA-04043: object Y does not exist

SQL> desc X
Name                                     Null? Type
----------------------------------------- -------- ----------------------------
ID                                              NUMBER(38)

SQL> insert into X values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from X;

  COUNT(*)
----------
      1

SQL>

分享0