转自DBA工作日记
DBMS_BACKUP_RESTORE用于特殊情况下的恢复 一般如下:系统崩溃,rman使用控制文件,没有使用控制文件自动备份,现在仅有最后一次全备(备份中包括控制文件),以及其增量备份,规档备份. 通常这种情况下不能使用常规RMAN来恢复，因为此全备份中备份的控制文件中没有包含本次的备份信息，rman使用控制文件备份的时候是先备份控制文件 后备份其它信息背景知识 在Oracle 816 以后的版本中,Oracle提供了一个包: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; PROCEDURE restoreControlfileTo(cfname IN varchar2); PROCEDURE restoreDataFileTo( dfnumber IN binary_integer ,toname IN varchar2 default NULL); SQL>startup force nomount; SQL> DECLARE devtype varchar2(256); done boolean; BEGIN --分配一个device channel，如果使用的操作系统文件，type就为空，如果是从磁带上恢复要用 "sbt_tape"； devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'t1'); --指明开始restore sys.dbms_backup_restore.restoreSetDatafile; --指出待恢复文件目标存储位置； sys.dbms_backup_restore.restoreControlfileTo(cfname=>'D:\ORACLE\ORADATA\FENET\CONTROL01.CTL'); --指定备份集的位置 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'D:\ORA9I6095222264.RMAN', params=>null); --释放通道 sys.dbms_backup_restore.deviceDeallocate; END; 可以通过该语句得到file#和name的对应关系 select 'sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>' || file# || ',toname=>' ||chr(39)|| name ||chr(39) || ');', 'sys.dbms_backup_restore.applySetDatafile(dfnumber=>' || file# || ',toname=>' ||chr(39)|| name ||chr(39) || ');' from v$datafile; 在nomount状态下执行以下语句 恢复0级备份的语句 DECLARE devtype varchar2(256); done boolean; BEGIN devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1'); sys.dbms_backup_restore.restoreSetDatafile; sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>1,toname=>'D:\ORACLE\ORADATA\FENET\SYSTEM01.DBF'); sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>2,toname=>'D:\ORACLE\ORADATA\FENET\UNDOTBS01.DBF'); sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>3,toname=>'D:\ORACLE\ORADATA\FENET\CWMLITE01.DBF'); sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>4,toname=>'D:\ORACLE\ORADATA\FENET\DRSYS01.DBF'); sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>5,toname=>'D:\ORACLE\ORADATA\FENET\EXAMPLE01.DBF'); sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>6,toname=>'D:\ORACLE\ORADATA\FENET\INDX01.DBF'); sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>7,toname=>'D:\ORACLE\ORADATA\FENET\ODM01.DBF'); sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>8,toname=>'D:\ORACLE\ORADATA\FENET\TOOLS01.DBF'); sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>9,toname=>'D:\ORACLE\ORADATA\FENET\USERS01.DBF'); sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>10,toname=>'D:\ORACLE\ORADATA\FENET\XDB01.DBF'); sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>11,toname=>'D:\ORACLE\ORADATA\FENET\BJIC.ORA'); sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>12,toname=>'D:\ORACLE\ORADATA\FENET\PM_USERS.DBF'); sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'D:\ORA9I6095222264.RMAN', params=>null); sys.dbms_backup_restore.deviceDeallocate; END; 恢复增量备份的语句 DECLARE devtype varchar2(256); done boolean; BEGIN devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1'); sys.dbms_backup_restore.applySetDatafile; sys.dbms_backup_restore.applySetDatafile(dfnumber=>1,toname=>'D:\ORACLE\ORADATA\FENET\SYSTEM01.DBF'); sys.dbms_backup_restore.applySetDatafile(dfnumber=>2,toname=>'D:\ORACLE\ORADATA\FENET\UNDOTBS01.DBF'); sys.dbms_backup_restore.applySetDatafile(dfnumber=>3,toname=>'D:\ORACLE\ORADATA\FENET\CWMLITE01.DBF'); sys.dbms_backup_restore.applySetDatafile(dfnumber=>4,toname=>'D:\ORACLE\ORADATA\FENET\DRSYS01.DBF'); sys.dbms_backup_restore.applySetDatafile(dfnumber=>5,toname=>'D:\ORACLE\ORADATA\FENET\EXAMPLE01.DBF'); sys.dbms_backup_restore.applySetDatafile(dfnumber=>6,toname=>'D:\ORACLE\ORADATA\FENET\INDX01.DBF'); sys.dbms_backup_restore.applySetDatafile(dfnumber=>7,toname=>'D:\ORACLE\ORADATA\FENET\ODM01.DBF'); sys.dbms_backup_restore.applySetDatafile(dfnumber=>8,toname=>'D:\ORACLE\ORADATA\FENET\TOOLS01.DBF'); sys.dbms_backup_restore.applySetDatafile(dfnumber=>9,toname=>'D:\ORACLE\ORADATA\FENET\USERS01.DBF'); sys.dbms_backup_restore.applySetDatafile(dfnumber=>10,toname=>'D:\ORACLE\ORADATA\FENET\XDB01.DBF'); sys.dbms_backup_restore.applySetDatafile(dfnumber=>11,toname=>'D:\ORACLE\ORADATA\FENET\BJIC.ORA'); sys.dbms_backup_restore.applySetDatafile(dfnumber=>12,toname=>'D:\ORACLE\ORADATA\FENET\PM_USERS.DBF'); sys.dbms_backup_restore.applyBackupPiece(done=>done,handle=>'D:\ORA9I6095222264.L1', params=>null); sys.dbms_backup_restore.deviceDeallocate END; 恢复归档日志archive log文件 SQL>DECLARE devtype varchar2(256); done boolean; BEGIN devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1'); sys.dbms_backup_restore.restoreSetArchivedLog; sys.dbms_backup_restore.restoreArchivedLogRange; sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'D:\ORA9I6095222264.arc',params=>null); sys.dbms_backup_restore.deviceDeallocate; END mount数据库 SQL> alter database mount; 恢复数据库到某一时间点 SQL> >recover database until time '2006-12-14 10:00:00'; 启动数据库 SQL> alter database open resetlogs;
How to extract controlfiles, datafiles, and archived logs from
SMR backupsets without using RMAN Introduction: When using RMAN to restore objects (datafiles, controlfiles, or archivelogs) from backupsets, the object restore can be driven from the recovery catalog or the target database controlfile. This note explains how to extract objects from backupsets when the recovery catalog and controlfiles have been lost. In this scenario, you effectively perform. the RMAN functions through PL/SQL procedure calls Contents: 1. Prerequisites 2. Extracting the controlfile from a backupset 3. Extracting datafiles from a backupset 4. Applying incrementals 5. Extracting archivelogs from a backupset 6. A typical scenario 7. Errors 8. Things to be done 1. Prerequisites The customer must have a knowledge of the contents of backupsets i.e. what they contain, when the backups were created, and the type of backups. Ideally they should have logs of the RMAN backup sessions that produced the backupsets. Note that the following anonymous PL/SQL blocks are run on the instance of the database being recovered (the 'target'). The instance must be at least started (once the controlfile has been restored the database can also be mounted). Anonymous blocks can be executed in this manner as long as they call only 'fixed' packages. The DBMS_BACKUP_RESTORE packages are fixed. IMPORTANT: All the anonymous blocks must be executed by SYS or a user who has execute privilege on SYS.DBMS_BACKUP_RESTORE 2. Extracting the controlfile from a backupset The first stage is to extract the controlfile from a backupset. This is achieved by making use of the following SYS.DBMS_BACKUP_RESTORE packaged functions & procedures: FUNCTION deviceAllocate - allocates a device for sequential I/O PROCEDURE restoreSetDataFile - begins a restore conversation PROCEDURE restoreControlfileTo - specifies the controlfile destination PROCEDURE restoreBackupPiece - performs the restore PROCEDURE deviceDeallocate - deallocates the I/O device The following anonymous block can be created and executed to restore a controlfile from a backupset. Before executing it, you MUST edit the block as follows: a. The filetable PL/SQL table entries must reflect the backuppieces comprising the backupset b. The v_maxPieces variable must reflect the number of backuppieces comprising the backupset c. The call to restoreControlfileTo must specify the correct controlfile path & filename IMPORTANT: The latest backup of the controlfile should be restored. Because recovery (using backup controlfile) will be performed manually, the recovering session will need to start applying redo from the current log sequence AT THE TIME OF THE CONTROLFILE BACKUP. Thus, to take advantage of incremental backups, restore a controlfile taken along with the incremental backups, thus reducing the amount of redo required during recovery.
3. Extracting datafiles from a backupset
The second stage is to extract the datafiles from a backupset. This is achieved by making use of the following SYS.DBMS_BACKUP_RESTORE packaged functions & procedures: FUNCTION deviceAllocate - allocates a device for sequential I/O PROCEDURE restoreSetDataFile - begins a restore conversation PROCEDURE restoreDataFileTo - datafile number & destination PROCEDURE restoreBackupPiece - performs the restore PROCEDURE deviceDeallocate - deallocates the I/O device The following anonymous block can be created and executed to restore a datafile from a backupset. Before executing it, you MUST edit the block as follows: a. The filetable PL/SQL table entries must reflect the backuppieces comprising the backupset b. The v_maxPieces variable must reflect the number of backuppieces comprising the backupset c. The call to restoreDataFileTo must specify the correct datafile number, and datafile path & filename
4. Applying incrementals
If incrementals are to be applied, you must execute this anonymous block for each incremental datafile backup. The following SYS.DBMS_BACKUP_RESTORE packaged functions & procedures are called: FUNCTION deviceAllocate - allocates a device for sequential I/O PROCEDURE applySetDataFile - begins a restore conversation PROCEDURE applyDataFileTo - datafile number & destination PROCEDURE applyBackupPiece - performs the restore PROCEDURE deviceDeallocate - deallocates the I/O device The following anonymous block can be created and executed to restore a datafile from a backupset. Before executing it, you MUST edit the block as follows: a. The filetable PL/SQL table entries must reflect the backuppieces comprising the backupset b. The v_maxPieces variable must reflect the number of backuppieces comprising the backupset c. The call to applyDataFileTo must specify the correct datafile number, and datafile path & filename
5. Extracting archivelogs from a backupset
The last restore stage is to extract the archivelogs from a backupset. This is achieved by making use of the following SYS.DBMS_BACKUP_RESTORE packaged functions & procedures: FUNCTION deviceAllocate - allocates a device for sequential I/O PROCEDURE restoreSetArchivedLog - begins a restore conversation PROCEDURE restoreArchivedLog - archivelog sequence & thread numbers PROCEDURE restoreBackupPiece - performs the restore PROCEDURE deviceDeallocate - deallocates the I/O device The following anonymous block can be created and executed to restore an archivelog from a backupset. Before executing it, you MUST edit the block as follows: a. The filetable PL/SQL table entries must reflect the backuppieces comprising the backupset b. The v_maxPieces variable must reflect the number of backuppieces comprising the backupset c. The call to restoreSetArchivedLog must specify the destination where the archivelog is to be restored. Ideally the destination string should be the same as init.ora:log_archive_dest d. The call to restoreArchivedLog must specify the log sequence number and thread number of the archivelog
For restoring multiple archives from a backupset, add a loop
around sys.dbms_backup_restore.restoreArchivedLog() for seq in .. loop sys.dbms_backup_restore.restoreArchivedLog(thread=>1, sequence=>seq); end loop 6. A typical scenario A customer has backupsets consisting of: o. an incremental level 0 database backup o. an incremental level 2 database backup o. archivelogs from the time of the level 2 backup to the current time The target database and recovery catalog have been irretrievably lost. In this situation, the following steps should be followed (using the above anonymous blocks): 1. Start the target instance (nomount) 2. Restore the latest controlfile, ideally from the same backupset as the last incremental to be restored (make further copies if necessary as per the init.ora) 3. Mount the database 4. Restore the datafiles from the level 0 backupset 5. Restore (apply) the datafiles from the level 2 backupset 6. Restore the archivelogs from the archivelog backupset 7. Using tradtional v7 recovery techniques, recover the database (until cancel using backup controlfile) 8. Open the database (resetlogs) 9. Rebuild the recovery catalog & re-register the target database 10. Make backups of the target database and recovery catalog database 8. Errors 8.1 ORA-19615 & ORA-19613 when attempting to extract files Errorstack: ORA-19583: conversation terminated due to error ORA-19615: some files not found in backup set ORA-19613: datafile not found in backup set ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 1043 ORA-06512: at line 40 The problem is that one or more backup pieces specified in the v_fileTable table contain NO blocks for the datafile that you are trying to extract. For example, I may have run an RMAN backup and allocated 2 channels to backup the (4 datafile) database. This will create 2 backupsets.
Although the backup pieces may contain blocks from all datafiles
associated with their backupset, they will not contain blocks from a different backupset i.e. pieces 1a and 1b will NOT contain blocks from datafiles 3 or 4. If I want to restore datafile 1, and include either backup pieces 1b or 2b in v_fileTable, I will get the errorstack above. This is why it is important to know what files are in what backupset. The original RMAN backup log will help here. 8. Things to be done 8.1. Error handling If the procedures fail with an unhandled exception (quite likely, as no exception handlers have been set up), the allocated device does not get deallocated. This is unfriendly (the user must exit & restart the session) and will be addressed |
