rman备份,未用catalog,控制文件丢失的解决(2)
2024-07-21 02:34:23
供稿：网友
PROCEDURE restoreControlfileTo(cfname IN varchar2); -- This copies the controlfile from the backup set to an Operating system
-- file. If the database is mounted, the name must NOT match any of the
-- current controlfiles.
--
-- Input parameters:
-- cfname
-- Name of file to create or overwrite with the controlfile from the
-- backup set.PROCEDURE restoreDataFileTo( dfnumber IN binary_integer
,toname IN varchar2 default NULL);
--
-- restoreDataFileTo creates the output file from a complete backup in the
-- backup set. 假如您有爱好可以去阅读一下这两个文件的注释说明．我们首先尝试恢复控制文件：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:/Oracle/Control01.ctl');
8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'D:/KDE/DEMO_01FR79OT_1_1.DBF', params=>null);
9 sys.dbms_backup_restore.deviceDeallocate;
10 END;
11 /PL/SQL procedure sUCcessfully completed.OK,控制文件恢复完成．对以上内容的解释: 第五行 分配一个device channel，因为使用的操作系统文件，所以这里为空，假如是从磁带上恢复要用
"sBT_tape"；
第六行 指明开始restore ；
第七行 指出待恢复文件目标存储位置；
第八行 从哪个备份片中恢复；
第九行 释放设备通道．不妨对以上操作的结果验证一下： SQL> host dir d:/oracle
Volume in drive D is DATA
Volume Serial Number is DC79-57F8
Directory of d:/oracle07/18/2004 09:08 PM <DIR> .
07/18/2004 09:08 PM <DIR> ..
06/08/2004 03:21 PM <DIR> admin
07/18/2004 09:08 PM 1,871,872 CONTROL01.CTL
07/16/2004 11:27 AM <DIR> ORA92
07/18/2004 09:02 PM <DIR> 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:/oracle/oradata/demo/SYSTEM01.DBF');
8 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'
d:/oracle/oradata/demo/UNDOTBS01.DBF');
9 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'
d:/oracle/oradata/demo/DRSYS01.DBF');
10 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'
d:/oracle/oradata/demo/EXAMPLE01.DBF');
11 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>'
d:/oracle/oradata/demo/INDX01.DBF');
12 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>06,toname=>'
d:/oracle/oradata/demo/ODM01.DBF');
13 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>07,toname=>'
d:/oracle/oradata/demo/TOOLS01.DBF');
14 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>08,toname=>'
d:/oracle/oradata/demo/USERS01.DBF');
15 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>09,toname=>'
d:/oracle/oradata/demo/XDB01.DBF');
16 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'
D:/KDE/DEMO_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:/KDE/ARC00002.001
ORA-00280: change 243854 for thread 1 is in sequence #2
Specify log: {<RET>=suggested filename AUTO CANCEL}D:/KDE/ARC00002.001
ORA-00279: change 244089 generated at 07/18/2004 20:58:18 needed for thread 1
ORA-00289: suggestion : D:/KDE/ARC00003.001
ORA-00280: change 244089 for thread 1 is in sequence #3
ORA-00278: log file 'D:/KDE/ARC00002.001' no longer needed for this recoverySpecify log: {<RET>=suggested filename AUTO CANCEL}
cancel
Media recovery cancelled.SQL> alter database open resetlogs;Database altered.最后，不得不resetlogs ． 然后，打扫战场，马上进行数据库的全备份。假如您是DBA的话，应该进一步制定并完善备份计划．亡羊补牢，为时未晚。
总结一下1 控制文件在备份中意义重大，建议每次对其单独备份，假如数据库版本答应的话，应该设置为控制文件自动备 份。同时应该尽可能地增大CONTROL_FILE_RECORD_KEEP_TIME这个初始化参数的值。以便备份信息能更长时间的保留2 应该制定比较完善的备份计划，否则备份计划一旦出现缺口，将可能给系统带来灾难．记住， ＂可能出错的地方一定会出错＂．3 熟悉RMAN内部备份机制，对DBMS_BACKUP_RESTORE的用法有一定的把握在要害时侯很有帮助．4 备份脚本应该对Log重定向并保存．以便在出错的查找有用信息．
参考资料：RMAN Recovery Without Recovery Catalog or Controlfiles by Bonnie Bizzarodbmsbkrs.sql 和 prvtbkrs.plb 文件说明注释(可在你的系统 $ORACLE_HOME/rdbms/admin/中找到．)
相关链接：
本文的更多讨论，请参考这里：
http://www.itpub.net/244345.Html
DBA工作备忘录之二: EXP出错的一个案例
http://www.itpub.net/showthread.php?s=&threadid=238819
DBA工作备忘录之一:用events 跟踪解决不能创建物化试图一例
http://www.dbanotes.nethttp://oracle.chinaitlab.com/Oracle-Case-of-10046_I.htm
原文出处
<a href="http://www.dbanotes.nethttp://oracle.chinaitlab.com/Rman_nocatalog_lost_controlfile_howto.htm">
http://www.dbanotes.net/Rman_nocatalog_lost_controlfile_howto.htm</a>
