當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

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

發布時間：2024/7/23 编程问答 32 豆豆

生活随笔收集整理的這篇文章主要介紹了 oracle flashback 深入研究,oracle 之flashback 深入研究。小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

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>

這就是閃回查詢，閃回版本查詢，閃回事務查詢。

總結

以上是生活随笔為你收集整理的oracle flashback 深入研究,oracle 之flashback 深入研究。的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：安装版mysql错误2_【gem安装】m
下一篇： leetcode37 --- solve