oracle flashback 深入研究,oracle之flashback深入研究

女爷i 2023-01-19 04:48 252阅读 0赞

oracle之flashback深入研究

今天是2013-08-24，开始进行oracle flashback 内部原理研究，记录一下笔记。

SQL> startup

ORACLE instance started.

Total System Global Area  405020672 bytes

Fixed Size                  2213816 bytes

Variable Size             260048968 bytes

Database Buffers          138412032 bytes

Redo Buffers                4345856 bytes

Database mounted.

Database opened.

SQL>

SQL> select \* from t1;

ID        SAL JOB

\---------- ---------- ----------

1          9 z

1          2 a

2          3 b

3          4 c

1          3 b

2          1 a

7 rows selected.

SQL> select xidusn,ubafil,ubablk from v$transaction;

no rows selected

SQL> delete from t1 where id=3;

1 row deleted.

SQL> select xidusn,ubafil,ubablk from v$transaction;

XIDUSN     UBAFIL     UBABLK

\---------- ---------- ----------

17          8       2583

SQL> alter system dump datafile 8 block 2583;

System altered.

SQL> set vlaue for a60

SP2-0158: unknown SET option "vlaue"

SQL> col value for a60

SQL> set linesize 200

SQL> select \* from v$diag\_info where name='Default Trace File';

INST\_ID NAME                                                             VALUE

\---------- ---------------------------------------------------------------- ------------------------------------------------------------

1 Default Trace File                                               /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS\_ora\_1801.trc

SQL> commit;

Commit complete.

UNDO BLK:

xid: 0x0011.019.000000b4  seq: 0x23c cnt: 0x46  irb: 0x46  icl: 0x0   flg: 0x0000

Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset

\---------------------------------------------------------------------------

0x01 0x1f90     0x02 0x1f4c     0x03 0x1ef4     0x04 0x1eb0     0x05 0x1e58

0x06 0x1e14     0x07 0x1dbc     0x08 0x1d78     0x09 0x1d20     0x0a 0x1cdc

0x0b 0x1c84     0x0c 0x1c40     0x0d 0x1be8     0x0e 0x1ba4     0x0f 0x1b4c

0x10 0x1b08     0x11 0x1ab0     0x12 0x19d0     0x13 0x18f0     0x14 0x1868

0x15 0x1800     0x16 0x1794     0x17 0x1620     0x18 0x1508     0x19 0x1480

0x1a 0x1414     0x1b 0x13a8     0x1c 0x1224     0x1d 0x119c     0x1e 0x1134

0x1f 0x10c8     0x20 0x0f4c     0x21 0x0edc     0x22 0x0e54     0x23 0x0de8

0x24 0x0d7c     0x25 0x0d38     0x26 0x0ca0     0x27 0x0c14     0x28 0x0bd0

0x29 0x0b74     0x2a 0x0b18     0x2b 0x0ad4     0x2c 0x0a78     0x2d 0x0a1c

0x2e 0x09d8     0x2f 0x097c     0x30 0x0920     0x31 0x08dc     0x32 0x0880

0x33 0x0824     0x34 0x07dc     0x35 0x0778     0x36 0x071c     0x37 0x06d8

0x38 0x067c     0x39 0x0620     0x3a 0x05dc     0x3b 0x0580     0x3c 0x0524

0x3d 0x04e0     0x3e 0x0484     0x3f 0x0428     0x40 0x0340     0x41 0x02b4

0x42 0x0250     0x43 0x01e8     0x44 0x016c     0x45 0x0114     0x46 0x00d0

\*-----------------------------

\* Rec \#0x22  slt: 0x19  objn: 468(0x000001d4)  objd: 468  tblspc: 1(0x00000001)

\*       Layer:  11 (Row)   opc: 1   rci 0x00

Undo type:  Regular undo    Begin trans    Last buffer split:  No

Temp Object:  No

Tablespace Undo:  No

rdba: 0x00000000Ext idx: 0

flg2: 0

\*-----------------------------

uba: 0x02000a17.023c.21 ctl max scn: 0x0000.00a82f35 prv tx scn: 0x0000.00a82f6f

txn start scn: scn: 0x0000.00a83315 logon user: 0

prev brb: 33580056 prev bcl: 0

KDO undo record:

KTB Redo

op: 0x04  ver: 0x01

compat bit: 4 (post-11) padding: 1

op: L  itl: xid:  0x0010.000.000000cb uba: 0x02006433.024f.39

flg: C---    lkc:  0     scn: 0x0000.00a832c7

KDO Op code: DRP row dependencies Disabled

xtype: XA flags: 0x00000000  bdba: 0x0080f7a9  hdba: 0x0080039a

