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)進行事務恢復

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-626579/，如需轉載，請註明出處，否則將追究法律責任。