首页 / 数据库 / MySQL / ORA-01157&ORA-01110故障解决

今天在启动虚拟机测试库时，数据库报了ORA-01157和ORA-01110错误，提示找不到一个数据文件。我一看文件名就知道问题在哪儿了，是另一台虚拟机没有启动导致的，因为这个数据库通过dnfs创建了一个“远程”表空间。由于暂时不想启动那台虚拟机（节省内存），干脆就把这个表空间删除，熟悉一下trouble-shooting的过程。1.环境准备
我们在Oracle11g中进行测试。SQL>SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE 11.2.0.3.0 ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - ProductionSQL>2.启动数据库报错在启动数据库过程中，报了ORA-01157和ORA-01110错误，提示找不到数据文件。SYS@HOEGH>startup
 
ORACLE instance started.Total System Global Area 941600768 bytesFixed Size 1348860 bytesVariable Size 524290820 bytesDatabase Buffers 411041792 bytesRedo Buffers 4919296 bytesDatabase mounted.ORA-01157: cannot identify/lock data file 9 - see DBWR trace fileORA-01110: data file 9: "/u02/oradata/HOEGH/test_tbs01.dbf"SYS@HOEGH>select status from v$instance;STATUS------------MOUNTEDSYS@HOEGH>
由于另外一台虚拟机没有启动，数据库在启动过程中后台进程不能找到相应的数据文件或者不能锁定相应的数据文件，数据库将禁止访问这些数据文件而其他的数据文件则没有影响。ORA-01157错误一般和ORA-01110错误一起出现。 
3.重启数据库到mount状态，删除数据文件
启动数据库时，nomount状态读取参数文件，mount状态读取控制文件，在mount状态下可以删除数据文件。SYS@HOEGH>
 
SYS@HOEGH>startup nomountORACLE instance started.Total System Global Area 941600768 bytesFixed Size 1348860 bytesVariable Size 524290820 bytesDatabase Buffers 411041792 bytesRedo Buffers 4919296 bytesSYS@HOEGH>SYS@HOEGH>SYS@HOEGH>alter database mount;Database altered.SYS@HOEGH>alter database datafile "/u02/oradata/HOEGH/test_tbs01.dbf" offline drop;Database altered.
 
4.打开数据库，删除表空间
启动数据库到open状态，查找数据文件对应的表空间名称；然后，删除表空间。SYS@HOEGH>alter database open;
 Database altered.SYS@HOEGH>SYS@HOEGH>select file_id,tablespace_name from dba_data_files; FILE_ID TABLESPACE_NAME---------- ------------------------------       4 USERS       3 UNDOTBS1       2 SYSAUX       1 SYSTEM       5 TEST1       6 TEST2       7 TEST3       8 TEST       9 TEST_TBS9 rows selected.SYS@HOEGH>col file_name for a50SYS@HOEGH>col file_id for 99SYS@HOEGH>col tablespace_name for a10SYS@HOEGH>col status for a10SYS@HOEGH>col online_status for a20SYS@HOEGH>select file_name,file_id,TABLESPACE_NAME,STATUS,ONLINE_STATUS from dba_data_files;FILE_NAME FILE_ID TABLESPACE STATUS ONLINE_STATUS-------------------------------------------------- ------- ---------- ---------- --------------------/u01/app/oracle/oradata/HOEGH/users01.dbf 4 USERS AVAILABLE ONLINE/u01/app/oracle/oradata/HOEGH/undotbs01.dbf 3 UNDOTBS1 AVAILABLE ONLINE/u01/app/oracle/oradata/HOEGH/sysaux01.dbf 2 SYSAUX AVAILABLE ONLINE/u01/app/oracle/oradata/HOEGH/system01.dbf 1 SYSTEM AVAILABLE SYSTEM/u01/app/oracle/oradata/HOEGH/TEST101.dbf 5 TEST1 AVAILABLE ONLINE/u01/app/oracle/oradata/HOEGH/TEST201.dbf 6 TEST2 AVAILABLE ONLINE/u01/app/oracle/oradata/HOEGH/TEST301.dbf 7 TEST3 AVAILABLE ONLINE/u01/app/oracle/oradata/HOEGH/test.dbf 8 TEST AVAILABLE ONLINE/u02/oradata/HOEGH/test_tbs01.dbf 9 TEST_TBS AVAILABLE RECOVER9 rows selected.SYS@HOEGH>drop tablespace TEST_TBS including contents;Tablespace dropped.SYS@HOEGH>SYS@HOEGH>select file_name,file_id,TABLESPACE_NAME,STATUS,ONLINE_STATUS from dba_data_files;FILE_NAME FILE_ID TABLESPACE STATUS ONLINE_STATUS-------------------------------------------------- ------- ---------- ---------- --------------------/u01/app/oracle/oradata/HOEGH/users01.dbf 4 USERS AVAILABLE ONLINE/u01/app/oracle/oradata/HOEGH/undotbs01.dbf 3 UNDOTBS1 AVAILABLE ONLINE/u01/app/oracle/oradata/HOEGH/sysaux01.dbf 2 SYSAUX AVAILABLE ONLINE/u01/app/oracle/oradata/HOEGH/system01.dbf 1 SYSTEM AVAILABLE SYSTEM/u01/app/oracle/oradata/HOEGH/TEST101.dbf 5 TEST1 AVAILABLE ONLINE/u01/app/oracle/oradata/HOEGH/TEST201.dbf 6 TEST2 AVAILABLE ONLINE/u01/app/oracle/oradata/HOEGH/TEST301.dbf 7 TEST3 AVAILABLE ONLINE/u01/app/oracle/oradata/HOEGH/test.dbf 8 TEST AVAILABLE ONLINE8 rows selected.SYS@HOEGH>
 
5.重启数据库
在进行上述操作后，重启数据库，确保数据库能够正常打开。SYS@HOEGH>
 
SYS@HOEGH>shut immediateDatabase closed.Database dismounted.ORACLE instance shut down.SYS@HOEGH>SYS@HOEGH>SYS@HOEGH>startupORACLE instance started.Total System Global Area 941600768 bytesFixed Size 1348860 bytesVariable Size 511707908 bytesDatabase Buffers 423624704 bytesRedo Buffers 4919296 bytesDatabase mounted.Database opened.SYS@HOEGH>更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址