备份与恢复:用rman方式基于日志序列的不完全恢复

RMAN> run{
(XJOP&Zw\lL02> allocate channel c1 type disk;ITPUB个人空间~8q)Q pk*RF G1kf
3> backup full tag 'dbfull' format 'd:\backup\full%u_%s_%p' database
"F&ZaW'f I04> include current controlfile;
9L/y0L$^k U^S05> sql 'alter system archive log current';ITPUB个人空间*Qql![$^
6> release channel c1;ITPUB个人空间&m l6l ??.sj#s'D
7> }

sql> insert into test values (16);

commit;

sql>alter system switch logfile;

sql> insert into test values (17);

commit;

sql>alter system switch logfile;

SQL> select * from test;

         A
.kBa
w5ZdI'B%e'Bq0----------ITPUB个人空间p7x-AdQ6M$u"O
         1
Z(h$m2O]0         2ITPUB个人空间
O:\7mI ~l]q8?D
         3ITPUB个人空间9bXm3J].X5QE
         4ITPUB个人空间0\H3G\"} `)cv
         5
z0ZV(E&c8ir0         6ITPUB个人空间 a/g|f%gi X
c*FP)I
         7
@i2h7]V6g*C0         8
+p2H dyy7z TX0         9
-F#`$vCpP+n8z#C)Hq0        10ITPUB个人空间9D@!}i{;Sp$U}
        11

         A
Z~A'Z.\.x0----------ITPUB个人空间#m3S)d#RI8MI
        12
