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;

ITPUB个人空间I DA b,G
Starting backup at 10-JUN-08ITPUB个人空间\*WQZ](wr
current log archivedITPUB个人空间 ZgSx_H JC
using target database controlfile instead of recovery catalogITPUB个人空间J}:W^Q
allocated channel: ORA_DISK_1
?E%p5hf2v0channel ORA_DISK_1: sid=13 devtype=DISK
~|:w["O
a @*V!\WKE0channel ORA_DISK_1: starting archive log backupset
(L yr k#M([ i0channel ORA_DISK_1: specifying archive log(s) in backup setITPUB个人空间xwf6RT1IL
input archive log thread=1 sequence=1 recid=265 stamp=657037381
e8X"@ FvmIO0input archive log thread=1 sequence=2 recid=266 stamp=657037399
z3`W_p3Z1i0input archive log thread=1 sequence=3 recid=267 stamp=657037410ITPUB个人空间3W6F&AU)_J"BU
input archive log thread=1 sequence=4 recid=268 stamp=657037416
d_H9Oxp/y:{.Om0input archive log thread=1 sequence=5 recid=269 stamp=657037420ITPUB个人空间)F*B6Bt2i`@"v
input archive log thread=1 sequence=6 recid=270 stamp=657037422ITPUB个人空间X:r h!O!o2R8arz
input archive log thread=1 sequence=7 recid=271 stamp=657037462ITPUB个人空间 F!TO*G,tpwx
input archive log thread=1 sequence=8 recid=272 stamp=657038049ITPUB个人空间)Re
Z{.U
channel ORA_DISK_1: starting piece 1 at 10-JUN-08
2h/fu$Yb3}J5O0channel ORA_DISK_1: finished piece 1 at 10-JUN-08ITPUB个人空间o(Ba4r+y ESsY
piece handle=D:\BACKUP\3PJIJ6N2_1_1 comment=NONE
{8w:` `)cO0channel ORA_DISK_1: backup set complete, elapsed time: 00:00:09ITPUB个人空间l)^4O+WZ K
\
channel ORA_DISK_1: deleting archive log(s)ITPUB个人空间?2pu}OH
archive log filename=D:\ARCHPAUL\PUBTEST_1_1.DBF recid=265 stamp=657037381
%og.Q x;K;|A8b.U0archive log filename=D:\ARCHPAUL\PUBTEST_1_2.DBF recid=266 stamp=657037399
3],K)u%aVM/RK T0archive log filename=D:\ARCHPAUL\PUBTEST_1_3.DBF recid=267 stamp=657037410
~9R&T
z^0archive log filename=D:\ARCHPAUL\PUBTEST_1_4.DBF recid=268 stamp=657037416ITPUB个人空间jx/bs
a6Iy"wSD`
archive log filename=D:\ARCHPAUL\PUBTEST_1_5.DBF recid=269 stamp=657037420ITPUB个人空间 BfB_#UL&E\c9I0N
archive log filename=D:\ARCHPAUL\PUBTEST_1_6.DBF recid=270 stamp=657037422
6oBj9qT0archive log filename=D:\ARCHPAUL\PUBTEST_1_7.DBF recid=271 stamp=657037462
)u|iS^a0archive log filename=D:\ARCHPAUL\PUBTEST_1_8.DBF recid=272 stamp=657038049
4Z-Q%e?n7g0xT0Finished backup at 10-JUN-08

