http://blog.itpub.net/7199859/viewspace-343273/]dbms_backup_restore包恢复控制文件,数据文件,归档文件的测试案例

这个包恢复主要用于控制文件丢失,没备份和恢复目录也不存在的情况下进行恢复,
但有rman 备份产生的备份集
测试环境 oracle 9.2.0.8+winxp sp2
1.用rman backup database include current controlfile plus archivelog delete input
备份数据库
2.自动备份 CONFIGURE CONTROLFILE AUTOBACKUP OFF; 关闭
C:\Documents and Settings\Paul Yi>rman target /
Recovery Manager: Release 9.2.0.8.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: PUBTEST (DBID=799229701)
RMAN> backup database include current controlfile plus archivelog delete input;

Starting backup at 10-JUN-08
current log archived
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=265 stamp=657037381
input archive log thread=1 sequence=2 recid=266 stamp=657037399
input archive log thread=1 sequence=3 recid=267 stamp=657037410
input archive log thread=1 sequence=4 recid=268 stamp=657037416
input archive log thread=1 sequence=5 recid=269 stamp=657037420
input archive log thread=1 sequence=6 recid=270 stamp=657037422
input archive log thread=1 sequence=7 recid=271 stamp=657037462
input archive log thread=1 sequence=8 recid=272 stamp=657038049
channel ORA_DISK_1: starting piece 1 at 10-JUN-08
channel ORA_DISK_1: finished piece 1 at 10-JUN-08
piece handle=D:\BACKUP\3PJIJ6N2_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:09
channel ORA_DISK_1: deleting archive log(s)
archive log filename=D:\ARCHPAUL\PUBTEST_1_1.DBF recid=265 stamp=657037381
archive log filename=D:\ARCHPAUL\PUBTEST_1_2.DBF recid=266 stamp=657037399
archive log filename=D:\ARCHPAUL\PUBTEST_1_3.DBF recid=267 stamp=657037410
archive log filename=D:\ARCHPAUL\PUBTEST_1_4.DBF recid=268 stamp=657037416
archive log filename=D:\ARCHPAUL\PUBTEST_1_5.DBF recid=269 stamp=657037420
archive log filename=D:\ARCHPAUL\PUBTEST_1_6.DBF recid=270 stamp=657037422
archive log filename=D:\ARCHPAUL\PUBTEST_1_7.DBF recid=271 stamp=657037462
archive log filename=D:\ARCHPAUL\PUBTEST_1_8.DBF recid=272 stamp=657038049
Finished backup at 10-JUN-08
Starting backup at 10-JUN-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00001 name=D:\ORACLE\ORADATA\PUBTEST\SYSTEM01.DBF
input datafile fno=00002 name=D:\ORACLE\ORADATA\PUBTEST\UNDOTBS01.DBF
input datafile fno=00004 name=D:\ORACLE\ORADATA\PUBTEST\INDX01.DBF
input datafile fno=00006 name=D:\ORACLE\ORADATA\PUBTEST\USERS01.DBF
input datafile fno=00005 name=D:\ORACLE\ORADATA\PUBTEST\TOOLS01.DBF
input datafile fno=00003 name=D:\ORACLE\ORADATA\PUBTEST\EXAMPLE01.DBF
channel ORA_DISK_1: starting piece 1 at 10-JUN-08
channel ORA_DISK_1: finished piece 1 at 10-JUN-08
piece handle=D:\BACKUP\3QJIJ6NB_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:47
Finished backup at 10-JUN-08
Starting backup at 10-JUN-08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=9 recid=273 stamp=657038106
channel ORA_DISK_1: starting piece 1 at 10-JUN-08
channel ORA_DISK_1: finished piece 1 at 10-JUN-08
piece handle=D:\BACKUP\3RJIJ6OQ_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=D:\ARCHPAUL\PUBTEST_1_9.DBF recid=273 stamp=657038106
Finished backup at 10-JUN-08
RMAN>
C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.8.0 - Production on Tue Jun 10 14:45:36 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
SQL> insert into test1 select * from test1;
8388608 rows created.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> commit;
Commit complete.
SQL> commit;
Commit complete.
SQL> alter system switch logfile; --日志切换 生成归档
System altered.
SQL> select count(*) from test1;
COUNT(*)
----------
16777216
SQL> insert into test1 select * from test1 where rownum<10; --当前在线日志 9 rows created. SQL> commit;
Commit complete.
SQL> select count(*) from test1;
COUNT(*)
----------
16777225
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
删除D:\oracle\oradata\pubtest\*.ctl.*.dbf,*.log 所有文件 控制文件,数据文件,重做日志文件丢失
C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.8.0 - Production on Tue Jun 10 14:54:24 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 101785012 bytes
Fixed Size 454068 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
利用包恢复控制文件包
SQL> 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.restorecontrolfileto(cfname=>'D:\oracle\oradata\pubtest\control01.ctl');
sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'d:\backup\3LJIIK4O_1_1',params=>null); 红色代表备份集
sys.dbms_backup_restore.devicedeallocate;
end;
/
PL/SQL procedure successfully completed.
拷贝control01.ctl 命名为control02.ctl,control03.ctl 和参数文件一致
SQL> alter database mount;
Database altered.
利用包恢复数据文件 注意文件名和路径 可以通过视图
select file#,name from v$datafile 查出来
SQL> 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=>01,toname=>'d:\oracle\oradata\pubtest\system01.dbf');
sys.dbms_backup_restore.restoredatafileto(dfnumber=>02,toname=>'d:\oracle\oradata\pubtest\undotbs01.dbf');
sys.dbms_backup_restore.restoredatafileto(dfnumber=>03,toname=>'d:\oracle\oradata\pubtest\example01.dbf');
sys.dbms_backup_restore.restoredatafileto(dfnumber=>04,toname=>'d:\oracle\oradata\pubtest\indx01.dbf');
sys.dbms_backup_restore.restoredatafileto(dfnumber=>05,toname=>'d:\oracle\oradata\pubtest\tools01.dbf');
sys.dbms_backup_restore.restoredatafileto(dfnumber=>06,toname=>'d:\oracle\oradata\pubtest\users01.dbf');
sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'d:\backup\3NJIIRVU_1_1',params=>null);
sys.dbms_backup_restore.devicedeallocate;
end;
/
PL/SQL procedure successfully completed.
利用包恢复归档日志文件 也要设置归档路径目标
因为这里plus archivelog 命令备份会产生两个归档
日志备份集
SQL> declare
devtype varchar2(256);
done boolean;
begin
devtype:=sys.dbms_backup_restore.deviceallocate(type=>'',ident=>'t1');
sys.dbms_backup_restore.restoresetarchivedlog(destination=>'d:\archpaul\');
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>1);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>2);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>3);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>4);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>5);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>6);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>7);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>8);
sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'d:\backup\3PJIJ6N2_1_1',params=>null);
sys.dbms_backup_restore.devicedeallocate;
end;
/
PL/SQL procedure successfully completed.
SQL> declare
devtype varchar2(256);
done boolean;
begin
devtype:=sys.dbms_backup_restore.deviceallocate(type=>'',ident=>'t1');
sys.dbms_backup_restore.restoresetarchivedlog(destination=>'d:\archpaul\');
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>9);
sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'d:\backup\3RJIJ6OQ_1_1',params=>null);
sys.dbms_backup_restore.devicedeallocate;
end;
/

