在沒有備份的情況下，執行中的資料庫突然丟失了或者誤刪除一個非關鍵資料檔案，
這時候，記得不要馬上關閉資料庫，這個問題可以從系統層上來恢復丟失了的資料檔案。

系統層恢復資料庫執行下的資料檔案過程如下：

1、建立測試表：
SQL> create table t2 tablespace TS_ORA11GR2 as select 1 id from dual;
Table created.

2、刪除表空間TS_ORA11GR2的資料檔案：
[oracle@enmo OCMU]$ rm ts_ora11gr2_01.dbf
[oracle@enmo OCMU]$ ls
control01.ctl example01.dbf redo02.log redo05.log system01.dbf ts_audit01.dbf users01.dbf
control03.ctl MYTEST02.dbf redo03.log redo06.log temp01.dbf tscatalog.dbf
create redo01.log redo04.log sysaux.dbf test01.dbf undotbs01.dbf

3、嘗試建立新表到丟失的資料檔案上：
SQL> create table t3 tablespace TS_ORA11GR2 as select 1 id from dual;
create table t3 tablespace ts_test as select 1 id from dual
*
ERROR at line 1:
ORA-00959: tablespace 'TS_ORA11GR2' does not exist

4、作業系統層檢視控制程式碼：
[oracle@enmo ~]$ ps -ef|grep dbw|grep -v grep
oracle 4610 1 0 16:43 ? 00:00:01 ora_dbw0_OCMU
[oracle@enmo ~]$

total 0
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 9 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_OCMU.dat
lr-x------ 1 oracle oinstall 64 Oct 12 23:33 8 -> /dev/zero
lr-x------ 1 oracle oinstall 64 Oct 12 23:33 7 -> /proc/4610/fd
lr-x------ 1 oracle oinstall 64 Oct 12 23:33 6 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 Oct 12 23:33 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 Oct 12 23:33 4 -> /dev/null
lr-x------ 1 oracle oinstall 64 Oct 12 23:33 3 -> /dev/null
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 271 -> /u01/app/oracle/oradata/mytemp.dbf
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 270 -> /u01/app/oracle/oradata/OCMU/temp01.dbf
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 269 -> /u01/app/oracle/oradata/OCMU/tscatalog.dbf
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 268 -> /u01/app/oracle/oradata/OCMU/ts_audit01.dbf
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 267 -> /u01/app/oracle/oradata/OCMU/MYTEST02.dbf
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 266 -> /u01/app/oracle/oradata/OCMU/test01.dbf
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 265 -> /u01/app/oracle/oradata/OCMU/ts_ora11gr2_01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 264 -> /u01/app/oracle/oradata/OCMU/example01.dbf
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 263 -> /u01/app/oracle/oradata/OCMU/users01.dbf
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 262 -> /u01/app/oracle/oradata/OCMU/sysaux.dbf
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 261 -> /u01/app/oracle/oradata/OCMU/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 260 -> /u01/app/oracle/oradata/OCMU/system01.dbf
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 259 -> /u01/app/FRA/control04.ctl
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 258 -> /u01/app/oracle/oradata/OCMU/control03.ctl
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 257 -> /u01/app/oracle/fast_recovery_area/control02.ctl
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 256 -> /u01/app/oracle/oradata/OCMU/control01.ctl
l-wx------ 1 oracle oinstall 64 Oct 12 23:33 2 -> /dev/null
lr-x------ 1 oracle oinstall 64 Oct 12 23:33 11 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 10 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/lkORA11GR2
l-wx------ 1 oracle oinstall 64 Oct 12 23:33 1 -> /dev/null
lr-x------ 1 oracle oinstall 64 Oct 12 23:33 0 -> /dev/null

5、根據控制程式碼找回檔案：
cp /proc//4610/fd/265 /u01/app/oracle/oradata/OCMU/ts_ora11gr2_01.dbf

6、檢視檔案位置：
SQL> select file#,status,name from v$datafile;

FILE# STATUS
---------- -------
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
1 SYSTEM
/u01/app/oracle/oradata/OCMU/system01.dbf

2 ONLINE
/u01/app/oracle/oradata/OCMU/undotbs01.dbf

3 ONLINE
/u01/app/oracle/oradata/OCMU/sysaux.dbf

FILE# STATUS
---------- -------
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
4 ONLINE
/u01/app/oracle/oradata/OCMU/users01.dbf

5 ONLINE
/u01/app/oracle/oradata/OCMU/example01.dbf

6 ONLINE
/u01/app/oracle/oradata/OCMU/ts_ora11gr2_01.dbf

7、修改檔案狀態：
SQL> alter database datafile 6 offline;
Database altered.

8、再次檢視：
SQL> select file#,status,name from v$datafile;
FILE# STATUS
---------- -------
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
1 SYSTEM
/u01/app/oracle/oradata/OCMU/system01.dbf

2 ONLINE
/u01/app/oracle/oradata/OCMU/undotbs01.dbf

3 ONLINE
/u01/app/oracle/oradata/OCMU/sysaux.dbf

FILE# STATUS
---------- -------
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
4 ONLINE
/u01/app/oracle/oradata/OCMU/users01.dbf

5 ONLINE
/u01/app/oracle/oradata/OCMU/example01.dbf

6 RECOVER
/u01/app/oracle/oradata/OCMU/ts_ora11gr2_01.dbf

9、恢復檔案：
SQL> recover datafile 6;
Media recovery complete.

10、設定資料檔案online：
SQL> alter database datafile 6 online;

Database altered.

恢復完成。

作業系統層恢復非關鍵資料檔案

相關文章