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

备份与恢复:用rman方式基于时间的不完全恢复

上一篇 / 下一篇  2008-04-10 10:45:28 / 个人分类：rman备份与恢复

查看( 107 ) / 评论( 2 ) / 评分( 0 / 5 )

C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Apr 10 10:28:39 2008

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


;}$lJl?0Connected to:ITPUB个人空间PiD c`4Q
Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionITPUB个人空间/|e/p9o{
With the Partitioning, OLAP and Oracle Data Mining optionsITPUB个人空间ZK3s?J8Q/P}
JServer Release 9.2.0.4.0 - Production

SQL> create table test (a int);

Table created.

SQL> insert into test values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> hostITPUB个人空间N$dw6d z7v"Jd
Microsoft Windows XP [Version 5.1.2600]ITPUB个人空间Za7`1Z)ou
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Paul Yi>rman target /

Recovery Manager: Release 9.2.0.4.0 - Production

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

connected to target database: PUBTEST (DBID=796553641)

RMAN> runITPUB个人空间)xf{4L(e A?
2> {allocate channel c1 type disk;ITPUB个人空间j\S%| ?Q0i@)q
3> backup full tag 'dbfull' format 'd:\backup\full%U_%s_%p' databaseITPUB个人空间_7M.UK#c jKUr'H
4> include current controlfile;ITPUB个人空间X+K B#zWx`
5> sql 'alter system archive log current';
vJoPy&D06> release channel c1;ITPUB个人空间} r.P3t;`7H
7> }

using target database controlfile instead of recovery catalog
&gN6Z%n?m1v0allocated channel: c1
5x(}{ SD5S @0channel c1: sid=19 devtype=DISK

Starting backup at 10-APR-08
`%FE6{5U5a6kY e ]0channel c1: starting full datafile backupsetITPUB个人空间np ~0E \r
channel c1: specifying datafile(s) in backupsetITPUB个人空间\8?/L4{v9sg'w K
including current SPFILE in backupset
R1G7U2X6{R!C _M~1v(F[0including current controlfile in backupsetITPUB个人空间/C.E(]9~#c*b&f&x A*@
input datafile fno=00001 name=D:\ORACLE\ORADATA\PUBTEST\SYSTEM01.DBF
tM w6Ww k9Cu0input datafile fno=00004 name=D:\ORACLE\ORADATA\PUBTEST\TOOLS01.DBFITPUB个人空间H UZbI0vy5MR
input datafile fno=00002 name=D:\ORACLE\ORADATA\PUBTEST\UNDOTBS01.DBFITPUB个人空间2i(RT0?Fo7n|'qd
input datafile fno=00003 name=D:\ORACLE\ORADATA\PUBTEST\INDX01.DBF
d(AfES w{0input datafile fno=00005 name=D:\ORACLE\ORADATA\PUBTEST\USERS01.DBF
m] ub2f0channel c1: starting piece 1 at 10-APR-08
h brP$z5a0channel c1: finished piece 1 at 10-APR-08
X3i(j6h$S.j-B0piece handle=D:\BACKUP\FULL02JDF9CK_1_1_2_1 comment=NONE
&U:Z:I+p Z-]9J0channel c1: backup set complete, elapsed time: 00:00:47
d6XPsb.hT0Finished backup at 10-APR-08

sql statement: alter system archive log current

released channel: c1

RMAN> exit


]T,fSj"x3C T0Recovery Manager complete.

C:\Documents and Settings\Paul Yi>exit

SQL> insert into test values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

         A
.g'nC6s9y'z;Ts^0----------ITPUB个人空间"` ?[ Y+D(Mh
         1
pb'O1Yr~r2l0         2

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

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

Session altered.

SQL> select sysdate from dual;

SYSDATEITPUB个人空间DhX1vy f/oi
-------------------
"c!Am h.ZC02008-04-10 10:36:14  --恢复基于这个时间点之前的

SQL> drop table test;

Table dropped.

SQL> shutdown immediate;ITPUB个人空间)]5{gJ1lAdT
Database closed.
8E ?B+F E$H?yB0Database dismounted.
5Q'X0RWh3FW0ORACLE instance shut down.
S*E(|/Vg~0SQL> startup mount;
^*?@NT/~5SFx0ORACLE instance started.

Total System Global Area  101785252 bytesITPUB个人空间E0~V6L A~
Fixed Size                   454308 bytesITPUB个人空间 aV9m{kngP+}
Variable Size              75497472 bytes
.f0z!k*RV0Database Buffers           25165824 bytes
q!C2e`%KQx:V0Redo Buffers                 667648 bytes
"PbY|U ~;n0Database mounted.
+P,eK"w}PCRJ5M0SQL> exit
Teopa5\0Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionITPUB个人空间 L!EQX0U(|
With the Partitioning, OLAP and Oracle Data Mining optionsITPUB个人空间}$p3b-]$F P
JServer Release 9.2.0.4.0 - Production

C:\Documents and Settings\Paul Yi>rman target /

Recovery Manager: Release 9.2.0.4.0 - Production

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

connected to target database: PUBTEST (DBID=796553641)

RMAN> run{
^)N6N0KL-ws02> allocate channel c1 type disk;ITPUB个人空间 @l.`:F%@.?
3> restore database;ITPUB个人空间SC(bTJ L
4>recover database until time '2008-04-10 10:36:14';ITPUB个人空间P ^6VdV{g
5> sql 'alter database open resetlogs';
$H+B8{}+I2_ K06> release channel c1;
)[&uz0av:R07> }

using target database controlfile instead of recovery catalog
Z F/hQ\^0allocated channel: c1ITPUB个人空间%h&i3e&Y:Jwl%Z
channel c1: sid=11 devtype=DISK

Starting restore at 10-APR-08

datafile 4 not processed because file is read-only
2f+s+LstS7Bn[0channel c1: starting datafile backupset restore
!V%C7^ B P'F8j p0channel c1: specifying datafile(s) to restore from backup set
ox5O)eT V'w O0restoring datafile 00001 to D:\ORACLE\ORADATA\PUBTEST\SYSTEM01.DBF
*M3wyF KVV{0restoring datafile 00002 to D:\ORACLE\ORADATA\PUBTEST\UNDOTBS01.DBFITPUB个人空间7a4p9j};\*J0gu
restoring datafile 00003 to D:\ORACLE\ORADATA\PUBTEST\INDX01.DBF
+_z*VQ!sy/I7T0restoring datafile 00005 to D:\ORACLE\ORADATA\PUBTEST\USERS01.DBF
k%L0~Xfn W fO0channel c1: restored backup piece 1
Hv.n\L:X/}@ Mn#@0piece handle=D:\BACKUP\FULL02JDF9CK_1_1_2_1 tag=DBFULL params=NULLITPUB个人空间'D%{(L5t a7k/MFp
channel c1: restore complete
)QnY8rF `+w0Finished restore at 10-APR-08

Starting recover at 10-APR-08
at r R!A~0released channel: c1
0b)r0T s2v{{ F{0RMAN-00571: ===========================================================ITPUB个人空间!i a+xLZ
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============ITPUB个人空间2]m*e~![t1Fv%P
RMAN-00571: ===========================================================ITPUB个人空间 w$? K#EWg
RMAN-03002: failure of recover command at 04/10/2008 10:41:56ITPUB个人空间1c^Ji'i'Q0al,Q
ORA-01861: literal does not match format string

RMAN> exit


F"vL-F#W$z i0e0Recovery Manager complete.

C:\Documents and Settings\Paul Yi>set nls_date_format='yyyy-mm-dd hh24:mi:ss';

C:\Documents and Settings\Paul Yi>set nls_date_format='yyyy-mm-dd hh24:mi:ss'

C:\Documents and Settings\Paul Yi>rman target /

Recovery Manager: Release 9.2.0.4.0 - Production

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

RMAN-00571: ===========================================================
.hAS3F,Au7a0RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============ITPUB个人空间 Np\@'`?9C
RMAN-00571: ===========================================================
j4N4D0m N8|c0RMAN-00554: initialization of internal recovery manager package failedITPUB个人空间2D RNcfOF E%c
RMAN-04005: error from target database:
cd"S f3q&r/Oa1w0ORA-00604: error occurred at recursive SQL level 1ITPUB个人空间z JcN#j0D
ORA-02248: invalid option for ALTER SESSION

C:\Documents and Settings\Paul Yi>rman target /

Recovery Manager: Release 9.2.0.4.0 - Production

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

RMAN-00571: ===========================================================ITPUB个人空间 v NoK8wRiD
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
x3]J!F@"f0RMAN-00571: ===========================================================ITPUB个人空间2w6^Q"h&Tsad0w@
RMAN-00554: initialization of internal recovery manager package failedITPUB个人空间)|8t6vl P;z1g*} pD
RMAN-04005: error from target database:
a)M m*Z~0ORA-00604: error occurred at recursive SQL level 1
oDeu3[8c;q8rbM*n0ORA-02248: invalid option for ALTER SESSION

C:\Documents and Settings\Paul Yi>set nls_date_format=yyyy-mm-dd hh24:mi:ss

C:\Documents and Settings\Paul Yi>rman target /

Recovery Manager: Release 9.2.0.4.0 - Production

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

connected to target database: PUBTEST (DBID=796553641)

RMAN> run{ITPUB个人空间4r(}n:K#|3?%C
2> allocate channel c1 type disk;ITPUB个人空间$c+g7dJH6b#])K
3> restore database;ITPUB个人空间 VdG.`!];^
4> recover database until time '2008-04-10 10:36:14';
htG,BR(`05> sql 'alter database open resetlogs';
&M v?eM4pHPx06> release channel c1;
y0xG|d*^07> }

using target database controlfile instead of recovery catalog
xj:@ HRfE0allocated channel: c1ITPUB个人空间_?$JK X%]1o
channel c1: sid=9 devtype=DISK

Starting restore at 2008-04-10 10:44:30

datafile 4 not processed because file is read-onlyITPUB个人空间)N8Pu t*d
skipping datafile 1; already restored to file D:\ORACLE\ORADATA\PUBTEST\SYSTEM0
R5{\ uL-xlb }P0.DBF
2I9w,R q Q)G3~)Xy0skipping datafile 2; already restored to file D:\ORACLE\ORADATA\PUBTEST\UNDOTBSITPUB个人空间6^7[(R _4s
1.DBFITPUB个人空间(P!KF)tzLH
skipping datafile 3; already restored to file D:\ORACLE\ORADATA\PUBTEST\INDX01.ITPUB个人空间?&[0D_U(d#V%D6Q6f
BFITPUB个人空间!Y[(`+U7r]
skipping datafile 5; already restored to file D:\ORACLE\ORADATA\PUBTEST\USERS01
)i!{+l\bo8f0DBFITPUB个人空间Q+be2}e2ub9SE
restore not done; all files readonly, offline, or already restoredITPUB个人空间 T7rki#J'o9Yf9h
Finished restore at 2008-04-10 10:44:30

Starting recover at 2008-04-10 10:44:30
*U0wTK A\0datafile 4 not processed because file is read-only

starting media recovery

archive log thread 1 sequence 2 is already on disk as file D:\ORACLE\ORA92\RDBM
[M6y6Bkk0\ARC00002.001ITPUB个人空间SbW0Xp|
archive log thread 1 sequence 3 is already on disk as file D:\ORACLE\ORA92\RDBM
8j x d _j|x0\ARC00003.001ITPUB个人空间-vS9G+C"I
archive log thread 1 sequence 4 is already on disk as file D:\ORACLE\ORA92\RDBM
']8s({%e R0\ARC00004.001
#heGQCb0archive log filename=D:\ORACLE\ORA92\RDBMS\ARC00002.001 thread=1 sequence=2ITPUB个人空间;e"Z bGU5ux6y
media recovery completeITPUB个人空间 T VCZ q-dfLu
Finished recover at 2008-04-10 10:44:32

sql statement: alter database open resetlogs

released channel: c1

RMAN> exit


UH F1LM9?rJ0Recovery Manager complete.

C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Apr 10 10:44:52 2008

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

ITPUB个人空间i!xBt `!?"S"j;a5C
Connected to:ITPUB个人空间$h!y9gjy&@M
Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionITPUB个人空间%B-y zi[T7F!kN&p
With the Partitioning, OLAP and Oracle Data Mining options
s5\j,^L&Py6nF0JServer Release 9.2.0.4.0 - Production

SQL> select * from test;

         AITPUB个人空间'\J+\ m,fbH V g
----------ITPUB个人空间Jl)~:z4p
         1
4KBUn xe{ ps0         2

SQL>


导入论坛 引用链接 收藏 分享给好友 推荐到圈子 管理 举报

TAG:

paul oracle my love 引用 删除 paulyibinyi   /   2008-04-21 17:00:59
基于时间点的时间格式也可这样写
　 run
　　 {
　　 set until time "to_date('2008042115:00:00','yyyymmdd hh24:mi:ss')"
　　 restore database;
　　 recover database;
　　 alter database open resetlogs;
　　 }
引用 删除 Guest   /   2008-04-11 09:45:36
评 -5 分
 

评分：0

我来说两句

显示全部

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

日历

« 2008-07-09  
日 一 二 三 四 五 六
  12345
6789101112
13141516171819
20212223242526
2728293031  

数据统计

  • 访问量: 9664
  • 日志数: 240
  • 图片数: 1
  • 建立时间: 2007-12-11
  • 更新时间: 2008-07-09

RSS订阅

Open Toolbar