空间管理您的位置: 51Testing软件测试网 » pioneer of living space.......... » 日志

我们拒绝平庸，拒绝随波逐流，拒绝墨守成规，让梦想不再流浪。

oracle闪回特性

上一篇 / 下一篇 2014-06-05 14:05:57 / 个人分类：甲骨文

查看( 673 ) / 评论( 0 ) / 评分( 0 / 0 )

k1\OtGt,~!o-I0

(Q5L0~-r`oA.qa/nH051Testing软件测试网LK1M6y:E+H,M

[ A6|SV%b051Testing软件测试网h!nP8nh/|[

$M"w.^%V}e V5h:~,N0

|$G1N0v"F,N0

51Testing软件测试网v~HsqZ

特性概述

EZ#`vr+YB2q0闪回技术通常用于快速、简单恢复数据库中出现的人为误操作等逻辑错误，即：闪回只对逻辑错误有意义，对数据块损坏和联机日志损坏必须采用介质恢复。

51Testing软件测试网$?3q$P;mD/A

闪回类型

从闪回的方式可以将闪回分为：

ss{ d+_%F,S01、基于数据库级别闪回；

n!B`/R)t9Y;dk02、表级别闪回；

3、事务级别闪回.

i2R+I:vQXcS0

3rb5J#@]%r0根据闪回对数据的影响程度又可以分为:

1、闪回恢复；51Testing软件测试网$md8dUa$~,_

BNU@.i3S{3T5O[7\02、闪回查询。

闪回恢复将修改数据，闪回点之后的数据将全部丢失。而闪回查询则可以查询数据被DML的不同版本，也可以在此基础之上确定是否进行恢复等。

51Testing软件测试网 hdI+Q'F+AE

如何启用闪回启用闪回前检查

确认是否启用闪回功能

$T.DAk?*[xK0SQL> select flashback_on from v$database;51Testing软件测试网r!oDm%c&n5V

v6r"CZV1gU V3s@8I0

1v xbx r&K8H&G0FLASHBACK_ON

qU@r/f Ma[T0------------------

gO4^Av_h[8F:U+BzE0NO

t.cr xe7f*u0

SQL>51Testing软件测试网1]0P2Wtxh8v9x

S s0s4LV)C0说明：

elj1nZkffx*N3U01、flashback_on取值为NO，说明尚未启用闪回功能，取值为YES则表示启用了闪回功能。51Testing软件测试网Y-PSZd+r4g

W0u@ T8X.PbT\02、该参数仅能在数据库mount状态下修改。

-lWOu-{9wC051Testing软件测试网^s4@*_/v xu

:}0V*y)I6J:c+q0确认当前日志归档模式51Testing软件测试网3H,S/j6uu"j

SQL> archive log list

"O:ReYH d0Database log mode Archive Mode

Automatic archival Enabled51Testing软件测试网x)} c0t'R

"IZ-NXZ5Y9JR/t0Archive destination /opt/oracle/product/11g/dbs/arch

7j)pIxt}0Oldest online log sequence 216

};xxK%K%B5P0Next log sequence to archive 220

W4E OGB"`0Current log sequence 220

KQ2X8?:my0SQL>

说明：

如果非归档模式下，启用闪回失败：

({&Xn e!{o+q0SQL> alter database flashback on;51Testing软件测试网u)DBP@rc#@

alter database flashback on51Testing软件测试网1mP9}[3mTE8H

c7_%T;| [*aT8I-` l0*

ERROR at line 1:

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.51Testing软件测试网B`X"}9s Kw#c

n P7d9v)qLx0ORA-38707: Media recovery is not enabled.51Testing软件测试网2Jea aA+fr

51Testing软件测试网eg5m(` xb

oracle@mmsc101:~> oerr ora 38706

3f)`F?9L&S-z#L#B%[!F038706, 00000, "Cannot turn on FLASHBACK DATABASE logging."

// *Cause: An ALTER DATABASE FLASHBACK ON command failed.

aTwz5s^ F T-[0// Other messages in the alert log describe the problem.

Lx"[7EOtC0// *Action: Fix the problem and retry.

oracle@mmsc101:~> oerr ora 38707

38707, 00000, "Media recovery is not enabled."

6T:z_eA a0// *Cause: An ALTER DATABASE FLASHBACK ON command failed because media

_E\Y{0// recovery was not enabled.51Testing软件测试网(`.l\\0D"g

// *Action: Turn on media recovery with an ALTER DATABASE ARCHIVELOG

U0X5cNo:^j%l0// command and then retry the command.

4`D2[f"j$IaN051Testing软件测试网kX*]W,C \'W

`'m-Z Oz8O0

检查/修改恢复区设置

SQL> show parameter db_recovery_file_dest51Testing软件测试网,cTe!Xx(h

IkN%X O7D1j5H0

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------51Testing软件测试网0fqI!UN[{

M3B J Y@1i8oM"f7?9Jv$e0db_recovery_file_dest string51Testing软件测试网!zm7[(uK8Tt

S+y8ne{4o+K0db_recovery_file_dest_size big integer 0

SQL>

+_DRI r+}L?0

D.v"{9v#QGPA0说明：

上述查询结果展示db_recovery_file_dest取值为空，则表示没有配置闪回区域，启用闪回后需要设置该值，string类型的串。51Testing软件测试网L3VKh {oQ*Y

3wq*Z8MDj5WG051Testing软件测试网!g)`C_mma

检查/修改闪回时间设置51Testing软件测试网QJ$YCN

SQL> show parameter db_flashback_retention_target

5DN i!U ^0ni0

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

9Y[F:EqJ0db_flashback_retention_target integer 1440

SQL>51Testing软件测试网3oXJ9ecK

tM"vcb0说明：

^p'C4RY?(lq#l01、db_flashback_retention_target单位为分钟，表示能恢复数据的时间长短，默认值为1440（24*60）；51Testing软件测试网7G"Z5pq9Te`

xeXOYT02、如果想调整这个参数的取值，可以使用如下语句进行调整：

alter system set db_flashback_retention_target=1440;

3W(mf%Pb6L051Testing软件测试网wpQ,_'^fn E

OjRR9Y0检查闪回区域大小

SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE

@ O @9nA-UN;a051Testing软件测试网$R hYA*C-Q

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

:Ur1v4`$e(ul%q^!sfb'k0db_recovery_file_dest_size big integer 0

说明：

|j*nK8MV;c;m0db_recovery_file_dest_size参数单位为G；

启用闪回

$F3[7jz3b A2wP0说明：

下列步骤为在归档模式下，在没有启用闪回功能前提下启用闪回功能的操作步骤，详细信息如下：

[0l9~^3BD&E4[x0步骤1 手工创建闪回数据存放路径

在/opt/oracle/目录下创建flash_recovery目录。51Testing软件测试网Kg ?v.Ny

$bm sxjomH6cG}0步骤2 修改闪回数据存放路径

SQL> alter system set db_recovery_file_dest_size = 2048M scope=both;

^ [{0kl0System altered.

/i+u`D9];@051Testing软件测试网C {;e/r5{.P

W OB FgF!@.n?W[x0SQL>

Q2{5| C/u0

~Xvu3LD8C nx)R0步骤3 重新启动数据库到Mount状态

SQL> shutdown immediate

Database closed.

S|;~4FEB0Database dismounted.

ORACLE instance shut down.

Database mounted.

SQL> startup mount51Testing软件测试网K8t|'^ Dc(E

3Mp'h7U.] j0ORACLE instance started.

'KuA8F&V E0

0r Ia?v.E N0Total System Global Area 8284340224 bytes

:?[_6]%jh-v0Fixed Size 2145944 bytes51Testing软件测试网gDNy1i g

Variable Size 6375342440 bytes

Database Buffers 1879048192 bytes

Redo Buffers 27803648 bytes51Testing软件测试网 T%a:kD$Z&~gi

Database mounted.

SQL>

k4f0R9|}:G1cuY+[.[C0

步骤4 启动flashback database选项

8?f(U{ V/^r~B0SQL> alter system set db_recovery_file_dest='/opt/oracle/flash_recovery' scope=both;

2fWj/j&d&xRG0System altered.

SQL> alter database flashback on;51Testing软件测试网)y'NwZG

h0A-\h h}J0

Database altered.51Testing软件测试网2bz&t&x/?;[u;r`

yBe7y]p051Testing软件测试网 tA,? W2E^v M

|!R_r Llz2G0SQL> alter database open;

Database altered.51Testing软件测试网9rJ3B%x q5\~

6y-\P6V(`$X4e0

em`"[NC0SQL>

s0J;i3S;e*sPlk)Z051Testing软件测试网V@7\"\)L W

步骤5 检查闪回进程是否启动

SQL> ho ps -ef | grep rvwr51Testing软件测试网w+K/n{-u hP

oracle 23486 1 0 17:09 ? 00:00:00 ora_rvwr_sdp51Testing软件测试网a!~|8UHs)v

oracle 1634 14984 0 17:26 pts/6 00:00:00 /bin/bash -c ps -ef | grep rvwr51Testing软件测试网 Hy:@r fcn

oracle 1636 1634 0 17:26 pts/6 00:00:00 grep rvwr51Testing软件测试网.?PGJ3]7N6P

\:fL1O"J9i$ww6I0

SQL>

y!RB{m{0

闪回数据库（flashback database）概述 flashback database的特性

flashback data1base闪回到过去的某一时刻，闪回点之后的工作全部丢失。使用resetlogs创建新的场景并打开数据库(一旦resetlogs之后，将不能再flashback至resetlogs之前的时间点)。51Testing软件测试网FA(K*Z3M'~~a

2EI@WTB0常用的场景：truncate table、多表发生意外错误等。

m7kaE-I0

flashback database的组成

!E wqt?R:tBLq0闪回缓冲区：当启用flashback database，则sga中会开辟一块新区域作为闪回缓冲区,大小由系统分配；

启用新的rvwr进程:rvwr进程将闪回缓冲区的内容写入到闪回日志中。

(Zt'{Z3o'w;O0

闪回日志与联机重做日志的区别

Wt1m]#i*x0闪回日志不同于联机重做日志，闪回日志在联机重做日志基础之上生成，是完整

2j%\Qw`hZ0数据块映像的日志。联机日志则是变化的日志。闪回日志不能复用，也不能归档。

闪回日志使用循环写方式。51Testing软件测试网^m4hFP#X`

va?O:[` L0简单的讲，就是：联机日志会记录改变前后的值，而闪回日志只记录改变前的值。

1ZO6pV1O}I0H051Testing软件测试网7i7^pruuq

闪回数据库的实现机理

buffer cache<-->flashback cache<-->rvwr<-->闪回日志【日志只记录修改前的旧值】

闪回database的配置 1 查看闪回数据量、时间等相关信息

--下面查看闪回区分配的大小为大约32M，闪回1440分钟(24小时)以内的数据则需要46M左右的空间51Testing软件测试网9F)M"OsW8Wx

,a&Z%r!_hlvf0SQL> select oldest_flashback_scn old_flhbck_scn,oldest_flashback_time old_flhbck_tim,

2 retention_target rete_trgt,flashback_size/1024/1024 flhbck_siz,

3 estimated_flashback_size/1024/1024 est_flhbck_size

"j,J]w_rnt}t(~W04 from v$flashback_database_log;

9Ywc7Z1i {e%[0OLD_FLHBCK_SCN OLD_FLHBCK_TIM RETE_TRGT FLHBCK_SIZ EST_FLHBCK_SIZE

2huL5KW0-------------- ------------------- ---------- ---------- ---------------

M"hkEt05813199 02/09/2012 17:09:06 1440 32.0078125 46.2890625

9B5^(l P4jkH051Testing软件测试网^3AfoL

SQL>

z}^-TkK~2H&`0说明：

列oldest_flashback_time说明了允许返回的最早的时间点。

2 查看闪回

SQL> set wrap off

J]X-G~)X8ht0SQL> select * from v$flashback_database_stat;51Testing软件测试网(].Eae'v5^ Y

?PO:WKK8K0truncating (as requested) before column ESTIMATED_FLASHBACK_SIZE

#t(KQ d,S$FE]"N0

%y%Wx @ Y#}u0BEGIN_TIME END_TIME FLASHBACK_DATA DB_DATA REDO_DATA

$brR!]KbS*Q7c3]A0------------------- ------------------- -------------- ---------- ----------

-i0fLh.Fja002/09/2012 17:09:06 02/09/2012 17:57:12 1351680 1466368 326144

gMnMh2C f0

si9P)bK#x|-T$_0SQL>

3 查看闪回中sga分配的空间大小

6EO&T;l ~{C)Q&G0SQL> select * from v$sgastat where name like 'flashback%';51Testing软件测试网2P$GMHi$K

51Testing软件测试网'~8`&znh:k

'kfez }Uv0POOL NAME BYTES

------------ -------------------------- ----------51Testing软件测试网X&x:s9}0z-R

~ p H q$w0shared pool flashback generation buff 33554432

shared pool flashback_marker_cache_si 9200

F1fB$U#SeYgg0

x5L(_3?p9aF0SQL>51Testing软件测试网9Y Cdh&I7\'G

4 查看生成的闪回日志

-kel/K0@Dp["]j;M0SQL> ho ls -hlt $ORACLE_BASE/flash_recovery/SDP/flashback

total 33M

2Z{wVI {)M$HRM0-rw-r----- 1 oracle oinstall 33M 2012-02-09 17:59 o1_mf_7m739k3t_.flb

SQL>

51Testing软件测试网/R|.{$pUe+~

U9D%b6T3D051Testing软件测试网 ~8U#]R.b

使用flashback闪回数据库实践

t&Y+Uwfi;j0前提条件：归档日志可用。

Km.KQh-I+H'a0步骤1 关闭数据库51Testing软件测试网;l h{Q$R1Jq

1_R:sap6nJ0步骤2 启动数据库到mount状态

!ls6C9xc.\-Yh~NJQ0步骤3 闪回至某个时间点、scn或log sequence number51Testing软件测试网L0X^|]

;\zJ9k!P,zV"H0步骤4 使用resetlogs打开数据库

使用sqlplus闪回数据库 sqlplus几种常用的闪回数据库方法

l(uXIUz0基于SCN闪回51Testing软件测试网 d2W7~8`4|{

yD)?XeC0FLASHBACK [STANDBY] DATABASE [<database_name>] TO [BEFORE] SCN <system_change_number>

51Testing软件测试网;H f4q0w3t,M

_a%O0g"aFt0基于时间戳闪回

FLASHBACK [STANDBY] DATABASE [<database_name>] TO [BEFORE] TIMESTMP <system_timestamp_value>51Testing软件测试网Sq5j[sdK

M^$fj4TO~}b051Testing软件测试网k9\0VT1h

基于时点闪回

wIjUe8j%U&S0FLASHBACK [STANDBY] DATABASE [<database_name>] TO [BEFORE] RESTORE POINT <restore_point_name>

eW/MPE+QNX}-T;ly051Testing软件测试网"i7F^.J,] U`

如下面的示例：

d8[U(Xm'w%Jw0SQL> flashback database to timestamp('2010-10-24 13:04:30','yyyy-mm-dd hh24:mi:ss');51Testing软件测试网EF'Y\,v;o

:j8[f8O1?;`7PQ&bM1F5M{051Testing软件测试网#c;P;[/E d(n

SQL> flashback database to scn 5813199;

51Testing软件测试网e7QG*TG R

#^wS-Rpr(f_0SQL> flashback database ro restore point wyz_test;51Testing软件测试网OTf;zl)gi

R9|-x'Hw/k]gJEW051Testing软件测试网5w2Z(t[&@3q1A

a.基于时间戳闪回

步骤1、创建测试表，并插入数据

kN;H8k dFZqpL*K0oracle@mmsc101:~> sqlplus mmsg/mmsg@sdp51Testing软件测试网:ay0}1R)~8x

51Testing软件测试网4PjIz-x(~

S EG7li%Q8x+j_wM.S#t0SQL*Plus: Release 11.1.0.6.0 - Production on Thu Feb 9 18:14:31 2012

51Testing软件测试网&axZc|`p

Connected to:

9S*aQTyN:e'c0Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production51Testing软件测试网$K.|#D+hJ

With the Partitioning, OLAP, Data Mining and Real Application Testing options

{)Q!Y Ix$A0SQL> create table scn_test(id int);

Table created.51Testing软件测试网6fj!v;yqe

av6[:f+|jA:{$Q0

SQL> insert into scn_test values(1);

6U-x/T&BK051Testing软件测试网'm1I8AK@

1 row created.

v:F uP5j&L*c0

$CsG C l(DgK:Z I0SQL> insert into scn_test values(2);

R-Z4y7ht2E*@d051Testing软件测试网.Ti!?:CF

1 row created.

6j[I$\t(El0

,[4X8{E0r0w)@$FnJ3}0SQL> commit;

51Testing软件测试网&h`^;HJ7jB1a

+L,N4Q `8Kb)dZ0Commit complete.

SQL> select count(0) from scn_test;51Testing软件测试网!l*jC'N}ilZ

COUNT(0)

I9{8Ng1?/YP`0----------

251Testing软件测试网V@,q7q4v,p

]R^i)W||051Testing软件测试网L!{P{7BzK'g.[

C}9v7g1_l&Y#H]*_!q~0SQL>51Testing软件测试网"X%MZsm

GMd"i ok0Y0说明：51Testing软件测试网Ql{&EcM

4fZq?3W0上述测试表中有两个记录。51Testing软件测试网roG2C|:Rg

3_EqRAM0步骤2、获取系统当前时间51Testing软件测试网7O3J+|tc/q)Rr

MY F'W%GgL0SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') time from dual;

&|xI,C%TS6Z{m0

k9?A4`+Y&p0TIME

---------------------------------------------------------

2012-02-09 18:39:30

x1P_3qO9w0SQL>

pg xCR m)D0

h8K4Rs*o3b0步骤3、删除表scn_test

SQL> drop table scn_test;

)gTWxb^0

_&Q*QtO,]2b:?,E-p0Table dropped.

BmJq Y)KJ,c1qa T051Testing软件测试网 A"b(Be^7j|V

SQL> commit;

g2c/M,V*E|7@!V)Y[0

Commit complete.

LiW&J o^`CsI051Testing软件测试网B'oN0Xb

TG,[q@#\ de0SQL>

2Y&YbI+iO051Testing软件测试网m'e4Y"FmZ

@jFs5jBr4V051Testing软件测试网^1\9}bf

Wf8T9Chi!kS0步骤4、新创建表tmp

)\.y@[RkyBT,z$F0SQL> create table tmp as select * from modules;

+} vw[6^`n0Table created.

l5}~h5N'Xi9Jq0SQL> commit;

[#g%X"p5d)^0

Commit complete.

){{T`$|.|u m0

SQL>

KT9J)fd0说明：51Testing软件测试网%Gr.F,B6or*P

B#x6lx#{ O0目的是为了验证闪回后这张表不存在。51Testing软件测试网5n7KRCt

51Testing软件测试网s.UKT b]5DX

步骤5、启动数据库到mount状态51Testing软件测试网-n1U4zmD

oracle@mmsc101:~> sqlplus / as sysdba

h7z6q.H:a051Testing软件测试网&rLq9~ J

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Feb 9 18:24:49 2012

'W2t U&m#_h0

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production51Testing软件测试网6pq^)i.l6l

With the Partitioning, OLAP, Data Mining and Real Application Testing options

51Testing软件测试网}JvDL

SQL> shutdown immediate

,}%mAJ#Bpb.e7{WrR0SQL> startup mount

9l'J5]sr0

步骤6、dba用户实施闪回

SQL> flashback database to_timestamp('2012-02-09 18:39:30','yyyy-mm-dd hh24:mi:ss');

%w;\x.^+o*AE v0Flashback complete.51Testing软件测试网+g*WyF O

SQL> alter database open resetlogs;

_+`)?RU051Testing软件测试网/}j|6o c-qt

L'o?Zm0Database altered.

SQL>

,?*l0bL3`(`Q0e0步骤7、闪回结果查看

SQL> connect mmsg/mmsg@sdp

Connected.

p#c-o6I;J x0SQL> select count(0) from scn_test;

mXr~&Zs;K1{0

km aP5mn(D0COUNT(0)

H*O;{+K_UCJ0----------

0xSH1^N$b9^$@~0

2P_ M"n2m0SQL> select * from tmp;

select * from tmp

v'WV.ZpN~|$~$`0ERROR at line 1:

u1l*Ify$S#~'W;D$I0ORA-00942: table or view does not exist

%x;D#`ebD^g0

-K_'j5QJjjY0SQL>

上述查询结果说明表已经闪回恢复，闪回点之后的数据全部丢失。51Testing软件测试网 P'sk){.`0^*f

.QWP\ E4lLd051Testing软件测试网MO)lw6E2oZ

b.基于SCN号闪回

^U?G`$u0步骤1 获取当前SCN

4tcUkn0cMh0f_0SQL> select current_scn from v$database;

#CK M?'p3Zcp}M0

CURRENT_SCN

4rQ s"t,p$]B0-----------

&o.Ks5pB+w_Z0581597051Testing软件测试网n:g/Xb2Y

W0ra)lX/^0

{9P#_P$~dn0步骤2 删除mmsg用户下的scn_test表

SQL> connect mmsg/mmsg@sdp51Testing软件测试网[ G&V#h],Yej

Connected.

0B#fz!s\#G*b^5o0SQL> drop table scn_test;

n/og-B%Din4\ }0

Table dropped.51Testing软件测试网e {!e^|1`+h

#z8m(`z[^^XH'yDT0SQL> commit;

51Testing软件测试网%bA m9F/kd

Commit complete.

?"EN+g%e0

7j Z `"V_!a5k rQ0步骤3 手动执行检查点

-A-xKC"_7u#E?$D0SQL> alter system checkpoint;

GmG0~(?K0v8?0

%Jk W@(|ZL%xqU0System altered.

YUlSPog8V051Testing软件测试网iE cQ)E4Z"Y

m8b%[9^V@8h7~2V0SQL>

r.n5O{@_5P051Testing软件测试网C8GprQ e hr

步骤4 实施闪回

SQL> shutdown immediate

~O/~O9}"p[|.m0Database closed.51Testing软件测试网oN O{3Ea

Database dismounted.

ORACLE instance shut down.

`S5fc5|H0SQL> startup mount

ORACLE instance started.51Testing软件测试网J)KO(NO5o

s"M1Nf#Yy\Kwf0

7p~2d!{9F&Q0Total System Global Area 8284340224 bytes51Testing软件测试网 w`~9Y{#o

;{| f`5ZD'B eP)xv0Fixed Size 2145944 bytes

Variable Size 6375342440 bytes

Database Buffers 1879048192 bytes51Testing软件测试网(Dw2Z}d

&EK/GO4xP9~0Redo Buffers 27803648 bytes51Testing软件测试网%RcP-f4]@

Database mounted.

8K't,aQV6x&ac H,Z-@%[0SQL> flashback database to scn 5815970;

)v)f%q&i3bvi,~,N$KR0

Flashback complete.51Testing软件测试网g3Sn%N8?u

Y-KdZ(WZ!jr@0SQL> alter database open resetlogs;

EkpA7L$X'tZ)a]0Database altered.

SQL>

:aT oC6p0O#s0步骤5 查询闪回结果

M GKRPpKz1\0SQL> connect mmsg/mmsg@sdp

"Q(mswsx?0Connected.

,`cYGL)~0D{0SQL> select count(0) from scn_test;

C-{yw4mE2T5n h0COUNT(0)

$m"\&?y%VpI0----------51Testing软件测试网,v1Dk(JW

51Testing软件测试网,j+x4s5V#u}\

(]SLOq%]v\0SQL>51Testing软件测试网y,v(h(x9W8OO

dr2rE(L0

VmgbH[;u}0

c.基于时点闪回

O%NWb^ Pl0步骤1 创建测试表

dE,Un G(h0SQL> create table test(id int,describe varchar2(20));

Table created.

j `{B@051Testing软件测试网!I,g PXZd

"ym.]P^L:o0SQL> insert into test values(1,'ABC');51Testing软件测试网#d/t3oPd&j^

PC%p"Y7J||7b-v }01 row created.

51Testing软件测试网m-Zn'_3b^

SQL> insert into test values(2,'DEF');

Y-wc-l@"FyE01 row created.

!pM[.Ixui051Testing软件测试网D\(vb z)HN"G

SQL> commit;

1kF#?P)B0

Commit complete.

SQL> select * from test;51Testing软件测试网fS]K1MGsP

ID DESCRIBE

zF+rm0|&o(~9V~+|@0---------- --------------------

;h*`I8F1To i6@01 ABC

2 DEF

_a3f a1DGb9z@0SQL>51Testing软件测试网5fU!Ur3xV

8[R/yt1EpCpS I$N0

|)ClrWXH0步骤2 创建闪回点51Testing软件测试网,\Rs&x w

oracle@mmsc101:~> sqlplus / as sysdba

0^-zMv+]|z"M051Testing软件测试网bWajw"W`~

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Feb 9 18:54:11 2012

])tX:n*G x Y;uN Q/k0

2DkK#E-?0

k*}2T K%JB$g0

Connected to:

U?qs1IU!A0Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

VVR,Nt9|a0With the Partitioning, OLAP, Data Mining and Real Application Testing options

51Testing软件测试网7`r#f+cdL

SQL> create restore point wyz_test;

Restore point created.

SQL>

I6z G:CS_K%J0

#L@|7x'{g+CP1t051Testing软件测试网{Uoz,@5XM

-XM7oZ~F Yhw0步骤3 再次插入记录

{5r5|-Qwww0SQL> insert into test values(3,'GHI');

FO[&{H0

HgJ${4]h#B4|01 row created.

SQL> commit;

51Testing软件测试网[4ZG0Ho3v|

X1f1d`2o0Commit complete.

51Testing软件测试网JM*}$`%o

SQL> select * from test;

,w!UI@n7lFX9h]051Testing软件测试网&Xo~ Pr

ID DESCRIBE51Testing软件测试网FZ2r+[$uR&x;c

okr{:G%v4um0---------- --------------------51Testing软件测试网b-Ndf1\}o

1 ABC

~3p/O/Nq0RLa.hW02 DEF

3 GHI

wQ8\"du*Io?c0

q Mpw-|%L0SQL>51Testing软件测试网{7r9AU3sC

51Testing软件测试网q na*{#T

步骤4闪回实施

SQL> shutdown immediate51Testing软件测试网F};h!w7f]c

Database closed.

%Qe gN2S:j0Database dismounted.

ORACLE instance shut down.51Testing软件测试网"^E)}4{X

/a~}nrv}m0SQL> startup mount51Testing软件测试网6f4IlU_wK

W#xse Z3P)?o9K0ORACLE instance started.

51Testing软件测试网#N-_ \khJ

Total System Global Area 8284340224 bytes

{5F&\;@T}0Fixed Size 2145944 bytes

;C'g{e0tc7q0Variable Size 6375342440 bytes

UMm2eyR c0j!_Y0Database Buffers 1879048192 bytes51Testing软件测试网 K$f6H}m{(q

%kn7x/Ly0Redo Buffers 27803648 bytes

ch&n$T3\ b.V0Database mounted.

3q3gDau1C0SQL> flashback database to restore point wyz_test;51Testing软件测试网-uM6\~&U

h7_~pk0Flashback complete.

&~0c#U~1I"z-[-|n051Testing软件测试网PH+lRnWl

SQL> alter database open resetlogs;51Testing软件测试网DeKqg/y

eR+cL6G#D0Database altered.

zu.i5M {!wI'm0

SQL>51Testing软件测试网&[7WXy oJ2T

q,q/uZy_y051Testing软件测试网eOi-M%]m

步骤5 查看闪回结果

SQL> connect mmsg/mmsg@sdp

Connected.

-} Wh&Kb0SQL> select * from test;51Testing软件测试网V'{.kXt!j

1JaC}1E ur.}0

x1\y`V0ID DESCRIBE

+F T[;s ] qfj(tL^)B0---------- --------------------

1 ABC51Testing软件测试网N AEiH3V;m.I

/F4vK)v!E4R3fF02 DEF

S.{!e0xr? M;C051Testing软件测试网J1b}(l_ r o

]EaD"Z'FK0SQL>

$K2U@)H0fG'y2s'z;e9y0说明：

A9qGVK!T0闪回点是全局的，不区分用户，即在某个point后，不同用户做了不同的操作，只有闪回到该point后，该point后的不同用户的操作都将丢失。

~ tgv+VcN051Testing软件测试网1e,X\dO)j'h

使用RMAN进行flashback database 使用RMAN进行闪回数据库的几种常用办法

0R @8i3O+a0RMAN> flashback database to scn=918987;

RMAN> flashback database to sequence=85 thread=1;

QAE%SM0

a.基于时间戳闪回

{ni,{q@S0步骤1 创建测试表

oracle@mmsc101:~> sqlplus / as sysdba

D^~5F:Kog0

7z9e\ n&aP3D*Y0SQL*Plus: Release 11.1.0.6.0 - Production on Thu Feb 9 19:02:24 2012

4z1lAJAP} g0

1s#u0wkK.I%E0

3D-Umc.Bi0Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

I&P-[,})r8@4O0With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table mmsg.tmp as select * from mmsg.modules;

Table created.

t:C'{$[8G Eoo7I051Testing软件测试网9A6s+wwGf1h

,lh ~(F(O6W$q0SQL> select count(0) from mmsg.tmp;

t2hwgb_\8@/I ~051Testing软件测试网;X{-T;R)Y-V

COUNT(0)

7a P#GbID&Z0----------

1851Testing软件测试网;Cx[k1^k

v+~|)R X4M0

步骤2 获取数据库当前时间

(|8D1e;{!R u,cm0SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') tm from dual;51Testing软件测试网-r9Z;VFp

MA#y%ym\Hl%b!q4zF.vdE0

Cnct2?_0TM

-------------------

;J.q-reT02012-02-09 19:03:1451Testing软件测试网9\K!F!IXj

3]Z(D$FI9{/QU0步骤3 删除测试表

SQL> drop table mmsg.tmp;

51Testing软件测试网/E ^-WmV

Table dropped.

SQL> commit;

%y0E ^1zbkJ7z0Commit complete.51Testing软件测试网gJRE&wh}

D&q*|dt\~3xY0

步骤4 闪回实践51Testing软件测试网'[5Z7|?K!L%@

Y(] lWhcF(o3@0SQL> shutdown immediate51Testing软件测试网S}gb Dwx

@@9s)n;G4f,Zil0Database closed.51Testing软件测试网3t,ab J1e*w

Database dismounted.

7\E,id5m\"K0ORACLE instance shut down.

)s&n&rQ5^0SQL> startup mount51Testing软件测试网seY-U0u)h

ORACLE instance started.

@&p&f`XLC|051Testing软件测试网Ne0D*p2Z3CM:^

Total System Global Area 8284340224 bytes

Fixed Size 2145944 bytes51Testing软件测试网;C ov6IV|9]

H/_]m B0f`0Variable Size 6375342440 bytes

)Q$P}:I[K0Database Buffers 1879048192 bytes

@[bV0T"AM"h0Redo Buffers 27803648 bytes

Database mounted.

SQL>51Testing软件测试网?.h U-cM0U

-C.?j/X'`0

oracle@mmsc101:~/product/11g/bin> ./rman target/

Tr)ij}%j9Yz051Testing软件测试网IA-n1g6q&no

Recovery Manager: Release 11.1.0.6.0 - Production on Thu Feb 9 19:05:03 2012

51Testing软件测试网!B{ Oc#Oz8W

51Testing软件测试网o\B},t#Cu

ErI&Z%d"]&I0connected to target database: SDP (DBID=2998391018, not open)51Testing软件测试网w"k Ut(\J&n

J2p*G7A$aK;DkX5x0

6G3]5V4U"cVX0RMAN> flashback database to time="to_date('2012-02-09 19:03:14','yyyy-mm-dd hh24:mi:ss')";

51Testing软件测试网 |(iIU3S

Starting flashback at 02/09/2012 19:05:09

using target database control file instead of recovery catalog51Testing软件测试网 d2d%xD+?L'P

[n4t+?7p/m^V5[;z)L0allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1086 device type=DISK

uOiY0K051Testing软件测试网JtH)HX9U

6\b$p-~2B]9y0

starting media recovery

|#L^ cT~"Op I0media recovery complete, elapsed time: 00:00:0351Testing软件测试网x9KK Bg5uI

Finished flashback at 02/09/2012 19:05:14

RMAN>

#F2i-FIw|,S[zP0oracle@mmsc101:~> sqlplus / as sysdba51Testing软件测试网{y \/e)b%_

6Y [/[]m0SQL*Plus: Release 11.1.0.6.0 - Production on Thu Feb 9 19:05:36 2012

.hB.d9ySXYa D0L0

@4P|2V4L1ZK051Testing软件测试网0Ot[^F2t7A

;O:@ ~,WQ+r0Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

p+l*C0q7\u r;CL0G0With the Partitioning, OLAP, Data Mining and Real Application Testing options

]O2X$_ v9_)B @w%K051Testing软件测试网0M?.e\8| iu

BBJr)k*]G0SQL> alter database open resetlogs;

IJ6i,@N2t051Testing软件测试网c-tj4iXR

Database altered.

%BE_pe G5R*x0步骤5 查看闪回结果

)P3u Kq%R"`H0SQL> select count(0) from mmsg.tmp;

{x8AGsL9u(F;QZ$S7F2n g0

6RfG pb"q:{D:w5O0COUNT(0)

]P7Q&{WMLlD+x$s6p0----------

1851Testing软件测试网,~6z \z7S

51Testing软件测试网 ^!V%h3@^qhi

%TVmfE x {O0SQL>

D&?+v9fG1m0

b.基于SCN闪回

'ltX%tY)bj)w0

c.基于sequence闪回

~+IWJ8ync[Yg[G"x)m0

tFSzJ"Ds/Q0说明：51Testing软件测试网\utFF:N'V+n

RMAN闪回操作和sqlplus闪回类似，命令可参考sqlplus的操作。51Testing软件测试网&C3X]Z4vC

*G K p5s'{051Testing软件测试网1aW.M`J7NO @

;Gf/Qmk0es_'w0

闪回表

PwhaKIUZ7f+t/J0就是将表里的数据推回到过去的某个时间点，是利用undo表空间里记录的数据被改变前的值，如果闪回表所需要的undo数据，由于保留的时间超过了初始化参数undo_retention所指定的值，从而导致该undo数据块被其他事务覆盖，就不能恢复到指定的时间点了。

闪回表的局限：当前的时间点到要闪回到的时间点之间不允许有ddl操作，否则闪回无法成功。51Testing软件测试网Y^;qV3t@/H

%O5yW5fs051Testing软件测试网6lW}#?\5F

示例：

SQL> select count(0) from mmsg.tmp;

Am7J:S4Ct/j0

COUNT(0)

Y|E5HgAA0----------

+v*H {/A7l6V018

f?,cu)|b!^0

SQL> drop table mmsg.tmp;

Table dropped.

$v3iG'~cl {D%}2^0

J%xkH-u0S;vMZ+\0SQL> flashback table mmsg.tmp to before drop;51Testing软件测试网9Lj,`]SX

O*XZX_0Flashback complete.

SQL> select count(0) from mmsg.tmp;51Testing软件测试网TY.?9z"e7]$g

ZJ,Hg?2g W0

#`@+SV3D0COUNT(0)

dD#u'h/G!k0----------

Q?P}4Q018

mE`:h pz`\0@Gx0

SQL>

;Q1Y*}*]5Bd hj#C0

说明：

闪回表，需要在数据库启用回收站条件下才能进行表的闪回，否则报错：

SQL> FLASHBACK TABLE mmsg.TEST TO BEFORE DROP;51Testing软件测试网KQ6\ `VH

&D#pC,q K3b#u-D |&r0FLASHBACK TABLE mmsg.TEST TO BEFORE DROP

*51Testing软件测试网'Y7UWW&l

ERROR at line 1:

O!o.a(QhW2{K0ORA-38305: object not in RECYCLE BIN51Testing软件测试网 Tj s7^p3E.A

51Testing软件测试网tp5Kq*C&E+x

闪回版本查询

{Mq2x.W1dO5`0所谓版本指的是每次事务所引起的数据行的变化的情况，每一次变化就是一个版本。闪回版本查询使用的undo表空间里记录的undo数据。

51Testing软件测试网a2Vq)Be0J

示例：

`d+_)m0\#r2m3s`01、创建测试表，并插入数据；

!a gqHv#CX02、查询表中数据总量；51Testing软件测试网Sn*\4[4n

51Testing软件测试网SL TnK!A k7H

3、drop 表中某个记录，查询表中总量；

4、查看对应的该表中的闪回版本信息

select versions_starttime, versions_endtime, versions_xid,
versions_operation, moduleid
e.u"N2S,~%Q%H^+h:J A H)p0from modules versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME
eT!L&pf(A0
*jtAm m+wv {a0

V9Ex$~,B(YE6?0说明：

其中modules为测试表的表名称。

51Testing软件测试网)?#o8vq'~i m

闪回事务查询

闪回事务查询提供的是一个视图，flashback_transaction_query，利用这个视图，可以显示哪些事务引起了数据的变化，并为此提供了撤销事务的SQL语句。

Exl u&xHF(U0闪回事务查询利用的是undo表空间的undo数据

NG9m_(z;M\,@0

示例：51Testing软件测试网[!^#Ibp6|

\a+MV!r Yd01、dba用户登录数据库，查询视图信息；

select * from flashback_transaction_query where table_owner='MMSG'；

Xkyfdx&d02、查找对应的闪回事务，确定闪回操作时间点，执行UNDO_SQL字段提供的回滚语句进行闪回操作。51Testing软件测试网b~%hOmRi

#y+MV9?td%Z051Testing软件测试网2z s| kF9l-H

闪回查询

查询过去某个时刻表的数据的情况，一旦确认某个时刻的数据满足我们的需求以后，可以根据这个时间执行闪回表。51Testing软件测试网%CWKB zN3g

附录查看闪回区使用情况

SQL> select name,space_limit/1024/1024 sp_limt,space_used/1024/1024 sp_usd,

YFD P&EQ3`/A+ue02 space_reclaimable/1024/1024 sp_recl,

3 number_of_files num_fils from v$recovery_file_dest;

Ucy)eR3Os0

UIp.ph&xaR0NAME51Testing软件测试网uYdB:m/|

gZw;m.l,]0--------------------------------------------------------------------------------

#_ \ H'Y,H0SP_LIMT SP_USD SP_RECL NUM_FILS

5Pa,a1S{LS0---------- ---------- ---------- ----------

/opt/oracle/flash_recovery

2048 918.15918 0 12

5^(B [;},p-MuZ1_ }Zr051Testing软件测试网$zg~"HK4{,j

%oVr5gx M0SQL>51Testing软件测试网*DQ,VV)t

将某些表空间排除在闪回之外

SQL> alter tablespace MMSG flashback off;

SQL> select name,flashback_on from v$tablespace where ts#=4;

NAME FLA

~*{!J6Q \j*}+^0

NZJ6C'I7e$kUT0--------------- ---

51Testing软件测试网,n@atZ%K

MMSG NO

;^(]7e$@K-zd0

0a1P#`2A8C!aa5W0说明：

如果需要对上述表空间启用闪回功能，则需要在mount模式下对该表空间进行开启该功能。

收藏举报

TAG:

查看全部评论

丰卉者

用户菜单

我的栏目

标题搜索

日历

数据统计

访问量: 109185
日志数: 89
图片数: 1
文件数: 15
建立时间: 2013-03-01
更新时间: 2018-08-23

oracle闪回特性

用户菜单

我的栏目

标题搜索

日历

我的存档

数据统计

RSS订阅