利用dbms_backup_restore包手工進行資料庫恢複

情景描述：
資料運行在歸檔模式下，用RMAN備份資料，除最後一次RMAN FULL全備份外（全備份包含控制檔案備份，但不是Controlfile Autobackup），所有資料檔案、控制檔案全部丟失，資料庫無法啟動。
恢複情況：
dbms_backup_restore包是Oracle伺服器和作業系統之間IO操作介面，正常情況下有RMAN直接調用進行資料恢複。由此可見我們可以在作業系統級直接調用這個包來進行資料恢複。
在次用到的procedure介紹，具體詳細情況可以參考$oracle_home/rdbms/admin/dbmsbkrs.sql和prvtbkrs.plb指令碼注釋說明
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;

-- Describe the device to be used for sequential I/O. For device types where
-- only one process at a time can use a device, this call allocates a device
-- for exclusive use by this session. The device remains allocated until
-- deviceDeallocate is called or session termination. The device can be used
-- both for creating and restoring backups.
--
-- Specifying a device allocates a context that exists until the session
-- terminates or deviceDeallocate is called. Only one device can be specified
-- at a time for a particular session. Thus deviceDeallocate must be called
-- before a different device can be specified. This is not a limitation since
-- a session can only read or write one backup at a time.
--
-- The other major effect of allocating a device is to specify the name space
-- for the backup handles (file names). The handle for a sequential file does
-- not necessarily define the type of device used to write the file. Thus it
-- is necessary to specify the device type in order to interpret the file
-- handle. The NULL device type is defined for all systems. It is the file
-- system supplied by the operating system. The sequential file handles are
-- thus normal file names.
--
-- A device can be specified either by name or by type.
-- If the type is specified but not the name, the system picks an
-- available device of that type.
-- If the name is specified but not the type, the type is determined
-- from the device.
-- If neither the type or the name is given, the backups are files in
-- the operating system file system.

-- Note that some types of devices, optical disks for example, can be shared
-- by many processes, and thus do not really require allocation of the device
-- itself. However we do need to allocate the context for accessing the
-- device, and we do need to know the device type for proper interpretation
-- of the file handle. Thus it is always necessary to make the device
-- allocation call before making most other calls in this package.
--
-- Input parameters:
-- type
-- If specified, this gives the type of device to use for sequential
-- I/O. The allowed types are port specific. For example a port may
-- support the type "TAPE" which is implemented via the Oracle tape
-- API. If no type is specified, it may be implied by specifying a
-- particular device name to allocate. The type should be allowed to
-- default to NULL if operating system files are to be used.
--
-- name
-- If specified, this names a particular piece of hardware to use for
-- accessing sequential files. If not specified, any available
-- device of the correct type will be allocated. If the device cannot
-- be shared, it is allocated to this session for exclusive use.
-- The name should be allowed to default to NULL if operating system
-- files are to be used.
--
-- ident
-- This is the users identifier that he uses to name this device. It
-- is only used to report the status of this session via
-- dbms_application_info. This value will be placed in the CLIENT_INFO
-- column of the V$SESSION table, in the row corresponding to the
-- session in which the device was allocated. This value can also
-- be queried with the dbms_application_info.read_client_info procedure.
--
-- noio
-- If TRUE, the device will not be used for doing any I/O. This allows
-- the specification of a device type for deleting sequential files
-- without actually allocating a piece of hardware. An allocation for
-- noio can also be used for issuing device commands. Note that some
-- commands may actually require a physical device and thus will get
-- an error if the allocate was done with noio set to TRUE.
--
-- params
-- This string is simply passed to the device allocate OSD. It is
-- completely port and device specific.
--
-- Returns:
-- It returns a valid device type. This is the type that should be
-- allocated to access the same sequential files at a later date. Note
-- that this might not be exactly the same value as the input string.
-- The allocate OSD may do some translation of the type passed in. The
-- return value is NULL when using operating system files.

PROCEDURE restoreControlfileTo(cfname IN varchar2);

-- This copies the controlfile from the backup set to an operating system
-- file. If the database is mounted, the name must NOT match any of the
-- current controlfiles.
--
-- Input parameters:
-- cfname
-- Name of file to create or overwrite with the controlfile from the
-- backup set.

PROCEDURE restoreDataFileTo( dfnumber IN binary_integer
,toname IN varchar2 default NULL);
--
-- restoreDataFileTo creates the output file from a complete backup in the
-- backup set.
具體操作情況如下:
1、手工恢複控制檔案
SQL> startup nomount;
ORACLE 常式已經啟動。
SQL> declare
  2   devtype varchar2(256);
  3   done  boolean;
  4  begin
  5  devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
  6  sys.dbms_backup_restore.restoresetdatafile;
  7  sys.dbms_backup_restore.restorecontrolfileto(cfname=>'c:/oracle/oradata/tes
tdb/control01.ctf');
  8  sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'d:/databak/A
CT_TESTDB_13G292QT_35',params=>null);
  9  sys.dbms_backup_restore.devicedeallocate;
 10  end;
 11  /

PL/SQL 過程已成功完成。

SQL> alter database mount;

資料庫已更改。
2、手工恢複資料檔案
SQL> declare
  2   devtype varchar2(256);
  3   done  boolean;
  4  begin
  5  devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
  6  sys.dbms_backup_restore.restoresetdatafile;
  7  sys.dbms_backup_restore.restoredatafileto(dfnumber=>01,toname=>'C:/ORACLE/O
RADATA/TESTDB/SYSTEM01.DBF');
  8  sys.dbms_backup_restore.restoredatafileto(dfnumber=>02,toname=>'C:/ORACLE/O
RADATA/TESTDB/UNDOTBS01.DBF');
  9  sys.dbms_backup_restore.restoredatafileto(dfnumber=>03,toname=>'C:/ORACLE/O
RADATA/TESTDB/INDX01.DBF');
10  sys.dbms_backup_restore.restoredatafileto(dfnumber=>05,toname=>'C:/ORACLE/O
RADATA/TESTDB/USERS01.DBF');
11  sys.dbms_backup_restore.restoredatafileto(dfnumber=>04,toname=>'C:/ORACLE/O
RADATA/TESTDB/TOOLS01.DBF');
12  sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'d:/databak/A
CT_TESTDB_13G292QT_35',params=>null);
13  sys.dbms_backup_restore.devicedeallocate;
14  end;
15  /

PL/SQL 過程已成功完成。

3、手工恢複歸檔記錄檔
SQL> DECLARE
  2   devtype varchar2(256);
  3   done boolean;
  4   BEGIN
  5   devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
  6   sys.dbms_backup_restore.restoreSetArchivedLog;
  7   sys.dbms_backup_restore.restoreArchivedLogRange;
  8   sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'D:/DATABAK/
LOG_T539265984_S36_P1', params=>null);
  9   sys.dbms_backup_restore.deviceDeallocate;
 10   end;
 11  /

PL/SQL 過程已成功完成。

SQL> recover database until time '2004-10-11 12:04:45' using backup controlfile
ORA-00279: 更改 77996 (在 10/11/2004 12:04:45 產生) 對於線程 1 是必需的

指定日誌: {<RET>=suggested | filename | AUTO | CANCEL}
C:/oracle/arch/1_1.DBF
已應用的日誌。
完成介質恢複。
SQL> ALTER DATABASE OPEN RESETLOGS;

資料庫已更改。