Advanced Diagnostic using oradebug dumpvar

oradebug工具是Oracle数据库调优和诊断的利器,合理运用oradebug可以大幅减少我们收集诊断信息所花费的时间。当然前提是合理运用,对于初级DBA有这样一个忠告,不要在生产环境中去接触或修改自己所不熟悉的领域的东西,这一点很重要。并不是说中高级DBA在知识和经验上能达到巨细靡遗的状态,实际上中高级Oracle DBA可能每天都在和新事物打交道。恰恰相反,中高级DBA是对以上忠告最忠实的践行者,在生产环境中绝不随意涉险于不确定、不明确、不熟悉的区域,很多时候这会让人觉得是一种”好奇心丧失”的表现。

言归正传,我们所要介绍的是oradebug的dumpvar命令,该命令用于打印转储固定的PGA/UGA/SGA的变量:,其语法如下:

oradebug dumpvar
Print/dump a fixed PGA/SGA/UGA variable.
Syntax                                                              Parameter
oradebug dumpvar <p|s|uga> <variable name> [level]                  <p|s|uga> PGA,SGA or UGA
                                                                    fixed variable name
                                                                    [level]
使用示例
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmac.cn


SQL> show parameter db_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     200

SQL> oradebug setmypid;
Statement processed.

SQL> oradebug dumpvar sga kcfdpk
kfil kcfdpk_ [698B950, 698B954) = 000000C8

这里的KCFDPK变量保存了db_files参数的值,000000C8 == 200

利用oradebug工具我们不仅可以做到对这些内部变量的窥视,还可以做到修改,
但是这对我们实际的诊断没有益处,仅仅可以用来满足某些模拟实验

仅仅了解dumpvar命令的使用方法并没有意义,只有和有意义的内部变量结合起来才能真正起到高级诊断的作用,在这里抛砖引玉罗列出部分诊断变量:

sgauso --  该变量可以用于判断use_stored_outlines

SQL> oradebug setmypid;
Statement processed.

Default use_stored_outlines=false;

SQL> oradebug dumpvar sga sgauso
qolprm sgauso_ [060021418, 06002143C) =
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

SQL> alter system set use_stored_outlines=true;
System altered.

SQL> oradebug dumpvar sga sgauso
qolprm sgauso_ [060021418, 06002143C) =
00000001 45440007 4C554146 00000054 00000000 00000000 00000000 00000000 00000000

45440007 4C554146 ==>      DEFAULT,意为优化器使用DEFAULT category中存放的outline.


ksmvpa -- the size of the variable part of the pga

SQL> oradebug dumpvar pga ksmvpa
b4 ksmvpa_ [0068AA6B4, 0068AA6B8) = 0000E920          -- 59680 bytes

kkjsre        -- The SGA variable kkjsre must be 1 for jobs to execute automatically.

SQL> oradebug  dumpvar sga kkjsre
word kkjsre_ [060025760, 060025764) = 00000001

SQL> exec  dbms_ijob.set_enabled(false);
PL/SQL procedure successfully completed.

SQL> oradebug  dumpvar sga kkjsre
word kkjsre_ [060025760, 060025764) = 00000000

kcmsmx          --the MAX reasonable scn 

SQL> oradebug dumpvar pga kcmsmx
kscn kcmsmx_ [00B7E811C, 00B7E8124) = CA7F0000 00000C6C         -- 11.2.0.2

SQL> oradebug dumpvar pga kcmsmx
kscn kcmsmx_ [0068AB02C, 0068AB034) = A701C000 00000B3D         -- 10.2.0.4 

Notice the MAX scn allowed on 11.2 is far higher that that at earlier releases
(due to the fix from bug 9254170).
Use the fix for 9254170 on ALL DBs with an SCN rate set to match between versions.
Avoid excessive SCN generation rates.

kcvlcm - logging checkpoints to alert - FALSE

SQL> show parameter log_checkpoints_to_alert

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert             boolean     TRUE

SQL> oradebug dumpvar sga kcvlcm
word kcvlcm_ [060019E88, 060019E8C) = 00000001

SQL> alter system set log_checkpoints_to_alert=false;

