ORA-7445($cold_qerfxArrayMaxSize)错误

Posted on 2011/11/25 by yangtingkun

客户的数据库告警日志中出现这个错误。
详细错误信息如下：

Wed Oct 26 15:38:14 2011
Errors IN file /oracleapp/oracle10g/admin/ora10/udump/ora10_ora_8209.trc:
ORA-07445: exception encountered: core dump [$cold_qerfxArrayMaxSize()+15264] [SIGSEGV] [Address NOT mapped TO object] [0x000003058] [] []
Wed Oct 26 15:38:27 2011
Errors IN file /oracleapp/oracle10g/admin/ora10/udump/ora10_ora_8209.trc:
ORA-00081: address range [0x6000000000127430, 0x6000000000127434) IS NOT readable
ORA-07445: exception encountered: core dump [$cold_qerfxArrayMaxSize()+15264] [SIGSEGV] [Address NOT mapped TO object] [0x000003058] [] []

对应的TRACE文件内容为：

/oracleapp/oracle10g/admin/ora10/udump/ora10_ora_8209.trc
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
WITH the Partitioning, OLAP AND DATA Mining options
ORACLE_HOME = /oracleapp/oracle10g
System name:	HP-UX
Node name:	db1
Release:	B.11.31
Version:	U
Machine:	ia64
Instance name: ora10
Redo thread mounted BY this instance: 1
Oracle process NUMBER: 575
Unix process pid: 8209, image: oracleora10@db1
 *** 2011-10-26 15:38:14.196
*** SERVICE NAME:(SYS$USERS) 2011-10-26 15:38:14.169
*** SESSION ID:(309.9118) 2011-10-26 15:38:14.169
Exception signal: 11 (SIGSEGV), code: 1 (Address NOT mapped TO object), addr: 0x3058, PC: [0x4000000002c96060, $cold_qerfxArrayMaxSize()+15264]
  r1: 600000000011d1c0       r20:                8       br5:                0
  r2:            52c57       r21: ffffffffffffffdf       br6: c00000000027d420
  r3: c000000018118c90       r22: ffffffffffffffff       br7: 400000000251a3a0
  r4: 6000000000127670       r23: 58244b474c535400        ip: 4000000002c96060
  r5:                0       r24: 58244b474c535400      iipa:                0
  r6: 9fffffffffff7910       r25: c000000018118c98       cfm:              58e
  r7: 9fffffffffff5e60       r26: ffffffffffffff00        um:               1a
  r8:              139       r27: ffffffffffffff00       rsc:               1f
  r9:               28       r28:                0       bsp: 9fffffffbf800200
 r10:                0       r29:                2  bspstore: 9fffffffbf800200
 r11:                0       r30: 4000000001868418      rnat:                0
 r12: 9fffffffffff5ce0       r31: c000000000001329       ccv:                0
 r13: 9fffffffbf5dd4b0      NaTs:            20500      unat:                0
 r14:             3058       PRs:            5cd57      fpsr:    9804c8a74433f
 r15:             3058       br0: 400000000250ebd0       pfs: c000000000001329
 r16:                0       br1: c0000000000536d0        lc:                0
 r17:                0       br2:                0        ec:                0
 r18:               10       br3:                0       isr: 9fffffffbf800200
 r19: 9fffffffbf586f40       br4:                0       ifa:                0
Reason code: 0008
*** 2011-10-26 15:38:14.260
ksedmp: internal OR fatal error
ORA-07445: exception encountered: core dump [$cold_qerfxArrayMaxSize()+15264] [SIGSEGV] [Address NOT mapped TO object] [0x000003058] [] []
CURRENT SQL statement FOR this SESSION:
SELECT decode(SUM(pins),0,0,round(100*(1 - SUM(reloads)/SUM(pins)),2)) FROM sys.v_$librarycache
----- Call Stack Trace -----
calling              CALL     entry                argument VALUES IN hex      
location             TYPE     point                (? means dubious VALUE)     
-------------------- -------- -------------------- ----------------------------
ksedst()+64          CALL     _etext_f()+23058430  000000001 ? 000000001 ?
                              09017162224          
ksedmp()+1680        CALL     _etext_f()+23058430  000000001 ?
                              09017162224          C000000000000D20 ?
                                                   40000000052B0470 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ?
ssexhd()+1552        CALL     _etext_f()+23058430  000000003 ?
                              09017162224          9FFFFFFFFFFEC9A0 ?
                                                   4000000004120D30 ?
                                                   6000000000127B2C ?
                                                   0000586D5 ?
                                                   6000000000127B30 ?
                                                   000000001 ?
                                                   6000000000127450 ?
<kernel>             CALL     _etext_f()+23058430  9FFFFFFFFFFECFA8 ?
                              09017162224          9FFFFFFFFFFECF98 ?
                                                   40000000011BD908 ?
                                                   000000007 ?
$cold_qerfxArrayMax  CALL     _etext_f()+23058430  9FFFFFFFFFFF0E00 ?
SIZE()+15264                  09017162224          10000000B ?
                                                   9FFFFFFFFFFF0C10 ?
qerfxStart()+368     CALL     _etext_f()+23058430  400000000184FD42 ?
                              09017162224          000000007 ? 000000007 ?
                                                   600000000011D1C0 ?
                                                   400000000251A510 ?
qergsStart()+1376    CALL     _etext_f()+23058430  400000000184FD78 ?
                              09017162224          000000010 ? 000000230 ?
                                                   000000001 ? 000000140 ?
                                                   000000011 ?
                                                   400000000184FD78 ?
                                                   000000007 ?
selexe()+1792        CALL     0000000000000007     C0000003B6B69118 ?
                                                   000000001 ?
                                                   600000000011D1C0 ?
