Oracle-Flashback使用 -01

记录级别：Flashback version query , flashback transaction query

表级别：flashbackup drop , flashbacktable

数据块级别：flashback database (只有这个需要DBA介入)

1.flashback database(RVWR)

à这个依赖于flashback log,可以作为不完全恢复的技术

使用flashback database有一些限制：

a.不能用于media failure

b.不能用于数据文件删除或shrink缩小了数据文件大小，这时必须用rman先restore数据文件，然后在flashback recovery

c.控制文件是从备份中恢复出来，或重建控制文件，不能用flashback database

d.flashback恢复到的scn，取决于flashback log中最早的scn。

启动flashback database

SQL>alter system set db_recovery_file_dest_size=2G;

System altered.

SQL>alter system set db_recovery_file_dest='/u01/flash_recovery_area';

System altered.

(这两个参数,先启动db_recovery_file_dest_size,然后db_recovery_file_dest)

SQL>shutdown immediate

Database closed.

Database dismounted.

ORACLEinstance shut down.

SQL>startup mount;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area835104768 bytes

Fixed Size2217952 bytes

Variable Size629147680 bytes

Database Buffers197132288 bytes

Redo Buffers6606848 bytes

Database mounted.

SQL>alter database flashback on;

Database altered.

SQL>select name,current_scn,flashback_on from v$database;

NAMECURRENT_SCN FLASHBACK_ON

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

DEER0 YES

SQL>alter system set db_flashback_retention_target=1440;(单位:分钟)

System altered.

à表示能回退多少分钟.

测试flashback database

14:51:09 SQL>select count(*) from test;

COUNT(*)

----------

55728

14:51:14 SQL>select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

11475425

14:51:20 SQL>truncate table test;

Table truncated.

14:51:24 SQL>conn / as sysdba

Connected.

14:51:39 SQL>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

14:51:55 SQL>startup mount;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area835104768 bytes

Fixed Size2217952 bytes

Variable Size629147680 bytes

Database Buffers197132288 bytes

Redo Buffers6606848 bytes

Database mounted.

14:52:07 SQL>flashback database to scn 11475425;

或者flashback database to timestamp to_timestamp('2012/06/10 14:51:24','yyyy/mm/dd hh24:mi:ss')

Flashback complete.

14:52:21 SQL>alter database open read only;

Database altered.

14:52:31 SQL>select count(*) from test;

select count(*) from test*

ERROR at line 1:

ORA-00942: table or view does not exist

14:52:46 SQL>conn scott/oracle

Connected.

14:52:53 SQL>select count(*) from test;

COUNT(*)

----------

55728

14:52:58 SQL>conn /as sysdba

Connected.

14:53:07 SQL>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

14:53:17 SQL>startup mount;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area835104768 bytes

Fixed Size2217952 bytes

Variable Size629147680 bytes

Database Buffers197132288 bytes

Redo Buffers6606848 bytes

Database mounted.

14:53:27 SQL>alter database open resetlogs;

Database altered.

使用到的视图:

select*fromv$flashback_database_log

select*fromv$flashback_database_logfile

select*fromv$flashback_database_stat

2.flashbackup drop

只能用于非系统表空间和本地管理表空间，recyclebin中对象只支持查询。

Flashback table to before drop [rename to xxxxx](先入先出清除)

Purge tablespace tablespace_name

Purge tablespace tablespace_name user username

Purge recyclebin

Purge dba_recyclebin

Drop table xxxx purge

Purgeindexrecycle_bin_object_name

3.flashback query

(a).flashback queryà只能查看一个时间点的

select * from t1 as of timestamp

to_timestamp('2012/06/10 15:18:47','yyyy/mm/dd hh24:mi:ss');

select * from t1 as of timestamp

to_timestamp('2012/06/10 15:18:47','yyyy/mm/dd hh24:mi:ss')

where id not in(select id from t1)

(b).flashback version queryà可以查看一个时间段内的

select versions_xid,versions_startscn,versions_endscn,versions_operation,id

,name from t1 versions between scn minvalue and maxvalue;

Ora_rowscn :伪列

SQL> select ora_rowscn,id,name from t1;

ORA_ROWSCNID NAME

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

114775371 a

114775372 b

114775373 dddddd

SQL> update t1 set name='xxxxx' where id=3;

1 row updated.

SQL> select ora_rowscn,id,name from t1;

ORA_ROWSCNID NAME

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

114775371 a

114775372 b

114775373 xxxxx

SQL> commit;

Commit complete.

SQL> select ora_rowscn,id,name from t1;

ORA_ROWSCNID NAME

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

114776621 a

114776622 b

114776623 xxxxx

从上面例子看出，只有commit之后ora_rowscn才会发生改变。

Ora_rowscn是数据块级别的，一个数据块内所有记录都是一个ora_rowscn,

