方法一：recover database using backup controlfile until cancel恢复。


SQL> startup

ORACLE instance started.

Total System Global Area167772160 bytes

Fixed Size2019320 bytes

Variable Size117440520 bytes

Database Buffers46137344 bytes

Redo Buffers2174976 bytes

Database mounted.

ORA-01122: database file 1 failed verification check

ORA-01110: data file 1: '/orac/oradata/jason/system01.dbf'

ORA-01207: file is more recent than control file - old control file

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area167772160 bytes

Fixed Size2019320 bytes

Variable Size117440520 bytes

Database Buffers46137344 bytes

Redo Buffers2174976 bytes

Database mounted.

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 867108 generated at 07/21/2011 05:49:08 needed for thread 1

ORA-00289: suggestion : /orac/archive/1_3_757050028.dbf

ORA-00280: change 867108 for thread 1 is in sequence #3

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: cannot open archived log '/orac/archive/1_3_757050028.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00308: cannot open archived log '/orac/archive/1_3_757050028.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/orac/oradata/jason/system01.dbf'

这种恢复方式，不会自动去找在线redo日志文件，所以必须要手工指定！

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 867108 generated at 07/21/2011 05:49:08 needed for thread 1

ORA-00289: suggestion : /orac/archive/1_3_757050028.dbf

ORA-00280: change 867108 for thread 1 is in sequence #3

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/orac/oradata/jason/redo01.log

ORA-00328: archived log ends at change 866593, need later change 867108

ORA-00334: archived log: '/orac/oradata/jason/redo01.log'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/orac/oradata/jason/system01.dbf'

不是需要的在线日志文件，继续执行。

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 867108 generated at 07/21/2011 05:49:08 needed for thread 1

ORA-00289: suggestion : /orac/archive/1_3_757050028.dbf

ORA-00280: change 867108 for thread 1 is in sequence #3

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/orac/oradata/jason/redo02.log

ORA-00328: archived log ends at change 846266, need later change 867108

ORA-00334: archived log: '/orac/oradata/jason/redo02.log'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/orac/oradata/jason/system01.dbf'

不是需要的在线日志文件，继续执行。

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 867108 generated at 07/21/2011 05:49:08 needed for thread 1

ORA-00289: suggestion : /orac/archive/1_3_757050028.dbf

ORA-00280: change 867108 for thread 1 is in sequence #3

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/orac/oradata/jason/redo03.log

Log applied.

Media recovery complete.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

恢复成功后需要使用resetlogs方式打开数据库，这个时候需要对库进行一次全部的备份。

SQL> alter database open resetlogs;

Database altered.

SQL>

方法二：重新创建控制文件。

SQL> startup

ORACLE instance started.

Total System Global Area167772160 bytes

Fixed Size2019320 bytes

Variable Size 134217736 bytes

Database Buffers29360128 bytes

Redo Buffers2174976 bytes

Database mounted.

ORA-01122: database file 1 failed verification check

ORA-01110: data file 1: '/orac/oradata/jason/system01.dbf'

ORA-01207: file is more recent than control file - old control file

生成控制文件的trace文件（nomount状态不可以）

SQL> alter database backup controlfile to trace as '/orac/controlfile.txt';

Database altered.

SQL>

查看此文件，修改此文件为以下内容：

[oracle@jason orac]$ vi controlfile.sql

#STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "JASON" NORESETLOGSARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/orac/oradata/jason/redo01.log'SIZE 50M,

GROUP 2 '/orac/oradata/jason/redo02.log'SIZE 50M,

GROUP 3 '/orac/oradata/jason/redo03.log'SIZE 50M

-- STANDBY LOGFILE

DATAFILE

'/orac/oradata/jason/system01.dbf',

'/orac/oradata/jason/undotbs01.dbf',

'/orac/oradata/jason/sysaux01.dbf',

'/orac/oradata/jason/users01.dbf',

'/orac/oradata/jason/jason01.dbf',

'/orac/oradata/jason/cat.dbf',

'/orac/oradata/jason/girl.dbf',

'/orac/oradata/jason/boy.dbf',

'/orac/oradata/jason/stone.dbf',

'/orac/oradata/jason/soft.dbf',

'/orac/oradata/jason/dog.dbf',

'/orac/oradata/jason/stone01.dbf'

CHARACTER SET ZHS16CGB231280

;

将原数据库启动到nomount状态执行脚本，执行之前删除旧的控制文件。

SQL> @/orac/controlfile.sql;

Control file created.

注意1：控制文件创建成功之后，数据库自动被挂载。可以在alter日志中看到。

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/orac/oradata/jason/system01.dbf'

