控制文件的通常恢复方法有：
1、从控制文件自动或二进制备份中恢复

2、从控制文件快照备份中恢复

3、控制文件trace文件备份中恢复

4、Oracle自动生成的控制文件脚本恢复

如下对相关的步骤及语法进行介绍：

3、控制文件trace文件备份中恢复

对数据库进行一些改动并备份控制文件的trace备份：

SQL> insert into t select * from dba_users;

8 rows created.

SQL> alter database backup controlfile to trace as '/opt/oracle/backups/control.txt';

Database altered.

SQL> insert into t select * from dba_users;

8 rows created.

SQL> alter system switch logfile;

System altered.

SQL>

[@more@]

在另外一个窗口中将控制文件通过OS的mv 或rm命令进行移动或删除；

[oracle@dbserv test]$ mv control01.ctl control02.ctl control03.ctl /opt/oracle/backups/

[oracle@dbserv test]$ ls

redo01.log redo02.log redo03.log sqlnet.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf

关闭并重新启动数据库

SQL> shutdown immediate;

ORA-01097: cannot shutdown while in a transaction - commit or rollback first

SQL> commit;

Commit complete.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size 1220432 bytes

Variable Size 486539440 bytes

Database Buffers 1644167168 bytes

Redo Buffers 15556608 bytes

ORA-00205: ?????????, ??????, ???????

将生成的控制文件trace文本备份中获取相关的恢复语句：

[oracle@xjtvpay ~]$ cd /opt/oracle/backups/

[oracle@xjtvpay backups]$ ls

control01.ctl control02.ctl control03.ctl control.txt

[oracle@xjtvpay backups]$ more control.txt

省略注视内容...

-- Set #1. NORESETLOGS case

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/opt/oracle/oradata/test/redo01.log' SIZE 50M,

GROUP 2 '/opt/oracle/oradata/test/redo02.log' SIZE 50M,

GROUP 3 '/opt/oracle/oradata/test/redo03.log' SIZE 50M

DATAFILE

'/opt/oracle/oradata/test/system01.dbf',

'/opt/oracle/oradata/test/undotbs01.dbf',

'/opt/oracle/oradata/test/sysaux01.dbf',

'/opt/oracle/oradata/test/users01.dbf'

CHARACTER SET ZHS16GBK

;

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');

RECOVER DATABASE

ALTER SYSTEM ARCHIVE LOG ALL;

ALTER DATABASE OPEN;

ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/test/temp01.dbf'

SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

-- Set #2. RESETLOGS case

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "TEST" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/opt/oracle/oradata/test/redo01.log' SIZE 50M,

GROUP 2 '/opt/oracle/oradata/test/redo02.log' SIZE 50M,

GROUP 3 '/opt/oracle/oradata/test/redo03.log' SIZE 50M

DATAFILE

'/opt/oracle/oradata/test/system01.dbf',

'/opt/oracle/oradata/test/undotbs01.dbf',

'/opt/oracle/oradata/test/sysaux01.dbf',

'/opt/oracle/oradata/test/users01.dbf'

CHARACTER SET ZHS16GBK

;

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');

RECOVER DATABASE USING BACKUP CONTROLFILE

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/test/temp01.dbf'

SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

根据以上内容介绍，将其中的部分复制到文本文件后，在SQL PLUS行调用并执行即可。因我们的日志文件都健全，因此执行第一个NORESETLOGS case对应的内容：

[oracle@xjtvpay backups]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jun 15 21:38:23 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> @creatconf.sql;

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size 1220432 bytes

Variable Size 486539440 bytes

Database Buffers 1644167168 bytes

Redo Buffers 15556608 bytes

Control file created.

PL/SQL procedure successfully completed.

ORA-00283: recovery session canceled due to errors

ORA-00264: no recovery required

System altered.

Database altered.

Tablespace altered.

SQL> select count(*) from t;

COUNT(*)

----------

32

SQL>