利用句柄找回误删除的数据文件

误删除数据文件，如果没有备份，在不关库的情况下，是可以恢复的。如：rm /u01/app/oracle/oradata/PROD1/test.dbf

1. 创建测试表空间
   

   点击(此处)折叠或打开

   1. SQL> create tablespace test02 datafile '/u01/app/oracle/oradata/PROD1/test02.dbf' size 10M autoextend on maxsize 2G;
      
   2. 
      
   3. Tablespace created.
   
   
2. 
   在测试表空间上创建测试表
   

   点击(此处)折叠或打开

   1. SQL> create table t tablespace test02 as select * from dba_objects;
      
   2. 
      
   3. Table created.
   
   
3. 
   删除数据文件
   

   点击(此处)折叠或打开

   1. SQL> !rm /u01/app/oracle/oradata/PROD1/test02.dbf
   
   
4. 
   再次建表，验证数据文件已经丢失
   

   点击(此处)折叠或打开

   1. SQL> create table t1 tablespace test02 as select * from dba_objects;
      
   2. create table t1 tablespace test02 as select * from dba_objects
      
   3.                                                    *
      
   4. ERROR at line 1:
      
   5. ORA-01565: error in identifying file '/u01/app/oracle/oradata/PROD1/test02.dbf'
      
   6. ORA-27037: unable to obtain file status
      
   7. Linux-x86_64 Error: 2: No such file or directory
      
   8. Additional information: 3
   
   
5. 
   切记，此时不能关闭数据库
6. 
   查看DBWR进程的pid
   

   点击(此处)折叠或打开

   1. [oracle@host01 PROD1]$ ps -ef|grep dbw|grep -v grep
      
   2. oracle 19784 1 0 21:59 ? 00:00:00 ora_dbw0_PROD1
7. 
   找到句柄
   
   
   

   
   

   点击(此处)折叠或打开

   1. [oracle@host01 PROD1]$ ls -rtl /proc/19784/fd
      
   2. total 0
      
   3. lrwx------ 1 oracle oinstall 64 Apr 5 22:15 9 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/lkPROD1
      
   4. lrwx------ 1 oracle oinstall 64 Apr 5 22:15 8 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_PROD1.dat
      
   5. lr-x------ 1 oracle oinstall 64 Apr 5 22:15 7 -> /dev/zero
      
   6. lr-x------ 1 oracle oinstall 64 Apr 5 22:15 6 -> /proc/19784/fd
      
   7. lr-x------ 1 oracle oinstall 64 Apr 5 22:15 5 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
      
   8. lr-x------ 1 oracle oinstall 64 Apr 5 22:15 4 -> /dev/null
      
   9. lr-x------ 1 oracle oinstall 64 Apr 5 22:15 3 -> /dev/null
      
   10. lrwx------ 1 oracle oinstall 64 Apr 5 22:15 265 -> /u01/app/oracle/oradata/PROD1/test02.dbf (deleted)
8. 
   将找到的句柄拷贝回来
   

   点击(此处)折叠或打开

   1. [oracle@host01 PROD1]$ cp /proc//19784/fd/265 /u01/app/oracle/oradata/PROD1/test02.dbf
   
   
9. 
   查看数据文件的状态
   

   点击(此处)折叠或打开

   1. SQL> select file#,status,name from v$datafile;
      
   2. 
      
   3.      FILE# STATUS NAME
      
   4. ---------- ------- --------------------------------------------------
      
   5.          1 SYSTEM /u01/app/oracle/oradata/PROD1/system01.dbf
      
   6.          2 ONLINE /u01/app/oracle/oradata/PROD1/sysaux01.dbf
      
   7.          3 ONLINE /u01/app/oracle/oradata/PROD1/undotbs01.dbf
      
   8.          4 ONLINE /u01/app/oracle/oradata/PROD1/users01.dbf
      
   9.          5 ONLINE /u01/app/oracle/oradata/PROD1/example01.dbf
      
   10.          6 ONLINE /u01/app/oracle/oradata/PROD1/test01.dbf
       
   11.          7 ONLINE /u01/app/oracle/oradata/PROD1/test02.dbf
       
   12. 
       
   13. 7 rows selected.
   
   
10. 
    将数据文件offline
    

    点击(此处)折叠或打开

    1. SQL> alter database datafile 6 offline;
       
    2. 
       
    3. Database altered.
    4. SQL> select file#,status,name from v$datafile;
       
            FILE# STATUS  NAME
       ---------- ------- --------------------------------------------------
                1 SYSTEM  /u01/app/oracle/oradata/PROD1/system01.dbf
                2 ONLINE  /u01/app/oracle/oradata/PROD1/sysaux01.dbf
                3 ONLINE  /u01/app/oracle/oradata/PROD1/undotbs01.dbf
                4 ONLINE  /u01/app/oracle/oradata/PROD1/users01.dbf
                5 ONLINE  /u01/app/oracle/oradata/PROD1/example01.dbf
                6 RECOVER /u01/app/oracle/oradata/PROD1/test01.dbf
                7 ONLINE  /u01/app/oracle/oradata/PROD1/test02.dbf
       
       
       
    
    
11. 
    恢复数据文件
    

    点击(此处)折叠或打开

    1. SQL> recover datafile 6;
       
    2. Media recovery complete.
    
    
12. 
    将数据文件online
    

    点击(此处)折叠或打开

    1. SQL> alter database datafile 6 online;
       
    2. 
       
    3. Database altered.
    
    
13. 
    验证
    

    点击(此处)折叠或打开

    1. SQL> select count(*) from t;
       
    2. 
       
    3.   COUNT(*)
       
    4. ----------
       
    5.      86955
    
    

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

转载于:http://blog.itpub.net/31405405/viewspace-2136673/