ORA-7445[__milli_memcpy]分析

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-7445[__milli_memcpy]分析

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

帮朋友分析日志的过程中发现在两个节点上多次出现类此错误
节点1日志分析

Mon Mar  5 08:19:20 2012
Errors in file /u01/app/oracle/admin/hddms/udump/hddms1_ora_18556.trc:
ORA-07445: exception encountered: core dump [__milli_memcpy()+2448] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []
Mon Mar  5 08:19:21 2012
Errors in file /u01/app/oracle/admin/hddms/udump/hddms1_ora_18556.trc:
ORA-07445: exception encountered: core dump [kghalf()+961] [SIGSEGV] [Invalid permissions for mapped object] [0x000000000] [] []
ORA-07445: exception encountered: core dump [__milli_memcpy()+2448] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []
Mon Mar  5 08:19:22 2012
Errors in file /u01/app/oracle/admin/hddms/udump/hddms1_ora_18556.trc:
ORA-07445: exception encountered: core dump [kghalf()+464] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFFFFFFFF0] [] []
ORA-07445: exception encountered: core dump [kghalf()+961] [SIGSEGV] [Invalid permissions for mapped object] [0x000000000] [] []
ORA-07445: exception encountered: core dump [__milli_memcpy()+2448] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []

对应trace文件

/u01/app/oracle/admin/hddms/udump/hddms1_ora_18556.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /u01/app/oracle/product/9.2.0
System name:	HP-UX
Node name:	HDDB1
Release:	B.11.23
Version:	U
Machine:	ia64
Instance name: hddms1
Redo thread mounted by this instance: 1
Oracle process number: 42
Unix process pid: 18556, image: oracle@HDDB1 (TNS V1-V3)
*** 2012-03-05 08:19:20.675
*** SESSION ID:(100.40701) 2012-03-05 08:19:20.602
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x9fffffffbf580000, PC: [0x400000000182a9f0, __milli_memcpy()+2448]
  r1: 6000000000524900       r20:                8       br5:                0
  r2:                0       r21: 9fffffffbf4c21ec       br6: 40000000018ad000
  r3:               37       r22:                8       br7: e000000120001400
  r4:                0       r23: c00000005e3bac98        ip: 400000000182a9f0
  r5: 60000000005311d8       r24: 9fffffffbf4c2268      iipa:                0
  r6:         40000004       r25: 9fffffffbf4c2268       cfm:       2a54295428
  r7: 9fffffffffff1ba8       r26: 9fffffffbf580000        um:               1a
  r8: 9fffffffbf4c2268       r27: 9fffffffbf57ff00       rsc:               1f
  r9:             8000       r28: 9fffffffbf580008       bsp: 9fffffffbf802150
 r10: 9fffffffbf4ba260       r29: 9fffffffbf57ff08  bspstore: 9fffffffbf802150
 r11:                0       r30:                0      rnat:                0
 r12: 9ffffffffffe5240       r31:                0       ccv:         20000000
 r13: 9fffffffbf5a1420      NaTs:                0      unat:                0
 r14: 9fffffffbf4c23e8       PRs: ffffffffffff4837      fpsr:    9804c8274433f
 r15: 9fffffffbf580028       br0: 400000000202a7e0       pfs: c000000000001b3e
 r16:  3fffffffffffdff       br1:                0        lc:  3ffffffffffce89
 r17: 9fffffffbf4c2258       br2:                0        ec:                5
 r18:                0       br3:                0       isr: 9fffffffbf802150
 r19:               80       br4:                0       ifa:                0
Reason code: 0008
*** 2012-03-05 08:19:20.735
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [__milli_memcpy()+2448] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []
Current SQL statement for this session:
update pmis.PM_BZ_DC_DWRFHSJ set ZGFDFH=:v_result where PARINST_ID=:v_pid and T_ID>:v_mdtbegin and T_ID<=:v_mdtend
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
c00000008975ebc0        49  procedure PMIS.PM_DC_SJJGGC_EXECINF
c0000000971ef530         2  anonymous block
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp()+528         call     ksedst()             000000001 ?
                                                   C050000000000996 ?
                                                   4000000002A53E60 ?
ssexhd()+1184        call     ksedmp()             000000003 ?
                                                   C0500000000010A9 ?
                                                   40000000025E1420 ?
                                                   FFFFFFFFFFFF9075 ?
                                                   9FFFFFFFFFFDF2E0 ?
                                                   6000000000514428 ?
                                                   6000000000514420 ?
                                                   60000000005178E0 ?
<kernel>             call     ssexhd()             400000000086EFB0 ?
                                                   000000015 ? 000000007 ?
                                                   C050000000000085 ?
__milli_memcpy()+24  call     <kernel>
48
$cold_updgrh()+1088  call     __milli_memcpy()     000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   9FFFFFFFBF564348 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ?
upduaw()+224         call     $cold_updgrh()       C0000000A2665550 ?
                                                   9FFFFFFFBF558FA0 ?
                                                   C000000000000C1E ?
                                                   4000000001916600 ?
                                                   000000000 ? 00000E137 ?
kdusru()+720         call     upduaw()             C0000000A2665550 ?
                                                   C000000000005BBF ?
                                                   4000000001912AC0 ?
                                                   00000E135 ?
                                                   9FFFFFFFFFFE52B0 ?
                                                   C0000000A2665660 ?
                                                   9FFFFFFFFFFE63F0 ?
                                                   9FFFFFFFBF5590D8 ?
kauupd()+544         call     kdusru()             9FFFFFFFBF4C5D24 ?
                                                   000000000 ?
                                                   9FFFFFFFBF558FA0 ?
                                                   000000000 ?
                                                   C00000008B5D1108 ?
                                                   C00000008B5D1138 ?
                                                   000000018 ?
                                                   9FFFFFFFBF5591E0 ?
updrow()+3024        call     kauupd()             9FFFFFFFFFFE65D0 ?
                                                   000000000 ?
                                                   6000000000530010 ?
                                                   000000000 ?
                                                   C00000009202CF38 ?
                                                   00000010B ? 00001AA7A ?
                                                   C000000086F2B778 ?
qerupFetch()+608     call     updrow()             9FFFFFFFBF4C89F0 ?
                                                   C0000000A2665550 ?
                                                   C000000000000C1D ?
                                                   40000000018F5BF0 ?
                                                   000000000 ?
updaul()+1008        call     qerupFetch()         C0000000A0DB24D0 ?
                                                   000000000 ?
                                                   C0000000A2665550 ?
                                                   000007FFF ?
updThreePhaseExe()+  call     updaul()             C000000091A57FA0 ?
432                                                000000006 ? 000000000 ?
                                                   C0000000000019BB ?
                                                   40000000019DF9B0 ?
                                                   000008E23 ?
                                                   60000000001D30F0 ?
                                                   0000000BD ?
