A table space offline after alert log ORA-01135 and ORA-01110 issues

This article is original article, reprint please indicate source: http://blog.csdn.net/msdnchina/article/details/44336789

Recently encountered a case, is to offline a tablespace after the alert log constantly error, the initial error was found in 11.2.0.2. Later I experimented on 11.2.0.4 linux x86-64 bit and found the same phenomenon.
Here are my lab steps:

[[email protected] ~]$ 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> 

From the alert log, see:

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'

View the contents of the/u01/app/oracle/diag/rdbms/test/test/trace/test_m000_3250.trc file as follows:

[[email protected] trace]$ cat /u01/app/oracle/diag/rdbms/test/test/trace/test_m000_3250.trc
Trace file /u01/app/oracle/diag/rdbms/test/test/trace/test_m000_3250.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u02/app/oracle/product/11.2.0.4/db_1
System name:    Linux
Node name:      rhel63single
Release:        2.6.32-279.el6.x86_64
Version:        #1 SMP Wed Jun 13 18:24:36 EDT 2012
Machine:        x86_64
VM name:        VMWare Version: 6
Instance name: test
Redo thread mounted by this instance: 1
Oracle process number: 22
Unix process pid: 3250, image: [email protected] (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 offline
ORA-01110: data file 12: '/u01/app/oracle/oradata/test/test.dbf'
[[email protected] trace]$ 

Later, I searched for a bit in MOS:
Getting Trace Files with Db_structure_integrity_check and an ORA-01110. (Document ID 1252614.1)
This article is consistent with this phenomenon.

The reasons for this article are:

These files is 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
This HCM (Health Check Monitor) is available from 11.1.

The following is from: Checker Run Found xx New persistent Data failures. (Document 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.

Expand: Views related to health check (in fact, synonym) are:

V$HM_CHECK
V$HM_CHECK_PARAM
V$HM_FINDING
V$HM_INFO
V$HM_RECOMMENDATION
V$HM_RUN

The query results of the above view can be used in customer DB Patrol.

Solution See below:

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;

A table space offline after alert log ORA-01135 and ORA-01110 issues