Oracle性能调优：零停机，在线恢复

会计考友

零停机，在线恢复
   
    开始执行恢复操作，分为如下七个步骤：
   
    1、强制将数据库启动到mount状态
   
6 Q4 H/ [, A' M1 ?    RMAN>startup force mount;
   
    Oracle instancestarted
   
6 y9 e0 B5 U$ u2 F  m, D    databasemounted
   
# Q+ F- q) D) v+ x, D8 m, b    Total System GlobalArea 285212672 bytes
   
4 A. g2 m+ K4 L6 y+ i9 L    Fixed Size 1218968 bytes
1 W$ ?4 E/ I/ B1 n# ?6 H   
    VariableSize 92276328 bytes
, v3 b0 m4 @  F. z( m   
    DatabaseBuffers 184549376 bytes
- b7 O* H. L2 I   
" ^1 b4 s% _. D0 X3 A) Z& p    RedoBuffers 7168000 bytes
1 k- Q/ I6 I( A   
6 u7 C3 N  T$ x6 t) F    2、将对应的数据文件offline
   
    SQL> alter database datafile '/DBData/WWL/wwl001.dbf' offline;
   
: D% D3 A* x& G3 o% @1 U( \4 s    Database altered.
  ]- V& @# l) j1 ~- F) `   
# |. h% s. Z+ u3 s0 G    3、开启数据库
3 I: i- o3 X3 _8 |8 |   
    SQL> alter database open;
4 G6 N9 v, g2 Z( k5 g2 u! l) M8 x   
  y2 [2 X: |, `  I8 `/ B1 B- M0 N! d8 O    Database altered.
   
    4、将数据文件从备份中restore出来
  Q" m6 L& f6 _" u! N* S9 j6 U   
7 s' x0 k0 a  ]7 ~5 `- _    RMAN> restore datafile'/DBData/WWL/wwl001.dbf';
1 w. t  n7 }0 q; m9 I   
    Starting restore at 22-JUN-12
   
    using target database control fileinstead of recovery catalog
   
3 C% R) ]% G7 Z9 y" j" k    allocated channel: ORA_DISK_1
   
