本文详细介绍了 Oracle 数据库启动时出现 ORA-01157 和 ORA-01110 错误的原因及解决方法。通过实例展示了如何定位问题数据文件并采取相应措施解决问题。
RAC + ASM单节点添加表空间的后果0
也许实际总要比想像更复杂，我们看一下以下测试。
首先在B节点创建ASM磁盘组：
SQL> create diskgroup testdg external redundancy disk '/dev/rdsk/c2t0d3s6';创建完成之后TESTDG磁盘组自动被装载。
Diskgroup created.
SQL> select group_number,name,state,total_mb from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TOTAL_MB
------------ ------------------------------ ----------- ----------
1 ORADG MOUNTED 16384
2 TESTDG MOUNTED 927
此时在节点A，TESTDG并未加载：
SQL> select group_number,name,state,total_mb from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TOTAL_MB
------------ ------------------------------ ----------- ----------
1 ORADG MOUNTED 16384
0 TESTDG DISMOUNTED 0
如果在此状态下，在B节点创建表空间及数据表：
SQL> create tablespace eygle datafile '+TESTDG' size 10M;
Tablespace created.
SQL> select group_number,name,state,total_mb from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TOTAL_MB
------------ ------------------------------ ----------- ----------
1 ORADG CONNECTED 16384
2 TESTDG CONNECTED 927
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+ORADG/eygle/datafile/system.256. 581100367
+ORADG/eygle/datafile/undotbs1.258. 581100367
+ORADG/eygle/datafile/sysaux.257. 581100367
+ORADG/eygle/datafile/users.259. 581100367
+ORADG/eygle/datafile/example.267. 581100507
+ORADG/eygle/datafile/undotbs2.268. 581100759
+TESTDG/eygle/datafile/eygle.256. 581636009
7 rows selected.
SQL> create table eygle tablespace eygle as select * from dba_users;
Table created.
SQL> select count(*) from eygle;
COUNT(*)
----------
27
此时在A节点查询会遇到如下错误：
SQL> select count(*) from eygle;
select count(*) from eygle
*
ERROR at line 1:
ORA- 01157: cannot identify/lock data file 7 - see DBWR trace file
ORA- 01110: data file 7: '+TESTDG/eygle/datafile/eygle.256. 581636009'
我们可以在ASM中挂接磁盘组：
SQL> alter diskgroup testdg mount;
Diskgroup altered.
但是你会发现在数据库中仍然无法访问数据表：
SQL> select count(*) from eygle;
select count(*) from eygle
*
ERROR at line 1:
ORA- 01157: cannot identify/lock data file 7 - see DBWR trace file
ORA- 01110: data file 7: '+TESTDG/eygle/datafile/eygle.256. 581636009'
告警日志文件记录了如下错误信息：
Errors in file /opt/oracle/admin/eygle/bdump/eygle1_dbw0_6467.trc:
ORA- 01157: cannot identify/lock data file 7 - see DBWR trace file
ORA- 01110: data file 7: '+TESTDG/eygle/datafile/eygle.256. 581636009'
ORA- 17503: ksfdopn:2 Failed to open file +TESTDG/eygle/datafile/eygle.256. 581636009
ORA- 15001: diskgroup "TESTDG" does not exist or is not mounted
ORA- 15001: diskgroup "TESTDG" does not exist or is not mounted
Sun Feb 5 21:33:32 2006
Errors in file /opt/oracle/admin/eygle/bdump/eygle1_dbw0_6467.trc:
ORA- 01186: file 7 failed verification tests
ORA- 01157: cannot identify/lock data file 7 - see DBWR trace file
ORA- 01110: data file 7: '+TESTDG/eygle/datafile/eygle.256. 581636009'
Sun Feb 5 21:33:32 2006
File 7 not verified due to error ORA- 01157
Sun Feb 5 21:33:32 2006
Errors in file /opt/oracle/admin/eygle/bdump/eygle1_dbw0_6467.trc:
ORA- 01157: cannot identify/lock data file 7 - see DBWR trace file
ORA- 01110: data file 7: '+TESTDG/eygle/datafile/eygle.256. 581636009'
ORA- 17503: ksfdopn:2 Failed to open file +TESTDG/eygle/datafile/eygle.256. 581636009
ORA- 15001: diskgroup "TESTDG" does not exist or is not mounted
ORA- 15001: diskgroup "TESTDG" does not exist or is not mounted
Sun Feb 5 21:33:32 2006
Errors in file /opt/oracle/admin/eygle/bdump/eygle1_dbw0_6467.trc:
ORA- 01186: file 7 failed verification tests
ORA- 01157: cannot identify/lock data file 7 - see DBWR trace file
ORA- 01110: data file 7: '+TESTDG/eygle/datafile/eygle.256. 581636009'
File 7 not verified due to error ORA- 01157
实际上也就是说DBWR无法锁定该文件，只能重启实例予以解决。
bash-2.05$ export ORACLE_SID=eygle1
bash-2.05$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 5 22:49:47 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters and Data Mining options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size 1978336 bytes
Variable Size 385880096 bytes
Database Buffers 855638016 bytes
Redo Buffers 14794752 bytes
Database mounted.
Database opened.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+ORADG/eygle/datafile/system.256. 581100367
+ORADG/eygle/datafile/undotbs1.258. 581100367
+ORADG/eygle/datafile/sysaux.257. 581100367
+ORADG/eygle/datafile/users.259. 581100367
+ORADG/eygle/datafile/example.267. 581100507
+ORADG/eygle/datafile/undotbs2.268. 581100759
+TESTDG/eygle/datafile/eygle.256. 581636009
7 rows selected.
SQL> select count(*) from eygle;
COUNT(*)
----------
27
所以我们在RAC环境下的种种操作一定要认真慎重，不能马虎草率，切记切记。
今天遇到oracle启动报ORA-01157和ORA-01110的错误
sql>startup mount;
sql>alter database open;
RA-01157: 无法标识/锁定数据文件 10 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 10: 'D:\ORACLE\ORADATA\ORCL\USERS02.DBF'
解决过程如下：
1. startup mount
2. alter database open
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 12: '/oradata/orcldata/sacproduce02.dbf'
3. cd /oradata
4. cd orcldata
报错信息为不能进行读写操作，哈哈。找到原因了，挂载的硬盘不能进行读写操作了。要打开数据库，只能将该文件进行离线操作了。
5. archive log list
归档模式下
alter database datafile '/oradata/orcldata/sacproduce02.dbf' offline
非归档模式下
alter database datafile '/oradata/orcldata/sacproduce02.dbf'offline drop
6. 到此，数据库就可以open了。但是文件中还有需要的数据，接下来就是挂载硬盘，online数据文件,open数据库。
alter database datafile '/oradata/orcldata/sacproduce02.dbf' online;
ORA-01113: file 12 needs media recovery
ORA-01110: data file 12: '/oradata/orcldata/sacproduce02.dbf'
recover datafile '/oradata/orcldata/sacproduce02.dbf'
alter database datafile '/oradata/orcldata/sacproduce02.dbf' online;
alter database open;
OK,问题解决^_^
SQL> select * from dba_temp_files;
select * from dba_temp_files
ORA-01157: 无法标识/锁定数据文件 201 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 201: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB\TEMP01.DBF'
我的数据文件放在d:\oracle\product\10.1.0\oradata\db\下,我想起这个临时文件是直接拿其他数据库的备份过来,于是我想重命名他看下行不行.
SQL> alter database rename file 'c:\oracle\product\10.1.0\oradata\db\temp01.dbf' to 'd:\oracle\product\10.1.0\oradata\db\temp01.dbf';
alter database rename file 'c:\oracle\product\10.1.0\oradata\db\temp01.dbf' to 'd:\oracle\product\10.1.0\oradata\db\temp01.dbf'
ORA-01511: 重命名日志/数据文件时出错
ORA-01516: 不存在的日志文件, 数据文件或临时文件 "c:\oracle\product\10.1.0\oradata\db\temp01.dbf"
想删了临时表时间,可下面的命令不行,后来才知道直接drop 掉就行了,drop tablespace temp,竟然不用带temporary,郁闷．
SQL> alter temporary tablespace temp offline drop;
alter temporary tablespace temp offline drop
ORA-00940: 无效的 ALTER 命令
只好再建一个临时表空间了．
SQL> create temporary tablespace temp02 tempfile 'd:\oracle\product\10.1.0\oradata\db\temp02.dbf' size 170M;
Tablespace created
SQL> alter database default temporary tablespace temp02;
Database altered
再把出问题的旧的临时表空间删了，
SQL> drop tablespace temp;
Tablespace dropped
SQL> select * from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- -----------
D:\ORACLE\PRODUCT\10.1.0\ORADATA\DB\TEMP02.DBF 2 TEMP02
OK,没问题了．
最近在使用swingbench的时候碰到了ORA-01157故障，下面是其具体描述与解决。
- 1、故障现象
- --查询视图dba_data_files时出现ORA-01157故障
- SQL> select file_name,tablespace_name from dba_data_files where tablespace_name='SOE';
- select file_name,tablespace_name from dba_data_files where tablespace_name='SOE'
- *
- ERROR at line 1:
- ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
- ORA-01110: data file 6: '/u01/oracle/db/dbs/soe.dbf'
- --尝试drop tablespace 收到同样的错误
- SQL> drop tablespace soe including contents and datafiles;
- drop tablespace soe including contents and datafiles
- *
- ERROR at line 1:
- ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
- ORA-01110: data file 6: '/u01/oracle/db/dbs/soe.dbf'
- 2、分析
- --从错误号后的文字可判断DBWR不能识别或锁定文件号6，后面的ORA-01110给出了具体的文件位置
- --下面是错误号对应的具体描述
- SQL> ho oerr ora 01157
- 01157, 00000, "cannot identify/lock data file %s - see DBWR trace file"
- // *Cause: The background process was either unable to find one of the data
- // files or failed to lock it because the file was already in use.
- // The database will prohibit access to this file but other files will
- // be unaffected. However the first instance to open the database will
- // need to access all online data files. Accompanying error from the
- // operating system describes why the file could not be identified.
- // *Action: Have operating system make file available to database. Then either
- // open the database or do ALTER SYSTEM CHECK DATAFILES.
- --上面的描述指出了后台进程不能寻找到数据文件或者是因为文件在被其他进程使用而DBWR无法对其锁定。
- --对于象这类文件数据库将禁止对其进行访问，而其他数据文件则不受影响。
- --给出的决办法是确认错误号后的数据文件是否存在或可用，以及在open状态下执行ALTER SYSTEM CHECK DATAFILES命令
- 3、解决
- --尝试执行alter system check datafiles
- SQL> alter system check datafiles;
- System altered.
- --执行后故障依旧如下
- SQL> select file_name,tablespace_name from dba_data_files where tablespace_name='SOE';
- select file_name,tablespace_name from dba_data_files where tablespace_name='SOE'
- *
- ERROR at line 1:
- ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
- ORA-01110: data file 6: '/u01/oracle/db/dbs/soe.dbf'
- --查看数据字典记录的信息表明当前的表空间为online状态
- SQL> select tablespace_name,status,contents from dba_tablespaces where tablespace_name='SOE';
- TABLESPACE_NAME STATUS CONTENTS
- ------------------------------ --------- ---------
- SOE ONLINE PERMANENT
- --查看alert日志文件，也给出了该错误提示，提示给出了tarce文件
- oracle@v2048db01p:/u01/oracle/admin/SYISDB/bdump> tail -8 alert_SYISDB1.log
- Additional information: 3
- Tue Nov 13 09:43:17 2012
- Errors in file /u01/oracle/admin/SYISDB/bdump/syisdb1_dbw0_5925.trc:
- ORA-01186: file 6 failed verification tests
- ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
- ORA-01110: data file 6: '/u01/oracle/db/dbs/soe.dbf'
- Tue Nov 13 09:43:17 2012
- File 6 not verified due to error ORA-01157
- --查看表空间soe对应的数据文件是否存在
- oracle@v2048db01p:~> export ORACLE_SID=+ASM1
- oracle@v2048db01p:~> asmcmd
- ASMCMD> cd +DG2/SYISDB/DATAFILE
- ASMCMD> ls
- CCDATA.289.799174049
- SYSAUX.260.796819341
- SYSTEM.259.796819335
- UNDOTBS1.261.796819339
- UNDOTBS2.257.796819343
- USERS.256.796819343
- X.290.799234531
- ccdata.dbf
- ASMCMD> ls *soe* --#没有任何含soe的数据文件
- asmcmd: entry '*soe*' does not exist in directory '+DG2/SYISDB/DATAFILE/'
- ASMCMD> ls *SOE* --#没有任何含soe的数据文件，由此可知表空间soe对应的数据文件已经丢失
- asmcmd: entry '*SOE*' does not exist in directory '+DG2/SYISDB/DATAFILE/'
- --因此直接删除该表空间及数据文件，注，生产环境不建议此操作
- SQL> alter database datafile 6 offline drop;
- Database altered.
- --再次查看数据字典信息，依然处于Online状态
- --Author : Robinson
- --Blog : http://blog.youkuaiyun.com/robinson_0612
- SQL> select tablespace_name,status,contents from dba_tablespaces where tablespace_name='SOE';
- TABLESPACE_NAME STATUS CONTENTS
- ------------------------------ --------- ---------
- SOE ONLINE PERMANENT
- --下面的查询貌似也有问题，对应的数据文件在上一命令中已经清除了，而此时依旧显示AVAILABLE
- SQL> col file_name format a55
- SQL> set linesize 160
- SQL> select file_name,tablespace_name,status from dba_data_files where tablespace_name='SOE';
- FILE_NAME TABLESPACE_NAME STATUS
- ---------------------------------------- ------------------------------ ---------
- /u01/oracle/db/dbs/soe.dbf SOE AVAILABLE
- --尝试在该表空间创建对象，收到了ORA-01658错误
- SQL> create table t tablespace soe as select * from dba_objects;
- create table t tablespace soe as select * from dba_objects
- *
- ERROR at line 1:
- ORA-01658: unable to create INITIAL extent for segment in tablespace SOE
- --查看对应的错误信息
- --错误信息表明没有足够的连续空间分配初始extent.
- SQL> ho oerr ora 01658
- 01658, 00000, "unable to create INITIAL extent for segment in tablespace %s"
- // *Cause: Failed to find sufficient contiguous space to allocate INITIAL
- // extent for segment being created.
- // *Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the
- // tablespace or retry with a smaller value for INITIAL
- --再次查看状态，发现此时对应的数据文件为RECOVER
- SQL> col file_name format a40
- SQL> select file_name,tablespace_name,status,ONLINE_STATUS from dba_data_files where tablespace_name='SOE';
- FILE_NAME TABLESPACE_NAME STATUS ONLINE_
- ---------------------------------------- ------------------------------ --------- -------
- /u01/oracle/db/dbs/soe.dbf SOE AVAILABLE RECOVER
- --查看v$recover_file视图，给出文件未找到OFFLINE FILE NOT FOUND
- SQL> select * from v$recover_file;
- FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
- ---------- ------- ------- ----------------------------------------------------------------- ---------- ------------------
- 6 OFFLINE OFFLINE FILE NOT FOUND 0
- --查看对应的数据文件也不存在
- SQL> ho ls -hltr /u01/oracle/db/dbs/soe.dbf
- ls: /u01/oracle/db/dbs/soe.dbf: No such file or directory
- --删除整个表空间及数据文件
- SQL> drop tablespace soe including contents and datafiles;
- Tablespace dropped.
- --下面的查询表示表空间soe已经被彻底清除
- SQL> select * from v$recover_file;
- no rows selected
- SQL> select file_name,tablespace_name,status,ONLINE_STATUS from dba_data_files where tablespace_name='SOE';
- no rows selected
总结：
ORA-01157通常由后台进程DBWR锁定而产生。
如果在恢复期间，如数据库已经mount，而一个或多个数据文件不能打开导致数据库不能open时会出现该提示。
数据文件丢失，数据文件的许可问题，如数据文件oracle用户没有写权限等都会产生ORA-01157。
如果open状态的情形下，ORA-01157未列出的数据文件不会受到影响。
补充说明：
细心的朋友应该可能已经发现当时在检查对应的数据文件的时候，只检查了ASM磁盘是否存在对应的数据文件。
由于出错数据库为RAC，因此忽略了检查提示中的文件系统对应的数据文件。说来还是不够仔细，狂汗......
就其原因应该是这样，在使用swingbench时，创建soe表空间时直接一路next，导致将数据文件创建到了文件系统，而文件系统是非共享的。(RAC环境)
更多参考：
有关Oracle 网络配置相关基础以及概念性的问题请参考：
配置ORACLE 客户端连接到数据库
配置非默认端口的动态服务注册
配置sqlnet.ora限制IP访问Oracle
Oracle 监听器日志配置与管理
设置 Oracle 监听器密码(LISTENER)
Oracle RAC 监听配置
ORACLE RAC 下非缺省端口监听配置(listener.ora tnsnames.ora)
Oracle RAC 客户端连接负载均衡(Load Balance)
有关基于用户管理的备份和备份恢复的概念请参考
Oracle 基于备份控制文件的恢复(unsing backup controlfile)
有关RMAN的备份恢复与管理请参考
有关ORACLE体系结构请参考
扫一扫
844
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
