oracle 10513,oracle event 10513作用

最新推荐文章于 2021-04-10 21:34:06 发布

weixin_40009026

最新推荐文章于 2021-04-10 21:34:06 发布

阅读量94

点赞数

文章标签： oracle 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(*)

----------

[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)进行事务恢复