opiexe()+8320        CALL     0000000000000001     C0000003B650EB10 ?
                                                   9FFFFFFFFFFF5F80 ?
                                                   000004658 ?
                                                   600000000011D1C0 ?
                                                   9FFFFFFFFFFF5F96 ?
                                                   9FFFFFFFFFFF5F8A ?
                                                   9FFFFFFFFFFF5F94 ?
                                                   9FFFFFFFFFFF5F90 ?
kpoal8()+3600        CALL     _etext_f()+23058430  000000180 ?
                              09016042352          9FFFFFFFBF590F0C ?
                                                   9FFFFFFFFFFF5FC0 ?
                                                   9FFFFFFFFFFF5D30 ?
                                                   600000000011D1C0 ?
                                                   9FFFFFFFFFFF5D3C ?
                                                   9FFFFFFFBF590E00 ?
                                                   9FFFFFFFBF590E10 ?
opiodr()+2064        CALL     _etext_f()+23058430  9FFFFFFFFFFF80B0 ?
                              09016090224          400000000304FDD0 ?
                                                   000000000 ?
                                                   9FFFFFFFFFFF79F0 ?
                                                   600000000011D1C0 ?
                                                   C000000000001836 ?
ttcpip()+1824        CALL     __text_start_f()+22  6000000000129A70 ?
                              768464               6000000000015DD0 ?
                                                   9FFFFFFFFFFFA790 ?
                                                   6000000000015DD0 ?
                                                   9FFFFFFFFFFF80C0 ?
                                                   600000000011D1C0 ?
                                                   000000017 ?
                                                   6000000000021838 ?
opitsk()+2224        CALL     0000000000000017     6000000000021830 ?
                                                   000000000 ?
                                                   9FFFFFFFFFFFA790 ?
                                                   000000001 ?
                                                   9FFFFFFFFFFFA900 ?
                                                   9FFFFFFFFFFFA6F4 ?
                                                   4000000001EA0780 ?
                                                   9FFFFFFFFFFFA6E8 ?
opiino()+1920        CALL     _etext_f()+23058430  000000000 ? 000000000 ?
                              09016090072          600000000011D1C0 ?
                                                   40000000023A45D0 ?
                                                   000008001 ?
                                                   9FFFFFFFFFFFA6E4 ?
opiodr()+2064        CALL     _etext_f()+23058430  00000003C ?
                              09016090072          9FFFFFFFFFFFF0D0 ?
                                                   9FFFFFFFFFFFF0C0 ?
                                                   9FFFFFFFFFFFBE00 ?
                                                   000000084 ?
                                                   600000000010EC20 ?
opidrv()+1104        CALL     __text_start_f()+22  6000000000129A70 ?
                              767104               6000000000015DD0 ?
                                                   9FFFFFFFFFFFF0C0 ?
                                                   6000000000015DD0 ?
                                                   9FFFFFFFFFFFC950 ?
                                                   600000000011D1C0 ?
sou2o()+240          CALL     _etext_f()+23058430  00000003C ? 000000004 ?
                              09017120608          9FFFFFFFFFFFF0C0 ?
opimai_real()+240    CALL     _etext_f()+23058430  9FFFFFFFFFFFF0E0 ?
                              09017120608          00000003C ? 000000004 ?
                                                   9FFFFFFFFFFFF0C0 ?
main()+352           CALL     _etext_f()+23058430  000000000 ?
                              09017120608          9FFFFFFFFFFFF110 ?
main_opd_entry()+80  CALL     _etext_f()+23058430  000000002 ?
                              09017120608          9FFFFFFFFFFFF5C0 ?
                                                   C000000000033910 ?
                                                   000000000 ?
--------------------- Binary Stack Dump ---------------------

查询MOS发现，这是HP UNIX上的一个bug，ORA-07445[$cold_qerfxArrayMaxSize()] running 10.2 on HP-UX [ID 1339184.1]，在HP UNIX上的10.2版本可能碰到这个问题，当前数据库的版本是10.2.0.2 for HP unix，满足错误发生条件。
这个bug是指定平台上的问题，所以大的补丁集中可能并不会包含这个问题的解决，因此可以通过单独的补丁patch: 5442780来解决这个问题。

Posted in BUG | Tagged , , | Leave a comment

告警日志出现kewastUnPackStats信息

Posted on 2011/11/24 by yangtingkun

这个错误信息发现在客户的11.2.0.1数据库告警日志中。
告警信息如下：

Fri Jul 22 14:00:51 2011
kewastUnPackStats(): bad magic 1 (0x2afd1cbafec6, 0)
kewastUnPackStats(): bad magic 1 (0x2afd1cbafec6, 0)
kewastUnPackStats(): bad magic 1 (0x2afd1cbafec6, 0)
kewastUnPackStats(): bad magic 1 (0x2afd1cbafec6, 0)
kewastUnPackStats(): bad magic 1 (0x2afd1cbafec6, 0)
kewastUnPackStats(): bad magic 1 (0x2afd1cbafec6, 0)
kewastUnPackStats(): bad magic 1 (0x2afd1cbafec6, 0)
kewastUnPackStats(): bad magic 1 (0x2afd1cbafec6, 0)
kewastUnPackStats(): bad magic 1 (0x2afd1cbafec6, 0)
kewastUnPackStats(): bad magic 1 (0x2afd1cbafec6, 0)
kewastUnPackStats(): bad magic 1 (0x2afd1cbafec6, 0)
kewastUnPackStats(): bad magic 1 (0x2afd1cbafec6, 0)
kewastUnPackStats(): bad magic 1 (0x2afd1cbafec6, 0)
kewastUnPackStats(): bad magic 1 (0x2afd1cbafec6, 0)
kewastUnPackStats(): bad magic 1 (0x2afd1cbafec6, 0)
kewastUnPackStats(): bad magic 1 (0x2afd1cbafec6, 0)
kewastUnPackStats(): bad magic 1 (0x2afd1cbafd98, 0)
kewastUnPackStats(): bad magic 1 (0x2afd1cbafd98, 0)

