讲解删除部分控制文件后的数据库启动

环境：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>