在Oracle 816 以后的版本中,Oracle提供了一个包: DBMS_BACKUP_RESTORE.DBMS_BACKUP_RESTORE,由dbmsbkrs.sql 和 prvtbkrs.plb 这两个脚本创建,
catproc.sql 脚本运行后会调用这两个包。该包是Oracle服务器和操作系统之间IO操作的接口,由恢复管理器直接调用。
在catalog的情况下,采用RMAN备份,如果丢失了控制文件,可以直接用RMAN恢复:
SQL>startup nomount;
ORACLE instance started.
Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL>
c:\>rman target xxx catalog xxx
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: zhs16gbk (not mounted)
connected to recovery catalog database
RMAN>restore controlfile from 'C:\SOFTWARE\ORACLE\ORA92\DATABASE\C-165830944-20070412-01';
在nocatalog的情况下,采用RMAN备份,因为控制文件中包含了rman的备份信息,所以如果丢失了控制文件,不能用平常的方法来恢复。我们可以利用DBMS_BACKUP_RESTORE的包来恢复。该包在nomount下即可运行,也就是说,只需要启动到nomount下就可以利用它来恢复控制文件或者数据文件以及归档日志。
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);
1. 从备份中恢复控制文件
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=>'C:\SOFTWARE\ORACLE\ORADATA\MYDATA\CONTROL01.CTL');
--SYS.DBMS_BACKUP_RESTORE.RestoreControlFileTo(cfname=>'C:\SOFTWARE\ORACLE\ORADATA\MYDATA\CONTROL02.CTL');
--SYS.DBMS_BACKUP_RESTORE.RestoreControlFileTo(cfname=>'C:\SOFTWARE\ORACLE\ORADATA\MYDATA\CONTROL03.CTL');
--指定备份集的位置
SYS.DBMS_BACKUP_RESTORE.restoreBackupPiece(done=>done,handle=>'D:\DBBAK\CK_52_1_623326577', 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.restoreSetDatafile;
SYS.DBMS_BACKUP_RESTORE.RestoreSPFileTo(sfname=>'D:\CONTROL01.CTL');
SYS.DBMS_BACKUP_RESTORE.restoreBackupPiece(done=>done,handle=>'h:\rmanbackup\CNTR_20080401_2699_1_650869237', params=>null);
SYS.DBMS_BACKUP_RESTORE.DeviceDeAllocate;
END;
2. 从备份中恢复数据文件(数据文件可以从v$datafile中获得)
A. 恢复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=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\SYSTEM01.DBF');
SYS.DBMS_BACKUP_RESTORE.RestoreDataFileTo(dfnumber=>2,toname=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\UNDOTBS01.DBF');
SYS.DBMS_BACKUP_RESTORE.RestoreDataFileTo(dfnumber=>3,toname=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\INDX01.DBF');
SYS.DBMS_BACKUP_RESTORE.RestoreDataFileTo(dfnumber=>4,toname=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\TOOLS01.DBF');
SYS.DBMS_BACKUP_RESTORE.RestoreDataFileTo(dfnumber=>5,toname=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\USERS01.DBF');
SYS.DBMS_BACKUP_RESTORE.restoreBackupPiece(done=>done,handle=>'d:\dbbak\BK_1_1_623326719', params=>null);
SYS.DBMS_BACKUP_RESTORE.DeviceDeAllocate;
END;
B. 恢复增量备份中的数据文件
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=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\SYSTEM01.DBF');
SYS.DBMS_BACKUP_RESTORE.ApplySetDataFile(dfnumber=>2,toname=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\UNDOTBS01.DBF');
SYS.DBMS_BACKUP_RESTORE.ApplySetDataFile(dfnumber=>3,toname=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\INDX01.DBF');
SYS.DBMS_BACKUP_RESTORE.ApplySetDataFile(dfnumber=>4,toname=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\TOOLS01.DBF');
SYS.DBMS_BACKUP_RESTORE.ApplySetDataFile(dfnumber=>5,toname=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\USERS01.DBF');
SYS.DBMS_BACKUP_RESTORE.applyBackupPiece(done=>done,handle=>'D:\DBBAK\BK_1_1_623326719', params=>null);
SYS.DBMS_BACKUP_RESTORE.DeviceDeAllocate;
END;
(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; )
3. 从备份中恢复归档日志
DECLARE
devtype VARCHAR2(256);
done BOOLEAN;
begin
devtype:=SYS.DBMS_BACKUP_RESTORE.DeviceAllocate(type=>'',ident=>'t1');
SYS.DBMS_BACKUP_RESTORE.restoreSetArchivedLog('c:\software\oracle\oradata\zhs16gbk\archive');
SYS.DBMS_BACKUP_RESTORE.restoreArchivedLog(thread=>1,sequence=>42);
SYS.DBMS_BACKUP_RESTORE.restoreArchivedLog(thread=>1,sequence=>43);
SYS.DBMS_BACKUP_RESTORE.restoreBackupPiece(done=>done,handle=>'d:\dbbak\AL_3_1_623326755',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.restoreSetArchivedLog('c:\software\oracle\oradata\zhs16gbk\archive');
SYS.DBMS_BACKUP_RESTORE.restoreArchivedLog(thread=>1,sequence=>44);
SYS.DBMS_BACKUP_RESTORE.restoreArchivedLog(thread=>1,sequence=>45);
SYS.DBMS_BACKUP_RESTORE.restoreBackupPiece(done=>done,handle=>'d:\dbbak\AL_4_1_623326982',params=>null);
SYS.DBMS_BACKUP_RESTORE.DeviceDeAllocate;
end;
OR (以下是将这两个备份文件中归档日志全部恢复出来)
DECLARE
devtype VARCHAR2(256);
done BOOLEAN;
BEGIN
devtype:=SYS.DBMS_BACKUP_RESTORE.DeviceAllocate(type=>'',ident=>'T1');
SYS.DBMS_BACKUP_RESTORE.restoreSetArchivedLog('c:\software\oracle\oradata\zhs16gbk\archive');
SYS.DBMS_BACKUP_RESTORE.restoreArchivedLogRange;
SYS.DBMS_BACKUP_RESTORE.restoreBackupPiece(done=>done,handle=>'d:\dbbak\AL_3_1_623326755',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.restoreSetArchivedLog('c:\software\oracle\oradata\zhs16gbk\archive');
SYS.DBMS_BACKUP_RESTORE.restoreArchivedLogRange;
SYS.DBMS_BACKUP_RESTORE.restoreBackupPiece(done=>done,handle=>'d:\dbbak\AL_4_1_623326982',params=>null);
SYS.DBMS_BACKUP_RESTORE.DeviceDeAllocate;
END;