由于系统突然断电等原因，所以在重启以后数据库从redo file中恢复数据进行前滚。

SQL> RECOVER DATABASE;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/orac/oradata/jason/temp01.dbf' REUSE;


Note:

执行创建控制文件脚本的时候报如下错误：

SQL> @/orac/controlfile.sql

SP2-0042: unknown command "DATAFILE" - rest of line ignored.

SP2-0734: unknown command beginning "'/orac/ora..." - rest of line ignored.

SP2-0734: unknown command beginning "'/orac/ora..." - rest of line ignored.

SP2-0734: unknown command beginning "'/orac/ora..." - rest of line ignored.

SP2-0044: For a list of known commands enter HELP

and to leave enter EXIT.

SP2-0734: unknown command beginning "'/orac/ora..." - rest of line ignored.

SP2-0734: unknown command beginning "'/orac/ora..." - rest of line ignored.

SP2-0734: unknown command beginning "'/orac/ora..." - rest of line ignored.

SP2-0734: unknown command beginning "'/orac/ora..." - rest of line ignored.

SP2-0044: For a list of known commands enter HELP

and to leave enter EXIT.

SP2-0734: unknown command beginning "'/orac/ora..." - rest of line ignored.

SP2-0734: unknown command beginning "'/orac/ora..." - rest of line ignored.

SP2-0734: unknown command beginning "'/orac/ora..." - rest of line ignored.

SP2-0734: unknown command beginning "'/orac/ora..." - rest of line ignored.

SP2-0044: For a list of known commands enter HELP

and to leave enter EXIT.

SP2-0734: unknown command beginning "'/orac/ora..." - rest of line ignored.

SP2-0734: unknown command beginning "CHARACTER ..." - rest of line ignored.

1CREATE CONTROLFILE REUSE DATABASE "JASON" NORESETLOGSARCHIVELOG

2MAXLOGFILES 16

3MAXLOGMEMBERS 3

4MAXDATAFILES 100

5MAXINSTANCES 8

6MAXLOGHISTORY 292

7LOGFILE

8GROUP 1 '/orac/oradata/jason/redo01.log'SIZE 50M,

9GROUP 2 '/orac/oradata/jason/redo02.log'SIZE 50M,

10GROUP 3 '/orac/oradata/jason/redo03.log'SIZE 50M

11* -- STANDBY LOGFILE、


只需要执行如下语句可以解决

SQL> set sqlbl on

关于sqlbl参数解释如下（来自官方），是因为我们的SQL脚本中存在空白行，因此在执行脚本时会报错。

SET SQLBL[ANKLINES] {ON | OFF}

SET SQLBLANKLINES is not supported in iSQL*Plus

Controls whether SQL*Plus puts blank lines within a SQL command or script. ON interprets blank lines and new lines as part of a SQL command or script. OFF, the default value, does not allow blank lines or new lines in a SQL command or script or script.

Enter the BLOCKTERMINATOR to stop SQL command entry without running the SQL command. Enter the SQLTERMINATOR character to stop SQL command entry and run the SQL statement

注意2:

二者的区别再于：

备份的控制文件不能自动进行完全恢复，可以手工apply日志进行完全恢复。


重新创建的可以自动进行完全恢复。


进一步的理解是前者只能使用resetlogs选项打开数据库，因为oracle认为using backup controlfile用的是不完全恢复.当使用了备份的控制文件或创建控制文件时使用了resetlogs选项时应使用using backup controlfile来进行恢复，并且最后用resetlogs打开


再引入biti的一段话就是：
对于恢复来说，如果数据库正常关闭，在控制文件中存在着一个最大的checkpoint number and  SCN(其实这里就是end scn了) 则在做恢复的时候根据数据库文件自动恢复到该控制文件记录的SCN,所以对于备份的控制文件而言，如果自动恢复，则到该SCN处不再继续恢复了，认为恢复已经完毕。这时如果控制文件是和数据文件一个时间点冷备份的话，都不会进行恢复，会提示你无须恢复，直接就可以打开数据库

而数据库如若是异常关闭（abort  /掉电　／　）或者重建控制文件，则控制文件中记录SCN认为是无穷大（正常关闭的时候会修改为当前数据库SCN）。这样恢复的时候要尝试恢复到该SCN则需要应用到所有的日志包括联机日志使用完毕,而如果是备份的控制文件，虽然不能自动进行完全恢复，但在using backup  controlfile 下，因为是可以手工　apply 日志，则你可以一个一个的apply 到　联机日志完毕，这样也可以实现完全恢复。这段文件转载：http://space.itpub.net/12361284/viewspace-168290。