恢复rm 删除数据文件

意外RM 数据文件的恢复

1.      删除rm数据文件 lerry_01.dbf

[oracle@STCS ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 9 09:56:40 2012

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> select name from v$datafile;

 

NAME

--------------------------------------------------------------------------------

/oradata/ora12/system01.dbf

/oradata/ora12/sysaux01.dbf

/oradata/ora12/undotbs01.dbf

/oradata/ora12/users01.dbf

/oradata/ora12/test01.dbf

/oradata/ora12/lerry01.dbf

/oradata/ora12/rman_tb01.dbf

 

SQL> !rm /oradata/ora12/lerry01.dbf

 

SQL> !ls -l /oradata/ora12/lerry01.dbf

ls: /oradata/ora12/lerry01.dbf: No such file or directory

 

 

2.      在lerry表空间中建表 报错

SQL> create table lerry.a as select * from dba_tables;

create table lerry.a as select * from dba_tables

                                      *

ERROR at line 1:

ORA-01116: error in opening database file 5

ORA-01110: data file 5: '/oradata/ora12/lerry01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

 

DBWR进程会打开所有数据文件的句柄

[oracle@STCS ~]$ ps -ef|grep dbw|grep -v grep

oracle    1347     1  0 Jan07 ?        00:00:01 ora_dbw0_ora12

3.      检查dbwr进程PID

查看该进程所有文件句柄

[oracle@STCS ~]$ ll /proc/1347/fd

total 30

lr-x------  1 oracle dba 64 Jan  9 10:02 0 -> /dev/null

l-wx------  1 oracle dba 64 Jan  9 10:02 1 -> /dev/null

l-wx------  1 oracle dba 64 Jan  9 10:02 10 -> /ora101/diag/rdbms/ora12/ora12/trace/ora12_ora_1288.trc

l-wx------  1 oracle dba 64 Jan  9 10:02 11 -> /ora101/diag/rdbms/ora12/ora12/trace/ora12_ora_1288.trm

lr-x------  1 oracle dba 64 Jan  9 10:02 12 -> /ora101/11.2/rdbms/mesg/oraus.msb

lr-x------  1 oracle dba 64 Jan  9 10:02 13 -> /dev/zero

lr-x------  1 oracle dba 64 Jan  9 10:02 14 -> /proc/1347/fd

lr-x------  1 oracle dba 64 Jan  9 10:02 15 -> /dev/zero

lrwx------  1 oracle dba 64 Jan  9 10:02 16 -> /ora101/11.2/dbs/hc_ora12.dat

lrwx------  1 oracle dba 64 Jan  9 10:02 17 -> /ora101/11.2/dbs/lkORA12

lrwx------  1 oracle dba 64 Jan  9 10:02 18 -> /oradata/ora12/control01.ctl

lrwx------  1 oracle dba 64 Jan  9 10:02 19 -> /ora101/flash_recovery_area/ora12/control02.ctl

l-wx------  1 oracle dba 64 Jan  9 10:02 2 -> /dev/null

lrwx------  1 oracle dba 64 Jan  9 10:02 20 -> /oradata/ora12/system01.dbf

lrwx------  1 oracle dba 64 Jan  9 10:02 21 -> /oradata/ora12/sysaux01.dbf

lrwx------  1 oracle dba 64 Jan  9 10:02 22 -> /oradata/ora12/undotbs01.dbf

lrwx------  1 oracle dba 64 Jan  9 10:02 23 -> /oradata/ora12/users01.dbf

lrwx------  1 oracle dba 64 Jan  9 10:02 24 -> /oradata/ora12/test01.dbf

lrwx------  1 oracle dba 64 Jan  9 10:02 25 -> socket:[398704]

lrwx------  1 oracle dba 64 Jan  9 10:02 26 -> /oradata/ora12/rman_tb01.dbf

lrwx------  1 oracle dba 64 Jan  9 10:02 27 -> /oradata/ora12/temp02.dbf

lr-x------  1 oracle dba 64 Jan  9 10:02 28 -> /ora101/11.2/rdbms/mesg/oraus.msb

l-wx------  1 oracle dba 64 Jan  9 10:02 3 -> /ora101/11.2/rdbms/log/ora12_ora_1288.trc

lrwx------  1 oracle dba 64 Jan  9 10:02 31 -> /oradata/ora12/lerry01.dbf (deleted)

lr-x------  1 oracle dba 64 Jan  9 10:02 4 -> /dev/null

lr-x------  1 oracle dba 64 Jan  9 10:02 5 -> /dev/null

lr-x------  1 oracle dba 64 Jan  9 10:02 6 -> /dev/null

lrwx------  1 oracle dba 64 Jan  9 10:02 7 -> /ora101/11.2/dbs/hc_ora12.dat

lrwx------  1 oracle dba 64 Jan  9 10:02 8 -> /ora101/11.2/dbs/lkinstora12 (deleted)

lr-x------  1 oracle dba 64 Jan  9 10:02 9 -> /proc/1347/fd

 

然后通过句柄恢复数据(被删除的数据文件被标示为deleted)

4.      直接cp该句柄文件回原位置

[oracle@STCS ~]$ cp /proc/1347/fd/31 /oradata/ora12/lerry01.dbf

查看恢复才成功的数据文件

[oracle@STCS ~]$ ll /oradata/ora12/lerry01.dbf

-rw-r-----  1 oracle dba 10493952 Jan  9 10:08 /oradata/ora12/lerry01.dbf

 

5.      进行数据文件恢复:

SQL> alter database datafile 5 offline;

 

Database altered.

 

SQL> recover datafile 5;

Media recovery complete.

SQL> alter database datafile 5 online;

 

Database altered.

再次验证：

SQL> create table lerry.a as select * from dba_tables;

 

Table created.

 

SQL> select table_name,tablespace_name from dba_tables where wner='LERRY';

 

TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

TT                             USERS

A                              LERRY

T1

 

注意：在意外的删除OS下的数据文件，不要忙着重启db和os，可以同过dbwn进程相关句柄找回数据文件 

 

Recover datafile 6;

 

 

但是在测试的时候 还是会出问题

1.      Create tablespace lerry datafile ‘/oradata/ora10/lerry01.dbf’ size 200;

2.      Create table aa tablespace lerry as select * from dba_tables;

3.      $rm /oradata/ora10/lerry01.dbf

4.      Sql>Create table bb tablespace lerry as select * from dba_tables;

   Table created.

5.      Sql>alter system switch logfile;

6.      SQL> select count(*) from bb;

 COUNT(*)

----------

    2125

我想是不是数据还没有写入到datafile 中，还只是在buffer cache中

7.      Sql>alter system flush buffer_cache;

8.      Sql>select count(*) from bb;

Errors in file /orahome/admin/ora10/bdump/ora10_ckpt_12823.trc:

ORA-01110: data file 5: '/oradata/ora10/lerry.dbf'

ORA-01116: error in opening database file 5

ORA-01110: data file 5: '/oradata/ora10/lerry.dbf'

ORA-27041: unable to open file

9.      按照以上方法恢复数据文件

在recover datafile 时出现错误

SQL>  recover datafile 5;

ORA-00283: recovery session canceled due to errors

ORA-01110: data file 5: '/oradata/ora10/lerry.dbf'

ORA-01115: IO error reading block from file 5 (block # 1)

ORA-01110: data file 5: '/oradata/ora10/lerry.dbf'

ORA-27091: unable to queue I/O

ORA-27069: attempt to do I/O beyond the range of the file

Additional information: 1

Additional information: 1

这是什么原因造成的呢？？

 

 

参考：

http://www.dbform.com/html/2011/1761.html

http://www.eygle.com/archives/2011/12/oracle_linux_rm_rf.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24849178/viewspace-732330/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24849178/viewspace-732330/