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

数据库版本：9204ITPUB个人空间.Pk!U!jZ f+p-[f
目标数据库名称 paul
-g;a fZ;rN:dC0辅助实例 AUX1

ITPUB个人空间'y4BjV3Y d$y%~g+`:Y
1.备份数据库用带恢复目录的模式ITPUB个人空间5H,D,^3kt%e5NY/p
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)
lcuD v_:v3lg0connected to recovery catalog database

RMAN> backup database;

Starting backup at 02-JAN-08ITPUB个人空间}2mXIB6e
starting full resync of recovery catalog
/~ b;[7t R4x'z-m0full resync completeITPUB个人空间YZ*vmd|
allocated channel: ORA_DISK_1ITPUB个人空间^:F&XQ2\h#pi
channel ORA_DISK_1: sid=13 devtype=DISK
,Cty
{7Q/l&_i0channel ORA_DISK_1: starting full datafile backupset
NuK#Bh8U)sL S3o6r:^0channel ORA_DISK_1: specifying datafile(s) in backupset
4xZ"L4r{}0input datafile fno=00001 name=D:\ORACLE\ORADATA\PAUL\SYSTEM01.DBF
.R9sE` D0input datafile fno=00002 name=D:\ORACLE\ORADATA\PAUL\UNDOTBS01.DBF
(s!dJ`B?z\+a0input datafile fno=00003 name=D:\ORACLE\ORADATA\PAUL\INDX01.DBFITPUB个人空间.||:A{.|P(ss
input datafile fno=00005 name=D:\ORACLE\ORADATA\PAUL\USERS01.DBF
N0Fa*u1L6A3qT0input datafile fno=00004 name=D:\ORACLE\ORADATA\PAUL\TOOLS01.DBF
7~G"JO5PbA
C:?0channel ORA_DISK_1: starting piece 1 at 02-JAN-08
/T(Y#E4I{5U+Vu/H1O5B0channel ORA_DISK_1: finished piece 1 at 02-JAN-08ITPUB个人空间kZ%qh1~YT
piece handle=D:\BACKUP\4BJ55C61_1_1 comment=NONE
:E5E[7M6AJ#W0channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55

svhqx,SN:p*Z0lm0Finished backup at 02-JAN-08

Starting Control File and SPFILE Autobackup at 02-JAN-08
%t H*\$i Tk3A0piece handle=D:\BACKUP\C-1605165889-20080102-09 comment=NONEITPUB个人空间i8m5o7r m0Nk2rL%Y1eS
Finished Control File and SPFILE Autobackup at 02-JAN-08

RMAN>

2在paul数据库表空间ts_test下创建测试用户ITPUB个人空间)Mf@w$S h/Vb;r
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.

9B9MB2i(nq!pe0Connected to:ITPUB个人空间+L*uJ"p[?h&zl
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
#dQb"c+e+c*h0With the Partitioning, OLAP and Oracle Data Mining optionsITPUB个人空间9O$iLY'GLv#t
JServer Release 9.2.0.4.0 - Production
6fxNOe$f0sql> 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
s+At:\0y0Connected.ITPUB个人空间&e#Go [G9wP*Y1D
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个人空间Z6Xw*NP\-d(J
---------- --------------------
$~:I:x{lq q.l0       109 abcd

SQL> commit;

Commit complete.

SQL> conn /as sysdbaITPUB个人空间5CbB)P8jO3O
Connected.ITPUB个人空间V4zZ w(_|@~
备份控制文件ITPUB个人空间[H%Z0NF
SQL> alter database backup controlfile to 'd:\control02.ctl' reuse;

Database altered.

SQL>

SQL> conn test/testITPUB个人空间:RO7EPQ7`H[ i{
Connected.ITPUB个人空间 Ove:_(k5e;@'X
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERIDITPUB个人空间GS[#aE7br u ?
------------------------------ ------- ----------ITPUB个人空间{8~6mhF ]S2ge
STU                               TABLE

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

Session altered.

记住删除此表的时刻是ITPUB个人空间0|0y5J4E@;Q6F
SQL> select sysdate from dual;

SYSDATE
m5hU~F8Zk.m0-------------------ITPUB个人空间&h1B7q(U~C/~
2008-01-02 15:14:08

SQL> drop table stu;

Table dropped.

3.为辅助实例创建初始化参数文件ITPUB个人空间aV]bWw8dYC
新建 d:\oracle\oradata\aux1 目录
#A3Zs:b/pi:_*~0复制d:\oracle\ora92\database\INITpaul.ora 为initaux1.ora文件ITPUB个人空间b fs Z3cVJ
然后修改其内容，将实例名字修改为：aux1, 添加lock_name_space=AUX1，修改control_files路径为：
kpf2c4w@0control_files='d:\control02.ctl'ITPUB个人空间
t
TVa%Z\S}
修改log_archive_start=false
;l)R-iWJaSh,tH@0辅助实例必须为非归档模式。
DZ K](VH*\}0添加如下两个参数：
~V&B#Hy7B&dU"y0db_file_name_convert=("d:\oracle\oradata\paul", "d:\oracle\oradata\aux1")ITPUB个人空间
c|b A'f;M(J6yMM
log_file_name_convert=("d:\oracle\oradata\paul", "d:\oracle\oradata\aux1")

4. 创建辅助实例服务 用oradmin工具ITPUB个人空间:j/o~R;v;T3C$g2l
C:\Documents and Settings\Paul Yi>oradim -new -sid aux1 -intpwd abcdefg -startmode manualITPUB个人空间8fKEB.H?{,gK~)[i
 ITPUB个人空间0}f?? x
5.启动辅助实例到nomount状态ITPUB个人空间|2_2k!R)F _0{z
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.
5g
Z1}["X.u;D gZ0SQL> create spfile from pfile;ITPUB个人空间n;x!k H;?o
file created
a_!J%O;[Q,wJt0SQL> startup nomountITPUB个人空间[D*o)T+Y"`g+h
Total System Global Area  101785252 bytesITPUB个人空间z/g"^ @Q0sg
Fixed Size                   454308 bytesITPUB个人空间yVG|_.og
Variable Size              75497472 bytes
C8Z\(R ~P0Database Buffers           25165824 bytes
P^-_+\`Z a,HM;l
X$U0Redo Buffers                 667648 bytes

6.开始表空间不完全恢复
`Rp9S {5PIsp0C:\Documents and Settings\Paul Yi>set oracle_sid=aux1

C:\Documents and Settings\Paul Yi>rman targetsys/abcdefg@paulcatalog paultest/
_:|JB8u*vk O0paultest@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个人空间Q Ldi"Bw
connected to recovery catalog databaseITPUB个人空间AmP V7Jb7B0mB#F
connected to auxiliary database: paul (not mounted)

RMAN> run{
D'j x}-?(r(]I-Q02> allocate auxiliary channel a1 type disk;ITPUB个人空间t:C3m~ i*^%t
3> allocate channel c1 type disk;
W4b_YHp't04> recover tablespace ts_test until time "to_date('2008-01-02 15:14:08','yyyy-mmITPUB个人空间O
v~
ca7a N2mE
-dd hh24:mi:ss')";
~|!So9n.`A8T5E
X05> }

allocated channel: a1
OlI;npc/I/O GWj%g;m%I0channel a1: sid=10 devtype=DISK

allocated channel: c1ITPUB个人空间I2tv t4T@
channel c1: sid=9 devtype=DISK

Starting recover at 02-JAN-08

printing stored script. Memory Script
7],y%i9Tvs(K[S UD0{
"kw$`$wE.s7S2VO0# set the until clause
EUD(CM8d6h Q'E K"L0set until  time "to_date('2008-01-02 15:14:08','yyyy-mm-dd hh24:mi:ss')";
Zbm.u:Fz0# restore the controlfileITPUB个人空间#~ d3z.E
cSb
restore clone controlfile to clone_cf;ITPUB个人空间j&L BA!d3h y!q t
# replicate the controlfileITPUB个人空间P5Qa!Py2f+U+e
replicate clone controlfile from clone_cf;
f-F.hc@U7C0wn0# mount the controlfile
G[LDE-R){0sql clone 'alter database mount clone database';
+[2l8_ d(gH0# archive current online log for tspitr to a resent until timeITPUB个人空间y_-M6[&Pr;B A7{
sql 'alter system archive log current';ITPUB个人空间$HY&{X]({7W
# avoid unnecessary autobackups for structural changes during TSPITR
E3g^VSS A_/x0sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
u-cn`)A hI0# resync catalog after controlfile restore
"WLf:xE4Ad5?0resync catalog;
#xi:JZ#x a F0}
N'{zjV*BqR0executing script. Memory Script

executing command: SET until clause

Starting restore at 02-JAN-08

channel a1: starting datafile backupset restore
p d8[.TC*KSzG0channel a1: restoring controlfileITPUB个人空间OLzWP'k@4q
output filename=D:\CONTROL02.CTL
5e b;B!D$\K1QW \ qhR0channel a1: restored backup piece 1
PH0x6B-_#G0piece handle=D:\BACKUP\C-1605165889-20080102-0D tag=null params=NULL
XA0k6a#z^'Ag`0channel a1: restore complete
NU6`a&m Y0Finished restore at 02-JAN-08

replicating controlfile
@Ysz:@Q$r0input 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个人空间 mAb7?Wr
B\ W
full resync complete

printing stored script. Memory ScriptITPUB个人空间U8ai+{#yfwV
{
;s uy
a;T"Z\0# generated tablespace point-in-time recovery scriptITPUB个人空间"Pta#A#q{8J
# set the until clauseITPUB个人空间_1l^xp'w2c X/LdTA
set until  time "to_date('2008-01-02 15:14:08','yyyy-mm-dd hh24:mi:ss')";
8a
ky,D!SLP[0plsql <<<-- tspitr_2ITPUB个人空间H-L.o,z!A'mky
declareITPUB个人空间0n9UW%U-z%w
  sqlstatement       varchar2(512);
!QF l+yPz'F1J.YnF0  offline_not_needed exception;
]
eZ T`l~!w/]0  pragma exception_init(offline_not_needed, -01539);ITPUB个人空间.crW'Ox9\6L[T
begin
J~W8hW/~0  sqlstatement := 'alter tablespace '||  'TS_TEST' ||' offline for recover';

I:tvh T pAE/z0  krmicd.writeMsg(6162, sqlstatement);ITPUB个人空间 @,} F gb$E$D!t1M B,B
  krmicd.execSql(sqlstatement);ITPUB个人空间K'j|e8U$ibK
exceptionITPUB个人空间S)iQ)t2P-X f
  when offline_not_needed thenITPUB个人空间f ge;c [ ]v
F,q
    null;
a8KUKO[0end; >>>;ITPUB个人空间aZJ]mR
# set a destination filename for restoreITPUB个人空间3XF3z,O2JJ
set newname for datafile  1 toITPUB个人空间qRR|jwR,l,`
 "D:\ORACLE\ORADATA\AUX1\SYSTEM01.DBF";
r!{;C};L@3I-J0# set a destination filename for restore
g
G U1F*]3H8D+H0set newname for datafile  2 toITPUB个人空间 ae/|m)UD5z
 "D:\ORACLE\ORADATA\AUX1\UNDOTBS01.DBF";
F9a:M+{Xq o#{0# set a destination filename for restore
1f)_%D8^IjV+Q0set newname for datafile  6 toITPUB个人空间2^-\M$s2m
 "D:\ORACLE\ORADATA\PAUL\TS_TEST01.DBF";
@.A*r'v W0# restore the tablespaces in the recovery set plus the auxilliary tablespacesITPUB个人空间 e sd8C(A^
restore clone datafile  1, 2, 6;ITPUB个人空间 x}G2b M
switch clone datafile all;ITPUB个人空间_$Q%y4\/k D5vnE
#online the datafiles restored or flipped
GTvt9[p[0sql clone "alter database datafile  1 online";ITPUB个人空间7|8iZ?Q EKk
#online the datafiles restored or flipped
pd{FF.p{1b0sql clone "alter database datafile  2 online";ITPUB个人空间6r(~DMrH"mXD _ Lz
#online the datafiles restored or flipped
*^Ro*A+sr0sql clone "alter database datafile  6 online";ITPUB个人空间+k ~
Y!W,B
# make the controlfile point at the restored datafiles, then recover them
sC l[Ja-o0recover clone database tablespace  "TS_TEST", "SYSTEM", "UNDOTBS1";ITPUB个人空间#u:]&b5c ^_
alter clone database open resetlogs;
%Lf1TX&_q$X0# PLUG HERE the creation of a temporary tablespace if export fails due to lack
Rb/y0K;H h1O0# of temporary space.ITPUB个人空间K6F UJ*W]I
# For example in Unix these two lines would do that:
\/b,D_-j3J0#sql clone "create tablespace aux_tspitr_tmp
k._ L^s)F:^0#           datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
M4rdR%N2z
["z7S&s0}ITPUB个人空间I_!hF`iU~v9~
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 restore
uA6{%qt"R0channel a1: specifying datafile(s) to restore from backup set
}`!hX^0restoring datafile 00001 to D:\ORACLE\ORADATA\AUX1\SYSTEM01.DBFITPUB个人空间zGjj c+O7I
^$L
restoring datafile 00002 to D:\ORACLE\ORADATA\AUX1\UNDOTBS01.DBF
X(VC.xa0restoring datafile 00006 to D:\ORACLE\ORADATA\PAUL\TS_TEST01.DBF
8dH,_#?2~e0channel a1: restored backup piece 1
NX+NWf9pDZ(f)I+p0piece handle=D:\BACKUP\4GJ55FO2_1_1 tag=TAG20080102T151058 params=NULLITPUB个人空间'w:KJ`$A1Lam&P
channel a1: restore complete
3g;`-f"m:d{0Finished restore at 02-JAN-08

datafile 6 switched to datafile copy
\%qiUl_8N)Z` ?.U@ eN0input datafilecopy recid=33 stamp=642958383 filename=D:\ORACLE\ORADATA\PAUL\TS_T
EI-o"o @[%ru0EST01.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
_(G6y8W3JG],yv0BFITPUB个人空间5HZ`7o8q;P
archive log filename=D:\ARCHPAUL\1_206.DBF thread=1 sequence=206
l%FUW6q0media recovery completeITPUB个人空间l$q;j U:W%y
Finished recover at 02-JAN-08

database opened

printing stored script. Memory Script
*b)|G E.}3J.p0{ITPUB个人空间^eQ8M4KmW2sB$O
# export the tablespaces in the recovery setITPUB个人空间0s j,e"F-VDy;Uw:wn
host 'exp userid =\"/@ as sysdba\" point_in_time_recover=y tablespaces=
+?
@3Yb1]A0 TS_TEST file=
lE/j W"`7Q"{T"v0tspitr_a.dmp';
3JV'b"kGn xt0# shutdown clone before importITPUB个人空间)nK Jw
i4Ou*Q'o
shutdown clone immediateITPUB个人空间"X'RI6cW%@ Z
# import the tablespaces in the recovery set
5b\(@2BA#mqP0host 'imp userid =\"sys/abcdefg@paulas sysdba\" point_in_time_recover=y file=
~/CH*Dem{\ET3H0tspitr_a.dmp';
"{orw8H(iT:n0# online/offline the tablespace imported
T8q*s9^f3l5{LM0sql "alter tablespace  TS_TEST online";ITPUB个人空间!p&d6t m:^
Y)\R N
sql "alter tablespace  TS_TEST offline";ITPUB个人空间0B[i;k1F,Pf
# enable autobackups in case user does open resetlogs from RMAN after TSPITRITPUB个人空间*kZmI S,A.d
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';ITPUB个人空间kZV
a6|'lc|H
# resync catalog after tspitr finishedITPUB个人空间%K4ro0Ef
resync catalog;
*y%mTGbs;D0}ITPUB个人空间n(~f/~A {^e
executing script. Memory Script

Af,?*]XL'?wM"JA0Export: 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个人空间.t%ez7N S^
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionITPUB个人空间0{K%|.Au9x:[5Hy7g
With the Partitioning, OLAP and Oracle Data Mining optionsITPUB个人空间6t%s:F"aN3[5Tvl;eGC
JServer Release 9.2.0.4.0 - Production
qy6Fv8P J|A0s0Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
2bAse3~3OZ_u+]0Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...ITPUB个人空间2e
dF^"C K/f
For tablespace TS_TEST ...
Cqqy o/H+o,J0. exporting cluster definitionsITPUB个人空间[u^| OBB
. exporting table definitions
b(Li3hsy:}0. . exporting table                            STUITPUB个人空间}JD~8FZD
. exporting referential integrity constraintsITPUB个人空间A,l r7u4Z&t1w
. exporting triggersITPUB个人空间9X-|i!U:l;^{'o
. end point-in-time recovery
%y t?-g4@U(Z$Z*w2f$Z0Export terminated successfully without warnings.ITPUB个人空间1]f*a??n
{
host command complete

database closed
S2EM3C6S0database dismountedITPUB个人空间 GIK]+vWW7f&B
Oracle instance shut down

d_ag?6V0Import: Release 9.2.0.4.0 - Production on Wed Jan 2 15:33:26 2008

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

y9dO'q*I$t`0w#v0Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
4V$ywi;qf0With the Partitioning, OLAP and Oracle Data Mining options
MtK#@e M~'iO0JServer Release 9.2.0.4.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
k1w6l \8D4]p%Jz^0About to import Tablespace Point-in-time Recovery objects...
sJ)OSeO0import done in ZHS16GBK character set and AL16UTF16 NCHAR character setITPUB个人空间2BJc6c_.QP
. importing TEST's objects into TEST
;dUM'W~N^(qO0. . importing table                          "STU"ITPUB个人空间t(mvV$|O(\
. importing SYS's objects into SYSITPUB个人空间N D.y1R_9sS
Import terminated successfully without warnings.
~wO9OQ)^u0host 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个人空间%b W:d5N!I,K4Y
PI|J
full resync complete
Ar^ Bmyo0Finished recover at 02-JAN-08
P
?6h Ts0released channel: c1

RMAN>

;nAaS([1N1S0C:\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.

r,Mzr!K;`)Y O0Connected to:
T+B']f!z,O4s0Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionITPUB个人空间E+Q*K|-W9I1a,{2XB5p
With the Partitioning, OLAP and Oracle Data Mining optionsITPUB个人空间(ESG_K
JServer Release 9.2.0.4.0 - Production

SQL> alter tablespace ts_test online;

Tablespace altered.

SQL> conn test/testITPUB个人空间4m&d+C5t$JP5b
Connected.ITPUB个人空间^St*r%[:h
SQL> select * from stu;

        NO NAMEITPUB个人空间)^|6O)y4]M
---------- --------------------ITPUB个人空间E&I+H*W1Y5d1d
       109 abcd
x:en1yg.z0      ITPUB个人空间d9[9?0`wk
      至此，已经恢复