作者: Fenng | 可以转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明网址: http://www.dbanotes.net/database/rman_nocatalog_lost_controlfile_howto.html

解决过程

[@more@]

首先，用控制文件作数据库系统的全备份：

C:WUTemp>rman target /

Recovery Manager: Release 9.2.0.1.0 - Production.

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: DEMO (DBID=3272375326)

RMAN> run {

2> allocate channel C1 type disk;

3> backup full tag 'FullBackup' format 'd:KDE%d_%u_%s_%p.dbf' database include current controlfile;

4> sql ' alter system archive log current';

5> release channel C1;

6> }

using target database controlfile instead of recovery catalog

allocated channel: C1

channel C1: sid=15 devtype=DISK

Starting backup at 18-JUL-04

channel C1: starting full datafile backupset

channel C1: specifying datafile(s) in backupset

including current SPFILE in backupset

including current controlfile in backupset

input datafile fno=00001 name=D:ORACLEORADATADEMOSYSTEM01.DBF

input datafile fno=00002 name=D:ORACLEORADATADEMOUNDOTBS01.DBF

input datafile fno=00004 name=D:ORACLEORADATADEMOEXAMPLE01.DBF

input datafile fno=00009 name=D:ORACLEORADATADEMOXDB01.DBF

input datafile fno=00005 name=D:ORACLEORADATADEMOINDX01.DBF

input datafile fno=00008 name=D:ORACLEORADATADEMOUSERS01.DBF

input datafile fno=00003 name=D:ORACLEORADATADEMODRSYS01.DBF

input datafile fno=00006 name=D:ORACLEORADATADEMOODM01.DBF

input datafile fno=00007 name=D:ORACLEORADATADEMOTOOLS01.DBF

channel C1: starting piece 1 at 18-JUL-04

channel C1: finished piece 1 at 18-JUL-04

piece handle=D:KDEDEMO_01FR79OT_1_1.DBF comment=NONE

channel C1: backup set complete, elapsed time: 00:01:17

Finished backup at 18-JUL-04

sql statement: alter system archive log current

released channel: C1

如上所示，我们做了一次数据库的Full备份．备份片中包括控制文件．注意上面输出内容的黑体部分．我们在后面的恢复操作中会用到．

模拟错误，关掉实例，删掉所有的控制文件和所有的.DBF文件。然后starup会看到如下的出错信息:

SQL> startup

ORACLE instance started.

Total System Global Area 152115804 bytes

Fixed Size 453212 bytes

Variable Size 100663296 bytes

Database Buffers 50331648 bytes

Redo Buffers 667648 bytes

ORA-00205: error in identifying controlfile, check alert log for more info

查看alert Log,应该是系统找不到控制文件．现在情形和客户问题一致．不过在继续讲述之前，我们还需要介绍一点背景知识．

我们首先尝试恢复控制文件：

SQL>startup force nomount;

SQL> DECLARE

2 devtype varchar2(256);

3 done boolean;

4 BEGIN

5 devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');

6 sys.dbms_backup_restore.restoreSetDatafile;

7 sys.dbms_backup_restore.restoreControlfileTo (cfname=>'d:oracleControl01.ctl');

8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'D:KDEDEMO_01FR79OT_1_1.DBF', params=>null);

9 sys.dbms_backup_restore.deviceDeallocate;

10 END;

11 /

PL/SQL procedure successfully completed.

OK,控制文件恢复完成．对以上内容的解释:

不妨对以上操作的结果验证一下：

SQL> host dir d:oracle

Volume in drive D is DATA

Volume Serial Number is DC79-57F8

Directory of d:oracle

07/18/2004 09:08 PM  .

07/18/2004 09:08 PM  ..

06/08/2004 03:21 PM  admin

07/18/2004 09:08 PM 1,871,872 CONTROL01.CTL

07/16/2004 11:27 AM  ORA92

07/18/2004 09:02 PM  oradata

这样，我们成功的 restore 了控制文件 .如果控制文件在 Full 备份之后单独做的，接下来关掉实例，拷贝控制文件到具体位置，然后 rman 执行 restore database; 即可。

可是，我们这里的情况有些不同．

视丢失文件的情况而定，继续进行如下的恢复操作：

SQL> DECLARE

2 devtype varchar2(256);

3 done boolean;

4 BEGIN

5 devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');

6 sys.dbms_backup_restore.restoreSetDatafile;

7 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'d:oracleoradatademoSYSTEM01.DBF');

8 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'d:oracleoradatademoUNDOTBS01.DBF');

9 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'d:oracleoradatademoDRSYS01.DBF');

10 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'d:oracleoradatademoEXAMPLE01.DBF');

11 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>'d:oracleoradatademoINDX01.DBF');

12 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>06,toname=>'d:oracleoradatademoODM01.DBF');

13 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>07,toname=>'d:oracleoradatademoTOOLS01.DBF');

14 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>08,toname=>'d:oracleoradatademoUSERS01.DBF');

15 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>09,toname=>'d:oracleoradatademoXDB01.DBF');

16 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'D:KDEDEMO_01FR79OT_1_1.DBF',

params=>null);

17 sys.dbms_backup_restore.deviceDeallocate;

18 END;

19 /

PL/SQL procedure successfully completed.

--我们的情形是所有的数据文件都丢失了，那就如法炮制 ...........
--文件对应编号来自前面全备份时候的屏幕输出内容．所以，在备份的时候保留操作 Log 是个很好的习惯．

SQL> startup force mount;

ORACLE instance started.

Total System Global Area 152115804 bytes

Fixed Size 453212 bytes

Variable Size 100663296 bytes

Database Buffers 50331648 bytes

Redo Buffers 667648 bytes

Database mounted.

SQL> Recover database using backup controlfile until cancel ;

ORA-00279: change 243854 generated at 07/18/2004 20:57:03 needed for thread 1

ORA-00289: suggestion : D:KDEARC00002.001

ORA-00280: change 243854 for thread 1 is in sequence #2

Specify log: {=suggested | filename | AUTO | CANCEL}

D:KDEARC00002.001

ORA-00279: change 244089 generated at 07/18/2004 20:58:18 needed for thread 1

ORA-00289: suggestion : D:KDEARC00003.001

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

ORA-00278: log file 'D:KDEARC00002.001' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

CANCEL

Media recovery cancelled.

SQL> alter database open resetlogs;

Database altered.

最后，不得不resetlogs ．

然后，打扫战场，马上进行数据库的全备份。如果您是DBA的话，应该进一步制定并完善备份计划．亡羊补牢，为时未晚。

控制文件在备份中意义重大，建议每次对其单独备份，如果数据库版本允许的话，应该设置为控制文件自动备份。同时应该尽可能地增大CONTROL_FILE_RECORD_KEEP_TIME这个初始化参数的值。以便备份信息能更长时间的保留
应该制定比较完善的备份计划，否则备份计划一旦出现缺口，将可能给系统带来灾难．记住，＂可能出错的地方一定会出错＂．
熟悉RMAN内部备份机制，对DBMS_BACKUP_RESTORE的用法有一定的掌握在关键时侯很有帮助．
备份脚本应该对Log重定向并保存．以便在出错的查找有用信息．

RMAN Recovery Without Recovery Catalog or Controlfiles by Bonnie Bizzaro
dbmsbkrs.sql 和 prvtbkrs.plb 文件说明注释(可在你的系统 $ORACLE_HOME/rdbms/admin/中找到．)