updexe()+624         call     updThreePhaseExe()   C000000091A57FA0 ?
                                                   000000000 ?
                                                   9FFFFFFFBF558FA0 ?
                                                   9FFFFFFFFFFF1AF0 ?
                                                   C000000000000FA6 ?
                                                   40000000019DFCA0 ?
                                                   000000000 ?
opiexe()+6944        call     updexe()             C000000091A57FA0 ?
                                                   9FFFFFFFBF4C8A00 ?
                                                   C000000000002858 ?
                                                   4000000001857620 ?
                                                   000008E25 ?
                                                   9FFFFFFFFFFF1AD0 ?
                                                   9FFFFFFFBF558FA0 ?
                                                   9FFFFFFFBF4C88D4 ?
opipls()+3488        call     opiexe()             000000004 ?
                                                   9FFFFFFFFFFF2F50 ?
                                                   9FFFFFFFFFFF1D30 ?
                                                   C000000000002C60 ?
                                                   40000000019852D0 ?
                                                   000000000 ? 000007123 ?
                                                   9FFFFFFFFFFF1B40 ?
opiodr()+3088        call     opipls()             9FFFFFFFFFFF2F50 ?
                                                   9FFFFFFFFFFF2F58 ?
                                                   000000004 ?
                                                   C00000000000214A ?
                                                   40000000018747B0 ?
                                                   00000E123 ?
                                                   9FFFFFFFFFFF2E70 ?
                                                   9FFFFFFFFFFF2DE0 ?
rpidrus()+304        call     opiodr()             9FFFFFFFFFFF3810 ?
                                                   9FFFFFFFFFFF37E0 ?
                                                   9FFFFFFFFFFF36F8 ?
                                                   9FFFFFFFFFFF36F4 ?
skgmstack()+288      call     rpidrus()            9FFFFFFFFFFF4DA0 ?
                                                   C000000000000716 ?
                                                   4000000001862FE0 ?
                                                   9FFFFFFFFFFF47F0 ?
                                                   600000000004F3C0 ?
                                                   9FFFFFFFFFFF4DB8 ?
                                                   6000000000524900 ?
                                                   9FFFFFFFFFFF4DA0 ?
rpidru()+256         call     skgmstack()          9FFFFFFFFFFF4DC0 ?
                                                   600000000004EDF0 ?
                                                   00000F618 ?
                                                   400000000129E400 ?
                                                   9FFFFFFFFFFF4DA0 ?
rpiswu2()+784        call     rpidru()             00000057B ?
                                                   C000000000001329 ?
                                                   4000000001870CE0 ?
rpidrv()+2096        call     rpiswu2()            C00000007D7B3708 ?
                                                   9FFFFFFFFFFF5510 ?
                                                   6000000000515CE8 ?
                                                   60000000000502E8 ?
                                                   9FFFFFFFFFFF5520 ?
                                                   000000205 ?
                                                   6000000000530010 ?
                                                   6000000000531090 ?
psddr0()+256         call     rpidrv()             000000000 ? 000000066 ?
                                                   9FFFFFFFFFFF6630 ?
                                                   00000003A ?
psdnal()+672         call     psddr0()             000000000 ? 000000066 ?
                                                   9FFFFFFFFFFF6630 ?
                                                   000000030 ?
pevm_EXIM()+400      call     psdnal()             9FFFFFFFFFFF84A0 ?
                                                   9FFFFFFFFFFF8440 ?
                                                   6000000000531A04 ?
                                                   600000000064BAA0 ?
                                                   C000000090880CC8 ?
                                                   600000000063EA80 ?
pfrrun()+37120       call     pevm_EXIM()          9FFFFFFFBF567AD8 ?
                                                   C000000090880CC8 ?
                                                   C000000000001532 ?
                                                   400000000173DAD0 ?
                                                   000006269 ?
                                                   4000000002190B40 ?
                                                   9FFFFFFFBF4BA068 ?
                                                   9FFFFFFFBF5C08A0 ?
peicnt()+544         call     pfrrun()             9FFFFFFFBF567CDC ?
                                                   C000000000000D1D ?
                                                   4000000001676FE0 ?
kkxexe()+832         call     peicnt()             9FFFFFFFFFFF84A0 ?
                                                   9FFFFFFFBF567AD8 ?
                                                   C000000000000818 ?
                                                   40000000021A3A80 ?
                                                   00000FEAB ?
                                                   9FFFFFFFFFFF7EB0 ?
                                                   9FFFFFFFBF567CDC ?
                                                   000000000 ?
opiexe()+12592       call     kkxexe()             9FFFFFFFFFFF8430 ?
                                                   C000000000002858 ?
                                                   4000000001858C30 ?
                                                   0000080AB ?
                                                   9FFFFFFFFFFF8420 ?
                                                   9FFFFFFFBF4F3A6C ?
                                                   9FFFFFFFBF4F3A68 ?
                                                   9FFFFFFFFFFF84A0 ?
opiall0()+3456       call     opiexe()             000000004 ?
                                                   9FFFFFFFFFFF9A80 ?
                                                   9FFFFFFFFFFF8830 ?
                                                   C0000000000024D1 ?
                                                   40000000017A3350 ?
                                                   000000000 ? 00000C82B ?
                                                   9FFFFFFFFFFF8640 ?
kpoal8()+2272        call     opiall0()            000000002 ?
                                                   9FFFFFFFFFFF9A10 ?
                                                   9FFFFFFFFFFFA2B4 ?
                                                   000000000 ?
                                                   9FFFFFFFFFFF9954 ?
                                                   9FFFFFFFFFFFA2BC ?
                                                   000000000 ?
                                                   FFFFFFFFFFFFDFFF ?
opiodr()+3088        call     kpoal8()             9FFFFFFFFFFFA0B0 ?
                                                   000000000 ? 000000000 ?
                                                   C00000000000214A ?
                                                   40000000018747B0 ?
                                                   00000F82F ?
                                                   9FFFFFFFFFFFA130 ?
                                                   9FFFFFFFFFFFA1D0 ?
ttcpip()+1888        call     opiodr()             9FFFFFFFFFFFA460 ?
                                                   9FFFFFFFFFFFA430 ?
                                                   9FFFFFFFFFFFA348 ?
                                                   9FFFFFFFFFFFA344 ?
                                                   C0000000000018B9 ?
                                                   400000000172CB70 ?
                                                   000000000 ? 00000C0AB ?
opitsk()+1920        call     ttcpip()             6000000000052C40 ?
                                                   000000002 ?
                                                   9FFFFFFFFFFFB950 ?
                                                   6000000000273AA0 ?
                                                   9FFFFFFFFFFFBAB0 ?
                                                   9FFFFFFFFFFFB8C4 ?
                                                   9FFFFFFFBF75A660 ?
                                                   4000000000D2C7AA ?
opiino()+2656        call     opitsk()             000000000 ? 000000000 ?
                                                   C000000000000D1F ?
                                                   400000000236CE90 ?
                                                   000000000 ?
