在很多异常恢复中,我们经常使用 dbms_backup_restore 包去提取备份集中的文件,最近有朋友问,是否可以输入多个备份片,执行自动恢复。
我们看一看这个包中指定备份片的存储过程部分:
PROCEDURE RESTOREBACKUPPIECE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
HANDLE VARCHAR2 IN
DONE BOOLEAN OUT
PARAMS VARCHAR2 IN DEFAULT
FROMDISK BOOLEAN IN DEFAULT
其中HANDLE是一个字符型的变量输入,一般来说我们给出的格式是:
sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/backupset/o1_mf_nnndf_TAG2012T225557_5w6f3xjm_.bkp', params=>null);
如何指定多个备份片?在MOS上参考如下一段代码,可以给我们一些帮助:
set serveroutput on size 1000000
DECLARE
v_dev varchar2(50);
v_done boolean:=FALSE;
type t_fileTable is table of varchar2(255) index by binary_integer;
v_fileTable t_fileTable;
type t_dataTable is table of varchar2(4000) index by binary_integer;
v_dataTable t_dataTable;
v_maxPieces number:=1;
v_maxFiles number:=1;
v_restore_from varchar2(4);
v_file_no number:=0;
v_file_name varchar2(513);
v_debug boolean:=FALSE; -- Optional Change to TRUE to Debug PL/SQL.
v_msr boolean;
BEGIN
-- CHANGE 1.
-- Populate the file table below with datafiles to be restored.
-- This matches the output from logs.
v_dataTable(1):='fno=1 name=/oracle/dbdump/VCEMV1/datafile1.dbf';
--
-- CHANGE 2.
-- Set the below to the max number of files to be restored from above table.
v_maxFiles := 1;
-- CHANGE 3.
-- Set the Below To the name of the backup pieces you wish to reference.
-- Use backup.log if available to locate all backup pieces for a backup.
-- If backup is on Disk ensure you have directories as well,
-- If tape then just use the backup piece name.
v_fileTable(1):='/oracle/dbdump/VCEMV1/dblevel0_VCEMV1_2355_1.rman';
v_fileTable(2):='/oracle/dbdump/VCEMV1/dblevel0_VCEMV1_2355_2.rman';
v_fileTable(3):='/oracle/dbdump/VCEMV1/dblevel0_VCEMV1_2355_3.rman';
v_fileTable(4):='/oracle/dbdump/VCEMV1/dblevel0_VCEMV1_2355_4.rman';
-- CHANGE 4.
-- Set the below to the total number of backup pieces in Backup to restore.
--
v_maxPieces:=4;
-- CHANGE 5. (Optional If Tape)
-- Set the below to TAPE if RESTORE FROM TAPE.
--
v_restore_from := 'DISK';
-- CHANGE 6 - change this to TRUE for multi-section backup
v_msr := TRUE;
IF (v_msr) THEN
v_maxFiles := 1;
END IF;
dbms_output.put_line('Restoring All Data Files :');
dbms_output.put_line('-----------------');
For i IN 1..v_maxFiles LOOP
v_file_no := substr(v_dataTable(i),5,instr(v_dataTable(i),' ',1,1)-5);
v_file_name :=substr(v_dataTable(i),instr(v_dataTable(i),'=',1,2)+1);
dbms_output.put_line('Attempting To Restore :'||v_file_name);
FOR i IN 1..v_maxPieces LOOP
BEGIN
IF v_restore_from = 'DISK' THEN
v_dev:=sys.dbms_backup_restore.deviceAllocate(type=>null,ident=>'D1');
ELSE
-- CHANGE 7. (Optional Tape Only).
-- Remember to set Params correctly for tape media.
-- If not required then remove parameter.
--
v_dev:=sys.dbms_backup_restore.deviceAllocate(type=>'sbt_tape',ident=>'T1',params=>'SBT_LIBRARY=oracle.disksbt,ENV=(BACKUP_DIR=/u003/backup/ORA1020/)');
END IF;
sys.dbms_backup_restore.restoreSetDatafile;
sys.dbms_backup_restore.restoreDataFileTo(dfnumber=>v_file_no,toname=>v_file_name);
IF (i = 1 AND v_msr) THEN
sys.dbms_backup_restore.initMSR(dfnumber=>v_file_no,fname=>v_file_name);
END IF;
dbms_output.put_line('----------------');
dbms_output.put_line('Trying : '||v_fileTable(i)||' From : '||v_restore_from);
sys.dbms_backup_restore.restoreBackupPiece(done=>v_done,handle=>v_fileTable(i),params=>null);
EXCEPTION
WHEN OTHERS THEN
IF v_debug THEN
dbms_output.put_line(SQLERRM);
ELSE
dbms_output.put_line('Data File: Not Found');
END IF;
sys.dbms_backup_restore.deviceDeallocate;
sys.dbms_backup_restore.restoreCancel;
END;
IF v_done THEN
dbms_output.put_line('Data File : Found');
dbms_output.put_line('----------------');
dbms_output.put_line('RESTORED Data File To: '||v_file_name);
sys.dbms_backup_restore.deviceDeallocate;
sys.dbms_backup_restore.restoreCancel;
v_done:=FALSE;
exit WHEN NOT v_msr;
END IF;
END LOOP;
IF (v_msr) THEN
sys.dbms_backup_restore.setParms(p0 => 5, p1 => v_file_no, p5 => v_file_name);
END IF;
END LOOP;
END;
/
供参考。