itli: 1  ispac: 0  maxfr: 4858

tabn: 0 slot: 27(0x1b)

SQL> select to\_number('a82f35','xxxxxxxxxxxxxxxx') sc from dual;

\----------

11022133

SQL> select current\_scn from v$database;

CURRENT\_SCN

\-----------

11054748

SQL>

注意：闪回查询和undo\_retention有直接关系；

eg：

SQL> update t1 set job='ab' where id=1;

3 rows updated.

SQL>  select versions\_starttime,versions\_endtime,versions\_xid,versions\_operation,id,sal from t1 versions between timestamp minvalue and maxvalue

2  ;

VERSIONS\_STARTTIME                                                          VERSIONS\_ENDTIME     VERSIONS\_XID     V         ID        SAL

\--------------------------------------------------------------------------- -------------------- ---------------- - ---------- ----------

1          9

1          2

2          3

1          3

2          1

6 rows selected.

SQL> show parameter undo

NAME                                 TYPE        VALUE

\------------------------------------ ----------- ------------------------------

\_optimizer\_undo\_cost\_change          string      11.2.0.1

\_undo\_autotune                       boolean     FALSE

undo\_management                      string      AUTO

undo\_retention                       integer     60

undo\_tablespace                      string      UNDOTBS3

SQL> alter system set undo\_retention=1800;

System altered.

SQL> select versions\_starttime,versions\_endtime,versions\_xid,versions\_operation,id,sal from t1 versions between timestamp minvalue and maxvalue;

VERSIONS\_STARTTIME                                                          VERSIONS\_ENDTIME     VERSIONS\_XID     V         ID        SAL

\--------------------------------------------------------------------------- -------------------- ---------------- - ---------- ----------

1          9

24-AUG-13 09.49.38 PM                                                                            11000300BF000000 D          3          4

1          2

2          3

24-AUG-13 09.49.38 P                             3          4

1          3

2          1

8 rows selected.

1)闪回查询：

SQL> select \* from t1 ;

ID        SAL JOB

\---------- ---------- ----------

1          9 z

1          2 a

2          3 b

1          3 b

2          1 a

6 rows selected.

SQL> select \* from t1 as of scn 11022133;

ID        SAL JOB

\---------- ---------- ----------

1          2 a

2          3 b

3          4 c

1          3 b

2          1 a

6 rows selected.

SQL>

2)闪回版本查询：

SQL> r

1\* select \* from t1

ID        SAL JOB

\---------- ---------- ----------

1          9 ab

1          2 ab

2          3 b

1          3 ab

2          1 a

6 rows selected.

SQL> delete from t1 where id=1;

3 rows deleted.

SQL> commit;

Commit complete.

SQL> select versions\_startscn,versions\_endscn,versions\_xid,versions\_operation,id,sal,job from t1 versions between scn minvalue and maxvalue;

VERSIONS\_STARTSCN VERSIONS\_ENDSCN VERSIONS\_XID     V         ID        SAL JOB

\----------------- --------------- ---------------- - ---------- ---------- ----------

11144497                 0D001600D8000000 D          1          9 z

11144497                             1          9 z

11144497                 0D001600D8000000 D          1          3 b

11144497                 0D001600D8000000 D          1          2 a

11054404                 11000300BF000000 D          3          4 c

11144497                             1          2 a

2          3 b

11054404                             3          4 c

11144497                             1          3 b

2          1 a

11 rows selected.

SQL>

然后我们更具versions\_xid找到相应的事务

3)事务查询；

借助flashback\_transaction\_query这个视图；

首先看一下 这个视图结构；

SSQL> desc flashback\_transaction\_query;

Name                                                                                                              Null?    Type

\----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------

XID                                                                                                                        RAW(8)

START\_SCN                                                                                                                  NUMBER

START\_TIMESTAMP                                                                                                            DATE

COMMIT\_SCN                                                                                                                 NUMBER

COMMIT\_TIMESTAMP                                                                                                           DATE

LOGON\_USER                                                                                                                 VARCHAR2(30)

UNDO\_CHANGE\#                                                                                                               NUMBER

OPERATION                                                                                                                  VARCHAR2(32)

TABLE\_NAME                                                                                                                 VARCHAR2(256)

TABLE\_OWNER                                                                                                                VARCHAR2(32)

ROW\_ID                                                                                                                     VARCHAR2(19)

UNDO\_SQL                                                                                                                   VARCHAR2(4000)

SQL>

然后我们进行一次查询：

eg：

SQL> conn rhys/root

Connected.

SQL> select \* from t1;

ID        SAL JOB

\---------- ---------- ----------

2          3 b

3          1 a

1          1 ab

