归档模式下的日志组丢member恢复

一 总体描述

本实验模拟数据库归档模式下的日志组丢失某个member,在数据库一致性关闭与非一致性关闭的情况下对数据库恢复的情况,因日志组有多个member,所以此实验属非关键性故障模拟.

二 操作环境

OS

$cat /etc/redhat-release ITPUB个人空间:P"cB/t:uW.T#n
Red Hat Enterprise Linux Server release 5.6 (Tikanga)
+cSy}["_3ut0$uname -a ITPUB个人空间8ra{:ZMK'e.v!u4L|
Linux stu00 2.6.18-238.el5 #1 SMP Tue Jan 4 15:24:05 EST 2011 i686 i686 i386 GNU/Linux ITPUB个人空间0yC3[,SaQ6u]'^

DB

SQL> set lines 150ITPUB个人空间nsf2c` zX!N$z
COL PRODUCT FORMAT A55
` P}9V5R,@w5g3W]0COL VERSION FORMAT A15
HV0[-Z/a)rX0COL STATUS FORMAT A15ITPUB个人空间3Qh2WP5YokJo0]
SELECT * FROM PRODUCT_COMPONENT_VERSION;ITPUB个人空间r3yT(NM:h7o
archive log list;SQL> SQL> SQL> SQL>ITPUB个人空间 c `q fnZ
PRODUCT                                                 VERSION         STATUS
{G1tp%V%s0------------------------------------------------------- --------------- ---------------ITPUB个人空间7|JL{ [4U*q bv}*f*h
NLSRTL                                                  10.2.0.4.0      ProductionITPUB个人空间9]w}(~ gQ)iBH
Oracle Database 10g Enterprise Edition                  10.2.0.4.0      Prod
k'}BK9Q*c{QJa0PL/SQL                                                  10.2.0.4.0      ProductionITPUB个人空间I&nv(Z.f ?
TNS for Linux:                                          10.2.0.4.0      ProductionITPUB个人空间:]-v&wgQs)T.@%E
ITPUB个人空间:F*rx#p~#S(p gQ
SQL>ITPUB个人空间.{TU+aH'Bu)C6k
Database log mode              Archive Mode
3O2RUM*H"s0Automatic archival             EnabledITPUB个人空间+Kj+Wj6PH
Archive destination            /home/oracleITPUB个人空间e7E2V ];R
Oldest online log sequence     111ITPUB个人空间'u T"QuG o*b+J
Next log sequence to archive   114ITPUB个人空间 m3tL%{p/v.NX W:y
Current log sequence           114ITPUB个人空间u#FV&g?R%Pcr
SQL> ITPUB个人空间#V!l n.eX9[&G

other

三 结构设计

a 模拟环境ITPUB个人空间8_ JY1okL G

1.向各日志组中添加member (每组至少一个) (此步文档中略掉,添加成员例句: alter database add logfile member  '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02.log' to group 2; ) ITPUB个人空间OeB&Nn!PL A
2.操作系统中使用rm命令删除当前日志组下的某个member. ITPUB个人空间$A*f;e5] J `,^
3.查看状态 ITPUB个人空间_8Mi6s.p;xZ%X3FJ

b 在2的条件下以shutdown immediate的形式关闭数据库,启动数据库进行恢复.

c 在2的条件下以shutdown abort的形式关闭数据库,启动数据库进行恢复.

9L6I%n4fZo {0

四 详细步骤

a模拟日志组某一成员丢失

a.1 查看数据库当前日志组及日志组成员信息.(每组两个member)
P IP Li0SQL> select * from v$log;
5SGTS&DT#m0
c/{{!A!~)TO,v0    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM ITPUB个人空间 FPY^J ww}
---------- ---------- ---------- ---------- ---------- --- --------------- ------------- --------- ITPUB个人空间 WLAh'N
         1          1        127   52428800          2 YES INACTIVE              1476205 10-JUN-12 ITPUB个人空间%pR%EX'v5MK0E
         2          1        129   52428800          2 NO  CURRENT               1476209 10-JUN-12
)@)w9v_:P/s0         3          1        126   52428800          2 YES INACTIVE              1476203 10-JUN-12
"Xup6CG7Q5O6P*y S0         5          1        128   62914560          2 YES INACTIVE              1476207 10-JUN-12 ITPUB个人空间'jw7P/T Q?

