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
在查询结果中,注意到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> 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.
再多执行一些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
通过以上输出,根据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
通过执行相应的undo语句可以撤消该事务,通过这些新特性,oracle提供了一种”回滚”提交事务的
方法,极大地方便了用户应对不同情况的数据库恢复.