查询了一下MOS，发现是11.2.0.1上的bug，在访问V$ACTIVE_SESSION_HISTORY视图时，由于NULL字符导致错误的ASH数据，详细的bug描述可以参考Bug 8730312 – wrong Null ASH data may cause dumps and kew* messages in alert.log [ID 8730312.8]。
Oracle在11.2.0.2已经FIXED这个问题。对于11.2的环境，建议还是直接安装11.2.0.2，或者打到最新的11.2.0.3。

Posted in BUG | Tagged , , | Leave a comment

单一会话引发的死锁

Posted on 2011/11/23 by yangtingkun

客户环境中出现了ORA-60死锁错误，检查日志发现，持有锁和等待锁的是同一个会话。
一般来说构成死锁至少需要两个会话，而当前的问题是一个会话引发的：

Wed Nov 23 10:19:46 2011
ORA-00060: Deadlock detected. More info IN file /oracle/admin/db1/udump/db1_ora_3408686.trc.

对应的详细信息：

*** 2011-10-29 10:11:28.970
*** SERVICE NAME:(db1) 2011-10-29 10:11:28.960
*** SESSION ID:(5562.45) 2011-10-29 10:11:28.960
DEADLOCK DETECTED ( ORA-00060 )
[TRANSACTION Deadlock]
The following deadlock IS NOT an ORACLE error. It IS a
deadlock due TO USER error IN the design OF an application
OR FROM issuing incorrect ad-hoc SQL. The following
information may aid IN determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process SESSION holds waits  process SESSION holds waits
TX-000c0016-000499ad        16    5562     X             16    5562           X
SESSION 5562: DID 0001-0010-00000092	SESSION 5562: DID 0001-0010-00000092
ROWS waited ON:
SESSION 5562: obj - rowid = 00009050 - AAAJBQAAWAAArQ6AAG
  (dictionary objn - 36944, file - 22, block - 177210, slot - 6)
Information ON the OTHER waiting sessions:
END OF information ON OTHER waiting sessions.

可以看到，等待的和持有锁的是同一个会话。
根据trace信息记录的对象，发现问题是自治事务导致的。
在主事务中如果更新了部分记录，这是启动自治事务更新同样的记录，就会造成死锁，下面通过一个简单的例子模拟了这个错误的产生：

SQL> CREATE TABLE t (id NUMBER, name varchar2(30));
TABLE created.
SQL> INSERT INTO t SELECT rownum, tname FROM tab;
4 ROWS created.
SQL> commit;
Commit complete.
SQL> CREATE OR REPLACE PROCEDURE p_test AS
2 pragma autonomous_transaction;
3 BEGIN
4 UPDATE t SET name = name WHERE id = 1;
5 commit;
6 END;
7 /
PROCEDURE created.
SQL> BEGIN
2 UPDATE t SET name = name WHERE id = 1;
3 p_test;
4 END;
5 /
BEGIN
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting FOR resource
ORA-06512: at "TEST.P_TEST", line 4
ORA-06512: at line 3

在使用自治事务的时候要避免当前事务锁定的记录和自治事务中锁定的记录相互冲突。

Posted in ORACLE | Tagged , | Leave a comment

ORA-600(kokegPinLob1)错误

Posted on 2011/11/22 by yangtingkun

客户10.2.0.5 Oracle for AIX系统中，出现了这个ORA-600错误。
其中错误信息为：

Fri Nov 18 19:50:18 GMT+08:00 2011Errors IN file /oracle/admin/ccicwmix/udump/ccicwmix_ora_4915530.trc:
ORA-00600: internal error code, arguments: [kokegPinLob1], [], [], [], [], [], [], []

对应的详细TRACE文件：

*** ACTION NAME:() 2011-11-18 19:50:18.033
*** MODULE NAME:(JDBC Thin Client) 2011-11-18 19:50:18.033
*** SERVICE NAME:(SYS$USERS) 2011-11-18 19:50:18.033
*** SESSION ID:(859.5) 2011-11-18 19:50:18.033
*** 2011-11-18 19:50:18.033
ksedmp: internal OR fatal error
ORA-00600: internal error code, arguments: [kokegPinLob1], [], [], [], [], [], [], []
CURRENT SQL statement FOR this SESSION:
SELECT * FROM (  SELECT task.jobid || '-' || task.stepid || '-' || task.taskid ID, task.code WBSNO, (SELECT wmsys.wm_concat(chr(10) || '(' || to_char(logdate,'yyyy-mm-dd') || ')[' || to_char(ACTUALHOURS,'fm990.099') || 'H]' || chr(13) || substr(remark,0,200)) ll.jobid=task.jobid AND ll.stepid=task.stepid AND ll.taskid=task.taskid AND logdate BETWEEN to_date(:1, 'yyyy-MM-dd') AND to_date(:2, 'yyyy-MM-dd')) detaildesc, ……)ORDER BY wbsno
----- Call Stack Trace -----
calling              CALL     entry                argument VALUES IN hex      
location             TYPE     point                (? means dubious VALUE)     
-------------------- -------- -------------------- ----------------------------
ksedst+001c          bl       ksedst1              FFFFFFFFFFF7EA0 ? 104CCE8A4 ?
ksedmp+0290          bl       ksedst               104C251D0 ?
ksfdmp+02d8          bl       03F397B4             
kgerinv+00dc         bl       _ptrgl               
kgesinv+0020         bl       kgerinv              FFFFFFFFFFF8BD0 ? 000000000 ?
                                                   FFFFFFFFFFF8A40 ?
                                                   44A420288A5DC2D8 ?
                                                   102A34300 ?
ksesin+006c          bl       kgesinv              00000035B ? 50000000540A0 ?
                                                   700000188E6FAB0 ? 1108EFE30 ?
                                                   FFFFFFFFFFF8AD0 ?
kokegPinLob+00dc     bl       ksesin               104EE00D8 ? 000000000 ?
                                                   70000017FC8F488 ? 6BEC7DDB1 ?
                                                   000000000 ? 0F93A79B9 ?
                                                   09E377EB9 ?
                                                   FFFFFFFFFFFFFFFA ?
kokegCollectGarbage  bl       kokegPinLob          1104208E0 ? 000000011 ?
FromScalar+004c                                    000000009 ?
kokegGarbageCollect  bl       kokegCollectGarbage  FFFFFFFFFFF8C90 ? 000000000 ?
Rworo+008c                    FromScalar           35B00050000 ? 500018C54E1F0 ?
rworupo+0648         bl       kokegGarbageCollect  201000300000000 ?
                              Rworo                FFFFFFFF00001FE8 ?
qersoFetch+0e18      bl       03F38D08             
kpofrws+019c         bl       _ptrgl               
opifch2+13a4         bl       01FC83C0             
opifch+003c          bl       opifch2              1100D4610 ? 000000000 ?
                                                   FFFFFFFFFFF9BC0 ?
opiodr+0b2c          bl       _ptrgl               
ttcpip+1020          bl       _ptrgl               
opitsk+117c          bl       01FC7F78             
opiino+09d0          bl       opitsk               0FFFFD670 ? 000000000 ?
opiodr+0b2c          bl       _ptrgl               
opidrv+04a4          bl       opiodr               3C102A9A18 ? 404C780A0 ?
                                                   FFFFFFFFFFFF630 ? 0102A9A10 ?
sou2o+0090           bl       opidrv               3C02A2D6DC ? 4A0071248 ?
                                                   FFFFFFFFFFFF630 ?
opimai_real+01bc     bl       01FC4434             
main+0098            bl       opimai_real          000000000 ? 000000000 ?
__start+0070         bl       main                 000000000 ? 000000000 ?
--------------------- Binary Stack Dump ---------------------

根据报错的查询语句，detaildesc是一个子查询作为列出现在SELECT列表中。和应用程序的开发人员确认detaildesc这个列的类型是CLOB，而这个查询的聚集函数是wmsys.wm_concat。根据MOS上查询的结果，问题指向Bug 9824435 ORA-600 [kokegPinLob1] from aggregate returning a LOB。
确认受影响的版本包括10.2.0.5和11.2.0.2，这个bug在补丁集11.2.0.3和12.1中被解决。显然不可能通过补丁集来解决这个问题。可以在10.2.0.5上直接应用Patch 9824435。

Posted in BUG | Tagged , , , | Leave a comment

ORA-600(kgscLogOff-notempty)错误

Posted on 2011/11/21 by yangtingkun

客户的10.2.0.2环境在告警日志中出现这个错误。
错误信息为：

Thu Oct 27 21:18:03 2011
Errors IN file /oracleapp/oracle10g/admin/ora10/udump/ora10_ora_27896.trc:
ORA-00600: internal error code, arguments: [kgscLogOff-notempty], [1], [], [], [], [], [], []
Thu Oct 27 21:18:05 2011
Errors IN file /oracleapp/oracle10g/admin/ora10/udump/ora10_ora_27896.trc:
ORA-00600: internal error code, arguments: [kgscLogOff-notempty], [1], [], [], [], [], [], []
ORA-00081: address range [0x6000000000127430, 0x6000000000127434) IS NOT readable
ORA-00600: internal error code, arguments: [kgscLogOff-notempty], [1], [], [], [], [], [], []

查询MOS发现这个bug是在会话LOG OFF的时候报错。其实从错误信息中也可以看到这一点。

*** SERVICE NAME:(SYS$USERS) 2011-10-27 21:18:03.874
*** SESSION ID:(488.65460) 2011-10-27 21:18:03.874
*** 2011-10-27 21:18:03.874
ksedmp: internal OR fatal error
ORA-00600: internal error code, arguments: [kgscLogOff-notempty], [1], [], [], [], [], [], []
----- Call Stack Trace -----
calling              CALL     entry                argument VALUES IN hex      
location             TYPE     point                (? means dubious VALUE)     
-------------------- -------- -------------------- ----------------------------
ksedst()+64          CALL     _etext_f()+23058430  000000000 ? 000000001 ?
                              09017162224          
ksedmp()+1680        CALL     _etext_f()+23058430  000000000 ?
                              09017162224          C000000000000D20 ?
                                                   40000000052B0470 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ?
ksfdmp()+48          CALL     _etext_f()+23058430  000000003 ?
                              09017162224          
kgerinv()+400        CALL     _etext_f()+23058430  400000000944F6F0 ?
                              09017162224          000000003 ?
                                                   C000000000000612 ?
                                                   000008F07 ? 000000000 ?
                                                   000000000 ?
kgeasnmierr()+144    CALL     _etext_f()+23058430  6000000000015C50 ?
                              09017162224          6000000000016D08 ?
                                                   6000000000014240 ?
                                                   600000000011C078 ?
                                                   6000000000017070 ?
$cold_kgscLogOff()+  CALL     _etext_f()+23058430  6000000000015C50 ?
144                           09017162224          6000000000268350 ?
                                                   6000000000268360 ?
                                                   6000000000017080 ?
                                                   000000000 ? 000000001 ?
kkslof()+320         CALL     _etext_f()+23058430  6000000000015C50 ?
                              09017162224          
opifcs()+592         CALL     _etext_f()+23058430  C0000001E8158788 ?
                              09017162224          C000000000000E21 ?
                                                   4000000002DB9C40 ?
                                                   000000000 ? 000000000 ?