opiodr()+3088        call     opiino()             60000000005DEC18 ?
                                                   6000000000531170 ?
                                                   9FFFFFFFFFFFF4C0 ?
                                                   C00000000000214A ?
                                                   40000000018747B0 ?
                                                   00000E825 ?
                                                   9FFFFFFFFFFFD7C0 ?
                                                   9FFFFFFFFFFFF4D0 ?
opidrv()+1088        call     opiodr()             9FFFFFFFFFFFDF10 ?
                                                   9FFFFFFFFFFFDEE0 ?
                                                   9FFFFFFFFFFFDDF8 ?
                                                   9FFFFFFFFFFFDDF4 ?
sou2o()+48           call     opidrv()             9FFFFFFFFFFFEF60 ?
                                                   000000004 ?
                                                   9FFFFFFFFFFFF4C0 ?
main()+352           call     sou2o()              9FFFFFFFFFFFF4E0 ?
                                                   00000003C ? 000000004 ?
                                                   9FFFFFFFFFFFF4C0 ?
main_opd_entry()+80  call     main()               000000000 ?
                                                   9FFFFFFFFFFFF9B0 ?
                                                   C000000000000004 ?
                                                   C00000000002FA60 ?
--------------------- Binary Stack Dump ---------------------

观察节点2，发现类此错误不同之处有

Wed Mar  7 08:19:11 2012
Errors in file /u01/app/oracle/admin/hddms/udump/hddms2_ora_16729.trc:
ORA-07445: exception encountered: core dump [__milli_memcpy()+2496] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []
Wed Mar  7 08:19:12 2012
Errors in file /u01/app/oracle/admin/hddms/udump/hddms2_ora_16729.trc:
ORA-00600: internal error code, arguments: [kghfrempty:ds], [0x9FFFFFFFBF55A550], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [__milli_memcpy()+2496] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []
Wed Mar  7 08:19:13 2012
Errors in file /u01/app/oracle/admin/hddms/udump/hddms2_ora_16729.trc:
ORA-07445: exception encountered: core dump [kgidmp()+1568] [SIGSEGV] [Address not mapped to object] [0xB38F00000000D9] [] []
ORA-00600: internal error code, arguments: [kghfrempty:ds], [0x9FFFFFFFBF55A550], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [__milli_memcpy()+2496] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []
Wed Mar  7 08:19:14 2012
Errors in file /u01/app/oracle/admin/hddms/udump/hddms2_ora_16729.trc:
ORA-07445: exception encountered: core dump [kghalf()+993] [SIGSEGV] [Address not mapped to object] [0xC0000000A8B01DA0] [] []
ORA-07445: exception encountered: core dump [kgidmp()+1568] [SIGSEGV] [Address not mapped to object] [0xB38F00000000D9] [] []
ORA-00600: internal error code, arguments: [kghfrempty:ds], [0x9FFFFFFFBF55A550], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [__milli_memcpy()+2496] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []

对应trace文件不同之处

*** 2012-03-07 08:19:11.582
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [__milli_memcpy()+2496] [SIGSEGV] [Address not mapped to object] [0x9FFFFFFFBF580000] [] []
No current SQL statement being executed.
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
c000000090c4a818      1356  procedure PMIS.PM_DC_SJJGGC_QDX
c0000000a25512c0        43  procedure PMIS.PM_DC_SJJGGC_EXECINF
c00000009a8306e8         2  anonymous block

通过这两个节点的错误记录，都包含PMIS.PM_DC_SJJGGC_EXECINF和anonymous，所以初步怀疑是某个程序在这个时间定运行某个任务(包含PMIS.PM_DC_SJJGGC_EXECINF过程)导致。
继续观察trace日志发现两个节点都是类此情况相同的程序，相同的主机

  SO: c00000007d7b3708, type: 4, owner: c00000007d6d0190, flag: INIT/-/-/0x00
    (session) trans: c000000082974470, creator: c00000007d6d0190, flag: (100041) USR/- BSY/-/-/-/-/-
              DID: 0001-002A-0000693D, short-term DID: 0000-0000-00000000
              txn branch: 0000000000000000
              oct: 6, prv: 0, sql: c0000000a229ab38, psql: c000000084e6b510, user: 517/PMIS
    O/S info: user: Administrator, term: DMIS_SWITCH2, ospid: 5600:3020, machine: WORKGROUP\DMIS_SWITCH2
              program: PMTDCtrl.exe
    application name: PMTDCtrl.exe, hash value=0
    last wait for 'enqueue' blocking sess=0x0 seq=32849 wait_time=127
                name|mode=54540004, id1=1, id2=10
    temporary object counter: 0

现在更加怀疑是PMTDCtrl.exe运行某过程导致数据库出现这些错误

错误总结
查看MOS发现该问题是一个bug导致(Bug 6166690:ORA-7445 [MILLI_MEMCPY] WHEN REF CURSOR AS OUT PARAMETER)
错误原因:It is caused by a Dynamic SQL used to obtain a REF CURSOR from a procedure (that opens the cursor).
处理建议:通过个bug，我们可以分析PMIS.PM_DC_SJJGGC_EXECINF过程，看看是否符合bug描述,如果符合建议改写过程或者打上补丁(Patch 2709343)
补丁:p2709343_92080_HPUX-IA64

记录一次ORA-600[13013]处理过程

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：记录一次ORA-600[13013]处理过程

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在一次数据库的异常处理完成后，发现alert日志中出现ORA-600[13013]错误

Thu Mar 08 23:29:37 2012
Errors in file /opt/oracle/diag/rdbms/chf/chf/trace/chf_smon_24137.trc  (incident=38681):
ORA-00600: internal error code, arguments: [13013], [5001], [518], [4198427], [170], [4198427], [17], [], [], [], [], []
Incident details in: /opt/oracle/diag/rdbms/chf/chf/incident/incdir_38681/chf_smon_24137_i38681.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Non-fatal internal error happenned while SMON was doing flushing of monitored table stats.
SMON encountered 1 out of maximum 100 non-fatal internal errors.

trace文件中信息
从这里可以看出是对sys.col_usage$表进行update操作导致该错误发生

Dump continued from file: /opt/oracle/diag/rdbms/chf/chf/trace/chf_smon_24137.trc
ORA-00600: internal error code, arguments: [13013], [5001], [518], [4198427], [170], [4198427], [17], [], [], [], [], []
========= Dump for incident 38681 (ORA 600 [13013]) ========
*** 2012-03-08 23:29:37.400
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=3c1kubcdjnppq) -----
update sys.col_usage$ set   equality_preds    = equality_preds    + decode(bitand(:flag,1),0,0,1),   equijoin_preds    = equijoin_preds    + decode(bitand(:flag,2),0,0
,1),   nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0,1),   range_preds       = range_preds       + decode(bitand(:flag,8),0,0,1),   like_preds
    = like_preds        + decode(bitand(:flag,16),0,0,1),   null_preds        = null_preds        + decode(bitand(:flag,32),0,0,1),  timestamp = :time where obj# = :ob
