当前控制文件丢失，有老的控制文件备份，但老的控制文件备份之后有逻辑结构的变化，
比如新增了数据文件，这种情况的恢复方法，以下为模拟过程，注意红色部分
C:\Documents and Settings\olm>sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3月 13 16:56:56 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> spool d:\test.log
SQL> alter database backup controlfile to trace;
数据库已更改。
---------------------------------------------------------备份控制文件
SQL> create tablespace test datafile 'D:\3.oracle\app\oradata\test\test01.dbf' size 10m;
表空间已创建。
SQL> create table test (id number) tablespace test;
表已创建。
SQL> insert into test (id) values (1);
已创建 1 行。
SQL> commit;
提交完成。
SQL> col name format a50
SQL> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 D:\3.ORACLE\APP\ORADATA\TEST\SYSTEM01.DBF
2 D:\3.ORACLE\APP\ORADATA\TEST\UNDOTBS01.DBF
3 D:\3.ORACLE\APP\ORADATA\TEST\SYSAUX01.DBF
4 D:\3.ORACLE\APP\ORADATA\TEST\USERS01.DBF
5 D:\3.ORACLE\APP\ORADATA\TEST\TEST01.DBF
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
删除test01.dbf文件
C:\Documents and Settings\olm>sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3月 13 17:07:11 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
已连接到空闲例程。
SQL> startup nomount;
ORACLE 例程已经启动。
Total System Global Area 612368384 bytes
Fixed Size 1250452 bytes
Variable Size 192940908 bytes
Database Buffers 415236096 bytes
Redo Buffers 2940928 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'D:\3.ORACLE\APP\ORADATA\TEST\REDO01.LOG' SIZE 50M,
9 GROUP 2 'D:\3.ORACLE\APP\ORADATA\TEST\REDO02.LOG' SIZE 50M,
10 GROUP 3 'D:\3.ORACLE\APP\ORADATA\TEST\REDO03.LOG' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'D:\3.ORACLE\APP\ORADATA\TEST\SYSTEM01.DBF',
14 'D:\3.ORACLE\APP\ORADATA\TEST\UNDOTBS01.DBF',
15 'D:\3.ORACLE\APP\ORADATA\TEST\SYSAUX01.DBF',
16 'D:\3.ORACLE\APP\ORADATA\TEST\USERS01.DBF'
17 CHARACTER SET AL32UTF8
18 ;
控制文件已创建。
---------------------------------------------备份的控制文件mount数据库
SQL> col name for a50
SQL> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 D:\3.ORACLE\APP\ORADATA\TEST\SYSTEM01.DBF
2 D:\3.ORACLE\APP\ORADATA\TEST\UNDOTBS01.DBF
3 D:\3.ORACLE\APP\ORADATA\TEST\SYSAUX01.DBF
4 D:\3.ORACLE\APP\ORADATA\TEST\USERS01.DBF
SQL> recover database;
完成介质恢复。
SQL> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 D:\3.ORACLE\APP\ORADATA\TEST\SYSTEM01.DBF
2 D:\3.ORACLE\APP\ORADATA\TEST\UNDOTBS01.DBF
3 D:\3.ORACLE\APP\ORADATA\TEST\SYSAUX01.DBF
4 D:\3.ORACLE\APP\ORADATA\TEST\USERS01.DBF
SQL> alter database open;
数据库已更改。
SQL> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 D:\3.ORACLE\APP\ORADATA\TEST\SYSTEM01.DBF
2 D:\3.ORACLE\APP\ORADATA\TEST\UNDOTBS01.DBF
3 D:\3.ORACLE\APP\ORADATA\TEST\SYSAUX01.DBF
4 D:\3.ORACLE\APP\ORADATA\TEST\USERS01.DBF
5 D:\3.ORACLE\APP\PRODUCT\10.2.0\DATABASE\MISSING000
05
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- --------------
1 D:\3.ORACLE\APP\ORADATA\TEST\SYSTEM01.DBF SYSTEM
2 D:\3.ORACLE\APP\ORADATA\TEST\UNDOTBS01.DBF ONLINE
3 D:\3.ORACLE\APP\ORADATA\TEST\SYSAUX01.DBF ONLINE
4 D:\3.ORACLE\APP\ORADATA\TEST\USERS01.DBF ONLINE
5 D:\3.ORACLE\APP\PRODUCT\10.2.0\DATABASE\MISSING000 RECOVER
05
SQL> alter database rename file 'D:\3.ORACLE\APP\PRODUCT\10.2.0\DATABASE\MISSING00005' to 'D:\3.ORACLE\APP\ORADATA\TEST\test01.dbf';
数据库已更改。
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ------------------------------------------------------------ --------------
1 D:\3.ORACLE\APP\ORADATA\TEST\SYSTEM01.DBF SYSTEM
2 D:\3.ORACLE\APP\ORADATA\TEST\UNDOTBS01.DBF ONLINE
3 D:\3.ORACLE\APP\ORADATA\TEST\SYSAUX01.DBF ONLINE
4 D:\3.ORACLE\APP\ORADATA\TEST\USERS01.DBF ONLINE
5 D:\3.ORACLE\APP\ORADATA\TEST\TEST01.DBF RECOVER
SQL> recover datafile 5;
ORA-00279: 更改 593292 (在 03/13/2008 17:00:42 生成) 对于线程 1 是必需的
ORA-00289: 建议: D:\3.ORACLE\APP\PRODUCT\10.2.0\RDBMS\ARC00002_0649180773.001
ORA-00280: 更改 593292 (用于线程 1) 在序列 #2 中
指定日志: {=suggested | filename | AUTO | CANCEL}
cancel;
ORA-00308: 无法打开归档日志 'cancel;'
ORA-27041: 无法打开文件
OSD-04002: ？???？？?
O/S-Error: (OS 2) ？？？????？???？？???
指定日志: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: 更改 593293 (在 03/13/2008 17:06:54 生成) 对于线程 1 是必需的
ORA-00289: 建议: D:\3.ORACLE\APP\PRODUCT\10.2.0\RDBMS\ARC00003_0649180773.001
ORA-00280: 更改 593293 (用于线程 1) 在序列 #3 中
ORA-00278: 此恢复不再需要日志文件 'D:\3.ORACLE\APP\PRODUCT\10.2.0\RDBMS\ARC00002_0649180773.001'
已应用的日志。
完成介质恢复。
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ------------------------------------------------------------ --------------
1 D:\3.ORACLE\APP\ORADATA\TEST\SYSTEM01.DBF SYSTEM
2 D:\3.ORACLE\APP\ORADATA\TEST\UNDOTBS01.DBF ONLINE
3 D:\3.ORACLE\APP\ORADATA\TEST\SYSAUX01.DBF ONLINE
4 D:\3.ORACLE\APP\ORADATA\TEST\USERS01.DBF ONLINE
5 D:\3.ORACLE\APP\ORADATA\TEST\TEST01.DBF OFFLINE
SQL> alter database datafile 5 online;
数据库已更改。
SQL> select * from test;
ID
----------
1
