oracle恢复分片和非分片备份？

分片备份命令参考：适合大数据库并行备份提高备份速度

对于超大数据库，混合有小文件和大文件表空间，section size 表示分片，大小一般大于32G，可结合通道数量设置最佳值。

run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
backup section size 32768m as backupset incremental level=0 tablespace DATA,USERS,JYC_BIG,JYC_BIG2 format '/bak/jycdb0_%U' tag=jycdb0;
release channel t1;
release channel t2;
release channel t3;
release channel t4;
}

非分片的恢复：可以多个数据文件一次性恢复

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=4 name=/home/db/oracle/oradata/hisdb/users01.dbf';
v_dataTable(2):='fno=9 name=/home/db/oracle/oradata/hisdb/data03.dbf';
v_dataTable(3):='fno=7 name=/home/db/oracle/oradata/hisdb/data01.dbf';
v_dataTable(4):='fno=8 name=/home/db/oracle/oradata/hisdb/data02.dbf';
v_dataTable(5):='fno=5 name=/home/db/oracle/oradata/hisdb/users02.dbf';
v_dataTable(6):='fno=10 name=/home/db/oracle/oradata/hisdb/data04.dbf';
v_dataTable(7):='fno=6 name=/home/db/oracle/oradata/hisdb/users03.dbf';

--
-- CHANGE 2.
-- Set the below to the max number of files to be restored from above table.

v_maxFiles := 7;

-- 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):='/bak/hisdb0_1b2eh346_1_1';
v_fileTable(2):='/bak/hisdb0_172eh345_1_1';
v_fileTable(3):='/bak/hisdb0_162eh345_1_1';
v_fileTable(4):='/bak/hisdb0_152eh345_1_1';


-- 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;
v_msr := FALSE;
IF (v_msr) THEN
v_maxFiles := 8;
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;
/

分片的恢复：v_maxFiles只能是1,也就是说只能恢复一个数据文件的分片恢复，不能同时多个数据文件同时恢复分片。

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=4 name=/home/db/oracle/oradata/hisdb/users01.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):='/bak/hisdb0_1o2em1lf_2_1';
v_fileTable(2):='/bak/hisdb0_1o2em1lf_1_1';

-- CHANGE 4.
-- Set the below to the total number of backup pieces in Backup to restore.
--

v_maxPieces:=2;

-- 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;
--v_msr := FALSE;
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||'|'||v_file_no);

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);
dbms_output.put_line('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);
dbms_output.put_line('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;
/