当oraclek中的数据文件在OS系统下被误删除后,该如何恢复呢? 这时千万不要慌张重启数据库或者操作系统,可以通过dbwn进程相关句柄找回数据文件。
测试:
步骤1:用DBA身份登陆数据库并查看表空间状态和数据文件的路径
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 18 18:53:44 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
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
2 UNDOTBS1 YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 WUTONG YES NO YES
6 rows selected.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
WUTONG ONLINE
6 rows selected.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u03/oracle/oradata/WUTONG/datafile/o1_mf_system_cx3xt90z_.dbf
/u03/oracle/oradata/WUTONG/datafile/o1_mf_sysaux_cx3xt930_.dbf
/u03/oracle/oradata/WUTONG/datafile/o1_mf_undotbs1_cx3xt93b_.dbf
/u03/oracle/oradata/WUTONG/datafile/o1_mf_users_cx3xt940_.dbf
/u03/oracle/oradata/WUTONG/datafile/o1_mf_wutong_cx415lcj_.dbf
步骤2:模拟删除数据文件,创建表时提示失败
SQL> !rm /u03/oracle/oradata/WUTONG/datafile/o1_mf_wutong_cx415lcj_.dbf
SQL> !ls -l /u03/oracle/oradata/WUTONG/datafile/o1_mf_wutong_cx415lcj_.dbf
ls: cannot access /u03/oracle/oradata/WUTONG/datafile/o1_mf_wutong_cx415lcj_.dbf: No such file or directory
SQL> create table wutong.test as select * from scott.emp;
create table wutong.test as select * from scott.emp
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5:
'/u03/oracle/oradata/WUTONG/datafile/o1_mf_wutong_cx415lcj_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
步骤3:在OS系统下查看dbw进程,找到进程SPID号,在/proc/27722/fd路径下找到被删除的数据文件
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ ps -ef|grep dbw|grep -v grep
oracle 27722 1 0 Sep14 ? 00:02:29 ora_dbw0_wutong
[oracle@localhost ~]$ ll /proc/27722/fd
total 0
lr-x------. 1 oracle oinstall 64 Sep 18 18:59 0 -> /dev/null
l-wx------. 1 oracle oinstall 64 Sep 18 18:59 1 -> /dev/null
lrwx------. 1 oracle oinstall 64 Sep 18 18:59 10 -> /u01/app/oracle/product/11.2.0.4/dbs/lkWUTONG
lr-x------. 1 oracle oinstall 64 Sep 18 18:59 11 -> /u01/app/oracle/product/11.2.0.4/rdbms/mesg/oraus.msb
l-wx------. 1 oracle oinstall 64 Sep 18 18:59 2 -> /dev/null
lrwx------. 1 oracle oinstall 64 Sep 18 18:59 256 -> /u03/oracle/oradata/WUTONG/controlfile/o1_mf_cx3xw15p_.ctl
lrwx------. 1 oracle oinstall 64 Sep 18 18:59 257 -> /u01/app/oracle/fast_recovery_area/WUTONG/controlfile/o1_mf_cx3xw181_.ctl
lrwx------. 1 oracle oinstall 64 Sep 18 18:59 258 -> /u03/oracle/oradata/WUTONG/datafile/o1_mf_system_cx3xt90z_.dbf
lrwx------. 1 oracle oinstall 64 Sep 18 18:59 259 -> /u03/oracle/oradata/WUTONG/datafile/o1_mf_sysaux_cx3xt930_.dbf
lrwx------. 1 oracle oinstall 64 Sep 18 18:59 260 -> /u03/oracle/oradata/WUTONG/datafile/o1_mf_undotbs1_cx3xt93b_.dbf
lrwx------. 1 oracle oinstall 64 Sep 18 18:59 261 -> /u03/oracle/oradata/WUTONG/datafile/o1_mf_users_cx3xt940_.dbf
lrwx------. 1 oracle oinstall 64 Sep 18 18:59 262 -> /u03/oracle/oradata/WUTONG/datafile/o1_mf_wutong_cx415lcj_.dbf (deleted)
lrwx------. 1 oracle oinstall 64 Sep 18 18:59 263 -> /u03/oracle/oradata/WUTONG/datafile/o1_mf_temp_cx3xw7ty_.tmp
lr-x------. 1 oracle oinstall 64 Sep 18 18:59 3 -> /dev/null
lr-x------. 1 oracle oinstall 64 Sep 18 18:59 4 -> /dev/null
lr-x------. 1 oracle oinstall 64 Sep 18 18:59 5 -> /dev/null
lr-x------. 1 oracle oinstall 64 Sep 18 18:59 6 -> /u01/app/oracle/product/11.2.0.4/rdbms/mesg/oraus.msb
lr-x------. 1 oracle oinstall 64 Sep 18 18:59 7 -> /proc/27722/fd
lr-x------. 1 oracle oinstall 64 Sep 18 18:59 8 -> /dev/zero
lrwx------. 1 oracle oinstall 64 Sep 18 18:59 9 -> /u01/app/oracle/product/11.2.0.4/dbs/hc_wutong.dat
步骤4:把被删除的数据文件CP到原有的数据文件路径下:
[oracle@localhost ~]$ cp /proc/27722/fd/262 /u03/oracle/oradata/WUTONG/datafile/o1_mf_wutong_cx415lcj_.dbf
[oracle@localhost ~]$ ll /u03/oracle/oradata/WUTONG/datafile/o1_mf_wutong_cx415lcj_.dbf
-rw-r-----. 1 oracle oinstall 3774881792 Sep 18 19:02 /u03/oracle/oradata/WUTONG/datafile/o1_mf_wutong_cx415lcj_.dbf
步骤5:把数据文件offline ,然后介质恢复后再online
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 18 19:03:33 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
SQL> alter database datafile 5 offline;
Database altered.
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;
Database altered.
SQL> create table wutong.test as select * from scott.emp;
Table created.