rman表空间基于时间点的不完全恢复

数据库版本：9204
wTD't2go\z{,zV m0目标数据库名称 paul
bhWT4XV1ajy;qQ'M0辅助实例 AUX1

ITPUB个人空间;eU K*XWWoM
1.备份数据库用带恢复目录的模式ITPUB个人空间 b*E|g_o
C:\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)ITPUB个人空间8\
d+s4Be&T
connected to recovery catalog database

RMAN> backup database;

Starting backup at 02-JAN-08
[)^t-v$y{mC Z0starting full resync of recovery catalogITPUB个人空间F;wHxm Xw
full resync completeITPUB个人空间IX%HaZJz-PAd
allocated channel: ORA_DISK_1ITPUB个人空间"Vhi LrJe*p*{N
channel ORA_DISK_1: sid=13 devtype=DISKITPUB个人空间z
Ubc!TqfbM
channel ORA_DISK_1: starting full datafile backupsetITPUB个人空间
e3XR$q/OF5RZJ,C
channel ORA_DISK_1: specifying datafile(s) in backupset
"iLwk']4[#h'a0input datafile fno=00001 name=D:\ORACLE\ORADATA\PAUL\SYSTEM01.DBFITPUB个人空间2~3g? LqK4F
input datafile fno=00002 name=D:\ORACLE\ORADATA\PAUL\UNDOTBS01.DBF
5?6i,`2Q o Q6o3{4V9Yb0input datafile fno=00003 name=D:\ORACLE\ORADATA\PAUL\INDX01.DBFITPUB个人空间/M,\"r4MJ
input datafile fno=00005 name=D:\ORACLE\ORADATA\PAUL\USERS01.DBFITPUB个人空间y xmZjs6~
input datafile fno=00004 name=D:\ORACLE\ORADATA\PAUL\TOOLS01.DBF
]1TS$`,[H#Hn;Cj.`0channel ORA_DISK_1: starting piece 1 at 02-JAN-08ITPUB个人空间$S$p&fzG*A
channel ORA_DISK_1: finished piece 1 at 02-JAN-08
^|9V"p C|8u-ym X,K-uT0piece handle=D:\BACKUP\4BJ55C61_1_1 comment=NONE
^d8hc$r'E-g0channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55ITPUB个人空间Ze3l n-?(^
Finished backup at 02-JAN-08

Starting Control File and SPFILE Autobackup at 02-JAN-08ITPUB个人空间dw&F-]8_pJy;pv
piece handle=D:\BACKUP\C-1605165889-20080102-09 comment=NONE
^cI XO@0Finished Control File and SPFILE Autobackup at 02-JAN-08

RMAN>

2在paul数据库表空间ts_test下创建测试用户ITPUB个人空间.H4Tw6~"msl
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个人空间~3j+dTII
Connected to:
c4Qd3KK2`|e'_0Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionITPUB个人空间mf'[9v^t
p Y$l.Y
With the Partitioning, OLAP and Oracle Data Mining options
,N9lv~#X)}:}0JServer Release 9.2.0.4.0 - ProductionITPUB个人空间Zt(^"i;\Y2r/Pl+z
sql> 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
wc&k%{Q%I)~Z[:@6W)O0Connected.
1t l
b1N5ZI0SQL> 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 NAME
X8cOz7l:\z0---------- --------------------

{cF-Cv~5Ra0       109 abcd

SQL> commit;

Commit complete.

SQL> conn /as sysdba
3t3cgn[0Connected.ITPUB个人空间Q'xw8P'~ n
备份控制文件
([nO
Dx;i7_ R0SQL> alter database backup controlfile to 'd:\control02.ctl' reuse;

Database altered.

SQL>

SQL> conn test/test
? s)^N |&En0Connected.
-Q(b&L rp/Nr}0SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
*L0F5JB%l:UWW0------------------------------ ------- ----------
I`\M)G+a_0STU                               TABLE

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

