Oracle下rm 刪除數(shù)據(jù)文件恢復(fù)
下面是整個測試步驟
1.在數(shù)據(jù)庫open的時候，直接刪除test表空間中的數(shù)據(jù)文件Last login: Tue Feb 7 10:24:27 2017 from 192.168.36.103[root@oracle1 ~]# su - oracleocrl:/home/oracle@oracle1>ps -ef |grep pmonoracle 6098 1 0 Feb07 ? 00:00:05 ora_pmon_ocrloracle 18634 18603 0 11:34 pts/1 00:00:00 grep pmonocrl:/home/oracle@oracle1>sqlplus / as sysdbaSQL*Plus: Release 12.1.0.2.0 PRoduction on Fri Feb 10 11:36:15 2017Copyright (c) 1982, 2014, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real application Testing optionsSQL> select name from v$datafile;NAME--------------------------------------------------------------------------------/data/oradata/ocrl/datafile/system01.dbf/data/oradata/ocrl/datafile/sysaux01.dbf/data/oradata/ocrl/datafile/undotbs01.dbf/data/oradata/ocrl/datafile/users01.dbf/data/oradata/ocrl/datafile/test.dbfSQL> exitDisconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsocrl:/home/oracle@oracle1>cd /data/oradata/ocrl/datafile/ocrl:/data/oradata/ocrl/datafile@oracle1>lssysaux01.dbf system01.dbf test.dbf undotbs01.dbf users01.dbfocrl:/data/oradata/ocrl/datafile@oracle1>rm -rf test.dbfocrl:/data/oradata/ocrl/datafile@oracle1>lssysaux01.dbf system01.dbf undotbs01.dbf users01.dbfocrl:/data/oradata/ocrl/datafile@oracle1>sqlplus / as sysdbaSQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 10 11:37:23 2017Copyright (c) 1982, 2014, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing options2.嘗試在tets表中間中創(chuàng)建表，開始報錯SQL> create table t tablespace test as select * from dual;create table t tablespace test as select * from dual *ERROR at line 1:ORA-01116: error in opening database file 5ORA-01110: data file 5: '/data/oradata/ocrl/datafile/test.dbf'ORA-27041: unable to open filelinux-x86_64 Error: 2: No such file or directoryAdditional information: 3SQL> exit3.檢查dbwr的進程PIDocrl:/data/oradata/ocrl/datafile@oracle1>ps -ef|grep dbw0oracle 6121 1 0 Feb07 ? 00:00:32 ora_dbw0_ocrloracle 19015 18603 0 11:52 pts/1 00:00:00 grep dbw04.dbwr會打開所有數(shù)據(jù)文件的句柄，在proc目錄中可以查到，目錄是進程PID，fd表示文件描述符ocrl:/data/oradata/ocrl/datafile@oracle1>cd /proc/6121/fdocrl:/proc/6121/fd@oracle1>ls -ltotal 0lr-x------ 1 oracle dba 64 Feb 7 10:11 0 -> /dev/nulll-wx------ 1 oracle dba 64 Feb 7 10:11 1 -> /dev/nulll-wx------ 1 oracle dba 64 Feb 7 10:11 2 -> /dev/nulllrwx------ 1 oracle dba 64 Feb 7 10:11 256 -> /data/oradata/ocrl/control/control01.ctllrwx------ 1 oracle dba 64 Feb 7 10:11 257 -> /data/oradata/ocrl/control/control02.ctllrwx------ 1 oracle dba 64 Feb 10 11:42 258 -> /data/oradata/ocrl/datafile/system01.dbflrwx------ 1 oracle dba 64 Feb 10 11:42 259 -> /data/oradata/ocrl/datafile/sysaux01.dbflrwx------ 1 oracle dba 64 Feb 10 11:42 260 -> /data/oradata/ocrl/datafile/undotbs01.dbflrwx------ 1 oracle dba 64 Feb 10 11:42 261 -> /data/oradata/ocrl/datafile/users01.dbflrwx------ 1 oracle dba 64 Feb 10 11:42 262 -> /data/oradata/ocrl/datafile/test.dbf (deleted)lrwx------ 1 oracle dba 64 Feb 10 11:42 263 -> /data/oradata/ocrl/tempfile/temp01.dbflr-x------ 1 oracle dba 64 Feb 7 10:11 3 -> /dev/nulllr-x------ 1 oracle dba 64 Feb 7 10:11 4 -> /u01/app/oracle/product/12.1.0/db_1/rdbms/mesg/oraus.msblr-x------ 1 oracle dba 64 Feb 7 10:11 5 -> /proc/6121/fdlrwx------ 1 oracle dba 64 Feb 7 10:11 6 -> /u01/app/oracle/product/12.1.0/db_1/dbs/hc_ocrl.datl-wx------ 1 oracle dba 64 Feb 7 10:11 7 -> /u01/app/oracle/product/12.1.0/db_1/rdbms/mesg/oraus.msblrwx------ 1 oracle dba 64 Feb 7 10:11 9 -> /u01/app/oracle/product/12.1.0/db_1/dbs/lkOCRL
注意：注意其中“/data/oradata/ocrl/datafile/test.dbf (deleted)”字樣，表示該文件已經(jīng)被刪除5.直接cp該句柄文件名回原位置ocrl:/proc/6121/fd@oracle1>cp 262 /data/oradata/ocrl/datafile/test.dbfocrl:/proc/6121/fd@oracle1>ls0 1 2 256 257 258 259 260 261 262 263 3 4 5 6 7 9ocrl:/proc/6121/fd@oracle1>cd /data/oradata/ocrl/tempfile/ocrl:/data/oradata/ocrl/tempfile@oracle1>cd /data/oradata/ocrl/datafile/ocrl:/data/oradata/ocrl/datafile@oracle1>lssysaux01.dbf system01.dbf test.dbf undotbs01.dbf users01.dbf6.進行數(shù)據(jù)文件recover--在歸檔模式下ocrl:/data/oradata/ocrl/datafile@oracle1>sqlplus / as sysdbaSQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 10 11:44:24 2017Copyright (c) 1982, 2014, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> alter database datafile 5 offline;Database altered.SQL> recover datafile 5;Media recovery complete.SQL> alter database datafile 5 online;Database altered.SQL> select file#,name from v$datafile; FILE#----------NAME-------------------------------------------------------------------------------- 1/data/oradata/ocrl/datafile/system01.dbf 2/data/oradata/ocrl/datafile/sysaux01.dbf 3/data/oradata/ocrl/datafile/undotbs01.dbf FILE#----------NAME-------------------------------------------------------------------------------- 4/data/oradata/ocrl/datafile/users01.dbf 5/data/oradata/ocrl/datafile/test.dbfSQL> create table t tablespace test as select * from dual;Table created.SQL> select * from t;D-XSQL> exit至此完成數(shù)據(jù)文件恢復(fù)在非歸檔模式下,需要shutdown后,進入mount模式,offline,recover,online,open恢復(fù)的原理是，在Linux操作系統(tǒng)中，如果文件從操作系統(tǒng)級別被rm掉，之前打開該文件的進程仍然持有相應(yīng)的文件句柄，所指向的文件仍然可以讀寫，并且該文件的文件描述符可以從/proc目錄中獲得。但是要注意的是，此時如果關(guān)閉數(shù)據(jù)庫，則此句柄會消失，那么除了掃描磁盤進行文件恢復(fù)之外就沒有其它方法了，因此在數(shù)據(jù)庫出現(xiàn)問題的時候，如果不確認(rèn)情況的復(fù)雜程度，千萬不要隨便關(guān)閉數(shù)據(jù)庫。重啟數(shù)據(jù)庫往往是沒有意義的，甚至是致命的新聞熱點
疑難解答
