是否有想过如何在Oracle中实现脏读(dirty read),在Oracle官方文档或者Asktom的时候显然会提到Oracle是不实现脏读的, 总是有undo来提供数据块的前镜像(before image)以维护一致性Consistent, 通过正常途径我们几乎不可能破坏Oracle中查询的一致性来实现脏读。 是否真的无计可施? 非也,非也,Oracle作为一个高度复杂而又可控的RDBMS,仍是有很多空子可以钻的。 我们先来介绍以下的2个隐藏参数: _offline_rollback_segments or _corrupted_rollback_segments 这2个隐藏参数对于熟悉Oracle数据库异常恢复或者解决ORA-600[4XXX]问题有经验的同学来说肯定不陌生,因为这2个参数是针对Undo存在Corruption讹误时忽略问题的有力工具,而大家对这2个参数实际起到的作用有多少认识呢? 我们可能在以下几种场景中用到_offline_rollback_segments 和 _corrupted_rollback_segments 这2个隐藏参数:
- 强制打开数据库(FORCE OPEN DATABASE)
- 控制一致性读和延迟块清除(consistent read & delayed block cleanout)
- 强制删除某个rollback segment回滚段
- 以上2个参数所列出的Undo Segments(撤销段/回滚段)将不会被在线使用online
- 在UNDO$数据字典基表中将体现为OFFLINE的记录
- 在实例instance的生命周期中将不会再给新的事务分配使用
- 参数所列出的Undo Segments列表上的活跃事务active transaction将即不被回滚亦不被标记为dead以便SMON去回滚(了解你所不知道的SMON功能(五):Recover Dead transaction)
- 在实例startup启动并open database的阶段仍将读取_OFFLINE_ROLLBACK_SEGMENTS所列出的Undo segments(撤销段/回滚段),若访问这些undo segments出现了问题则将在alert.log和其他TRACE中体现出来,但不影响实际的startup进程
- 若查询数据块发现活跃的事务,并ITL指向对应的undo segments则:
- 若读取undo segments的transaction table事务表发现事务已提交则做数据块的清除
- 若读取发现事务仍活动未commit,则生成一个CR块拷贝
- 若读取该undo segments存在问题(可能是corrupted讹误,可能是missed丢失)则产生一个错误并写出到alert.log,查询将异常终止
- 若DML更新相关的数据块会导致服务进程为了恢复活跃事务而进入死循环消耗大量CPU,解决方法是通过可以进行的查询工作重建相关表
- 在实例启动startup并open database的阶段_CORRUPTED_ROLLBACK_SEGMENTS所列出的undo segments(撤销段/回滚段)将不会被访问读取
- 所有指向这些被_CORRUPTED_ROLLBACK_SEGMENTS列出的undo segments的事务都被认为已经提交了commit,和这个undo segments已经被drop时类似
- 这将导致严重的逻辑讹误
- 如果数据字典上有活跃事务那么将更糟糕,数据字典逻辑讹误会造成数据库管理问题
- 如果bootstrap自举核心对象有活跃事务,那么将无法忽略错误ORA-00704: bootstrap process failure错误,导致无法强制打开数据库(见拙作Oracle数据恢复:解决ORA-00600:[4000] ORA-00704: bootstrap process failure错误一例)
- 衷心地建议用_CORRUPTED_ROLLBACK_SEGMENTS这个参数打开数据库后导出数据并重建数据库,这个参数使用的后遗症可能很顽固
- Oracle公司内部有叫做TXChecker的工具可以检查问题事务
SQL> alter system set event= '10513 trace name context forever, level 2' scope=spfile; System altered. SQL> alter system set "_in_memory_undo"=false scope=spfile; System altered. 10513 level 2 event可以禁止SMON 回滚rollback 死事务 dead transaction _in_memory_undo 禁用 in memory undo 特性 SQL> startup force; ORACLE instance started. Total System Global Area 3140026368 bytes Fixed Size 2232472 bytes Variable Size 1795166056 bytes Database Buffers 1325400064 bytes Redo Buffers 17227776 bytes Database mounted. Database opened. session A: SQL> conn maclean/maclean Connected. SQL> create table maclean tablespace users as select 1 t1 from dual connect by level exec dbms_stats.gather_table_stats('','MACLEAN'); PL/SQL procedure successfully completed. SQL> set autotrace on; SQL> select sum(t1) from maclean; SUM(T1) ---------- 501 Execution Plan ---------------------------------------------------------- Plan hash value: 1679547536 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | | 2 | TABLE ACCESS FULL| MACLEAN | 501 | 1503 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------ Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 515 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processe 在没有活跃事务的情况下,直接读取current block, 全表扫描一致性读,consistent gets只要3次 SQL> update maclean set t1=0; 501 rows updated. SQL> alter system checkpoint; System altered. 这里session A不commit; 另开一个 session: SQL> conn maclean/maclean Connected. SQL> SQL> set autotrace on; SQL> select sum(t1) from maclean; SUM(T1) ---------- 501 Execution Plan ---------------------------------------------------------- Plan hash value: 1679547536 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | | 2 | TABLE ACCESS FULL| MACLEAN | 501 | 1503 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------ Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 505 consistent gets 0 physical reads 108 redo size 515 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 为了一致性读 上面的查询需要通过undo构造CR块,这导致consistent gets上升到 505 [oracle@vrh8 ~]$ ps -ef|grep LOCAL=YES |grep -v grep oracle 5841 5839 0 09:17 ? 00:00:00 oracleG10R25 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) [oracle@vrh8 ~]$ kill -9 5841 杀掉session A对应的Server Process服务进程,这导致dead transaction 但是不被smon回滚 select ktuxeusn, to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') "Time", ktuxesiz, ktuxesta from x$ktuxe where ktuxecfl = 'DEAD'; KTUXEUSN Time KTUXESIZ KTUXESTA ---------- -------------------- ---------- ---------------- 2 06-AUG-2012 09:20:45 7 ACTIVE 此时有1个active rollback segment SQL> conn maclean/maclean Connected. SQL> set autotrace on; SQL> select sum(t1) from maclean; SUM(T1) ---------- 501 Execution Plan ---------------------------------------------------------- Plan hash value: 1679547536 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | | 2 | TABLE ACCESS FULL| MACLEAN | 501 | 1503 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------ Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 411 consistent gets 0 physical reads 108 redo size 515 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 到上面为止 虽然通过kill进程 和禁止smon 回滚dead transaction , 形成了一个不回滚的死事务 但是仍通过undo实现了一致性读 找出当前active的rollback segment的名字 SQL> select segment_name from dba_rollback_segs where segment_id=2; SEGMENT_NAME ------------------------------ _SYSSMU2$ SQL> alter system set "_corrupted_rollback_segments"='_SYSSMU2$' scope=spfile; System altered. 用 _corrupted_rollback_segments 废掉 上面的2个rollback segment, 这将导致无法提供undo SQL> startup force; ORACLE instance started. Total System Global Area 3140026368 bytes Fixed Size 2232472 bytes Variable Size 1795166056 bytes Database Buffers 1325400064 bytes Redo Buffers 17227776 bytes Database mounted. Database opened. SQL> conn maclean/maclean Connected. SQL> set autotrace on; SQL> select sum(t1) from maclean; SUM(T1) ---------- 94 Execution Plan ---------------------------------------------------------- Plan hash value: 1679547536 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | | 2 | TABLE ACCESS FULL| MACLEAN | 501 | 1503 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------ Statistics ---------------------------------------------------------- 228 recursive calls 0 db block gets 29 consistent gets 5 physical reads 116 redo size 514 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 1 rows processed SQL> / SUM(T1) ---------- 94 Execution Plan ---------------------------------------------------------- Plan hash value: 1679547536 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | | 2 | TABLE ACCESS FULL| MACLEAN | 501 | 1503 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------ Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 514 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed以上可以看到 consistent gets下降到3,服务进程读取数据块发现存在活跃事务,但是ITL指向的UNDO SEGMENTS在_corrupted_rollback_segments的列表中,所以直接认为该事务已经COMMIT提交,以便绕过UNDO。 这里实现了脏读,虽然通过上述方法去实现脏读在产品环境中没有实际收益(有部分数据库软件允许脏读来做统计信息收集),破坏了一致性且需要设置需重启实例的 隐藏参数 , 仅供参考。
转载于:https://blog.51cto.com/maclean/1278435