~)ia1{ap m0SQL> select * from v$logfile; ITPUB个人空间7NLZyvc X;g)aK
ITPUB个人空间!eGm |6Vb2pa`0U
    GROUP# STATUS          TYPE    MEMBER                                                                           IS_ ITPUB个人空间/efjUb,F8i
---------- --------------- ------- -------------------------------------------------------------------------------- ---
&l q.Z;Dt(@5\/rw0         1                 ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo1.log                      NO ITPUB个人空间 W"b&~4B:V.F,^8j8nfd
         5                 ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo5.log                      NO
(Al4^ wI0         3                 ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03.log                     NO
]WZGtST*E0         2                 ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02.log                     NO ITPUB个人空间 g:{'pX*D&j&F6z#P N
         1                 ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo01a.log                    NO ITPUB个人空间'z"w(C9a1D#[T:B
         2                 ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02b.log                    NO
Pm%Wsu%[7_BZ0         3                 ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03b.log                    NO
%|6u ]WgM0         5                 ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo05b.log                    NO
)wQ%x.R~8bv(pz0
TWy#g KR08 rows selected.
/G!v%G5}}$M1j0 ITPUB个人空间,Xz]9] w n"@)Q
SQL>
X1j(S Y'Tmp}\0
c6{\$u)n$F.S,K`'Fzt0 ITPUB个人空间9y~%C wW
a.2 使用操作系统命令删除当前日志组的一个member ITPUB个人空间k-XE3oc;EM
SQL> !rm /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02b.log ITPUB个人空间+@k6g c^*R,i1KM
ITPUB个人空间4a \sx\V#Dl
a.3 此时使用日志切换,aler日志中会出现以下信息 ITPUB个人空间R-l(U&b;w/| A(t
Sun Jun 10 22:21:26 2012 ITPUB个人空间dx"s3?.V(onm
Errors in file /u01/app/oracle/product/10.2.0/db_1/admin/testb/bdump/testb_arc1_4140.trc: ITPUB个人空间4F:I'js8k*Ug
ORA-00313: open failed for members of log group 2 of thread 1
t'pm]E0ORA-00312: online log 2 thread 1: '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02b.log' ITPUB个人空间'g)["m#}$U&O+FK
ORA-27037: unable to obtain file status
N!L9ceO!v^$k$n0Linux Error: 2: No such file or directory
h/N4A9Ti#Z(V0a0Additional information: 3
E&_ o9|!x1fJ0 ITPUB个人空间_!t9\:W]0P
a.4 多次切换日志组,直到将丢失member的日志组状态切换到current
cQP4pe;z z|'`0SQL> alter system switch logfile;
g~6v*k,V`,qH_0.... ITPUB个人空间1xm-M?j*u,{M
SQL> select * from v$log; ITPUB个人空间mEw6wg"z

/z pOIB0    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM ITPUB个人空间(c{ s4X#k&YhW
---------- ---------- ---------- ---------- ---------- --- --------------- ------------- ---------
i4Qn#JFqq7TT0         1          1        135   52428800          2 YES ACTIVE                1476696 10-JUN-12 ITPUB个人空间%e,zD5o#mx
         2          1        137   52428800          2 NO  CURRENT               1476704 10-JUN-12
O~dZH"C0_ D C0         3          1        134   52428800          2 YES ACTIVE                1476461 10-JUN-12
4wL1B&j5K[\Wf0         5          1        136   62914560          2 YES ACTIVE                1476702 10-JUN-12
`^1h X0}6jg"t+HN0
,?K3O w:~Z#n4Z ^8X0SQL> ITPUB个人空间7T q6eKa8k