2ne:O8[*_tn{0        13ITPUB个人空间Cyr%_ o.L,[
        14  --这之前数据是14号日志前产生的

        16
fL(TtVv0        17 --这之后数据是14号日志后产生的

16 rows selected.

SQL> archive log list;ITPUB个人空间'O,Z%WRfH8z(t{
Database log mode              Archive Mode
a,H)T4R,H$[.f0Automatic archival             EnabledITPUB个人空间E!X#z%@
E
Archive destination            D:\oracle\ora92\RDBMS
6dVmD#[KK0Oldest online log sequence     14
_O@
^ZZX4Y%B_s0Next log sequence to archive   16ITPUB个人空间
M8J]~+x:t$x
Current log sequence           16ITPUB个人空间N(w,y/J{8yIz

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUSITPUB个人空间
f_!V;Y|
---------- ---------- ---------- ---------- ---------- --- ----------------ITPUB个人空间G$b
q2u3HX bq
FIRST_CHANGE# FIRST_TIME
p gG mEq:p^0------------- -------------------ITPUB个人空间Vnl7I.yL*H6I3e%N
         1          1         16  104857600          1 YES ACTIVE
~(h{ jD0        84134 2008-04-10 11:39:19

         2          1         17  104857600          1 NO  CURRENTITPUB个人空间.JQT+Q*P[H
        84150 2008-04-10 11:40:00

         3          1         15  104857600          1 YES ACTIVEITPUB个人空间F`+jM2V#z ^9k,Kj
        84128 2008-04-10 11:39:10

ITPUB个人空间2E"wh_7[/Z
SQL> exitITPUB个人空间J+?"G:Ib,xD
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
A},ki([4cV%|0With the Partitioning, OLAP and Oracle Data Mining options
/I-N&P9S$c`wj%J-D0JServer Release 9.2.0.4.0 - Production

RMAN> shutdown immediate;

using target database controlfile instead of recovery catalogITPUB个人空间-I GRaTXx
database closedITPUB个人空间S3D1CR%sj
database dismountedITPUB个人空间+TecW:Hnq!m
Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)ITPUB个人空间h5i9G~+`o O&iB7t
Oracle instance started

Total System Global Area     101785252 bytes

Fixed Size                      454308 bytesITPUB个人空间L3w!i7L)Y3?
Variable Size                 75497472 bytesITPUB个人空间)Xy+h\(L
Database Buffers              25165824 bytes
4|Z9z6f4R@`]
\0Redo Buffers                    667648 bytes

RMAN> alter database mount;

database mounted

RMAN> run{
t } Z6{"v6|P02> allocate channel c1 type disk;
,W4imFH@rE\03>set until logseq 14 thread 1;    --基于日志序列号的不完全恢复
\8DC+QS{~ mL04> restore database;
b%c/`:H iH5F2[05> recover database;ITPUB个人空间"Y)B1x9Hql:OPEl`
6> sql 'alter database open resetlogs';ITPUB个人空间kN7ef;[ ?W+A4A
7> }

allocated channel: c1
o\D`*a;@l!g,t9Ey0channel c1: sid=11 devtype=DISK

executing command: SET until clause

Starting restore at 2008-04-10 11:50:05

datafile 4 not processed because file is read-only
9\/E#r%|'_ |s0channel c1: starting datafile backupset restore
%@~I#ac7e+IH)R0channel c1: specifying datafile(s) to restore from backup set
#d? [R]1~V0restoring datafile 00001 to D:\ORACLE\ORADATA\PUBTEST\SYSTEM01.DBFITPUB个人空间0V3PXcB q;L8Kr
restoring datafile 00002 to D:\ORACLE\ORADATA\PUBTEST\UNDOTBS01.DBF
z lU}zGy0restoring datafile 00003 to D:\ORACLE\ORADATA\PUBTEST\INDX01.DBFITPUB个人空间 k4le'uUd8u:y
restoring datafile 00005 to D:\ORACLE\ORADATA\PUBTEST\USERS01.DBFITPUB个人空间!Nmh*q8@q{
channel c1: restored backup piece 1
`]V }.x a)v0piece handle=D:\ORACLE\ORA92\DATABASE\04JDFCP3_1_1 tag=TAG20080410T113100 params
(Y:u!\qPU
g#eb0=NULL
GTx8o.Ek/J qCM/_0channel c1: restore complete
h}&{ S`4D5kMu0Finished restore at 2008-04-10 11:50:42

Starting recover at 2008-04-10 11:50:42
'B/i
C cY!V'c+La n0datafile 4 not processed because file is read-only

starting media recovery

archive log thread 1 sequence 9 is already on disk as file D:\ORACLE\ORA92\RDBMS
@&XQ w%v$iY
k0\ARC00009.001
+}4u
?
~N&Ly!z uf0archive log thread 1 sequence 10 is already on disk as file D:\ORACLE\ORA92\RDBMITPUB个人空间2}gHFn3HX)c
S\ARC00010.001
t#Om-jKP0archive log thread 1 sequence 11 is already on disk as file D:\ORACLE\ORA92\RDBM
k:a#xj+P8@?0S\ARC00011.001ITPUB个人空间@0A8u#C4`T
archive log thread 1 sequence 12 is already on disk as file D:\ORACLE\ORA92\RDBMITPUB个人空间o)?$Z B V_!NFr2h
S\ARC00012.001
7v+^JB'y0archive log thread 1 sequence 13 is already on disk as file D:\ORACLE\ORA92\RDBMITPUB个人空间1]:p|b5\
S\ARC00013.001ITPUB个人空间v \%|1O.NZ%R9S7B \(DS
archive log filename=D:\ORACLE\ORA92\RDBMS\ARC00009.001 thread=1 sequence=9ITPUB个人空间9D.Y5j\;r@]CY
archive log filename=D:\ORACLE\ORA92\RDBMS\ARC00010.001 thread=1 sequence=10ITPUB个人空间.X
`F4a&m9E8i
archive log filename=D:\ORACLE\ORA92\RDBMS\ARC00011.001 thread=1 sequence=11ITPUB个人空间o/P+@3D:l)o%W7H/b
archive log filename=D:\ORACLE\ORA92\RDBMS\ARC00012.001 thread=1 sequence=12
2G*qqoo)k0archive log filename=D:\ORACLE\ORA92\RDBMS\ARC00013.001 thread=1 sequence=13ITPUB个人空间+btH DW%n,]Rx@ [
media recovery complete
!qcrWT0Finished recover at 2008-04-10 11:50:43

sql statement: alter database open resetlogsITPUB个人空间
A7?p8tHgHJ
released channel: c1

RMAN> exit

ITPUB个人空间t8lwc!^&V
Recovery Manager complete.

C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Apr 10 11:52:12 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

)z V5S\ PX0Connected to:
i$_!B0~z^0Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionITPUB个人空间:s4~!G:x2om:t"Ts
With the Partitioning, OLAP and Oracle Data Mining options
v9t5z(~k+gp"f0JServer Release 9.2.0.4.0 - Production

SQL> select * from test;

         A
| p6np&t0----------ITPUB个人空间CWTmCqT&SN@q
         1
/ya0c4R o~:|0         2ITPUB个人空间3e]5v-?/]m&E&l
         3ITPUB个人空间9D}}s V]A#e'W
         4
oTEU$z"EK6n0         5ITPUB个人空间1Xl}n1nl1I3[ |H
         6ITPUB个人空间6dU*k,\ s {3q1^4S$~d
         7
iW-?~,xv0vI USf0         8
Y;^?+R~\0         9ITPUB个人空间5\7o"h.`'Px
        10ITPUB个人空间 Go)\1w C
        11

         AITPUB个人空间a7D"n1jF*d0Wx*D
----------
qAIDT0        12
/n \!t$M;Wg0        13ITPUB个人空间R/{9rO_"[[U
        14

14 rows selected.

SQL>

看结果只恢复到14号日志前的数据