一個資料表空間offline後alert日誌報ORA-01135 和ORA-01110的問題，ora-01135ora-01110

一個資料表空間offline後alert日誌報ORA-01135 和ORA-01110的問題，ora-01135ora-01110

本文是原創文章，轉載請註明出處：http://blog.csdn.net/msdnchina/article/details/44336789

最近遇到一個案例，是將一個資料表空間offline之後，alert日誌中不斷的報錯，最初該報錯是在11.2.0.2被發現的。後來我在11.2.0.4的linux x86-64 bit下實驗了一下，也發現了相同的現象。
如下是我的實驗步驟：

[oracle@rhel63single ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 13 09:11:24 2015Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> 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.dbf11 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-XSQL> 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 timeORA-01110: data file 12: '/u01/app/oracle/oradata/test/test.dbf'SQL> ! dateFri Mar 13 09:17:12 CST 2015SQL> 

 

從alert日誌中看到：

Fri Mar 13 09:15:38 2015create tablespace test datafile '/u01/app/oracle/oradata/test/test.dbf' size 10MORA-1543 signalled during: create tablespace test datafile '/u01/app/oracle/oradata/test/test.dbf' size 10M...Fri Mar 13 09:15:57 2015create tablespace testtbs datafile '/u01/app/oracle/oradata/test/test.dbf' size 10MCompleted: create tablespace testtbs datafile '/u01/app/oracle/oradata/test/test.dbf' size 10MFri Mar 13 09:16:46 2015alter database datafile '/u01/app/oracle/oradata/test/test.dbf' offlineCompleted: alter database datafile '/u01/app/oracle/oradata/test/test.dbf' offlineFri Mar 13 09:17:01 2015Checker run found 1 new persistent data failuresFri Mar 13 09:20:28 2015Errors in file /u01/app/oracle/diag/rdbms/test/test/trace/test_m000_3186.trc:ORA-01135: file 12 accessed for DML/query is offlineORA-01110: data file 12: '/u01/app/oracle/oradata/test/test.dbf'Fri Mar 13 09:30:29 2015Errors in file /u01/app/oracle/diag/rdbms/test/test/trace/test_m000_3217.trc:ORA-01135: file 12 accessed for DML/query is offlineORA-01110: data file 12: '/u01/app/oracle/oradata/test/test.dbf'Fri Mar 13 09:40:30 2015Errors in file /u01/app/oracle/diag/rdbms/test/test/trace/test_m000_3250.trc:ORA-01135: file 12 accessed for DML/query is offlineORA-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_m000_3250.trcTrace file /u01/app/oracle/diag/rdbms/test/test/trace/test_m000_3250.trcOracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORACLE_HOME = /u02/app/oracle/product/11.2.0.4/db_1System name:    LinuxNode name:      rhel63singleRelease:        2.6.32-279.el6.x86_64Version:        #1 SMP Wed Jun 13 18:24:36 EDT 2012Machine:        x86_64VM name:        VMWare Version: 6Instance name: testRedo thread mounted by this instance: 1Oracle process number: 22Unix process pid: 3250, image: oracle@rhel63single (M000)*** 2015-03-13 09:40:30.195*** SESSION ID:(130.7) 2015-03-13 09:40:30.195*** CLIENT ID:() 2015-03-13 09:40:30.195*** SERVICE NAME:(SYS$BACKGROUND) 2015-03-13 09:40:30.195*** MODULE NAME:(MMON_SLAVE) 2015-03-13 09:40:30.195*** ACTION NAME:(Monitor Tablespace Thresholds) 2015-03-13 09:40:30.195 DDE rules only execution for: ORA 1110----- START Event Driven Actions Dump -------- END Event Driven Actions Dump --------- START DDE Actions Dump -----Executing SYNC actions----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----Successfully dispatched----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----Executing ASYNC actions----- END DDE Actions Dump (total 0 csec) -----ORA-01135: file 12 accessed for DML/query is offlineORA-01110: data file 12: '/u01/app/oracle/oradata/test/test.dbf'[oracle@rhel63single trace]$ 

 

後來在mos中搜尋了一下：
Getting Trace Files With DB_STRUCTURE_INTEGRITY_CHECK And An ORA-01110. (文檔 ID 1252614.1)
該文章與本現象相符。

該文章給出原因是：

These files are generated by the Health Check Monitor and specifically, the DB Structure Integrity Check.

http://docs.oracle.com/cd/B28359_01/server.111/b28310/diag007.htm#ADMIN11269
這個hcm（health check monitor）是從11.1就有的。

 

如下來自：Checker Run Found xx New Persistent Data Failures. (文檔 ID 1391202.1)

By default the database runs the Health Check on a regular basis.During the course of these health check executions if any failures are detected then these kind of messages are logged to the alert log.Health Monitor checks (also known as checkers, health checks, or checks) examine various layers and components of the database. Health checks detect file corruptions, physical and logical block corruptions, undo and redo corruptions, data dictionary corruptions, and more. The health checks generate reports of their findings and, in many cases, recommendations for resolving problems.

拓展一下：與health check相關的視圖（其實是synonym）有：

V$HM_CHECKV$HM_CHECK_PARAMV$HM_FINDINGV$HM_INFOV$HM_RECOMMENDATIONV$HM_RUN

以上視圖的查詢結果可以用到客戶db的巡檢中。

 

解決方案見下：

 

Solution

 The ORA-01110 is not seen in the alert log, but if you set an Error Stack for it, you would see it. So, we need to set an Error Stack for the ORA-01110 to get the name of the offline data file. 1) Set the following event and check the alert log for occurrence of the ORA-01110.      SQL> connect / as sysdba      SQL> alter system set events '1110 trace name errorstack level 3'; 2) If the trace files are still being generated, then once a file is created, the ORA-01110 should now be seen       in the alert log. When you capture it, disable the tracing:      SQL> connect / as sysdba      SQL> alter system set events '1110 trace name errorstack off'; 3. The ORA-01110 in the alert log seen, should give the name of the data file that's offline. 4. Please switch that data file/ table space status to online, and that should avoid the traces. 5. If the table space/ data file is set to offline for a reason, then the traces are expected and should be of no       concern. Note that you can also use below query to find offline data files. The point from above steps is to get the data file triggering the error:   SQL> connect / as sysdba SQL> column file_name format a40; SQL> column tablespace_name format a12; SQL> select file_name,file_id,tablespace_name,online_status from dba_data_files order by              tablespace_name, file_id;Even when the temp file is offline, ORA-01110 is seen in alert log or trace file. Run the sql to check temp files and online or drop it.SQL>select file_name, tablespace_name, status from dba_temp_files order by file_name;