最近遇到一个案例，是将一个表空间offline之后，alert日志中不断的报错，最初该报错是在11.2.0.2被发现的。后来我在11.2.0.4的linux x86-64 bit下试验了一下，也发现了相同的现象。
如下是我的实验步骤：
[oracle@rhel63single ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 13 09:11:24 2015 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> select name from v$dbfile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/test/users01.dbf /u01/app/oracle/oradata/test/undotbs01.dbf /u01/app/oracle/oradata/test/sysaux01.dbf /u01/app/oracle/oradata/test/system01.dbf /u01/app/oracle/oradata/test/ten01.dbf /u01/app/oracle/oradata/test/tb_test_01.dbf /u01/app/oracle/oradata/test/ts1.dbf /u01/app/oracle/oradata/test/ts2.dbf /u01/app/oracle/oradata/test/test01.dbf /u01/app/oracle/oradata/test/test_uni_sz_2m_01.dbf /u01/app/oracle/oradata/test/test_uni_sz_1m_01.dbf 11 rows selected. SQL> create tablespace testtbs datafile '/u01/app/oracle/oradata/test/test.dbf' size 10M; Tablespace created. SQL> create table testtab tablespace testtbs as select * from dual; Table created. SQL> select * from testtab; D - X SQL> alter database datafile '/u01/app/oracle/oradata/test/test.dbf' offline; Database altered. SQL> select * from testtab; select * from testtab * ERROR at line 1: ORA-00376: file 12 cannot be read at this time ORA-01110: data file 12: '/u01/app/oracle/oradata/test/test.dbf' SQL> ! date Fri Mar 13 09:17:12 CST 2015 SQL>
?
从alert日志中看到：
Fri Mar 13 09:15:38 2015 create tablespace test datafile '/u01/app/oracle/oradata/test/test.dbf' size 10M ORA-1543 signalled during: create tablespace test datafile '/u01/app/oracle/oradata/test/test.dbf' size 10M... Fri Mar 13 09:15:57 2015 create tablespace testtbs datafile '/u01/app/oracle/oradata/test/test.dbf' size 10M Completed: create tablespace testtbs datafile '/u01/app/oracle/oradata/test/test.dbf' size 10M Fri Mar 13 09:16:46 2015 alter database datafile '/u01/app/oracle/oradata/test/test.dbf' offline Completed: alter database datafile '/u01/app/oracle/oradata/test/test.dbf' offline Fri Mar 13 09:17:01 2015 Checker run found 1 new persistent data failures Fri Mar 13 09:20:28 2015 Errors in file /u01/app/oracle/diag/rdbms/test/test/trace/test_m000_3186.trc: ORA-01135: file 12 accessed for DML/query is offline ORA-01110: data file 12: '/u01/app/oracle/oradata/test/test.dbf' Fri Mar 13 09:30:29 2015 Errors in file /u01/app/oracle/diag/rdbms/test/test/trace/test_m000_3217.trc: ORA-01135: file 12 accessed for DML/query is offline ORA-01110: data file 12: '/u01/app/oracle/oradata/test/test.dbf' Fri Mar 13 09:40:30 2015 Errors in file /u01/app/oracle/diag/rdbms/test/test/trace/test_m000_3250.trc: ORA-01135: file 12 accessed for DML/query is offline ORA-01110: data file 12: '/u01/app/oracle/oradata/test/test.dbf'
?
查看/u01/app/oracle/diag/rdbms/test/test/trace/test_m000_3250.trc文件的内容如下：
[oracle@rhel63single trace]$ cat /u01/app/oracle/diag/rdbms/test/test/trace/test_