Starting backup at 10-JUN-08ITPUB个人空间+E:Y:sM@%`
using channel ORA_DISK_1ITPUB个人空间L,F2B.z @&X)s#hzVu
channel ORA_DISK_1: starting full datafile backupsetITPUB个人空间9CHdm,H
channel ORA_DISK_1: specifying datafile(s) in backupsetITPUB个人空间GacSh#Z'bX
including current SPFILE in backupsetITPUB个人空间@{^9Q5\D
including current controlfile in backupset
@lf#t"i~f-o!o0input datafile fno=00001 name=D:\ORACLE\ORADATA\PUBTEST\SYSTEM01.DBF
LhL rg0input datafile fno=00002 name=D:\ORACLE\ORADATA\PUBTEST\UNDOTBS01.DBF
\Z$i-^9HV8O0input datafile fno=00004 name=D:\ORACLE\ORADATA\PUBTEST\INDX01.DBFITPUB个人空间v!`4}X y
input datafile fno=00006 name=D:\ORACLE\ORADATA\PUBTEST\USERS01.DBFITPUB个人空间$M z y8Q2VxLp j
input datafile fno=00005 name=D:\ORACLE\ORADATA\PUBTEST\TOOLS01.DBFITPUB个人空间8d3U3BZ!B\
input datafile fno=00003 name=D:\ORACLE\ORADATA\PUBTEST\EXAMPLE01.DBF
7NK K*If0Qy$b0channel ORA_DISK_1: starting piece 1 at 10-JUN-08
6I"A$FD^~8E0channel ORA_DISK_1: finished piece 1 at 10-JUN-08ITPUB个人空间3{T7zL7Sj'?
piece handle=D:\BACKUP\3QJIJ6NB_1_1 comment=NONE
REz `9e }[9M0channel ORA_DISK_1: backup set complete, elapsed time: 00:00:47ITPUB个人空间z1Y7p7J(vj$D
Finished backup at 10-JUN-08

Starting backup at 10-JUN-08ITPUB个人空间 _H+rn'M$vZ-H[
current log archivedITPUB个人空间2DQy+pX
using channel ORA_DISK_1
1_P%oe7{S p.L/H0channel ORA_DISK_1: starting archive log backupsetITPUB个人空间*j@(y'sx5e
channel ORA_DISK_1: specifying archive log(s) in backup setITPUB个人空间 I6A3gt$Lfa$H
input archive log thread=1 sequence=9 recid=273 stamp=657038106
,D;dcjc5H0channel ORA_DISK_1: starting piece 1 at 10-JUN-08
2[|Q}2Eh)^4]R0channel ORA_DISK_1: finished piece 1 at 10-JUN-08ITPUB个人空间M.RG#{g5V9wL6T
piece handle=D:\BACKUP\3RJIJ6OQ_1_1 comment=NONE
U~NQ&fOk+f9w0channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02ITPUB个人空间8Z4B`Q;f,E&Y
channel ORA_DISK_1: deleting archive log(s)
B+JZ
^Q!i|r I5m?0archive log filename=D:\ARCHPAUL\PUBTEST_1_9.DBF recid=273 stamp=657038106
%A[8[u.L)Z yEy0g0Finished 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.

Y"c\kel0Connected to:ITPUB个人空间B3Pn^Hy?
Oracle9i Enterprise Edition Release 9.2.0.8.0 - ProductionITPUB个人空间L9i3tH6M;`$Q+}+R|
s
With the Partitioning, OLAP and Oracle Data Mining options
h(hB+X6T~}0JServer 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(*)ITPUB个人空间.o | dO-i
d tq
----------
%KC9O2g_$Rs4Y'y0  16777216

SQL> insert into test1 select * from test1 where rownum<10; --当前在线日志

9 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from test1;

  COUNT(*)ITPUB个人空间!yu#gp)_l;~
----------ITPUB个人空间4U c^ cCy1Kt
  16777225

SQL> shutdown abort;
Q5\!X+a]/@0ORACLE instance shut down.ITPUB个人空间 bSPy,QG$kKEX
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;
W{(PO}lY&J0ORACLE instance started.

Total System Global Area  101785012 bytesITPUB个人空间)g&sSB4[}Y
Fixed Size                   454068 bytes
,XoL&s$q0Variable Size              75497472 bytes
{^5t2v+d)i4`cu?8E-c0Database Buffers           25165824 bytes
!P9?i!lG&~4O)\m$~)x0Redo Buffers                 667648 bytes

