環境：Windows xp，Oracle9i 9.2.0

具體示例如下：

C:/Documents and Settings/w>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.1.0 - PRoduction on Mon Mar 20 10:08:10 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

ORA-00205: error in identifying controlfile, check alert log for more info

alert_orcl.log文件發現錯誤如下：

ORA-00202: controlfile: 'd:/oracle/oradata/orcl/CONTROL01.CTL'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) 系統找不到指定的文件。

解決方法1：將CONTROL03.CTL復制2份，分別將其改名為CONTROL01.CTL和CONTROL02.CTL，重新啟動后問題解決。

解決方法2：首先查看d:/oracle/oradata/orcl/目錄

如果發現只有CONTROL03.CTL，其它兩個都沒有，需要修改spfileorcl.ora。

如果系統下pfile文件和spfile都在D:/oracle/ora92/database/(分別對應INITorcl.ora和SPFILEORCL.ORA,修改INITorcl.ora文件control_files參數)

通過create pfile from spfile;然后將修改control_files=("d:/oracle/oradata/orcl/CONTROL01.CTL", "d:/oracle/oradata/orcl/CONTROL02.CTL", "d:/oracle/oradata/orcl/CONTROL03.CTL")

為control_files=("d:/oracle/oradata/orcl/CONTROL03.CTL")

然后

create spfile from pfile;

SQL> startup

ORACLE instance started.

Total System Global Area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

Database mounted.

Database opened.

SQL>

至此，成功解決此問題。

解決方法3：重新創建控制文件，首先刪除剩余的控制文件

SQL> create controlfile database orcl logfile

2 group 1('d:/oracle/oradata/orcl/redo01.log') size 100m,

3 group 2('d:/oracle/oradata/orcl/redo02.log') size 100m

4 noresetlogs

5 datafile

6 'd:/oracle/oradata/orcl/CWMLITE01.DBF',

7 'd:/oracle/oradata/orcl/EXAMPLE01.DBF',

8 'd:/oracle/oradata/orcl/INDX01.DBF',

9 'd:/oracle/oradata/orcl/ODM01.DBF',

10 'd:/oracle/oradata/orcl/OEM_REPOSITORY.DBF',

11 'd:/oracle/oradata/orcl/PERFSTAT.DBF',

12 'd:/oracle/oradata/orcl/TTAPPS01.DBF',

13 'd:/oracle/oradata/orcl/UNDOTBS01.DBF',

14 'd:/oracle/oradata/orcl/USERS01.DBF',

15 'd:/oracle/oradata/orcl/XDB01.DBF',

16 'd:/oracle/oradata/orcl/SYSTEM01.DBF',

17 'd:/oracle/oradata/orcl/tools01.DBF'

18 maxloghistory 2000 maxdatafiles 2000 maxlogmembers 5 character set ZHS16GBK

;

Control file created.

啟動數據庫:

SQL> startup

ORACLE instance started.

Total System Global Area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: 'D:/ORACLE/ORADATA/ORCL/SYSTEM01.DBF'

然后對數據庫進行介質恢復:

SQL> recover database;

Media recovery complete.

打開數據庫:

SQL> alter database open;

Database altered.

最后查看測試數據：

SQL> select * from scott.test;

T

----------

1

2

3

SQL>