System altered.

SQL> oradebug dumpvar sga kcvlcm
word kcvlcm_ [060019E88, 060019E8C) = 00000000

kcvcpr - false (a checkpoint is requested)

SQL> oradebug dumpvar sga  kcvcpr
word kcvcpr_ [060019E90, 060019E94) = 00000000

kcvcpf - false (checkpointing fast)

kcvgcw -false a global checkpointing is waiting

SQL> oradebug dumpvar sga kcvgcw
sword kcvgcw_ [060025748, 06002574C) = 00000000

kcfcpd - true if checkpoint writes done

kcvblg  kcvblg[0] is s 1 incidate some file/s in backup mode

SQL> oradebug dumpvar sga kcvblg
ub4 * kcvblg_ [060019E18, 060019E20) = 9A248508 00000000

SQL> oradebug peek 0x9A248508 100
[09A248508, 09A24856C) =
00000001 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000002 00000000
00000002 00000002 00000000 00000000 ...

alter database begin backup;

SQL>  oradebug peek 0x9A248508 100
[09A248508, 09A24856C) =
00000001 00000003 00000003 00000003 00000003
00000003 00000003 00000003 00000003 00000003
00000003 00000003 00000003 00000003 ...

kcsgscn_           -- current scn 

SQL> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [0600122B0, 0600122E0) =
01C7DB4B 00000000 00000000 00000000              01C7DB4B  -- 29875019
0001E907 00000000 00000000 00000000
00000000 00000000 60011F90 00000000              60011F90 is fixed

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
   29875044

kslwlst_  --  waiter list latch

SQL> oradebug dumpvar sga kslwlst
ksllt kslwlst_ [200040AC, 20004174) =
00000000 00000009 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 ...

kcfdfk -- 2 * db_files

SQL> oradebug dumpvar sga kcfdfk
kfil kcfdfk_ [060017EF0, 060017EF4) = 00000190            --400    when db_files=200 

SQL> alter system set db_files=2000 scope=spfile;
System altered.

SQL> oradebug dumpvar sga kcfdfk
kfil kcfdfk_ [060017EF0, 060017EF4) = 00000FA0             --4000

kcffsz  Address of Datafile Size 

So kcbzib() passes in information about how many blocks to read
( and where to start reading from ) The assertion is 

  if (bno < 2 || bno + nblks - 1 > (kcflsz[fno] ? kcflsz[fno] : kcffsz[fno]))

              ==============================================================

              .......NOTE: This section is removed after 9g..................

  {
     /* if the user gave us the block number then this is an external error,
      * but if the dba was internally generated then this is a corruption of
      * some kind. */
     ASSERTNM5(usrdba, OERINM("kcfrbd_2"),
               fno, bno, nblks, kcflsz[fno], kcffsz[fno]);

SQL> oradebug dumpvar sga kcffsz
ub4 * kcffsz_ [060017F20, 060017F28) = 9988E700 00000000

SQL> oradebug peek 0x9988E700 200
[09988E700, 09988E7C8) =
00000001
0007B200
00030980
0000AF00
00067CA0
00003200
00280000
00000500
00000000
00000A00
00000000
00000000
00000600
00040000 ...

SQL> select blocks,to_char(blocks,'XXXXXXXX') hex_blocks from v$datafile;

    BLOCKS HEX_BLOCK
---------- ---------
    504320     7B200
    199040     30980
     44800      AF00
    425120     67CA0
     12800      3200
   2621440    280000
      1280       500
         0         0
      2560       A00
     38400      9600
      6400      1900

    BLOCKS HEX_BLOCK
---------- ---------
      1536       600
    262144     40000

13 rows selected.

kcflsz -- like kcffsz

SQL> oradebug dumpvar  sga kcflsz
ub4 * kcflsz_ [060017F28, 060017F30) = 99892588 00000000

SQL> oradebug peek 0x99892588 200
[099892588, 099892650) =
00000000 0007B200 00030980 0000AF00 00067CA0
00003200 00280000 00000500 00000000 00000A00
00000000 00000000 00000600 00040000 ...

THe arguments are file number, block number, number of blocks, kcflsz[fno],
kcffsz[fno]
Error comes out of kcf.c.
Can they try a validate structure cascade on this table and see what happens?
Please update the customer field so that it does not read internal.

kcf.c kcf.c Kernel Cache Files component.
ksl.c Kernel Service layer Latching & Wait-post Implement.
ksm.c Kernel Service Memory component implementation.
kkj.c Kernel Kompiletime Job queue.

[转]如何阅读systemstate dump

转自老白的<oracle rac 日记>一书,

dump systemstate产生的跟踪文件包含了系统中所有进程的进程状态等信息。每个进程对应跟踪文件中的一段内容,反映该进程的状态信息,包括进程信息,会话信息,enqueues信息(主要是lock的信息),缓冲区的信息和该进程在SGA区中持有的(held)对象的状态等信息。dump systemstate产生的跟踪文件是从dump那一刻开始到dump任务完成之间一段事件内的系统内所有进程的信息。

那么通常在什么情况下使用systemstate比较合适呢?

Oracle推荐的使用systemstate事件的几种情况是:

数据库hang住了

数据库很慢

进程正在hang

数据库出现某些错误

资源争用

 

dump systemstate的语法为:

ALTER SESSION SET EVENTS ‘immediate trace name systemstate level 10’;

也可以使用ORADEBUG实现这个功能:

sqlplus -prelim / as sysdba

oradebug setmypid

oradebug unlimit;

oradebug dump systemstate 10

如果希望在数据库发生某种错误时调用systemstate事件,可以在参数文件(spfile或者pfile)中设置event参数,

例如,当系统发生死锁(出现ORA-00060错误)时dump systemstate:

event = “60 trace name systemstate level 10”

 

LEVEL参数:

10    Dump all processes (IGN state)

5     Level 4 + Dump all processes involved in wait chains (NLEAF state)

4     Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state)

