oracle的回閃查詢
oracle回閃查詢的新特性
從oracle9i開始,oracle開始提供回閃查詢特性(flashback query),允許將回滾段中的資料進行回閃,透過下面的例子來看一下這個從oracle9i開始提供的新特性.
SQL> update emp set sal=4000 where empno=7788;
1 row updated.
SQL> update emp set sal=4000 where empno=7782;
1 row updated.
SQL> update emp set sal=4000 where empno=7698;
1 row updated.
先不提交這個事務,在另外視窗新開session,使用sys使用者查詢相關資訊,進行進一步的分析
獲得事務資訊
從事務表中可以獲得關於這個事務的資訊,該事務位於9號回滾段(XIDUSN),在9號回滾段
上,該事務位於第29號事務槽(XIDSLOT):
SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;
XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC
---------- ---------- ---------- ---------- ---------- ----------
9 29 385 1350 2 22
從v$rollstat檢視中也可獲得事務資訊,xacts欄位代表的是活動事務的數量,同樣看到該事務
位於9號回滾段
SQL> select usn,writes,rssize,xacts,hwmsize,shrinks,wraps from v$rollstat;
USN WRITES RSSIZE XACTS HWMSIZE SHRINKS WRAPS
---------- ---------- ---------- ---------- ---------- ---------- ----------
0 7620 385024 0 385024 0 0
1 21390 29351936 0 29351936 0 0
2 22108 3268608 0 3268608 0 0
3 29954 450560 0 450560 0 0
4 23700 843776 0 843776 0 0
5 23334 450560 0 450560 0 0
6 21082 450560 0 450560 0 0
7 23146 2285568 0 2285568 0 0
8 28742 843776 0 843776 0 1
9 22648 2088960 1 2088960 0 0
10 24326 2220032 0 2220032 0 0
11 rows selected.
這是執行alter system dump datafile 2 block 1350
轉儲的回滾表空間中的資料塊的資訊的一部分
*-----------------------------
* Rec #0x1d slt: 0x24 objn: 517(0x00000205) objd: 517 tblspc: 0(0x00000000)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x00800546.0129.1b ctl max scn: 0x0000.000e4e9c prv tx scn: 0x0000.000e4ea6
txn start scn: scn: 0x0000.000e7526 logon user: 0
prev brb: 8389956 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0006.016.0000015d uba: 0x00800419.00fe.11
flg: C--- lkc: 0 scn: 0x0000.000e7524
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x0040100f hdba: 0x00401001
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 116(0x74) flag: 0x2c lock: 0 ckix: 191
ncol: 9 nnew: 7 size: 0
Vector content:
col 2: [ 2] c1 0a
col 3: [ 2] c1 0a
col 4: [ 1] 80
col 5: [ 1] 80
col 6: [ 1] 80
col 7: [ 1] 80
col 8: [ 7] 78 71 01 07 0b 07 34
先注意到這裡存在一個資訊ctl max scn: 0x0000.000e4e9c,這個轉換為scn值就是:
SQL> select (to_number('000','xxxx')*power(2,32)+to_number('e4e9c','xxxxxxxx')) scn
from dual;
SCN
----------
937628
查詢一下當前資料的scn:
SQL> select dbms_flashback.get_system_change_number scn from dual;
SCN
----------
949630
SQL>
透過特定的語法,可以將scn 937628的歷史狀態資料查詢出來:
SQL> select * from scott.emp as of scn 937628 where empno in(7788,7782,7698);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 10.00 20
SQL>
在查詢結果中,注意到3名員工的薪水恢復到了之前的狀態.而在當前的查詢中,這個資料是變化
後的4000:
SQL> select * from scott.emp where empno in(7788,7782,7698);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7698 BLAKE MANAGER 7839 1981-5-1 4000.00 30
7782 CLARK MANAGER 7839 1981-6-9 4000.00 10
7788 SCOTT ANALYST 7566 1987-4-19 4000.00 20
由於這個查詢需要從undo中獲限前映象資訊,如果undo中的資訊被覆蓋,則以上查詢將會失敗.
測試一下,當新建undo表空間,切換undo表空間,再將原表空間offline之後:
SQL> create undo tablespace undotbs2 datafile
2 '/u01/app/oracle/product/10.2.0/oradata/jingyong/undotbs02.dbf' size 10M;
Tablespace created
SQL> alter system set undo_tablespace=undotbs2;
System altered.
SQL> alter tablespace undotbs1 offline;
Tablespace altered.
SQL> alter session set events='immediate trace name flush_cache';
Session altered.
再來查詢,此時出現錯誤,記錄該檔案已經不可讀取:
SQL> select * from scott.emp as of scn 937628 where empno in(7788,7782,7698);
select * from scott.emp as of scn 937628 where empno in(7788,7782,7698)
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u01/app/oracle/product/10.2.0/oradata/jingyong/undotbs01.dbf'
將undotbs1重新啟用:
SQL> alter tablespace undotbs1 online;
Tablespace altered.
SQL> alter system set undo_tablespace=undotbs1;
System altered.
此時前映象資訊再次可以查詢,
SQL> select * from scott.emp as of scn 937628 where empno in(7788,7782,7698);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 10.00 20
如果在其他session中執行大量事務,使用前映象資訊被覆蓋:
在執行其它大量事務之前觀察回滾段的使用情況:
SQL> select usn,xacts,rssize,hwmsize from v$rollstat where usn=9;
USN XACTS RSSIZE HWMSIZE
---------- ---------- ---------- ----------
9 0 2088960 2088960
SQL> begin
2 for i in 1..2000 loop
3 update scott.emp set sal=4000;
4 rollback;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select usn,xacts,rssize,hwmsize from v$rollstat where usn=9;
USN XACTS RSSIZE HWMSIZE
---------- ---------- ---------- ----------
9 1 4186112 4186112
SQL>
那麼再次查詢就可能會收到如下錯誤:
SQL> select * from scott.emp as of scn 937628 where empno in(7788,7782,7698);
select * from scott.emp as of scn 937628 where empno in(7788,7782,7698)
ERROR at line 1:
ORA-01555: snapshot too old:rollback segment number 9 with name "_SYSSMU9$" too small
ORA-01555錯誤出現說明要查詢的前鏡偈資訊已經丟失了.
使用errorstack進行錯誤跟蹤
errorstack是oracle提供的介面用於診斷oracle的錯誤資訊.
診斷事件可以在session級設定,也可以在系統級設定,通常如果要診斷全域性錯誤,最好在
系統級設定.設定了errorstack事件之後,oracle會將出錯時的資訊記入跟蹤檔案中.
使用者就可以透過跟蹤檔案進行錯誤診斷和排查.
可以透過errorstack事件來跟蹤ora-01555錯誤:
OS Pid: 2928 executed alter session set events '1555 trace name errorstack level 4'
ORA-01555 caused by SQL statement below (SQL ID: bnaqc462nnjtb, Query Duration=0 sec, SCN: 0x0000.00068478):
Mon Jan 7 20:23:35 2013
select * from scott.emp as of scn 427128 where empno in(7788,7782,7698)
Mon Jan 7 20:23:35 2013
Errors in file /u01/app/oracle/admin/jingyong/udump/jingyong_ora_2928.trc:
ORA-01555: snapshot too old: rollback segment number 3 with name "???" too small
這裡注意到,觸發ora-01555錯誤的語句被記錄,出現錯誤的scn也被記錄,這個scn:0x0000.00068478
找到jingyong_ora_2928.trc跟蹤檔案,就可以獲得關於這次錯誤的相關資訊用於診斷.
錯誤資訊如下
ksedmp: internal or fatal error
ORA-01555: snapshot too old: rollback segment number 3 with name "???" too small
Current SQL statement for this session:
select * from scott.emp as of scn 427128 where empno in(7788,7782,7698)
資料塊資訊,這裡的塊頭就包含了ITL資訊,根據這個ITL資訊中的UBA,oracle可以定位回滾段.
查詢前映象資訊,如果不存在,就可能出現ora-01555錯誤
Block header dump: 0x01000024
Object id on Block? Y
seg/obj: 0xc7cd csc: 0x00.6bfcb itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000021 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0003.011.000000f2 0x00805794.00c8.4a --U- 14 fsc 0x0000.0006bfdb
oracle 10g回閃查詢特性的增強
oracle9i提供的閃回特性增強,為恢復帶來了極大的方便,但是oracle9i的回閃查詢只能提供某個
時間點的資料檢視,並不能告訴使用者這樣的資料經過幾個事務,怎樣被修改的(update,insert,delete)
,而這些資訊在回滾段中是存在的,在oracle10g中,oracle進一步加強了回閃查詢的特性,提供以下
兩種回閃查詢:
回閃版本查詢(flashback versions query)
回閃事務查詢(flashback transaction query)
回閃版本查詢允許使用一個新的versions子句查詢兩個時間點或者scn之間的資料版本.
這些版本可以按照事務進行區分,回閃版本查詢只返回提交資料.沒提交的資料不被顯示
先建立一個測試表,執行一些DML操作
SQL> create table test as select username from dba_users;
Table created.
SQL> select * from test;
USERNAME
------------------------------
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
SCOTT
JYTEST1
JYTEST
OUTLN
MDSYS
ORDSYS
USERNAME
------------------------------
EXFSYS
DMSYS
WMSYS
CTXSYS
ANONYMOUS
XDB
ORDPLUGINS
SI_INFORMTN_SCHEMA
OLAPSYS
TSMSYS
BI
USERNAME
------------------------------
PM
MDDATA
IX
SH
DIP
OE
HR
29 rows selected.
SQL> delete from test where username='BI';
1 row deleted.
SQL> delete from test where username='JYTEST1';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from test;
USERNAME
------------------------------
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
SCOTT
JYTEST
OUTLN
MDSYS
ORDSYS
EXFSYS
USERNAME
------------------------------
DMSYS
WMSYS
CTXSYS
ANONYMOUS
XDB
ORDPLUGINS
SI_INFORMTN_SCHEMA
OLAPSYS
TSMSYS
PM
MDDATA
USERNAME
------------------------------
IX
SH
DIP
OE
HR
27 rows selected.
SQL>
再多執行一些DML操作
SQL> update test set username='JYTEST1' where username='JYTEST';
1 row updated.
SQL> delete from test where username='IX';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from test;
USERNAME
------------------------------
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
SCOTT
JYTEST1
OUTLN
MDSYS
ORDSYS
EXFSYS
USERNAME
------------------------------
DMSYS
WMSYS
CTXSYS
ANONYMOUS
XDB
ORDPLUGINS
SI_INFORMTN_SCHEMA
OLAPSYS
TSMSYS
PM
MDDATA
USERNAME
------------------------------
SH
DIP
OE
HR
JY
27 rows selected.
再來看看oracle10g的回閃版本查詢,透過使用versions子句,和對資料表引入了一系列的偽列(
version_startime等),可以獲得對資料表的所有事務操作,注意以下輸出中的versions_operation
代表了不同型別的操作(D-Delete,I-Insert,U-Update),versions_xid是一個重要資料,代表了不
同版本的事務ID:
SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,
2 username from test versions between timestamp minvalue and maxvalue;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION USERNAME
--------------------------- ---------------------------- ---------------- ------------------ ---------------------- --------
07-1月 -13 11.07.58 下午 0300150096010000 I JY
07-1月 -13 11.06.46 下午 06002B0093010000 D IX
07-1月 -13 11.06.46 下午 06002B0093010000 U JYTEST1
07-1月 -13 11.05.22 下午 05001E00A7010000 D JYTEST1
07-1月 -13 11.05.22 下午 05001E00A7010000 D BI
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
SCOTT
07-1月 -13 11.05.22 下午 JYTEST1
07-1月 -13 11.06.46 下午 JYTEST
OUTLN
MDSYS
ORDSYS
EXFSYS
DMSYS
WMSYS
CTXSYS
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION USERNAME
--------------------------- ---------------------------- ---------------- ------------------ ---------------------- --------
ANONYMOUS
XDB
ORDPLUGINS
SI_INFORMTN_SCHEMA
OLAPSYS
TSMSYS
07-1月 -13 11.05.22 下午 BI
PM
MDDATA
07-1月 -13 11.06.46 下午 IX
SH
DIP
OE
HR
34 rows selected
SQL>
透過以上輸出,根據versions_xid可以清晰地區分不同事務在不同時間對資料所作的更改
具備了flashback version query查詢的基礎,就可以進行基於flashback version query的事務級恢復了.
這就是flashback transaction query, flashback transaction query可以從flashback transaction query
檢視中獲得指定事務的歷史資訊以及undo_sql,透過undo_sql,就可以撤消特定的提交事務.flashback
transaction query需要用到flashback_transaction_query檢視,先看一下檢視:
SQL> desc flashback_transaction_query
Name Type Nullable Default Comments
---------------- -------------- -------- ------- -----------------------------------------
XID RAW(8) Y Transaction identifier
START_SCN NUMBER Y Transaction start SCN
START_TIMESTAMP DATE Y Transaction start timestamp
COMMIT_SCN NUMBER Y Transaction commit SCN
COMMIT_TIMESTAMP DATE Y Transaction commit timestamp
LOGON_USER VARCHAR2(30) Y Logon user for transaction
UNDO_CHANGE# NUMBER Y 1-based undo change number
OPERATION VARCHAR2(32) Y forward operation for this undo
TABLE_NAME VARCHAR2(256) Y table name to which this undo applies
TABLE_OWNER VARCHAR2(32) Y owner of table to which this undo applies
ROW_ID VARCHAR2(19) Y rowid to which this undo applies
UNDO_SQL VARCHAR2(4000) Y SQL corresponding to this undo
該檢視的定義為:
select xid, start_scn, start_timestamp,
decode(commit_scn, 0, commit_scn, 281474976710655, NULL, commit_scn)
commit_scn, commit_timestamp,
logon_user, undo_change#, operation, table_name, table_owner,
row_id, undo_sql
from sys.x$ktuqqry;
對於x$ktuqqry的查詢非常耗時
測試一下對於x$ktuqqry表的查詢
SQL> select count(addr) from x$ktuqqry;
COUNT(ADDR)
-----------
74786
如果需要撤消xid=0300150096010000的事務,可以透過如下步驟進行:
SQL> set autotrace on
SQL> select undo_sql from flashback_transaction_query where xid='0300150096010000';
UNDO_SQL
--------------------------------------------------------------------------------
delete from "SYS"."TEST" where ROWID = 'AAAM3VAABAAAO9KAAd';
Execution Plan
----------------------------------------------------------
Plan hash value: 1115820779
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2008 | 0 (0)| 00:00:01 |
|* 1 | FIXED TABLE FULL| X$KTUQQRY | 1 | 2008 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(RAWTOHEX("XID")='0300150096010000')
Statistics
----------------------------------------------------------
90926 recursive calls
0 db block gets
238964 consistent gets
21783 physical reads
0 redo size
506 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4115 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
透過執行相應的undo語句可以撤消該事務,透過這些新特性,oracle提供了一種"回滾"提交事務的
方法,極大地方便了使用者應對不同情況的資料庫恢復.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26015009/viewspace-752343/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle的閃回查詢Oracle
- oracle閃回查詢Oracle
- oracle 閃回查詢Oracle
- Oracle閃回查詢,閃回版本查詢與閃回事務查詢的使用區別總結Oracle
- 閃回查詢之閃回版本查詢
- 閃回查詢之閃回表查詢
- 【閃回特性之閃回查詢】使用閃回查詢(select as of)
- 閃回表、閃回查詢
- Oracle 11G 閃回技術 閃回版本查詢和閃回事務查詢Oracle
- Oracle 11G 閃回技術 使用Oracle閃回查詢Oracle
- 閃回查詢
- 閃回(關於閃回查詢)
- 閃回刪除、閃回查詢
- oracle閃回版本查詢學習Oracle
- Oracle 11g 閃回查詢Oracle
- Oracle 10g的閃回查詢概述Oracle 10g
- 基本閃回查詢和閃回表
- 閃回技術一:閃回查詢
- Oracle 11G 閃回技術 使用閃回版本查詢Oracle
- 閃回版本查詢與閃回事務查詢
- 閃回查詢(轉)
- 閃回查詢(1)
- Oracle 11g 閃回版本查詢Oracle
- DM7閃回與閃回查詢
- 回閃查詢查詢刪除的資料
- oracle flashback特性(1.2)--閃回查詢之As of scnOracle
- Flashback Query閃回查詢
- 閃回查詢(undo sql)SQL
- 閃回版本查詢操作
- Oracle 11G 閃回技術 使用Oracle閃回事務查詢Oracle
- Oracle資料庫的閃回查詢功能簡介Oracle資料庫
- 使用oracle 閃回查詢找回誤更新的資料Oracle
- [閃回特性之閃回版本查詢]Flashback Version Query
- oracle flashback特性(1.1)--閃回查詢之As of timestampOracle
- oracle flashback特性(1.4)--閃回查詢之Transaction queryOracle
- 學習Oracle10g:閃回版本查詢Oracle
- DM8 閃回查詢
- 閃回版本查詢技術: