PostgreSQL.Essential.Reference
2010/06/07 by Leave a Comment
ORA-600 internal error[kqrfrpo]一例
2010/06/07 by Leave a Comment
3月17日某客户主机上出现了文件系统空间不足的问题，经查发现是Oracle在1点左右产生了大量trace跟踪文件以致耗尽了磁盘空间，这些trace跟踪文件均是由Oracle服务进程遭遇错误“ORA-600: : internal error code, arguments: [kqrfrpo], [0x700000279FF98E0], [11], [], [], [], [], []”后在用户udump目录下所产生。
据客户工作人员称，在当时情况下(1点20分左右)以系统DBA权限本地方式登录数据库缓慢，而在1点25分时，登录缓慢现象消失，当时检查数据库，发现没有异常等待(如latch free等)和资源锁，数据库恢复正常。
Ora-600错误代码代表发生的错误是Oracle内部错误，一般是由于Oracle bug，操作系统bug或不当设置等问题引起的；具体发生的问题细节需要通过错误附加项来了解，本次错误中出现的第一位错误附加项是kqrfrpo。
通过METALINK相关文档我们发现ORA-600 kqrfrpo错误极有可能是Oracle 9i中的bug 3835429 OERI[kqrfrpo] / DB hang after killing a user process 所引起的，该bug跨越版本9.2.0.1.0至9.2.0.6.0，在版本9.2.0.7.0中得到了修正。
该bug的产生原因简述为，当一个用户进程在某个不恰当的时机被杀死，那么字典缓存latch可能无限期地被挂起，当其他进程无法获得该闩，则可能引起数据库级别的挂起(database wide hang)。
进一步分析ORA-600 kqrfrpo错误可能造成的影响，METALINK文档中就该错误可能造成影响的分类如下：
* 实例意外终止，即Oracle数据库crash(如pmon进程发生ora-600错误)
* 进程意外终止，在数据库层面表现为会话级的失败
* 内存块损坏
* 可能导致磁盘上的数据损坏
* 无任何影响
幸运的是，本次的ORA-600[kqrfrp]错误没有发生在数据库后台进程(pmon等)中，因此没有发生实例意外终止的现象，但出现登录数据库缓慢的现象，并且伴随用户进程因ora-600错误而异常中止。仔细观察600错误的trace文档可以发现，其中部分数据库服务进程的应用客户端为JDBC THIN CLIENT即java瘦客户端应用，若该类应用服务在活动情况下遭遇上述错误可能导致SQL执行出现问题，进而使得应用逻辑在数据库层面未得到实现。实际的情况仍需要得到应用方面的确认。
回顾该系统之前的情况，于3月16日夜间因通过cics连接的数据库服务进程遭遇ORA-600[4454]错误，在当时情况下无法在数据库级别杀死session，故在操作系统级别杀死了上述遭遇ORA-600[445]错误的服务进程；联系到以上情况，有可能是杀死进程触发了BUG，使得ORA-600[kqrfrpo]错误出现。从杀死用户进程到ora-600错误出现，期间跨越了4个小时。
针对ORA-600 kqrfrpo错误，因考虑到该错误是通过杀死用户服务进程的操作触发，故可以将之视为在特殊操作情况下才可能发生的隐性错误，实际数据库运行周期内需要杀死服务进程的情况并不常见，故该错误发生的概率较低。建议：
* 优先使用alter system kill session的命令来清除相关会话和进程
* 应用针对该bug的补丁3835429以彻底解决该问题。
直接路径读取对于延迟块清除的影响
2010/06/02 by 2 Comments
在Oracle 11g版本中串行的全表扫描可能使用直接路径读取(direct path read)的方式取代之前版本中一直使用的DB FILE SCATTERED READ, 显然direct path read具备更多的优势：
1. 减少了对栓的使用，避免可能的栓争用
2.物理IO的大小不再取决于buffer_cache中所存在的块；试想某个8个块的extent中1,3,5,7号块在高速缓存中，而2,4,6,8块没有被缓存，传统的方式在读取该extent时将会是对2,4,6,8块进行4次db file sequential read,这是一种十分可怕的状况，其效率往往要比单次读取这个区间的所有8个块还要低得多，虽然Oracle为了避免这种情况总是尽可能的不缓存大表的块(读入后总是放在队列最冷的一端);而direct path read则可以完全避免这类问题，尽可能地单次读入更多的物理块。
当然直接路径读取也会引入一些缺点：
1.在直接路径读取某段前需要对该对象进行一次段级的检查点(A segment checkpoint).
2.可能导致重复的延迟块清除操作(我们假设你了解delayed block cleanout是什么).
metalink 文档[ID 793845.1] 对该新版本中的变化进行了描述:
Applies to:
Oracle Server – Enterprise Edition – Version: 11.1.0.6 to 11.1.0.7
This problem can occur on any platform.Symptoms
After migrating an 11g database from a standalone to a 4-node RAC, a noticeable increase of 'direct path read' waits were observed at times.Here are the Cache sizes and Top 5 events.waits Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------- Buffer Cache: 3,232M 3,616M Std Block Size: 8K Shared Pool Size: 6,736M 6,400M Log Buffer: 8,824K Top 5 Timed Foreground Events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Avg wait % DB Event Waits Time(s) (ms) time Wait Class ------------------------------ ------------ ----------- ------ ------ ---------- DB CPU 13,916 42.1 direct path read 1,637,344 13,359 8 40.4 User I/O db file sequential read 47,132 1,111 24 3.4 User I/O DFS lock handle 301,278 1,028 3 3.1 Other db file parallel read 14,724 554 38 1.7 User I/OChanges
Migrated from a standalone database to a 4-node RAC.
Moved from Unix file system storage to ASM.Using Automatic Shared Memory Management (ASMM).
The setting of db_cache_size in spfile/pfile is low compared to normal workload requirements.Cause
There have been changes in 11g in the heuristics to choose between direct path reads or reads through buffer cache for serial table scans.
In 10g, serial table scans for “large” tables used to go through cache (by default) which is not the case anymore. In 11g, this decision to read via direct path or through cache is based on the size of the table, buffer cache size and various other stats.
Direct path reads are faster than scattered reads and have less impact on other processes because they avoid latches.Solution
When using Automatic Shared Memory Management (ASMM) and with buffer cache low limit set at a low end compared to the normal workload requirements and usually after startup, 11g might choose to do serial direct path read scans for large tables that do not fit in the SGA. When ASMM increases the buffer cache due to increased demand, 11g might not again do serial direct path read scans for these same large tables. If you like to avoid this from happening, you should note the buffer cache and share pool requirements for a normal workload and set the low limits of buffer cache and shared pool in spfile/pfile close to these normal workload values.
db_cache_size
shared_pool_size
下面我们对直接路径读取对于延迟块清除造成的影响进行测试：
SQL> create table tv as select rownum rn,rpad('A',600,'Z') rp from dual
2 connect by level <=300000;
表已创建。
新建一个会话a:
SQL> set linesize 200 pagesize 1400;
SQL> select count(*) from tv;
COUNT(*)
----------
300000
SQL> select vm.sid, vs.name, vm.value
2 from v$mystat vm, v$sysstat vs
3 where vm.statistic# = vs.statistic#
4 and vs.name in ('cleanouts only - consistent read gets',
5 'session logical reads',
6 'physical reads',
7 'physical reads direct');
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
25 session logical reads 27281
25 physical reads 27273
25 physical reads direct 27273
25 cleanouts only - consistent read gets 0
-- 显然查询采用了直接路径读取方式
SQL> update tv set rn=rn+1; -- 尝试批量更新
SQL> alter system flush buffer_cache;
-- 刷新高速缓存，造成延迟块清除的情景，并提交
系统已更改。
SQL> commit;
提交完成。
新建一个会话b:
SQL> set linesize 200 pagesize 1400;
SQL> select count(*) from tv;
COUNT(*)
----------
300000
SQL> select vm.sid, vs.name, vm.value
2 from v$mystat vm, v$sysstat vs
3 where vm.statistic# = vs.statistic#
4 and vs.name in ('cleanouts only - consistent read gets',
5 'session logical reads',
6 'physical reads',
7 'physical reads direct','redo size');
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
25 session logical reads 54554
25 physical reads 27273
25 physical reads direct 27273
25 redo size 0
25 cleanouts only - consistent read gets 27273
--查询采用direct path read时产生了延迟块清除操作，但不产生redo
SQL> select count(*) from tv;
COUNT(*)
----------
300000
SQL> select vm.sid, vs.name, vm.value
2 from v$mystat vm, v$sysstat vs
3 where vm.statistic# = vs.statistic#
4 and vs.name in ('cleanouts only - consistent read gets',
5 'session logical reads',
6 'physical reads',
7 'physical reads direct','redo size');
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
25 session logical reads 109104
25 physical reads 54546
25 physical reads direct 54546
25 redo size 0
25 cleanouts only - consistent read gets 54546
再次查询仍采用直接路径读取,产生了相同数目的延迟块清除操作，并没有产生redo;可见direct path read的清除操作仅是针对从磁盘上读取到PGA内存中的镜像，而不对实际的块做任何修改，因而也没有任何redo;
下面我们使用普通串行全表扫描方式，设置event 10949可以避免采用直接路径读取方式.关于该事件可以参见这里.
SQL> ALTER SESSION SET EVENTS '10949 TRACE NAME CONTEXT FOREVER';
会话已更改。
SQL> select count(*) from tv;
COUNT(*)
----------
300000
SQL> select vm.sid, vs.name, vm.value
2 from v$mystat vm, v$sysstat vs
3 where vm.statistic# = vs.statistic#
4 and vs.name in ('cleanouts only - consistent read gets',
5 'session logical reads',
6 'physical reads',
7 'physical reads direct','redo size');
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
25 session logical reads 163662
25 physical reads 81819
25 physical reads direct 54546
25 redo size 1966560
25 cleanouts only - consistent read gets 81819
SQL> select count(*) from tv;
COUNT(*)
----------
300000
SQL> select vm.sid, vs.name, vm.value
2 from v$mystat vm, v$sysstat vs
3 where vm.statistic# = vs.statistic#
4 and vs.name in ('cleanouts only - consistent read gets',
5 'session logical reads',
6 'physical reads',
7 'physical reads direct','redo size');
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
25 session logical reads 190947
25 physical reads 95673
25 physical reads direct 54546
25 redo size 1966560
25 cleanouts only - consistent read gets 81819
第一次采用普通全表扫描方式时产生了与direct path read时相同量的延迟块清除操作，并因此产生了大量的redo,这种模式回归到了最经典的延迟块清除情景中；之后的一次读取则不再需要清除块和产生重做了，我们在读取一个“干净”的表段。
从以上测试我们可以了解到，11g中使用更为广泛的direct path read方式对有需要延迟块清除操作的段所可能产生的影响，因为实际没有一个“修改块”的操作，所以虽然延迟块清除操作在该种模式下每次都必须产生，却实际没有产生脏块，因而也就不会有“写块”的必要，故而也没有redo的产生。所产生的负载可能更多的体现在cpu time的使用上。
ORA-00600 [KCBZPB_1], [59033077], [4], [1], [] example
2010/05/31 by 1 Comment
below is the 600 entry in the alertlog:
alert.log: Hex dump of Absolute File 14, Block 312821 in trace file /u01/ORAHOME/app/oracle/admin/TIGERS7/bdump/tigers7_dbw0_10999.trc *** Corrupt block relative dba: 0x0384c5f5 (file 14, block 312821) Bad header found during preparing block for write Data in bad block - type: 6 format: 1 rdba: 0x00000384 last change scn: 0xf90b.c5f55f7c seq: 0x9 flg: 0x72 consistency value in tail: 0x0001f90b check value in block header: 0x102, block checksum disabled spare1: 0x6, spare2: 0x2, spare3: 0x0 *** Thu Apr 16 18:32:48 2009 Errors in file /u01/ORAHOME/app/oracle/admin/TIGERS7/bdump/tigers7_dbw0_10999.trc: ORA-00600: internal error code, arguments: [kcbzpb_1], [59033077], [4], [1], [], [], [], [] Thu Apr 16 18:32:49 2009 Errors in file /u01/ORAHOME/app/oracle/admin/TIGERS7/bdump/tigers7_dbw0_10999.trc: ORA-00600: internal error code, arguments: [kcbzpb_1], [59033077], [4], [1], [], [], [], [] DBW0: terminating instance due to error 600 Instance terminated by DBW0, pid = 10999 Thu Apr 16 19:04:58 2009
After that, We have executed dbverify against the identified file and it produced no errors:
DBVERIFY: Release 9.2.0.8.0 - Production on Thu Apr 16 19:31:32 2009 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. DBVERIFY - Verification starting : FILE = /u32/ORAINDX/oradata/TIGERS7/indx01.dbf DBVERIFY - Verification complete Total Pages Examined : 1280000 Total Pages Processed (Data) : 0 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 1262823 Total Pages Failing (Index): 0 Total Pages Processed (Other): 8751 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 8426 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Highest block SCN : 10386833124905 (2418.1602203177)
we do open a sr ,and oracle support suggest to do below query:
ACTION PLAN
===========
1) please describe the sequence of events leading up to the problem
2) please upload the alert.log. ZIP if >2MB. Dot not use RAR.
3) please describe your backup strategy:
a) when was your last valid backup?
b) are you using RMAN to perform this backup?
c) do you have all archivelogs from the last backup to now?
d) was this a hot or cold backup?
4) even if you’re not using RMAN, run the following in RMAN:
$ rman target /
RMAN> backup validate check logical database;
5) Once RMAN validate is completed, run the following in SQL*Plus as SYSDBA:
SQL> select * from v$database_block_corruption;
6) Please run the following query in SQL*Plus as SYSDBA
— db must be in either MOUNT or OPEN mode
— Save the queries to a file, eg. rec_query1.sql, then run it in SQL*Plus
—————– start ——————
set echo on
set pagesize 2000 linesize 200 trimspool on
col name form a60
col status form a10
col dbname form a15
col member form a60
col inst_id form 999
col resetlogs_time form a25
col created form a25
col DB_UNIQUE_NAME form a15
col stat form 9999999999
col thr form 99999
col “Uptime” form a80
spool rec_query1.out
show user
alter session set nls_date_format=’DD-MM-RR hh24:mi:ss’;
select inst_id, instance_name, status,
to_char(STARTUP_TIME,’dd-Mon-yyyy hh24:mi’) || ‘ – ‘ ||
trunc(SYSDATE-(STARTUP_TIME) ) || ‘ day(s), ‘ ||
trunc(24*((SYSDATE-STARTUP_TIME) – trunc(SYSDATE-STARTUP_TIME)))||’ hour(s), ‘ ||
mod(trunc(1440*((SYSDATE-STARTUP_TIME) – trunc(SYSDATE-STARTUP_TIME))), 60) ||’ minute(s), ‘ ||
mod(trunc(86400*((SYSDATE-STARTUP_TIME) – trunc(SYSDATE-STARTUP_TIME))), 60) ||’ seconds’
“Uptime”
from gv$instance
order by inst_id
/
select dbid, name dbname, open_mode, database_role,
to_char(created,’dd-Mon-YYYY hh24:mi:ss’) created,
to_char(resetlogs_time,’dd-Mon-YYYY hh24:mi:ss’) resetlogs_time
from v$database;
archive log list;
select count(*) from v$backup where status = ‘ACTIVE’;
select * from v$log;
select * from v$logfile;
select * from v$recover_file order by 1;
select distinct(status)from v$datafile;
select FILE#,TS# , status, NAME from v$datafile
where status not in (‘SYSTEM’,’ONLINE’)
order by 1;
select fhsta, count(*)
from X$KCVFH group by fhsta;
select min(fhrba_Seq), max(fhrba_Seq)
from X$KCVFH;
select hxfil FILE#,fhsta STAT,fhscn SCN,
fhthr thr, fhrba_Seq SEQUENCE,fhtnm TABLESPACE
from x$kcvfh order by 1;
7) dump the block. Run the following as SYSDBA in SQL*Plus:
SQL> alter session set max_dump_file_size=unlimited;
SQL> oradebug setmypid;
SQL> alter system dump datafile ‘full pathname for file 14’ block 312821;
SQL> oradebug tracefile_name;
==> upload the said trace file
8) run dbv against datafile 14:
$ dbv file=
spool off
—————– end ——————
RESEARCH
===============
ORA-600 [4519] “Block Corruption Detected – Cache type wrong”
We found a corrupted block when trying to read a block using
consistent read. An invalid block type was found.
Possible Block Corruption in Memory.
ORA-600 [kcbzpb_1] A block has been read cleanly from disk and updated successfully by the
clients of the cache layer.
Before the cache layer writes the block back to disk it does a health
check on the cache header.
If requested to do so (default), it generates a checksum for the block.
The health check is failing.
MEMORY CORRUPTION
ORA-600 [kcbzpb_1] was raised because DBA 59033077 => 14,312821 was found corrupted when read in the cache before we writ eit to disk.
Alert.log shows same block as corrupted, BAD HEADER, meaning blocks was overwriten.
Now DBV doesn’t show any corruption in file 14.
ACTION PLAN
====================
Hi,
I reviewed the information and the crash was caused by in memory corruption.
If restarted your database should be fine.
RESEARCH
================
Db crashed with ORA-600 [KCBZPB_1] because of corrupted block in memory:
STACK: kcbbxsv kcbbwlru kcbbdrv ksbabs ksbrdp
Bug.5866883/5845232 (36) INSTANCE GOES DOWN DUE TO ORA-600 [KCBZPB_1] V9208:
Bug.5845843/5845232 (96) DATABASE CRASH BY ORA-00600 [2032] , ORA-00600 [KCBZPB_1]
Bug:5845232: Block corruption / errors from concurrent dequeue operations
Tags: AQ CORR/PHY DUMP OERI R9208 REGRESSION SUPERCEEDED
Details:
This problem is introduced in 9.2.0.8 by the fix for bug 4144683.
Concurrent dequeue operations can lead to block corruption
/ memory corruption with varying symptoms such as ORA-600 [6033],
ORA-600 [6101] and ORA-600 [kcoapl_blkchk] if DB_BLOCK_CHECKING is enabled.
The fix for this bug is Patch 6401576.
Bug:6401576 ORA-600 [KTBAIR1] / ORA-600 [KCBZPB_1] / CORRUPTION MESSAGES –> DB CRASH
Abstract: OERI[ktbair1] / ORA-600 [6101] index corruption possible
Fixed-Releases: WIN:9208P22
Tags: CORR/IND OERI
Details:
Note: This fix replaces the fix in bug 5845232.
Certain index operations can lead to block corruption
/ memory corruption with varying symptoms such as ORA-600 [6033],
ORA-600 [6101] , ORA-600 [ktbair1] , ORA-600 [kcbzpb_1],
ORA-600 [4519] and ORA-600 [kcoapl_blkchk] if DB_BLOCK_CHECKING is enabled.
ISSUE CLARIFICATION
====================
Db crashed with ORA-600 [KCBZPB_1]
ISSUE VERIFICATION
===================
alert.log and trace file
CAUSE DETERMINATION
======================
in memory corruption
CAUSE JUSTIFICATION
====================
Bug:6401576 ORA-600 [KTBAIR1] / ORA-600 [KCBZPB_1] / CORRUPTION MESSAGES –> DB CRASH
POTENTIAL SOLUTION(S)
======================
apply patch for Bug:6401576
POTENTIAL SOLUTION JUSTIFICATION(S)
====================================
to fi x the issue
SOLUTION / ACTION PLAN
=======================
Hi,
These errors looks very similar to Bug:6401576 ORA-600 [KTBAIR1] / ORA-600 [KCBZPB_1] / CORRUPTION MESSAGES –> DB CRASH
Please download and apply one-off patch for Bug:6401576 from
Metalink->Patches->patch#=6401576 ->Platform=Hp_UX
Thanks, Rodica
关于参数log_file_name_convert
2010/05/31 by 1 Comment
Oracle文档对于该参数的描述十分容易产生歧义：converts the filename of a new log file on the primary database to the filename of a log file on the standby database，有时被误解为归档日志的文件名转换。
如在某standby备库进行以下测试：
alter system set log_file_name_convert='orcl','ZZZZZZ' scope=spfile; SQL> select fnnam,fnonm from x$kccfn; FNNAM -------------------------------------------------------------------------------- FNONM -------------------------------------------------------------------------------- /u01/oradata/ZZZZZZ/redo03.log /u01/oradata/orcl/redo03.log /u01/oradata/ZZZZZZ/redo02.log /u01/oradata/orcl/redo02.log /u01/oradata/ZZZZZZ/redo01.log /u01/oradata/orcl/redo01.log alter system set log_file_name_convert='orcl','8888888' scope=spfile; SQL> select fnnam,fnonm from x$kccfn; FNNAM -------------------------------------------------------------------------------- FNONM -------------------------------------------------------------------------------- /u01/oradata/8888888/redo03.log /u01/oradata/orcl/redo03.log /u01/oradata/8888888/redo02.log /u01/oradata/orcl/redo02.log /u01/oradata/8888888/redo01.log /u01/oradata/orcl/redo01.log
v$datafile中的大部分信息来源于x$kccfn内部视图,kccfn意为[F]ile [N]ames来源于Controlfile，其中 fnnam为经过对controlfile中文件名记录转制(由db_file_name_convert或 log_file_name_convert等参数convert)后的记录，而fnonm为控制文件中的原始文件名(或曰文件路径)。若在Data Guard配置过程中遭遇到日志文件名或数据文件名的转制问题，可以通过查询该视图进一步分析。
author: maclean
permanent link:https://www.askmac.cn/2010/05/31/%E5%85%B3%E4%BA%8E%E5%8F%82%E6%95%B0log_file_name_convert/
date:2010-05-31
All rights reserved.
ORA-00600 [kcbz_check_objd_typ_3]错误一例
2010/05/31 by 3 Comments
5月26日凌晨某客户实例警告日志中出现”ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_3], [0], [0], [1], [], [], [], []“，经过分析发现与之前CR实例发生的600错误情况症状相同。
从跟踪文件m1_m0001_4209.trc中可以看到当时的执行的SQL语句，如附件。
PL/SQL的调用堆栈为：
—– PL/SQL Call Stack —–
object line object
handle number name
3f3e89300 10 package body SYS.PRVT_HDM
3f5e9d3d8 16 SYS.WRI$_ADV_HDM_T
3f3f90898 1535 package body SYS.PRVT_ADVISOR
3f3f90898 1618 package body SYS.PRVT_ADVISOR
3f3e89300 106 package body SYS.PRVT_HDM
3eb69a3f8 1 anonymous block
函数调用堆栈为：
kgerinv kgeasnmierr kcbassertbd3 kcbz_check_objd_typ kcbzib kcbgtcr ktrget kdsgrp kdsfbr qertbFetchByRowid rwsfcd…….
以上信息与metalink Bug 4430244 中的描述完全一致，原因为Segment advisor的代码错误的将已被drop的对象数据块加载到缓存区中，导致后续的操作出现错误。
通过之前的调查已明确当前系统中已应用过Bug 4430244的补丁，认为可能是ORACLE没有解决但存在的未知BUG，或者是以前 提供的4430244补丁存在缺陷，原因如下：
1. 已确定目前所应用的小补丁没有冲突，即各小补丁所实现的功 能不受影响；
2. 当前数据库系统已经使用了与本次错误信息完全符合的bug 4430244的小补丁；
3. 当相关的小补丁都应用之后，在metalink上仍有客户提交完全相同的错误(详见bug 7032704和bug 6818725)。
4. 本次错误相关的数据块与以前(2008年7月)出现的不一致，因此不是物理存储上的损坏。
5. 本次错误与Bug 6388743 “ORA-00600 [KCBZ_CHECK_OBJD_TYP_3],[0],[0],[1],[],[],[],[] OCCURRED”中的描述完全一致，ORACLE猜测是但没有确定是BUG 4430244， 只是建议使用4430244的小补丁或10.2.0.4的补丁包， 最后此bug以客户应用10.2.0.4补丁包而被视 为结束。真实原因不明。
Metalink在之前该实例出现错误的SR提出以下解决方法：
1. 在RAC的所有实例中冲 刷buffer_cache
使用命令刷数据缓存区后，会使缓存区的数据块都标记为free，即以前缓存的数据都被移出内存。影响主要是在一段时间内使物理读增加，因 此建议在业务空闲时段操作，避免在刷缓存的同时有高负载应用在申请缓存空间。该方法可临时防止相关错误抛出。
2. 停用Segment advisor job
该job相关功能是进行 对象段(如表，索引等)的存储空间状态收集，识别 是否适合根据其可用空间大小进行收缩，并提供建议，例如是否存在较大的浪费空间，表的高水位线是否太高，是否需要进行表的回缩等。如 不需要或不关心对象段的存储空间状态，可不做相关操作。该方法可以长远解决问题的发生。
3. 升级数据库版本至10.2.0.4
author: maclean
permanent link:https://www.askmac.cn/2010/05/31/ora-00600-kcbz_check_objd_typ_3%E9%94%99%E8%AF%AF%E4%B8%80%E4%BE%8B/
date:2010-05-31
All rights reserved.
ora-4031 and "obj stat memory" component in Shared Pool
2010/05/28 by 4 Comments
My customer has an application which using a lots of “temporary” table. They create so many tables but only operate once.And they are using automatic SGA management,all this cause large memory usage in shared pool, especially the “obj stat memo”.
Recently they encountered the error 4031 , there are still 1G space available in shared pool, so i believe the root cause is too many obj stat fragment .
Verified the issue in the log file as noted below:
LOG FILE
-----------------------
Filename =gpnms4_j000_22234.trc.log
See the following error:
*** 2010-05-22 21:47:37.388
ORA-12012: error on auto execute of job 2
ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","select SYSDATE+(30 / (24*60)...","sga heap(4,0)","kglsim heap")
*** 2010-05-22 21:47:37.410
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","select sysdate+(64)/(24*60) ...","sga heap(6,0)","kglsim heap")
ORA-12012: error on auto execute of job 2
ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","select SYSDATE+(30 / (24*60)...","sga heap(4,0)","kglsim heap")
4031 specified trace as noted below:
TRACE FILE gpnms4_j000_22234.trc
------------ *** SERVICE NAME:(SYS$USERS) 2010-05-22 21:47:37.240 *** SESSION ID:(121.20137) 2010-05-22 21:47:37.240 *** 2010-05-22 21:47:37.240 ================================= Begin 4031 Diagnostic Information ================================= The following information assists Oracle in diagnosing causes of ORA-4031 errors. This trace may be disabled by setting the init.ora _4031_dump_bitvec = 0 ===================================== Allocation Request Summary Informaton ===================================== Current information setting: 04014fff SGA Heap Dump Interval=3600 seconds Dump Interval=300 seconds Last Dump Time=05/22/2010 21:47:37 Dump Count=1 Allocation request for: kglsim heap Heap: 380048e48, size: 4064 ****************************************************** HEAP DUMP heap name="sga heap(4,0)" desc=380048e48 extent sz=0xfe0 alt=216 het=32767 rec=9 flg=-125 opc=2 parent=0 owner=0 nex=0 xsz=0x1000000 latch set 4 of 6 durations enabled for this heap reserved granules for root 0 (granule size 16777216) ==================== Process State Object ==================== ---------------------------------------- SO: 5761a3750, type: 2, owner: 0, flag: INIT/-/-/0x00 (process) Oracle pid=37, calls cur/top: 56e79bdc8/572cd9160, flag: (0) - int error: 0, call error: 0, sess error: 0, txn error 0 (post info) last post received: 1359 0 4 last post received-location: kslpsr last process to post me: 57f156848 1 6 last post sent: 0 0 24 last post sent-location: ksasnd last process posted by me: 57f156848 1 6 (latch info) wait_event=0 bits=60 holding (efd=19) 5731bf3a0 Child shared pool sim alloc level=6 child#=12 Location from where latch is held: kglsim_chk_heaplist: alloc: Context saved from call: 0 state=busy, wlstate=free holding (efd=19) 5711d3de0 Child library cache level=5 child#=15 Location from where latch is held: kglobpn: child:: latch Context saved from call: 22 state=busy, wlstate=free Process Group: DEFAULT, pseudo proc: 57416ad08 O/S info: user: oracle, term: UNKNOWN, ospid: 22234 OSD pid info: Unix process pid: 22234, image: oracle@SHUDBa1 (J000) SO: 5761d48b0, type: 4, owner: 5761a3750, flag: INIT/-/-/0x00 (session) sid: 121 trans: 0, creator: 5761a3750, flag: (40000041) USR/- BSY/-/-/-/-/- DID: 0001-0025-0060BC7E, short-term DID: 0000-0000-00000000 txn branch: 0 oct: 0, prv: 0, sql: 0, psql: 0, user: 93/WUBIAO O/S info: user: oracle, term: UNKNOWN, ospid: 22234, machine: SHUDBa1 program: oracle@SHUDBa1 (J000) last wait for 'SGA: allocation forcing component growth' blocking sess=0x0 seq=2 wait_time=10349 seconds since wait started=0 =0, =0, =0 Dumping Session Wait History for 'SGA: allocation forcing component growth' count=1 wait_time=10349 =0, =0, =0 for 'db file sequential read' count=1 wait_time=89 file#=7, block#=6f767, blocks=1 temporary object counter: 0 ----- Call Stack Trace ----- ksm_4031_dump <- ksmasg <- kghnospc <- kghalo <- kglsim_chk_heaplist <- kglsim_upd_newhp <- 3076 <- kglUpdateSimulator <- kglobpn <- kglpim <- qcdlgtd <- qcsfplob <- qcsprfro <- qcsprfro_tree <- qcsprfro_tree <- qcspafq <- qcspqbDescendents <- qcspqb <- kkmdrv <- opiSem <- opiprs <- kksParseChildCursor <- rpiswu2 <- kksLoadChild <- kxsGetRuntimeLock <- kksfbc <- kkspsc0 <- kksParseCursor <- opiosq0 <- opiodr <- rpidrus <- skgmstack <- rpidru <- rpiswu2 <- rpidrv <- rpisplu <- rpispl <- kkjfnd <- kkjex1e <- kkjsexe <- kkjrdp <- opirip <- opidrv <- sou2o <- opimai_real <- main ============================== Memory Utilization of Subpool 1 ================================ Allocation Name Size _________________________ __________ "free memory " 390197664 "obj stat memo " 403349328 "KGH: NO ACCESS " 16695456 ============================== Memory Utilization of Subpool 2 ================================ Allocation Name Size _________________________ __________ "free memory " 319286368 "obj stat memo " 380580336 "KGH: NO ACCESS " 16728160 ============================== Memory Utilization of Subpool 3 ================================ Allocation Name Size _________________________ __________ "free memory " 325262592 "obj stat memo " 351782568 "KGH: NO ACCESS " 16728160 ============================== Memory Utilization of Subpool 4 ================================ Allocation Name Size _________________________ __________ "free memory " 556035112 "obj stat memo " 551594016 "KGH: NO ACCESS " 32394336 ============================== Memory Utilization of Subpool 5 ================================ Allocation Name Size _________________________ __________ "free memory " 577033336 "obj stat memo " 432672408 "KGH: NO ACCESS " 16736320 ============================== Memory Utilization of Subpool 6 ================================ Allocation Name Size _________________________ __________ "free memory " 333050648 "obj stat memo " 406731024 "KGH: NO ACCESS " 16719968 LIBRARY CACHE STATISTICS: namespace gets hit ratio pins hit ratio reloads invalids -------------- --------- --------- --------- --------- ---------- ---------- CRSR 2214572558 0.355 3977649054 0.403 81251674 34891116 TABL 69900473 0.844 438948912 0.885 11952398 0 BODY 1126452 0.970 86610514 0.999 50956 0 TRGR 104735463 0.994 156443548 0.994 369130 0 INDX 23588405 0.667 47086339 0.801 1478850 0 CLST 2746262 0.997 4303977 0.994 15628 0
At first, i think this “obj stat memory” subheap can be cleaned up by flush shared pool, but i ‘m wrong ; The component is permanent in shared pool.
SQL> select * from v$sgastat where name='obj stat memo'; POOL NAME BYTES ------------ -------------------------- ---------- shared pool obj stat memo 1454448 SQL> alter system flush shared_pool; System altered. SQL> select * from v$sgastat where name='obj stat memo'; POOL NAME BYTES ------------ -------------------------- ---------- shared pool obj stat memo 1454448
After that I read the metalink note “Bug 5573238 Shared pool memory use / ORA-4031 due to “obj stat memo” in one subpool”, oracle support advise to workaround this problem by setting STATISTICS_LEVEL=BASIC or “_object_statistics”=false. But STATISTICS_LEVEL=BASIC will disable many automatic features like ASMM, Automatic statistics gathering;If you set “_object_statistics”=false, then as _object_statistics control the population of segment stats in the v$segstat andv$segment_statistics views. So setting this to false will result in no data populating these v$views.Both the parameters will require a database restart.
And i have opened a new tar , the oracle support advise as below:
Trace file shows indeed a high size for the ‘obj stat memo’ component but also some imbalance between the subpools.
Bug 5573238 is not an issue here as fixed as from 10.2.0.4 but the workaround can be used here as well. So, setting
“statistics_level”=basic or “_object_statistics”=false should also workaround this issue.The imbalance of the subpools on the other hand might be caused by bug 6271590 which is fixed as from PSU patch
10.2.0.4.2.I would suggest to apply the latest PSU patch available right now and that is 10.2.0.4.4.
For the ‘obj stat memo’ issue, you have to choose between further investigating the issue or using the workaround by
setting “statistics_level”=basic or “_object_statistics”=false.If you want to further investigate the issue, we will need a more detailed heapdump of the ORA-4031.
To Do:
1. Apply PSU patch 10.2.0.4.4 (patch 9352164) on top of patchset 10.2.0.42. Regarding the ‘obj stat memo’ issue:
a) Workaround the issue by setting “statistics_level”=basic or “_object_statistics”=false
+
Bounce the instance-OR-
b) Further investigate the issue:
Set following parameters in the init.ora file (SPFILE/PFILE):
SQL> alter system set max_dump_file_size = unlimited scope=spfile;
SQL> alter system set events ‘4031 trace name heapdump level 536870914’ scope=spfile;Bounce the instance
Once the ORA-4031 reoccurs, provide alert+trace file
So imbalance between subpool can be fixed , and will reduce the likelihood of 4031 occurrence. But we can never flush huge memory used by “obj stat memory”,i think it’s awful .
famous summary stack trace from Oracle Version 8.1.7.4.0 Bug Note
2010/05/22 by 6 Comments
as this bug note claimed that:
PROBLEM:
——–
Customer frequently receives the following errors while rollback of a
transcation using Portal application:ORA-603: ORACLE server session terminated by fatal error
ORA-600: internal error code, arguments: [6856], [0], [0], [], [], [], [],
[]ORA-600: internal error code, arguments: [25012], [3], [15], [], [], [], [],
[]DIAGNOSTIC ANALYSIS:
——————–
Alert.log:
~~~~~~~~~~
Wed May 19 12:47:28 2004
Errors in file /opt/oracle/admin/ORTPTP/udump/ortptp_ora_6363.trc:
ORA-603: ORACLE server session terminated by fatal error
ORA-600: internal error code, arguments: [6856], [0], [0], [], [], [], [],
[]
Wed May 19 14:38:39 2004
Errors in file /opt/oracle/admin/ORTPTP/udump/ortptp_ora_782.trc:
ORA-600: internal error code, arguments: [25012], [3], [15], [], [], [], [],
[]Tablespace 3 = TEMP tablespace.
Block dump in tracefile ortptp_ora_21207.trc points to TEMP tablespace and
TEMP segment:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Block header dump: 0x00c0b917
Object id on Block? Y
seg/obj: 0xc0b916 csc: 0x00.18f4bc itc: 1 flg: O typ: 1 – DATA
fsl: 0 fnx: 0x0 ver: 0x01
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~WORKAROUND:
———–RELATED BUGS:
————-
3562030REPRODUCIBILITY:
—————-
FrequentlyTEST CASE:
———-STACK TRACE:
————
Summary Stack (to Full stack) (to Function List)
ksedmp # KSE: dump the process state
kgeriv # KGE Record Internal error code (with Va_list) (IGNORE)
kgeasi # Raise an error on an ASSERTION failure (IGNORE)
kdbmrd ? Module Notes: kdb.c – Kernel Data Block structure and
internal manipulation
kdoqmd ? Module Notes: kdo.c – Kernel Data Operations
kcoapl NAME: kcoapl – Kernel Cache Op APpLy
kcbchg1
kcbchg
ktuapundo ktuapundo – Kernel Transaction Undo APply UNdo
ktbapundo ktbapundo – Kernel Transaction Block APply UNdo
kdoiur declare local objects */
kcoubk kcoubk – Kernel Cache Op Undo callBacK — invoke undo
callback routine */
ktundo ktundo – Kernel Transaction UNDO
ktubko Get undo record to rollback transaction, non-CR only */
ktuabt ktuabt – Kernel Transaction Undo ABorT
*/
ktcrab KTC: Kernel Transaction Control Real ABort – Abort a
transaction.
ktdabt
k2labo abort session: first abort aborts tx
k2send TESTING SUPPORT:
xctrol XaCTion ROLlback: Rollback the current transaction of the
current session.
opiodr OPIODR: ORACLE code request driver – route the current
request
ttcpip TTCPIP: Two Task Common PIPe read/write
opitsk opitsk – Two Task Oracle Side Function Dispatcher
opiino opiino – ORACLE Program Interface INitialize Opi
opiodr OPIODR: ORACLE code request driver – route the current
request
opidrv # opidrv – ORACLE Program Interface DRiVer (IGNORE)
sou2o # Main Oracle executable entry point
main # Standard executable entry point
start # C program entry point (IGNORE)
**********************************************************************************************another summary:
drepprep perform the document indexing
evapls EVAluate any PLSql function
kcmclscn check Lamport SCN
kcsadj1 adjust SCN
kgesinv KGE Signal Internal (Named) error (with VA_list)
kghalo KGH: main allocation entry point
kghalp KGH: Allocate permanent memory
kghfnd KGH: Find a chunk of at least the minimum size
kghfrunp KGH: Ask client to free unpinned space
kghfrx Free extent. This is called when a heap is unpinned to request that it
kghgex KGH: Get a new extent
kghnospc KGH: There is no space available in the heap
kghpmalo KGH: Find and return a permanent chunk of space
kghxal Allocate a fixed size piece of shared memory.
kglhpd KGL HeaP Deallocate
kglobcl KGL OBject CLear all tables
kglpnal KGL PiN ALlOcate
kglpnc KGL: PiN heaps and load data pieces of a Cursor object
kglpndl KGL PiN DeLete
kglrfcl KGL ReFerence CLear
kgmexec KGM EXECute
kkmpost POST PROCESSING
kksalx ALlocate ‘size’ bytes from the eXecution-time heap
kkscls KKS: Close the cursor, user is done with it
kkspfda Multiple context area management
kkssbt KKS: set bind types
kksscl KKS: scan child list?
koklcopy KOK Lob COPY.
koklcpb2c KOK Lob CoPy Binary data (BFILE/BLOB) into Clob
kolfgdir KOL File Get DIRectory object, path and FileNames.
kpuexec KPU: Execute
kpuexecv8 KPU: Execute V8
kpurcsc KPU Remote Call with ServiceContext, Callbacks
kqdgtc return an open and parsed cursor for the given statement
kqldprr KQLD Parent Referential constraint Read
kqllod KQL: database object load
kqlsadd kqlsadd – KQLS ADD a new element to a subordinate set
kqlslod KQLS: Load all subordinate set elements for a given heap
kslcll KSL: Clean up after a given latch
kslcllt Clean up after a given latch
kslilcr invoke latch cleanup routine:
ksmapg KSM: Callback function for allocating a PGA extent, calls OSD to alloc
ksmasg Callback function for allocating an SGA extent.
kssxdl KSS: delete SO ignoring all except severe errors. cleans latches
ksucln KSUCLN: Cleanup detached process
ksudlc delete call
ksudlp KSU: delete process.called when user detaches or during cleanup by PMON
ksuxda KSUCLN: Attempt to delete all processes that are marked dead.
ksuxdl KSUCLN: Delete state object for PMON
ksuxfl KSU: Find dead processes and cleanup their latches. Called by PMON
kxfpbgpc Get Permanent Chunks
kxfpbgtc Buffer Allocation Get Chunk
kxfpnfy KXFP: NotiFY (component notifier)
kxfxse KXFX: execute
kxstcls Trace cursor closing
opicca ORACLE Program Interface: Clear Context Area
opiclo ORACLE Program Interface: CLOse cursor
opiprs ORACLE Program Interface: PaRSe
opitca OPITCA: sets up the context area
pextproc Pefm call EXTernal PROCedure
qerocStart This function creates a collection iterator row-source to iterate
qkadrv QKADRV: allocate query structures
qkajoi QKAJOI: Query Kernel Allocation: JOIn processing
qximeop QXIM Evaluate OPerand
rpicls RPI: Recursive Program Interface CLoSe
selexe SELEXE: prepare context area for fetch
xtyinpr XTY Insert Numeric PRecision operator
ORA-600 Lookup Error Categories
Applies to:
Oracle Server – Enterprise Edition – Version:
Oracle Server – Personal Edition – Version:
Oracle Server – Standard Edition – Version:
Information in this document applies to any platform.
Checked for relevance 04-Jun-2009Purpose
This note aims to provide a high level overview of the internal errors which may be encountered on the Oracle Server (sometimes referred to as the Oracle kernel). It is written to provide a guide to where a particular error may live and give some indication as to what the impact of the problem may be. Where a problem is reproducible and connected with a specific feature, you might obviously try not using the feature. If there is a consistent nature to the problem, it is good practice to ensure that the latest patchsets are in place and that you have taken reasonable measures to avoid known issues.
For repeatable issues which the ora-600 tool has not listed a likely cause , it is worth constructing a test case. Where this is possible, it greatly assists in the resolution time of any issue. It is important to remember that, in a many instances , the Server is very flexible and a workaround can very often be achieved.
Scope and Application
This bulletin provides Oracle DBAs with an overview of internal database errors.
Disclaimer: Every effort has been made to provide a reasonable degree of accuracy in what has been stated. Please consider that the details provided only serve to provide an indication of functionality and, in some cases, may not be wholly correct.ORA-600 Lookup Error Categories
In the Oracle Server source, there are two types of ora-600 error :
- the first parameter is a number which reflects the source component or layer the error is connected with; or
- the first parameter is a mnemonic which indicates the source module where the error originated. This type of internal error is now used in preference to an internal error number.
Both types of error may be possible in the Oracle server.
Internal Errors Categorised by number range
The following table provides an indication of internal error codes used in the Oracle server. Thus, if ora-600[X] is encountered, it is possible to glean some high level background information : the error in generated in the Y layer which indicates that there may be a problem with Z.
Ora-600 Base Functionality Description 1 Service Layer The service layer has within it a variety of service related components which are associated with in memory related activities in the SGA such as, for example : the management of Enqueues, System Parameters, System state objects (these objects track the use of structures in the SGA by Oracle server processes), etc.. In the main, this layer provides support to allow process communication and provides support for locking and the management of structures to support multiple user processes connecting and interacting within the SGA. Note : vos – Virtual Operating System provides features to support the functionality above. As the name suggests it provides base functionality in much the same way as is provided by an Operating System.
Ora-600 Base Functionality Description 1 vos Component notifier 100 vos Debug 300 vos Error 500 vos Lock 700 vos Memory 900 vos System Parameters 1100 vos System State object 1110 vos Generic Linked List management 1140 vos Enqueue 1180 vos Instance Locks 1200 vos User State object 1400 vos Async Msgs 1700 vos license Key 1800 vos Instance Registration 1850 vos I/O Services components 2000 Cache Layer Where errors are generated in this area, it is advisable to check whether the error is repeatable and whether the error is perhaps associated with recovery or undo type operations; where this is the case and the error is repeatable, this may suggest some kind of hardware or physical issue with a data file, control file or log file. The Cache layer is responsible for making the changes to the underlying files and well as managing the related memory structures in the SGA. Note : rcv indicates recovery. It is important to remember that the Oracle cache layer is effectively going through the same code paths as used by the recovery mechanism.
Ora-600 Base Functionality Description 2000 server/rcv Cache Op 2100 server/rcv Control File mgmt 2200 server/rcv Misc (SCN etc.) 2400 server/rcv Buffer Instance Hash Table 2600 server/rcv Redo file component 2800 server/rcv Db file 3000 server/rcv Redo Application 3200 server/cache Buffer manager 3400 server/rcv Archival & media recovery component 3600 server/rcv recovery component 3700 server/rcv Thread component 3800 server/rcv Compatibility segment It is important to consider when the error occurred and the context in which the error was generated. If the error does not reproduce, it may be an in memory issue.
4000 Transaction Layer Primarily the transaction layer is involved with maintaining structures associated with the management of transactions. As with the cache layer , problems encountered in this layer may indicate some kind of issue at a physical level. Thus it is important to try and repeat the same steps to see if the problem recurs.
Ora-600 Base Functionality Description 4000 server/txn Transaction Undo 4100 server/txn Transaction Undo 4210 server/txn Transaction Parallel 4250 server/txn Transaction List 4300 space/spcmgmt Transaction Segment 4400 txn/lcltx Transaction Control 4450 txn/lcltx distributed transaction control 4500 txn/lcltx Transaction Block 4600 space/spcmgmt Transaction Table 4800 dict/rowcache Query Row Cache 4900 space/spcmgmt Transaction Monitor 5000 space/spcmgmt Transaction Extent It is important to try and determine what the object involved in any reproducible problem is. Then use the analyze command. For more information, please refer to the analyze command as detailed in the context of Note:28814.1; in addition, it may be worth using the dbverify as discussed in Note:35512.1.
6000 Data Layer The data layer is responsible for maintaining and managing the data in the database tables and indexes. Issues in this area may indicate some kind of physical issue at the object level and therefore, it is important to try and isolate the object and then perform an anlayze on the object to validate its structure.
Ora-600 Base Functionality Description 6000 ram/data
ram/analyze
ram/indexdata, analyze command and index related activity 7000 ram/object lob related errors 8000 ram/data general data access 8110 ram/index index related 8150 ram/object general data access Again, it is important to try and determine what the object involved in any reproducible problem is. Then use the analyze command. For more information, please refer to the analyze command as detailed in the context of Note:28814.1; in addition, it may be worth using the dbverify as discussed in Note:35512.1.
12000 User/Oracle Interface & SQL Layer Components This layer governs the user interface with the Oracle server. Problems generated by this layer usually indicate : some kind of presentation or format error in the data received by the server, i.e. the client may have sent incomplete information; or there is some kind of issue which indicates that the data is received out of sequence
Ora-600 Base Functionality Description 12200 progint/kpo
progint/opilob related
errors at interface level on server side, xa , etc.12300 progint/if OCI interface to coordinating global transactions 12400 sqlexec/rowsrc table row source access 12600 space/spcmgmt operations associated with tablespace : alter / create / drop operations ; operations associated with create table / cluster 12700 sqlexec/rowsrc bad rowid 13000 dict/if dictionary access routines associated with kernel compilation 13080 ram/index kernel Index creation 13080 sqllang/integ constraint mechanism 13100 progint/opi archival and Media Recovery component 13200 dict/sqlddl alter table mechanism 13250 security/audit audit statement processing 13300 objsupp/objdata support for handling of object generation and object access 14000 dict/sqlddl sequence generation 15000 progint/kpo logon to Oracle 16000 tools/sqlldr sql loader related You should try and repeat the issue and with the use of sql trace , try and isolate where exactly the issue may be occurring within the application.
14000 System Dependent Component internal error values This layer manages interaction with the OS. Effectively it acts as the glue which allows the Oracle server to interact with the OS. The types of operation which this layer manages are indicated as follows.
Ora-600 Base Functionality Description 14000 osds File access 14100 osds Concurrency management; 14200 osds Process management; 14300 osds Exception-handler or signal handler management 14500 osds Memory allocation 15000 security/dac,
security/logon
security/ldaplocal user access validation; challenge / response activity for remote access validation; auditing operation; any activities associated with granting and revoking of privileges; validation of password with external password file 15100 dict/sqlddl this component manages operations associated with creating, compiling (altering), renaming, invalidating, and dropping procedures, functions, and packages. 15160 optim/cbo cost based optimizer layer is used to determine optimal path to the data based on statistical information available on the relevant tables and indexes. 15190 optim/cbo cost based optimizer layer. Used in the generation of a new index to determine how the index should be created. Should it be constructed from the table data or from another index. 15200 dict/shrdcurs used to in creating sharable context area associated with shared cursors 15230 dict/sqlddl manages the compilation of triggers 15260 dict/dictlkup
dict/libcachedictionary lookup and library cache access 15400 server/drv manages alter system and alter session operations 15410 progint/if manages compilation of pl/sql packages and procedures 15500 dict/dictlkup performs dictionary lookup to ensure semantics are correct 15550 sqlexec/execsvc
sqlexec/rowsrchash join execution management;
parallel row source management15600 sqlexec/pq component provides support for Parallel Query operation 15620 repl/snapshots manages the creation of snapshot or materialized views as well as related snapshot / MV operations 15640 repl/defrdrpc layer containing various functions for examining the deferred transaction queue and retrieving information 15660 jobqs/jobq manages the operation of the Job queue background processes 15670 sqlexec/pq component provides support for Parallel Query operation 15700 sqlexec/pq component provides support for Parallel Query operation; specifically mechanism for starting up and shutting down query slaves 15800 sqlexec/pq component provides support for Parallel Query operation 15810 sqlexec/pq component provides support for Parallel Query operation; specifically functions for creating mechanisms through which Query co-ordinator can communicate with PQ slaves; 15820 sqlexec/pq component provides support for Parallel Query operation 15850 sqlexec/execsvc component provides support for the execution of SQL statements 15860 sqlexec/pq component provides support for Parallel Query operation 16000 loader sql Loader direct load operation; 16150 loader this layer is used for ‘C’ level call outs to direct loader operation; 16200 dict/libcache this is part of library Cache operation. Amongst other things it manages the dependency of SQL objects and tracks who is permitted to access these objects; 16230 dict/libcache this component is responsible for managing access to remote objects as part of library Cache operation; 16300 mts/mts this component relates to MTS (Multi Threaded Server) operation 16400 dict/sqlddl this layer contains functionality which allows tables to be loaded / truncated and their definitions to be modified. This is part of dictionary operation; 16450 dict/libcache this layer layer provides support for multi-instance access to the library cache; this functionality is applicable therefore to OPS environments; 16500 dict/rowcache this layer provides support to load / cache Oracle’s dictionary in memory in the library cache; 16550 sqlexec/fixedtab this component maps data structures maintained in the Oracle code to fixed tables such that they can be queried using the SQL layer; 16600 dict/libcache this layer performs management of data structures within the library cache; 16651 dict/libcache this layer performs management of dictionary related information within library Cache; 16701 dict/libcache this layer provides library Cache support to support database creation and forms part of the bootstrap process; 17000 dict/libcache this is the main library Cache manager. This Layer maintains the in memory representation of cached sql statements together will all the necessary support that this demands; 17090 generic/vos this layer implementations error management operations: signalling errors, catching errors, recovering from errors, setting error frames, etc.; 17100 generic/vos Heap manager. The Heap manager manages the storage of internal data in an orderly and consistent manner. There can be many heaps serving various purposes; and heaps within heaps. Common examples are the SGA heap, UGA heap and the PGA heap. Within a Heap there are consistency markers which aim to ensure that the Heap is always in a consistent state. Heaps are use extensively and are in memory structures – not on disk. 17200 dict/libcache this component deals with loading remote library objects into the local library cache with information from the remote database. 17250 dict/libcache more library cache errors ; functionality for handling pipe operation associated with dbms_pipe 17270 dict/instmgmt this component manages instantiations of procedures, functions, packages, and cursors in a session. This provides a means to keep track of what has been loaded in the event of process death; 17300 generic/vos manages certain types of memory allocation structure. This functionality is an extension of the Heap manager. 17500 generic/vos relates to various I/O operations. These relate to async i/o operation, direct i/o operation and the management of writing buffers from the buffer cache by potentially a number of database writer processes; 17625 dict/libcache additional library Cache supporting functions 17990 plsql plsql ‘standard’ package related issues 18000 txn/lcltx transaction and savepoint management operations 19000 optim/cbo cost based optimizer related operations 20000 ram/index bitmap index and index related errors. 20400 ram/partnmap operations on partition related objects 20500 server/rcv server recovery related operation 21000 repl/defrdrpc,
repl/snapshot,
repl/triggerreplication related features 23000 oltp/qs AQ related errors. 24000 dict/libcache operations associated with managing stored outlines 25000 server/rcv tablespace management operations Internal Errors Categorised by mnemonic
The following table details mnemonics error stems which are possible. If you have encountered : ora-600[kkjsrj:1] for example, you should look down the Error Mnemonic column (errors in alphabetical order) until you find the matching stem. In this case, kkj indicates that something unexpected has occurred in job queue operation.
Error Mnemonic(s) Functionality Description ain ainp ram/index ain – alter index; ainp – alter index partition management operation apacb optim/rbo used by optimizer in connect by processing atb atbi atbo ctc ctci cvw dict/sqlddl alter table , create table (IOT) or cluster operations as well as create view related operations (with constraint handling functionality) dbsdrv sqllang/parse alter / create database operation ddfnet progint/distrib various distributed operations on remote dictionary delexe sqlexec/dmldrv manages the delete statement operation dix ram/index manages drop index or validate index operation dtb dict/sqlddl manages drop table operation evaa2g evah2p evaa2g dbproc/sqlfunc various functions involves in evaluating operand outcomes such as : addition , average, OR operator, bites AND , bites OR, concatenation, as well as Oracle related functions : count(), dump() , etc. The list is extensive. expcmo expgon dbproc/expreval handles expression evaluation with respect to two operands being equivalent gra security/dac manages the granting and revoking of privilege rights to a user gslcsq plsldap support for operations with an LDAP server insexe sqlexec/dmldrv handles the insert statement operation jox progint/opi functionality associated with the Java compiler and with the Java runtime environment within the Server k2c k2d progint/distrib support for database to database operation in distributed environements as well as providing, with respect to the 2-phase commit protocol, a globally unique Database id k2g k2l txn/disttx support for the 2 phase commit protocol protocol and the coordination of the various states in managing the distributed transaction k2r k2s k2sp progint/distrib k2r – user interface for managing distributed transactions and combining distributed results ; k2s – handles logging on, starting a transaction, ending a transaction and recovering a transaction; k2sp – management of savepoints in a distributed environment. k2v txn/disttx handles distributed recovery operation kad cartserv/picklercs handles OCIAnyData implementation kau ram/data manages the modification of indexes for inserts, updates and delete operations for IOTs as well as modification of indexes for IOTs kcb kcbb kcbk kcbl kcbs kcbt kcbw kcbz cache manages Oracle’s buffer cache operation as well as operations used by capabilities such as direct load, has clusters , etc. kcc kcf rcv manages and coordinates operations on the control file(s) kcit context/trigger internal trigger functionality kck rcv compatibility related checks associated with the compatible parameter kcl cache background lck process which manages locking in a RAC or parallel server multiple instance environment kco kcq kcra kcrf kcrfr kcrfw kcrp kcrr kcs kct kcv rcv various buffer cache operation such as quiesce operation , managing fast start IO target, parallel recovery operation , etc. kd ram/data support for row level dependency checking and some log miner operations kda ram/analyze manages the analyze command and collection of statistics kdbl kdc kdd ram/data support for direct load operation, cluster space management and deleting rows kdg ram/analyze gathers information about the underlying data and is used by the analyze command kdi kdibc3 kdibco kdibh kdibl kdibo kdibq kdibr kdic kdici kdii kdil kdir kdis kdiss kdit kdk ram/index support of the creation of indexes on tables an IOTs and index look up kdl kdlt ram/object lob and temporary lob management kdo ram/data operations on data such as inserting a row piece or deleting a row piece kdrp ram/analyze underlying support for operations provided by the dbms_repair package kds kdt kdu ram/data operations on data such as retrieving a row and updating existing row data kdv kdx ram/index functionality for dumping index and managing index blocks kfc kfd kfg asm support for ASM file and disk operations kfh kfp kft rcv support for writing to file header and transportable tablespace operations kgaj kgam kgan kgas kgat kgav kgaz argusdbg/argusdbg support for Java Debug Wire Protocol (JDWP) and debugging facilites kgbt kgg kgh kghs kghx kgkp vos kgbt – support for BTree operations; kgg – generic lists processing; kgh – Heap Manager : managing the internal structures withing the SGA / UGA / PGA and ensures their integrity; kghs – Heap manager with Stream support; kghx – fixed sized shared memory manager; kgkp – generic services scheduling policies kgl kgl2 kgl3 kgla kglp kglr kgls dict/libcache generic library cache operation kgm kgmt ilms support for inter language method services – or calling one language from another kgrq kgsk kgski kgsn kgss vos support for priority queue and scheduling; capabilities for Numa support; Service State object manager kgupa kgupb kgupd0 kgupf kgupg kgupi kgupl kgupm kgupp kgupt kgupx kguq2 kguu vos Service related activities activities associated with for Process monitor (PMON); spawning or creating of background processes; debugging; managing process address space; managing the background processes; etc. kgxp vos inter process communication related functions kjak kjat kjb kjbl kjbm kjbr kjcc kjcs kjctc kjcts kjcv kjdd kjdm kjdr kjdx kjfc kjfm kjfs kjfz kjg kji kjl kjm kjp kjr kjs kjt kju kjx ccl/dlm dlm related functionality ; associated with RAC or parallel server operation kjxgf kjxgg kjxgm kjxgn kjxgna kjxgr ccl/cgs provides communication & synchronisation associated with GMS or OPS related functionality as well as name service and OPS Instance Membership Recovery Facility kjxt ccl/dlm DLM request message management kjzc kjzd kjzf kjzg kjzm ccl/diag support for diagnosibility amongst OPS related services kkb dict/sqlddl support for operatoins which load/change table definitions kkbl kkbn kkbo objsupp/objddl support for tables with lobs , nested tables and varrays as well as columns with objects kkdc kkdl kkdo dict/dictlkup support for constraints, dictionary lookup and dictionary support for objects kke optim/cbo query engine cost engine; provides support functions that provide cost estimates for queries under a number of different circumstances kkfd sqlexec/pq support for performing parallel query operation kkfi optim/cbo optimizer support for matching of expressions against functional ndexes kkfr kkfs sqlexec/pq support for rowid range handling as well as for building parallel query query operations kkj jobqs/jobq job queue operation kkkd kkki dict/dbsched resource manager related support. Additionally, provides underlying functions provided by dbms_resource_manager and dbms_resource_manager_privs packages kklr dict/sqlddl provides functions used to manipulate LOGGING and/or RECOVERABLE attributes of an object (non-partitioned table or index or partitions of a partitioned table or index) kkm kkmi dict/dictlkup provides various semantic checking functions kkn ram/analyze support for the analyze command kko kkocri optim/cbo Cost based Optimizer operation : generates alternative execution plans in order to find the optimal / quickest access to the data. Also , support to determine cost and applicability of scanning a given index in trying to create or rebuild an index or a partition thereof kkpam kkpap ram/partnmap support for mapping predicate keys expressions to equivalent partitions kkpo kkpoc kkpod dict/partn support for creation and modification of partitioned objects kkqg kkqs kkqs1 kkqs2 kkqs3 kkqu kkqv kkqw optim/vwsubq query rewrite operation kks kksa kksh kksl kksm dict/shrdcurs support for managing shared cursors/ shared sql kkt dict/sqlddl support for creating, altering and dropping trigger definitions as well as handling the trigger operation kkxa repl/defrdrpc underlying support for dbms_defer_query package operations kkxb dict/sqlddl library cache interface for external tables kkxl dict/plsicds underlying support for the dbms_lob package kkxm progint/opi support for inter language method services kkxs dict/plsicds underlying support for the dbms_sys_sql package kkxt repl/trigger support for replication internal trigger operation kkxwtp progint/opi entry point into the plsql compiler kky drv support for alter system/session commands kkz kkzd kkzf kkzg kkzi kkzj kkzl kkzo kkzp kkzq kkzr kkzu kkzv repl/snapshot support for snapshots or Materialized View validation and operation kla klc klcli klx tools/sqlldr support for direct path sql loader operation kmc kmcp kmd kmm kmr mts/mts support for Multi Threaded server operation (MTS) : manange and operate the virtual circuit mechanism, handle the dispatching of massages, administer shared servers and for collecting and maintaining statistics associated with MTS knac knafh knaha knahc knahf knahs repl/apply replication apply operation associated with Oracle streams kncc repl/repcache support for replication related information stored and maintained in library cache kncd knce repl/defrdrpc replication related enqueue and dequeue of transction data as well as other queue related operations kncog repl/repcache support for loading replicaiton object group information into library cache kni repl/trigger support for replication internal trigger operation knip knip2 knipi knipl knipr knipu knipu2 knipx repl/intpkg support for replication internal package operation. kno repl/repobj support for replication objects knp knpc knpcb knpcd knpqc knps repl/defrdrpc operations assocaied with propagating transactions to a remote node and coordination of this activity. knst repl/stats replication statistics collection knt kntg kntx repl/trigger support for replication internal trigger operation koc objmgmt/objcache support for managing ADTs objects in the OOCI heap kod objmgmt/datamgr support for persistent storage for objects : for read/write objects, to manage object IDs, and to manage object concurrency and recovery. koh objmgmt/objcache object heap manager provides memory allocation services for objects koi objmgmt/objmgr support for object types koka objsupp/objdata support for reading images, inserting images, updating images, and deleting images based on object references (REFs). kokb kokb2 objsupp/objsql support for nested table objects kokc objmgmt/objcache support for pinning , unpinning and freeing objects kokd objsupp/datadrv driver on the server side for managing objects koke koke2 koki objsupp/objsql support for managing objects kokl objsupp/objdata lob access kokl2 objsupp/objsql lob DML and programmatic interface support kokl3 objsupp/objdata object temporary LOB support kokle kokm objsupp/objsql object SQL evaluation functions kokn objsupp/objname naming support for objects koko objsupp/objsup support functions to allow oci/rpi to communicate with Object Management Subsystem (OMS). kokq koks koks2 koks3 koksr objsupp/objsql query optimisation for objects , semantic checking and semantic rewrite operations kokt kokt2 kokt3 objsupp/objddl object compilation type manager koku kokv objsupp/objsql support for unparse object operators and object view support kol kolb kole kolf kolo objmgmt/objmgr support for object Lob buffering , object lob evaluation and object Language/runtime functions for Opaque types kope2 kopi2 kopo kopp2 kopu koputil kopz objmgmt/pickler 8.1 engine implementation, implementation of image ops for 8.1+ image format together with various pickler related support functions kos objsupp/objsup object Stream interfaces for images/objects kot kot2 kotg objmgmt/typemgr support for dynamic type operations to create, delete, and update types. koxs koxx objmgmt/objmgt object generic image Stream routines and miscellaneous generic object functions kpcp kpcxlt progint/kpc Kernel programmatic connection pooling and kernel programmatic common type XLT translation routines kpki progint/kpki kernel programatic interface support kpls cartserv/corecs support for string formatting operations kpn progint/kpn support for server to server communication kpoal8 kpoaq kpob kpodny kpodp kpods kpokgt kpolob kpolon kpon progint/kpo support for programmatic operations kpor progint/opi support for streaming protocol used by replication kposc progint/kpo support for scrollable cursors kpotc progint/opi oracle side support functions for setting up trusted external procedure callbacks kpotx kpov progint/kpo support for managing local and distributed transaction coordination. kpp2 kpp3 sqllang/parse kpp2 – parse routines for dimensions;
kpp3 – parse support for create/alter/drop summary statementskprb kprc progint/rpi support for executing sql efficiently on the Oracle server side as well as for copying data types during rpi operations kptsc progint/twotask callback functions provided to all streaming operation as part of replication functionality kpu kpuc kpucp progint/kpu Oracle kernel side programmatic user interface, cursor management functions and client side connection pooling support kqan kqap kqas argusdbg/argusdbg server-side notifiers and callbacks for debug operations. kql kqld kqlp dict/libcache SQL Library Cache manager – manages the sharing of sql statements in the shared pool kqr dict/rowcache row cache management. The row cache consists of a set of facilities to provide fast access to table definitions and locking capabilities. krbi krbx krby krcr krd krpi rcv Backup and recovery related operations :
krbi – dbms_backup_restore package underlying support.; krbx – proxy copy controller; krby – image copy; krcr – Recovery Controlfile Redo; krd – Recover Datafiles (Media & Standby Recovery); krpi – support for the package : dbms_pitrkrvg krvt rcv/vwr krvg – support for generation of redo associated with DDL; krvt – support for redo log miner viewer (also known as log miner) ksa ksdp ksdx kse ksfd ksfh ksfq ksfv ksi ksim ksk ksl ksm ksmd ksmg ksn ksp kspt ksq ksr kss ksst ksu ksut vos support for various kernel associated capabilities ksx sqlexec/execsvc support for query execution associated with temporary tables ksxa ksxp ksxr vos support for various kernel associated capabilities in relation to OPS or RAC operation kta space/spcmgmt support for DML locks and temporary tables associated with table access ktb ktbt ktc txn/lcltx transaction control operations at the block level : locking block, allocating space within the block , freeing up space, etc. ktec ktef ktehw ktein ktel kteop kteu space/spcmgmt support for extent management operations :
ktec – extent concurrency operations; ktef – extent format; ktehw – extent high water mark operations; ktein – extent information operations; ktel – extent support for sql loader; kteop – extent operations : add extent to segment, delete extent, resize extent, etc. kteu – redo support for operations changing segment header / extent mapktf txn/lcltx flashback support ktfb ktfd ktft ktm space/spcmgmt ktfb – support for bitmapped space manipulation of files/tablespaces; ktfd – dictionary-based extent management; ktft – support for temporary file manipulation; ktm – SMON operation ktp ktpr ktr ktri txn/lcltx ktp – support for parallel transaction operation; ktpr – support for parallel transaction recovery; ktr – kernel transaction read consistency;
ktri – support for dbms_resumable packagektsa ktsap ktsau ktsb ktscbr ktsf ktsfx ktsi ktsm ktsp ktss ktst ktsx ktt kttm space/spcmgmt support for checking and verifying space usage ktu ktuc ktur ktusm txn/lcltx internal management of undo and rollback segments kwqa kwqi kwqic kwqid kwqie kwqit kwqj kwqm kwqn kwqo kwqp kwqs kwqu kwqx oltp/qs support for advanced queuing :
kwqa – advanced queue administration; kwqi – support for AQ PL/SQL trusted callouts; kwqic – common AQ support functions; kwqid – AQ dequeue support; kwqie – AQ enqueu support ; kwqit – time management operation ; kwqj – job queue scheduler for propagation; kwqm – Multiconsumer queue IOT support; kwqn – queue notifier; kwqo – AQ support for checking instType checking options; kwqp – queueing propagation; kwqs – statistics handling; kwqu – handles lob data. ; kwqx – support for handling transformationskwrc kwre oltp/re rules engine evaluation kxcc kxcd kxcs sqllang/integ constraint processing kxdr sqlexec/dmldrv DML driver entrypoint kxfp kxfpb kxfq kxfr kxfx sqlexec/pq parallel query support kxhf kxib sqlexec/execsvc khhf- support for hash join file and memory management; kxib – index buffering operations kxs dict/instmgmt support for executing shared cursors kxti kxto kxtr dbproc/trigger support for trigger operation kxtt ram/partnmap support for temporary table operations kxwph ram/data support for managing attributes of the segment of a table / cluster / table-partition kza security/audit support for auditing operations kzar security/dac support for application auditing kzck security/crypto encryption support kzd security/dac support for dictionary access by security related functions kzec security/dbencryption support inserting and retrieving encrypted objects into and out of the database kzfa kzft security/audit support for fine grained auditing kzia security/logon identification and authentication operations kzp kzra kzrt kzs kzu kzup security/dac security related operations associated with privileges msqima msqimb sqlexec/sqlgen support for generating sql statments ncodef npi npil npixfr progint/npi support for managing remote network connection from within the server itself oba sqllang/outbufal operator buffer allocate for various types of operators : concatenate, decode, NVL, etc. the list is extensive. ocik progint/oci OCI oracle server functions opiaba opidrv opidsa opidsc opidsi opiexe opifch opiino opilng opipar opipls opirip opitsk opix progint/opi OPI Oracle server functions – these are at the top of the server stack and are called indirectly by ythe client in order to server the client request. orlr objmgmt/objmgr support for C langauge interfaces to user-defined types (UDTs) orp objmgmt/pickler oracle’s external pickler / opaque type interfaces pesblt pfri pfrsqc plsql/cox pesblt – pl/sql built in interpreter; pfri – pl/sql runtime; pfrsqc – pl/sql callbacks for array sql and dml with returning piht plsql/gen/utl support for pl/sql implementation of utl_http package pirg plsql/cli/utl_raw support for pl/sql implementation of utl_raw package pism plsql/cli/utl_smtp support for pl/sql implementation of utl_smtp package pitcb plsql/cli/utl_tcp support for pl/sql implementation of utl_tcp package piur plsql/gen/utl_url support for pl/sql implementation of utl_url package plio plsql/pkg pl/sql object instantiation plslm plsql/cox support for NCOMP processing plsm pmuc pmuo pmux objmgmt/pol support for pl/sql handling of collections prifold priold plsql/cox support to allow rpc forwarding to an older release prm sqllang/param parameter handling associated with sql layer prsa prsc prssz sqllang/parse prsa – parser for alter cluster command; prsc – parser for create database command; prssz – support for parse context to be saved psdbnd psdevn progint/dbpsd psdbnd – support for managing bind variables; psdevn – support for pl/sql debugger psdicd progint/plsicds small number of ICD to allow pl/sql to call into ‘C’ source psdmsc psdpgi progint/dbpsd psdmsc – pl/sql system dependent miscellaneous functions ; psdpgi – support for opening and closing cursors in pl/sql psf plsql/pls pl/sql service related functions for instantiating called pl/sql unit in library cache qbadrv qbaopn sqllang/qrybufal provides allocation of buffer and control structures in query execution qcdl qcdo dict/dictlkup qcdl – query compile semantic analysis; qcdo – query compile dictionary support for objects qci dict/shrdcurs support for SQL language parser and semantic analyser qcop qcpi qcpi3 qcpi4 qcpi5 sqllang/parse support for query compilation parse phase qcs qcs2 qcs3 qcsji qcso dict/dictlkup support for semantic analysis by SQL compiler qct qcto sqllang/typeconv qct – query compile type check operations; qcto – query compile type check operators qcu sqllang/parse various utilities provided for sql compilation qecdrv sqllang/qryedchk driver performing high level checks on sql language query capabilities qerae qerba qerbc qerbi qerbm qerbo qerbt qerbu qerbx qercb qercbi qerco qerdl qerep qerff qerfi qerfl qerfu qerfx qergi qergr qergs qerhc qerhj qeril qerim qerix qerjm qerjo qerle qerli qerlt qerns qeroc qeroi qerpa qerpf qerpx qerrm qerse qerso qersq qerst qertb qertq qerua qerup qerus qervw qerwn qerxt sqlexec/rowsrc row source operators :
qerae – row source (And-Equal) implementation; qerba – Bitmap Index AND row source; qerbc – bitmap index compaction row source; qerbi – bitmap index creation row source; qerbm – QERB Minus row source; qerbo – Bitmap Index OR row source; qerbt – bitmap convert row source; qerbu – Bitmap Index Unlimited-OR row source; qerbx – bitmap index access row source; qercb – row source: connect by; qercbi – support for connect by; qerco – count row source; qerdl – row source delete; qerep – explosion row source; qerff – row source fifo buffer; qerfi – first row row source; qerfl – filter row source definition; qerfu – row source: for update; qerfx – fixed table row source; qergi – granule iterator row source; qergr – group by rollup row source; qergs – group by sort row source; qerhc – row sources hash clusters; qerhj – row source Hash Join; qeril – In-list row source; qerim – Index Maintenance row source; qerix – Index row source; qerjo – row source: join; qerle – linear execution row source implementation; qerli – parallel create index; qerlt – row source populate Table; qerns – group by No Sort row source; qeroc – object collection iterator row source; qeroi – extensible indexing query component; qerpa – partition row sources; qerpf – query execution row source: prefetch; qerpx – row source: parallelizer; qerrm – remote row source; qerse – row source: set implementation; qerso – sort row source; qersq – row source for sequence number; qerst – query execution row sources: statistics; qertb – table row source; qertq – table queue row source; qerua – row source : union-All;
qerup – update row source; qerus – upsert row source ; qervw – view row source; qerwn – WINDOW row source; qerxt – external table fetch row sourceqes3t qesa qesji qesl qesmm qesmmc sqlexec/execsvc run time support for sql execution qkacon qkadrv qkajoi qkatab qke qkk qkn qkna qkne sqlexec/rwsalloc SQL query dynamic structure allocation routines qks3t sqlexec/execsvc query execution service associated with temp table transformation qksmm qksmms qksop sqllang/compsvc qksmm – memory management services for the SQL compiler; qksmms – memory management simulation services for the SQL compiler; qksop – query compilation service for operand processing qkswc sqlexec/execsvc support for temp table transformation associated for with clause. qmf xmlsupp/util support for ftp server; implements processing of ftp commands qmr qmrb qmrs xmlsupp/resolver support hierarchical resolver qms xmlsupp/data support for storage and retrieval of XOBs qmurs xmlsupp/uri support for handling URIs qmx qmxsax xmlsupp/data qmx – xml support; qmxsax – support for handling sax processing qmxtc xmlsupp/sqlsupp support for ddl and other operators related to the sql XML support qmxtgx xmlsupp support for transformation : ADT -> XML qmxtsk xmlsupp/sqlsupp XMLType support functions qsme summgmt/dict summary management expression processing qsmka qsmkz dict/dictlkup qsmka – support to analyze request in order to determine whether a summary could be created that would be useful; qsmkz – support for create/alter summary semantic analysis qsmp qsmq qsmqcsm qsmqutl summgmt/dict qsmp – summary management partition processing; qsmq – summary management dictionary access; qsmqcsm – support for create / drop / alter summary and related dimension operations; qsmqutl – support for summaries qsms summgmt/advsvr summary management advisor qxdid objsupp/objddl support for domain index ddl operations qxidm objsupp/objsql support for extensible index dml operations qxidp objsupp/objddl support for domain index ddl partition operations qxim objsupp/objsql extensible indexing support for objects qxitex qxopc qxope objsupp/objddl qxitex – support for create / drop indextype; qxope – execution time support for operator callbacks; qxope – execution time support for operator DDL qxopq qxuag qxxm objsupp/objsql qxopq – support for queries with user-defined operators; qxuag – support for user defined aggregate processing; qxxm – queries involving external tables rfmon rfra rfrdb rfrla rfrm rfrxpt drs implements 9i data guard broker monitor rnm dict/sqlddl manages rename statement operation rpi progint/rpi recursive procedure interface which handles the the environment setup where multiple recursize statements are executed from one top level statement rwoima sqlexec/rwoprnds row operand operations rwsima sqlexec/rowsrc row source implementation/retrieval according to the defining query sdbima sqlexec/sort manages and performs sort operation selexe sqlexec/dmldrv handles the operation of select statement execution skgm osds platform specific memory management rountines interfacing with O.S. allocation functions smbima sor sqlexec/sort manages and performs sort operation sqn dict/sqlddl support for parsing references to sequences srdima srsima stsima sqlexec/sort manages and performs sort operation tbsdrv space/spcmgmt operations for executing create / alter / drop tablespace and related supporting functions ttcclr ttcdrv ttcdty ttcrxh ttcx2y progint/twotask two task common layer which provides high level interaction and negotiation functions for Oracle client when communicating with the server. It also provides important function of converting client side data / data types into equivalent on the server and vice versa uixexe ujiexe updexe upsexe sqlexec/dmldrv support for : index maintenance operations, the execution of the update statement and associated actions connected with update as well as the upsert command which combines the operations of update and insert vop optim/vwsubq view optimisation related functionality xct txn/lcltx support for the management of transactions and savepoint operations xpl sqlexec/expplan support for the explain plan command xty sqllang/typeconv type checking functions zlke security/ols/intext label security error handling component
ORA-600 [kddummy_blkchk] [18038] 一例
2010/05/21 by 3 Comments
一位客户的Oracle告警日志中出现了ORA-600 [kddummy_blkchk] [18038]故障，alert中的具体信息：
Errors in file /u01/app/oracle/admin/prdw014a/udump/prdw014a_ora_4377.trc: ORA-00600: internal error code, arguments: [kddummy_blkchk], [222], [5792], [18038], [], [], [], [] Mon May 17 15:27:53 2010 Trace dumping is performing id=[cdmp_20100517152753] Mon May 17 15:27:53 2010 Doing block recovery for file 2 block 504365 Block recovery from logseq 159276, block 166357 to scn 10934615778284 Mon May 17 15:27:53 2010 Recovery of Online Redo Log: Thread 1 Group 4 Seq 159276 Reading mem 0 Mem# 0: /u01/app/oracle/dataPRDW014/redo04a_1.log Mem# 1: /u01/app/oracle/dataPRDW014/redo04a_2.log Block recovery completed at rba 159276.167277.16, scn 2545.3924010007 Doing block recovery for file 222 block 5792 Block recovery from logseq 159276, block 84741 to scn 10934615778283 Mon May 17 15:27:53 2010 Recovery of Online Redo Log: Thread 1 Group 4 Seq 159276 Reading mem 0 Mem# 0: /u01/app/oracle/dataPRDW014/redo04a_1.log Mem# 1: /u01/app/oracle/dataPRDW014/redo04a_2.log Block recovery completed at rba 159276.167277.16, scn 2545.3924009964 Mon May 17 15:27:55 2010 Block recovery completed at rba 159276.167277.16, scn 2545.3924009964 Mon May 17 15:27:55 2010 Corrupt Block Found TSN = 67, TSNAME = OBA_DATA RFN = 222, BLK = 5792, RDBA = 931141280 OBJN = 1657288, OBJD = 1699775, OBJECT = W_ORG_DS, SUBOBJECT = SEGMENT OWNER = BMS_OBA_DW, SEGMENT TYPE = Table Segment Mon May 17 15:32:56 2010 Trace dumping is performing id=[cdmp_20100517153255]
附600错误产生的trace信息：
prdw014a_ora_4377.trc
/u01/app/oracle/admin/prdw014a/udump/prdw014a_ora_4377.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining Scoring Engine
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/102prdw014
System name: SunOS
Node name: v08k405
Release: 5.9
Version: Generic_122300-29
Machine: sun4u
Instance name: prdw014a
Redo thread mounted by this instance: 1
Oracle process number: 109
Unix process pid: 4377, image: oracle@v08k405
*** 2010-05-17 15:23:15.391
*** ACTION NAME:() 2010-05-17 15:23:15.389
*** MODULE NAME:(pmdtm@v04k413 (TNS V1-V3)) 2010-05-17 15:23:15.389
*** SERVICE NAME:(prdw014_taf) 2010-05-17 15:23:15.389
*** SESSION ID:(789.48811) 2010-05-17 15:23:15.389
TYP:0 CLS: 4 AFN:222 DBA:0x378016a0 OBJ:1699775 SCN:0x09f1.e9e3a3eb SEQ: 2 OP:14.4
kteop redo - redo operation on extent map
RESIZE: entry:0 delta:
...
..
..
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kddummy_blkchk], [222], [5792], [18038], [], [], [], []
Current SQL statement for this session:
INSERT /*+ SYS_DL_CURSOR */ INTO bms_oba_dw.W_ORG_DS ("W_CUSTOMER_CLASS","NAME","ST_ADDRESS","CITY","STATE","ZIPCODE","COUNTRY","CUST_TYPE_CODE","CUST_TYPE_NAME","ACTIVE_FLG","DOM_ULT_DUNS_NUM","DUNS_NUM","EMP_COUNT","FORMED_DT","GLBLULT_DUNS_NUM","ANNUAL_REVENUE","BRANCH_FLG","BIRTH_DT","NO_OF_CHILDREN","LEGAL_NAME","FAMILY_NAME","OTHER_NAME","PREFERRED_NAME","INDV_ADDNL_TITLE","INDV_TITLE","INDV_MARITAL_STATE","INDV_GENDER","EMAIL_ADDRESS","RELATIONSHIP_STATE","INDV_EMP_STATUS","FAX_NUM","PAGER_NUM","MOBILE_NUM","LIFE_CYCLE_STATE","CUST_CAT_CODE","CUST_CAT_NAME","SIC_CODE","SIC_NAME","GOVT_ID_TYPE","GOVT_ID_VALUE","DUNNS_SITE_NAME","DUNNS_GLOBAL_NAME","DUNNS_LEGAL_NAME","CUSTOMER_NUM","ALT_CUSTOMER_NUM","ALT_PHONE_NUM","INTERNET_HOME_PAGE","LEGAL_STRUCT_CODE","LEGAL_STRUCT_NAME","DIRECT_MKTG_FLG","SOLICITATION_FLG","CUSTOMER_HIER1_CODE","CUSTOMER_HIER1_NAME","CUSTOMER_HIER2_CODE","CUSTOMER_HIER2_NAME","CUSTOMER_HIER3_CODE","CUSTOMER_HIER3_NAME","CUSTOMER_HIER4_CODE","CUSTOMER_HIER4_NAME","CUSTOMER_HIER5_CODE","CUSTOMER_HIER5_NAME","CUSTOMER_HIER6_CODE","CREATED_BY_ID","CHANGED_BY_ID","CREATED_ON_DT","CHANGED_ON_DT","AUX1_CHANGED_ON_DT","AUX2_CHANGED_ON_DT","AUX3_CHANGED_ON_DT","AUX4_CHANGED_ON_DT","SRC_EFF_FROM_DT","SRC_EFF_TO_DT","DELETE_FLG","DATASOURCE_NUM_ID","INTEGRATION_ID","TENANT_ID","X_CUSTOM","MOT_ATTRIBUTE1","MOT_ATTRIBUTE2","MOT_ATTRIBUTE3","MOT_ATTRIBUTE4","MOT_ATTRIBUTE5","MOT_ATTRIBUTE6","MOT_ATTRIBUTE7","MOT_ATTRIBUTE8","MOT_ATTRIBUTE9","MOT_ATTRIBUTE10","MOT_ATTRIBUTE11","MOT_ATTRIBUTE12","MOT_ATTRIBUTE13","MOT_ATTRIBUTE14","MOT_ATTRIBUTE15","MOT_ATTRIBUTE16","MOT_ATTRIBUTE17","MOT_ATTRIBUTE18","MOT_ATTRIBUTE19","MOT_ATTRIBUTE20","MOT_PARTY_TYPE","MOT_PHONE_AREA_CODE","MOT_ORIG_SYSTEM_REFERENCE","MOT_PER_EMAIL_ADDR","MOT_PERSON_FIRST_NAME","MOT_PHONE_EXTENSION","MOT_ALTERNATE_NAME","MOT_TELEPHONE_TYPE","MOT_SALES_CHANNEL_CODE","MOT_ACCOUNT_NAME","MOT_ATTRIBUTE_CATEGORY","MOT_INTERCOMPANY_FLAG","MOT_PARTY_NUMBER","MOT_PARTY_ID","MOT_LAST_UPDATE_LOGIN","MOT_CUST_CLASS_DESC","MOT_RECEIPT_METHOD_NAME","MOT_PHONE_NUMBER","MOT_CONTACT_POINT_PURPOSE","MOT_SALESREP_NAME","MOT_PAY_TERMS_CODE","MOT_PAY_TERMS_NAME") VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp()+744 CALL ksedst() 000000840 ?
FFFFFFFF7FFF620C ?
000000000 ?
FFFFFFFF7FFF2D00 ?
FFFFFFFF7FFF1A68 ?
FFFFFFFF7FFF2468 ?
kgerinv()+200 PTR_CALL 0000000000000000 000106800 ? 10681C1C4 ?
10681C000 ? 00010681C ?
000106800 ? 10681C1C4 ?
kseinpre()+96 CALL kgerinv() 106816B18 ? 000000000 ?
1064564C0 ? 000000003 ?
FFFFFFFF7FFF6750 ?
000001430 ?
ksesin()+52 CALL kseinpre() 000106800 ? 000000003 ?
00000025F ? 10681C1B8 ?
FFFFFFFF7FFF6750 ?
1068167D8 ?
kco_blkchk()+2568 CALL ksesin() 1064564C0 ? 000000003 ?
000106800 ? 0000000DE ?
000000000 ? 000106800 ?
kcoapl()+1284 CALL kco_blkchk() 0001900DE ? 0378016A0 ?
0000016A0 ? 00000FC00 ?
000000000 ?
FFFFFFFF7FFF89F8 ?
kcbapl()+412 CALL kcoapl() 000000002 ? 000002300 ?
000105800 ? 583DBC000 ?
106816C98 ? 00010598F ?
kcrfw_redo_gen()+16 CALL kcbapl() FFFFFFFF7FFF89B8 ?
376 583FB7870 ?
FFFFFFFF7AF3AA3C ?
B6E9FABD0 ? 000000000 ?
583DBC000 ?
kcbchg1_main()+1363 CALL kcrfw_redo_gen() 000000000 ?
2 FFFFFFFF7FFF76C8 ?
B693A9998 ? 000000000 ?
3800135A0 ?
FFFFFFFF7FFF7700 ?
kcbchg1()+1324 CALL kcbchg1_main() 000100C00 ?
FFFFFFFF7FFF7850 ?
000000000 ? 583FB7870 ?
000000000 ? 00000FFFF ?
ktuchg()+968 CALL kcbchg1() 000106819 ? 1068195B8 ?
1068195C8 ? 106819000 ?
000000000 ? 106819000 ?
ktbchg2nt()+104 CALL ktuchg() 000000002 ? 000000001 ?
FFFFFFFF7FFF8928 ?
B67A76DD8 ? 000000000 ?
000000000 ?
kteopgen()+728 CALL ktbchg2nt() FFFFFFFF7FFF89B8 ?
FFFFFFFF7FFF87C4 ?
000000000 ? 000000000 ?
FFFFFFFF7FFF8928 ?
FFFFFFFF7FFF9D98 ?
kteopresize()+2276 CALL kteopgen() FFFFFFFF7FFF89B8 ?
000000006 ? 000106800 ?
000000002 ? 10682247C ?
106816B18 ?
ktsxbmdelext1()+968 CALL kteopresize() FFFFFFFF7FFF9D98 ?
8 FFFFFFFF7FFF9E88 ?
000000004 ? 000000002 ?
000000000 ? 000000000 ?
ktsstrm_segment()+6 CALL ktsxbmdelext1() FFFFFFFF7AD33A78 ?
308 0000016A0 ? 0003FFFFF ?
FFFFFFFF7AD33A78 ?
106822000 ? 000000043 ?
ktsmg_trimf()+1208 CALL ktsstrm_segment() 000000000 ? 000000003 ?
000000001 ? 000100C00 ?
106819000 ? 000000000 ?
kdbltrmt()+1916 CALL ktsmg_trimf() 00010598F ? 0000010E2 ?
106822478 ? 000000005 ?
10682247C ? 106816B18 ?
kdblfpl()+96 CALL kdbltrmt() 000000006 ? 000000000 ?
FFFFFFFF7AD33918 ?
000000180 ? 0000010E4 ?
000000008 ?
kdblfl()+1948 CALL kdblfpl() FFFFFFFF7FFFB0AC ?
FFFFFFFF7AD33918 ?
000000000 ?
FFFFFFFF7AD33AE0 ?
FFFFFFFF7AD33A68 ?
000000000 ?
klafin()+160 CALL kdblfl() FFFFFFFF7FFFB0AC ?
FFFFFFFF7AD33918 ?
000000000 ? 000000001 ?
000000008 ? 000106800 ?
kpodpfin()+76 CALL klafin() FFFFFFFF7AF35C40 ?
1059BF2B8 ? 000000321 ?
FFFFFFFF7AD33918 ?
000000000 ? 000400000 ?
kpodpmop()+320 CALL kpodpfin() FFFFFFFF7AF35C40 ?
000106816 ? 000106800 ?
000000321 ? 000000001 ?
FFFFFFFF7AF35BC8 ?
opiodr()+1496 PTR_CALL 0000000000000000 000000301 ? 000000321 ?
进过与Oracle support确认,定位为Bug 5386204 – Block corruption / OERI[kddummy_blkchk] after direct load of ASSM segment [ID 5386204.8].
“kteop redo – redo operation on extent map” 记录是确定该Bug的一个重要依据。
该Bug的Oracle note:
Bug 5386204 Block corruption / OERI[kddummy_blkchk] after direct load of ASSM segment
This note gives a brief overview of bug 5386204.
The content was last updated on: 08-FEB-2010
Click here for details of each of the sections below.
This bug is alerted in Note:580561.1
Affects:Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions < 11
Versions confirmed as being affected* 9.2.0.8
* 10.2.0.1
* 10.2.0.2
* 10.2.0.3
* 10.2.0.4Platforms affected Generic (all / most platforms affected)
Fixed:
This issue is fixed in
* 9.2.0.8 Patch 15 on Windows Platforms
* 10.2.0.2 Patch 15 on Windows Platforms
* 10.2.0.3 Patch 5 on Windows Platforms
* 10.2.0.4.1 (Patch Set Update)
* 10.2.0.4 Patch 2 on Windows Platforms
* 10.2.0.5 (Server Patch Set)
* 11.1.0.6 (Base Release)Symptoms:
Related To:
* Internal Error May Occur (ORA-600)
* Corruption (Logical)
* ORA-600 [kddummy_blkchk]* Direct Path Operations
* ASSM Space Management (Bitmap Managed Segments)Description
Block corruption / ORA-600 [kddummy_blkchk][file#] [block#] [18038]
can occur on a segment which has been direct loaded.(The corruption shows as a PAGETABLE SEGMENT HEADER
having blocks in the “Auxillary Map” outside of the “Extent Map”
range)Note:
This bug was previously incorrectly listed as fixed in 10.2.0.4Further details on this issue can be found in Note:580561.1
ORA-600 [kddummy_blkchk][][][18038] during extent operations like TRUNCATE on ASSM tablespaces [ID 580561.1]Applies to:
Oracle Server – Enterprise Edition – Version: 9.2.0.8 to 10.2.0.4
Information in this document applies to any platform.
DescriptionThis alert describes the problem in Bug 5386204 / Note 5386204.8.
Block corruption with error ORA-600 [kddummy_blkchk] [file#] [block#] [18038]
may be reported during a DROP/TRUNCATEThe corruption shows as a PAGETABLE SEGMENT HEADER having blocks in the
“Auxillary Map” outside of the “Extent Map” range.The same operation terminated without any error in previous RDBMS versions
like Oracle9i.Likelihood of Occurrence
The object is populated by direct path operations such as SQL*Loader using DIRECT=Y for example.
The object is stored in a Locally Managed Tablespace (LMT) that is using ASSM (dba_tablespaces.segment_space_management=’AUTO’).
Bug 5386204 is mostly hit when db_block_size=16384.Possible Symptoms
One evidence of hitting this bug might be the value 18038 in the third argument of
ORA-600 [kddummy_blkchk] where [18038] is a check error code.@Error check code 18038 means that the “Data dba” stored in “Auxiliary Map” is out of range
@TYP:0 CLS: 4 AFN:234 DBA:0x3a801554 OBJ:0 SCN:0x000b.290f5e0d SEQ: 1 OP:14.2
@In this case “Data dba: 0x3a801555” stored in the “Auxiliary Map” is equal to 0x3a801551 + 4 which is out of the extent 0, hence the error.
@Note that extent 0 is 4 blocks, so extent 0 starts from 0x3a801551 to 0x3a801554.Workaround or Resolution
In order to identify objects that are affected by the corruption, use the procedure
DBMS_SPACE_ADMIN.ASSM_TABLESPACE_VERIFY@DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY is also an option but it requires patch for Bug 6760697 is needed)
How to execute DBMS_SPACE_ADMIN.ASSM_TABLESPACE_VERIFY:
alter system set DB_BLOCK_CHECKSUM = OFF;
— open a new session and run :
exec DBMS_SPACE_ADMIN.assm_tablespace_verify(‘<Tablespace Name>’, DBMS_SPACE_ADMIN.TS_VERIFY_DEEP, DBMS_SPACE_ADMIN.SEGMENT_VERIFY_DEEP);See if any trace file is generated in the directory defined by user_dump_dest.
The absence of a trace file means that no corrupt segments were found.Note: DB_BLOCK_CHECKSUM has to be disabled; otherwise the same ORA-600 error may be produced
@Oracle check block type 0x23=PAGETABLE SEGMENT HEADER even if DB_BLOCK_CHECKING is not set.
Example of output from DBMS_SPACE_ADMIN.ASSM_TABLESPACE_VERIFY
Segment header [dba: 0x003a801554, (file 234,block 5460)]
Segment object id: 7825838; inc. no.: 0
*********verifying extent map and tablespace bitmap consistency
———
Verifying extent map and auxilliary extent map consistency in the segment
Block Corruption in seg hdr / ext map block: rdba: 0x3a801554, err code: 18038Identifying the object using the segment header information.
Segment header [dba: 0x003a801554, (file 234,block 5460)]
select *
from DBA_EXTENTS
where FILE_ID = 234
and 5460 between block_id and block_id + blocks – 1;Identifying the object using the Segment object id information.
Segment object id: 7825838; inc. no.: 0
select *
from DBA_OBJECTS
where DATA_OBJECT_ID = 7825838;@How to execute DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY
WORKAROUNDs:
Disable DB_BLOCK_CHECKSUM for any action taken.
Note: DB_BLOCK_CHECKSUM has to be disabled; otherwise the same ORA-600 error may be produced
alter system set DB_BLOCK_CHECKSUM = OFF;
— open a new sessionDROP TABLE .. PURGE;
ALTER TABLE .. MOVE ..;
Create table as select (CTAS)
export/import, etcPatches
The patch prevents the corruption from taking place. Affected objects will have to be recreated.
This bug was previously incorrectly listed as fixed in 10.2.0.4.
@This problem is fixed in the 10.2.0.5 Patch Set (not available yet and still without a due date).
This problem is fixed in the 11.1.0.6 rdbms release.One off patches for this issue are available for some platforms / versions.
See Patch 5386204 for patch availability.
Modification History
03-JUN-2008 – Initial Alert version
04-JUN-2008 – Implemented correction
11-JUN-2008 – Added info about DB_BLOCK_CHECKSUM
13-JUN-2008 – PublishedReferences
BUG:5386204 – ORA-600 [KDDUMMY_BLKCHK] ERRORS WITH CODE 18038
NOTE:5386204.8 – Bug 5386204 – Block corruption / OERI[kddummy_blkchk] after direct load of ASSM segment
Bug 5386204: ORA-600 [KDDUMMY_BLKCHK] ERRORS WITH CODE 18038
Show Bug Attributes Bug Attributes
Type B – Defect Fixed in Product Version 11.1
Severity 1 – Complete Loss of Service Product Version 10.2.0.2
Status 80 – Development to Q/A Platform 226 – Linux x86-64
Created 12-Jul-2006 Platform Version 2.6.5-7.191-SMP
Updated 20-May-2010 Base Bug –
Database Version 10.2.0.2
Affects Platforms Generic
Product Source OracleShow Related Products Related Products
Line Oracle Database Products Family Oracle Database
Area Oracle Database Product 5 – Oracle Server – Enterprise EditionHdr: 5386204 10.2.0.2 RDBMS 10.2.0.2 SPACE PRODID-5 PORTID-226 ORA-600
Abstract: ORA-600 [KDDUMMY_BLKCHK] ERRORS WITH CODE 18038*** 07/12/06 12:59 am ***
TAR:
—-PROBLEM:
——–
1. Clear description of the problem encountered
Customer is getting repeated ORA-600 [kddummy_blkchk] errors reported with
internal check code 18038 on tables which have had bulk deletions made. This
has occurred on both production and test instances.2. Pertinent configuration information (MTS/OPS/distributed/etc)
RAC, ASM3. Indication of the frequency and predictability of the problem
Problem is intermittent but occurs several times a day impacting the
customers ability to work.4. Sequence of events leading to the problem
Error is typically signalled on a COMMIT most likely following a deletion
from the tables.5. Technical impact on the customer. Include persistent after effects.
Severe, as it occurs multiple times per day, and corrupt the underlying
tables preventing further data loads.DIAGNOSTIC ANALYSIS:
——————–
The trace files show that the problem occurs following a bulk deletion from
the underlying tables, which appear to corrupt the extent map, as the segment
header dump shows 1 extent of 4 blks, but the deleteion entry in the redo
stream shows one extent of 8 blks, e.g.:REDO RECORD – Thread:1 RBA: 0x0005da.000e5e34.01c0 LEN: 0x00fc VLD: 0x01
SCN: 0x000d.37eacce9 SUBSCN: 5 07/11/2006 10:29:53
CHANGE #1 TYP:0 CLS:60 AFN:39 DBA:0x09c322e0 OBJ:4294967295
SCN:0x000d.37eacce9 SEQ: 2 OP:5.1
ktudb redo: siz: 112 spc: 15940 flg: 0x0022 seq: 0x011d rec: 0x06
xid: 0x0016.020.000005b6
ktubu redo: slt: 32 rci: 5 opc: 14.5 objn: 2 objd: 93662 tsn: 12
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: Yes
0x00000000
kteopu undo – undo operation on extent map
segdba: 0x87e3cc class: 4 mapdba:0x87e3cc offset: 3
rbr extent – dba: 0x0 nbk: 0x0
kteop redo – redo operation on extent map
ADD: dba:0x803673d len:8 at offset:1
DEFAULT: ???
SETSTAT: exts:2 blks:16 lastmap:0x0 mapcnt:0
CHANGE #2 TYP:0 CLS: 4 AFN:2 DBA:0x0087e3cc OBJ:93662 SCN:0x000d.37eacce9
SEQ: 1 OP:14.4
kteop redo – redo operation on extent map
DELETE: entry:1
shift back: dba:0x0 len:0
SETSTAT: exts:1 blks:8 lastmap:0x0 mapcnt:0WORKAROUND:
———–
NoneRELATED BUGS:
————-
Bug 4949123 – ORA-600: [KDDUMMY_BLKCHK], [541], [147050], [18038]REPRODUCIBILITY:
—————-
Consistently occurring at customers site.TEST CASE:
———-
n/aSTACK TRACE:
————
ksedst ksedmp ksfdmp kgerinv kseinpre ksesin kco_blkchk kcoapl kcbapl
kcrfw_redo_gen kcbchg1_main kcbchg1 ktuchg ktbchg2nt kteopgen kteopresize
ktsxbmdelext1 ktsstrm_segment ktsmg_icmt_prepare ktcifc ktucmt ktpcmt ktcrcm
ktdcmt k2lcom k2send xctctl xctcom_with_options kksExecuteCommand opiexe
opipls opiodr rpidrus skgmstack rpidru rpiswu2 rpidrv psddr0 psdnal
pevm_EXECC pfrinstr_EXECC pfrrun_no_tool pfrrun plsql_run peicnt kkxexe
opiexe kpoal8 opiodr ttcpip opitsk opiino opiodr opidrv sou2o opimai_real
main __libc_start_main _startSUPPORTING INFORMATION:
———————–
alertlogs and trace files24 HOUR CONTACT INFORMATION FOR P1 BUGS:
—————————————-
n/aDIAL-IN INFORMATION:
——————–
n/aIMPACT DATE:
————
21-JUL-2006*** 07/12/06 02:34 am *** (CHG: Asg->NEW OWNER OWNER)
A redo dump of the segment header during the entire procedure execution was
requested on 06 Aug and supplied on 09 Aug so why are you asking for this
information again when you already have it? Please check that file
(redo_1.trc in bug5386204_07Aug.zip), and let me know if you need anything
else.
*** 09/19/06 02:39 am *** (CHG: Sta->30)
Uploaded the requested information in file bug5386204_Oct02.zip.*** 11/27/06 11:13 am ***
Here is one theory we (space group) have on this bug so far:
During direct load one of the segments does not get loaded with any data. The
segment is empty and the first extent has 8 blocks (this is 16k block size).
However it goes through the usual high water mark movement phase (even though
the hwm does not move). During the hwm movement phase, the segment is trimmed
close to 64k boundary. For ASSM segment with 16k block size, this means the
segment will be left with no data blocks after the trim- 4 blocks after the
trim would represent bitmaps and segment header only.There are two issues here:
(1) Why was ktsstrm_segment called on an empty (or unloaded) segment at first
place?
(2) Even if it was called, why is segment trimmed to 64k boundary?I’m working on the 2nd issue and will give an update soon.
*** 11/29/06 03:18 am *** (CHG: Pri->1)
*** 11/29/06 03:18 am ***
*** 11/29/06 03:25 am *** -> CLOSED
*** 11/29/06 05:31 pm ***
*** 11/30/06 10:36 pm ***
We ran into some issues (bugs) while testing the code for the diagnostic
patch. I was hoping to have it finished by today but it seems it’ll take some
more time and I’m pretty hopeful of having it ready to go by tomorrow evening
(PST). I’m really sorry for the delay.
*** 12/01/06 07:16 pm ***
*** 12/02/06 05:05 pm ***
Sorry for the delay in replying. I would expect the long regressions to be
complete by sunday afternoon PST. I should be ready to release the patch by
sunday evening if things go fine. Will keep this page updated on my progress.
*** 12/03/06 05:30 pm ***
*** 12/04/06 05:35 pm ***
It seems most of major issues with the long regressions have been taken care
of and I hope to get a clean run on the farm soon, by tomorrow end of day and
the patch should be on its way soonafter.I had a question though, that will help me in getting the patch out faster. I
wanted to know if the customer has had any diagnosibility patches installed
on their 10.2.0.2.0 release version.Another thing which I would like to mention here is that my patch modifies
only one file (ktss.c) in the RDBMS code.
*** 12/05/06 02:19 am ***
*** 12/05/06 04:42 pm ***
*** 12/05/06 05:06 pm ***
I was hoping to have all the farm regressions (and the patch) done by today
evening but it seems farm is taking a bit long to finish the regressions.
I’ll work on the patch as soon as I have the regressions done. Sorry for the
delay. I’ll provide an update on that in the next few hours.
*** 12/05/06 09:07 pm ***
My regressions are still moving very slowly through the queue on the farm.
The farm seems to be busy with 11g Beta 4 deadline round the corner. My
regressions have been on the farm for more than a day now. I’ll work on the
patch as soon as I have a clean farm run.
*** 12/06/06 06:15 pm ***
Still waiting on the clean farm runs. Fortunately, I’ve been able to get a
high priority on the farm jobs. So, I expect things to run clean soon. Will
keep things updated here.
*** 12/07/06 05:54 pm ***
Got my farm runs completed last night but got a small number of diffs. Have
been trying to isolate them and hopefully soon, everything should be clean.
Farm has been giving those diffs over and over again though those look
unrelated to my change. Currently, verifying them on my linux workstation.
*** 12/08/06 06:01 pm ***
*** 12/08/06 06:23 pm ***
Have been able to run almost all the long regressions locally and things look
clean. There’s just a couple of long regressions which I’m still running and
I should expect to be ready to go as soon as they are completed. Should be
able to start the patch building soon.
*** 12/11/06 01:07 am ***
There’s one long regression which seems to be broken. I’m currently working
on that to have it run clean. Will update as soon as I have it running clean.
*** 12/11/06 01:23 pm ***
Everything is clean now. Working on starting the patch building process.
*** 12/11/06 02:46 pm ***The customer has confirmed that following application of the suplied patch
the error no longer occurred when running the testcase, which ran through to
completion after about 8 hours. They are resetting the testcase, and will
run it again to verify this, but the initial response is that this looks to
have resolved the problem.Can you confirm if the patch would need to be rebuilt as a permananent fix,
ie. any diagnostics to be removed etc. or is it actually the full fix anyway?
*** 12/13/06 07:19 am ***
The customer has confirmed the following:1. Rerun the test for the 2nd time with patched rdbms: completed quickly and
without any problems.
2. Rollbacked the patch: the test failed as expected within 30 minutes.
3. Re-applied the patch and ran the test once again: completed ok.This appears to confirm that the patch resolves the problem so could we have
an answer to the previous update?
*** 12/13/06 07:47 pm ***
That is good news.
No additional diagnostics have been added to the patch. So, it’s not needed
to be rebuilt. I guess the supplied patch should be complete in itself.
*** 12/14/06 12:40 am ***
Thanks for the update.
该文档描述当使用直接路径方式导入数据时一定概率导致该Bug产生,譬如使用Sql loader且DIRECT=Y;
该Bug只会由存贮在本地管理方式(LTM)并自动段管理(ASSM)的对象引发， 并且当标准块大小为16k时出现概率较高(Bug 5386204 is mostly hit when db_block_size=16384.)
一般数据库都会启用db_block_checksum,该参数控制Oracle在读入块时做检验操作，[18038]是kddummy_blkchk的一种错误代码，出现该错误代码说明存储在段头中的辅助区间图中的Data dba越界, 我们举一个段头来看:
Start dump data blocks tsn: 4 file#: 4 minblk 139 maxblk 139
buffer tsn: 4 rdba: 0x0100008b (4/139)
scn: 0x0000.000f327e seq: 0x01 flg: 0x04 tail: 0x327e2301
frmt: 0x02 chkval: 0x619e type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
.......
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 9 #blocks: 72
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x0101e1f1 ext#: 8 blk#: 8 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 65
mapblk 0x00000000 offset: 8
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x0101e1f1 ext#: 8 blk#: 8 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 65
mapblk 0x00000000 offset: 8
Level 1 BMB for High HWM block: 0x0101e1e9
Level 1 BMB for Low HWM block: 0x0101e1e9
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x0100008a
Last Level 1 BMB: 0x0101e1e9
Last Level II BMB: 0x0100008a
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 9 obj#: 51806 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x01000089 length: 8
0x0101e1a1 length: 8
0x0101e1a9 length: 8
0x0101e1b9 length: 8
0x0101e1c1 length: 8
0x0101e1c9 length: 8
0x0101e1d9 length: 8
0x0101e1e1 length: 8
0x0101e1e9 length: 8
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01000089 Data dba: 0x0100008c
Extent 1 : L1 dba: 0x01000089 Data dba: 0x0101e1a1
Extent 2 : L1 dba: 0x0101e1a9 Data dba: 0x0101e1aa
Extent 3 : L1 dba: 0x0101e1a9 Data dba: 0x0101e1b9
Extent 4 : L1 dba: 0x0101e1c1 Data dba: 0x0101e1c2
Extent 5 : L1 dba: 0x0101e1c1 Data dba: 0x0101e1c9
Extent 6 : L1 dba: 0x0101e1d9 Data dba: 0x0101e1da
Extent 7 : L1 dba: 0x0101e1d9 Data dba: 0x0101e1e1
Extent 8 : L1 dba: 0x0101e1e9 Data dba: 0x0101e1ea
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x0100008a
其中辅助区间图( Auxillary Map)列出了该段每个区间(Extent)的一级位图块以及区间中实际数据开始的data block address (Data dba).譬如Extent 0 中的Data dba应在
(0x0100008A ~0x01000090)之间，否则即越界。
DROP或TRUNCATE是触发该Bug的主要操作，原因是这2个操作都需要使用到Pagetable segment header中的Auxiliary Map。
Oracle建议的WorkAround方式主要是通过MOVE TABLESPACE 来”REBUILD”这个PAGETABLE SEGMENT HEADER。
这个Case中Oracle support给出Workaround建议：
1-. Make sure the below query will return the table mentioned above:
SQL> select owner, object_name, object_type, SUBOBJECT_NAME, OBJECT_ID,
DATA_OBJECT_ID, CREATED,LAST_DDL_TIME,TIMESTAMP
from DBA_OBJECTS
where DATA_OBJECT_ID =1699775;If so continue:
SQL>alter system set DB_BLOCK_CHECKSUM = OFF;
Find all indexes for W_ORG_DS table.
SQL> select owner, index_name, index_type, table_name , table_owner from dba_indexes
Where table_owner = ‘BMS_OBA_DW’ and
Table_name = ‘W_ORG_DS’;connect as BMS_OBA_DW
SQL> desc W_ORG_DS
if this table does not have LONG column, then Alter table table_name move is like a CTAS but better since is using the same name of the object plus keeping any related object like index, etc. If it has Long column then export/truncate/import need to be use;
SQL>Alter table W_ORG_DS Move;
Then rebuild all indexes for W_ORG_DS table as per above query: .i.e.
SQL>Alter index rebuild
To avoid problem, please apply patch for bug 5386204, see note 580561.1 for further information.
Oracle文档宣称其已在10.2.0.4的第一个patch set update及10.2.0.5中修复了该Bug.
注：最早认为该Bug在10.2.0.4中就已经修复了，但后来确认“This bug was previously incorrectly listed as fixed in 10.2.0.4”。
EnterpriseDB Replication，复制Oracle数据测试(2)
2010/05/21 by Leave a Comment
介绍完了EnterpriseDB复制软件基本原理和注意事项，我们接下来进行Oracle数据复制到EntepriseDB advanced Server的实际演练。
先在Oracle实例中创建复制测试所用到的数据：
SQL> drop user source cascade; User dropped. SQL> create user source identified by source; User created. SQL> grant dba to source; Grant succeeded. SQL> grant create any trigger to source; Grant succeeded. SQL> conn source/source Connected. SQL> create table EMP 2 ( 3 EMPNO NUMBER(4) not null, 4 ENAME VARCHAR2(10), 5 JOB VARCHAR2(9), 6 MGR NUMBER(4), 7 HIREDATE DATE, 8 SAL NUMBER(7,2), 9 COMM NUMBER(7,2), 10 DEPTNO NUMBER(2) 11 ) 12 tablespace USERS; Table created. SQL> alter table EMP 2 add constraint pk_empno primary key (EMPNO); Table altered. SQL> create table DEPT 2 ( DEPTNO NUMBER(2) not null, DNAME VARCHAR2(14), LOC VARCHAR2(13) ) 3 4 5 6 7 tablespace USERS; Table created. SQL> alter table DEPT 2 add constraint PK_DEPT primary key (DEPTNO); Table altered. SQL> alter table EMP 2 add constraint fk_deptno foreign key (DEPTNO) 3 references dept (DEPTNO); Table altered. SQL>
同时创建EnterpriseDB Advanced Server中的目标数据库及用户：
edb=# create user subuser password 'subuser'; ERROR: role "subuser" already exists edb=# alter user subuser with Superuser; ALTER ROLE edb=# create database subuser tablespace users; CREATE DATABASE
EnterpriseDB复制服务需要DBA Management Server服务的相关支持，其运作方式如下图：
我们首先需要注册管理服务器，其默认端口为9000,为确保主机上的管理服务已打开可以运行以下命令：
[enterprisedb@rh2 ~]$ source edb_83.env [enterprisedb@rh2 ~]$ cd $EDBHOME [enterprisedb@rh2 edba]$ cd mgmtsvr/bin [enterprisedb@rh2 bin]$ ls attachments DBA_Management_Server.pid jboss_init_redhat.sh mgmtsvr.000 run.conf shutdown.jar twiddle.sh wrapper.log wsrunclient.sh BrowserLauncher.class deployer.sh jboss_init_suse.sh mgmtsvr.sh run.jar shutdown.sh wrapper_83 wsconsume.sh wstools.sh classpath.sh jboss_init_hpux.sh kill_wrapper.sh probe.sh run.sh twiddle.jar wrapper.conf wsprovide.sh [enterprisedb@rh2 bin]$ ./mgmtsvr.sh status DBA Management Server is running (31198). --目前服务已打开 [enterprisedb@rh2 bin]$ ./mgmtsvr.sh stop Stopping DBA Management Server... Stopped DBA Management Server. [enterprisedb@rh2 bin]$ ./mgmtsvr.sh start -- 若未打开，则start Starting DBA Management Server...
接着我们可以从桌面上的application栏打开replication console,并选择注册管理服务(register management Server):
成功注册服务后，我们需要分别在发布服务和订阅服务中配置JVM option,右键点击Publication service选择Advanced JVM options,在该窗口内Insert一条记录,如图：
其内容为-Djava.rmi.server.hostname=$IP, 其中$IP为已注册的DBA Management Server所监听的IP地址。配置完成后分别启动发布与订阅服务。
针对订阅服务也需要进行以上配置，JVM options也添加的条目为-Djava.rmi.server.hostname=$IP。
开始创建发布服务Oracle数据源：
并创建相关的发布服务：
接着创建订阅服务数据库：
创建具体的订阅服务：
上述配置均成功完成后，源端的数据定义默认已复制到订阅端，我们来验证一下：
[enterprisedb@rh2 ~]$ psql subuser subuser
Password for user subuser:
Welcome to psql 8.3.0.112, the EnterpriseDB interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with edb-psql commands
\g or terminate with semicolon to execute query
\q to quit
subuser=# desc source.emp;
Table "source.emp"
Column | Type | Modifiers
----------+-----------------------------+-----------
empno | numeric(4,0) | not null
ename | character varying(10) |
job | character varying(9) |
mgr | numeric(4,0) |
hiredate | timestamp without time zone |
sal | numeric(7,2) |
comm | numeric(7,2) |
deptno | numeric(2,0) |
Indexes:
"pk_empno" PRIMARY KEY, btree (empno)
subuser=# desc source.dept;
Table "source.dept"
Column | Type | Modifiers
--------+-----------------------+-----------
deptno | numeric(2,0) | not null
dname | character varying(14) |
loc | character varying(13) |
Indexes:
"pk_dept" PRIMARY KEY, btree (deptno)
接下来尝试在源端Oracle数据库中产生一定量的数据，并通过快照方式复制到订阅端：
SQL> insert into dept select * from scott.dept;
4 rows created.
SQL> commit;
Commit complete.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> begin
2 for i in 1..9999 loop
3 insert into emp values(i,'Maclean','DBA',1,sysdate-365,8888,50,10);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select count(*) from emp;
COUNT(*)
----------
9999
在点中订阅服务subuser中选择功能栏中的snapshot复制方式：
其复制过程中产生的日志：
Source database connectivity info…
conn =jdbc:oracle:thin:@192.168.0.115:1521:g10r21
user =source
password=******
Target database connectivity info…
conn =jdbc:edb://192.168.0.115:5444/subuser
user =subuser
password=******
Importing redwood schema SOURCE…
Table List: ‘DEPT’,’EMP’
Loading Table Data in 8 MB batches…
Disabling FK constraints & triggers on source.dept before truncate…
Truncating table DEPT before data load…
Disabling indexes on source.dept before data load…
Loading Table: DEPT …
Migrated 4 rows.
Enabling FK constraints & triggers on source.dept…
Enabling indexes on source.dept after data load…
Creating Constraint: PK_DEPT
Table Data Load Summary: Total Time(s): 1.261 Total Rows: 4
Disabling FK constraints & triggers on source.emp before truncate…
Truncating table EMP before data load…
Disabling indexes on source.emp before data load…
Loading Table: EMP …
Migrated 9999 rows.
Enabling FK constraints & triggers on source.emp…
Enabling indexes on source.emp after data load…
Creating Constraint: PK_EMPNO
Table Data Load Summary: Total Time(s): 3.782 Total Rows: 9999 Total Size(MB): 0.494140625
Performing ANALYZE on EnterpriseDB database…
Data Load Summary: Total Time (sec): 5.043 Total Rows: 10003 Total Size(MB): 0.506Schema SOURCE imported successfully.
Migration process completed successfully.
Migration logs have been saved to /s01/edba/mgmtsvr/server/default/deploy/edb-rrep-ws.war/WEB-INF/logs
******************** Migration Summary ********************
Tables: 2 out of 2
Constraints: 2 out of 2Total objects: 4
Successful count: 4
Failure count: 0*************************************************************
可以看到快照成功复制了我们需要的数据，现在我们来尝试使用同步模式(synchronize mode) ,我们先来定义一个持续性的间隔为5s的 Scheduled Task,选中订阅服务并点击功能栏中的Configure Schedule,选择Synchronize和Continuously,间隔时间选择为10s:
我们在源端Oracle数据库中修改员工工资，并观察订阅端EDB数据库中的情况：
-- source database
20:08:51 SQL> select sum(sal) from emp;
SUM(SAL)
----------
88871112
20:09:09 SQL> update emp set sal=sal*1.1 ;
9999 rows updated.
20:09:34 SQL> commit;
Commit complete.
20:09:36 SQL> select sum(sal) from emp;
SUM(SAL)
----------
97758223.2
-- EntepriseDB端
subuser=# select sum(sal) from source.emp;
sum
-------------
97758223.20
(1 row)
好了，以上我们利用EnterpriseDB Replication软件完成了一个由Oracle数据库到EDB advanced server间最简单的数据复制服务的配置。