SQL> delete from t1 where id=1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select versions\_startscn,versions\_endscn,versions\_xid,versions\_operation,id,sal,job from t1 versions between scn minvalue and maxvalue;

VERSIONS\_STARTSCN VERSIONS\_ENDSCN VERSIONS\_XID     V         ID        SAL JOB

\----------------- --------------- ---------------- - ---------- ---------- ----------

11166640                 13000A00D9000000 U          3          1 a

2          3 b

11166640                             2          1 a

11187698                 13001300DB000000 D          1          1 ab

11166640        11187698 13000A00D9000000 I          1          1 ab

6 rows selected.

SQL> select xid,logon\_user,start\_scn,operation,table\_name,row\_id,undo\_sql from flashback\_transaction\_query where xid='13000A00D9000000';

select xid,logon\_user,start\_scn,operation,table\_name,row\_id,undo\_sql from flashback\_transaction\_query where xid='13000A00D9000000'

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> conn sys/root as sysdba

Connected.

SQL> select current\_scn from v$database;

CURRENT\_SCN

\-----------

11187761

SQL> select xid,logon\_user,start\_scn,operation,table\_name,row\_id,undo\_sql from flashback\_transaction\_query where xid='13001300DB000000';

XID              LOGON\_USER                      START\_SCN OPERATION

\---------------- ------------------------------ ---------- --------------------------------

TABLE\_NAME

\--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ROW\_ID

\-------------------

UNDO\_SQL

\--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

13001300DB000000 RHYS                                    0 UNKNOWN

XID              LOGON\_USER                      START\_SCN OPERATION

\---------------- ------------------------------ ---------- --------------------------------

TABLE\_NAME

\--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ROW\_ID

\-------------------

UNDO\_SQL

\--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

13001300DB000000 RHYS                                    0 BEGIN

SQL>

但是没有任何undo\_sql，查看网络发现如下：

\#这里执行这个语句的意思是追加日志

supplemental logging 的定义 ：

redo log一般用于实例恢复及介质恢复。在redo log中这些数据被自动记录。不过一些

基于redo的application可能在redo log中记录额外的一些列。 这种记录额外列的过程

被称为supplemental logging

缺省情况下，数据库没有打开对supplemental logging的支持。

然后重新做实验如下：

SQL> alter database add supplemental log data;

Database altered.

SQL> select \* from t1;

ID        SAL JOB

\---------- ---------- ----------

2          3 b

2          1 a

SQL> update t1 set id=3 where job='D';

1 row updated.

SQL> commit;

Commit complete.

SQL> select versions\_startscn,versions\_endscn,versions\_xid,versions\_operation,id,sal,job from t1 versions between scn minvalue and maxvalue;

VERSIONS\_STARTSCN VERSIONS\_ENDSCN VERSIONS\_XID     V         ID        SAL JOB

\----------------- --------------- ---------------- - ---------- ---------- ----------

11144497                 0D001600D8000000 D          1          9 z

11144497                             1          9 z

11211146                 0B001600DF000000 U          3            D

11144497                 0D001600D8000000 D          1          3 b

11144497                 0D001600D8000000 D          1          2 a

11144497                             1          2 a

2          3 b

11144497                             1          3 b

2          1 a

11211146                                          D

10 rows selected.

SQL> select xid,logon\_user,start\_scn,operation,table\_name,row\_id,undo\_sql from flashback\_transaction\_query where xid='0B001600DF000000';

^CERROR:

ORA-01013: user requested cancel of current operation

no rows selected

SQL> set timing on

SQL> r

1\* select xid,logon\_user,start\_scn,operation,table\_name,row\_id,undo\_sql from flashback\_transaction\_query where xid='0B001600DF000000'

XID              LOGON\_USER                      START\_SCN OPERATION

\---------------- ------------------------------ ---------- --------------------------------

TABLE\_NAME

\--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ROW\_ID

\-------------------

UNDO\_SQL

\--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

0B001600DF000000 SYS                              11211144 UPDATE

AAASCgAAFAAAACHAAF

update "SYS"."T1" set "ID" = NULL where ROWID = 'AAASCgAAFAAAACHAAF';

XID              LOGON\_USER                      START\_SCN OPERATION

\---------------- ------------------------------ ---------- --------------------------------

TABLE\_NAME

\--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ROW\_ID

\-------------------

UNDO\_SQL

\--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

0B001600DF000000 SYS                              11211144 BEGIN

Elapsed: 00:03:40.77

SQL>

可以看到sql了。

那么之前的事务是不是也能自动再重新记录sql呢？答案是否定的如下：

SQL> select xid,logon\_user,start\_scn,operation,table\_name,row\_id,undo\_sql from flashback\_transaction\_query where xid=hextoraw('13001300DB000000');

