【備份恢復】無備份線上恢復非關鍵資料檔案

當誤刪除表空間資料檔案（特指非關鍵資料檔案，而不是關鍵資料檔案），則可以在不備份就可以修復資料檔案，但有個前提，即不能關庫，假如已經關庫了，則沒有備份，只能offline再刪除表空間；

 

實驗如下：

1：建立測試表空間

SYS@ORA11GR2>create tablespace ts_test datafile '/u01/app/oracle/oradata/ORA11GR2/test.dbf' size 20m autoextend on;

 

Tablespace created

 

2：在測試表空間上建立測試表

SYS@ORA11GR2>create table t1 tablespace ts_test as select 1 id from dual;

 

Table created.

 

3：刪除資料檔案

[oracle@wang ~]$ cd /u01/app/oracle/oradata/ORA11GR2/

[oracle@wang ORA11GR2]$ ls

control01.ctl  redo01.log  sysaux01.dbf  test.dbf

control02.ctl  redo02.log  system01.dbf  undotbs01.dbf

example01.dbf  redo03.log  temp01.dbf    users01.dbf

[oracle@wang ORA11GR2]$

[oracle@wang ORA11GR2]$ ls test.dbf

test.dbf

[oracle@wang ORA11GR2]$ rm test.dbf

[oracle@wang ORA11GR2]$

 

4：再建立表，驗證，資料檔案已丟失

SYS@ORA11GR2>create table t2 tablespace ts_test as select 1 id from dual;

 

Table created.

 

SYS@ORA11GR2>exit

 

[oracle@wang ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 12 23:26:22 2016

 

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

 

 

Connected to:

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

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

 

SYS@ORA11GR2>drop table t2 purge;

drop table t2 purge

           *

ERROR at line 1:

ORA-01116: error in opening database file 6

ORA-01110: data file 6: '/u01/app/oracle/oradata/ORA11GR2/test.dbf'

ORA-27041: unable to open file

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

Additional information: 3

 

5：切記，此時，不能關閉資料庫

 

6：檢視DBWR程式的pid

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

oracle     899     1  0 10:59 ?        00:00:11 ora_dbw0_ORA11GR2

oracle   29549     1  0 Oct11 ?        00:00:03 ora_dbw0_SF

 

7：找到控制程式碼 請參閱此連結http://www.cnblogs.com/cute/archive/2011/04/20/2022280.html

[oracle@wang ~]$ ll -rt /proc/899/fd

total 0

lr-x------ 1 oracle oinstall 64 Oct 12 23:29 6 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb

lr-x------ 1 oracle oinstall 64 Oct 12 23:29 5 -> /dev/null

lr-x------ 1 oracle oinstall 64 Oct 12 23:29 4 -> /dev/null

lr-x------ 1 oracle oinstall 64 Oct 12 23:29 3 -> /dev/null

l-wx------ 1 oracle oinstall 64 Oct 12 23:29 2 -> /dev/null

l-wx------ 1 oracle oinstall 64 Oct 12 23:29 1 -> /dev/null

lr-x------ 1 oracle oinstall 64 Oct 12 23:29 0 -> /dev/null

lrwx------ 1 oracle oinstall 64 Oct 12 23:29 9 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_ORA11GR2.dat

lr-x------ 1 oracle oinstall 64 Oct 12 23:29 8 -> /dev/zero

lr-x------ 1 oracle oinstall 64 Oct 12 23:29 7 -> /proc/899/fd

lrwx------ 1 oracle oinstall 64 Oct 12 23:29 264 -> /u01/app/oracle/oradata/ORA11GR2/test.dbf (deleted)

lrwx------ 1 oracle oinstall 64 Oct 12 23:29 263 -> /u01/app/oracle/oradata/ORA11GR2/temp01.dbf

lrwx------ 1 oracle oinstall 64 Oct 12 23:29 262 -> /u01/app/oracle/oradata/ORA11GR2/example01.dbf

lrwx------ 1 oracle oinstall 64 Oct 12 23:29 261 -> /u01/app/oracle/oradata/ORA11GR2/users01.dbf

lrwx------ 1 oracle oinstall 64 Oct 12 23:29 260 -> /u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

lrwx------ 1 oracle oinstall 64 Oct 12 23:29 259 -> /u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

lrwx------ 1 oracle oinstall 64 Oct 12 23:29 258 -> /u01/app/oracle/oradata/ORA11GR2/system01.dbf

lrwx------ 1 oracle oinstall 64 Oct 12 23:29 257 -> /u01/app/oracle/oradata/ORA11GR2/control02.ctl

lrwx------ 1 oracle oinstall 64 Oct 12 23:29 256 -> /u01/app/oracle/oradata/ORA11GR2/control01.ctl

lr-x------ 1 oracle oinstall 64 Oct 12 23:29 11 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb

lrwx------ 1 oracle oinstall 64 Oct 12 23:29 10 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/lkORA11GR2

[oracle@wang ~]$

 

8：將找到的控制程式碼複製回來

[oracle@wang ~]$ cp /proc//899/fd/264 /u01/app/oracle/oradata/ORA11GR2/test.dbf

 

9：檢視資料檔案的狀態

SYS@ORA11GR2>select file#,status,name from v$datafile;

 

  1     SYSTEM    /u01/app/oracle/oradata/ORA11GR2/system01.dbf

  2    ONLINE     /u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

  3    ONLINE    /u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

  4    ONLINE    /u01/app/oracle/oradata/ORA11GR2/users01.dbf

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

  6    ONLINE   /u01/app/oracle/oradata/ORA11GR2/test.dbf

 

6 rows selected.

 

10：將6號資料檔案offline

SYS@ORA11GR2>alter database datafile 6 offline;

 

Database altered.

——再次檢視資料檔案狀態：

SYS@ORA11GR2>select file#,status,name from v$datafile;

 

     FILE# STATUSNAME

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

         1 SYSTEM /u01/app/oracle/oradata/ORA11GR2/system01.dbf

         2 ONLINE /u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

         3 ONLINE /u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

         4 ONLINE /u01/app/oracle/oradata/ORA11GR2/users01.dbf

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

         6 RECOVER  /u01/app/oracle/oradata/ORA11GR2/test.dbf

 

6 rows selected.

 

11：恢復資料檔案

SYS@ORA11GR2>recover datafile 6;

Media recovery complete.

SYS@ORA11GR2>

 

12：將6號資料檔案online

SYS@ORA11GR2>alter database datafile 6 online;

 

Database altered.

 

13.驗證：（檔案已經修復回來了）

SYS@ORA11GR2>!ls /u01/app/oracle/oradata/ORA11GR2/test.dbf

 

/u01/app/oracle/oradata/ORA11GR2/test.dbf