create tablespace rmtest datafile '/u01/123.dbf' size 10M;
create table t1 tablespace rmtest as select * from dba_objects where rownum<200;
SQL> select segment_name,tablespace_name from dba_segments where tablespace_Name='RMTEST';
SQL> SET LINES 120
SQL> /
SEGMENT_NAME TABLESPACE_NAME
------------------------ ------------------------------
T1 RMTEST
SQL>
----模拟脏事务并不提交。
[oracle@std ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 4 19:36:33 2019
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> delete from t1;
199 rows deleted.
SQL>
删除文件
SQL> host rm /u01/123.dbf
[oracle@std ~]$ ps -ef|grep dbw
oracle 1732 1 0 18:57 ? 00:00:00 ora_dbw0_std
oracle 3657 1669 0 19:37 pts/0 00:00:00 grep --color=auto dbw
[oracle@std ~]$ lsof -p 1732|grep "/u01/123.dbf"
oracle 1732 oracle 265uW REG 8,3 10493952 16777288 /u01/123.dbf (deleted)
SQL> alter tablespace rmtest read only;
alter tablespace rmtest read only
*
ERROR at line 1:
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/u01/123.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
cd /proc/1732/fd
cp 265 /u01/123.dbf
alter tablespace rmtest read only;
归档模式没有备份条件下删除数据文件恢复 Linux(数据库未崩溃
场景描述:操作系统级别的删除数据文件(/oracle/oradata/ora10g/app1.dbf),而且数据库没有崩溃,仍然处于open状态。
原理:在Linux操作系统中,如果文件从操作系统级别被删除掉,之前打开该文件的进程仍然持有相应的文件句柄,所指向的文件仍然可以读写,并且该文件的文件描述符可以从/proc目录中获得。如果关闭数据库,则句柄就会丢失。
恢复步骤如下:
1,检查dbwr的进程PID
$ ps -ef|grep dbw0|grep -v grep
2,dbwr会打开所有数据文件的句柄。
在proc目录中可以查到,目录名是进程PID,fd表示文件描述符
如图:
[root@localhost fd]# ls -l
total 0
lrwx------ 1 oracle dba 64 Jan 3 18:35 23 -> /oracle/oradata/ora10g/example01.dbf
lrwx------ 1 oracle dba 64 Jan 3 18:35 24 -> /oracle/oradata/ora10g/test01.dbf
lrwx------ 1 oracle dba 64 Jan 3 18:3525-> /oracle/oradata/ora10g/app1.dbf (deleted)
lrwx------ 1 oracle dba 64 Jan 3 18:35 26 -> /oracle/oradata/ora10g/perfstat.dbf
lrwx------ 1 oracle dba 64 Jan 3 18:35 27 -> /oracle/oradata/ora10g/test02.dbf
lrwx------ 1 oracle dba 64 Jan 3 18:35 28 -> /oracle/oradata/ora10g/temp01.dbf
lrwx------ 1 oracle dba 64 Jan 3 18:35 29 -> /oracle/oradata/ora10g/temp02.dbf
红色部分就是表示app1文件已经被删除了
3,直接拷贝句柄到原来数据文件的位置
host cp /proc/3591/fd/25/oracle/oradata/ora10g/app1.dbf
4,恢复文件
alter database datafile 7 offline;
recover datafile 7;
alter database datafile 7 online;
alter database datafile '/u01/123.dbf' offline;
[oracle@std ~]$ lsof -p 1732|grep "/u01/123.dbf" 就不存在了---