PL/SQL procedure successfully completed.
使用备份的控制文件恢复数据库 因为丢失当前在线日志文件
介质恢复时用until cancel open resetlogs打开数据库
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 780486 generated at 06/10/2008 14:34:09 needed for thread 1
ORA-00289: suggestion : D:\ARCHPAUL\PUBTEST_1_9.DBF
ORA-00280: change 780486 for thread 1 is in sequence #9

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 780505 generated at 06/10/2008 14:35:06 needed for thread 1
ORA-00289: suggestion : D:\ARCHPAUL\PUBTEST_1_10.DBF
ORA-00280: change 780505 for thread 1 is in sequence #10
ORA-00278: log file 'D:\ARCHPAUL\PUBTEST_1_9.DBF' no longer needed for this
recovery

ORA-00279: change 781271 generated at 06/10/2008 14:46:04 needed for thread 1
ORA-00289: suggestion : D:\ARCHPAUL\PUBTEST_1_11.DBF
ORA-00280: change 781271 for thread 1 is in sequence #11
ORA-00278: log file 'D:\ARCHPAUL\PUBTEST_1_10.DBF' no longer needed for this
recovery

ORA-00279: change 781354 generated at 06/10/2008 14:47:05 needed for thread 1
ORA-00289: suggestion : D:\ARCHPAUL\PUBTEST_1_12.DBF
ORA-00280: change 781354 for thread 1 is in sequence #12
ORA-00278: log file 'D:\ARCHPAUL\PUBTEST_1_11.DBF' no longer needed for this
recovery

ORA-00279: change 781369 generated at 06/10/2008 14:47:46 needed for thread 1
ORA-00289: suggestion : D:\ARCHPAUL\PUBTEST_1_13.DBF
ORA-00280: change 781369 for thread 1 is in sequence #13
ORA-00278: log file 'D:\ARCHPAUL\PUBTEST_1_12.DBF' no longer needed for this
recovery

ORA-00279: change 781376 generated at 06/10/2008 14:47:57 needed for thread 1
ORA-00289: suggestion : D:\ARCHPAUL\PUBTEST_1_14.DBF
ORA-00280: change 781376 for thread 1 is in sequence #14
ORA-00278: log file 'D:\ARCHPAUL\PUBTEST_1_13.DBF' no longer needed for this
recovery

ORA-00308: cannot open archived log 'D:\ARCHPAUL\PUBTEST_1_14.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from test1;
COUNT(*)
----------
16777216
可以看到当前在线日志文件里面的9条数据丢失