3     Level 2 + Dump only processes thought to be in a hang (IN_HANG state)

1-2   Only HANGANALYZE output, no process dump at all

 

如果Level过大的话会产生大量的跟踪文件并影响系统的I/O性能,建议不要采用3级以上的跟踪。Hanganalyze报告会分作许多片断,会话片断信息总是由一个header详尽描述被提取的的会话信息。

 

一般来说,一份systemstate dump中包含了以下内容:

dump header文件头

process dump dump时所有的process的dump信息,每个process一个专门的章节。

call dump在process dump中,包含call dump

session dump每个process中,都有1个或多个(MTS时)session dump

enqueue dump

buffer dump在session dump中可能包含buffer dump

 

在阅读systemstate dump时,一般首先使用ASS工具来进行分析。ASS是oracle工程师编写的一个AWK脚本,用于分析systemstate dump文件,找出dump中可能存在问题的地方。通过ASS的输出结果,我们就可以发现一些blocker的线索,这些线索就是我们重点要查看的地方。

 

我们可以通过搜索SO的地址信息来定位某个SO,找到后分析这个SO的信息,并且通过PARENT SO的地址找到其PARENT,建立这些SO的关系图。比如我们找到一个SESSION的SO,就可以看看这个session属于哪个process,这个session正在执行的sql是什么,等等。通过这种分析,就把可能存在问题的SO及关联的SO全部找出来,这样就为进一步分析提供了素材。

 

 

1、标准的state object header(SO)

state object header中包含了一些基本的信息,比如:

SO: c00004ti4jierj, type: 2. owner: 0000000000, flag: init/-/-/0x00

其中SO是state object的号码;

type表示state object的类别;

TYPE: state object的已知类别:

2 process(进程)

3 call

4 session(会话信息)

5 enqueue(锁信息)

6 file infomation block(文件信息块,每个FIB标识一个文件)

11 broadcast handle(广播消息句柄)

12 KSV slave class state

13 ksvslvm

16 osp req holder(会话执行os操作的holder)

18 enqueue resource detail(锁资源详细资料)

19 ges message(ges消息)

20 namespace [ksxp] key

24 buffer [db buffer]

36 dml lock

37 temp table lock(临时表锁)

