oracle event 10513作用

禁用smon进行tx recovery(所谓tx recovery就是open后数据文件包含提交和未提交数据，数据不一致)，不会造成数据库不一致，虽然我们禁用了smon自动恢复，但是当查询的时候还是会进行回滚从undo中读取 回滚数据（等同于用到哪个对象回滚哪个对象，这种方式会带来压力，且若undo损坏就十分麻烦了，那么这将是另一个恢复问题select segment_name,status,tablespace_name from dba_rollback_segs看那个段损坏 使用隐藏参数_offline_rollback_segments 标记，然后drop rollback segment ‘xxx’ ，此时才会造成真正的数据不一致）

实验证明：

SQL> create table t1 (a int ,b int)
  2  ;

Table created.

SQL> declare
  2  begin
  3  for i in 1..10000 loop
  4  insert into t1 values(i,i+1);
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.
此时 数据未提交，且dirty buffer还未写入disk（可以从x$bh跟踪判断）

SQL> shutdown abort
ORACLE instance shut down.
SQL>startup

SQL>  select usn,UNDOBLOCKSDONE,UNDOBLOCKSDONE from v$fast_start_transactions;

no rows selected  ~~~可以看到 没有tx recovery
SQL> conn xh/a831115
Connected.
SQL> alter session set events'10046 trace name context forever ,level 12';

Session altered.

SQL> select count(*) from t1;

  COUNT(*)
----------
         0
[oracle@ora10g udump]$ tail -f xh_ora_3950.trc
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 3950, image: oracle@ora10g (TNS V1-V3)

*** 2010-01-31 07:58:21.949
*** ACTION NAME:() 2010-01-31 07:58:21.948
*** MODULE NAME:(SQL*Plus) 2010-01-31 07:58:21.948
*** SERVICE NAME:(SYS$USERS) 2010-01-31 07:58:21.948
*** SESSION ID:(159.5) 2010-01-31 07:58:21.948
WAIT #2: nam='SQL*Net message to client' ela= 18 driver id=1650815232 #bytes=1 p3=0 obj#=10416 tim=1235249904246510
中间省略

WAIT #2: nam='db file scattered read' ela= 1279 file#=4 block#=1238772 blocks=5 obj#=52017 tim=1235249942505615
WAIT #2: nam='db file sequential read' ela= 31 file#=2 block#=2357 blocks=1 obj#=52017 tim=1235249942548227
WAIT #2: nam='db file sequential read' ela= 222 file#=2 block#=2356 blocks=1 obj#=52017 tim=1235249942548461
WAIT #2: nam='db file sequential read' ela= 25 file#=2 block#=2355 blocks=1 obj#=52017 tim=1235249942548701
WAIT #2: nam='db file sequential read' ela= 47 file#=2 block#=2354 blocks=1 obj#=52017 tim=1235249942549277
WAIT #2: nam='db file sequential read' ela= 29 file#=2 block#=2363 blocks=1 obj#=52017 tim=1235249942549671
WAIT #2: nam='db file sequential read' ela= 7 file#=2 block#=2362 blocks=1 obj#=52017 tim=1235249942550250
WAIT #2: nam='db file sequential read' ela= 5 file#=2 block#=2361 blocks=1 obj#=52017 tim=1235249942550427
WAIT #2: nam='db file sequential read' ela= 3 file#=2 block#=2360 blocks=1 obj#=52017 tim=1235249942550434
WAIT #2: nam='db file sequential read' ela= 0 file#=2 block#=2359 blocks=1 obj#=52017 tim=1235249942550437
WAIT #2: nam='db file sequential read' ela= 32 file#=2 block#=2367 blocks=1 obj#=52017 tim=1235249942551459
WAIT #2: nam='db file sequential read' ela= 26 file#=2 block#=2366 blocks=1 obj#=52017 tim=1235249942551644
WAIT #2: nam='db file sequential read' ela= 50 file#=2 block#=2365 blocks=1 obj#=52017 tim=1235249942552207
WAIT #2: nam='db file sequential read' ela= 1 file#=2 block#=2364 blocks=1 obj#=52017 tim=1235249942552388
FETCH #2:c=50992,e=49379,p=107,cr=10041,cu=0,mis=0,r=1,dep=1,og=1,tim=1235249942553110
STAT #2 id=1 cnt=1 pid=0 pos=1 bj=0 p='SORT AGGREGATE (cr=10041 pr=107 pw=0 time=49404 us)'
STAT #2 id=2 cnt=0 pid=1 pos=1 bj=52017 p='TABLE ACCESS FULL T1 (cr=10041 pr=107 pw=0 time=49099 us)'
=====================
PARSING IN CURSOR #3 len=23 dep=0 uid=58 ct=3 lid=58 tim=1235249942553325 hv=4235652837 ad='2f8ebc44'
select count(*) from t1
END OF STMT
PARSE #3:c=185971,e=216712,p=121,cr=10298,cu=0,mis=1,r=0,dep=0,og=1,tim=1235249942553304
BINDS #3:
EXEC #3:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1235249942553364
WAIT #3: nam='SQL*Net message to client' ela= 736 driver id=1650815232 #bytes=1 p3=0 obj#=52017 tim=1235249942554132
FETCH #3:c=7999,e=7775,p=0,cr=10041,cu=0,mis=0,r=1,dep=0,og=1,tim=1235249942561965
WAIT #3: nam='SQL*Net message from client' ela= 11 driver id=1650815232 #bytes=1 p3=0 obj#=52017 tim=1235249942562129
FETCH #3:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1235249942562141
WAIT #3: nam='SQL*Net message to client' ela= 510 driver id=1650815232 #bytes=1 p3=0 obj#=52017 tim=1235249942562658

分析:可以看到大量的读取undo(file 2)进行事务恢复