jn and intcol# = :coln

MOS中关于ORA-600 [13013]描述

Format: ORA-600 [13013] [a] [b] {c} [d] [e] [f]
Arg [a] Passcount
Arg [b] Data Object number
Arg {c} Tablespace Decimal Relative DBA (RDBA) of block containing the row to be updated
Arg [d] Row Slot number
Arg [e] Decimal RDBA of block being updated (Typically same as {c})
Arg [f] Code

验证MOS中描述

SQL> select dbms_utility.data_block_address_file(4198427) rfile,
  2  dbms_utility.data_block_address_block(4198427) blocks
  3  from dual;
     RFILE     BLOCKS
---------- ----------
         1       4123
SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME
  2    FROM DBA_EXTENTS A
  3   WHERE FILE_ID = &FILE_ID
  4     AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
Enter value for file_id: 1
old   3:  WHERE FILE_ID = &FILE_ID
new   3:  WHERE FILE_ID = 1
Enter value for block_id: 4123
old   4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
new   4:    AND 4123 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
OWNER SEGMENT_NAME SEGMENT_TY TABLESPACE PARTI
----- ------------ ---------- ---------- -----
SYS   COL_USAGE$   TABLE      SYSTEM
--和trace文件中异常表一致
SQL>  select object_type,object_name from dba_objects where object_id=518;
OBJECT_TYPE         OBJECT_NAME
------------------- ------------------------------
TABLE               COL_USAGE$
--也和trace文件中异常表一致

分析异常表

SQL> ANALYZE TABLE sys.COL_USAGE$ VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE sys.COL_USAGE$ VALIDATE STRUCTURE CASCADE
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file
SQL> select index_name,COLUMN_NAME,COLUMN_POSITION FROM DBA_IND_COLUMNS
2    WHERE TABLE_NAME='COL_USAGE$';
INDEX_NAME      COLUMN_NAM COLUMN_POSITION
--------------- ---------- ---------------
I_COL_USAGE$    OBJ#                     1
I_COL_USAGE$    INTCOL#                  2
SQL> set autot trace exp
SQL>  SELECT /*+ FULL(t1) */ OBJ#,INTCOL#
  2   FROM sys.COL_USAGE$ t1
  3   MINUS
  4   SELECT /*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL#
  5   FROM sys.COL_USAGE$ t where OBJ# is not null or INTCOL# is not null;
no rows selected
--无记录返回
Execution Plan
----------------------------------------------------------
Plan hash value: 399371572
------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time
   |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |  4262 | 76716 |    27  (71)| 00:00:01 |
|   1 |  MINUS              |              |       |       |            |          |
|   2 |   SORT UNIQUE       |              |  4262 | 38358 |     9  (12)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| COL_USAGE$   |  4262 | 38358 |     8   (0)| 00:00:01 |
|   4 |   SORT UNIQUE NOSORT|              |  4262 | 38358 |    18   (6)| 00:00:01 |
|*  5 |    INDEX FULL SCAN  | I_COL_USAGE$ |  4262 | 38358 |    17   (0)| 00:00:01 |
------------------------------------------------------------------------------------
--验证表两个sql是否正确(一个全表扫描，另个index 快速扫描)
SQL>  SELECT /*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL#
  2   FROM sys.COL_USAGE$ t where OBJ# is not null or INTCOL# is not null
  3   MINUS
  4  SELECT /*+ FULL(t1) */ OBJ#,INTCOL#
  5   FROM sys.COL_USAGE$ t1;
      OBJ#    INTCOL#
---------- ----------
4294951004          2
4294951004          3
4294951004          4
4294951004         26
4294951004         27
4294951037          4
4294951037          5
4294951037          6
4294951037          9
4294951037         10
4294951840         11
      OBJ#    INTCOL#
---------- ----------
4294951840         12
4294951906          4
4294952709          3
4294952867          4
4294952867          9
16 rows selected.
--证明index中的记录比表中多了16条

解决问题并验证

SQL> alter index sys.I_COL_USAGE$ rebuild online;
Index altered.
SQL>  SELECT /*+ FULL(t1) */ OBJ#,INTCOL#
 FROM sys.COL_USAGE$ t1
  2    3   MINUS
  4   SELECT /*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL#
  5   FROM sys.COL_USAGE$ t where OBJ# is not null or INTCOL# is not null
  6  ;
no rows selected
SQL>  SELECT /*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL#
  2   FROM sys.COL_USAGE$ t where OBJ# is not null or INTCOL# is not null
  3   MINUS
  4  SELECT /*+ FULL(t1) */ OBJ#,INTCOL#
  5   FROM sys.COL_USAGE$ t1;
no rows selected

这次出现此问题的原因是因为在更新语句中使用索引找到一条记录，然后到表中去查询时该记录不存在，出现此错误，一般解决方法是重建索引
官方关于ORA-600[13013]说明

Getting ORA-01476 during execution of DBMS_STATS.GATHER_SCHEMA_STATS

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Getting ORA-01476 during execution of DBMS_STATS.GATHER_SCHEMA_STATS

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

alert日志

Fri Jan 27 22:00:09 2012
GATHER_STATS_JOB encountered errors.  Check the trace file.
Fri Jan 27 22:00:09 2012
Errors in file /oracle10/admin/ocs/bdump/ocs1_j001_29138.trc:
ORA-01476: divisor is equal to zero

trace内容

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracle10/app/product/db/10.2.0
System name:    HP-UX
Node name:      ocsdb1
Release:        B.11.23
Version:        U
Machine:        ia64
Instance name: ocs1
Redo thread mounted by this instance: 1
Oracle process number: 60
Unix process pid: 29138, image: oracle@ocsdb1 (J001)
*** ACTION NAME:(GATHER_STATS_JOB) 2012-01-27 22:00:09.308
*** MODULE NAME:(DBMS_SCHEDULER) 2012-01-27 22:00:09.308
*** SERVICE NAME:(SYS$USERS) 2012-01-27 22:00:09.308
*** SESSION ID:(988.31342) 2012-01-27 22:00:09.307
ORA-01476: divisor is equal to zero
*** 2012-01-27 22:00:09.417
GATHER_STATS_JOB: GATHER_TABLE_STATS('"OCS_SM"','"HLP_SMS_SEND"','""', ...)
ORA-01476: divisor is equal to zero

错误原因
oracle unpublished Bug 5645718

解决方法
1.Setting event 38041 at level 16

sql> connect / as sysdba
sql> alter system set events '38041 trace name context forever, level 16';

2.Patch 6319761

手动提交分布式事务一例

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：手动提交分布式事务一例

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

一.alert文件中出现了很多类此记录

