rman表空间基于时间点的不完全恢复
上一篇 / 下一篇 2008-01-02 15:45:49 / 个人分类:rman备份与恢复
数据库版本:9204
wTD't2go\z{,zV
m0目标数据库名称 paul
bhWT4XV1ajy;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个人空间"Vhi
LrJe*p*{N
channel ORA_DISK_1: sid=13 devtype=DISKITPUB个人空间zUbc!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&fzG*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_pJ y;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 ^tp
Y$l.Y
With the Partitioning, OLAP and Oracle Data Mining options
,N9lv~#X)}:}0JServer Release 9.2.0.4.0 - ProductionITPUB个人空间Z t(^"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
lb1N5ZI0SQL> 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
备份控制文件
([nODx;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(e6Q1ZdY-`0-------------------
.h |zuG j?9m02008-01-02 15:14:08
SQL> drop table stu;
Table dropped.
3.为辅助实例创建初始化参数文件ITPUB个人空间'O;fj?)l
新建 d:\oracle\oradata\aux1 目录
3}#]JBr3g.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;{'gRF0VTpm
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.F3C T/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 Kwt$X\Q
SQL> startup nomountITPUB个人空间3G\Z.|I6ZLM
Total System Global Area 101785252 bytes
H a }$dW ? K u.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/
3Yqq!])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{
%W2pBi.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个人空间/Bw1e3EC&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个人空间,t EZ_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!P q7_0A
channel a1: restored backup piece 1
MZ[o)Q"_G!E0piece handle=D:\BACKUP\C-1605165889-20080102-0D tag=null params=NULLITPUB个人空间F:I6fpo v-A D
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个人空间gkj Yl^w
full resync complete
printing stored script. Memory ScriptITPUB个人空间}7EfXB@1},dq_
{ITPUB个人空间u
o.eCP0[,z
|6p}
# generated tablespace point-in-time recovery scriptITPUB个人空间A`]6V4R#A
# set the until clause
TQ:r#r@2tEB$D#Dn0set 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 kI0 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 ]8FJ"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
Xz.l{!mYp&h
r0 "D:\ORACLE\ORADATA\AUX1\UNDOTBS01.DBF";ITPUB个人空间7^
kdO!gx w
# 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'n qx0#sql clone "create tablespace aux_tspitr_tmpITPUB个人空间.IJye+z
oL8k
# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";ITPUB个人空间N;kf/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个人空间$kDw'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*@$wu4j rb;~/\
Finished restore at 02-JAN-08
datafile 6 switched to datafile copyITPUB个人空间2LhH\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 JD!?.H+|
media recovery complete
bj%l*?^cGc0Finished recover at 02-JAN-08
database opened
printing stored script. Memory Script
We|uQ:wT0{ITPUB个人空间&qc0q
`kV$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