flashback query

flashback query的介绍：

flashback query可以查询表的历史记录，至于多长时间是由undo_retention决定的。如果
undo的信息没有被overwrite的话，可以查询更长时间。

案例：

下面是一个用户错误地更改了表的
数据，通过flashback query可以得到更改之前的数据。

点击(此处)折叠或打开

1. select * from dbs101.payment as of timestamp sysdate-60/1440 where pid = 100
   
2. union all
   
3. select * from dbs101.payment where pid = 100
   
4. create table dbs101.payment_bak as select select * from dbs101.payment as of
   
5. timestamp sysdate-60/1440 where pid = 100;
   
6. update dbs101.payment set amount = (
   
7. select amount from dbs101.payment_bak b
   
8. where dbs101.payment.id = b.id
   
9. )
   
10. and dbs101.payment.id = 100;
    
11. commit;

在这里可以取到1个小时之前的数据，这是因为undo segment的信息还没有被覆盖。

当事务没有提交时，可以从视图v$transaction中找到usn，slot，sqn，undo的文件和块。

点击(此处)折叠或打开

1. select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;
   
2. SQL> select usn,name from v$rollname where usn=51;
   
3.  
   
4.        USN NAME
   
5. ---------- ------------------------------
   
6.         51 _SYSSMU51$

 
dump出undo segment头。
alter system dump undo header '_SYSSMU51$';

点击(此处)折叠或打开

1. TRN CTL:: seq: 0x20c0 chd: 0x002e ctl: 0x0014 inc: 0x00000000 nfb: 0x0000
   
2.             mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
   
3.             uba: 0x46800319.20c0.12 scn: 0x0418.149521f0
   
4. Version: 0x01
   
5.   FREE BLOCK POOL::
   
6.     uba: 0x00000000.20c0.11 ext: 0x2 spc: 0x1848
   
7.     uba: 0x00000000.20c0.02 ext: 0x2 spc: 0x1ef6
   
8.     uba: 0x00000000.20be.26 ext: 0x0 spc: 0xa2a
   
9.     uba: 0x00000000.1f16.02 ext: 0x4 spc: 0x1f1e
   
10.     uba: 0x00000000.1f16.0b ext: 0x4 spc: 0x1b0c
    
11.   TRN TBL::
    
12.  
    
13.   index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
    
14.   ------------------------------------------------------------------------------------------------
    
15.    0x00 9 0x00 0x9ea1 0x0003 0x0418.14952377 0x46800318 0x0000.000.00000000 0x00000001 0x00000000 1312888300
    
16. ......
    
17.    0x0c 10 0x80 0x9e77 0x0002 0x0418.14952662 0x46800319 0x0000.000.00000000 0x00000001 0x00000000 0
    
18. ......

可以看到ITL表中0x0c记录是当前的记录。

再dump出undo文件块的内容：

点击(此处)折叠或打开

1. alter system dump datafile 282 block 793;
   
2. uba: 0x46800319.20c0.10 ctl max scn: 0x0418.149521cd prv tx scn: 0x0418.149521f0
   
3. txn start scn: scn: 0x0418.149523e8 logon user: 569
   
4.  prev brb: 1182794519 prev bcl: 0
   
5. KDO undo record:
   
6. KTB Redo
   
7. op: 0x03 ver: 0x01
   
8. op: Z
   
9. Array Update of 1 rows:
   
10. tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 0
    
11. ncol: 1 nnew: 1 size: 0
    
12. KDO Op code: 21 row dependencies Disabled
    
13.   xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x012afa9c hdba: 0x012afa9b
    
14. itli: 2 ispac: 0 maxfr: 4858
    
15. vect = 0
    
16. col 0: [ 2] c1 03

这里记录了更新前的数据。

再dump出数据块的内容：

点击(此处)折叠或打开

1. select file_id,block_id from dba_extents where segment_name='PAYMENT';
   
2. SQL> select file_id,block_id,blocks from dba_extents where segment_name='PAYMENT';
   
3.  
   
4.    FILE_ID BLOCK_ID BLOCKS
   
5. ---------- ---------- ----------
   
6.          4 2816665 8
   
7.  
   
8. alter system dump datafile 4 block min 2816665 block max 2816673;
   