Session altered.

记住删除此表的时刻是ITPUB个人空间]2C%?AT5b
SQL> select sysdate from dual;

SYSDATE
5sZ2N(e6Q1Z
dY-`0-------------------
.h |zuGj?9m02008-01-02 15:14:08

SQL> drop table stu;

Table dropped.

3.为辅助实例创建初始化参数文件ITPUB个人空间'O;fj?)l
新建 d:\oracle\oradata\aux1 目录
3}#]JBr3g.H3r&E3e!m0复制d:\oracle\ora92\database\INITpaul.ora 为initaux1.ora文件ITPUB个人空间&o8U m;^8PY*c
然后修改其内容，将实例名字修改为：aux1, 添加lock_name_space=AUX1，修改control_files路径为：
l0jiz i2~&hQ$bG [0control_files='d:\control02.ctl'ITPUB个人空间-|G}:l6io+d
修改log_archive_start=false
9ynz0mtk:f0辅助实例必须为非归档模式。
O}+]Vw*bF0添加如下两个参数：ITPUB个人空间q-L'|HE7Z3p
db_file_name_convert=("d:\oracle\oradata\paul", "d:\oracle\oradata\aux1")ITPUB个人空间F1OA?4y`
log_file_name_convert=("d:\oracle\oradata\paul", "d:\oracle\oradata\aux1")

4. 创建辅助实例服务 用oradmin工具ITPUB个人空间E;{'gRF0VTpm
C:\Documents and Settings\Paul Yi>oradim -new -sid aux1 -intpwd abcdefg -startmode manual
yK:DN,t#{5n0 
qLU&RR7\1s7gl9m05.启动辅助实例到nomount状态
#U%RJ$k&`qmu.F3CT/K0C:\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.ITPUB个人空间2WS3q(P%]8R4?~
SQL> create spfile from pfile;ITPUB个人空间0R"C@ JXs7H&X&X
file createdITPUB个人空间e-J Kw
t$X\Q
SQL> startup nomountITPUB个人空间3G\Z.|I6ZLM
Total System Global Area  101785252 bytes
H a }$dW?Ku.Z X,^0Fixed Size                   454308 bytes
E"u#\L_0\XP'j@0Variable Size              75497472 bytes
M:@(y0p _7T0Database Buffers           25165824 bytesITPUB个人空间QS1@)jE.WhSR
Redo Buffers                 667648 bytes

6.开始表空间不完全恢复ITPUB个人空间,y\BTt-I j-_
C:\Documents and Settings\Paul Yi>set oracle_sid=aux1

C:\Documents and Settings\Paul Yi>rman targetsys/abcdefg@paulcatalog paultest/
3Y
qq!])h0paultest@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)ITPUB个人空间jV7_d'])zR
connected to recovery catalog databaseITPUB个人空间1@;THUA&A:C
connected to auxiliary database: paul (not mounted)

