使用檔案控制代碼方式恢複誤刪除的Oracle資料檔案____Oracle

環境說明：
作業系統：Linux
資料庫版本：11.2.0.4

begin~ 建立資料表空間test_tb

SQL> create tablespace test_tb datafile '/u01/app/oracle/oradata/PROD/test.dbf' size 50M;Tablespace created.

建立使用者test

SQL> create user test identified by oracle default tablespace test_tb;User created.

給使用者test授權

SQL> grant connect,resource,dba to test;Grant succeeded.

建立表t1

SQL> conn test/oracleConnected.SQL> create table t1 as select * from dba_objects;Table created.

SQL> select count(*) from t1;  COUNT(*)----------     82347

查看資料庫歸檔模式

SQL> archive log listDatabase log mode          No Archive ModeAutomatic archival         DisabledArchive destination        USE_DB_RECOVERY_FILE_DESTOldest online log sequence     65Current log sequence           67

將資料庫啟動到mount狀態

SQL> shut immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area 1252663296 bytesFixed Size          2252824 bytesVariable Size         788533224 bytesDatabase Buffers      452984832 bytesRedo Buffers            8892416 bytesDatabase mounted.

開啟歸檔

SQL> alter database archivelog;Database altered.

開啟資料庫

SQL> alter database open;Database altered.

驗證是否開啟歸檔

SQL> archive log listDatabase log mode          Archive ModeAutomatic archival         EnabledArchive destination        USE_DB_RECOVERY_FILE_DESTOldest online log sequence     65Next log sequence to archive   67Current log sequence           67

類比誤刪除資料檔案test.dbf

[oracle@vastdata1 ~]$ rm -f /u01/app/oracle/oradata/PROD/test.dbf

再次向t1表插入資料失敗

SQL> insert into t1 select * from dba_objects;insert into t1 select * from dba_objects            *ERROR at line 1:ORA-01116: error in opening database file 5ORA-01110: data file 5: '/u01/app/oracle/oradata/PROD/test.dbf'ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3

此時資料庫沒有關閉，使用控制代碼恢複即可

[oracle@vastdata1 ~]$ ps -ef |grep -v grep |grep dbw0oracle    37300      1  0 17:45 ?        00:00:00 ora_dbw0_PROD[oracle@vastdata1 ~]$ cd /proc/37300/fd[oracle@vastdata1 fd]$ lltotal 0lr-x------ 1 oracle oinstall 64 May 26 17:53 0 -> /dev/nulll-wx------ 1 oracle oinstall 64 May 26 17:53 1 -> /dev/nulllrwx------ 1 oracle oinstall 64 May 26 17:53 10 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/lkPRODlr-x------ 1 oracle oinstall 64 May 26 17:53 11 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msbl-wx------ 1 oracle oinstall 64 May 26 17:53 2 -> /dev/nulllrwx------ 1 oracle oinstall 64 May 26 17:53 256 -> /u01/app/oracle/oradata/PROD/control01.ctllrwx------ 1 oracle oinstall 64 May 26 17:53 257 -> /u01/app/oracle/fast_recovery_area/PROD/control02.ctllrwx------ 1 oracle oinstall 64 May 26 17:53 258 -> /u01/app/oracle/oradata/PROD/system01.dbflrwx------ 1 oracle oinstall 64 May 26 17:53 259 -> /u01/app/oracle/oradata/PROD/sysaux01.dbflrwx------ 1 oracle oinstall 64 May 26 17:53 260 -> /u01/app/oracle/oradata/PROD/undotbs01.dbflrwx------ 1 oracle oinstall 64 May 26 17:53 261 -> /u01/app/oracle/oradata/PROD/users01.dbflrwx------ 1 oracle oinstall 64 May 26 17:53 262 -> /u01/app/oracle/oradata/PROD/test.dbf (deleted)lrwx------ 1 oracle oinstall 64 May 26 17:53 263 -> /u01/app/oracle/oradata/PROD/temp01.dbflr-x------ 1 oracle oinstall 64 May 26 17:53 3 -> /dev/nulllr-x------ 1 oracle oinstall 64 May 26 17:53 4 -> /dev/nulllr-x------ 1 oracle oinstall 64 May 26 17:53 5 -> /dev/nulllr-x------ 1 oracle oinstall 64 May 26 17:53 6 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msblr-x------ 1 oracle oinstall 64 May 26 17:53 7 -> /proc/37300/fdlr-x------ 1 oracle oinstall 64 May 26 17:53 8 -> /dev/zerolrwx------ 1 oracle oinstall 64 May 26 17:53 9 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_PROD.dat

使用cp命令將刪除的檔案恢複到原來額位置

[oracle@vastdata1 fd]$ cp 262 /u01/app/oracle/oradata/PROD/test.dbf

資料庫一直是open的，SCN不斷的變化，將test.dbf檔案offline

SQL> alter database datafile '/u01/app/oracle/oradata/PROD/test.dbf' offline;Database altered.

使用cp恢複出來的資料檔案和資料庫當前的資訊不一致，需要recover

SQL> recover datafile '/u01/app/oracle/oradata/PROD/test.dbf' Media recovery complete.

在recover完成後將test.dbf檔案online

SQL> alter database datafile '/u01/app/oracle/oradata/PROD/test.dbf' online;Database altered.

重啟資料庫

SQL> conn / as sysdbaConnected.SQL> shut immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 1252663296 bytesFixed Size          2252824 bytesVariable Size         788533224 bytesDatabase Buffers      452984832 bytesRedo Buffers            8892416 bytesDatabase mounted.Database opened.

測試

SQL> conn test/oracleConnected.SQL> select count(*) from t1;  COUNT(*)----------     82347

注意：資料庫是在歸檔模式下，資料庫、作業系統沒有重啟，如果關閉資料庫或作業系統這種方式是不適合的。

end~