学习oracle技术,每天学一点,每天进步一点 oracle 10g ocp

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

上一篇 / 下一篇  2008-01-02 15:45:49 / 个人分类:rman备份与恢复

数据库版本:9204
!W#W.|\(A+G;{[5|*kQ0目标数据库名称 paul
1g}9MW2f-F:Tf&k}Ct0辅助实例 AUX1

ITPUB个人空间PWO(C N!h
1.备份数据库用带恢复目录的模式ITPUB个人空间h}L:X kW
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
|G0JRU0full resync complete
T.h3?1Q[(e P-v0allocated channel: ORA_DISK_1ITPUB个人空间ja3e h Sx
channel ORA_DISK_1: sid=13 devtype=DISK
g6szx(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*ry4r0input datafile fno=00003 name=D:\ORACLE\ORADATA\PAUL\INDX01.DBFITPUB个人空间.IiGu0^ j
input datafile fno=00005 name=D:\ORACLE\ORADATA\PAUL\USERS01.DBFITPUB个人空间 ahWZ 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@Qgiq`*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
\-QyO @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个人空间BP8Vy9~ 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
;_udrY?mZ0JServer Release 9.2.0.4.0 - Production
xh o)VW&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'tELfw0zk0Connected.ITPUB个人空间iE/@qr
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个人空间nmi1I 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个人空间2ywlqqN%z
STU                               TABLE

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

Session altered.

记住删除此表的时刻是ITPUB个人空间qcx\{#W6L
SQL> select sysdate from dual;

SYSDATE
9rHAN%b2a0-------------------ITPUB个人空间`TX bZZ
2008-01-02 15:14:08

SQL> drop table stu;

Table dropped.

3.为辅助实例创建初始化参数文件ITPUB个人空间~A?!M7e A
新建 d:\oracle\oradata\aux1 目录ITPUB个人空间E"hV8e/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{.NF7B3P.j:kx0修改log_archive_start=false
SO.t$b0U"H;XW4B}^0辅助实例必须为非归档模式。
%|$\i(LQ"L0添加如下两个参数:ITPUB个人空间-P]/Y$FfpkM t"`
db_file_name_convert=("d:\oracle\oradata\paul", "d:\oracle\oradata\aux1")ITPUB个人空间;zz.ah ^J
log_file_name_convert=("d:\oracle\oradata\paul", "d:\oracle\oradata\aux1")

4. 创建辅助实例服务 用oradmin工具ITPUB个人空间EJT2}"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个人空间2pI4L/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
kdO*[,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;jM)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个人空间:Vji 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 Qb%wmu
5> }

allocated channel: a1
9EJK%GK|+BW.H*e0channel a1: sid=10 devtype=DISK

allocated channel: c1ITPUB个人空间!H8sD:j Pov"^
channel c1: sid=9 devtype=DISK

Starting recover at 02-JAN-08

printing stored script. Memory Script
4Al e{`"q$hb1u/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';
)Ad 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)mW
executing script. Memory Script

executing command: SET until clause

Starting restore at 02-JAN-08

channel a1: starting datafile backupset restore
0o'JWxd,FhLc6aC0channel 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
,wd 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.SC$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 |#@Ov
  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
`gcdFS&HDu0    null;ITPUB个人空间qFMH[W(i*I
end; >>>;ITPUB个人空间] Nj7\5~B:V s*I
# set a destination filename for restore
L%LS[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个人空间'yb0m:{9O$`
set newname for datafile  2 to
/uL,rB6_|T0 "D:\ORACLE\ORADATA\AUX1\UNDOTBS01.DBF";ITPUB个人空间-QMOfZ@.~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个人空间GEN)W4NW l)K8rf
#online the datafiles restored or flipped
k I'_(k#Qm4kN-M0sql clone "alter database datafile  6 online";
Pq)Dnh ?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
xWk cg U0#           datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
Vj5O.w:gyC5A%ur0}
"|rne$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+EG|
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个人空间 ZI0]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(lEl
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$FyU0archive log filename=D:\ARCHPAUL\1_206.DBF thread=1 sequence=206ITPUB个人空间,qWD}z&C!tF
media recovery completeITPUB个人空间?BAH#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;aCYl0 TS_TEST file=ITPUB个人空间]0Tb8Z k4KzC
tspitr_a.dmp';
%o fX j4j]A9XZ9y0# 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 wt`0tspitr_a.dmp';
7_X&r9F'Ysp0# 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,|rIE!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...
0bTG*F(IS6m dl J0For tablespace TS_TEST ...ITPUB个人空间1IPpS vdx-}
. exporting cluster definitions
8~"k? C z0. exporting table definitions
;Of myO_0. . exporting table                            STUITPUB个人空间 EI c](K-s
. exporting referential integrity constraints
6QJ-]i8P&S.x?U0. exporting triggers
&?E2JTtk~;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个人空间+cYv^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
(sQY3p!g.M6K"E9E G0About to import Tablespace Point-in-time Recovery objects...ITPUB个人空间8u|G!rh'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
cy,V iXa:`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-^&ig^9d4opB)s6_
full resync complete
U.H@]^*{4G0Finished recover at 02-JAN-08ITPUB个人空间qU/aW-{"XWh`a}"u
released channel: c1

RMAN>


5B'LQ4U/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 ZC3kX d'ek$}4`:_
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
r }!jfgci3Y0With the Partitioning, OLAP and Oracle Data Mining options
7s-x"_:SiE0JServer 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~ CpF+E t-C
SQL> select * from stu;

        NO NAME
g.t gXsV:z}C0---------- --------------------
0i;wCgJz5T0       109 abcdITPUB个人空间} B4[ m6nF-o
      
cxL0U;oY0      至此,已经恢复


TAG:

 

评分:0

我来说两句

显示全部

:loveliness: :handshake :victory: :funk: :time: :kiss: :call: :hug: :lol :'( :Q :L ;P :$ :P :o :@ :D :( :)

Open Toolbar