以下情况是在redo没有问题的情况下，控制文件丢失之前数据库是归档和非常归档的情况下以下都适用
控制文件内容：RESETLOGS与NORESETLOGS都可以，但是最后都是必须以alter database open RESETLOGS打开
控制文件内容：ARCHIVELOG和NOARCHIVELOG都可以，表示恢复后的数据库归档模式，和控制文件丢失之前的归档模式没有关系
重建控制文件要注意的一点就是：因为控制文件中指定了所有的数据文件和online redo log的位置，所以该控制文件中的SCN大于最后一个归档日志的SCN（日志已经归档说明对应的online redo log不是current，则归档日志对应的online redo log已经经历了switch logfile，switch logfile是会写增量检查点的，增量检查点是记录在控制文件中的）

1.
sql>alter database backup controlfile to trace as '/home/oracle/ctl.control';

2.
编辑/home/ctl.control只留下如下内容
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "CRP" RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 5
MAXDATAFILES 512
MAXINSTANCES 8
MAXLOGHISTORY 223197
LOGFILE
GROUP 1 '/u01/CRP/db/apps_st/data/log01.dbf' SIZE 1024M BLOCKSIZE 512,
GROUP 2 '/u01/CRP/db/apps_st/data/log02.dbf' SIZE 1024M BLOCKSIZE 512,
GROUP 3 '/u01/CRP/db/apps_st/data/log03.dbf' SIZE 1024M BLOCKSIZE 512,
GROUP 4 '/u01/CRP/db/apps_st/data/log04.dbf' SIZE 1024M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/CRP/db/apps_st/data/system01.dbf',
'/u01/CRP/db/apps_st/data/sysaux01.dbf',
'/u01/CRP/db/apps_st/data/undotbs01.dbf',
'/u01/CRP/db/apps_st/data/users01.dbf'
CHARACTER SET AL32UTF8
;

3.
删除所有控制文件
sql>shutdown abort;
sql>@/home/oracle/ctl.control
以上命令会自动把数据库带入mount状态
这时可以查寻select * from v$log，但是current redo是不准的，以实际/u01/CRP/db/apps_st/data/下面哪个文件最新来判断哪个是current状态

4.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile;
ORA-00279: change 317794627 generated at 07/28/2017 21:01:57 needed for thread 1
ORA-00289: suggestion : /db/oracle/oradata/HYPRD/archivelog/1_1_950561799.dbf
ORA-00280: change 317794627 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/CRP/db/apps_st/data/log01.dbf --输入非current状态的redo会报错，但是不影响继续恢复，可以继续输入current redo
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: '/u01/CRP/db/apps_st/data/log01.dbf'

SQL> recover database using backup controlfile;
ORA-00279: change 317790921 generated at 07/28/2017 19:55:03 needed for thread 1
ORA-00289: suggestion : /db/oracle/oradata/HYPRD/archivelog/1_6_950558028.dbf
ORA-00280: change 317790921 for thread 1 is in sequence #6

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/CRP/db/apps_st/data/log02.dbf --输入current redo
Log applied.
Media recovery complete.

5
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open NORESETLOGS;
alter database open NORESETLOGS
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open

SQL> alter database open RESETLOGS;
Database altered.

6.
重建tempfile

备注：遇到过的duplicate备机无法顺序完成情况下，采用了重建控制文件的方式，必须如此(不存在redo有没有问题的概念)
4
rman>recover database;
或
sql>recover database using backup controlfile;

5
rman>alter database open resetlogs;
或
sql>alter database open resetlogs;--和上面4步骤的sql>匹配，步骤4使用rman>，则5步骤也只能用上面的rman>

ITPub博客

create controlfile恢复例子

最新文章