ksuxds()+1504        CALL     _etext_f()+23058430  C0000001E8158788 ?
                              09017162224          4000000002E57DE0 ?
                                                   000008F9F ?
                                                   9FFFFFFFBF56BFC4 ?
                                                   9FFFFFFFBF56BFC6 ?
                                                   C0000001E8158788 ?
                                                   00000003F ?
                                                   9FFFFFFFBF56BFBC ?
ksudel()+128         CALL     0000000000000006     C0000001E8159B78 ?
                                                   60000000001274D4 ?
                                                   9FFFFFFFFFFF6A90 ?
                                                   600000000011D1C0 ?
opilof()+2624        CALL     0000000000000006     C0000001E8158788 ?
                                                   6000000000127678 ?
                                                   4000000003D0C7E0 ?
                                                   C0000000000011A9 ?
                                                   00000810D ?
                                                   60000000001274D4 ?
opiodr()+2064        CALL     <kernel>             9FFFFFFFFFFF80B0 ?
                                                   400000000304FDD0 ?
                                                   00000820F ?
                                                   9FFFFFFFFFFF7020 ?
                                                   600000000011D1C0 ?
                                                   C000000000001836 ?
Cannot find symbol IN .
Cannot find symbol IN .
ttcpip()+1824        CALL     __text_start_f()+22  6000000000129A70 ?
                              765064               6000000000015DD0 ?
                                                   9FFFFFFFFFFFA790 ?
                                                   6000000000015DD0 ?
                                                   9FFFFFFFFFFF80C0 ?
                                                   600000000011D1C0 ?
                                                   000000000 ?
                                                   6000000000021838 ?
opitsk()+2224        CALL     0000000000000000     6000000000021830 ?
                                                   000000000 ?
                                                   9FFFFFFFFFFFA790 ?
                                                   000000000 ?
                                                   9FFFFFFFFFFFA900 ?
                                                   9FFFFFFFFFFFA6F4 ?
                                                   4000000001EA0780 ?
                                                   9FFFFFFFFFFFA6E8 ?
opiino()+1920        CALL     _etext_f()+23058430  000000000 ? 000000000 ?
                              09016090072          600000000011D1C0 ?
                                                   40000000023A45D0 ?
                                                   000008001 ?
                                                   9FFFFFFFFFFFA6E4 ?
opiodr()+2064        CALL     _etext_f()+23058430  00000003C ?
                              09016090072          9FFFFFFFFFFFF0D0 ?
                                                   9FFFFFFFFFFFF0C0 ?
                                                   9FFFFFFFFFFFBE00 ?
                                                   000000084 ?
                                                   600000000010EC20 ?
opidrv()+1104        CALL     __text_start_f()+22  6000000000129A70 ?
                              767104               6000000000015DD0 ?
                                                   9FFFFFFFFFFFF0C0 ?
                                                   6000000000015DD0 ?
                                                   9FFFFFFFFFFFC950 ?
                                                   600000000011D1C0 ?
sou2o()+240          CALL     _etext_f()+23058430  00000003C ? 000000004 ?
                              09017120608          9FFFFFFFFFFFF0C0 ?
opimai_real()+240    CALL     _etext_f()+23058430  9FFFFFFFFFFFF0E0 ?
                              09017120608          00000003C ? 000000004 ?
                                                   9FFFFFFFFFFFF0C0 ?
main()+352           CALL     _etext_f()+23058430  000000000 ?
                              09017120608          9FFFFFFFFFFFF110 ?
main_opd_entry()+80  CALL     _etext_f()+23058430  000000002 ?
                              09017120608          9FFFFFFFFFFFF5C0 ?
                                                   C000000000033910 ?
                                                   000000000 ?
--------------------- Binary Stack Dump ---------------------

MOS中没有记录进一步的信息，不过根据后面的ORA-81错误，可以判断，应该是会话进行退出登录的清理动作时，发现了内存中有部分地址不可读，造成了这个错误的产生。
检查trace还可以发现下面的信息：

Memory dump OF process state object:
Dump OF memory FROM 0xC0000001E804BEE8 TO 0xC0000001E804C6D8
C0000001E804BEE0                   ******** ********          [********]
C0000001E804BEF0 ******** ******** ******** ********  [****************]
        Repeat 125 times
C0000001E804C6D0 ******** ********                    [********]        
Symbolic dump OF process state object:
kqfdumpvar: address 0xC0000001E804BEE8 cannot be dumped AS TYPE 'ksupr' (8 bytes are unreadable)
KSFD PGA DUMPS 
NUMBER OF completed I/O requests=0 flags=0
END OF PROCESS STATE

显然在处理PGA的时候，处理8个字节的地址不可读，导致了这个ORA-600错误。根据MOS中的记录，由于错误发生在LOG OFF的时候，可以简单的忽略这个问题，并不会对系统造成影响，而这个错误在10.2.0.4和11.1.0.6中被fixed。

Posted in BUG | Tagged , , , | 1 Comment

ORA-7445(kgegec)错误

Posted on 2011/11/20 by yangtingkun

客户数据库出现大量的ORA-7445错误。
这是一个11.1.0.6 for Windows 64bit的环境，在告警日志中包含了大量的ORA-7445错误：