( Y, H! ~( V( `5 s4 \8 i    channel ORA_DISK_1: sid=141devtype=DISK
   
    channel ORA_DISK_1: startingdatafile backupset restore
   
7 ~' q6 C' b8 F3 C% T1 p9 F    channel ORA_DISK_1: specifyingdatafile（s） to restore from backup set
   
    restoring datafile 00005 to /DBData/WWL/wwl001.dbf
   
4 q; V  R2 O$ H: B+ R! ]5 t    channel ORA_DISK_1: reading frombackup piece /DBBak/bak_WWL_06_22_0vne4ph6_1_1
" z: H9 \9 k5 e6 n' b% S% j' G   
/ E9 n- N5 L8 L+ y    channel ORA_DISK_1: restored backuppiece 1
   
    piecehandle=/DBBak/bak_WWL_06_22_0vne4ph6_1_1 tag=TAG20120622T010021
1 Q: _2 |1 B$ m3 s! ^; r9 e   
/ F6 m0 t; S8 ^( X+ l    channel ORA_DISK_1: restorecomplete, elapsed time: 00:00:07
6 \+ ~+ z, _  O5 ?8 g" X   
    Finished restore at 22-JUN-12

会计考友

查看到数据文件已经restore出来了。
   
; f8 g! k0 J& c, j9 X    [oracle@wwldb WWL]$ ll wwl*
2 N+ p( B3 i; G. J3 |   
! L$ \; y( U- E/ a0 k* P9 Q6 B  [    -rw-r----- 1 oracle oinstall52436992 Jun 22 01:21 wwl001.dbf
( s3 J* h/ o  {, a& F* k2 {/ H   
: W! T4 d+ ]1 F( k8 ^    -rw-r----- 1 oracle oinstall 5251072 Jun 22 01:20 wwl002.dbf
& q% o# B7 L7 u  X( o8 y   
! T2 }- H& B& L, R, Q8 X0 ]    -rw-r----- 1 oracle oinstall 5251072 Jun 22 01:20 wwl003.dbf
2 f0 t, z8 u. g% X  a$ g) `   
2 d% R# _+ ~1 }1 Y0 {' v6 F6 M    5、执行数据恢复，保证scn一致
   
    RMAN>recover datafile '/DBData/WWL/wwl001.dbf';
   
    Startingrecover at 22-JUN-12
   
$ W$ }* T+ V* K/ D- D/ t3 K" F    using targetdatabase control file instead of recovery catalog
   
    allocatedchannel: ORA_DISK_1
   
    channelORA_DISK_1: sid=144 devtype=DISK
   
2 S: K7 V- M; l, T( A3 H    starting mediarecovery
' ~9 W7 v4 r, Q( z   
    media recoverycomplete, elapsed time: 00:00:01
   
# ~, F/ x% t0 v( o4 Q" B7 ^7 `. F    Finishedrecover at 22-JUN-12
) l9 X- g# p9 o* b   
5 t# f3 P4 J* }0 z1 |. c  y    RMAN>
   
    6、将数据文件在线
& L& C4 u. \- f, c   
  V& Z( E9 _9 a6 h, U    SQL> conn / as sysdba
   
    Connected.
  H+ N$ e$ l" }. H, R- l6 h+ s   
    SQL> alter database datafile'/DBData/WWL/wwl001.dbf' online;
   
9 _" @7 e8 |5 n8 i$ x* f5 {( z7 Y    Database altered.
' |; X" G0 F  H: V8 N   
    7、验证数据是否恢复，可以看到数据全部恢复回来了。
5 `8 _& e7 ~4 b# q* [# c  a   
! |' n5 Q9 T2 L/ W( x  G# c    SQL>conn wwl/wwl
0 S( k( @" M# a2 e0 A   
    Connected.
   
    SQL>select * from tab;
   
& G- M! K6 N, b7 ^8 q4 V! ?    TNAME TABTYPE CLUSTERID
; H1 N  |9 z% M; S6 c5 j2 l   
    ------------------------------------- ----------
   
    WWL01 TABLE
   
4 d9 G6 ]" Z4 T1 X6 o! I    WWL02 TABLE
: A+ z0 u; D1 e, v* n* Y   
9 Z2 ~8 j/ H) a8 l9 Y    WWL03 TABLE
7 l& x% c1 p3 U) A3 R& g+ {. d5 y   
    WWL04 TABLE
0 G6 w- s" G8 x) R  b4 |3 r' R$ Z   
    WWL05 TABLE
   
! p+ B2 F  l- q0 I    SQL>select count（*） from tab;
2 g) G4 }' E& ^1 S   
    COUNT（*）
1 N1 ^1 S0 _! F% ~) X/ h0 x  l   
    ----------
   
: W. W  U4 p4 ?! {) b( ~5 p5 q9 q    5

会计考友

SQL>
# f, m+ |. u$ c) N   
    1.7 恢复方法二，离线恢复
7 H" n4 ~& o9 j* ?7 D) m2 l& Q8 K1 a   
    开始执行恢复操作，分为五个步骤：
6 [( }  z3 D: c' p   
    1、强制将数据库启动到mount状态
   
    SQL> startupforce mount;
   
- u8 D4 w: u3 T    ORACLE instancestarted.
4 |1 a7 j+ D& {4 H; O) X   
    Total SystemGlobal Area 285212672 bytes
) \8 P  s/ M0 M' e5 ]   
, t8 _8 b$ y! v. N& E- `5 U    Fixed Size 1218968 bytes
# \0 b4 T9 j- S5 v& H" Y# O  N   
, E) [% g' l  q" N2 I    VariableSize 96470632 bytes
   
    DatabaseBuffers 180355072 bytes
   
8 b/ f0 m- ~4 `3 i2 \    RedoBuffers 7168000 bytes
( f% H4 L7 U$ a   
2 j/ U& m. K" K- P) @5 v    Databasemounted.
0 J+ B: w5 f. `( s: E% H& V   
    2、将数据文件从备份中restore出来
' [' i. |* }3 E% Q0 {9 h: W   
    RMAN> restoredatafile '/DBData/WWL/wwl002.dbf'
   
    2> ;
   
. P/ {# R0 e4 Y! P& D# m! @. g2 ?    Starting restoreat 22-JUN-12
# R) m" ?; Z' A6 k   
    using targetdatabase control file instead of recovery catalog
( e# d  d7 P  g( c) I) R/ T( o   
    allocatedchannel: ORA_DISK_1
   
    channelORA_DISK_1: sid=156 devtype=DISK
' u6 t$ y4 S. T* k; ~7 o& _2 D   
    channelORA_DISK_1: starting datafile backupset restore
, v2 w$ n; \/ d: L. ?   
    channelORA_DISK_1: specifying datafile（s） to restore from backup set
. I  s7 p* Y( w% t& A5 i- g   
    restoringdatafile 00006 to /DBData/WWL/wwl002.dbf
   
    channelORA_DISK_1: reading from backup piece /DBBak/bak_WWL_06_22_0vne4ph6_1_1
   
6 H, L/ A' X! T* r/ U/ I5 \5 `    channelORA_DISK_1: restored backup piece 1
   
! O/ F' Y, c( P/ B    piecehandle=/DBBak/bak_WWL_06_22_0vne4ph6_1_1 tag=TAG20120622T010021
   
: v* W$ _6 D( j: K7 E0 }9 |; U    channelORA_DISK_1: restore complete, elapsed time: 00:00:01
   
6 B( h$ `- K5 x    Finished restoreat 22-JUN-12
- ]9 h0 d4 q' }, a$ |+ x" _   
8 ]$ @$ c) y/ V; C" Z% Y    3、执行数据恢复，保证scn一致
4 Q8 K, r0 N, h& R1 [& T' D   
    RMAN> recover datafile '/DBData/WWL/wwl002.dbf';
   
    Starting recover at 22-JUN-12
   
    using channel ORA_DISK_1

会计考友

starting media recovery
   
    media recovery complete, elapsed time: 00:00:02
   
    Finished recover at 22-JUN-12
   
    RMAN>
2 b) {0 O7 D/ j# x$ v   
    4、开启数据库
1 o! d) w5 M) n6 ]& m   
  J6 F% P0 E( ~/ Q% u% ^7 c    SQL> conn /as sysdba
   
    Connected.
7 x" U% `. J8 e) C   
4 u5 T9 k0 G' z0 X  `    SQL> alterdatabase open;
   
, F+ `' ~2 a; y6 @: e- S. V; J    Databasealtered.
   
    5、验证数据是否恢复
; `9 l. ?+ j* h$ @5 E3 m: j   
  |$ ?( z* T) ]  B$ o    SQL> select *from tab;
   
    TNAME TABTYPE CLUSTERID
   
    ------------------------------------- ----------
   
    WWL01 TABLE
& J6 @, Y1 `7 r% ?' j' @9 c   
    WWL02 TABLE
3 [8 j$ Z) V3 ]1 Y# B% Y( [   
    WWL03 TABLE
   
    WWL04 TABLE
: q: y. X5 Y- Y4 R: |4 e' F   
2 g1 }  m( c2 d+ r; ]3 q, R    WWL05 TABLE
   
    SQL> selectcount（*） from wwl01;
. {, ~: O$ ]7 a! k/ u/ E" x. ]* F   
    COUNT（*）
   
' V) x8 E) j, {" |+ J9 h    ----------
* \  _# l, z" H8 B+ k2 w/ }   
: [, E* ~/ i1 D% H& x5 w    5
   
4 ?2 c8 F! ~0 E  I6 w% e    SQL>