9. ---------data file block dump
   
10. Block header dump: 0x012afa9c
    
11.  Object id on Block? Y
    
12.  seg/obj: 0x2241b8 csc: 0x418.149526b4 itc: 2 flg: E typ: 1 - DATA
    
13.      brn: 0 bdba: 0x12afa99 ver: 0x01 opc: 0
    
14.      inc: 0 exflg: 0
    
15.  
    
16.  Itl Xid Uba Flag Lck Scn/Fsc
    
17. 0x01 0x0035.01a.0000a9e6 0x468000ed.2fb1.26 C--- 0 scn 0x0418.14952662
    
18. 0x02 0x0033.00c.00009e77 0x46800319.20c0.12

这里记录了Itl列表。Xid，Uba等等信息。
 
执行commit和checkpoint操作，发现Itl并没有改变。

点击(此处)折叠或打开

1. Block header dump: 0x012afa9c
   
2.  Object id on Block? Y
   
3.  seg/obj: 0x2241b8 csc: 0x418.149526b4 itc: 2 flg: E typ: 1 - DATA
   
4.      brn: 0 bdba: 0x12afa99 ver: 0x01 opc: 0
   
5.      inc: 0 exflg: 0
   
6.  
   
7.  Itl Xid Uba Flag Lck Scn/Fsc
   
8. 0x01 0x0035.01a.0000a9e6 0x468000ed.2fb1.26 C--- 0 scn 0x0418.14952662
   
9. 0x02 0x0033.00c.00009e77 0x46800319.20c0.12 ---- 1 fsc 0x0000.00000000
   
10.  
    
11. alter system checkpoint;
    
12. Block header dump: 0x012afa9c
    
13.  Object id on Block? Y
    
14.  seg/obj: 0x2241b8 csc: 0x418.149526b4 itc: 2 flg: E typ: 1 - DATA
    
15.      brn: 0 bdba: 0x12afa99 ver: 0x01 opc: 0
    
16.      inc: 0 exflg: 0
    
17.  
    
18.  Itl Xid Uba Flag Lck Scn/Fsc
    
19. 0x01 0x0035.01a.0000a9e6 0x468000ed.2fb1.26 C--- 0 scn 0x0418.14952662
    
20. 0x02 0x0033.00c.00009e77 0x46800319.20c0.12

 
当过了12个小时候，在看看dump的内容。

点击(此处)折叠或打开

1. Block header dump: 0x012afa9c
   
2.  Object id on Block? Y
   
3.  seg/obj: 0x2241b8 csc: 0x418.1495598e itc: 2 flg: E typ: 1 - DATA
   
4.      brn: 0 bdba: 0x12afa99 ver: 0x01 opc: 0
   
5.      inc: 0 exflg: 0
   
6.  
   
7.  Itl Xid Uba Flag Lck Scn/Fsc
   
8. 0x01 0x0035.01a.0000a9e6 0x468000ed.2fb1.26 C--- 0 scn 0x0418.14952662
   
9. 0x02 0x0033.00c.00009e77 0x46800319.20c0.12 C-U- 0 scn 0x0418.14953b13

 
Itl的scn和lck发生了改变。

再执行flashquery，发现rollback segment已经过期了。

点击(此处)折叠或打开

1. SQL> select * from dbs101.payment as of timestamp sysdate-900/1440 ;
   
2.  
   
3. select * from dbs101.payment as of timestamp sysdate-900/1440

 
ORA-01555: snapshot too old: rollback segment number 51 with name "_SYSSMU51$" too small

再看看undo segment header的scn。

点击(此处)折叠或打开

1. undo segment header: ITL
   
2.   index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
   
3.   ------------------------------------------------------------------------------------------------
   
4.    0x0c 9 0x00 0x9eb0 0x002a 0x0418.14974f68 0x46800517 0x0000.000.00000000 0x00000001 0x00000000 1312942722

0x0418.14974f68 比data block中的scn 0x0418.14953b13要大，这说明undo segment已经被overwrite了。

注释：这里做这么多的dump是为了更好地理解flashback query怎么从undo中获取历史的信息。

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

转载于:http://blog.itpub.net/25105315/viewspace-704572/