39 list of blocks(用于block cleanout的块列表清单)

40 transaction(事务)

41 dummy

44 sort segment handle(排序段句柄)

50 row cache enqueue

52 user lock

53 library cache lock

54 library cache pin

55 library cache load lock

59 cursor enqueue

61 process queue

62 queue reference

75 queue monitor sob

 

owner是这个SO的父节点(如果为0,说明是最顶层的SO);flag表示状态,值有以下三种:

kssoinit;state object被初始化了

kssoflst;state object在freelist上

kssofcln;state object已经被pmon释放了。

 

State object header的数据结构如下:

struct kssob {

unsigned char       kssobtyp; /* state object的类别*/

unsigned char       kssobflg; /* flags */

unsigned char       kssobdelstage;

struct kssob *       kssobown; /*拥有者的SO指针*/

kgglk                   kssoblnk; /*在父对象成员链中的指针*/

}

2、processstate dump(ksupr)

processstate dump转储了进程的状态,从这些信息中我们可以了解进程的基本属性以及进程的状态。

在阅读processstate dump时,我们主要关注的进程的标识(FLAG),从中也可以知道进程的类别。从”(latch info)”中可以看到进程等待latch的情况,这也有助于了解进程故障的原因。另外,进程的OS信息对于进一步了解进程情况也是很有帮助的。

实际上,x$ksupr包含了进程的信息,通过该内存视图可以更进一步了解processstate dump的内容。

ADDR    地址

INDX    序号

INST_ID实例ID

KSSPAFLG   state object的状态:

KSSOINIT 0x01 // state object initialized

KSSOFLST 0x02 // state object is on free list

KSSOFCLN 0x04 // state object freed by PMON(for debugging)

KSSPAOWN  该SO的OWNER,如果自己是顶层的SO,那么owner为0

KSUPRFLG   该process的状态:

KSUPDEAD 0x01 process is dead and should be cleaned up

KSUPDSYS 0x02 detached,system process

KSUPDFAT 0x04 detached,fatal(system) process

KSUPDCLN 0x08 process is cleanup(pmon)

KSUPDSMN 0x10 process is smon

KSUPDPSU 0x20 pseudo process

KSUPDMSS 0x40 muti-stated server

KSUPDDPC 0x80 dispatcher process

KSUPRSER   进程的序号(SERIAL NO)

KSUPRIOC

KSLLALAQ   持有的latch

KSLLAWAT   正在等待的latch

KSLLAWHY  latch请求的上下文(用于debug)

KSLLAWER  latch请求的位置(用于debug)

KSLLASPN   本进程正在spin的latch

KSLLALOW  所持有latch级别的位图(0~9级)

KSLLAPSC   进程发出的POST消息的计数

KSLLAPRC   进程收到的POST消息的计数

KSLLAPRV   收到的最后一个POST的LOC ID,参考图中的①

KSLLAPSN   最后一个发送POST的LOC ID,参考图中的②

KSLLID1R    RESOURCE ID的第一部分

KSLLID2R    RESOURCE ID的第二部分

KSLLRTYP   RESOURCE TYPE+RESOURCE FLAG

KSLLRMTY  RESOURCE MANAGE的类型:

KRMENQ    0x01 enqueues

KRMLATCH  0x02 latches

KRMLIBCALK 0x03 library cache locks

KRMBUFLK  0x04 buffer locks

KSLLARPO   最后一个发送消息给这个进程的OS进程

KSLLASPO   这个进程最后一个发送信息过去的OS进程

KSUPRPID    OS进程号

KSUPRWID   等待事件的ID

KSUPRUNM  OS用户名

KSUPRMNM 用户的机器名

KSUPRPNM  用户程序名

KSUPRTID    用户终端名

KSSRCOBJ   STATE OBJECT RECOVERY数据中的正在被操作的对象

KSSRCFRE   STATE OBJECT RECOVERY数据中的FREELIST的地址

KSSRCSRC   STATE OBJECT RECOVERY数据中的SOURCE PARENT

KSSRCDST   STATE OBJECT RECOVERY数据中的DESTINATION PARENT