Fri Feb 10 05:25:01 2012
Errors in file /tmp/recover/ahcx216_reco_7956.trc:
ORA-12154: TNS:could not resolve service name
Fri Feb 10 05:25:01 2012
Errors in file /tmp/recover/ahcx216_reco_7956.trc:
ORA-12154: TNS:could not resolve service name

这里可以看出来两个信息：
1.出错的进程是rec0进程，而该进程的作用是解决分布式事务失败后遗留问题(事务提交或者回滚)
2.错误信息是tns不能被解析

二.查看trace文件

/tmp/recover/ahcx216_reco_7956.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /opt/oracle/product/9.2.0/db_1
System name:    Linux
Node name:      localhost.localdomain
Release:        2.6.9-89.0.0.0.1.ELhugemem
Version:        #1 SMP Tue May 19 04:38:38 EDT 2009
Machine:        i686
Instance name: ahcx216
Redo thread mounted by this instance: 1
Oracle process number: 7
Unix process pid: 7956, image: oracle@localhost.localdomain (RECO)
*** SESSION ID:(6.1) 2012-02-10 04:58:24.886
*** 2012-02-10 04:58:24.886
ERROR, tran=6.1.712757, session#=1, ose=0:
ORA-12154: TNS:could not resolve service name
………………
*** 2012-02-10 05:25:01.580
ERROR, tran=6.1.712757, session#=1, ose=0:
ORA-12154: TNS:could not resolve service name
*** 2012-02-10 05:25:01.627
ERROR, tran=12.19.99059, session#=1, ose=0:
ORA-12154: TNS:could not resolve service name

通过这里我们可以看出事务id分别为12.19.99059和6.1.712757有问题

三.查看dba_2pc_pending视图

SQL> select local_tran_id,state,fail_time,retry_time from dba_2pc_pending;
LOCAL_TRAN_ID     STATE            FAIL_TIME           RETRY_TIME
-------------- ---------------- --------------       --------------
6.1.712757	collecting	2010/12/1 13:39:03   2012/2/10 5:38:52
12.19.99059	collecting	2010/12/1 15:56:26   2012/2/10 5:38:52

可以看出，果然有两个分布式事务在2010年12月1日出了问题(本库是一个问题库，在把库拉起来后发现该问题)
因为是异机恢复，而且间隔时间较长，很多tns的信息都已经不存在，所以需要手工提交分布式事务

四.手动提交事务

SQL> BEGIN
  2  DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('6.1.712757');
  3  DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('12.19.99059');
  4  END;
  5  /
BEGIN
*
第 1 行出现错误:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 2
SQL> alter session set "_smu_debug_mode"=4;
Session altered.
-------------------------------------------------------------
--设置UNDO_SUPPRESS_ERRORS=true也可以解决此问题
alter system set UNDO_SUPPRESS_ERRORS = TRUE;
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('<事务ID>');
commit;
alter system set UNDO_SUPPRESS_ERRORS = false;
--------------------------------------------------------------
SQL> commit;
SQL> exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('6.1.712757');
PL/SQL procedure successfully completed.
SQL> exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('12.19.99059');
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('12.19.99059'); END;
*
ERROR at line 1:
ORA-01453: SET TRANSACTION must be first statement of transaction
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 1
--第一个分布式事务处理后，未提交导致
SQL> commit;
Commit complete.
SQL> exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('12.19.99059');
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

五.补充说明
开始在另一个会话中，执行失败原因

SQL> alter session set "_smu_debug_mode"=4;
会话已更改。
SQL> commit;
提交完成。
SQL> BEGIN
  2  DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('6.1.712757');
  3  DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('12.19.99059');
  4  END;
  5  /
BEGIN
*
第 1 行出现错误:
ORA-01453: SET TRANSACTION must be first statement of transaction
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 3

因为这里的begin end中包含了两个事务的清理，在清理完第一个事务之后，需要提交才能够清理第二个，这里因为没有提交，导致ORA-01453错误。

sqlplus版本不正确，导致ORA-12560

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：sqlplus版本不正确，导致ORA-12560

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

sqlplus登录数据库报ORA-12560

C:\Users\XIFENFEI>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 14 23:33:31 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-12560: TNS:protocol adapter error
Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error
Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
C:\Users\XIFENFEI>sqlplus -v
SQL*Plus: Release 11.2.0.2.0 Production

在这里发现sqlplus的版本信息不正确，我的数据库安装的11.2.0.1，这么怎么显示sqlplus是11.2.0.2，然后进入%ORACLE_HOME%/bin目录下面执行sqlplus，登录成功

C:\Users\XIFENFEI>cd e:\oracle\11_2_0\BIN
e:\oracle\11_2_0\BIN>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期二 2月 14 23:44:38 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL>

出现了这样的问题，考虑应该是环境变量设置不当导致，查看环境变量发现下面一部分

E:\oracle\timesten\bin;E:\oracle\timesten\ttoracle_home\instantclient_11_2;e:\oracle\11_2_0\bin;

问题的答案就是在这里，因为我的电脑是先安装Oracle，前几天安装了TimesTen，导致环境变量发生了改变，使得我在默认情况下使用sqlplus的时候，自动调用了TimesTen中的sqlplus导致

C:\Users\XIFENFEI>cd E:\oracle\timesten\ttoracle_home\instantclient_11_2
E:\oracle\timesten\ttoracle_home\instantclient_11_2>dir sqlplus*
 驱动器 E 中的卷没有标签。
 卷的序列号是 38D0-2A35
 E:\oracle\timesten\ttoracle_home\instantclient_11_2 的目录
2010/11/19  00:50           655,360 sqlplus.exe
2011/11/08  04:12            84,524 sqlplus.sym
2011/11/08  04:12               554 SQLPLUS_README
               3 个文件        740,438 字节
               0 个目录  9,825,832,960 可用字节

Query Duration=0与ORA-01555

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Query Duration=0与ORA-01555

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

1.ALERT日志错误
奇怪之处:Query Duration=0 sec,竟然出现了ORA-01555

Tue Feb  7 02:41:34 2012
ORA-01555 caused by SQL statement below (Query Duration=0 sec, SCN: 0x0b2e.efcd78a9):
Tue Feb  7 02:41:34 2012
SELECT "ID_NO","CUST_ID" FROM "DBACCADM"."DCUSTMSG" "C" WHERE "ID_NO"=:1

2.ORA-01555解释
超过了undo_retention时间，undo被覆盖导致ORA-01555

[zwq_acc1:/home/oraeye/check]oerr ora 1555
01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"
// *Cause: rollback records needed by a reader for consistent read are
//         overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
//          setting. Otherwise, use larger rollback segments

3.数据库版本

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

4.undo基本信息
从这里可以发现，两个节点的undo表空间还有很多剩余，缺发生了undo被覆盖从而出现了ORA-01555

SQL> col name for a20
SQL> col value for a15
SQL> SELECT INST_ID, NAME, VALUE
  2    FROM GV$PARAMETER
  3   WHERE UPPER (Name) LIKE '%UNDO%';
   INST_ID NAME                 VALUE
