平台:redhat 6.4
数据库:11.2.0.3
该实验模拟用rm -rf操作误删数据文件,不使用备份的情况下完全恢复。
我们现在创建一个表
SQL> create table tt as select * from dba_objects where rownum<=1000;
Table created.
SQL> select count(*) from tt;
COUNT(*)
----------
1000
删除这张表所在的用户数据文件
[orcl@gitiracb orcl]$ rm -rf users01.dbf
我们先找到这个文件上的进程
[orcl@gitiracb orcl]$ lsof | grep /home/orcl/app/oradata/orcl/users01.dbf
oracle 26852 orcl 259u REG 8,2 51126272 4462850 /home/orcl/app/oradata/orcl/users01.dbf (deleted)
oracle 26854 orcl 264u REG 8,2 51126272 4462850 /home/orcl/app/oradata/orcl/users01.dbf (deleted)
oracle 26858 orcl 259u REG 8,2 51126272 4462850 /home/orcl/app/oradata/orcl/users01.dbf (deleted)
可以看到这个数据文件上有三个进程分别是dbwn,lgwr,smon进程
[orcl@gitiracb orcl]$ ps -ef|grep 26852
orcl 26852 1 0 16:32 ? 00:00:03 ora_dbw1_orcl
orcl 29251 19572 0 17:19 pts/0 00:00:00 grep 26852
[orcl@gitiracb orcl]$ ps -ef|grep 26854
orcl 26854 1 0 16:32 ? 00:00:00 ora_lgwr_orcl
orcl 29274 19572 0 17:20 pts/0 00:00:00 grep 26854
[orcl@gitiracb orcl]$ ps -ef|grep 26858
orcl 26858 1 0 16:32 ? 00:00:00 ora_smon_orcl
orcl 29283 19572 0 17:20 pts/0 00:00:00 grep 26858
我们再根据其中的随便一个进程找到其删除文件的句柄
[orcl@gitiracb orcl]$ ls -l /proc/26852/fd
total 0
lr-x------ 1 orcl dba 64 Jun 2 16:35 0 -> /dev/null
l-wx------ 1 orcl dba 64 Jun 2 16:35 1 -> /dev/null
lr-x------ 1 orcl dba 64 Jun 2 16:35 10 -> /dev/zero
lr-x------ 1 orcl dba 64 Jun 2 16:35 11 -> /dev/zero
lrwx------ 1 orcl dba 64 Jun 2 16:35 12 -> /home/orcl/app/product/11.2.0/dbhome_1/dbs/hc_orcl.dat
lr-x------ 1 orcl dba 64 Jun 2 16:35 13 -> /home/orcl/app/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
lr-x------ 1 orcl dba 64 Jun 2 16:35 14 -> /proc/26852/fd
lr-x------ 1 orcl dba 64 Jun 2 16:35 15 -> /dev/zero
lrwx------ 1 orcl dba 64 Jun 2 16:35 16 -> /home/orcl/app/product/11.2.0/dbhome_1/dbs/hc_orcl.dat
lrwx------ 1 orcl dba 64 Jun 2 16:35 17 -> /home/orcl/app/product/11.2.0/dbhome_1/dbs/lkORCL
lr-x------ 1 orcl dba 64 Jun 2 16:36 18 -> /home/orcl/app/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
l-wx------ 1 orcl dba 64 Jun 2 16:35 2 -> /dev/null
lrwx------ 1 orcl dba 64 Jun 2 16:36 257 -> /home/orcl/app/oradata/orcl/system01.dbf
lrwx------ 1 orcl dba 64 Jun 2 16:36 258 -> /home/orcl/app/oradata/orcl/sysaux01.dbf
lrwx------ 1 orcl dba 64 Jun 2 16:35 259 -> /home/orcl/app/oradata/orcl/users01.dbf (deleted)
lrwx------ 1 orcl dba 64 Jun 2 16:35 260 -> /home/orcl/app/oradata/orcl/example01.dbf
lrwx------ 1 orcl dba 64 Jun 2 16:41 261 -> /home/orcl/app/oradata/orcl/undotbs01.dbf
lr-x------ 1 orcl dba 64 Jun 2 16:35 3 -> /dev/null
lr-x------ 1 orcl dba 64 Jun 2 16:35 4 -> /dev/null
lrwx------ 1 orcl dba 64 Jun 2 16:35 5 -> /home/orcl/app/product/11.2.0/dbhome_1/dbs/hc_orcl.dat
lr-x------ 1 orcl dba 64 Jun 2 16:35 6 -> /dev/null
lr-x------ 1 orcl dba 64 Jun 2 16:35 7 -> /dev/null
lr-x------ 1 orcl dba 64 Jun 2 16:35 8 -> /dev/null
lr-x------ 1 orcl dba 64 Jun 2 16:35 9 -> /dev/null
我们看到红色标明的就是刚刚删除数据文件的句柄信息,将这个259文件拷贝到原来的位置
[orcl@gitiracb orcl]$ cp /proc/26852/fd/259 /home/orcl/app/oradata/orcl/users01.dbf
再到数据库中恢复这个文件
SQL> alter database datafile '/home/orcl/app/oradata/orcl/users01.dbf' offline;
Database altered.
SQL> recover datafile '/home/orcl/app/oradata/orcl/users01.dbf';
Media recovery complete.
SQL> alter database datafile '/home/orcl/app/oradata/orcl/users01.dbf' online;
Database altered.
再看看alter文件信息
alter database datafile '/home/orcl/app/oradata/orcl/users01.dbf' offline
Completed: alter database datafile '/home/orcl/app/oradata/orcl/users01.dbf' offline
Tue Jun 02 17:09:54 2015
ALTER DATABASE RECOVER datafile '/home/orcl/app/oradata/orcl/users01.dbf'
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 1 Seq 67 Reading mem 0
Mem# 0: /home/orcl/app/oradata/orcl/redo01.log
Media Recovery Complete (orcl)
Completed: ALTER DATABASE RECOVER datafile '/home/orcl/app/oradata/orcl/users01.dbf'
alter database datafile '/home/orcl/app/oradata/orcl/users01.dbf' online
Completed: alter database datafile '/home/orcl/app/oradata/orcl/users01.dbf' online\
SQL> select count(*) from tt;
COUNT(*)
----------
1000
恢复完成
注:因为找句柄信息依赖于数据文件上的进程,如果数据文件上的进程全部被kill,例如数据库关闭了,无法完成上述任务。
因为需要recover操作,如果redo log 也同时被删除无法完成上述任务。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29989552/viewspace-1682538/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29989552/viewspace-1682538/