KSASTQNX  MESSAGE STATE中的前向指针

KSASTQPR   MESSAGE STATE中的后向指针

KSASTRPL   MESSAGE STATE中的REPLY VALUE

KSUPRPGP   PROCESS GROUP的名字

KSUPRTFI    进程的trace文件名

KSUPRPUM  PGA使用的内存

KSUPRPNAM  KSUPRPNAM+KSUPRPRAM是pga分配内存的总和

KSUPRPRAM

KSUPRPFM  pga可释放的内存

KSUPRPMM pga使用的最大内存

3、session state object 

会话信息中包含了大量我们所需要的信息,一般来说会话状态块是我们分析会话情况的重点。

在会话状态信息中,flag是十分重要的,我们可以从flag中了解会话目前的情况,以及flag位图的详细信息。该会话正在执行的sql和pl/sql的SO地址可以让我们找到当前会话正在做的工作,有助于进一步

分析。另外,会话的等待事件和历史等待事件可以让用户了解会话在现在和过去一段时间里等待的情况,如果要分析会话故障原因的话,这些资料都是十分重要的。

flag的位图如下:

KSUSFUSR   0x00000001   user session (as opposed to recursive session)

KSUSFREC   0x00000002   recursive session(always internal)

KSUSFAUD   0x00000004   audit logon/logoff,used by cleanup

KSUSFDCO  0x00000008   disable commit/rollback from plsql

KSUSFSYS   0x00000010   user session created by system processes

KSUSFSGA   0x00000020   whether UGA is allocate in sga

KSUSFLOG   0x00000040   whether user session logs on to ORACLE

KSUSFMSS   0x00000080   user session created by multi-stated server

KSUSFDIT    0x00000100   disable (defer) interrupt

KSUSFCLC   0x00000200   counted for current license count decrement

KSUSFDET   0x00000400   session has been detached

KSUSFFEX   0x00000800   “forced exit”during shutdown normal

KSUSFCAC   0x00001000   (cloned) session is cached

KSUSFILS    0x00002000   default tx isolation level is serializable

KSUSFOIL    0x00004000   override serializable for READ COMMITTED

KSUSFIDL    0x00008000   idle session scheduler

KSUSFSKP   0x00010000   SKIP unusable indexes maintenance

KSUSFCDF   0x00020000   defer all deferrable constraint by default

KSUSFCND  0x00040000   deferable constraints are immediate

KSUSFIDT    0x00080000   session to be implicitly detached

KSUSFTLA   0x00100000   transaction audit logged

KSUSFJQR   0x00200000   recource checking in job q process enabled

KSUSFMGS  0x00400000   session is migratable

KSUSFGOD  0x00800000   migratable session need to get ownership id

KSUSFSDS   0x01000000   suppress/enable TDSCN      computations

KSUSFMSP   0x02000000   parent of migratable session

KSUSFMVC  0x04000000   MV container update progress

KSUSFNAS   0x08000000   an NLS alter session call was done

KSUSFTRU    0x10000000  a trusted callout was performed

KSUSFHOA   0x20000000   an HO agent was called

KSUSFSTZ   0x40000000   an alter session set time_zone was done

KSUSFSRF   0x80000000   summary refresh

 

4、call state object

Call state object是针对一个call的,我们查看call state object的时候一定要注意depth值,以此判断该call是用户调用还是递归调用。

5、enqueue state object  

从enqueue state object中,我们主要可以查看锁的类型、锁的模式以及flag。

6、transaction dump

Transaction dump对应的oracle内存结构是KTCXB,可以通过X$KTCXB来了解更详细的情况。

flag的描述如下(资料来源早期版本,针对10g可能略有不同):

1          allocated but no transaction

2          transaction active

4          state object no longer valid

8          transaction about to commit/abort

10          space management transaction

20          recursive transaction

40          no undo logging

80          no change/commit,must rollback

100        use system undo segment (0)

200        valid undo segment assigned

400        undo seg assigned,lock acquired

800        change may have been made

1000      assigned undo seg

2000      required lock in cleanup

4000      is a pseudo space extent

8000      save the tx table & tx ctl block

