Oracle数据文件丢失导致数据库启动故障解决一例
1.环境:
操作系统:WindowsXp
数据库:Oracle10g R2
2.故障现象和解决办法
在command下启动oracle实例
C:>net start oracleserviceorcl
The OracleServiceORCL service is starting........
The OracleServiceORCL service was started successfully.
然后在sqlplus中连接到oracle
C:>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 9 13:39:48 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
作操作试图显示dba_users的结构,但出现错误
SQL> desc dba_users;
ERROR:
ORA-04043: object all_users does not exist
这个很奇怪,我使用的user具有sysdba的权限,应该可以看dba_user的信息的。
先看看数据库的状态吧,发现状态是mounted的,不是open
SQL> select status from v$instance;
STATUS
------------
MOUNTED
那就open数据库吧,问题终于浮出水面了,要来是数据文件'd:/ccbqd/ccbqd_1.dbf'不正常,去硬盘上检查,发现
这个数据文件就是没有了,可能是不小心删除了。
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'D:/CCBQD/CCBQD_1.DBF'
既然没有了这个数据文件,就只能数据文件offline,才能打开了
SQL> alter database datafile 'd:/ccbqd/ccbqd_1.dbf' offline drop;
Database altered.
open数据库
SQL> alter database open;
Database altered.
然后再做操作,发现一切正常了。
SQL> desc dba_users;
Name Null? Type
----------------------------------------- -------- -------------------------
USERNAME NOT NULL VARCHAR2(30)
3.后记
如果,能再找到相应的数据文件,可以将数据文件移动到正确的物理位置,然后online,再open数据库即可。
具体操作如下。
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 591396864 bytes
Fixed Size 1250332 bytes
Variable Size 188746724 bytes
Database Buffers 398458880 bytes
Redo Buffers 2940928 bytes
Database mounted.
SQL> alter database datafile 'd:/ccbqd/ccbqd_1.dbf' online;
Database altered.
SQL> alter database open;
Database altered.