数据块的任意记录被修改，快内所有记录的ora_rowscn都会改变。

create table t4 (id number(3),name varchar2(10)) rowdependencies;

à确保每行的ora_rowscn都不一样。

T2没有rowdependencies，T4有rowdependencies。

SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid) block,ora_rowscn,id,name

from t2;

FNOBLOCK ORA_ROWSCNID NAME

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

49799114778651 a

49799114778652 a

49799114778653 xxx

SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid) block,ora_rowscn,id,name

from t4;

FNOBLOCK ORA_ROWSCNID NAME

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

49831114783601 a

49831114803512 yyyy

49831114783993 xxx

alter system dump datafile 4 block 9799;(dump t2)

内容如下：

Start dumpdatablocks tsn: 4 file#:4 minblk 9799 maxblk 9799

Block dump from cache:

Dump of buffer cache at level 4 for tsn=4, rdba=16787015

BH (0x76fe0778) file#: 4 rdba: 0x01002647 (4/9799) class: 1 ba: 0x76ce6000

set: 5 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 450,28

dbwrid: 0 obj: 82452 objn: 82452 tsn: 4 afn: 4 hint: f

hash: [0x76fe0958,0x9094d8b0] lru: [0x76fe0ac0,0x76fe0730]

ckptq: [NULL] fileq: [NULL] objq: [0x76fe0ae8,0x8df6a258]

st: XCURRENT md: NULL tch: 8

flags: block_written_once redo_since_read

LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]

cr pin refcnt: 0 sh pin refcnt: 0

BH (0x76fe08a8) file#: 4 rdba: 0x01002647 (4/9799) class: 1 ba: 0x76ce8000

set: 5 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 450,28

dbwrid: 0 obj: 82452 objn: 82452 tsn: 4 afn: 4 hint: f

hash: [0x78bf5248,0x76fe0828] lru: [0x76fe0d20,0x77bf92a0]

lru-flags: moved_to_tail

ckptq: [NULL] fileq: [NULL] objq: [NULL]

st: CR md: NULL tch: 1

cr: [scn: 0x0.af2363],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.af2363],[sfl: 0x

0],[lc: 0x0.af2358]

flags: redo_since_read

cr pin refcnt: 0 sh pin refcnt: 0

Block dump from disk:

buffer tsn: 4 rdba: 0x01002647 (4/9799)

scn:0x0000.00af2369seq: 0x01 flg: 0x06 tail: 0x23690601

frmt: 0x02 chkval: 0x7ec8 type: 0x06=trans data

Hex dump of block: st=0, typ_found=1

Dump of memory from 0x00007FF7F727AA00 to 0x00007FF7F727CA00

