数据库在open的时候数据文件被从操作系统直接删除
因为在linux系统中,之前打开过该文件的进程仍然持有相应的文件句柄,所指向的文件仍然可以读写,
文件描述符可以从/proc目录中得到
如果关闭数据库,则该句柄会消失
实际实验中发现dbw0进程开启后就会持有所有数据文件的句柄,但只有数据库对文件进行过写入操作之后才算是真正的持有句柄文件,未执行过写入操作的的文件在被从操作系统删除后数据库并不能继续对该文件进行读写操作
在虚拟机中实验过程如下:
以exmple表空间为例进行示范
(一)
1.重启数据库,查看dbw0进程
[root@oracle ~]# ps -ef |grep dbw0 |grep -v grep
oracle 19362 1 0 10:50 ? 00:00:00 ora_dbw0_ORCL
2.查看锁定example01.dbf文件的进程
[root@oracle ~]# lsof |grep example
oracle 19362 oracle 23uW REG 253,0 104865792 918426 /u01/app/oracle/oradata/ORCL/example01.dbf
oracle 19364 oracle 26u REG 253,0 104865792 918426 /u01/app/oracle/oradata/ORCL/example01.dbf
oracle 19368 oracle 20u REG 253,0 104865792 918426 /u01/app/oracle/oradata/ORCL/example01.dbf
oracle 19374 oracle 24u REG 253,0 104865792 918426 /u01/app/oracle/oradata/ORCL/example01.dbf
oracle 19383 oracle 19u REG 253,0 104865792 918426 /u01/app/oracle/oradata/ORCL/example01.dbf
可以看到其中是有dbw0进程的
3.在example表空间中建立表并插入数据,并手动运行检查点进程,确保dbw0对文件进行了写入操作。
SYS@ORCL>create table test_a(x int) tablespace example;
Table created.
SYS@ORCL>insert into test_a select rownum from dual connect by rownum<=10000;
10000 rows created.
SYS@ORCL>commit;
Commit complete.
SYS@ORCL>alter system checkpoint;
System altered.
4.此时从操作系统删除example01.dbf
[oracle@oracle ORCL]$ ls
aaa01.dbf control03.ctl redo02.log system01.dbf undotbs01.dbf
control01.ctl example01.dbf redo03.log temp01.dbf users01.dbf
control02.ctl redo01.log sysaux01.dbf ts_catalog01.dbf users02.dbf
[oracle@oracle ORCL]$ rm example01.dbf
现在我们来查看一下锁定example01.dbf的进程
[root@oracle ~]# lsof |grep example
oracle 19362 oracle 23uW REG 253,0 104865792 918426 /u01/app/oracle/oradata/ORCL/example01.dbf (deleted)
oracle 19364 oracle 26u REG 253,0 104865792 918426 /u01/app/oracle/oradata/ORCL/example01.dbf (deleted)
oracle 19366 oracle 24u REG 253,0 104865792 918426 /u01/app/oracle/oradata/ORCL/example01.dbf (deleted)
oracle 19368 oracle 20u REG 253,0 104865792 918426 /u01/app/oracle/oradata/ORCL/example01.dbf (deleted)
oracle 19374 oracle 24u REG 253,0 104865792 918426 /u01/app/oracle/oradata/ORCL/example01.dbf (deleted)
oracle 19383 oracle 19u REG 253,0 104865792 918426 /u01/app/oracle/oradata/ORCL/example01.dbf (deleted)
依然有19362说明此时dbw0依然对文件保持锁定
5.此时清空buffer_cache中内容,然后对example表中数据进行查询、插入数据
SYS@ORCL>alter system flush buffer_cache;
System altered.
SYS@ORCL>select count(*) from test_a;
COUNT(*)
----------
10000
读取成功
插入数据
SYS@ORCL>insert into test_a values(5);
1 row created.
SYS@ORCL>commit;
Commit complete.
插入完成后手动运行检查点进程
SYS@ORCL>alter system checkpoint;
System altered.
SYS@ORCL>select name,status from v$datafile;
NAME
--------------------------------------------------------------------------------
STATUS
-------
/u01/app/oracle/oradata/ORCL/system01.dbf
SYSTEM
/u01/app/oracle/oradata/ORCL/undotbs01.dbf
ONLINE
/u01/app/oracle/oradata/ORCL/sysaux01.dbf
ONLINE
NAME
--------------------------------------------------------------------------------
STATUS
-------
/u01/app/oracle/oradata/ORCL/users01.dbf
ONLINE
/u01/app/oracle/oradata/ORCL/example01.dbf
ONLINE
/u01/app/oracle/oradata/ORCL/ts_catalog01.dbf
ONLINE
NAME
--------------------------------------------------------------------------------
STATUS
-------
/u01/app/oracle/oradata/ORCL/users02.dbf
ONLINE
/u01/app/oracle/oradata/ORCL/aaa01.dbf
ONLINE
8 rows selected.
此时我们发现运行完检查点进程example01.dbf依然处于online状态 说明此时数据库对文件进行读写。
6.通过dbw0进程持有的文件句柄对文件进行恢复
首先查看dbw0进程
[root@oracle ~]# ps -ef |grep dbw0 |grep -v grep
oracle 19362 1 0 10:50 ? 00:00:00 ora_dbw0_ORCL
则我们进入到/proc/19362/fd中可以看到dbw0持有的文件句柄,红色会代表已经被删除的
[oracle@oracle ~]# cd /proc/19362/fd
[oracle@oracle fd]# ll
total 0
lr-x------ 1 oracle oinstall 64 Aug 27 11:28 0 -> /dev/null
lr-x------ 1 oracle oinstall 64 Aug 27 11:28 1 -> /dev/null
lrwx------ 1 oracle oinstall 64 Aug 27 11:28 10 -> /u01/app/oracle/admin/ORCL/adump/ora_18307.aud
lr-x------ 1 oracle oinstall 64 Aug 27 11:28 11 -> /dev/zero
lr-x------ 1 oracle oinstall 64 Aug 27 11:28 12 -> /dev/zero
lr-x------ 1 oracle oinstall 64 Aug 27 11:28 13 -> /u01/app/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 Aug 27 11:28 14 -> /u01/app/oracle/product/10.2.0/db_1/dbs/hc_ORCL.dat
lrwx------ 1 oracle oinstall 64 Aug 27 11:28 15 -> /u01/app/oracle/product/10.2.0/db_1/dbs/lkORCL
lrwx------ 1 oracle oinstall 64 Aug 27 11:28 16 -> /u01/app/oracle/oradata/ORCL/control01.ctl
lrwx------ 1 oracle oinstall 64 Aug 27 11:28 17 -> /u01/app/oracle/oradata/ORCL/control02.ctl
lrwx------ 1 oracle oinstall 64 Aug 27 11:28 18 -> /u01/app/oracle/oradata/ORCL/control03.ctl
lrwx------ 1 oracle oinstall 64 Aug 27 11:28 19 -> /u01/app/oracle/oradata/ORCL/system01.dbf
l-wx------ 1 oracle oinstall 64 Aug 27 11:28 2 -> /u01/app/oracle/admin/ORCL/bdump/orcl_dbw0_18366.trc
lrwx------ 1 oracle oinstall 64 Aug 27 11:28 20 -> /u01/app/oracle/oradata/ORCL/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 Aug 27 11:28 21 -> /u01/app/oracle/oradata/ORCL/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 Aug 27 11:28 22 -> /u01/app/oracle/oradata/ORCL/users01.dbf
lrwx------ 1 oracle oinstall 64 Aug 27 11:28 23 -> /u01/app/oracle/oradata/ORCL/example01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Aug 27 11:28 24 -> /u01/app/oracle/oradata/ORCL/ts_catalog01.dbf
lrwx------ 1 oracle oinstall 64 Aug 27 11:28 25 -> /u01/app/oracle/oradata/ORCL/users02.dbf
lrwx------ 1 oracle oinstall 64 Aug 27 11:28 26 -> /u01/app/oracle/oradata/ORCL/aaa01.dbf
lrwx------ 1 oracle oinstall 64 Aug 27 11:28 27 -> /u01/app/oracle/oradata/ORCL/temp01.dbf
lr-x------ 1 oracle oinstall 64 Aug 27 11:28 28 -> /u01/app/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 Aug 27 11:28 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 Aug 27 11:28 4 -> /dev/null
l-wx------ 1 oracle oinstall 64 Aug 27 11:28 5 -> /u01/app/oracle/admin/ORCL/udump/orcl_ora_18307.trc
l-wx------ 1 oracle oinstall 64 Aug 27 11:28 6 -> /u01/app/oracle/admin/ORCL/bdump/alert_ORCL.log
lrwx------ 1 oracle oinstall 64 Aug 27 11:28 7 -> /u01/app/oracle/product/10.2.0/db_1/dbs/lkinstORCL (deleted)
l-wx------ 1 oracle oinstall 64 Aug 27 11:28 8 -> /u01/app/oracle/admin/ORCL/bdump/alert_ORCL.log
lrwx------ 1 oracle oinstall 64 Aug 27 11:28 9 -> /u01/app/oracle/product/10.2.0/db_1/dbs/hc_ORCL.dat
把23拷贝回去就能恢复已经删除的文件
[oracle@oracle fd]$ cp 23 /u01/app/oracle/oradata/ORCL/example01.dbf
将example数据文件置为offline,recover之后再置为online
SYS@ORCL>alter database datafile 5 offline;
Database altered.
SYS@ORCL>recover datafile 5;
Media recovery complete.
SYS@ORCL>alter database datafile 5 online;
Database altered.
(二)
虚拟机利用快照恢复到一开始相同状态
1.重启数据库,查看dbw0进程
[root@oracle ~]# ps -ef |grep dbw0 |grep -v grep
oracle 18366 1 0 11:23 ? 00:00:00 ora_dbw0_ORCL
2查看锁定example01.dbf文件的进程
[root@oracle ~]# lsof |grep example
oracle 18366 oracle 23uW REG 253,0 104865792 918426 /u01/app/oracle/oradata/ORCL/example01.dbf
oracle 18368 oracle 26u REG 253,0 104865792 918426 /u01/app/oracle/oradata/ORCL/example01.dbf
oracle 18372 oracle 20u REG 253,0 104865792 918426 /u01/app/oracle/oradata/ORCL/example01.dbf
oracle 18413 oracle 19u REG 253,0 104865792 918426 /u01/app/oracle/oradata/ORCL/example01.dbf
oracle 18782 oracle 26u REG 253,0 104865792 918426 /u01/app/oracle/oradata/ORCL/example01.dbf
可以看到其中是有dbw0进程的
3.在example表空间中建立表,插入数据,然后删除数据文件后再手动运行检查点进程
SYS@ORCL>create table test_a(x int) tablespace example;
Table created.
SYS@ORCL>insert into test_a select rownum from dual connect by rownum<=10000;
10000 rows created.
SYS@ORCL>commit;
Commit complete.
删除数据文件
[oracle@oracle ORCL]$ ls
aaa01.dbf control03.ctl redo02.log system01.dbf undotbs01.dbf
control01.ctl example01.dbf redo03.log temp01.dbf users01.dbf
control02.ctl redo01.log sysaux01.dbf ts_catalog01.dbf users02.dbf
[oracle@oracle ORCL]$ rm example01.dbf
手动运行检查点进程,查看数据文件状态
SYS@ORCL>alter system checkpoint;
System altered.
SYS@ORCL>select name,status from v$datafile;
NAME
--------------------------------------------------------------------------------
STATUS
-------
/u01/app/oracle/oradata/ORCL/system01.dbf
SYSTEM
/u01/app/oracle/oradata/ORCL/undotbs01.dbf
ONLINE
/u01/app/oracle/oradata/ORCL/sysaux01.dbf
ONLINE
NAME
--------------------------------------------------------------------------------
STATUS
-------
/u01/app/oracle/oradata/ORCL/users01.dbf
ONLINE
/u01/app/oracle/oradata/ORCL/example01.dbf
RECOVER
/u01/app/oracle/oradata/ORCL/ts_catalog01.dbf
ONLINE
NAME
--------------------------------------------------------------------------------
STATUS
-------
/u01/app/oracle/oradata/ORCL/users02.dbf
ONLINE
/u01/app/oracle/oradata/ORCL/aaa01.dbf
ONLINE
发现此事example01.dbf已经处于recover状态,此时已经不能对文件进行读写
查看进程对文件的锁定
[root@oracle ~]# lsof |grep example
[root@oracle ~]#
发现dbw0进程已经解除了对文件的锁定,不能进行读写操作