[分享] rman表空间基于时间点的不完全恢复

1.备份数据库用带恢复目录的模式
(_;ml&Ce,FC3A&xT0C:\Documents and Settings\Paul Yi>rman target / catalogpaultest/paultest@dev89

Recovery Manager: Release 9.2.0.4.0 - Production

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

connected to target database: PAUL (DBID=1605165889)
qN9n1NKDgbk0connected to recovery catalog database

RMAN> backup database;

Starting backup at 02-JAN-08
$Ao4l"m{I k0starting full resync of recovery catalog
o7r'A2o{}&O3f!L(C0full resync complete
3j T(] C(J:s0allocated channel: ORA_DISK_1ITPUB个人空间|x*J*RD
channel ORA_DISK_1: sid=13 devtype=DISK

@S
MEO0{;|K kk1N0channel ORA_DISK_1: starting full datafile backupsetITPUB个人空间*?Snt6R6r.y/R$q;w
channel ORA_DISK_1: specifying datafile(s) in backupset
s-qlDqYw0input datafile fno=00001 name=D:\ORACLE\ORADATA\PAUL\SYSTEM01.DBF
+Wktz4tn&K0input datafile fno=00002 name=D:\ORACLE\ORADATA\PAUL\UNDOTBS01.DBF
O%_Fj$`/Zw0input datafile fno=00003 name=D:\ORACLE\ORADATA\PAUL\INDX01.DBFITPUB个人空间:^z3x"o`Ui s
input datafile fno=00005 name=D:\ORACLE\ORADATA\PAUL\USERS01.DBFITPUB个人空间Ui
|+rgE8k
input datafile fno=00004 name=D:\ORACLE\ORADATA\PAUL\TOOLS01.DBF
A*b%^n*U.I9|0channel ORA_DISK_1: starting piece 1 at 02-JAN-08
ny3?v+T"D0L0channel ORA_DISK_1: finished piece 1 at 02-JAN-08ITPUB个人空间Rcl6I X gH
piece handle=D:\BACKUP\4BJ55C61_1_1 comment=NONE
%r*L I P5c!N n7l@J0channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55ITPUB个人空间B*LvQ-A(V9c
Finished backup at 02-JAN-08

Starting Control File and SPFILE Autobackup at 02-JAN-08ITPUB个人空间&f B0w"O3R%Tq
piece handle=D:\BACKUP\C-1605165889-20080102-09 comment=NONEITPUB个人空间S'pj2JD-[(Q
Finished Control File and SPFILE Autobackup at 02-JAN-08

RMAN>

2在paul数据库表空间tools下创建测试用户ITPUB个人空间Gq8F-Jn;s3y
C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Jan 2 14:15:45 2008

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

ITPUB个人空间 V!z8GHxY`
Connected to:ITPUB个人空间b U@BZ]@
Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionITPUB个人空间\ns*tKYc
With the Partitioning, OLAP and Oracle Data Mining optionsITPUB个人空间n3aGQ#x
JServer Release 9.2.0.4.0 - Production
e};z Q]0sql> create tablespace ts_test datafile 'd:\oracle\oradata\paul\ts_test01.dbf' size 50M;

SQL> create user test identified by test default tablespace ts_test;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

SQL> conn test/test
,J Y+f.K4a-? E,Qr0Connected.ITPUB个人空间
jV` C4y-w-y`"g6bQ
SQL> create table stu(no number,name varchar2(10));

Table created.

SQL> insert into stu values(109,'abcd');

1 row created.

SQL> select * from stu;

NO NAMEITPUB个人空间C?t
i[&Ol
---------- --------------------ITPUB个人空间 _qgx ZNQ
109 abcd

SQL> commit;

Commit complete.

SQL> conn /as sysdbaITPUB个人空间@ H%U0@B%r^2[
Connected.
UaA3xsh qF0备份控制文件
M)|0l\|"Z@v0SQL> alter database backup controlfile to 'd:\control02.ctl' reuse;

Database altered.

SQL>

SQL> conn test/testITPUB个人空间\ozQuw
Connected.ITPUB个人空间/D$w:A bk
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
T6?!D7H6W,@0------------------------------ ------- ----------ITPUB个人空间A!e%Wm3~/mOs(q I
STU TABLE

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

记住删除此表的时刻是ITPUB个人空间%e*G;ORo#R~
SQL> select sysdate from dual;

SYSDATE
HlN.I8q.b6G0-------------------

ae!aj"F+|02008-01-02 15:14:08

SQL> drop table stu;

Table dropped.

3.为辅助实例创建初始化参数文件ITPUB个人空间9z7qX;yjf#k
新建 d:\oracle\oradata\aux1 目录ITPUB个人空间9O4K(i%V$pF,m
复制d:\oracle\ora92\database\INITpaul.ora 为initaux1.ora文件
\0_X3|-fQ b*?E0然后修改其内容，将实例名字修改为：aux1, 添加lock_name_space=AUX1，修改control_files路径为：
t$k|:X7yi4h0control_files='d:\control02.ctl'
.Gc"D s+t+YD0修改log_archive_start=false
4?(X;Sy5KN0辅助实例必须为非归档模式。ITPUB个人空间`U8}IZw&H8qS2r*m1PO
添加如下两个参数：ITPUB个人空间,kD n(T K/[E&e3b
db_file_name_convert=("d:\oracle\oradata\paul", "d:\oracle\oradata\aux1")
_f [ O e9[AH0log_file_name_convert=("d:\oracle\oradata\paul", "d:\oracle\oradata\aux1")

4. 创建辅助实例服务 用oradmin工具ITPUB个人空间 b{MzZ)a l&m)@
C:\Documents and Settings\Paul Yi>oradim -new -sid aux1 -intpwd abcdefg -startmode manualITPUB个人空间8Y#?-f9VQ

8M.@"]x!Y6T(\05.启动辅助实例到nomount状态ITPUB个人空间^qA
@s.jY
C:\Documents and Settings\Paul Yi>set oracle_sid=aux1

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

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Jan 2 14:36:02 2008

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

Connected to an idle instance.
D:jSx6x$J0SQL> create spfile from pfile;ITPUB个人空间 T|3]'ybMl
file createdITPUB个人空间-IY.X\&j\ cHc)N
SQL> startup nomountITPUB个人空间%U"UBU/eh
Total System Global Area 101785252 bytesITPUB个人空间*qM,l_8Bv7L
Fixed Size 454308 bytesITPUB个人空间{U)X8?F!l
Variable Size 75497472 bytesITPUB个人空间/E5un8h vK/z(P
Database Buffers 25165824 bytes
r zX [r0Redo Buffers 667648 bytes

6.开始表空间不完全恢复ITPUB个人空间9k8|$h8S3j2Qz d*w ?
C:\Documents and Settings\Paul Yi>set oracle_sid=aux1

C:\Documents and Settings\Paul Yi>rman targetsys/abcdefg@paulcatalog paultest/ 6pg Uag]J9H0paultest@dev89auxiliary /
Recovery Manager: Release 9.2.0.4.0 - Production

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

connected to target database: PAUL (DBID=1605165889)
0`4w7ak"a@ T$?0connected to recovery catalog databaseITPUB个人空间)D0nE]U%wO
connected to auxiliary database: paul (not mounted)

RMAN> run{ITPUB个人空间s}'ND T+H/`s
2> allocate auxiliary channel a1 type disk;ITPUB个人空间:h$_1Or.vR8{
3> allocate channel c1 type disk;
\&E2R&\A04> recover tablespace ts_test until time "to_date('2008-01-02 15:14:08','yyyy-mmITPUB个人空间;Dd$y)Nj5r0lz
-dd hh24:mi:ss')";
` QS5Kb5J'H05> }

allocated channel: a1
(b E&U;p6Yu0channel a1: sid=10 devtype=DISK

allocated channel: c1ITPUB个人空间L5ydM R-\
channel c1: sid=9 devtype=DISK

Starting recover at 02-JAN-08

printing stored script. Memory ScriptITPUB个人空间Z;nIhwff%k#Z)|N&U
{
(O*e~4z]0HXl,U0# set the until clause
(b0^3G4Z0set until time "to_date('2008-01-02 15:14:08','yyyy-mm-dd hh24:mi:ss')";
F XW x` be5Z0# restore the controlfile
`T/G BM%O*h
v\0restore clone controlfile to clone_cf;
/n(K Q@TNQl'{/q0# replicate the controlfile
8W4eF^3ki[w/Q0replicate clone controlfile from clone_cf;ITPUB个人空间-g1` b3L T+qz2k
# mount the controlfileITPUB个人空间1e1ETn S6C{jDG:_
sql clone 'alter database mount clone database';
w\d*o/ns{x0# archive current online log for tspitr to a resent until time
-uW(B9W0M#g]2? d#Ot0sql 'alter system archive log current';ITPUB个人空间y9Yd ki
# avoid unnecessary autobackups for structural changes during TSPITR
Sd3n^#n%eZ0sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';ITPUB个人空间d0DPb f#h
# resync catalog after controlfile restore
| C'HX h m0resync catalog;
+k;w v9I y~Z0}
G;~e X,{ k G b7\0executing script. Memory Script

executing command: SET until clause

Starting restore at 02-JAN-08

channel a1: starting datafile backupset restoreITPUB个人空间 U4Mai ~Zk1l
channel a1: restoring controlfileITPUB个人空间?
J!P"kAN%Ecr
output filename=D:\CONTROL02.CTL
,Hz$c'KS.u_^0channel a1: restored backup piece 1
.Y5XD K&h.mc6\ h0piece handle=D:\BACKUP\C-1605165889-20080102-0D tag=null params=NULLITPUB个人空间w,r(jK9yx
channel a1: restore completeITPUB个人空间 ] ?jvBk(f [xZ9u
Finished restore at 02-JAN-08

replicating controlfile

|*rz3clm&n2as0input filename=D:\CONTROL02.CTL

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

starting full resync of recovery catalog
;LA'q:`3bww0N~}0full resync complete

printing stored script. Memory ScriptITPUB个人空间vly W8j
{
P:{C.xMD0# generated tablespace point-in-time recovery script
'Yz r.lL i0# set the until clause
Q;} Zj c$k o0set until time "to_date('2008-01-02 15:14:08','yyyy-mm-dd hh24:mi:ss')";
lBuA\E0plsql <<<-- tspitr_2
4{z`6SSP9`r
yn0declare
CD0T"V\0 sqlstatement varchar2(512);ITPUB个人空间
l#b`u)x
A
offline_not_needed exception;ITPUB个人空间9\&x Y7L
]
pragma exception_init(offline_not_needed, -01539);
{yRG#O%DK O.S0begin
zM9nl&y? i1I0 sqlstatement := 'alter tablespace '|| 'TS_TEST' ||' offline for recover';ITPUB个人空间 q.n2|TB`Z7o
krmicd.writeMsg(6162, sqlstatement);
? m;iB{5Nv#Lnv*rrR0 krmicd.execSql(sqlstatement);
7Wx*I7lJ{i)E0exception
Rf]"t/j'f&X0 when offline_not_needed then

fg(F~(l9aYV,K0 null;ITPUB个人空间4O/lwr2g
end; >>>;ITPUB个人空间.m D-S6?-yD|D
# set a destination filename for restoreITPUB个人空间3My
_ E u){` |YX
set newname for datafile 1 toITPUB个人空间 R#D%^*y J|
"D:\ORACLE\ORADATA\AUX1\SYSTEM01.DBF";
,CFv @#]9d!f'g"B/B+ob0# set a destination filename for restore
6h1r*\ y(~[&c Q s0set newname for datafile 2 toITPUB个人空间+? }qc d0U'f;K1K
"D:\ORACLE\ORADATA\AUX1\UNDOTBS01.DBF";
j#h^rT1G6Vu"k F0# set a destination filename for restore
._eFj9W
|1h1Qz,Z0set newname for datafile 6 to
d#lU}wSp0 "D:\ORACLE\ORADATA\PAUL\TS_TEST01.DBF";ITPUB个人空间'Z8X?_%rF~1j|
# restore the tablespaces in the recovery set plus the auxilliary tablespacesITPUB个人空间m[:Vq gQp
restore clone datafile 1, 2, 6;
dz+OO+J F0switch clone datafile all;

I*g5E R1_ X8?*B;o0#online the datafiles restored or flipped
+|$?s5~#cB%}3?5_+j0sql clone "alter database datafile 1 online";ITPUB个人空间/le+G1g`(pj/U
#online the datafiles restored or flippedITPUB个人空间a9s`nR O7a#q3q
sql clone "alter database datafile 2 online";
2U%X5yS.f5p0#online the datafiles restored or flippedITPUB个人空间/M'g4j'Ma[%v
sql clone "alter database datafile 6 online";ITPUB个人空间1s Z.}.~9Z1^
# make the controlfile point at the restored datafiles, then recover them
j2oUT8^1i0recover clone database tablespace "TS_TEST", "SYSTEM", "UNDOTBS1";ITPUB个人空间 Qc[T&Ug,Z
alter clone database open resetlogs;

ZiB h$O6z5J m0# PLUG HERE the creation of a temporary tablespace if export fails due to lack
0}Kt;XsQ:g0# of temporary space.ITPUB个人空间7w Y*I5Lz
# For example in Unix these two lines would do that:
6K/k D+F
z0#sql clone "create tablespace aux_tspitr_tmp
N9^s+}M4J-p1D0# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
.^BIh;j4V(~u0}ITPUB个人空间gk+bm{1@
executing script. Memory Script

executing command: SET until clause

sql statement: alter tablespace TS_TEST offline for recover

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 02-JAN-08

channel a1: starting datafile backupset restoreITPUB个人空间 Q \R1L}aq'Y.X
channel a1: specifying datafile(s) to restore from backup setITPUB个人空间0NB5Us%i ?M
restoring datafile 00001 to D:\ORACLE\ORADATA\AUX1\SYSTEM01.DBF
7In+o3k8MgB0x W0restoring datafile 00002 to D:\ORACLE\ORADATA\AUX1\UNDOTBS01.DBF
s
DFAp0restoring datafile 00006 to D:\ORACLE\ORADATA\PAUL\TS_TEST01.DBFITPUB个人空间3Bq)dPVd`-? y
channel a1: restored backup piece 1ITPUB个人空间6K"z(B^Z;b
piece handle=D:\BACKUP\4GJ55FO2_1_1 tag=TAG20080102T151058 params=NULL
n\L4Ap7i2t.ZJO0channel a1: restore completeITPUB个人空间s,r#l)k7W?'E,E
Finished restore at 02-JAN-08

datafile 6 switched to datafile copy
*SU8D0D2\R0input datafilecopy recid=33 stamp=642958383 filename=D:\ORACLE\ORADATA\PAUL\TS_TITPUB个人空间X)I;BP%} }^)d
EST01.DBF

sql statement: alter database datafile 1 online

sql statement: alter database datafile 2 online

sql statement: alter database datafile 6 online

Starting recover at 02-JAN-08

starting media recovery

archive log thread 1 sequence 206 is already on disk as file D:\ARCHPAUL\1_206.D
W6~'c`)`oaY`.Z0BF
hB XP*^ B0archive log filename=D:\ARCHPAUL\1_206.DBF thread=1 sequence=206
F@;fJ p/gK HF0media recovery complete
|u6S7u0Htg(g8Sq0Finished recover at 02-JAN-08

database opened

printing stored script. Memory ScriptITPUB个人空间:]%|W[*r\zc
{
M$tr$U1bI#X0# export the tablespaces in the recovery set
~0m:l#YtNt#@0host 'exp userid =\"/@ as sysdba\" point_in_time_recover=y tablespaces=
2k
m-k"~N*Ow U0 TS_TEST file=ITPUB个人空间}+i,Ph/e |p;h!C T
tspitr_a.dmp';
hEV(z }&x&Rr0# shutdown clone before importITPUB个人空间Mc8G(n+`5Y5N
shutdown clone immediateITPUB个人空间.ycJ2f*Zsw
# import the tablespaces in the recovery setITPUB个人空间~t2~ i{2]'Bf3l,y2|!Q
host 'imp userid =\"sys/abcdefg@paulas sysdba\" point_in_time_recover=y file=ITPUB个人空间Dx$@E xG
tspitr_a.dmp';
I.X"?:`1|6e3Oy0# online/offline the tablespace imported
7Ymr3A&NzP%nXh0sql "alter tablespace TS_TEST online";
m6@1K,ole%X0sql "alter tablespace TS_TEST offline";ITPUB个人空间+t _%|Xuk(x`
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
6NVg(Yo%lW`v a0sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';ITPUB个人空间V#Ru%V*e*U,^ NV f
# resync catalog after tspitr finished
~T~3{&t7gM+LT h0resync catalog;
Ww"b NL%i eGK0}ITPUB个人空间N'e\`a^u Hq
executing script. Memory Script

ITPUB个人空间2Ugx1ru*Tf
Export: Release 9.2.0.4.0 - Production on Wed Jan 2 15:33:12 2008

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

ITPUB个人空间]]7v|W#W1K_
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
x Gq0I0YP]:Spc0With the Partitioning, OLAP and Oracle Data Mining optionsITPUB个人空间6T5m/c x.V&a$Bg
JServer Release 9.2.0.4.0 - Production
7`AB` l U0Export done in ZHS16GBK character set and AL16UTF16 NCHAR character setITPUB个人空间[tgYuI)Rv!o'P
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...ITPUB个人空间9|K2dt$y9t
For tablespace TS_TEST ...
amAL
`I0. exporting cluster definitions
R*`,[;C"M"OR$q|0. exporting table definitionsITPUB个人空间#n0G8Qzd2}4do
. . exporting table STU
.r[5~
_MH V p0. exporting referential integrity constraints
2{UrB_ `X0. exporting triggersITPUB个人空间| ]-xe w:I&V
. end point-in-time recovery
V
Cjk~0Export terminated successfully without warnings.ITPUB个人空间'G'L9HPtR
D.^
host command complete

database closed
-wY{X+K5p.f%L i;WT0database dismounted
6M \va W'j0Oracle instance shut down


.X(~1r;E.o8o#|0Import: Release 9.2.0.4.0 - Production on Wed Jan 2 15:33:26 2008

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


F6~lSp-U5yXu0Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionITPUB个人空间?5zNP'}4P
With the Partitioning, OLAP and Oracle Data Mining options
l#fE4yk&z.i0JServer Release 9.2.0.4.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
S? `!OcO0About to import Tablespace Point-in-time Recovery objects...
im@ea/LT0import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
/_&j&zt7g@0. importing TEST's objects into TEST
!_ Q6l[9vNK ]`P0. . importing table "STU"
fxy&K/fu0. importing SYS's objects into SYS

|fiSC ]0Import terminated successfully without warnings.ITPUB个人空间jt/j:l&D#KoI
host command complete

sql statement: alter tablespace TS_TEST online

sql statement: alter tablespace TS_TEST offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

starting full resync of recovery catalogITPUB个人空间6Guqqe*D{H2a`
full resync completeITPUB个人空间T*fq%YZ(i q
Finished recover at 02-JAN-08ITPUB个人空间,U3FB)lg@
q&\1f$t
released channel: c1

RMAN>

ITPUB个人空间UT0v Of_
C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Jan 2 15:35:58 2008

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

ITPUB个人空间 Qk@ M|9R,lj
Connected to:ITPUB个人空间m6H%[ K$uf
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
\js1D"p0With the Partitioning, OLAP and Oracle Data Mining optionsITPUB个人空间_Q[2i2{ G
JServer Release 9.2.0.4.0 - Production

SQL> alter tablespace ts_test online;

Tablespace altered.

SQL> conn test/testITPUB个人空间4W3g C;Y3g
Connected.
1{5}!Gk"OG"f b4h*q-\0SQL> select * from stu;

NO NAME
^%F)OVGJ]:Z(Y0---------- --------------------
2NO1h4@8l]H,J6R.N0 109 abcd
&O4?'o2z-e DP)B4`&K0
1z;SX ];w%d0 至此，已经恢复