Thu Nov 10 00:00:43 2011
Exception [TYPE: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x75A20BE, kgegec()+76]
Thu Nov 10 00:00:43 2011
Exception [TYPE: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x75A20BE, kgegec()+76]
Thu Nov 10 00:00:43 2011
Errors IN file d:\app\administrator\diag\rdbms\gvdb\gvdb\cdump\gvdbcore.log
ORA-07445: caught exception [ACCESS_VIOLATION] at [kgegec()+76] [0x00000000075A20BE]
Thu Nov 10 00:00:43 2011
Errors IN file d:\app\administrator\diag\rdbms\gvdb\gvdb\cdump\gvdbcore.log
ORA-07445: caught exception [ACCESS_VIOLATION] at [kgegec()+76] [0x00000000075A20BE]
Thu Nov 10 00:01:03 2011
Exception [TYPE: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x75A20BE, kgegec()+76]
Thu Nov 10 00:01:03 2011
Exception [TYPE: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x75A20BE, kgegec()+76]
Thu Nov 10 00:01:03 2011
Errors IN file d:\app\administrator\diag\rdbms\gvdb\gvdb\cdump\gvdbcore.log
ORA-07445: caught exception [ACCESS_VIOLATION] at [kgegec()+76] [0x00000000075A20BE]
Thu Nov 10 00:01:03 2011
Errors IN file d:\app\administrator\diag\rdbms\gvdb\gvdb\cdump\gvdbcore.log
ORA-07445: caught exception [ACCESS_VIOLATION] at [kgegec()+76] [0x00000000075A20BE]

检查MOS发现这是11.1.0.6上的一个bug：ORA-07445 [Kgegec()] [ID 566690.1]，而导致这个问题的产生的原因是SYSTEM或SYSMAN用户后台运行一个JOB：EXECUTE_EM_DBMS_JOB_PROCS。
如果要避免这个ORA-7445错误的产生，只需要通过DBMS_JOB包，禁止这个JOB定时启动。

Posted in BUG | Tagged , , | Leave a comment

DBCA启动报错Java.Lang.Noclassdeffounderror

Posted on 2011/11/19 by yangtingkun

一个9204的数据库，在启动DBCA是出现NoClassdeffounderror错误。
尝试启动DBCA图形界面，DBCA没有启动，而是出现了Java.Lang.Noclassdeffounderror错误信息。
检查了ORACLE_HOME、PATH以及LD_LIBRARY_PATH等环境变量的设置，没有发现异常，查询了一下MOS，结果发现这个错误相关的记载还不少。
通过简单的排查，问题符合文档Dbca Fails With: Java.Lang.Noclassdeffounderror [ID 744730.1]的记录。
根据文档描述，导致问题的原因是由于安装文件损坏所致，不过这个数据库在刚安装完毕后启动DBCA时是没有问题的，那么现在导致问题的原因多半是由于操作系统或磁盘问题导致DBCA所需要使用的部分java class文件损坏。
解决问题的方法很简单，在9i的安装文件的第一张盘找到oembase.jar文件，并与ORACLE_HOME目录下的同名文件进行比较，检查文件大小和MD5校验和是否一致，如果不一致将这个文件拷贝到ORACLE_HOME/jlib下，并重命名为oembase-9_2_0.jar。

Posted in ORACLE | Tagged , | Leave a comment

ORA-600(15160)错误

Posted on 2011/11/18 by yangtingkun

客户数据库中发现了这个错误。
在告警日志中错误如下：

Wed Nov 2 11:13:17 2011
Errors IN file /oracleapp/oracle10g/admin/ora10/udump/ora10_ora_9007.trc:
ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], []
Wed Nov 2 11:13:36 2011
Errors IN file /oracleapp/oracle10g/admin/ora10/udump/ora10_ora_9007.trc:
ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], []
Wed Nov 2 11:13:44 2011
Errors IN file /oracleapp/oracle10g/admin/ora10/udump/ora10_ora_9007.trc:
ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], []
Wed Nov 2 11:13:47 2011
Errors IN file /oracleapp/oracle10g/admin/ora10/udump/ora10_ora_9007.trc:
ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], []
Wed Nov 2 11:14:14 2011
Errors IN file /oracleapp/oracle10g/admin/ora10/udump/ora10_ora_9007.trc:
ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], []
Wed Nov 2 11:14:18 2011
Errors IN file /oracleapp/oracle10g/admin/ora10/udump/ora10_ora_9007.trc:
ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], []
Wed Nov 2 11:15:39 2011
Errors IN file /oracleapp/oracle10g/admin/ora10/udump/ora10_ora_9007.trc:
ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], []
Wed Nov 2 11:15:43 2011
Errors IN file /oracleapp/oracle10g/admin/ora10/udump/ora10_ora_9007.trc:
ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], []

对应的TRACE文件详细信息：

/oracleapp/oracle10g/admin/ora10/udump/ora10_ora_9007.trc
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
WITH the Partitioning, OLAP AND DATA Mining options
ORACLE_HOME = /oracleapp/oracle10g
System name: HP-UX
Node name: wfrb1
Release: B.11.31
Version: U
Machine: ia64
Instance name: ora10
Redo thread mounted BY this instance: 1
Oracle process NUMBER: 511
Unix process pid: 9007, image: oracleora10@wfrb1
*** ACTION NAME:() 2011-11-02 11:13:17.618
*** MODULE NAME:(TOAD 8.0.0.47) 2011-11-02 11:13:17.618
*** SERVICE NAME:(ora10) 2011-11-02 11:13:17.618
*** SESSION ID:(508.11184) 2011-11-02 11:13:17.618
*** 2011-11-02 11:13:17.618
ksedmp: internal OR fatal error
ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], []
CURRENT SQL statement FOR this SESSION:
SELECT o.object_name, o.object_type, o.status, t.typecode, t.attributes, t.methods
FROM  SYS.DBA_TYPES t, SYS.DBA_OBJECTS o
WHERE o.owner = :own
AND   o.owner = t.owner
AND   o.object_type = 'TYPE'
AND   o.object_name = t.type_name
AND   o.subobject_name IS NULL
----- Call Stack Trace -----
calling              CALL     entry                argument VALUES IN hex      
location             TYPE     point                (? means dubious VALUE)     
-------------------- -------- -------------------- ----------------------------
ksedst()+64          CALL     _etext_f()+23058430  000000000 ? 000000001 ?
                              09017162224          
ksedmp()+1680        CALL     _etext_f()+23058430  000000000 ?
                              09017162224          C000000000000D20 ?
                                                   40000000052B0470 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ?