10000    no read-only optimize for 2pc

20000    multiple sess attached to this tx

40000    commit scn future set

80000    dependent scn future set

100000   dist call failed,force rollback

200000   remote uncoordinated ddl tx

400000   coordinated global tx

800000   pdml transaction

1000000        next must be commit or rollback

2000000        coordinator in pdml

4000000        disable block level recovery

8000000        library and/or row caches dirty

10000000      serializable transaction

20000000      waiting for unbound transaction

40000000      loosely coupled transaction branch

80000000      long-running transaction

 

flag2的描述如下(资料来源早期版本,针对10g可能略有不同):

1    tx needs refresh on commit

2    delete performed in tx

4    concurrency check enabled

8    insert performed

10   dir path insert performed

20   fast rollback on net disconnect

40   do not commit this tx

80   this tx made remote change

100 all read-only optim enabled

 

事务环境的结构如下:

Struct ktcev {

kenv             ktcevenv;

kuba             UBA的高水位;

kuba             ktcevucl;

sb2        在undo高水位块中的剩余空间;

kcbds     undo block的描述;

kdbafr    undo段头的DBA地址;

kturt *    指向undo seg的KTURT结构;

}

 

7、library object lock/handle

library object lock如下:

Flags的描述如下:

KGLLKBRO  0x0100   this lock is broken

KGLLKCBB   0x0200   this lock can be broken

KGLLKPNC  0x0400   “kgllkpnc” is a valid pin for the call

KGLLKPNS   0x0800   “kgllkpns” is a valid pin for the session

KGLLKCGA  0x1000   this lock is in CGA memory

KGLLKINH   0x2000   the instance lock is inherited

KGLLKLRU   0x4000   lock protects an object on the session cache lru

KGLLKKPC   0x8000   lock protects an object in the session keep cache

KGLLKRES   0x0010   reserved lock preventing handle from being freed

KGLLKCBK   0x0020   need to callback the client for delete/dump

 

作为library object的主体,handle的信息如图:

其中namespace的取值包括:

CRSR    cursor

TABL     table/view/sequence/synonym

BODY    body(e.g.,package body)

TRGR    trigger

INDX    index

CLST     cluster

KGLT    internal KGL testing

PIPE      pipe

LOB      lob

DIR       directory

QUEU    queue

OBJG     replication object group

PROP    replication propagator

JVSC     java source

JVRE     java resource

ROBJ     reserved for server-side RepAPI

REIP      replication internal package

CPOB    context policy object

EVNT    pub_sud internal information

SUMM   summary

DIMN    dimension

CTX      app context

OUTL    stored outlines

TULS     ruleset objects

RMGR   resource manager

XDBS    xdb schema

PPLN     pending scheduler plan

PCLS     pending scheduler class

SUBS     subscription information

LOCS    location information

RMOB   remote objects info

RSMD   RepAPI snapshot metadata

JVSD     java shared data

STFG    file group

TRANS  transformation

RELC     replication – log based child

STRM    stream:capture process in log-based replication

REVC    rule evaluation context

STAP     stream:apply process in log-based replication

RELS     source inlog-based replication

RELD    destination in log-based replication

IFSD     IFS schema

XDBC    XDB configuration management

USAG    user agent mapping

VOMDTABL  multi-versioned object for table

JSQI      scheduler-event queue info object

CDCS    change set

VOMDINDX multi-versioned object for index

STBO    sql tuing base object

HTSP     hintset    object

JSGA     scheduler global attributes

JSET     scheduler start time namespace

TABL_T temporary table

CLST_T temporary cluster

INDX_Ttemporary index

SCPD    sratch pad

JSLV      scheduler job slave

MODL   mining models

 

状态标志位的取值:

EXS       existent

NEX      no-existent

LOC

CRT       being created

ALT       being altered

DRP       being dropped

PRG       being purged

UPD      being uodated

RIV       marked for rolling invalidation

NRC      don’t recover when an exclusive pin fails

UDP      dep being updated

BOW     bad owner of database link

MEM     has frame memory associated with heap 0

REA       protected with read-only access at least once

NOA      protected with no access at least once

 

