ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 数据库打开情况下删除数据文件会发生什么(unix)

数据库打开情况下删除数据文件会发生什么(unix)

原创 Linux操作系统 作者:yitingyu2008 时间:2009-01-06 13:18:11 0 删除 编辑

数据库打开情况下删除数据文件会发生什么(unix)

创建测试表空间及表
 
 
SQL 10G>create tablespace testearse datafile '/opt/oracle/oradata/dbtest/testearse.dbf' size 1m;
Tablespace created.
 
SQL 10G>create table testearse(a number) tablespace  testearse;
Table created.
 
 
 
看看有哪些进程关联到这个数据文件
 
SQL 10G>!
[oracle@csdba ~]$ lsof |grep testearse
oracle     4424  oracle   33uW     REG        8,9    1056768     852911 /opt/oracle/oradata/dbtest/testearse.dbf
oracle    25121  oracle   15u      REG        8,9    1056768     852911 /opt/oracle/oradata/dbtest/testearse.dbf
 
 
删除这个数据文件

[oracle@csdba ~]$ rm /opt/oracle/oradata/dbtest/testearse.dbf
[oracle@csdba ~]$
 
再观看lsof的结果,发现相关的状态已经变成deleted,但是文件还是保持打开状态

[oracle@csdba ~]$ lsof |grep testearse
oracle     4424  oracle   33uW     REG        8,9    1056768     852911 /opt/oracle/oradata/dbtest/testearse.dbf (deleted)
oracle    25121  oracle   15u      REG        8,9    1056768     852911 /opt/oracle/oradata/dbtest/testearse.dbf (deleted)
 
 
oracle    4424     1  0 Sep14 ?        00:00:35 ora_dbw0_dbtest
oracle   25121 25893  0 14:41 ?        00:00:00 oracledbtest (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
 
 
SQL 10G>insert into testearse values(10);
1 row created.
SQL 10G>commit;
Commit complete.
SQL 10G>alter system checkpoint;
System altered.
SQL 10G>insert into testearse values(10);
1 row created.
SQL 10G>commit;
SQL 10G>select * from testearse;
         A
----------
        10
        10
        
SQL 10G>ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
Session altered.
SQL 10G>select * from testearse;
         A
----------
        10
        10
由于本身sqlplus的process和testearse.dbf还建立连接,所以这时仍然可以对这个表进行操作
 
 
 
退出sqlplus,重新开启sqlplus
 
  
SQL 10G>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining Scoring Engine options
 
[oracle@csdba ~]$ lsof |grep testearse
oracle     4424  oracle   33uW     REG        8,9    1056768     852911 /opt/oracle/oradata/dbtest/testearse.dbf (deleted)
 
这时只剩下dbwr进程和testearse.dbf还建立连接

[oracle@csdba bdump]$  sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 10 15:47:58 2005
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining Scoring Engine options
SQL 10G>conn test/test
Connected.
SQL 10G>select * from testearse;
         A
----------
         10
         10
SQL 10G>ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
Session altered.
 
刷新buffer cache,写入数据文件
 
这时由于原来sqlplus和testearse.dbf的连接已经关闭,新连接由于数据文件被删除而无法建立
,所以这时候不能对testearse进行操作
 
SQL 10G>select * from testearse;
select * from testearse
*
ERROR at line 1:
ORA-01116: error in opening database file 13
ORA-01110: data file 13: '/opt/oracle/oradata/dbtest/testearse.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
 
 

SQL 10G>alter system checkpoint;
System altered.

[oracle@csdba ~]$ lsof |grep testearse

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16571969/viewspace-528629/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2008-12-09

  • 博文量
    1
  • 访问量
    3502