做系统管理的都是这样，难免会误删文件，某天要是把某个Oracle数据文件删除，那该如何恢复呢？(这里数据库是OPEN的，并且未关闭)
建立测试表空间
创建测试用户
插入测试数据
删除数据文件
恢复数据库文件
建立测试表空间
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
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.创建测试账户
|
1
2
|
SQL>
create
user
test identified
by
test
default
tablespace test;
SQL>
grant
connect
,resource
to
test;
|
3.插入测试数据
|
1
2
3
4
5
6
7
8
|
SQL> conn test/test
SQL>
create
table
t1(id
int
);
SQL>
insert
into
t1
values
(1);
SQL>
select
*
from
t1;
ID
----------
1
|
4.删除数据文件
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
[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.数据文件恢复
|
1
2
3
4
5
6
|
[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就是被删除的文件
|
1
|
[oracle@db2 fd]$
cp
27
/opt/oracle/oradata/member/test01
.dbf
|
查看test表空间状态
|
1
2
3
4
5
6
7
8
|
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表文件
|
1
2
3
4
5
|
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.
|
#以上就成功恢复了，若是出现
|
1
2
3
4
5
|
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用户下
|
1
2
|
[root@db2 ~]# chown -R oracle.oinstall /opt/oracle/oradata/member/test01.dbf
再recover datafile
'/opt/oracle/oradata/member/test01.dbf'
|
实验完成后，删除测试用户及测试表空间
|
1
2
|
SQL>
drop
user
test
cascade
;
SQL>
drop
tablespace test INCLUDING CONTENTS
AND
DATAFILES;
|
本文转自 rong341233 51CTO博客，原文链接:http://blog.51cto.com/fengwan/1737664