通过对library cache object/handle的分析,可以找到相关的sql以及cursor的状态。

 

How to trigger ORA-00600,ORA-7445 by manual

Sometime we’d like to trigger ORA-00600/7445 internal errors for testing purpose, But it’s not easy if you don’t know a little trick like below:

declare
a exception;
pragma exception_init(a,-600);
begin
raise a;
end;

declare
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [],
[], [], []
ORA-06512: at line 5

or
oradebug unit_test dbke_test dde_flow_kge_ora ouch! 0 0

7445:
select spidfrom v$processp, v$sessions
where p.addr= paddr
and sid= sys_context('USERENV','SID');

kill -SEGV $PID

or

declare
a exception;
pragma exception_init(a,-7445);
begin
raise a;
end;

EVENT:10052 "Stop SMON from cleaning up obj$"

Text:   Stop SMON from cleaning up obj$
——————————————————————————-

NOTE:
Events should NEVER be set by customers unless advised to do so by
Oracle Support Services. You must read <Note:75713.1> before setting ANY
event on a system.

Usage:
~~~~~~
To disable cleanup of OBJ$ by SMON set this event in the init.ora file
thus:

EVENT=”10052 trace name context forever”

Explanation:
~~~~~~~~~~~~
Setting event 10052 stops SMON trying to clean up OBJ$.

The implication of this is that OBJ$ will grow with entries for
non-existent objects, eg: objects which have been dropped and
object entries created for negative dependency reasons.

The usual reason for setting this event is to prevent SMON from
crashing / spinning in the OBJ$ cleanup code whilst diagnostics
or further investigations are performed. It is also sometimes
used in Parallel Server environments so that OBJ$ cleanup is
only performed on selected nodes.

@   The routine it disables is <Function:kqlclo> (KQL Clean Obj$) which scans
@   obj$ for objects marked as non-existent and then deletes them if
@   they are not child dependencies of anything.

EVENT: 10500 "turn on traces for SMON"

Error:  ORA 10500
Text:   turn on traces for SMON
-------------------------------------------------------------------------------
Cause:
Action:
        Level:  <=5  trace instance recovery
        > 5  trace posting of SMON

To set event 10500:

For the instance:
a.  Shutdown database
b.  Edit the initialisation parameter file and add:
    event="10500 trace name context forever, level <value>"
c.  restart the database

For the SMON session:
Post the SMON process using oradbx (Oracle 7) or oradebug (Oracle 8).
For oradebug from server manager issue:

oradebug setospid <OS PID>
oradebug event 10500 trace name context forever, level <value>

For further information about oradebug Note 29786.1
                              oradbx   Note 28863.1

<value> is 'Level' as per above

Setting an Oracle event:The structure of the trace syntax

PURPOSE
-------

The purpose of this article is to explain briefly the structure of the syntax to
event-based trace generation.

Setting an event: The structure of the trace syntax
---------------------------------------------------

@ A comprehensive/full overview of the event syntax can be found in:
@ Note:9331.1 - Full Event Syntax (from ksdp.c)
@ Note:45217.1 - Summary Event Syntax for WWCS

0. "Setting an Event" - Abstract definition:
============================================

   "Setting an event" means to tell oracle to generate information in form of a
   so called trace file in the context of the event.

1. Event Classes to be traced:
==============================

   There are 4 Classes of traceable events:

   Class 1 "Dump something": Traces are generated upon so called unconditioned,
                             immediate, events. This is the case when oracle data has
			     to be dumped like, e.g., the headers of all redolog files
			     or the contents of the controlfile. These events can not
			     be set in the init<SID>.ora.

   Class 2 "Trap on Error" : Setting this class of (error-) events cause oracle to
                             generate a so called errorstack everytime the event happens.

   Class 3 "Change execution path" : Setting such an event will cause oracle to
                             change the execution path for some specific code segment.
			     For example, setting event "10269" prevents SMON from doing
			     free space coalescing.

   Class 4 "Trace something": Events from this class are set to obtain traces that are
                             used for, e.g., sql tuning. A common event is "10046" which
			     will cause oracle to trace  the sql access path on each
			     sql-statement.