---------- -------------------- ---------------
         1 undo_management      AUTO
         1 undo_tablespace      UNDOTBS1
         1 undo_suppress_errors FALSE
         1 undo_retention       1800
         2 undo_management      AUTO
         2 undo_tablespace      UNDOTBS2
         2 undo_suppress_errors FALSE
         2 undo_retention       1800
8 rows selected.
TABLESPACE_NAME                CURRENT_TOTAL(MB)   USED(MB)   FREE(MB)      FREE% AUT MAX_TOTAL(MB)
------------------------------ ----------------- ---------- ---------- ---------- --- -------------
UNDOTBS1                                   40950    1587.94 39362.0625      96.12 NO          40950
UNDOTBS2                                   57330    1926.31 55403.6875      96.64 NO          57330
SQL> SELECT DISTINCT STATUS ,
  2                  COUNT(*) "EXTENT_NUM",
  3                  SUM(BYTES) / 1024 / 1024 / 1024 "UNDO(G)"
  4    FROM DBA_UNDO_EXTENTS
  5   GROUP BY STATUS;
STATUS    EXTENT_NUM    UNDO(G)
--------- ---------- ----------
ACTIVE           208 .273658752
EXPIRED         7651 2.42865753
UNEXPIRED        941 .752548218

查询MOS[ID 761128.1]，发现可能是Oracle bug导致(BUG:6799685 – ORA-1555 ERROR WITH QUERY DURATION=0 AND UNDO_RETENTION=1800和BUG:5475085 – V$UNDOSTAT.EXPBLKREUCNT IS NEVER INCREMENTED)

5.解决方法
Increase the size of the UNDO tablespace and increase the UNDO_RETENTION parameter value to try to prevent required undo expiring too quickly.
基于本库，因为undo空间还有很大剩余，直接设置UNDO_RETENTION=3600即可(可以从一定程度上缓解整个问题，但是要从根本上解决整个问题，需要升级到10.2.0.4及其以上版本)

ORA-00600[4454]

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-00600[4454]

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

数据库版本信息

SQL> select * from gv$version;
   INST_ID BANNER
---------- ----------------------------------------------------------------
         1 Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
         1 PL/SQL Release 9.2.0.8.0 - Production
         1 CORE 9.2.0.8.0       Production
         1 TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
         1 NLSRTL Version 9.2.0.8.0 - Production
         2 Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
         2 PL/SQL Release 9.2.0.8.0 - Production
         2 CORE 9.2.0.8.0       Production
         2 TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
         2 NLSRTL Version 9.2.0.8.0 - Production
10 rows selected.

早上检查某运营商的结算数据库时发现

Mon Feb  6 16:03:23 2012
Errors in file /oracle9/app/admin/settl/udump/settl1_ora_1355948.trc:
ORA-00600: internal error code, arguments: [4454], [], [], [], [], [], [], []
Mon Feb  6 16:03:23 2012
Errors in file /oracle9/app/admin/settl/udump/settl1_ora_1355948.trc:
ORA-00600: internal error code, arguments: [4454], [], [], [], [], [], [], []

trace文件信息

*** SESSION ID:(100.35758) 2012-02-06 16:03:23.223
*** 2012-02-06 16:03:23.223
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4454], [], [], [], [], [], [], []
Current SQL statement for this session:
select trim(operator_id) into :b1  from b_sys_proc where program_name=:b2
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp+0148          bl       ksedst               1029746CC ?
ksfdmp+0018          bl       01FD4010
kgeriv+0118          bl       _ptrgl
kgeasi+00cc          bl       kgeriv               1101FAF48 ? 110248038 ?
                                                   00000000C ? 110235978 ?
                                                   000000040 ?
ktcsptg+00c4         bl       kgeasi               1100062D8 ? 110389E88 ?
                                                   116600001166 ? 200000002 ?
                                                   000000000 ? 70000010738F0E8 ?
                                                   0FFFFFFFF ? 0FFFFFFFF ?
opiexe+0524          bl       ktcsptg              000000000 ? 000000000 ?
opiefn0+01c0         bl       opiexe               49FFFFA640 ? 4900000001 ?
                                                   FFFFFFFFFFFA5C8 ?
opiefn+0100          bl       opiefn0              01000CF60 ? 1029C61B0 ?
                                                   000000002 ? FFFFFFFFFFFC1BC ?
                                                   000000001 ? 00000000C ?
                                                   00000000B ? 110061F50 ?
opiodr+08cc          bl       _ptrgl
ttcpip+0cc4          bl       _ptrgl
opitsk+0d60          bl       ttcpip               11000CF60 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
opiino+0758          bl       opitsk               000000000 ? 000000000 ?
opiodr+08cc          bl       _ptrgl
opidrv+032c          bl       opiodr               3C00000018 ? 4101FAF48 ?
                                                   FFFFFFFFFFFF790 ? 0A000EEA8 ?
sou2o+0028           bl       opidrv               3C0C000000 ? 4A0147B50 ?
                                                   FFFFFFFFFFFF790 ?