7FF7F727AA00 0000A206 01002647 00AF2369 06010000[....G&..i#......]

7FF7F727AA10 00007EC8 00000001 00014214 00AF2351[.~.......B..Q#..]

7FF7F727AA20 00000000 00320002 01002640 00140006[......2.@&......]

7FF7F727AA30 00002301 00C004FC 002807FB 00002003[.#........(.. ..]

7FF7F727AA40 00AF2369 00000000 00000000 00000000[i#..............]

7FF7F727AA50 00000000 00000000 00000000 00000000[................]

7FF7F727AA60 00000000 00030100 0018FFFF 1F641F76[............v.d.]

7FF7F727AA70 00001F64 1F900003 1F761F88 00000000[d.........v.....]

7FF7F727AA80 00000000 00000000 00000000 00000000[................]

Repeat 500 times

7FF7F727C9D0 00000000 00000000 012C0000 04C10202[..........,.....]

7FF7F727C9E0 78787803 0202012C 610104C1 0202012C[.xxx,......a,...]

7FF7F727C9F0 610103C1 0202012C 610102C1 23690601[...a,......a..i#]

Block header dump:0x01002647

Object id on Block? Y

seg/obj: 0x14214csc: 0x00.af2351itc: 2flg: Etyp: 1 - DATA

brn: 0bdba: 0x1002640 ver: 0x01 opc: 0

inc: 0exflg: 0

ItlXidUbaFlagLckScn/Fsc

0x010x0006.014.000023010x00c004fc.07fb.28--U-3fsc 0x0000.00af2369

0x020x0000.000.000000000x00000000.0000.00----0fsc 0x0000.00000000

bdba: 0x01002647

data_block_dump,data header at 0x7ff7f727aa64

===============

tsiz: 0x1f98

hsiz: 0x18

pbl: 0x7ff7f727aa64

76543210

flag=--------

ntab=1

nrow=3

frre=-1

fsbo=0x18

fseo=0x1f76

avsp=0x1f64

tosp=0x1f64

0xe:pti[0]nrow=3offs=0

0x12:pri[0]offs=0x1f90

0x14:pri[1]offs=0x1f88

0x16:pri[2]offs=0x1f76

block_row_dump:

tab 0, row 0, @0x1f90

tl: 8 fb: --H-FL-- lb: 0x1cc: 2

col0: [ 2]c1 02

col1: [ 1]61

tab 0, row 1, @0x1f88

tl: 8 fb: --H-FL-- lb: 0x1cc: 2

col0: [ 2]c1 03

col1: [ 1]61

tab 0, row 2, @0x1f76

tl: 10 fb: --H-FL-- lb: 0x1cc: 2

col0: [ 2]c1 04

col1: [ 3]78 78 78

end_of_block_dump

End dump data blocks tsn: 4 file#: 4 minblk 9799 maxblk 9799

select to_number('00af2369','xxxxxxxxxxx') from dual;

TO_NUMBER('00AF2369','XXXXXXXXXXX')

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

11477865

Dump出来的scn和查询表的ora_rowscn是一样。

alter system dump datafile 4 block 9831;

Start dump data blocks tsn: 4 file#:4 minblk 9831 maxblk 9831

Block dump from cache:

Dump of buffer cache at level 4 for tsn=4, rdba=16787047

BH (0x763da4e8) file#: 4 rdba: 0x01002667 (4/9831) class: 1 ba: 0x76040000

set: 6 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 474,28

dbwrid: 0 obj: 82456 objn: 82456 tsn: 4 afn: 4 hint: f

hash: [0x76fef5a8,0x90a62270] lru: [0x763da700,0x763da4a0]

ckptq: [NULL] fileq: [NULL] objq: [0x76fef998,0x8df6aa78]

st: XCURRENT md: NULL tch: 4

flags: block_written_once redo_since_read

LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]

cr pin refcnt: 0 sh pin refcnt: 0

BH (0x76fef4f8) file#: 4 rdba: 0x01002667 (4/9831) class: 1 ba: 0x76e76000

set: 6 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 474,28

dbwrid: 0 obj: 82456 objn: 82456 tsn: 4 afn: 4 hint: f

hash: [0x76fef808,0x763da598] lru: [0x90b1f818,0x763de4c0]

lru-flags: moved_to_tail

ckptq: [NULL] fileq: [NULL] objq: [NULL]

st: CR md: NULL tch: 2

cr: [scn: 0x0.af2d1d],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.af2d1d],[sfl: 0x0],[lc:

0x0.af257f]

flags: block_written_once redo_since_read

cr pin refcnt: 0 sh pin refcnt: 0

BH (0x76fef758) file#: 4 rdba: 0x01002667 (4/9831) class: 1 ba: 0x76e7a000

set: 6 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 474,28

dbwrid: 0 obj: 82456 objn: 82456 tsn: 4 afn: 4 hint: f

hash: [0x78bf3f48,0x76fef5a8] lru: [0x77feff60,0x76ff08e0]

lru-flags: moved_to_tail

ckptq: [NULL] fileq: [NULL] objq: [NULL]

st: CR md: NULL tch: 2

cr: [scn: 0x0.af257c],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.af257c],[sfl: 0x0],[lc:

0x0.af2558]

flags: redo_since_read

cr pin refcnt: 0 sh pin refcnt: 0

Block dump from disk:

buffer tsn: 4 rdba: 0x01002667 (4/9831)

scn: 0x0000.00af2d1fseq: 0x01 flg: 0x06 tail: 0x2d1f0601

frmt: 0x02 chkval: 0xa39f type: 0x06=trans data

Hex dump of block: st=0, typ_found=1

Dump of memory from 0x00007F63E42BEA00 to 0x00007F63E42C0A00

7F63E42BEA00 0000A206 01002667 00AF2D1F 06010000[....g&...-......]

7F63E42BEA10 0000A39F 00000001 00014218 00AF257D[.........B..}%..]

7F63E42BEA20 00000000 00320002 01002660 00120004[......2.`&......]

7F63E42BEA30 00001964 00C0046A 00300A9B 00002001[d...j.....0.. ..]

7F63E42BEA40 00AF2D1F 000C0002 000022F7 00C00391[.-......."......]

7F63E42BEA50 00040AEA 00002001 00AF257F 00000000[..... ...%......]

7F63E42BEA60 00000000 00030120 0018FFFF 1F501F4D[.... .......M.P.]

7F63E42BEA70 00001F50 1F8A0003 1F5E1F4D 00000000[P.......M.^.....]

7F63E42BEA80 00000000 00000000 00000000 00000000[................]

Repeat 498 times

7F63E42C09B0 02012C00 25580000 C10200AF 79790403[.,....X%......yy]

7F63E42C09C0 022C7979 58000002 0200AF25 780304C1[yy,....X%......x]

7F63E42C09D0 002C7878 58000002 0200AF25 610104C1[xx,....X%......a]

7F63E42C09E0 0002002C AF255800 03C10200 002C6101[,....X%......a,.]

7F63E42C09F0 58000002 0200AF25 610102C1 2D1F0601[...X%......a...-]

Block header dump:0x01002667

Object id on Block? Y

seg/obj: 0x14218csc: 0x00.af257ditc: 2flg: Etyp: 1 - DATA

brn: 0bdba: 0x1002660 ver: 0x01 opc: 0

inc: 0exflg: 0

ItlXidUbaFlagLckScn/Fsc

0x010x0004.012.000019640x00c0046a.0a9b.30--U-1fsc0x0000.00af2d1f

0x020x0002.00c.000022f70x00c00391.0aea.04--U-1fsc0x0000.00af257f

bdba: 0x01002667

data_block_dump,data header at 0x7f63e42bea64

===============

tsiz: 0x1f98

hsiz: 0x18

pbl: 0x7f63e42bea64

76543210

flag=--R-----

ntab=1

nrow=3

frre=-1

fsbo=0x18

fseo=0x1f4d

avsp=0x1f50

tosp=0x1f50

0xe:pti[0]nrow=3offs=0

0x12:pri[0]offs=0x1f8a

0x14:pri[1]offs=0x1f4d

0x16:pri[2]offs=0x1f5e

block_row_dump:

tab 0, row 0, @0x1f8a

tl: 14 fb: --H-FL-- lb: 0x0cc: 2

dscn 0x0000.00af2558

col0: [ 2]c1 02

col1: [ 1]61

tab 0, row 1, @0x1f4d

tl: 17 fb: --H-FL-- lb: 0x1cc: 2

dscn 0x0000.00af2558

col0: [ 2]c1 03

col1: [ 4]79 79 79 79

tab 0, row 2, @0x1f5e

tl: 16 fb: --H-FL-- lb: 0x2cc: 2

dscn 0x0000.00af2558

col0: [ 2]c1 04

col1: [ 3]78 78 78

end_of_block_dump

End dump data blocks tsn: 4 file#: 4 minblk 9831 maxblk 9831

Flashback version query不会跨越DDL操作

SQL> select versions_startscn,versions_starttime,versions_endscn,versions_endtime,versions_xid,versions_operation,id,name from t4 versions between scn minvalue and maxvalue where xxx=xxx;

select versions_startscn,versions_starttime,versions_endscn,versions_endtime,versions_xid,versions_operation,id,name from t4 versions between timestamp to_timestamp('2012/06/10 16:00:00','yyyy/mm/dd hh24:mi:ss') and to_timestamp('2012/06/10 18:00:00','yyyy/mm/dd hh24:mi:ss')

(c).flashbackup transaction query

select * fromflashback_transaction_querywhere xid in(select versions_xid from

t5 versions between scn minvalue and maxvalue);

(d).flashback table

Flashback table table_name to scn[to timestamp],如果想对表进行flashback,

必须允许表的row movement

SQL> set time on

19:55:15 SQL> create table t1 (id number,name varchar2(10));

Table created.

19:55:30 SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

11586851

19:55:48 SQL> insert into t1 values(1,'a');

1 row created.

19:55:56 SQL> insert into t1 values(2,'b');

1 row created.

19:56:00 SQL> insert into t1 values(3,'c');

1 row created.

19:56:03 SQL> commit;

Commit complete.

19:56:11 SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

11586863

19:56:14 SQL> insert into t1 values(4,'d');

1 row created.

19:56:33 SQL> insert into t1 values(5,'e');

1 row created.

19:56:36 SQL> commit;

Commit complete.

19:56:39 SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

11586873

19:56:42 SQL> insert into t1 values(6,'f');

1 row created.

19:56:55 SQL> insert into t1 values(7,'g');

1 row created.

19:56:59 SQL> commit;

Commit complete.

19:57:02 SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

11586887

19:59:34 SQL>alter table t1 enable row movement;

Table altered.

19:59:48 SQL> flashback table t1 to scn 11586873

19:59:512;

Flashback complete.

19:59:53 SQL> select * from t1;

ID NAME

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

1 a

2 b

3 c

4 d

5 e

20:01:37 SQL>flashback table t1 to timestamp to_timestamp('2012/06/11 19:56:39','yyyy/mm/dd hh24:mi:ss');

Flashback complete.

20:01:59 SQL> select * from t1;

ID NAME

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

1 a

2 b

3 c

4 d

5 e

	Flashback log	Tablespace bin	Undo	目的
Flashback database	ü			回滚数据库
Flashback drop		ü		恢复用户误删除的表
Flashback version query			ü	恢复用户误操作的数据
Flashback transaction query			ü	同上
Flashback table			ü	同上，但是是在表级别