ksfdmp()+48          CALL     _etext_f()+23058430  000000003 ?
                              09017162224          
kgeriv()+432         CALL     _etext_f()+23058430  400000000944FAD0 ?
                              09017162224          000000003 ?
                                                   C000000000000695 ?
                                                   000060E0F ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ?
kgesiv()+176         CALL     _etext_f()+23058430  6000000000015C50 ?
                              09017162224          6000000000016D08 ?
                                                   600000000011C078 ?
                                                   6000000000014240 ?
                                                   9FFFFFFFFFFEE9A8 ?
ksesic0()+192        CALL     _etext_f()+23058430  6000000000015C50 ?
                              09017162224          9FFFFFFFBF561168 ?
                                                   000003B38 ? 000000000 ?
                                                   9FFFFFFFFFFEE9A8 ?
$cold_kkogfp()+608   CALL     _etext_f()+23058430  000003B38 ?
                              09017162224          6000000000127450 ?
                                                   9FFFFFFFFFFEE9A8 ?
                                                   6000000000127B20 ?
kkooqb()+2112        CALL     _etext_f()+23058430  9FFFFFFFBF0FFE88 ?
                              09017162224          9FFFFFFFBF0EEA28 ?
                                                   000000001 ?
                                                   9FFFFFFFBF0EE468 ?
kkoqbc()+2912        CALL     0000000000000002     9FFFFFFFBF2C32E0 ?
                                                   000000006 ? 000000002 ?
                                                   000000000 ?
apakkoqb()+384       CALL     9fffffffbf2c3420     9FFFFFFFFFFF07B0 ?
                                                   9FFFFFFFBF2C32E0 ?
                                                   600000000011D1C0 ?
                                                   4000000003340880 ?
                                                   000060209 ?
apaqbd()+800         CALL     9fffffffbf2c3420     9FFFFFFFFFFF07B0 ?
                                                   9FFFFFFFBF2C32E0 ?
                                                   C00000019486D370 ?
                                                   40000000033402C0 ?
                                                   000000000 ?
kkqctCostTransfQB()  CALL     9fffffffbf2c3420     9FFFFFFFFFFF07B0 ?
+432                                               9FFFFFFFBF2C32E0 ?
                                                   C00000019486D370 ?
                                                   000000000 ?
kkqctdrvJP()+2384    CALL     9fffffffbf2c3420     9FFFFFFFBF2C32E0 ?
                                                   40000000021D41C0 ?
                                                   000069409 ?
                                                   9FFFFFFFFFFF07B0 ?
kkqjpdttr()+3472     CALL     0000000000069409     9FFFFFFFBF358D10 ?
                                                   000000100 ?
kkqctdrvTD()+944     CALL     0000000000069409     9FFFFFFFBF358D10 ?
                                                   4000000003400AD0 ?
                                                   00006870D ? 000000000 ?
                                                   000000001 ?
kkqjpddrv()+384      CALL     0000000000069409     9FFFFFFFBF557690 ?
                                                   C00000019486D370 ?
                                                   9FFFFFFFFFFF07FC ?
kkqdrv()+992         CALL     0000000000069409     9FFFFFFFBF557690 ?
                                                   60000000001274D4 ?
                                                   000000000 ?
                                                   400000000320AE60 ?
                                                   6000000000127434 ?
                                                   00006858F ?
kkqctdrvIT()+768     CALL     0000000000069409     9FFFFFFFBF557690 ?
                                                   9FFFFFFFBF557710 ?
.
.
.
main()+352           CALL     _etext_f()+23058430  000000000 ?
                              09017120608          9FFFFFFFFFFFF110 ?
main_opd_entry()+80  CALL     _etext_f()+23058430  000000002 ?
                              09017120608          9FFFFFFFFFFFF5C0 ?
                                                   C000000000033910 ?
                                                   000000000 ?
--------------------- Binary Stack Dump ---------------------

错误发生在Oracle的递归调用语句，在查询DBA_TYPES和DBA_OBJECTS视图时报错。
检查了MOS发现，这个错误的描述为：Ora-600 [15160] Joining Dba_objects and Dba_segments [ID 351092.1]。导致这个问题的原因是两个包含UNION ALL的视图关联。
当前版本是10202，这个bug在10.2.0.3以上的版本被解决。
除了打补丁之外，还可以通过设置隐含参数来解决这个问题：设置_optimizer_cost_based_transformation为off或者_optimizer_push_pred_cost_based为false，同样可以避免这个问题。不过这种和优化器相关的隐含参数的修改，可能会对执行计划的优化产生不利影响，因此修改后有可能造成少部分SQL语句执行计划的改变，因此在确认修改前应谨慎。

Posted in BUG | Tagged , , | Leave a comment

sqlplus本地登录报错ORA-12545

Posted on 2011/11/17 by yangtingkun

在客户服务器上尝试登录数据库是碰到错误。
步骤如下：

> sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production ON Thu Nov 17 17:24:16 2011
Copyright (c) 1982, 2005, Oracle. ALL rights reserved.
SQL> conn / AS sysdba
ERROR:
ORA-12545: CONNECT failed because target host OR object does NOT exist
SQL> conn USER/password
ERROR:
ORA-12545: CONNECT failed because target host OR object does NOT exist
SQL> conn USER/password@100.300.100.200/db
Connected.

尝试连接本地的数据库，结果出现了ORA-12545错误，尝试通过网络方式连接，反而没有碰到问题。这说明数据库本身是正常的，而本地无法连接，显示是本地设置出现了问题。
检查了环境变量的设置，包括ORACLE_SID、ORACLE_HOME和PATH，都未发现任何异常，再次尝试连接数据库：

> sqlplus USER/password@100.300.100.200/db
SQL*Plus: Release 10.2.0.1.0 - Production ON Thu Nov 17 17:26:54 2011
Copyright (c) 1982, 2005, Oracle. ALL rights reserved.
 