II. Event based trace generation syntax - Overview and examples:
================================================================

   1. Session:         alter session set events '10181 trace name context forever, level 1000';
   2. init<sid>.ora:   event="10181 trace name context forever, level 1000";

   -------------------------------------------------------------------------------------------
  | TRACE      |                        TRACE SYNTAX                                          |
  | CLASS      |                                                                              |
  |-------------------------------------------------------------------------------------------|
  |            | <event name> |                     <action>                                  |
  |-------------------------------------------------------------------------------------------|
  |            |              | <action key word> | "name" | <trace name> | <trace qualifier> |
   -------------------------------------------------------------------------------------------|
  |            |              |                   |        |              |                   |
  |            |  immediate   |   trace           | "name" | blockdump    |    level 67110390 |
  |            |  immediate   |   trace           | "name" | redohdr      |    level 10       |
  |            |  immediate   |   trace           | "name" | file_hdrs    |    level 10       |
  | "Dump      |  immediate   |   trace           | "name" | controlf     |    level 10       |
  | Something" |  immediate   |   trace           | "name" | systemstate  |    level 10       |
  |            |              |                   |        |              |                   |
  |-------------------------------------------------------------------------------------------
  |            |              |                   |        |              |                   |
  |            |        942   |   trace           | "name" | errorstack   |    forever        |
  |            |        942   |   trace           | "name" | errorstack   |    off            |
  | "Trap      |         60   |   trace           | "name" | errorstack   |    level 1        |
  | on         |       6501   |   trace           | "name" | processstate |    level 10       |
  | Error"     |       4030   |   trace           | "name" | heapdump     |    level 2        |
  |            |              |                   |        |              |                   |
  |-------------------------------------------------------------------------------------------
  |            |              |                   |        |              |                   |
  | "Change    |      10269   |   trace           | "name" | context      | forever, level 10 |
  | Execution  |              |                   |        |              |                   |
  | path"      |              |                   |        |              |                   |
  |            |              |                   |        |              |                   |
  |-------------------------------------------------------------------------------------------
  |            |              |                   |        |              |                   |
  |            |      10046   |   trace           | "name" | context      | forever, level 12 |
  | "Trace     |      10046   |   trace           | "name" | context      | off               |
  | something" |              |                   |        |              |                   |
  |            |              |                   |        |              |                   |
   -------------------------------------------------------------------------------------------

III: Trace syntax: Annotations
===============================

   0. There are tools like oradebug that allow for setting an event in another
      session; this is useful, e.g., for tracing the export utility.
      @Setting Events from Oracle Tools <Note:45219.1">
      @For a list of common ACTIONS see <Event:List>
      @For COMMON numeric events see    <event:Numeric>
   1. The general syntax of setting an event is:  <event name>  <action>
      <action> consists of three parts:           <action key word> <trace name> <trace qualifier>
      @<action key word> can be either "trace", "crash", or "debug".
      @ See <Note:9331.1">
      <event name> is either "immediate", by this indicating an unconditioned event
      or an event name given as a symbolic number from the system event name table.
      An unconditioned event (keyword "immediate") cannot be set in the parameter file.
      <trace qualifier> "forever" means: Activate a trace whenever this event occurs.
      <trace name> "context" is a special trace name and pertains only to events set up
      to either trace a diagnostic event or to change the behaviour of the oracle
      code execution path. It cannot be used in conjunction with errorstack- ("errorstack")
      or dump-generating ("immediate") events.
   2. There are exactly 2 types of events, session-events and process-events.
      Process-events are initialized in the parameter file, session-events
      are initialized with the "alter session..." or "alter system ..."command.
      When checking for posted events, the oracle server first checks for session events
      then for process-events.

RELATED DOCUMENTS
-----------------
@     Event Syntax for most common forms of event setting <Note:45217.1>
@     The FULL Event syntax <Note:9331.1>
@     Setting Events from Oracle Tools <Note:45219.1>
@     List of common ACTIONS <Event:List>
@     COMMON numeric events <Event:Numeric>

沪ICP备14014813号-2

沪公网安备 31010802001379号