ORA-01157&ORA-01110故障解决

大连云鹏

2015-07-17

今天在启动虚拟机测试库时，数据库报了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 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL>

2.启动数据库报错

在启动数据库过程中，报了ORA-01157和ORA-01110错误，提示找不到数据文件。

SYS@HOEGH>startup

ORACLE instance started.

Total System Global Area 941600768 bytes

Fixed Size 1348860 bytes

Variable Size 524290820 bytes

Database Buffers 411041792 bytes

Redo Buffers 4919296 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 9 - see DBWR trace file

ORA-01110: data file 9: \'/u02/oradata/HOEGH/test_tbs01.dbf\'

SYS@HOEGH>select status from v$instance;

STATUS

------------

MOUNTED

SYS@HOEGH>
由于另外一台虚拟机没有启动，数据库在启动过程中后台进程不能找到相应的数据文件或者不能锁定相应的数据文件，数据库将禁止访问这些数据文件而其他的数据文件则没有影响。ORA-01157错误一般和ORA-01110错误一起出现。

3.重启数据库到mount状态，删除数据文件
启动数据库时，nomount状态读取参数文件，mount状态读取控制文件，在mount状态下可以删除数据文件。

SYS@HOEGH>

SYS@HOEGH>startup nomount

ORACLE instance started.

Total System Global Area 941600768 bytes

Fixed Size 1348860 bytes

Variable Size 524290820 bytes

Database Buffers 411041792 bytes

Redo Buffers 4919296 bytes

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_TBS

9 rows selected.

SYS@HOEGH>col file_name for a50

SYS@HOEGH>col file_id for 99

SYS@HOEGH>col tablespace_name for a10

SYS@HOEGH>col status for a10

SYS@HOEGH>col online_status for a20

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 ONLINE

/u02/oradata/HOEGH/test_tbs01.dbf 9 TEST_TBS AVAILABLE RECOVER

9 rows selected.

SYS@HOEGH>drop tablespace TEST_TBS including contents;

Tablespace dropped.