oracle闪回特性
上一篇 / 下一篇 2014-06-05 14:05:57 / 个人分类:甲骨文
&a eLm*WY`?051Testing软件测试网c R^&{;D!I
51Testing软件测试网%U7A}%A#mk7e5J51Testing软件测试网9S2{4\q/R3LKu
51Testing软件测试网@|.}5}gKrJ1Rd51Testing软件测试网bt%nM!XOUW#Y2p
51Testing软件测试网&H_|aN:S"ywuVE51Testing软件测试网@9`,P D,X"Wd!Qe
51Testing软件测试网*| sBW%c51Testing软件测试网yB j Ub)I @^
51Testing软件测试网'Y4J0Fs.fjQ] L6rR@"MG0EA{9L+B$JP051Testing软件测试网DO1JA0w@7ocT
51Testing软件测试网+c {2{w^#| zU~#Hay-r^o(i l'O%GQ t)s0特性概述tq;R4E`+])G8_0闪回技术通常用于快速、简单恢复数据库中出现的人为误操作等逻辑错误,即:闪回只对逻辑错误有意义,对数据块损坏和联机日志损坏必须采用介质恢复。51Testing软件测试网9kT G%H`(Xn a.?
51Testing软件测试网.Sh'|4A,j?Mg\&f+] E3Z/DU+vq[1k0闪回类型KaFM+X#w2\.F0从闪回的方式可以将闪回分为:
T/l)@KjY!{051Testing软件测试网$U4FX:M ZbO1、 基于数据库级别闪回;
` H9]Xw+VM051Testing软件测试网%S8_:V bv6Eyb2、 表级别闪回;
m?R{R051Testing软件测试网dJP]`$]F7T(T3、 事务级别闪回.
)jYR3} n051Testing软件测试网$U5H.O7Z:mVtm Ba51Testing软件测试网}_,[DWWK Ki9Se6t
f0QYSZ6U3IO1`-l0根据闪回对数据的影响程度又可以分为:
#C)A9g B0e9D@7jb4]0e9JC,g@0WU01、 闪回恢复;51Testing软件测试网L-LQt?f
4Zz9bz V,v9_02、 闪回查询。
I T9G KYv051Testing软件测试网$VH2J jl-js F3p闪回恢复将修改数据,闪回点之后的数据将全部丢失。而闪回查询则可以查询数据被DML的不同版本,也可以在此基础之上确定是否进行恢复等。51Testing软件测试网(HP vh*X(TWZ`
2W8Q2\tk0
!@ f v*L d8iR L]0如何启用闪回启用闪回前检查51Testing软件测试网D"]B(m$g0zlF8Y确认是否启用闪回功能
)wJ#Y y)lIh*G|051Testing软件测试网1]d~4JfESQL> select flashback_on from v$database;
FAdeaA&dm051Testing软件测试网~ vIs5pvh't51Testing软件测试网.PU8[XXv
51Testing软件测试网.Z$e8cz&Y?GFLASHBACK_ON51Testing软件测试网 z7u3^8C x'? Wz?M
51Testing软件测试网[ wU~*c.m------------------51Testing软件测试网4a+L;JaX"eN~
51Testing软件测试网'Q#N\F `NO
GS%LT \bK7h0?p M@d Cp0
^'\6r4U.^e8]01A-D#Cr0U5~'k0SQL>51Testing软件测试网^*cR4|0|ajZ#Y3`
f2H Gp"f0
m:P\v$vHt051Testing软件测试网w9l4wK/mbE)[ R$P说明:
:Cx&RTw:Aj0_qBwtmW01、flashback_on取值为NO,说明尚未启用闪回功能,取值为YES则表示启用了闪回功能。
[ e%@S3_vV051Testing软件测试网A2e%s&E[O Ot2、该参数仅能在数据库mount状态下修改。51Testing软件测试网 w.OC'}gE5y6Hq+R2U
51Testing软件测试网{#I{:m2b:mt1jSX~R#C0zD:\6]^:b2Q Z3z0确认当前日志归档模式
@7Uw7p.r0g_%kM r(Kr8ek/h0SQL> archive log list51Testing软件测试网N/y]i:D~/Y5G,l
51Testing软件测试网7C5j/p3y)qDatabase log mode Archive Mode51Testing软件测试网,{ ^:x7rr7dYzz
|8`9f~ eE0Automatic archival Enabled51Testing软件测试网ZV%_ VXr a(vQ$AD B
8T{}9T|0Archive destination /opt/oracle/product/11g/dbs/arch
LMA$cZsy K0II`7a,sxq{ l3]bRk0Oldest online log sequence 216
2@$|b0b.@5c!a8L q#G(nz0gy}l7B"W(G]g0Next log sequence to archive 22051Testing软件测试网mN6u0Fb~&H5vx
*y |p&x p?0Current log sequence 22051Testing软件测试网~4r9@Rw9Jg
K o]7CsI-}s1?XK0SQL>51Testing软件测试网y`2X`SJR$Fs e
T!]'N3XW D8U:[4v051Testing软件测试网-f5S,P\ iv,Us Ep
-M(cmUqR0说明:51Testing软件测试网.qX!Y7c3fL Sd l)Lm
51Testing软件测试网|"|z5xd如果非归档模式下,启用闪回失败:
x9Jwx7} mru p0)kn(IW1Zi'w0SQL> alter database flashback on;
R9VP0G/E0R0Ln#A {RSk!k0alter database flashback on51Testing软件测试网Lq5cO&i~M s o8|3ft
51Testing软件测试网.Gs4T8K;b [C#O0s*51Testing软件测试网%x!yUALwx
'yu Zi"_o$x7c0ERROR at line 1:51Testing软件测试网r,a,}&nv
.b/Fw cwzD0ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
2M s^L9\~"hg9rj0t+B*TL/@%S0ORA-38707: Media recovery is not enabled.51Testing软件测试网m1G5@+H d^
[i t:Q(U{ev)T051Testing软件测试网 O:??(D3U9Np
51Testing软件测试网b7Gs7iJEoracle@mmsc101:~> oerr ora 38706
-c|@"R`*b(R051Testing软件测试网 m T \ OT\B t f/SA38706, 00000, "Cannot turn on FLASHBACK DATABASE logging."51Testing软件测试网j:Df)DH lq"L;|
V|%wvpEyM)BF~0// *Cause: An ALTER DATABASE FLASHBACK ON command failed.
3kD4WRn `5NIP051Testing软件测试网1`mW8Lw8_RjfG// Other messages in the alert log describe the problem.51Testing软件测试网 c3dH)r O5Ui"qd3H+Hs
51Testing软件测试网b1eI"Wg// *Action: Fix the problem and retry.51Testing软件测试网Z5r'f^,f qOhC
51Testing软件测试网)?sA;^)Coracle@mmsc101:~> oerr ora 3870751Testing软件测试网"v(d3_#a!{Z
nt7q)w_038707, 00000, "Media recovery is not enabled."51Testing软件测试网#|3u \d,~k6^ Z
b$tEy!} k\0// *Cause: An ALTER DATABASE FLASHBACK ON command failed because media51Testing软件测试网'MQhk-Wa
51Testing软件测试网LY't+T u5w6gq-u~// recovery was not enabled.
9E!w"i Gj/j051Testing软件测试网3f g1Je(u2v// *Action: Turn on media recovery with an ALTER DATABASE ARCHIVELOG
oH*B8_8P&@}h!Y051Testing软件测试网Z&Mwi8zO%Ku// command and then retry the command.
o&o|X\e^ h051Testing软件测试网 D0r6^#ra;`9?C51Testing软件测试网u sVLc%Q lu Y;f
51Testing软件测试网"eP%ss2v+Si] NZU51Testing软件测试网:h8e.m0^?Ku
XP.ksM0检查/修改恢复区设置
2~S*q0v9_;K#ezsr051Testing软件测试网VeudBbhm4g"I] nO1DNZC?051Testing软件测试网%k.r'~9b$l%I1@SQL> show parameter db_recovery_file_dest
t i6y(`]P5H{&e O051Testing软件测试网dn$@b"z51Testing软件测试网Jcz*ntxUx Y
#X(Zg+lF3^"i;W1[1G0NAME TYPE VALUE
%Zp Pi2\ OJ$A051Testing软件测试网j}+R$_?"u(P------------------------------------ ----------- ------------------------------
0s5]g4OJ+y[{G01V(RE } @I$U0db_recovery_file_dest string51Testing软件测试网aK[X:EV7r'p0e
51Testing软件测试网XB-m{F5UhVW]db_recovery_file_dest_size big integer 051Testing软件测试网 Tr.D5at!vbx"J
NnzG.P"{} g.N0SQL>
B2U7f t'S|051Testing软件测试网0z+NN+k"F9X2AuN:{0w-ip051Testing软件测试网d,du'}p8ML说明:51Testing软件测试网%wf8r}2nS&H
U!} @*K;grl,F0上述查询结果展示db_recovery_file_dest取值为空,则表示没有配置闪回区域,启用闪回后需要设置该值,string类型的串。51Testing软件测试网LiX?(\RVQ;[ ]B+qe
{~ I+Mqu051Testing软件测试网p5?U,S c X6~.l#k
51Testing软件测试网4a:V z$I.e7C0c检查/修改闪回时间设置51Testing软件测试网O3D)c#~ Lha|
WZ S\l#PH0SQL> show parameter db_flashback_retention_target
Z:rc!xL0Za n U7V051Testing软件测试网+e8Z'oTp]
51Testing软件测试网X6^z/k%]NAME TYPE VALUE51Testing软件测试网c:U.U6L(Bwq&oZ
51Testing软件测试网Q sr3H/f`pMd)i'W------------------------------------ ----------- ------------------------------51Testing软件测试网 Zx8X I8dYe5b
51Testing软件测试网lUx"W-d.v.P6g ?db_flashback_retention_target integer 144051Testing软件测试网9R(R!L;DM+{?C#p
51Testing软件测试网CLJT/D ZaSQL>
^ DHt*g:\ n,Q051Testing软件测试网(Rr!_P?X+t6v['P.Md"\051Testing软件测试网|s},SjW&M说明:51Testing软件测试网G"Qf|;yp _W
51Testing软件测试网'I)t `R X;J2H.H1、db_flashback_retention_target单位为分钟,表示能恢复数据的时间长短,默认值为1440(24*60);51Testing软件测试网 |,xcbI/n_nw
5d[)ik&ni02、如果想调整这个参数的取值,可以使用如下语句进行调整:
`D1Ky"H D0r0E~+\8[}W2j0alter system set db_flashback_retention_target=1440;
a-b-g)u]V051Testing软件测试网7w[TD,E\aN,}&w1XK gq0Nlx;{PM'}.lk051Testing软件测试网zDLH"X+iLYU!}9m;f
51Testing软件测试网(}5^XuTE^a检查闪回区域大小
EtMZ&LH;c7`051Testing软件测试网E8FW] t!{*si g#zSQL> show parameter DB_RECOVERY_FILE_DEST_SIZE
8m)lQe)AZw T-Z0d&SM1W wNd:vUG0
N9j3z#H/i4vwI(z04PBQ/|9|4x2|~)Y L0NAME TYPE VALUE51Testing软件测试网K-J&Z6UO|
~%S)O q#S0------------------------------------ ----------- ------------------------------
"j n'HR(V'Xp@"e0%x&LB,v"m6E-Upr(~0db_recovery_file_dest_size big integer 0
G]l j$k&~?1kk051Testing软件测试网/d;W!{$[)SP51Testing软件测试网$JK6bF"p|'V
8hJ"c(C V\0说明:
eo*gaj051Testing软件测试网8|IFG7Vq7\Ndb_recovery_file_dest_size参数单位为G;
t3r Za5RJ&b051Testing软件测试网2\)c"@`Qr)L^@)V9l51Testing软件测试网 S1Q9sPuhD{x @
8ELV,Bef051Testing软件测试网*y1Js'\(m#C|\
启用闪回