RMAN> run{
%W2pBi.uA02> allocate auxiliary channel a1 type disk;
%pR]5}k%]1v2|+V03> allocate channel c1 type disk;ITPUB个人空间:?7SP[(\A^i
4> recover tablespace ts_test until time "to_date('2008-01-02 15:14:08','yyyy-mmITPUB个人空间!P6VD3J4x x5hm_
-dd hh24:mi:ss')";
x7K*~ YEpI*l05> }

allocated channel: a1
'R@ hDjS.z$A0channel a1: sid=10 devtype=DISK

allocated channel: c1
}+uA w\s{0channel c1: sid=9 devtype=DISK

Starting recover at 02-JAN-08

printing stored script. Memory ScriptITPUB个人空间6ws cw.p g5a9c3kT9~`
{ITPUB个人空间G;AR3D.G E
# set the until clauseITPUB个人空间/B
w1e3EC&C*b.y
set until  time "to_date('2008-01-02 15:14:08','yyyy-mm-dd hh24:mi:ss')";ITPUB个人空间{-QvHjyq*d
# restore the controlfileITPUB个人空间,[$WYsn!RLf)y/~
restore clone controlfile to clone_cf;ITPUB个人空间A c B b-R OE[IZ
# replicate the controlfile
U6o@;pDmO0replicate clone controlfile from clone_cf;
gX-Eq;_iF0# mount the controlfile
;L?+jv.X(X!ANf0sql clone 'alter database mount clone database';
0M-m c2u"xl0# archive current online log for tspitr to a resent until timeITPUB个人空间,tEZ_7_&o+fS/q&G
sql 'alter system archive log current';ITPUB个人空间+j\)DU1\0[
# avoid unnecessary autobackups for structural changes during TSPITRITPUB个人空间;`!l_l+p
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';ITPUB个人空间q;T(Mn%~N/n"};v
# resync catalog after controlfile restoreITPUB个人空间;`Qz;NA]M
resync catalog;ITPUB个人空间M)h3NMmXAJ
}
h{FwN!n:H0executing script. Memory Script

executing command: SET until clause

Starting restore at 02-JAN-08

channel a1: starting datafile backupset restore
x*S7O(^!LI0channel a1: restoring controlfileITPUB个人空间-e[R#ZK
output filename=D:\CONTROL02.CTLITPUB个人空间(Z{.o-I4xj!Pq7_0A
channel a1: restored backup piece 1
M
Z[o)Q"_G!E0piece handle=D:\BACKUP\C-1605165889-20080102-0D tag=null params=NULLITPUB个人空间F:I6fpov-AD
channel a1: restore completeITPUB个人空间n,?/`F.u5pX7N
Finished restore at 02-JAN-08

replicating controlfileITPUB个人空间}$O V1Q6u
input 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 catalogITPUB个人空间g
kjYl^w
full resync complete

printing stored script. Memory ScriptITPUB个人空间}7EfXB@1},dq_
{ITPUB个人空间u o.e
CP0[,z |6p}
# generated tablespace point-in-time recovery scriptITPUB个人空间A`]6V4R#A
# set the until clause
TQ:r#r@2tEB$D#Dn0set until  time "to_date('2008-01-02 15:14:08','yyyy-mm-dd hh24:mi:ss')";ITPUB个人空间 g'O"f:Q6tbR
plsql <<<-- tspitr_2ITPUB个人空间8Gc-BY8j$EX
declare
Hp#zP hr0  sqlstatement       varchar2(512);
-H5F9q'I"|K f k
I0  offline_not_needed exception;
+B"lr6M;gQm0  pragma exception_init(offline_not_needed, -01539);

f`D
\ lE:wJ0begin
3}j9`
eyY0  sqlstatement := 'alter tablespace '||  'TS_TEST' ||' offline for recover';
S5g N/L*M5C4K0  krmicd.writeMsg(6162, sqlstatement);
D)Ya&DI0  krmicd.execSql(sqlstatement);
5|8b:q8D/JT$k ^{0q9h^%B0exceptionITPUB个人空间Xfi&pr)z^0C F&e&d w
  when offline_not_needed thenITPUB个人空间F{F8U ]8FJ"h
    null;ITPUB个人空间v%p0T2|SyHcA
end; >>>;
!Rl?0E)i5~2Q_0# set a destination filename for restoreITPUB个人空间 k^'vx8S)L
set newname for datafile  1 to
I L-Zu{0 "D:\ORACLE\ORADATA\AUX1\SYSTEM01.DBF";
"DT/h*@2V_7BoS0# set a destination filename for restore
r ]V R4gk'o N0set newname for datafile  2 to

X
z.l{!mYp&h r0 "D:\ORACLE\ORADATA\AUX1\UNDOTBS01.DBF";ITPUB个人空间7^ kdO!gxw
# set a destination filename for restoreITPUB个人空间Y%fD x0pB4^?)g*O_
set newname for datafile  6 toITPUB个人空间+m(n*R0T-L9|
 "D:\ORACLE\ORADATA\PAUL\TS_TEST01.DBF";
1Ub2E0H&K(Bv0# restore the tablespaces in the recovery set plus the auxilliary tablespaces
\~IH0P"M0restore clone datafile  1, 2, 6;
#YNk ^f$Fb*A0switch clone datafile all;ITPUB个人空间J
@oN1^e
#online the datafiles restored or flippedITPUB个人空间Js.~A"Vep
sql clone "alter database datafile  1 online";
\L'r8X8N{.rBQ^0#online the datafiles restored or flipped
#inv7DEM0sql clone "alter database datafile  2 online";ITPUB个人空间0o3f)c-K.aC2X$rj
#online the datafiles restored or flipped
|O Io%h0sql clone "alter database datafile  6 online";ITPUB个人空间6B bw7aP&x
# make the controlfile point at the restored datafiles, then recover themITPUB个人空间$FwDeIk-L6ma
recover clone database tablespace  "TS_TEST", "SYSTEM", "UNDOTBS1";ITPUB个人空间Rz~:x:p#m
alter clone database open resetlogs;ITPUB个人空间2j#b Bp {O6R#f[
# PLUG HERE the creation of a temporary tablespace if export fails due to lackITPUB个人空间+aLW.G'F6u
# of temporary space.
t A5Di2YuM0# For example in Unix these two lines would do that:

Tr)q'nqx0#sql clone "create tablespace aux_tspitr_tmpITPUB个人空间.IJye+z oL8k
#           datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";ITPUB个人空间N;kf/SW1L&j
}
~ O1|%^2W%L0executing 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个人空间8u*d@D9HN$H"S
channel a1: specifying datafile(s) to restore from backup setITPUB个人空间-FlY%O2p
restoring datafile 00001 to D:\ORACLE\ORADATA\AUX1\SYSTEM01.DBF
-k%s L5?i0restoring datafile 00002 to D:\ORACLE\ORADATA\AUX1\UNDOTBS01.DBF
~P(K"e!vY0restoring datafile 00006 to D:\ORACLE\ORADATA\PAUL\TS_TEST01.DBFITPUB个人空间$kD
w'W+c@"cX+Xk'V
channel a1: restored backup piece 1
+@1s|!_7W X3y9j#|9U0piece handle=D:\BACKUP\4GJ55FO2_1_1 tag=TAG20080102T151058 params=NULL
leG$P1w[9yC0channel a1: restore completeITPUB个人空间+Y*@$wu4j rb;~/\
Finished restore at 02-JAN-08

datafile 6 switched to datafile copyITPUB个人空间2L
hH\Q bg,?+My
input datafilecopy recid=33 stamp=642958383 filename=D:\ORACLE\ORADATA\PAUL\TS_TITPUB个人空间tjX8ufM-j?Hx
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.DITPUB个人空间(Y1W.An#s-GZ(X`5q,f
BFITPUB个人空间 y L2TA;M;Qel
archive log filename=D:\ARCHPAUL\1_206.DBF thread=1 sequence=206ITPUB个人空间3Pn JD!?.H+|
media recovery complete
bj%l*?^cGc0Finished recover at 02-JAN-08

database opened

printing stored script. Memory Script
We|uQ:wT0{ITPUB个人空间&qc0q `kV$B&`kE
# export the tablespaces in the recovery set
[-@3[fZ1{|:L/D]0host 'exp userid =\"/@ as sysdba\" point_in_time_recover=y tablespaces=
#l-wp \:V0 TS_TEST file=
A$A*KF'OjY1P[0tspitr_a.dmp';ITPUB个人空间a%h4PYfYl`F
# shutdown clone before import
r:P#F6T^^ nI0shutdown clone immediate
#`s nV"D0# import the tablespaces in the recovery setITPUB个人空间p*F5wV},q!~ AW
host 'imp userid =\"sys/abcdefg@paulas sysdba\" point_in_time_recover=y file=
8^(K&k.Z
o9jz+J0tspitr_a.dmp';
"de t^7o,B+ZpE0# online/offline the tablespace imported
7a(cb'rrv
F0sql "alter tablespace  TS_TEST online";
g%V#[Hwb0sql "alter tablespace  TS_TEST offline";ITPUB个人空间Hk` };},UY4A
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
]._$[0O"e;J_0sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
Vcr/dB}0# resync catalog after tspitr finished
;mL
p7e b Q1?z|]
W0resync catalog;ITPUB个人空间J8pIoB I
}
2Zp;@kRZ0executing script. Memory Script

ITPUB个人空间c4[
[-mh
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.

-iC
O(xAJ0Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionITPUB个人空间O C~lY4b7FF2\X,O
With the Partitioning, OLAP and Oracle Data Mining options
"uv.dO-f'\+t0JServer Release 9.2.0.4.0 - ProductionITPUB个人空间E4^S!p]@Z
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
5p(Ss-ld$oZi6@E:{2Q0Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
]3zm$RM0For tablespace TS_TEST ...
l eOFY%COP0. exporting cluster definitions
4Uj4UR0r&C5f3t'i0. exporting table definitionsITPUB个人空间RR ~G}&?L[8y O
. . exporting table                            STUITPUB个人空间q@`}Q%|s$w
. exporting referential integrity constraints
&OZ3[H&oT/H(O6G:X0. exporting triggers
/d d&c&xDG6v,T0. end point-in-time recovery
!v ?F2@,E/_0Export terminated successfully without warnings.
S4Yz[NJe&H+[0host command complete

database closed
^:O8am9Gm!H0database dismounted
tDG QJ @s3A8G0Oracle instance shut down

Fvv7L&D,i0Import: Release 9.2.0.4.0 - Production on Wed Jan 2 15:33:26 2008

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

ITPUB个人空间M-_/Q+v Bl([r
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionITPUB个人空间6Y y5n%sw D
With the Partitioning, OLAP and Oracle Data Mining optionsITPUB个人空间|lJ2Sg |E
JServer Release 9.2.0.4.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
7D~[h#fv0About to import Tablespace Point-in-time Recovery objects...ITPUB个人空间.E4UT0`8s7yb D
import done in ZHS16GBK character set and AL16UTF16 NCHAR character setITPUB个人空间S1Y@.W#R^
G
. importing TEST's objects into TESTITPUB个人空间y1L3]qL#d
. . importing table                          "STU"
5zdQh/|tH0. importing SYS's objects into SYSITPUB个人空间5TiN B(}t
Import terminated successfully without warnings.
%D8o/S9M3yB#U"W?'A"tj0host 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个人空间KwU|6c/F-Tx0R
full resync complete
|$[{-iZQW5{ Zf0Finished recover at 02-JAN-08ITPUB个人空间,n2M3_;WQk
released channel: c1

RMAN>

{y#ZJ2^0C:\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个人空间Crp?&DP8wA!vA^
Connected to:ITPUB个人空间d6`p:dn?
Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionITPUB个人空间9X{8r!m8la-tt
f5^O:L2~
With the Partitioning, OLAP and Oracle Data Mining optionsITPUB个人空间CDo:Y+G
JServer Release 9.2.0.4.0 - Production

SQL> alter tablespace ts_test online;

Tablespace altered.

SQL> conn test/test
4[*d3B#p xFV0Connected.
.z]MPC1h!epUh0SQL> select * from stu;

        NO NAMEITPUB个人空间9]h5]QJddv1e
---------- --------------------ITPUB个人空间@5W}-mE(mz
       109 abcdITPUB个人空间e i:| U$VPU7V5p#M
      ITPUB个人空间n)nk"A^L^
      至此，已经恢复