PL/SQL Developer编译过程引起ora-600[15419]

发表于 2012 年 2 月 6 日 由 惜分飞

数据库版本

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

alert中发现错误

Sun Feb  5 16:32:33 2012
Errors in file /oracle9/app/admin/crm/udump/crm2_ora_2434040.trc:
ORA-00600: internal error code, arguments: [15419], [severe error during PL/SQL execution], [], [], [], [], [], []
ORA-06544: PL/SQL: internal error, arguments: [2603], [], [], [], [], [], [], []
ORA-06553: PLS-707: unsupported construct or internal error [2603]
Sun Feb  5 16:32:33 2012
Trace dumping is performing id=[cdmp_20120205163233]
Sun Feb  5 16:32:45 2012
Errors in file /oracle9/app/admin/crm/udump/crm2_ora_2732864.trc:
ORA-00600: internal error code, arguments: [15419], [severe error during PL/SQL execution], [], [], [], [], [], []
ORA-06544: PL/SQL: internal error, arguments: [2603], [], [], [], [], [], [], []
ORA-06553: PLS-707: unsupported construct or internal error [2603]

trace文件内容

----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedmp+0148          bl       ksedst               1029746FC ?
ksfdmp+0018          bl       01FD4014             
kgeriv+0118          bl       _ptrgl               
kgesiv+0080          bl       kgeriv               FFFFFFFFFFF3AF0 ?
                                                   700000D072A1238 ?
                                                   FFFFFFFFFFF3A80 ?
                                                   3592393502A3F478 ?
                                                   100112038 ?
ksesic1+005c         bl       kgesiv               000000000 ? 700000000003BC8 ?
                                                   2FFFFFEFFFF40C8 ?
                                                   7000002E147A6E0 ? 110002A20 ?
kkxexe+0308          bl       ksesic1              3C3B00003C3B ? 000000001 ?
                                                   000000024 ? 102A1143C ?
                                                   000000009 ? 110362830 ?
                                                   000000002 ? 000007FFF ?
opiexe+274c          bl       kkxexe               1103B5B50 ?
opiodr+08cc          bl       _ptrgl               
rpidrus+008c         bl       opiodr               4103A2A20 ? 300000000 ?
                                                   FFFFFFFFFFF6A70 ? 50000F618 ?
skgmstack+00d0       bl       _ptrgl               
rpidru+0090          bl       skgmstack            0FFFF5F30 ? 1101FAF78 ?
                                                   110006448 ? FFFFFFFFFFF6AD8 ?
                                                   700000C7CF68900 ?
rpiswu2+0358         bl       _ptrgl               
rpidrv+07fc          bl       rpiswu2              700000C386E8718 ? 500000000 ?
                                                   1103B7908 ? FFFFFFFFFFF6E40 ?
                                                   000000000 ? 2E00000000 ?
                                                   1101E9CA8 ? 000000000 ?
rpiexe+0034          bl       rpidrv               5FFFF6A68 ? 400000A20 ?
                                                   FFFFFFFFFFF6A70 ? 00000002E ?
psdevnCallback+00d4  bl       rpiexe               FFFFFFFFFFF71D0 ?
rpiswu2+0358         bl       _ptrgl               
psdevn+0068          bl       rpiswu2              700000C386E8718 ?
                                                   10803D00008 ? 000000000 ?
                                                   102A1BA98 ? 1103B7210 ?
                                                   2E00000008 ? 108000003D0 ?
                                                   000000000 ?
pbedeevn+0350        bl       _ptrgl               
pbeevnd+0198         bcl      pevm_CHSNULL+01c0    FFFFFFFFFFF7370 ? 000000000 ?
                                                   100AE4A10 ? 000000000 ?
                                                   11035F738 ? 11035FB76 ?
pfrrun+0ce4          bl       pbeevnd              1103B7210 ? 1103B7278 ?
                                                   700000CE62919A6 ?
