isitdevops.com/databases

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;

/