main+0138            bl       01FD3A28
__start+0098         bl       main                 000000000 ? 000000000 ?
………………
SO: 70000010738f0e8, type: 4, owner: 7000001043acd90, flag: INIT/-/-/0x00
    (session) trans: 70000010dde1dd8, creator: 7000001043acd90, flag: (100041) USR/- BSY/-/-/-/-/-
              DID: 0001-0064-7625733A, short-term DID: 0000-0000-00000000
              txn branch: 7000001147dbed8
              oct: 0, prv: 0, sql: 700000117b7f778, psql: 0, user: 24/SETTLE
    O/S info: user: settle, term: , ospid: 1400836, machine: zwq_jies2
              program: /settlement/pkg01/bin/long/long_app@zwq_jies2 (T
    application name: /settlement/pkg01/bin/long/long_app@zwq_jies2 (T, hash value=0
    last wait for 'SQL*Net message from client' blocking sess=0x0 seq=483 wait_time=240
                driver id=54435000, #bytes=1, =0

查询MOS，发现是Bug# 1402161（虽然在mos中声明该bug是在9.2.0.1中已经被修复，但是这里的trace文件中的Call Stack Trace和mos中记录一致，而且网络上也存在很多9.2.0.8中关于该bug的情况），trace文件最后一段可以看出是在节点2上（zwq_jies2）运行/settlement/pkg01/bin/long/long_app程序导致出现该错误
产生该错误原因：

This exception is signalled because the savepoint number is not what was
  expected.
The current transaction savepoint is less than the beginning savepoint of
  the transaction.

解决方法：
把/settlement/pkg01/bin/long/long_app中的需要运行的程序分割成几个小程序运行。

Break the job into smaller chunks and reconnect for each part of the job
  to reset the savepoint number.

ORA-00600[ktspNextL1:4]

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-00600[ktspNextL1:4]

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在检查某运营商的客服数据库时发现如下错误

Tue Jan 31 22:00:50 2012
Errors in file /oracle10/admin/ahunicom/bdump/ahunicom1_j005_24445074.trc:
ORA-00600: internal error code, arguments: [ktspNextL1:4], [], [], [], [], [], [], []
Tue Jan 31 22:01:18 2012
Trace dumping is performing id=[cdmp_20120131220118]
Tue Jan 31 22:01:18 2012
Errors in file /oracle10/admin/ahunicom/bdump/ahunicom1_j005_24445074.trc:
ORA-00600: internal error code, arguments: [ORA-00600: internal error code, arguments: [ktspNextL1:4], [], [], [], [], [], [], []
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at "SYS.DBMS_SPACE", line 1500
ORA-06512: at "SYS.DBMS_SPACE", line 1566
], [], [], [], [], [], [], []

查看trace文件

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracle10/app/product/db/10.2.0
System name:    AIX
Node name:      zwq_kfdb1
Release:        1
Version:        6
Machine:        00C5C4764C00
Instance name: ahunicom1
Redo thread mounted by this instance: 1
Oracle process number: 192
Unix process pid: 24445074, image: oracle@zwq_kfdb1 (J005)
*** ACTION NAME:(AUTO_SPACE_ADVISOR_JOB) 2012-01-31 22:00:50.874
*** MODULE NAME:(DBMS_SCHEDULER) 2012-01-31 22:00:50.874
*** SERVICE NAME:(SYS$USERS) 2012-01-31 22:00:50.874
*** SESSION ID:(454.44574) 2012-01-31 22:00:50.874
*** 2012-01-31 22:00:50.873
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [ktspNextL1:4], [], [], [], [], [], [], []
Current SQL statement for this session:
insert into wri$_adv_objspace_trend_data select timepoint,  space_usage, space_alloc, quality from  table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL
, NULL,  NULL, 'FALSE', :5, 'FALSE'))
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
70000010d9a65e0      1834  package body SYS.DBMS_SPACE
70000010d9a65e0      3922  package body SYS.DBMS_SPACE
70000010d9a65e0      4233  package body SYS.DBMS_SPACE
70000011a7d9c88         1  anonymous block
700000180678048       344  SYS.WRI$_ADV_OBJSPACE_TREND_T
700000180678048      1485  SYS.WRI$_ADV_OBJSPACE_TREND_T
7000001334867d0      1535  package body SYS.PRVT_ADVISOR
7000001334867d0      1618  package body SYS.PRVT_ADVISOR
70000011f9f64a8       186  package body SYS.DBMS_ADVISOR
70000010d9a65e0      1500  package body SYS.DBMS_SPACE
70000010d9a65e0      1566  package body SYS.DBMS_SPACE
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst+001c          bl       ksedst1              70000017E9975D0 ? 100000001 ?
ksedmp+0290          bl       ksedst               104A2CDB0 ?
ksfdmp+0018          bl       03F2735C
kgerinv+00dc         bl       _ptrgl
kgeasnmierr+004c     bl       kgerinv              000000000 ? 000000001 ?
                                                   000000005 ? 7000000E19760FC ?
                                                   7000000E1976014 ?
ktspGetNextL1ForSca  bl       01F94828
n+0104
ktspScanInit+026c    bl       ktspGetNextL1ForSca  A0A0E1B89 ? 10564BCF4 ?
                              n                    80003000804DC ?
ktspGenExtentMap1+0  bl       ktspScanInit         000000000 ?
0e8
kteinmap1+00bc       bl       ktspGenExtentMap1    000000000 ? 000000001 ?
                                                   FFFFFFFFFFE3A80 ?
kteinmap+0010        bl       kteinmap1            000000000 ? 00000000D ?
kdgini+036c          bl       kteinmap             FFFFFFFFFFE3DA0 ?
kdg_block_auto+018c  bl       kdgini               000000000 ? A0B4A708C ?
                                                   5676A0005676A ?
                                                   80003000804DC ? 000000000 ?
                                                   70000016AD82170 ? 110195498 ?
                                                   70000016AD82148 ?
ktsa_object_space_u  bl       kdg_block_auto       FFFFFFFFFFE4C50 ?
sage+0950                                          FFFFFFFFFFE4CA0 ? 200000780 ?
                                                   70000011A7D9C88 ?

由trace文件中的insert语句可以知道，这个错误是DBMS_SPACE.OBJECT_GROWTH_TREND进行空间分析时被触发
查询MOS[ID 841158.1]，发现这个是一个没有公布的bug(5649098),在11.1.0.7中被修复

解决方案:
1.忽略这个错误，因为这个错误是不可重复的，发生的概率不大
2.升级到11.1.0.7及其以上版本
3.如果遇到这个错误，可以手工执行dbms_space.auto_space_advisor_job_proc();

老版本PL/SQL Developer操作数据库导致ORA-00600[17113]

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：老版本PL/SQL Developer操作数据库导致ORA-00600[17113]

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在巡检某运营商的计费库时，发现alert日志中发现如下错误

Thu Feb  2 13:54:52 2012
Errors in file /oracle9/app/admin/bill/udump/bill1_ora_35651918.trc:
ORA-00600: internal error code, arguments: [17113], [0x000000000], [], [], [], [], [], []

查看trace文件

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production
ORACLE_HOME = /oracle9/app/product/9.2.0
System name:    AIX
Node name:      zwq_bill_1
Release:        1
Version:        6
Machine:        00F64FF34C00
Instance name: bill1
Redo thread mounted by this instance: 1
Oracle process number: 200
Unix process pid: 35651918, image: oracle@zwq_bill_1 (TNS V1-V3)
*** 2012-02-02 13:54:52.169
*** SESSION ID:(210.1380) 2012-02-02 13:54:52.150
********** Internal heap ERROR 17113 addr=0 *********
******************************************************
HEAP DUMP heap name=""  desc=0
 extent sz=0x0 alt=0 het=0 rec=0 flg=0 opc=0
 parent=0 owner=0 nex=0 xsz=0x0
 Hla: -1
*** 2012-02-02 13:54:52.169
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [17113], [0x000000000], [], [], [], [], [], []
Current SQL statement for this session:
declare
  t_owner varchar2(30);
  t_name  varchar2(30);
  procedure check_mview is
    dummy integer;
  begin
    if ：object_type = 'TABLE' then
      select 1 into dummy
      from sys.all_objects
      where owner = ：object_owner
      and object_name = ：object_name
      and object_type = 'MATERIALIZED VIEW'
      and rownum = 1;
      ：object_type := 'MATERIALIZED VIEW';
    end if;
  exception
    when others then null;
  end;
