eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 恩墨科技 - 提供专业DBA猎头和人才推荐服务 | Blog首页 | 从 Oracle 18.3 的 DAX 存储支持看 Redo 优化 »

备份恢复:使用 dbms_backup_restore 如何指定多个备份片
modb.pro

在很多异常恢复中,我们经常使用 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;

/

供参考。


历史上的今天...
    >> 2012-07-16文章:
    >> 2011-07-16文章:
    >> 2008-07-16文章:
    >> 2007-07-16文章:
           摄影习作-百年之好合
           Tools:Linux下的SYSSTAT工具
    >> 2006-07-16文章:

By eygle on 2018-07-16 11:18 | Comments (0) | Backup&Recovery | 3288 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com