Connected TO:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
WITH the Partitioning, OLAP AND Oracle DATA Mining options
JServer Release 9.2.0.4.0 - Production
SQL> exit 
Disconnected FROM Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
WITH the Partitioning, OLAP AND Oracle DATA Mining options
JServer Release 9.2.0.4.0 - Production

这次同样连接到数据库，但是由于连接方式和第一次的不同，通过Oracle提示的信息找到了错误的原因。
首先尝试的简易连接方式是10g的新特性，而且从sqlplus的工具版本信息也可以看得出，sqlplus是10.2.0.1版本的。而连接到数据库后显示的数据库服务器版本信息却是9.2.0.4。
显然当前是通过一个10.2的sqlplus客户端，连接到9.2的数据库。那么无论ORACLE_HOME还是PATH都是指向10.2的客户端的，这就是为什么出现ORA-12545错误的原因：

[DEV]dev:/app/oracle/product
> export ORACLE_HOME=/app/oracle/product/920
[DEV]dev:/app/oracle/product
> export PATH=$ORACLE_HOME/bin:$PATH
[DEV]dev:/app/oracle/product
> sqlplus '/ as sysdba'
SQL*Plus: Release 9.2.0.4.0 - Production ON Thu Nov 17 17:28:41 2011
Copyright (c) 1982, 2002, Oracle Corporation. ALL rights reserved.
 
Connected TO:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
WITH the Partitioning, OLAP AND Oracle DATA Mining options
JServer Release 9.2.0.4.0 - Production
SQL>

定位问题的原因，解决就很简单了，设置ORACLE_HOME和PATH到9.2对应的目录后，在本地sqlplus成功连接数据库。

Posted in ORACLE | Tagged , | Leave a comment

设置AUTOTRACE出现ORA-3212错误

Posted on 2011/11/16 by yangtingkun

客户环境下设置了AUTOTRACE，结果碰到了ORA-3212错误。
详细错误如下：

SQL> conn / AS sysdba
SQL> GRANT SELECT ON v_$session TO posmrk;
GRANT succeeded.
SQL> GRANT SELECT ON v_$mystat TO posmrk;
GRANT succeeded.
SQL> GRANT SELECT ON v_$statname TO posmrk;
GRANT succeeded.
SQL> CONN POSMRK 
Enter password: 
Connected.
SQL> @?/rdbms/admin/utlxplan
TABLE created.
SQL> conn posmrk@219.143.210.210:1621/pcmrk
已连接。
SQL> SET autot trace
SQL> SELECT * FROM dual;
Error ORA-942 while gathering statistics
SP2-0612: Error generating AUTOTRACE report
SP2-0612: Error generating AUTOTRACE report
Execution Plan
----------------------------------------------------------
An uncaught error happened IN fetching the records : ORA-03212: TEMPORARY Segment cannot be created IN locally-managed tablespace
ORA-03212: TEMPORARY Segment cannot be created IN locally-managed tablespace
SP2-0612: Error generating AUTOTRACE STATISTICS report

由于当时没有网络和文档，只能根据错误描述来分析问题。这个错误似乎和表空间以及临时段有关，那么问题牵扯的层面并不太多。
检查了一下数据库的临时表空间设置，并未发现问题，检查了一下用户的表空间以及UNLIMITED TABLESPACE权限，也未发现异常。

SQL> conn system
Connected.
SQL> SET autot trace     
SQL> SELECT * FROM dual;
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 272002086
--------------------------------------------------------------------------
| Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo SIZE
        407  bytes sent via SQL*Net TO client
        400  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          1  ROWS processed
SQL> SET autot off

切换为其他用户，没有发现异常，说明应该是错误用户本身的设置所致。

SQL> SELECT username, temporary_tablespace 
  2  FROM dba_users
  3  WHERE username = 'POSMRK';
USERNAME                 TEMPORARY_TABLESPACE
------------------------ -----------------------------
POSMRK                   SYSTEM
SQL> ALTER USER posmrk TEMPORARY tablespace temp;
USER altered.
SQL> conn posmrk 
Connected.
SQL> SET autot trace
SQL> SELECT * FROM dual;
Error ORA-942 while gathering statistics
SP2-0612: Error generating AUTOTRACE report
SP2-0612: Error generating AUTOTRACE report
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 272002086
--------------------------------------------------------------------------
| Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
SP2-0612: Error generating AUTOTRACE STATISTICS report

检查用户的临时表空间设置，发现错误的设置为SYSTEM，显然这时导致问题的原因，从SYSTEM表空间转变为LOCAL管理方式以后，就不应该设置SYSTEM作为临时表空间了，而应该使用专门的TEMPORARY表空间。
对这个设置进行修改后，ORA-3212错误已经小时，还存在一个ORA-942错误，这个错误以前碰到过，应该是缺少动态视图的权限所致：

SQL> SET autot off
SQL> SELECT TABLE_NAME, privilege FROM user_tab_privs WHERE TABLE_NAME LIKE 'V_$%';
TABLE_NAME                     PRIVILEGE
------------------------------ -----------------------------------------------
V_$SESSION                     SELECT
V_$MYSTAT                      SELECT
V_$STATNAME                    SELECT
SQL> conn / AS sysdba
Connected.
SQL> GRANT SELECT ON v_$sesstat TO posmrk;
GRANT succeeded.
SQL> conn posmrk
Connected.
SQL> SET autot trace
SQL> SELECT * FROM dual;
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 272002086
--------------------------------------------------------------------------
| Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo SIZE
        407  bytes sent via SQL*Net TO client
        400  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          1  ROWS processed
SQL> SET autot off

刚开始授权的时候，授权了V_$SESSION权限而缺少了V_$SESSTAT视图的权限，导致这个问题产生，对视图授权后，问题解决。

Posted in ORACLE | Tagged , , , | Leave a comment