begin
  :sub_object := null;
  if :deep != 0 then
    begin
      if :part2 is null then
        select constraint_type, owner, constraint_name
          into ：object_type, ：object_owner, ：object_name
          from sys.all_constraints c
         where c.constraint_name = :part1 and c.owner = user
           and rownum = 1;
      else
        select constraint_type, owner, constraint_name, :part3
          into ：object_type, ：object_owner, ：object_name, :sub_object
          from sys.all_constraints c
         where c.constraint_name = :part2 and c.owner = :part1
           and rownum = 1;
      end if;
      if ：object_type = 'P' then ：object_type := 'PRIMARY KEY'; end if;
      if ：object_type = 'U' then ：object_type := 'UNIQUE KEY'; end if;
      if ：object_type = 'R' then ：object_type := 'FOREIGN KEY'; end if;
      if ：object_type = 'C' then ：object_type := 'CHECK CONSTRAINT'; end if;
      return;
    exception
      when no_data_found then null;
    end;
  end if;
  :sub_object := :part2;
  if (:part2 is null) or (:part1 != user) then
    begin
      select object_type, user, :part1
      into ：object_type, ：object_owner, ：object_name
      from sys.all_objects
      where owner = user
      and object_name = :part1
      and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
      and rownum = 1;
      if ：object_type = 'SYNONYM' then
        select s.table_owner, s.table_name
          into t_owner, t_name
          from sys.all_synonyms s
         where s.synonym_name = :part1
           and s.owner = user
           and rownum = 1;
        select o.object_type, o.owner, o.object_name
          into ：object_type, ：object_owner, ：object_name
          from sys.all_objects o
         where o.owner = t_owner
           and o.object_name = t_name
           and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
           and rownum = 1;
      end if;
      :sub_object := :part2;
      if :part3 is not null then
        :sub_object := :sub_object || '.' || :part3;
      end if;
      check_mview;
      return;
    exception
      when no_data_found then null;
    end;
  end if;
  begin
    select s.table_owner, s.table_name
      into t_owner, t_name
      from sys.all_synonyms s
     where s.synonym_name = :part1
       and s.owner = 'PUBLIC'
       and rownum = 1;
    select o.object_type, o.owner, o.object_name
      into ：object_type, ：object_owner, ：object_name
      from sys.all_objects o
     where o.owner = t_owner
       and o.object_name = t_name
       and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
       and rownum = 1;
    check_mview;
    return;
  exception
    when no_data_found then null;
  end;
  :sub_object := :part3;
  begin
    select o.object_type, o.owner, o.object_name
      into ：object_type, ：object_owner, ：object_name
      from sys.all_objects o
     where o.owner = :part1
       and o.object_name = :part2
       and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
       and rownum = 1;
    check_mview;
    return;
  exception
    when no_data_found then null;
  end;
  begin
    if :part2 is null and :part3 is null
    then
      select 'USER', null, :part1
      into ：object_type, ：object_owner, ：object_name
      from sys.all_users u
      where u.username = :part1
      and rownum = 1;
      return;
    end if;
  exception
    when no_data_found then null;
  end;
  begin
    if :part2 is null and :part3 is null and :deep != 0
    then
      select 'ROLE', null, :part1
      into ：object_type, ：object_owner, ：object_name
      from sys.session_roles r
      where r.role = :part1
      and rownum = 1;
      return;
    end if;
  exception
    when no_data_found then null;
  end;
  ：object_owner := null;
  ：object_type := null;
  ：object_name := null;
  :sub_object := null;
end;

通过观察trace的这部分可以知道，是执行这条sql语句导致了ora-600[17113]错误的产生，而这条语句是查询系统对象，初步怀疑是oracle客户端上产生，继续阅读trace文件发现

    SO: 7000000b926f188, type: 4, owner: 7000000bd298b00, flag: INIT/-/-/0x00
    (session) trans: 0, creator: 7000000bd298b00, flag: (41) USR/- BSY/-/-/-/-/-
              DID: 0001-00C8-0009F6EF, short-term DID: 0000-0000-00000000
              txn branch: 0
              oct: 47, prv: 0, sql: 7000000d481ee78, psql: 7000000e086d8d8, user: 567/IBILLAPP
    O/S info: user: Administrator, term: WWW-39A255460E8, ospid: 784:2080, machine: WORKGROUP\WWW-39A255460E8
              program: plsqldev.exe
    application name: PL/SQL Developer, hash value=1190136663
    action name: Main session, hash value=1773317990
    last wait for 'SQL*Net more data from client' blocking sess=0x0 seq=78 wait_time=4
                driver id=54435000, #bytes=34, =0
    temporary object counter: 0

从这里进一步确认是有人使用PL/SQL Developer从WORKGROUP\WWW-39A255460E8的机器上操作该数据库导致，查询mos，果然发现该问题记录[ID 396326.1]。
解决办法：使用新版本的plsql dev工具即可解决问题。

ORA-19583/ORA-27206/ORA-06512

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-19583/ORA-27206/ORA-06512

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

检查alert日志发现近期数据库出现了ORA-19583/ORA-27206/ORA-06512错误

Fri Dec 30 12:22:36 2011
Errors in file /oracle9/app/admin/ykcdb/udump/ykcdb_ora_5390564.trc:
ORA-19583: conversation terminated due to error
ORA-27206: requested file not found in media management catalog
ORA-06512: at "SYS.DBMS_BACKUP_RESTORE", line 625

进一步查看trace文件发现

Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production
ORACLE_HOME = /oracle9/app/product/9.2.0
System name:    AIX
Node name:      OFFON2
Release:        3
Version:        5
Machine:        000A36B8D600
Instance name: ykcdb
Redo thread mounted by this instance: 1
Oracle process number: 79
Unix process pid: 5390564, image: oracle@OFFON2 (TNS V1-V3)
*** 2011-12-30 12:22:36.051
*** SESSION ID:(32.62184) 2011-12-30 12:22:36.023
FATAL ERROR IN TWO-TASK SERVER: error = 12152
*** 2011-12-30 12:22:36.051
ksedmp: internal or fatal error
ORA-19583: conversation terminated due to error
ORA-27206: requested file not found in media management catalog
ORA-06512: at "SYS.DBMS_BACKUP_RESTORE", line 625
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp+0148          bl       ksedst               1025AE55C ?
opitsk+0968          bl       ksedmp               20289E71C ?
opiino+0798          bl       opitsk               000000000 ? 000000000 ?
opiodr+08c8          bl       _ptrgl
opidrv+032c          bl       opiodr               3C00000018 ? 4101C7F40 ?
                                                   FFFFFFFFFFFF810 ? 0A03547C8 ?
sou2o+0028           bl       opidrv               3C0C000000 ? 4A039C2D0

查看mos发现[ID 558993.1]提供了错误原因和解决方案
错误原因：RMAN backup to tape using veritas netbackup，The MML parameters like NB_ORA_CLIENT and NB_ORA_SERV were not given in proper case while configuring channel in RMAN backup.
解决方法：The NB_ORA_CLIENT and NB_ORA_SERV MML parameters are case sensitive. So make sure to give these parameters in same case while allocating or configuring RMAN channel as it is configure in Veritas server.