peicnt+01b8          bl       pfrrun               1103B7210 ?
kkxexe+01f8          bl       peicnt               FFFFFFFFFFF8F80 ? 1103B7210 ?
opiexe+274c          bl       kkxexe               1103B7908 ?
opiall0+102c         bl       opiexe               4FFFFFFB0 ? FFFFFFFFFFFBBB0 ?
                                                   FFFFFFFFFFFA0A0 ?
kpoal8+0a78          bl       opiall0              5EFFFFBED4 ? 22FFFFBC18 ?
                                                   FFFFFFFFFFFA5B8 ? 000000000 ?
                                                   FFFFFFFFFFFA508 ? 1102A6498 ?
                                                   5B00000070 ? 24000000007FFF ?
opiodr+08cc          bl       _ptrgl               
ttcpip+0cc4          bl       _ptrgl               
opitsk+0d60          bl       ttcpip               11000CF90 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
opiino+0758          bl       opitsk               000000000 ? 000000000 ?
opiodr+08cc          bl       _ptrgl               
opidrv+032c          bl       opiodr               3C00000018 ? 4101FAF78 ?
                                                   FFFFFFFFFFFF7B0 ? 0A000F350 ?
sou2o+0028           bl       opidrv               3C0C000000 ? 4A00E8B50 ?
                                                   FFFFFFFFFFFF7B0 ?
main+0138            bl       01FD3A28             
__start+0098         bl       main                 000000000 ? 000000000 ?
 
--------------------- Binary Stack Dump ---------------------

………………

SO: 700000c386e8718, type: 4, owner: 700000c3c7779a0, flag: INIT/-/-/0x00
    (session) trans: 0, creator: 700000c3c7779a0, flag: (41) USR/- BSY/-/-/-/-/-
              DID: 0002-02FA-00A04E87, short-term DID: 0000-0000-00000000
              txn branch: 0
              oct: 47, prv: 0, sql: 700000d85e7a0d8, psql: 7000002615ef140, user: 46/DBMARK
    O/S info: user: yuyangah, term: LENOVO-C18854BA, ospid: 5624:5632, machine: WORKGROUP\LENOVO-C18854BA
              program: plsqldev.exe
    application name: PL/SQL Developer, hash value=1190136663
    action name: 测试窗口 - procedure PRC_MARK_UP, hash value=3097949562
    last wait for 'pipe get' blocking sess=0x0 seq=42 wait_time=122
                handle address=700000cd07bddb8, buffer length=1000, timeout=e10
temporary object counter: 0

通过这里可以看出，WORKGROUP\LENOVO-C18854BA机上的yuyangah用户在使用PL/SQL Developer操作PRC_MARK_UP过程的时候，产生了该错误。查询mos[ID 436359.1]发现有相关记录
解决方法：
1.在编译包/过程/函数时，不要选中在pl/sql dev中的Tools > Preferences > Debugger >Add debug information when compiling

2.检查的版本pl/sql dev是否和数据库兼容，建议使用新版本

发表在 Oracle | 标签为 , | 一条评论

查看Aix进程占用内存大小

发表于 2012 年 2 月 5 日 由 惜分飞

内存从大到小排序

-bash-3.00$ ps aux | head -1 ; ps aux | sort -rn +3 | head -10
USER         PID %CPU %MEM   SZ  RSS    TTY STAT    STIME  TIME COMMAND
root     1908868  0.1  2.0 30508 29724      - A      Dec 21 235:34 /usr/sbin/secld
zhldang  2728188  0.0  0.0 1208 1288 pts/15 A    11:20:20  0:00 -bash 
zhldang  1421376  0.0  0.0 2036 1056      - A    11:20:15  0:00 sshd: zhldang@p
zerbo625 2482224  0.0  0.0 1164 1244 pts/24 A    11:04:35  0:00 -bash 
zerbo625 1523730  0.0  0.0 1796 1036      - A    11:04:29  0:00 sshd: zerbo625@
yxq198sm 2429092  0.0  0.0  152  156      - A    12:24:48  0:00 sleep 30 
yxq198sm 1327254  0.0  0.0 1600 1872      - A      Feb 01  0:06 /usr/bin/bsh /u
yanmotia 1274070  0.0  0.0 1164  768 pts/22 A      Dec 23  0:00 -bash 
xinximai 2658402  0.0  0.0 1156  760 pts/83 A      Dec 28  0:00 -bash 
xifengke 2330668  0.0  0.0 1160  756 pts/80 A      Dec 05  0:00 -bash