利用包恢复控制文件包
yH#B~Af$ykG0SQL> declare
!`%c H9D8e+{4b0` m0devtype varchar2(256);ITPUB个人空间 i kP L-jZ
done  boolean;ITPUB个人空间V*s` t/G!l7i$v
beginITPUB个人空间 pz.K8c$`5D
devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');ITPUB个人空间(v's$R7d?
sys.dbms_backup_restore.restoresetdatafile;
w*Yng)A5nQ0sys.dbms_backup_restore.restorecontrolfileto(cfname=>'D:\oracle\oradata\pubtest\control01.ctl');ITPUB个人空间*L,Q7A O'bML6]n6S
sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'d:\backup\3LJIIK4O_1_1',params=>null); 红色代表备份集
9{H`R&t&USd+a0sys.dbms_backup_restore.devicedeallocate;
;YQ:SXh"Ee;Qq0end;
?WbQd8Z0k'F v0/
$T6\k!CA/s+fn0PL/SQL procedure successfully completed.

拷贝control01.ctl   命名为control02.ctl,control03.ctl 和参数文件一致

SQL> alter database mount;

Database altered.

利用包恢复数据文件 注意文件名和路径  可以通过视图

select file#,name from v$datafile 查出来

SQL> declareITPUB个人空间,B7|C-u'Y`d R
devtype varchar2(256);ITPUB个人空间&r wfc-p X Di
done boolean;ITPUB个人空间N1f6yD7\:S
beginITPUB个人空间8^-G8Y
U^KD"r
devtype:=sys.dbms_backup_restore.deviceallocate(type=>'',ident=>'t1');ITPUB个人空间w Ap2FnL
{Q4dbe
sys.dbms_backup_restore.restoresetdatafile;ITPUB个人空间F M)QV&o{_
sys.dbms_backup_restore.restoredatafileto(dfnumber=>01,toname=>'d:\oracle\oradata\pubtest\system01.dbf');ITPUB个人空间d r'a6I8y1J T\\!?
sys.dbms_backup_restore.restoredatafileto(dfnumber=>02,toname=>'d:\oracle\oradata\pubtest\undotbs01.dbf');
.o h _%Io0sys.dbms_backup_restore.restoredatafileto(dfnumber=>03,toname=>'d:\oracle\oradata\pubtest\example01.dbf');ITPUB个人空间!YT*H%B0i(Ra
sys.dbms_backup_restore.restoredatafileto(dfnumber=>04,toname=>'d:\oracle\oradata\pubtest\indx01.dbf');ITPUB个人空间}3\JK9JInX/w
sys.dbms_backup_restore.restoredatafileto(dfnumber=>05,toname=>'d:\oracle\oradata\pubtest\tools01.dbf');ITPUB个人空间;g V5b%ISLJ(b
sys.dbms_backup_restore.restoredatafileto(dfnumber=>06,toname=>'d:\oracle\oradata\pubtest\users01.dbf');ITPUB个人空间[zb6b%Z
sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'d:\backup\3NJIIRVU_1_1',params=>null);ITPUB个人空间il9]^`
sys.dbms_backup_restore.devicedeallocate;
!t3a0b9kS_W0end;ITPUB个人空间bgvxm
| ~e [
/ITPUB个人空间,hU f0x-O+YLt
PL/SQL procedure successfully completed. 

利用包恢复归档日志文件 也要设置归档路径目标

因为这里plus archivelog 命令备份会产生两个归档

日志备份集

SQL> declareITPUB个人空间F A)AT;qD
gS)Sr$u
devtype varchar2(256);ITPUB个人空间/r3z!`/U1S0G g
done boolean;
k3k*u#H-b8o
A0begin
}
m+\}+Flq0devtype:=sys.dbms_backup_restore.deviceallocate(type=>'',ident=>'t1');
)d6{bJ[*J0sys.dbms_backup_restore.restoresetarchivedlog(destination=>'d:\archpaul\');
I;m} Z3aqk5E!C0sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>1);ITPUB个人空间h!B
i?Fi.n
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>2);
W~"F`bMT1A0sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>3);ITPUB个人空间Z Sd FW
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>4);
A+VbA1H F*V1a+d3A0sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>5);ITPUB个人空间*R x4o3a
X.Hdh]
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>6);
M5H)TdW4UIF0sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>7);ITPUB个人空间O}0^"q2rj4J
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>8);
+CWx+bN0sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'d:\backup\3PJIJ6N2_1_1',params=>null);ITPUB个人空间)Yrrb:YF[~1Xm
sys.dbms_backup_restore.devicedeallocate;ITPUB个人空间Y iqd2M,KI+} l
end;
#F_U3]A,O,v.z0/ITPUB个人空间 cj"Y$? C"kJQz\!q
PL/SQL procedure successfully completed.

SQL> declareITPUB个人空间r5?)fg5|-c-P.N}
devtype varchar2(256);
n1f?Hte'P'\]0done boolean;
}G v)n4v.G cX0begin
T _q"n}${ xFYl*e0devtype:=sys.dbms_backup_restore.deviceallocate(type=>'',ident=>'t1');ITPUB个人空间g;ROxH:\$| a
sys.dbms_backup_restore.restoresetarchivedlog(destination=>'d:\archpaul\');
Gf f6^ o!x0sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>9);ITPUB个人空间,O-_+_]V
sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'d:\backup\3RJIJ6OQ_1_1',params=>null);ITPUB个人空间3~ M*cwPTj(Bx
sys.dbms_backup_restore.devicedeallocate;ITPUB个人空间XgYIk]*c&U7^S
end;ITPUB个人空间'v-NS&B.t{-I2^2m
/
F$e A/~3X%t0 
b-^+lF5}8oc*A;DR6G1J0PL/SQL procedure successfully completed.

