【Oracle】数据库开启状态下删除数据文件

来源：互联网发布：apache ant 1.9.3 bin 编辑：程序博客网时间：2024/06/05 02:03

数据库在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进程已经解除了对文件的锁定，不能进行读写操作