数据库打开情况下删除数据文件会发生什么(unix)
数据库打开情况下删除数据文件会发生什么(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 ~]$ 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)))
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';
----------
10
10
SQL 10G>ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
Session altered.
SQL 10G>select * from testearse;
A
----------
10
10
----------
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)
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;
Connected.
SQL 10G>select * from testearse;
A
----------
10
10
----------
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
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/，如需转载，请注明出处，否则将追究法律责任。
上一篇：
没有了~
下一篇：
没有了~
请登录后发表评论
登录
全部评论
