[oracle] dba工作备忘录之三:rman备份,未使用catalog,控制文件丢失的解决办法 作者：fenng 日期：july 19 2004
情况描述客户报告数据库故障，新来的系统管理员误操作。删掉了一些文件。询问：删掉了那些文件？答曰：所有重要数据文件，所有控制文件。数据库原来是归档模式，用rman备份数据，rman 使用控制文件。幸运的是，最后一次rman full 备份是包括了控制文件在内。系统没有设定自动备份控制文件．现在状况是数据库无法启动．
不用说，客户的备份方案不够完善，但是这时候再去说这些话责备用户有事后诸葛亮之嫌，用户是上帝，不要去得罪他。还有，客户有full备份（虽然不是自动备份控制文件，这样无法用常规的恢复步骤来进行恢复）。这对我们来说是个绝对的好消息。
下面我们通过一次模拟操作来演示这个问题的解决办法。
解决过程首先，用控制文件作数据库系统的全备份：
代码:------------------------蓝色部分是输入内容，黑色部分是敏感信息，须加以注意----------------------------------------------------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 catalogallocated channel: c1channel c1: sid=15 devtype=disk
starting backup at 18-jul-04channel c1: starting full datafile backupsetchannel c1: specifying datafile(s) in backupsetincluding current spfile in backupsetincluding current controlfile in backupsetinput datafile fno=00001 name=d:/oracle/oradata/demo ystem01.dbfinput datafile fno=00002 name=d:/oracle/oradata/demo/undotbs01.dbfinput datafile fno=00004 name=d:/oracle/oradata/demo/example01.dbfinput datafile fno=00009 name=d:/oracle/oradata/demo/xdb01.dbfinput datafile fno=00005 name=d:/oracle/oradata/demo/indx01.dbfinput datafile fno=00008 name=d:/oracle/oradata/demo/users01.dbfinput datafile fno=00003 name=d:/oracle/oradata/demo/drsys01.dbfinput datafile fno=00006 name=d:/oracle/oradata/demo/odm01.dbfinput datafile fno=00007 name=d:/oracle/oradata/demo/tools01.dbfchannel c1: starting piece 1 at 18-jul-04channel c1: finished piece 1 at 18-jul-04piece handle=d:/kde/demo_01fr79ot_1_1.dbf comment=nonechannel c1: backup set complete, elapsed time: 00:01:17finished backup at 18-jul-04
sql statement: alter system archive log current
released channel: c1--如上所示，我们做了一次数据库的full备份．备份片中包括控制文件．注意上面输出内容的黑体部分．我们在后面的恢复操作中会用到． 模拟错误，关掉实例，删掉所有的控制文件和所有的.dbf文件。然后starup会看到如下的出错信息:
sql> startuporacle instance started.
total system global area 152115804 bytesfixed size 453212 bytesvariable size 100663296 bytesdatabase buffers 50331648 bytesredo buffers 667648 bytesora-00205: error in identifying controlfile, check alert log for more info
查看alert log,应该是系统找不到控制文件．现在情形和客户问题一致．不过在继续讲述之前，我们还需要介绍一点背景知识．
背景知识： 在oracle 816 以后的版本中,oracle提供了一个包:dbms_backup_restore.dbms_backup_restore 包是由dbmsbkrs.sql 和 prvtbkrs.plb 这两个脚本创建的.catproc.sql 脚本运行后会调用这两个包.所以是每个数据库都有的这个包是oracle服务器和操作系统之间io操作的接口.由恢复管理器直接调用。而且据说这两个脚本的功能是内建到oracle的一些库文件中的.
由此可见，我们可以在数据库 nomount 情况下调用这些package ,来达到我们的恢复目的。在dbmsbkrs.sql 和prvtbkrs.plb 这两个脚本中有详细的说明文档，出于篇幅问题，就不一一加以翻译了，但在下面会直接引用一些原文说明。
关键的内容有：
function deviceallocate( type in varchar2 default null ,name in varchar2 default null ,ident in varchar2 default null ,noio in boolean default false ,params in varchar2 default null )return varchar2;
-- describe the device to be used for sequential i/o. for device types where-- only one process at a time can use a device, this call allocates a device-- for exclusive use by this session. the device remains allocated until-- devicedeallocate is called or session termination. the device can be used-- both for creating and restoring backups.---- specifying a device allocates a context that exists until the session-- terminates or devicedeallocate is called. only one device can be specified-- at a time for a particular session. thus devicedeallocate must be called-- before a different device can be specified. this is not a limitation since-- a session can only read or write one backup at a time.---- the other major effect of allocating a device is to specify the name space-- for the backup handles (file names). the handle for a sequential file does-- not necessarily define the type of device used to write the file. thus it-- is necessary to specify the device type in order to interpret the file-- handle. the null device type is defined for all systems. it is the file-- system supplied by the operating system. the sequential file handles are-- thus normal file names.---- a device can be specified either by name or by type.-- if the type is specified but not the name, the system picks an-- available device of that type.-- if the name is specified but not the type, the type is determined-- from the device.-- if neither the type or the name is given, the backups are files in-- the operating system file system.
-- note that some types of devices, optical disks for example, can be shared-- by many processes, and thus do not really require allocation of the device-- itself. however we do need to allocate the context for accessing the-- device, and we do need to know the device type for proper interpretation-- of the file handle. thus it is always necessary to make the device-- allocation call before making most other calls in this package.---- input parameters:-- type-- if specified, this gives the type of device to use for sequential-- i/o. the allowed types are port specific. for example a port may-- support the type "tape" which is implemented via the oracle tape-- api. if no type is specified, it may be implied by specifying a-- particular device name to allocate. the type should be allowed to-- default to null if operating system files are to be used.---- name-- if specified, this names a particular piece of hardware to use for-- accessing sequential files. if not specified, any available-- device of the correct type will be allocated. if the device cannot-- be shared, it is allocated to this session for exclusive use.-- the name should be allowed to default to null if operating system-- files are to be used.---- ident-- this is the users identifier that he uses to name this device. it-- is only used to report the status of this session via-- dbms_application_info. this value will be placed in the client_info-- column of the v$session table, in the row corresponding to the-- session in which the device was allocated. this value can also-- be queried with the dbms_application_info.read_client_info procedure.---- noio-- if true, the device will not be used for doing any i/o. this allows-- the specification of a device type for deleting sequential files-- without actually allocating a piece of hardware. an allocation for-- noio can also be used for issuing device commands. note that some-- commands may actually require a physical device and thus will get-- an error if the allocate was done with noio set to true.---- params-- this string is simply passed to the device allocate osd. it is-- completely port and device specific.---- returns:-- it returns a valid device type. this is the type that should be-- allocated to access the same sequential files at a later date. note-- that this might not be exactly the same value as the input string.-- the allocate osd may do some translation of the type passed in. the-- return value is null when using operating system files.
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> declare2 devtype varchar2(256);3 done boolean;4 begin5 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:/oraclevolume in drive d is datavolume serial number is dc79-57f8directory of d:/oracle
07/18/2004 09:08 pm <dir> .07/18/2004 09:08 pm <dir> ..06/08/2004 03:21 pm <dir> admin07/18/2004 09:08 pm 1,871,872 control01.ctl 07/16/2004 11:27 am <dir> ora9207/18/2004 09:02 pm <dir> oradata这样，我们成功的restore了控制文件 .如果控制文件在full备份之后单独做的，接下来关掉实例，拷贝控制文件到具体位置，然后rman 执行restore database;即可。
可是，我们这里的情况有些不同．
视丢失文件的情况而定，继续进行如下的恢复操作：代码:--------------------------------------------------------------------------------sql> declare2 devtype varchar2(256);3 done boolean;4 begin5 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 ystem01.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 bytesfixed size 453212 bytesvariable size 100663296 bytesdatabase buffers 50331648 bytesredo buffers 667648 bytesdatabase mounted.sql> recover database using backup controlfile until cancel ;ora-00279: change 243854 generated at 07/18/2004 20:57:03 needed for thread 1ora-00289: suggestion : d:/kde/arc00002.001ora-00280: change 243854 for thread 1 is in sequence #2specify log: {<ret>=suggested | filename | auto | cancel}d:/kde/arc00002.001ora-00279: change 244089 generated at 07/18/2004 20:58:18 needed for thread 1ora-00289: suggestion : d:/kde/arc00003.001ora-00280: change 244089 for thread 1 is in sequence #3ora-00278: log file 'd:/kde/arc00002.001' no longer needed for this recoveryspecify log: {<ret>=suggested | filename | auto | cancel}cancelmedia 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.htmldba工作备忘录之二: exp出错的一个案例http://www.itpub.net/showthread.php?s=&threadid=238819dba工作备忘录之一:用events 跟踪解决不能创建物化试图一例 http://www.dbanotes.net/oracle/oracle-case-of-10046_i.htm原文出处<a href="http://www.dbanotes.net/oracle/rman_nocatalog_lost_controlfile_howto.htm">http://www.dbanotes.net/rman_nocatalog_lost_controlfile_howto.htm</a>
本文作者： fenng，现任某美资公司dba，业余时间混迹于各数据库相关的技术论坛。目前关注如何利用oracle数据库有效的构建企业应用。对oracle tuning、troubleshooting有一点研究。个人技术站点: http://www.dbanotes.net/ 。可以通过电子邮件 [email protected] 联系到他。 本文为dbanotes.net版权所有，转载请注明出处、作者并尽量保留本文所有超链接。