,Z;Z2JR'Y'J0(以上环境模拟完成)
niv Y8zs k.M!X!H0
`l ]Yv(x-}0

b.在a的基础上一致性关库的恢复

一致性的停止数据库,再启动数据库,查看日志组member状态. ITPUB个人空间!BXzJnx
SQL> shutdown immediate; ITPUB个人空间6fq UCo&\
Database closed.
7L*Vc%at6[0Y?V0Database dismounted. ITPUB个人空间i:]p-k)h ZovD3z
ORACLE instance shut down. ITPUB个人空间 |Y l%cA5@na3T4P
SQL> startup
d!c6s6Bx^%{0ORACLE instance started. ITPUB个人空间*i-L$IsP#{C

y$Y^yRz&wU5nb0Total System Global Area  285212672 bytes
Z `@ _+c8WL%V`0Fixed Size                  1267068 bytes ITPUB个人空间1c!uWg"I
Variable Size             125831812 bytes ITPUB个人空间 xML0c'ZE/L`#^
Database Buffers          155189248 bytes ITPUB个人空间 x%Kf`Y;rC5j
Redo Buffers                2924544 bytes
a*V/RPGE0Database mounted. ITPUB个人空间f!n"ki!H/YQW
Database opened. ITPUB个人空间ytor$Wx:O2~;]&T
SQL> ITPUB个人空间T+z'N;Pu$i U%hC-d
状态正常,去看看alert日志中都有什么记录.
`"W@;T _b0Database mounted in Exclusive Mode
&Wjk'N^mtLS T1o S0Completed: ALTER DATABASE   MOUNT
xoHt(DPfV F0Sun Jun 10 22:32:27 2012 ITPUB个人空间Z mc TtV#I
ALTER DATABASE OPEN
TRO!G.b6V {*B8W0Sun Jun 10 22:32:27 2012
-v&J*ZH.~wy/M0LGWR: STARTING ARCH PROCESSES
)FDvg\ @b0ARC0 started with pid=16, OS id=4618 ITPUB个人空间$N"oxqvU z4z
Sun Jun 10 22:32:28 2012
I&\Ok/^^;s0ARC0: Archival started ITPUB个人空间}-C+z7oc CKP)CR k
ARC1: Archival started
:J:i~ oh*@?J(d0LGWR: STARTING ARCH PROCESSES COMPLETE ITPUB个人空间}6K6i:T1gg0aq"M
ARC1 started with pid=17, OS id=4620 ITPUB个人空间 c9J'Ml%s-b%I2I
Sun Jun 10 22:32:28 2012 ITPUB个人空间*j W*Y6qH
Errors in file /u01/app/oracle/product/10.2.0/db_1/admin/testb/bdump/testb_lgwr_4598.trc:
&Y-J6P|l`?SQ'i9y0ORA-00313: open failed for members of log group 2 of thread 1
WUNeO%T;m0ORA-00312: online log 2 thread 1: '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02b.log' ITPUB个人空间3A*E#sA)\:I~
ORA-27037: unable to obtain file status
p*BA w!H0Linux Error: 2: No such file or directory
gfG`CW-g)P B0Additional information: 3
r.L(@ Kf"@&@cOd:i:J6{0Sun Jun 10 22:32:28 2012
3^&gNpN0Errors in file /u01/app/oracle/product/10.2.0/db_1/admin/testb/bdump/testb_lgwr_4598.trc: ITPUB个人空间4w _ ~R+Y
ORA-00321: log 2 of thread 1, cannot update log file header ITPUB个人空间#h&fu2dL xd;[\V
ORA-00312: online log 2 thread 1: '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02b.log'
e)GELU]4K0Sun Jun 10 22:32:28 2012
2}3J u6poVU0Errors in file /u01/app/oracle/product/10.2.0/db_1/admin/testb/bdump/testb_lgwr_4598.trc: ITPUB个人空间0o*F]^*`-S| Q-~
ORA-00313: open failed for members of log group 2 of thread 1 ITPUB个人空间3]"Py6`)vqnP
Sun Jun 10 22:32:29 2012 ITPUB个人空间s6Pu_D [)bU
Thread 1 opened at log sequence 137 ITPUB个人空间3ij P9{6] LH
  Current log# 2 seq# 137 mem# 1: /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02.log