XID              LOGON\_USER                      START\_SCN OPERATION                        TABLE\_NAME                                                                       ROW\_ID              UNDO\_SQL

\---------------- ------------------------------ ---------- -------------------------------- -------------------------------------------------------------------------------- ------------------- --------------------------------------------------------------------------------

13001300DB000000 RHYS                                    0 UNKNOWN                          T1

13001300DB000000 RHYS                                    0 BEGIN

Executed in 0.063 seconds

SQL>

另外一个问题就来了，这个查询过程也忒慢了吧。因为xid为raw类型。这时候为了提高查询速度使用hextoraw进行转换，使用内部索引来提高查询速度。

eg：

SQL> set autotrace on

SQL> select xid,logon\_user,start\_scn,operation,table\_name,row\_id,undo\_sql from flashback\_transaction\_query where xid=hextoraw('0B001600DF000000');

XID              LOGON\_USER                      START\_SCN OPERATION

\---------------- ------------------------------ ---------- --------------------------------

TABLE\_NAME

\--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ROW\_ID

\-------------------

UNDO\_SQL

\--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

0B001600DF000000 SYS                              11211144 UPDATE

AAASCgAAFAAAACHAAF

update "SYS"."T1" set "ID" = NULL where ROWID = 'AAASCgAAFAAAACHAAF';

XID              LOGON\_USER                      START\_SCN OPERATION

\---------------- ------------------------------ ---------- --------------------------------

TABLE\_NAME

\--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ROW\_ID

\-------------------

UNDO\_SQL

\--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

0B001600DF000000 SYS                              11211144 BEGIN

Elapsed: 00:00:00.03

Execution Plan

\----------------------------------------------------------

Plan hash value: 1747778896

\---------------------------------------------------------------------------------------------

\---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT        |                   |     1 |  2197 |     0   (0)| 00:00:01 |

|\*  1 |  FIXED TABLE FIXED INDEX| X$KTUQQRY (ind:1) |     1 |  2197 |     0   (0)| 00:00:01 |

\---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

\---------------------------------------------------

1 - filter("XID"=HEXTORAW('0B001600DF000000') )

Statistics

\----------------------------------------------------------

36  recursive calls

1  db block gets

51  consistent gets

0  physical reads

0  redo size

1139  bytes sent via SQL\*Net to client

523  bytes received via SQL\*Net from client

2  SQL\*Net roundtrips to/from client

3  sorts (memory)

0  sorts (disk)

2  rows processed

SQL>

对比一下：

SQL> select xid,logon\_user,start\_scn,operation,table\_name,row\_id,undo\_sql from flashback\_transaction\_query where xid='0B001600DF000000';

XID              LOGON\_USER                      START\_SCN OPERATION

\---------------- ------------------------------ ---------- --------------------------------

TABLE\_NAME

\--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ROW\_ID

\-------------------

UNDO\_SQL

\--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

0B001600DF000000 SYS                              11211144 UPDATE

AAASCgAAFAAAACHAAF

update "SYS"."T1" set "ID" = NULL where ROWID = 'AAASCgAAFAAAACHAAF';

XID              LOGON\_USER                      START\_SCN OPERATION

\---------------- ------------------------------ ---------- --------------------------------

TABLE\_NAME

\--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ROW\_ID

\-------------------

UNDO\_SQL

\--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

0B001600DF000000 SYS                              11211144 BEGIN

Elapsed: 00:03:51.14

Execution Plan

\----------------------------------------------------------

Plan hash value: 1115820779

\------------------------------------------------------------------------------

\------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |           |     1 |  2197 |     0   (0)| 00:00:01 |

|\*  1 |  FIXED TABLE FULL| X$KTUQQRY |     1 |  2197 |     0   (0)| 00:00:01 |

\------------------------------------------------------------------------------

Predicate Information (identified by operation id):

\---------------------------------------------------

1 - filter(RAWTOHEX("XID")='0B001600DF000000')

Statistics

\----------------------------------------------------------

2936981  recursive calls

10  db block gets

3772792  consistent gets

14256  physical reads

0  redo size

1139  bytes sent via SQL\*Net to client

523  bytes received via SQL\*Net from client

2  SQL\*Net roundtrips to/from client

731820  sorts (memory)

0  sorts (disk)

2  rows processed

SQL>

这就是闪回查询，闪回版本查询，闪回事务查询。

http://www.dengb.com/oracle/480378.htmlwww.dengb.comtruehttp://www.dengb.com/oracle/480378.htmlTechArticleoracle之flashback深入研究 今天是2013-08-24，开始进行oracle flashback 内部原理研究，记录一下笔记。 SQL startup ORACLE instance started. Total System Globa...