rman表空间基于时间点的不完全恢复
上一篇 / 下一篇 2008-01-02 15:45:49 / 个人分类：rman备份与恢复
数据库版本：9204
!W#W.| \(A+G;{[5|*kQ0目标数据库名称 paul
1g}9MW2f-F:Tf&k}C t0辅助实例 AUX1
ITPUB个人空间PWO(C
N!h
1.备份数据库用带恢复目录的模式ITPUB个人空间h}L:X k W
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个人空间3h7q`;qn J&z
connected to recovery catalog database
RMAN> backup database;
Starting backup at 02-JAN-08ITPUB个人空间,_!X5g |{ m
starting full resync of recovery catalog
|G0JR U0full resync complete
T.h3?1Q[(e P-v0allocated channel: ORA_DISK_1ITPUB个人空间ja3e h
Sx
channel ORA_DISK_1: sid=13 devtype=DISK
g6s z x(n*O9sM0channel ORA_DISK_1: starting full datafile backupset
z'H3p*WTMh3E#\9Ac}9w0channel ORA_DISK_1: specifying datafile(s) in backupsetITPUB个人空间3E0|jL9}0n
y,S
input datafile fno=00001 name=D:\ORACLE\ORADATA\PAUL\SYSTEM01.DBFITPUB个人空间9N/DH/bT Y'a$C
input datafile fno=00002 name=D:\ORACLE\ORADATA\PAUL\UNDOTBS01.DBF
5hp$yfgO*r y4r0input datafile fno=00003 name=D:\ORACLE\ORADATA\PAUL\INDX01.DBFITPUB个人空间.IiGu0^ j
input datafile fno=00005 name=D:\ORACLE\ORADATA\PAUL\USERS01.DBFITPUB个人空间 a hWZ D4D
input datafile fno=00004 name=D:\ORACLE\ORADATA\PAUL\TOOLS01.DBF
!C+O,C7X2{ Ss*w0channel ORA_DISK_1: starting piece 1 at 02-JAN-08ITPUB个人空间6@Qgi q`*f
channel ORA_DISK_1: finished piece 1 at 02-JAN-08
8f6fLLF(g0piece handle=D:\BACKUP\4BJ55C61_1_1 comment=NONE
U8yscL.Ih0channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55ITPUB个人空间 |,Z3P5w Z4v C h
Finished backup at 02-JAN-08
Starting Control File and SPFILE Autobackup at 02-JAN-08
\-Qy O
@3[2G0piece handle=D:\BACKUP\C-1605165889-20080102-09 comment=NONEITPUB个人空间+Hs~B;vO\#n
Finished Control File and SPFILE Autobackup at 02-JAN-08
RMAN>
2在paul数据库表空间ts_test下创建测试用户ITPUB个人空间BP8V y9~ J3u
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.
#d!A&D ` I!Z:m0Connected to:ITPUB个人空间#t2R;p} q,y6`
Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionITPUB个人空间C1u'O)D
e+{
With the Partitioning, OLAP and Oracle Data Mining options
;_udr Y?mZ0JServer Release 9.2.0.4.0 - Production
xh
o)V W&IV#u[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
,@+P'tE Lfw0zk0Connected.ITPUB个人空间i E/@q r
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个人空间n mi1I
IeG)H5p
---------- --------------------ITPUB个人空间-W_7zT\~1[1R
109 abcd
SQL> commit;
Commit complete.
SQL> conn /as sysdbaITPUB个人空间 h1Z'z#r#iI l0P2x o
Connected.ITPUB个人空间 A*@[^:^|i#~6G
备份控制文件
V9tf-yj}&I0SQL> alter database backup controlfile to 'd:\control02.ctl' reuse;
Database altered.
SQL>
SQL> conn test/test
B3B_JI(pV,{ m0Connected.ITPUB个人空间 N(t.n._H3O!O5DT1k,O
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
4| Wn [ P;v$B%SjF0------------------------------ ------- ----------ITPUB个人空间2ywl qqN%z
STU TABLE
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
记住删除此表的时刻是ITPUB个人空间 qc x\{#W6L
SQL> select sysdate from dual;
SYSDATE
9rHA N%b2a0-------------------ITPUB个人空间`TX
b ZZ
2008-01-02 15:14:08
SQL> drop table stu;
Table dropped.
3.为辅助实例创建初始化参数文件ITPUB个人空间~ A?!M7e
A
新建 d:\oracle\oradata\aux1 目录ITPUB个人空间E"h V8e/i5Cvg
复制d:\oracle\ora92\database\INITpaul.ora 为initaux1.ora文件ITPUB个人空间.{ V3Y/E/V0D8Z2f
然后修改其内容，将实例名字修改为：aux1, 添加lock_name_space=AUX1，修改control_files路径为：ITPUB个人空间I$Q L@x2h7V-f6K
control_files='d:\control02.ctl'
Q{.N F7B3P.j:kx0修改log_archive_start=false
SO.t$b0U"H;X W4B}^0辅助实例必须为非归档模式。
%|$\i(LQ"L0添加如下两个参数：ITPUB个人空间-P]/Y$Ffpk M t"`
db_file_name_convert=("d:\oracle\oradata\paul", "d:\oracle\oradata\aux1")ITPUB个人空间;z z.ah
^J
log_file_name_convert=("d:\oracle\oradata\paul", "d:\oracle\oradata\aux1")
4. 创建辅助实例服务 用oradmin工具ITPUB个人空间EJ T2}"f"Dd T
C:\Documents and Settings\Paul Yi>oradim -new -sid aux1 -intpwd abcdefg -startmode manualITPUB个人空间/H!s:p(o @8`,q;}om%]M
ITPUB个人空间r{9j3p
l
5.启动辅助实例到nomount状态ITPUB个人空间2p I4L/iRr7p
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.
B{zY8?{?d0SQL> create spfile from pfile;ITPUB个人空间I#F.p`}i$ra"aQ
file created
H#sL]}I$|il0SQL> startup nomount
'a4M-L&Gj3~3V\/j0Total System Global Area 101785252 bytes
kd O*[,cm |"I5B:?m0Fixed Size 454308 bytesITPUB个人空间 j:es'uj$u
Variable Size 75497472 bytes
v]y }*Br.ECR0Database Buffers 25165824 bytes
+|g\}6[0Redo Buffers 667648 bytes
6.开始表空间不完全恢复ITPUB个人空间P ^#N }\o
C:\Documents and Settings\Paul Yi>set oracle_sid=aux1
C:\Documents and Settings\Paul Yi>rman targetsys/abcdefg@paulcatalog paultest/ITPUB个人空间A&ZFPo [ H7` i
paultest@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个人空间 hG p:Y@%Ck
connected to recovery catalog database
e!i;j M)klB;e6w0Z&q0connected to auxiliary database: paul (not mounted)
RMAN> run{ITPUB个人空间c3x@ p]v$c)B
2> allocate auxiliary channel a1 type disk;ITPUB个人空间:V ji AEqGG
3> allocate channel c1 type disk;ITPUB个人空间I|x/v]}
4> recover tablespace ts_test until time "to_date('2008-01-02 15:14:08','yyyy-mmITPUB个人空间%h;V5^;Y
k3\~
-dd hh24:mi:ss')";ITPUB个人空间~)B Q b%wmu
5> }
allocated channel: a1
9EJ K%G K|+B W.H*e0channel a1: sid=10 devtype=DISK
allocated channel: c1ITPUB个人空间!H8sD:j P ov"^
channel c1: sid=9 devtype=DISK
Starting recover at 02-JAN-08
printing stored script. Memory Script
4A l e{`"q$h b1u/B?\0{
{ JK M9H4u9O0# set the until clause
"_[;r ho8j w @m0set until time "to_date('2008-01-02 15:14:08','yyyy-mm-dd hh24:mi:ss')";ITPUB个人空间2h!cE"b#L(|R5T2f
# restore the controlfile
| e-KGa.Y;E0restore clone controlfile to clone_cf;
F%M1G.gsU&a8AN0# replicate the controlfileITPUB个人空间6Z:O.Hjo8V {
replicate clone controlfile from clone_cf;ITPUB个人空间8d-Z,b^X3c
# mount the controlfile
he0r6PvuD4E0mY0sql clone 'alter database mount clone database';
Z&uV!rSiG X A6s0# archive current online log for tspitr to a resent until time
o9X5Qes#z
} ]0sql 'alter system archive log current';
)A d R:u3~R0# avoid unnecessary autobackups for structural changes during TSPITRITPUB个人空间 M(l$u;hF?:yG._
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';ITPUB个人空间 hVd gf7o
# resync catalog after controlfile restoreITPUB个人空间X`&pdX5}
resync catalog;ITPUB个人空间/e b(_CK#@
k6P5l
}ITPUB个人空间8h)T*R$t)m W
executing script. Memory Script
executing command: SET until clause
Starting restore at 02-JAN-08
channel a1: starting datafile backupset restore
0o'JWxd,F hLc6aC0channel a1: restoring controlfileITPUB个人空间8L ]
GpxO
output filename=D:\CONTROL02.CTL
O~ fjBl&C,l0channel a1: restored backup piece 1ITPUB个人空间@C U$YW"Y;e
piece handle=D:\BACKUP\C-1605165889-20080102-0D tag=null params=NULLITPUB个人空间E s J8L^,r7A
channel a1: restore completeITPUB个人空间z)T7f*c;?;CK8k
Finished restore at 02-JAN-08
replicating controlfile
2~ ?:i6f;{oBS;s0input 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
,w d s.^af#~ bhz'm
K%P0full resync complete
printing stored script. Memory Script
SD;H/dZ#H8\&z9u3c0{
tDkH
B W C0# generated tablespace point-in-time recovery script
:Mn9@!}Z@4m0# set the until clause
b8?'M/bJ%gg.{1r
d"t0set until time "to_date('2008-01-02 15:14:08','yyyy-mm-dd hh24:mi:ss')";
^g%w"^ U0plsql <<<-- tspitr_2
%{Z X_ _vnu0declare
.c+y.S C$l bS&~&?"Ml0 sqlstatement varchar2(512);ITPUB个人空间Y
f&TN[;V(eP:~@k/n
offline_not_needed exception;
U!Cu!N0F-E3h/y/j,V0 pragma exception_init(offline_not_needed, -01539);
-xE}`?8`#S/r(N~0beginITPUB个人空间})h*Q(?-w3FpA
sqlstatement := 'alter tablespace '|| 'TS_TEST' ||' offline for recover';ITPUB个人空间
A |#@ O v
krmicd.writeMsg(6162, sqlstatement);
g)r"p6X Iu0 krmicd.execSql(sqlstatement);
M` x:d%];H0exceptionITPUB个人空间p*t;I9I(x6o7vv:Q
when offline_not_needed then
`gc dFS&HDu0 null;ITPUB个人空间qFMH [ W(i*I
end; >>>;ITPUB个人空间] Nj7\5~B:V
s*I
# set a destination filename for restore
L%L S [W])L0set newname for datafile 1 to
.Z,M EuJ.JH
u]0 "D:\ORACLE\ORADATA\AUX1\SYSTEM01.DBF";ITPUB个人空间!R-@ ^8X"[XO(@L
# set a destination filename for restoreITPUB个人空间'y b0m:{9O$`
set newname for datafile 2 to
/uL,rB6_ | T0 "D:\ORACLE\ORADATA\AUX1\UNDOTBS01.DBF";ITPUB个人空间-Q MOfZ@.~W/@7Y
# set a destination filename for restoreITPUB个人空间;b!X1K&ep q2Y*wj1a
set newname for datafile 6 to
)m1Jin"F2|0 "D:\ORACLE\ORADATA\PAUL\TS_TEST01.DBF";
,ir.`c9@}0# restore the tablespaces in the recovery set plus the auxilliary tablespacesITPUB个人空间.?ly2Ey(u#s
restore clone datafile 1, 2, 6;
2O j$M.l%t1gG;~0switch clone datafile all;
`9YF^*d$t"_ j6A0#online the datafiles restored or flipped
%zd4uz8V\a G0sql clone "alter database datafile 1 online";ITPUB个人空间8q\UW,X3@%p+L
#online the datafiles restored or flipped
O-uK;w0J"]PDu0sql clone "alter database datafile 2 online";ITPUB个人空间GE N)W4NW
l)K8rf
#online the datafiles restored or flipped
k
I'_(k#Q m4k N-M0sql clone "alter database datafile 6 online";
Pq)Dn h ?0# make the controlfile point at the restored datafiles, then recover themITPUB个人空间/S~@3|:t1x
recover clone database tablespace "TS_TEST", "SYSTEM", "UNDOTBS1";ITPUB个人空间4{ r)tz ^w+wO
alter clone database open resetlogs;
7LSy5p%M0# PLUG HERE the creation of a temporary tablespace if export fails due to lackITPUB个人空间+C/[8a
v!r5m
B1];N
# of temporary space.
6t+G @lMLD.E kV0# For example in Unix these two lines would do that:
#z*Pj"QT0#sql clone "create tablespace aux_tspitr_tmp
xW k
cg
U0# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
Vj5O.w:gyC5A%ur0}
"| rn e$B0executing 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
.G9N
S@ Bv-p0channel a1: specifying datafile(s) to restore from backup setITPUB个人空间)G`T-a } G+E G|
restoring datafile 00001 to D:\ORACLE\ORADATA\AUX1\SYSTEM01.DBF
0y:B-H8^@t0restoring datafile 00002 to D:\ORACLE\ORADATA\AUX1\UNDOTBS01.DBF
`8N'~4B;}6H(?T0restoring datafile 00006 to D:\ORACLE\ORADATA\PAUL\TS_TEST01.DBF
0uP8z'U%~"U0channel a1: restored backup piece 1
q
rE)T|.d!tm0piece handle=D:\BACKUP\4GJ55FO2_1_1 tag=TAG20080102T151058 params=NULLITPUB个人空间
Z I0]D4M N8R
channel a1: restore complete
t6N2O#r@(I.u
Qz'Z9o [0Finished restore at 02-JAN-08
datafile 6 switched to datafile copyITPUB个人空间+cX_ Q5kZ
input datafilecopy recid=33 stamp=642958383 filename=D:\ORACLE\ORADATA\PAUL\TS_TITPUB个人空间N ^ _I(lE l
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
? Gj VRcf+e SR^0BF
4|*q| E$F yU0archive log filename=D:\ARCHPAUL\1_206.DBF thread=1 sequence=206ITPUB个人空间,qWD }z&C!tF
media recovery completeITPUB个人空间?BA H#hV [
Finished recover at 02-JAN-08
database opened
printing stored script. Memory Script
9NPcL#~ t#dlX0{
0q1^N [j U0# export the tablespaces in the recovery set
^XVu8g~0host 'exp userid =\"/@ as sysdba\" point_in_time_recover=y tablespaces=
xk;aCY l0 TS_TEST file=ITPUB个人空间]0T b8Z k4KzC
tspitr_a.dmp';
%o fX j4j]A9X Z9y0# shutdown clone before importITPUB个人空间-TTd
am0Vd7v7Df
shutdown clone immediate
ax7_ y6F}0# import the tablespaces in the recovery setITPUB个人空间nk#c&yL1D3h
host 'imp userid =\"sys/abcdefg@paulas sysdba\" point_in_time_recover=y file=
DIQ w t`0tspitr_a.dmp';
7_X&r9F'Y sp0# online/offline the tablespace importedITPUB个人空间GO ]!i5Aa4j g
sql "alter tablespace TS_TEST online";ITPUB个人空间lDDa$G |I {'R ^
sql "alter tablespace TS_TEST offline";ITPUB个人空间eHzq
\Fq!y ? U
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
9I%Y^
TG,|r IE!d0sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';ITPUB个人空间X#Db6hf@ iY
# resync catalog after tspitr finishedITPUB个人空间 h @a,F-Mcit/X
resync catalog;ITPUB个人空间
\;~Ux'|%|#D
}
oO%N2X1] ?4?+I(K0executing script. Memory Script
ITPUB个人空间 X~6A8r
T0we
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个人空间/A9m[w`e6\
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
1o,|0B&S7J0With the Partitioning, OLAP and Oracle Data Mining optionsITPUB个人空间
_ { Ur7o;q
JServer Release 9.2.0.4.0 - Production
r6{6V3R0Bx7g3w!h{0Export done in ZHS16GBK character set and AL16UTF16 NCHAR character setITPUB个人空间 y MrG`&k
Note: table data (rows) will not be exported
About to export Tablespace Point-in-time Recovery objects...
0b TG*F(IS6m
d l J0For tablespace TS_TEST ...ITPUB个人空间1IPpS
vdx-}
. exporting cluster definitions
8~"k? C z0. exporting table definitions
;Of my O _0. . exporting table STUITPUB个人空间
EI c](K-s
. exporting referential integrity constraints
6QJ-]i8P&S.x? U0. exporting triggers
&?E2J T tk~;N M2_0. end point-in-time recovery
$p(b'cm;t#\-YU,o7R"N0Export terminated successfully without warnings.ITPUB个人空间3V'CN:\F O
E.Xr
host command complete
database closed
*X&XadVr:^"q0database dismountedITPUB个人空间 }7UY J;gd7_-|
Oracle instance shut down
ITPUB个人空间$v-B'd)z ]l9\G
Import: Release 9.2.0.4.0 - Production on Wed Jan 2 15:33:26 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
{Wg]a
g3D0Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionITPUB个人空间+c Yv^9t`4\
With the Partitioning, OLAP and Oracle Data Mining options
S2Y HWXt4r7t1U0JServer Release 9.2.0.4.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
(s QY3p!g.M6K"E9E G0About to import Tablespace Point-in-time Recovery objects...ITPUB个人空间8u|G!r h'Uz
import done in ZHS16GBK character set and AL16UTF16 NCHAR character setITPUB个人空间&i$c\ eG
. importing TEST's objects into TEST
RhY
F3N0. . importing table "STU"ITPUB个人空间 T5B*}B9oC3Z)J
. importing SYS's objects into SYS
c y,V
iX a:`k0Import terminated successfully without warnings.
*Px#g`
W]7Oa0host 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个人空间0L-^&i g^9d4opB)s6_
full resync complete
U.H@]^*{4G0Finished recover at 02-JAN-08ITPUB个人空间qU/aW-{"XWh` a }"u
released channel: c1
RMAN>
5B'L Q4U/Mm:Z0C:\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.
5N ]IW;[.k(Qo4C+FO0Connected to:ITPUB个人空间O*Z Z C3kX d'e k$}4`:_
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
r
}!jfgc i3Y0With the Partitioning, OLAP and Oracle Data Mining options
7s-x"_:S iE0JServer Release 9.2.0.4.0 - Production
SQL> alter tablespace ts_test online;
Tablespace altered.
SQL> conn test/testITPUB个人空间2`h1x'[`THj"A
Connected.ITPUB个人空间*C~
C pF+E
t-C
SQL> select * from stu;
NO NAME
g.t g Xs V:z}C0---------- --------------------
0i;wCgJz5T0 109 abcdITPUB个人空间} B4[ m6nF-o
cxL0U;oY0 至此，已经恢复
导入论坛 引用链接 收藏 分享给好友 推荐到圈子 管理 举报
TAG:
标题搜索
日历
|
|||||||||
| 日 | 一 | 二 | 三 | 四 | 五 | 六 | |||
| 1 | 2 | 3 | 4 | ||||||
| 5 | 6 | 7 | 8 | 9 | 10 | 11 | |||
| 12 | 13 | 14 | 15 | 16 | 17 | 18 | |||
| 19 | 20 | 21 | 22 | 23 | 24 | 25 | |||
| 26 | 27 | 28 | 29 | 30 | 31 | ||||
我的存档
数据统计
- 访问量: 56640
- 日志数: 436
- 图片数: 4
- 建立时间: 2007-12-11
- 更新时间: 2009-07-02