使用备份的控制文件恢复数据库 因为丢失当前在线日志文件

介质恢复时用until cancel  open resetlogs打开数据库

SQL> recover database using backup controlfile until cancel;
k;Lrm%SKk0ORA-00279: change 780486 generated at 06/10/2008 14:34:09 needed for thread 1
_C aAH%~0ORA-00289: suggestion : D:\ARCHPAUL\PUBTEST_1_9.DBF
}6T0Zh:k CU*_@v0ORA-00280: change 780486 for thread 1 is in sequence #9

5Ss
zQ5QQ$d0Specify log: {<RET>=suggested | filename | AUTO | CANCEL}ITPUB个人空间s)]GZ0`8qa,c
autoITPUB个人空间t y\
s:@7N@'JN
ORA-00279: change 780505 generated at 06/10/2008 14:35:06 needed for thread 1
I1s,O4|N!fEB|0ORA-00289: suggestion : D:\ARCHPAUL\PUBTEST_1_10.DBFITPUB个人空间+\p h%V x B
ORA-00280: change 780505 for thread 1 is in sequence #10ITPUB个人空间.MQ#_9IT8ig.k)i
ORA-00278: log file 'D:\ARCHPAUL\PUBTEST_1_9.DBF' no longer needed for thisITPUB个人空间;P Z p%ei`
recovery

ITPUB个人空间"P9M3L.x8n{f C
ORA-00279: change 781271 generated at 06/10/2008 14:46:04 needed for thread 1
vIt/g7Y0ORA-00289: suggestion : D:\ARCHPAUL\PUBTEST_1_11.DBF
2VY*] C1s(q)TB0ORA-00280: change 781271 for thread 1 is in sequence #11
8N$z~0`&B|!o0TU0ORA-00278: log file 'D:\ARCHPAUL\PUBTEST_1_10.DBF' no longer needed for thisITPUB个人空间7s
lEW!_(xg
recovery

9w&bE8cR:jl0ORA-00279: change 781354 generated at 06/10/2008 14:47:05 needed for thread 1ITPUB个人空间5} Po zD
}3p
ORA-00289: suggestion : D:\ARCHPAUL\PUBTEST_1_12.DBF
/@5N7YQ jypG$v0ORA-00280: change 781354 for thread 1 is in sequence #12ITPUB个人空间|@|C1W)D6n,H-y0nG
ORA-00278: log file 'D:\ARCHPAUL\PUBTEST_1_11.DBF' no longer needed for this
*S,XOPaX+HN0recovery

.Gv7ZHp(H0ORA-00279: change 781369 generated at 06/10/2008 14:47:46 needed for thread 1ITPUB个人空间i%eX_YD&|Z1g
ORA-00289: suggestion : D:\ARCHPAUL\PUBTEST_1_13.DBF

G'Wwn y+j#{-F0ORA-00280: change 781369 for thread 1 is in sequence #13
@;z*Il)wA+xQW0ORA-00278: log file 'D:\ARCHPAUL\PUBTEST_1_12.DBF' no longer needed for this
4o;H SB&P/H5C7W%x0recovery

v%i3b_1Jz,K0ORA-00279: change 781376 generated at 06/10/2008 14:47:57 needed for thread 1
-p+Cm"Oq+j0ORA-00289: suggestion : D:\ARCHPAUL\PUBTEST_1_14.DBF
"[4JQ8GZK]
C3^-`0ORA-00280: change 781376 for thread 1 is in sequence #14ITPUB个人空间"ERqu{&vSd O
ORA-00278: log file 'D:\ARCHPAUL\PUBTEST_1_13.DBF' no longer needed for this

~ds W6H3s!V]0recovery

ITPUB个人空间/j X }+^s?
K!s
ORA-00308: cannot open archived log 'D:\ARCHPAUL\PUBTEST_1_14.DBF'ITPUB个人空间q@{YFb6S{J&C
ORA-27041: unable to open file
\$Q!b;`4d*M,c0OSD-04002: unable to open file
&cSvP ry0O/S-Error: (OS 2) The system cannot find the file specified.

?#G$~V_3ev5|0SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from test1;

  COUNT(*)
1T,Y)A'\&\ ZICo0----------
\V{{4Yag!H0  16777216

可以看到当前在线日志文件里面的9条数据丢失