删除部分数据库控制文件后所进行的故障恢复:
环境：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>
