linux 误删除Oracle数据文件恢复
来源:互联网 发布:道亨软件 编辑:程序博客网 时间:2024/06/05 05:29
本文偶得,权当记录查询,原文转至现某炬Oracle lunar 女神
[oracle@lunar ~]$ sqlplus /
as
sysdba
SQL*Plus: Release 11.2.0.3.0 Production
on
Fri Mar 15 23:46:35 2013
Copyright (c) 1982, 2011, Oracle.
All
rights reserved.
Connected
to
:
Oracle
Database
11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With
the Partitioning, OLAP, Data Mining
and
Real
Application Testing options
SQL>
select
tablespace_name
from
dba_tablespaces;
TABLESPACE_NAME
------------------------------------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EZIO
6
rows
selected.
创建测试表空间
SQL>
create
tablespace lunar_test datafile
'/test/lunar_test01.dbf'
size
10M;
Tablespace created.
SQL>
alter
user
lunar
default
tablespace lunar_test;
User
altered.
创建表插入数据
SQL> conn lunar/lunar
Connected.
SQL>
create
table
test_table (
name
varchar2(300)) tablespace lunar_test;
Table
created.
SQL>
insert
into
test_table
values
(
'Love you forever, my baby, Happy Childrens Day!'
);
1 row created.
SQL>
commit
;
Commit
complete.
SQL>
SQL>
select
*
from
test_table;
NAME
--------------------------------------------------------------------------------
Love you forever, my baby, Happy Childrens
Day
!
删除datafile
SQL> !rm -rf /test/lunar_test01.dbf
SQL> !ls -lrt /test/lunar_test01.dbf
ls: /test/lunar_test01.dbf:
No
such file
or
directory
SQL>
数据还在,因为从buffer cache中读到的
SQL> conn lunar/lunar
Connected.
SQL>
select
*
from
test_table;
NAME
--------------------------------------------------------------------------------
Love you forever, my baby, Happy Childrens
Day
!
执行flush buffer cache
SQL> conn /
as
sysdba
Connected.
SQL>
alter
system flush buffer_cache;
System altered.
可以看见,再次查询,报错文件状态不对了(找不到了)
SQL> conn lunar/lunar
Connected.
SQL>
select
*
from
test_table;
select
*
from
test_table
*
ERROR
at
line 1:
ORA-01116: error
in
opening
database
file 6
ORA-01110: data file 6:
'/test/lunar_test01.dbf'
ORA-27041: unable
to
open
file
Linux-x86_64 Error: 2:
No
such file
or
directory
Additional information: 3
检查dbwr进程的spid
SQL> !ps -ef|grep dbw
oracle 2757 1 0 12:29 ? 00:00:14 ora_dbw0_bb
oracle 8912 8872 0 23:50 pts/3 00:00:00 /bin/bash -c ps -ef|grep dbw
oracle 8914 8912 0 23:50 pts/3 00:00:00 grep dbw
找到dbwr的句柄
SQL> !/usr/sbin/lsof -p 2757
COMMAND PID
USER
FD TYPE DEVICE
SIZE
/
OFF
NODE
NAME
oracle 2757 oracle cwd DIR 253,0 4096 6947181 /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs
oracle 2757 oracle rtd DIR 253,0 4096 2 /
oracle 2757 oracle txt REG 253,0 232399473 1179781 /u01/app/oracle/product/11.2.0.3/dbhome_1/bin/oracle
oracle 2757 oracle DEL REG 0,4 1933317 /SYSV00000000
oracle 2757 oracle DEL REG 0,4 1966086 /SYSV00000000
oracle 2757 oracle DEL REG 0,4 1998855 /SYSV2b7268c4
oracle 2757 oracle mem REG 253,0 144776 2293762 /lib64/ld-2.5.so
oracle 2757 oracle mem REG 253,0 5328 4822547 /usr/lib64/libaio.so.1.0.1
oracle 2757 oracle mem REG 253,0 1722328 2293764 /lib64/libc-2.5.so
oracle 2757 oracle mem REG 253,0 23360 2293771 /lib64/libdl-2.5.so
oracle 2757 oracle mem REG 253,0 615136 2293809 /lib64/libm-2.5.so
oracle 2757 oracle mem REG 253,0 145872 2293775 /lib64/libpthread-2.5.so
oracle 2757 oracle mem REG 253,0 53448 2293781 /lib64/librt-2.5.so
oracle 2757 oracle mem REG 253,0 114352 2293986 /lib64/libnsl-2.5.so
oracle 2757 oracle mem REG 253,0 58949 6955646 /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libnque11.so
oracle 2757 oracle 262uW REG 253,0 87040000 4784725 /u01/app/oracle/oradata/bb/temp01.dbf
oracle 2757 oracle 263uW REG 253,0 10493952 1639050 /test/lunar_test01.dbf (deleted) 可以看见文件被标识为
delete
进入dbwr进程的File Descriptor number目录中
SQL> !ls -ltar /proc/2757/fd/
total 0
dr-xr-xr-x 7 oracle oinstall 0 Mar 15 12:29 ..
dr-x
------ 2 oracle oinstall 0 Mar 15 12:29 .
lr-x
------ 1 oracle oinstall 64 Mar 15 23:30 9 -> /dev/null
lr-x
------ 1 oracle oinstall 64 Mar 15 23:30 8 -> /dev/null
lrwx
------ 1 oracle oinstall 64 Mar 15 23:30 7 -> /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/hc_bb.dat
lr-x
------ 1 oracle oinstall 64 Mar 15 23:30 6 -> /dev/null
lr-x
------ 1 oracle oinstall 64 Mar 15 23:30 5 -> /dev/null
lr-x
------ 1 oracle oinstall 64 Mar 15 23:30 4 -> /dev/null
lr-x
------ 1 oracle oinstall 64 Mar 15 23:30 3 -> /dev/null
lrwx
------ 1 oracle oinstall 64 Mar 15 23:30 263 -> /test/lunar_test01.dbf (deleted) 可以看见dbwr的进程的FD号
lrwx
------ 1 oracle oinstall 64 Mar 15 23:30 262 -> /u01/app/oracle/oradata/bb/temp01.dbf
lrwx
------ 1 oracle oinstall 64 Mar 15 23:30 261 -> /u01/app/oracle/oradata/bb/ezio01.dbf
lrwx
------ 1 oracle oinstall 64 Mar 15 23:30 260 -> /u01/app/oracle/oradata/bb/users01.dbf
lrwx
------ 1 oracle oinstall 64 Mar 15 23:30 259 -> /u01/app/oracle/oradata/bb/undotbs01.dbf
lrwx
------ 1 oracle oinstall 64 Mar 15 23:30 258 -> /u01/app/oracle/oradata/bb/sysaux01.dbf
lrwx
------ 1 oracle oinstall 64 Mar 15 23:30 257 -> /u01/app/oracle/oradata/bb/system01.dbf
lrwx
------ 1 oracle oinstall 64 Mar 15 23:30 256 -> /u01/app/oracle/oradata/bb/control01.ctl
l-wx
------ 1 oracle oinstall 64 Mar 15 23:30 2 -> /dev/null
lr-x
------ 1 oracle oinstall 64 Mar 15 23:30 18 -> /u01/app/oracle/product/11.2.0.3/dbhome_1/rdbms/mesg/oraus.msb
lrwx
------ 1 oracle oinstall 64 Mar 15 23:30 17 -> /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/lkBB
lrwx
------ 1 oracle oinstall 64 Mar 15 23:30 16 -> /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/hc_bb.dat
lr-x
------ 1 oracle oinstall 64 Mar 15 23:30 15 -> /dev/zero
lr-x
------ 1 oracle oinstall 64 Mar 15 23:30 14 -> /proc/2757/fd
lr-x
------ 1 oracle oinstall 64 Mar 15 23:30 13 -> /u01/app/oracle/product/11.2.0.3/dbhome_1/rdbms/mesg/oraus.msb
lrwx
------ 1 oracle oinstall 64 Mar 15 23:30 12 -> /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/hc_bb.dat
lr-x
------ 1 oracle oinstall 64 Mar 15 23:30 11 -> /dev/zero
lr-x
------ 1 oracle oinstall 64 Mar 15 23:30 10 -> /dev/zero
l-wx
------ 1 oracle oinstall 64 Mar 15 23:30 1 -> /dev/null
lr-x
------ 1 oracle oinstall 64 Mar 15 23:30 0 -> /dev/null
lrwx
------ 1 oracle oinstall 64 Mar 15 23:37 19 -> socket:[42951]
恢复过程
[oracle@lunar ~]$
cd
/proc/2757/fd/
--根据File Descriptor number重建数据文件:
[oracle@lunar fd]$
cat
263 >
/test/lunar01
.dbf
检查下,文件已经恢复完成,大小为10m
[oracle@lunar fd]$
ls
-lrt
/test/lunar01
.dbf
-rw-r--r-- 1 oracle oinstall 10493952 Mar 15 23:50
/test/lunar01
.dbf
将数据文件offline
SQL>
alter
database
datafile
'/test/lunar_test01.dbf'
offline;
Database
altered.
[/sql
修改控制文件的文件信息,因为已经将rm的/test/lunar_test01.dbf,创建到/test/lunar01.dbf,因此要同步控制文件中的信息
1
SQL>
alter
database
rename file
'/test/lunar_test01.dbf'
to
'/test/lunar01.dbf'
;
Database
altered.
恢复datafile
SQL> recover datafile
'/test/lunar01.dbf'
;
Media recovery complete.
将数据文件online
SQL>
alter
database
datafile
'/test/lunar01.dbf'
online;
Database
altered.
完成恢复
SQL> conn lunar/lunar
Connected.
SQL>
select
*
from
test_table;
NAME
--------------------------------------------------------------------------------
Love you forever, my baby, Happy Childrens
Day
!
SQL>
SQL>
select
tablespace_name
from
dba_tablespaces;
TABLESPACE_NAME
------------------------------------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EZIO
LUNAR_TEST
7
rows
selected.
SQL>
select
name
from
v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/bb/system01.dbf
/u01/app/oracle/oradata/bb/sysaux01.dbf
/u01/app/oracle/oradata/bb/undotbs01.dbf
/u01/app/oracle/oradata/bb/users01.dbf
/u01/app/oracle/oradata/bb/ezio01.dbf
/test/lunar01.dbf
6
rows
selected
0 0
- linux 误删除Oracle数据文件恢复
- Linux下ORACLE误删除数据文件恢复操作
- 【转载】linux中误删除oracle数据文件的恢复操作
- 使用linux文件句柄恢复误删除的Oracle数据文件
- Linux中模拟oracle数据文件的误删除与恢复
- 使用linux文件句柄恢复误删除的Oracle数据文件
- 【转载】linux中误删除oracle数据文件的恢复操作
- Linux 下Oracle 数据文件被物理误删除的恢复
- Linux操作系统删除数据文件恢复
- oracle数据文件被误操作删除了恢复方法
- Oracle数据库数据文件rm -rf误删除后恢复
- Oracle数据库数据文件rm -rf误删除后恢复
- 使用文件句柄方式恢复误删除的Oracle数据文件
- 使用文件句柄方式恢复误删除的Oracle数据文件
- Linux下使用extundelete恢复ext3误删除数据文件
- 通过句柄恢复Linux下误删除的数据库数据文件
- Oracle 意外删除数据文件的恢复
- ORACLE 删除数据文件模拟灾难恢复
- Fiddler(二)
- easyUI验证表单两次密码是否相同
- Monash Bridging Course
- spring的DI/IOC机制
- delphi RestClient 用法1
- linux 误删除Oracle数据文件恢复
- Hadoop-2.7.3环境下Hive-2.1.1安装配置。
- 数据排序
- 计161_Problem : 字符串操作一(串)
- 使用kaptcha生成验证码
- logstash5.x 启动异常
- oracle分页的两种方式
- Tomcat安装与部署
- 使用eClipse将java源码打包成jar包