利用dbms_backup_restore包手工进行数据库恢复收藏

利用dbms_backup_restore包手工进行数据库恢复收藏
http://blog.csdn.net/withhl/archive/2004/10/11/131946.aspx
新一篇: RSS实际应用 | 旧一篇: recovery window理解

情景描述：
数据运行在归档模式下，用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 是必需的


指定日志: {=suggested | filename | AUTO | CANCEL}
C:\oracle\arch\1_1.DBF
已应用的日志。
完成介质恢复。
SQL> ALTER DATABASE OPEN RESETLOGS;

数据库已更改。