内存从大到小排序(详细执行命令)

-bash-3.00$ ps -ealf | head -1 ; ps -ealf | sort -rn +9 | head  -10
       F S      UID     PID    PPID   C PRI NI ADDR    SZ    WCHAN    STIME    TTY  TIME CMD
  240001 A     root  188582       1   0  60 20 2823c400 36708        *   Jan 03      - 676:39 /usr/sbin/secldapclntd 
  240001 A     root 1908868       1   0  60 22 6648400 30508        *   Dec 21      - 235:40 /usr/sbin/secldapclntd 
  240001 A     root  331840   86176   0  60 20 205f8400  4088            Sep 15      - 11:04 /usr/lpp/X11/bin/X -D /usr/lib/X11//rgb -T -force :0 -auth /var/dt/A:0-0xukaa 
  240001 A     root 2216054  233648   0  60 20 142a400  3992 f100060002c9e8c8 11:19:59      -  0:00 sshd: zhldang [priv] 
  240001 A     root 1515620  233648   0  60 20 2075f400  3992 f100060003bb0cc8 12:21:48      -  0:00 sshd: undeadbird [priv] 
  240001 A     root 2719848  233648   0  60 20 916a400  3988 f100060002b068c8 13:07:55      -  0:00 sshd: wangyh0313 [priv] 
  240001 A     root 2003106  233648   0  60 20 3428400  3876 f1000600038f94c8 11:02:58      -  0:00 sshd: intrepid [priv] 
  240001 A     root 2560234  233648   0  60 20 44a8400  3816 f1000600018264c8 11:01:54      -  0:00 sshd: xifenfei [priv] 
  240001 A     root 2846944  233648   0  60 20 22c7b400  3784 f100060003b050c8 11:04:12      -  0:00 sshd: zerbo625 [priv] 
  200001 A undeadbi 2220232 1007670   0  60 20 2355f400  3104 f100060001c2c878 13:03:08  pts/9  0:00 /usr/bin/smitty

查找某个进程使用内存

-bash-3.00$ ps aux | head -1 ; ps aux | grep xifenfei
USER         PID %CPU %MEM   SZ  RSS    TTY STAT    STIME  TIME COMMAND
xifenfei 2494532  0.0  0.0 1836 1044      - A    11:02:05  0:00 sshd: xifenfei@
xifenfei 2666546  0.0  0.0 1148 1228 pts/20 A    11:02:10  0:00 -bash 
root     2560234  0.0  0.0 3816 3024      - A    11:01:54  0:00 sshd: xifenfei 
xifenfei 2220230  0.0  0.0  912  940 pts/20 A    13:03:06  0:00 ps aux 
xifenfei 1310908  0.0  0.0  228  240 pts/20 A    13:03:06  0:00 grep xifenfei

查找某个进程使用内存(详细执行命令) 

-bash-3.00$ ps -ealf | head -1 ;ps -ealf|grep xifenfei
       F S      UID     PID    PPID   C PRI NI ADDR    SZ    WCHAN    STIME    TTY  TIME CMD
   40001 A xifenfei 2494532 2560234   0  60 20 37575400  1836          11:02:05      -  0:00 sshd: xifenfei@pts/20 
  200001 A xifenfei 2510946 2666546   1  60 20 14045400   960          12:57:25 pts/20  0:00 ps -ealf 
  200001 A xifenfei 2543730 2666546   0  60 20 10cc6400   228 f100010017976ca0 12:57:25 pts/20  0:00 grep xifenfei 
  240001 A     root 2560234  233648   0  60 20 44a8400  3816 f1000600018264c8 11:01:54      -  0:00 sshd: xifenfei [priv]
发表在 AIX | 评论关闭

ORA-00600[ktspNextL1:4]

发表于 2012 年 2 月 3 日 由 惜分飞

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

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();

发表在 ORA-xxxxx | 标签为 , , | 3 条评论