• 一个表空间offline后alert日志报ORA-01135 和ORA-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 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_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: 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 offline
    ORA-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_CHECK
    V$HM_CHECK_PARAM
    V$HM_FINDING
    V$HM_INFO
    V$HM_RECOMMENDATION
    V$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;
    


     

  • 相关阅读:
    php 通过curl获取远程数据,返回的是一个数组型的字符串,高手帮忙如何将这个数组类型的字符串变成数组。
    php中curl模拟post提交多维数组(转载)
    php://input
    win10的系统下怎么设置网页的字体变大
    PHP如何读取json数据
    Curl是什么,原文地址:http://www.phpchina.com/portal.php?mod=view&aid=40161
    百度地图应用封装
    仿百度糯米TP5项目笔记
    如何更改wampserver的网站根目录
    如何为form表单的button设置submit事件
  • 原文地址:https://www.cnblogs.com/slgkaifa/p/6761443.html
Copyright © 2020-2023  润新知