*^8{\"Ob5g0Successful open of redo thread 1 ITPUB个人空间+[&b+fCo*DNr3@
alert日志记录中依然是日志文件丢失,找不到相应的日志文件.
z~$~@Tq0oY(y0 ITPUB个人空间 R&l^z:} j:C5u
b.1 此时尝试执行恢复日志操作会报错,因丢失日志组member的状态在current.
%@3} h |B5}/n0SQL> alter database clear unarchived logfile group 2;
t'Wj;o8|^Ke/A%RO {0alter database clear unarchived logfile group 2
o \!i G&G;X7m.|0* ITPUB个人空间W;baYQeL&[
ERROR at line 1:
dd1a6X5S*h0ORA-01624: log 2 needed for crash recovery of instance testb (thread 1)
'o/x RQ(A5t"X+H0ORA-00312: online log 2 thread 1: '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02b.log'
,mo+O4Mhq1b i0ORA-00312: online log 2 thread 1: '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02.log' ITPUB个人空间 bwULl-Aj
ITPUB个人空间$T9NNMy*]_FK.I
SQL> ITPUB个人空间(Q;W BX]4p%o v*j'R
b.2 进行日志切换,然后再执行日志组的恢复,日志恢复成功.(在切换日志之前应该查看一下日志状态,这里忘记查看了,后面实验补上) ITPUB个人空间Bw1L!H ]3m9[
SQL> alter database clear unarchived logfile group 2; ITPUB个人空间_K#z:vh;tg
alter database clear unarchived logfile group 2 ITPUB个人空间!Dp:Myh`-O6]6s8W
* ITPUB个人空间\.j:iNp Z&y
ERROR at line 1:
Q%W/m)S5Tq0ORA-01624: log 2 needed for crash recovery of instance testb (thread 1) ITPUB个人空间Zgc0a%d S%jk
ORA-00312: online log 2 thread 1: '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02b.log' ITPUB个人空间B9gZB mhg,x
ORA-00312: online log 2 thread 1: '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02.log' ITPUB个人空间xY+F}%~|D

n,EW!dbT7U7K n0
f2P ];u Pi$y0SQL> alter system switch logfile; ITPUB个人空间C})|L"T8H!h9e8NJ

IxvD1]0System altered. ITPUB个人空间G#c_ _H?4iC4y:E

!yW uu)H0SQL>  alter database clear unarchived logfile group 2;
7L_3@+n$va0 ITPUB个人空间V^sTpW2w
Database altered. ITPUB个人空间r w7Q r}W
ITPUB个人空间VzbO'eL6i4op(L!o
SQL>
-~j?(u.DhEfO0
b:TmQ*NM6v0

c.在a的基础上非一致性关库的恢复

非一致性的关闭数据库,再启动数据库,查看日志组member状态. ITPUB个人空间6i7b P.IWN!d3?
SQL> shutdown abort ITPUB个人空间,p.l"v!H s(J3W wq:Y
ORACLE instance shut down. ITPUB个人空间_ L#M~mq;O-P
SQL> startup
|E7`|5O0ORACLE instance started.
wrPI;g JLty0
7i(Y#U,WqH*p*I0Total System Global Area  285212672 bytes ITPUB个人空间 y{X%["{)d+Y
Fixed Size                  1267068 bytes
\1Eb Iq*^d0Xa0Variable Size             125831812 bytes ITPUB个人空间{a5s3H8l'R3U M
Database Buffers          155189248 bytes ITPUB个人空间J^6e`0Q^I3f
Redo Buffers                2924544 bytes ITPUB个人空间Y ~j M'a;s*cN'@!Ga
Database mounted. ITPUB个人空间W1@!P,q4aKd9?4|3t
Database opened.
6HKYh$e$|]kt0SQL> select * from v$log; ITPUB个人空间#qLP1vJ8ttr6P-['I
ITPUB个人空间pn/A9LTy_
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
`DV zA&@#M0---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ITPUB个人空间6qf j&]g
         1          1        135   52428800          2 YES INACTIVE               1476696 10-JUN-12
,ji"a GGdJ6]!v(E&e0         2          1        137   52428800          2 YES INACTIVE               1476704 10-JUN-12 ITPUB个人空间Z(bF;^.\/\8L
         3          1        138   52428800          2 NO  CURRENT                1496929 10-JUN-12
*`-o |b$l0         5          1        136   62914560          2 YES INACTIVE               1476702 10-JUN-12 ITPUB个人空间D"e#M!_ Z"Ok
ITPUB个人空间X+kO Xuu6|1G
SQL> select * from v$logfile;
[A+H~'GF'L H0 ITPUB个人空间(zMAbSVIA
    GROUP# STATUS  TYPE    MEMBER                                                                           IS_ ITPUB个人空间(f5[-|"W9S7ne gQ
---------- ------- ------- -------------------------------------------------------------------------------- --- ITPUB个人空间t"}2NW)xB+[
         1         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo1.log                      NO
gk6\1d N2x0         5         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo5.log                      NO
0T(m+X G&l A0         3         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03.log                     NO ITPUB个人空间7DX,Wz"M$Y1M |
         2 STALE   ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02.log                     NO
C"A1U;HC8gc3Y `2u0         1         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo01a.log                    NO
9cGj\WCG U%^ k0         2 INVALID ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02b.log                    NO ITPUB个人空间!y,u V2Dv#g2[3w#o
         3         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03b.log                    NO
5?zJfA0         5         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo05b.log                    NO ITPUB个人空间8q/Hkcm!j H!xG

7r5Bn h1KC+I08 rows selected. ITPUB个人空间;r9U? \PwC

{Vv+M6rh9p YR0此时alert日志启动部分内容(与一致性停库一样,这里不记录了)
QQU6g1@PQ0gYn0 ITPUB个人空间|*F;l7uj1J
c.1 由于此时丢失member的日志组状态已不是current,因此执行重建日志命令可以成功,记录如下.
?$r;jy-a2P(tKE0SQL> alter database clear unarchived logfile group 2; ITPUB个人空间 WI-s*oU;q+K0| Q
ITPUB个人空间8`x f}0tf w9q
Database altered. ITPUB个人空间f1R^ k/u}X

&F3IY9_}'K oD a g,Y0SQL> select * from v$logfile;
}K"p9MQ'Q}`6R0 ITPUB个人空间N|#l |_QyU9J
    GROUP# STATUS  TYPE    MEMBER                                                                           IS_
B?X J$ahH._!yp0---------- ------- ------- -------------------------------------------------------------------------------- ---
o:u\:c!|+CM0         1         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo1.log                      NO ITPUB个人空间T)h1?N"S%FPS-Sb
         5         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo5.log                      NO ITPUB个人空间U v8D`pW~`4{9f
         3         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03.log                     NO
JyRl"B W,k9B^q,W1y0         2         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02.log                     NO ITPUB个人空间0] i/uKs~u`'M
         1         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo01a.log                    NO ITPUB个人空间[OCN%G+^X
         2         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02b.log                    NO
5\c&ab7Vnz|0         3         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03b.log                    NO ITPUB个人空间PwaA/j[2Pirj
         5         ONLINE  /u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo05b.log                    NO
3ns2rH;j2@r&l?n{*K0 ITPUB个人空间)Xx1M m%{eN$e z
8 rows selected. ITPUB个人空间/l d`4lj9}6]

|hY?up&w%H)i0SQL>
"sz$qR:teM0L}0此时查看操作系统日志文件已经创建回来. ITPUB个人空间b1JZ.Vn1Q#I
ITPUB个人空间 dr0B ue!Pa

五 个人总结

在归档日志下丢失redolog组的member,当发生日志切换时会向alert日志中添加错误信息.ora-00313,ora-00312.虽然丢失日志组某一个成员在无论是数据库一致性关闭还是数据库不一致性关闭的情况都对数据库没有影响,也不影响数据库的正常使用,如若放任错误不管,当发生某一日志组成员全部丢失时,做恢复操作就要麻烦的多.所以问题发生就要及早解决.做到平时用监控工具时刻"盯"着alert的报错.ITPUB个人空间q*wA0L3p@,u]Z8t

六 资料参考引用

Loss Of Online Redo Log And ORA-312 And ORA-313 [ID 117481.1]ITPUB个人空间Z5s/s|~ m!m

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25906825/viewspace-732874/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25906825/viewspace-732874/