一个表空间offline后alert日志报ORA-01135 和ORA-01110的问题

本文通过实验展示了在Oracle 11g中将表空间设置为离线后,系统如何持续生成错误日志,并提供了相应的解决办法。通过设置特定事件追踪错误来源,最终确定离线的数据文件。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

本文是原创文章,转载请注明出处:http://blog.youkuaiyun.com/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;


 

 

<think>嗯,用户遇到了Oracle数据库的ORA-01157ORA-01110错误,涉及数据文件9无法被识别或锁定。首先,我需要回忆一下这两个错误的常见原因解决方法。根据之前的引用内容,这些错误通常与数据文件的状态、路径问题或权限问题有关。 首先,我应该确认数据文件的状态。用户提到的数据文件9是USERS002.DBF,路径是D:\ORACLE19C\ORADATA\TEACHING\。可能需要检查该文件是否存在,或者路径是否正确。有时候文件可能被误删或移动,导致数据库无法访问。 然后,用户可能需要检查数据库的模式。如果数据库处于打开状态,可能需要先将其挂载(mount)而不是打开(open),以便进行恢复操作。这时候需要执行alter database datafile 9 offline或者alter database datafile 9 offline drop,具体取决于是否启用了归档模式。 接下来,数据文件的恢复步骤,可能需要使用RMAN进行恢复,或者从备份中还原文件。如果文件损坏,可能需要介质恢复。另外,权限问题也是常见原因,特别是Oracle进程是否有权限访问该数据文件所在的目录文件本身。 另外,引用中提到查看DBWR跟踪文件,这可能有助于找到更具体的错误原因。用户需要检查alert日志跟踪文件,以确定具体的问题所在,比如I/O错误或者文件系统权限问题。 还要考虑存储路径是否有效,有时候路径中的拼写错误或者磁盘问题会导致文件无法访问。此外,如果是在Data Guard环境中,可能需要特殊处理,比如引用3中提到的Data Guard相关解决方法。 总结可能的解决步骤:检查文件存在性路径,确认数据库状态,进行offline操作,恢复数据文件,处理权限问题,检查存储健康,最后打开数据库。同时提醒用户操作前备份,避免进一步的数据丢失。 用户可能需要逐步执行这些步骤,如果某个步骤失败,再根据错误信息调整解决方案。比如,如果offline失败,可能需要更进一步的诊断。此外,确保用户有最近的备份,以防万一需要从备份恢复。</think>针对Oracle数据库的ORA-01157ORA-01110错误,以下是分步解决方案: ### 错误描述 - **ORA-01157**: 无法标识/锁定数据文件9 - **ORA-01110**: 数据文件9: `D:\ORACLE19C\ORADATA\TEACHING\USERS002.DBF` ### 解决步骤 1. **验证数据文件状态** ```sql SELECT FILE#, NAME, STATUS FROM V$DATAFILE WHERE FILE# = 9; ``` 若显示`OFFLINE`或`RECOVER`,需进一步恢复操作[^1]。 2. **检查文件系统路径** - 确认文件`D:\ORACLE19C\ORADATA\TEACHING\USERS002.DBF`是否存在 - 验证Oracle进程(ORACLE_SERVICE/SID)是否有读写权限 - 检查存储设备是否挂载正常(适用于Linux/Windows共享存储) 3. **强制离线数据文件** ```sql ALTER DATABASE DATAFILE 9 OFFLINE; --错则尝试 ALTER DATABASE DATAFILE 9 OFFLINE DROP; -- 非归档模式下使用 ``` 该操作允许绕过损坏文件打开数据库[^2]。 4. **执行数据文件恢复** ```sql RECOVER DATAFILE 9; -- 若需完整恢复 ALTER DATABASE RECOVER DATAFILE 9; ``` 若文件已物理丢失,需从备份恢复: ```rman RMAN> RESTORE DATAFILE 9; RMAN> RECOVER DATAFILE 9; ``` 5. **重新联机数据文件** ```sql ALTER DATABASE DATAFILE 9 ONLINE; ``` 6. **验证数据库状态** ```sql ALTER SYSTEM CHECK DATAFILES; ``` ### 特殊场景处理 - **Windows权限问题**:右键文件属性→安全→添加Oracle运行账户完全控制权限 - **存储路径异常**:若文件路径变更,需重建控制文件 - **Data Guard环境**:主备库文件路径需保持一致,同步参数文件设置[^3] ### 注意事项 - 操作前必须备份当前数据库(包括控制文件) - 若文件系统损坏,需先修复存储设备 - 归档模式下建议使用`RECOVER`而非`OFFLINE DROP`
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值