Oracle資料檔案物理刪除後的恢複

Oracle資料檔案物理刪除後的恢複

做系統管理的都是這樣，難免會誤刪檔案，某天要是把某個Oracle資料檔案刪除，那該如何恢複呢？(這裡資料庫是OPEN的，並且未關閉)

1. 建立測試資料表空間
2. 建立測試使用者
3. 插入測試資料
4. 刪除資料檔案
5. 恢複資料庫檔案

建立測試資料表空間

SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/member/system01.dbf
/opt/oracle/oradata/member/sysaux01.dbf
/opt/oracle/oradata/member/undotbs01.dbf
/opt/oracle/oradata/member/users01.dbf
SQL> create tablespace test datafile '/opt/oracle/oradata/member/test01.dbf' size 10m;
Tablespace created.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/member/system01.dbf
/opt/oracle/oradata/member/sysaux01.dbf
/opt/oracle/oradata/member/undotbs01.dbf
/opt/oracle/oradata/member/users01.dbf
/opt/oracle/oradata/member/test01.dbf

2.建立測試賬戶
SQL> create user test identified by test default tablespace test;
SQL> grant connect,resource to test;

3.插入測試資料
SQL> conn test/test
SQL> create table t1(id int);
SQL> insert into t1 values(1);
SQL> select * from t1;
 
        ID
----------
        1

4.刪除資料檔案
[oracle@db2 ~]$ rm -f /opt/oracle/oradata/member/test01.dbf
[oracle@db2 ~]$ sqlplus test/test
SQL> create table t2 as select * from t1;
create table t2 as select * from t1
                                *
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/opt/oracle/oradata/member/test01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
 
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME                    TABLESPACE_NAME
------------------------------ ------------------------------
T1                            TEST

資料檔案被刪除了，這怎麼辦呢？這個時候千萬別重啟資料庫，否則資料就丟失了

5.資料檔案恢複

[oracle@db2 ~]$ ps -ef |grep dbw0
oracle    3309    1  0 12:07 ?        00:00:00 ora_dbw0_member
oracle    6217  5105  0 15:29 pts/0    00:00:00 grep dbw0
#找到ora_dbw0_SID的進程號3309
[oracle@db2 ~]$ cd /proc/3309/fd
#以上3309就是進程號，然後執行ls -al查看檔案的連結

可以看到檔案27就是被刪除的檔案
[oracle@db2 fd]$ cp 27 /opt/oracle/oradata/member/test01.dbf

查看test資料表空間狀態
SQL> select name,status from v$datafile;
NAME                      STATUS
----------------------------------------  -------
/opt/oracle/oradata/member/system01.dbf    SYSTEM
/opt/oracle/oradata/member/sysaux01.dbf    ONLINE
/opt/oracle/oradata/member/undotbs01.dbf    ONLINE
/opt/oracle/oradata/member/users01.dbf    ONLINE
/opt/oracle/oradata/member/test01.dbf    ONLINE

下線test01表檔案
SQL> alter database datafile '/opt/oracle/oradata/member/test01.dbf' offline;
SQL> recover datafile '/opt/oracle/oradata/member/test01.dbf';
Media recovery complete.
SQL> alter database datafile '/opt/oracle/oradata/member/test01.dbf' online;
Database altered.

#以上就成功恢複了，若是出現
SQL> recover datafile '/opt/oracle/oradata/member/test01.dbf';
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 5: '/opt/oracle/oradata/member/test01.dbf'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/opt/oracle/oradata/member/test01.dbf'

則有可能是/opt/oracle/oradata/member/test01.dbf檔案的許可權問題引起，在root使用者下
[root@db2 ~]# chown -R oracle.oinstall /opt/oracle/oradata/member/test01.dbf 
再recover datafile '/opt/oracle/oradata/member/test01.dbf'

實驗完成後，刪除測試使用者及測試資料表空間
SQL> drop user test cascade;
SQL> drop tablespace test INCLUDING CONTENTS AND DATAFILES;