ORA-xxxxx – 第11页 – Database SOS

alert 日志错误
OER 7451 in Load Indicator : Error Code = OSD-04500:指定了非法选项

Sun Apr 22 11:15:51 2012
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
Sun Apr 22 11:16:01 2012
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
Sun Apr 22 11:16:11 2012
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !

错误信息说明

07451, 00000, "slskstat: unable to obtain load information."
// *Cause:  kstat library returned an error. Possible OS failure
// *Action: Check result code in sercose[0] for more information.

数据库版本信息

SQL> select * from v$version;
BANNER
------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production  <<== 32位数据库
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

操作系统信息

C:\Users\XIFENFEI>systeminfo
主机名:           XIFENFEI-PC
OS 名称:          Microsoft Windows 7 旗舰版
OS 版本:          6.1.7601 Service Pack 1 Build 7601
OS 制造商:        Microsoft Corporation
OS 配置:          独立工作站
OS 构件类型:      Multiprocessor Free
注册的所有人:     XIFENFEI
注册的组织:       Microsoft
产品 ID:          00426-068-8452196-86428
初始安装日期:     2012/2/28, 20:37:08
系统启动时间:     2012/4/22, 9:16:07
系统制造商:       Dell Inc.
系统型号:         Inspiron N4050
系统类型:         x64-based PC       <<==操心系统是win 7 64位
处理器:           安装了 1 个处理器。
                  [01]: Intel64 Family 6 Model 42 Stepping 7 GenuineIntel ~2300 Mhz
BIOS 版本:        Dell Inc. A06, 2011/11/14
Windows 目录:     C:\Windows
系统目录:         C:\Windows\system32
启动设备:         \Device\HarddiskVolume1
系统区域设置:     zh-cn;中文(中国)
输入法区域设置:   zh-cn;中文(中国)
时区:             (UTC+08:00)北京，重庆，香港特别行政区，乌鲁木齐
物理内存总量:     8,100 MB
可用的物理内存:   5,196 MB
虚拟内存: 最大值: 9,122 MB
虚拟内存: 可用:   5,315 MB
虚拟内存: 使用中: 3,807 MB
页面文件位置:     D:\pagefile.sys
域:               WORKGROUP
登录服务器:       \\XIFENFEI-PC

错误原因

Installed 32-bit Oracle database software on a 64-bit MS Windows OS which is not supported.
Note: For the Database software, you can ONLY install the x64 version on MS Windows (x64).
          You can NOT install the 32-bit version Database software on MS Windows (x64).

解决办法

Install 32-bit Oracle database software only on 32-bit MS Windows OS.

ORA-00600[KSSADP1]

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

标题：ORA-00600[KSSADP1]

检查数据库发现ORA-00600[KSSADP1]错误

Thu Apr 19 21:16:45 2012
Errors in file /oracle9/app/admin/crm/udump/crm1_ora_442896.trc:
ORA-00600: internal error code, arguments: [KSSADP1], [], [], [], [], [], [], []
Thu Apr 19 21:16:45 2012
Errors in file /oracle9/app/admin/crm/udump/crm1_ora_442896.trc:
ORA-00600: internal error code, arguments: [KSSADP1], [], [], [], [], [], [], []
Thu Apr 19 21:16:45 2012
Trace dumping is performing id=[cdmp_20120419211645]
Thu Apr 19 21:16:46 2012
Errors in file /oracle9/app/admin/crm/udump/crm1_ora_442896.trc:
ORA-00600: internal error code, arguments: [KSSADP1], [], [], [], [], [], [], []
Thu Apr 19 21:16:47 2012
Errors in file /oracle9/app/admin/crm/udump/crm1_ora_442896.trc:
ORA-00600: internal error code, arguments: [KSSADP1], [], [], [], [], [], [], []

分析crm1_ora_442896.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 = /oracle9/app/product/9.2.0
System name:    AIX
Node name:      zwq_crm1
Release:        3
Version:        5
Machine:        00C420B44C00
Instance name: crm1
Redo thread mounted by this instance: 1
Oracle process number: 2354
Unix process pid: 442896, image: oracle@zwq_crm1 (TNS V1-V3)
*** SESSION ID:(927.39278) 2012-04-19 21:16:45.317
*** 2012-04-19 21:16:45.317
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [KSSADP1], [], [], [], [], [], [], []
----- 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
kgerinv+00e8         bl       _ptrgl
kgesinv+0020         bl       kgerinv              9001000A02B56F8 ?
                                                   9001000A02B9450 ?
                                                   FFFFFFFFFFF8430 ? 000000458 ?
                                                   900000000CBAFA4 ?
ksesin+005c          bl       kgesinv              FFFFFFFFFFF88E0 ? 1101FAF78 ?
                                                   900000000C0ECC0 ? 000010000 ?
                                                   000000002 ?
kssadpm_stage+00c4   bl       ksesin               102973C84 ? 000000000 ?
                                                   00000001E ? 000000000 ?
                                                   000000069 ? 00000000C ?
                                                   000000000 ? 000000000 ?
ksqgel+0138          bl       kssadpm_stage        000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
kcftis+003c          bl       ksqgel               000000000 ? 4029C61E0 ?
                                                   000000002 ? 0FFFFC16C ?
                                                   102A7977C ? 000000000 ?
                                                   000000003 ? 002A36408 ?
kcfhis+001c          bl       kcftis
krbbcc+0238          bl       kcfhis               11043B590 ?
krbpgc+001c          bl       krbbcc
ksmupg+0074          bl       _ptrgl
ksuded+00b8          bl       ksmupg               102924988 ? 000000020 ?
ksupucg+10ec         bl       ksuded               700000C376F5740 ? 000000000 ?
                                                   000000000 ?
opiodr+0474          bl       ksupucg              100000001 ?
ttcpip+0cc4          bl       _ptrgl
opitsk+0d60          bl       ttcpip               11000CF90 ?
                                                   442442216B736800 ?
                                                   FFFFFFFFFFFBF00 ? 1102E04BC ?
                                                   1102D7D20 ? 0000006A0 ?
                                                   1102D83C0 ? 0000006A0 ?
opiino+0758          bl       opitsk               000000000 ? 000000000 ?
opiodr+08cc          bl       _ptrgl
opidrv+032c          bl       opiodr               3C00000018 ? 4101FAF78 ?
                                                   FFFFFFFFFFFF840 ? 0A000F350 ?
sou2o+0028           bl       opidrv               3C0C000000 ? 4A00E8B50 ?
                                                   FFFFFFFFFFFF840 ?
main+0138            bl       01FD3A28
__start+0098         bl       main                 000000000 ? 000000000 ?
--------------------- Binary Stack Dump ---------------------
Cursor Dump:
----------------------------------------
Cursor 1 (110360418): CURROW  curiob: 110369b78
 curflg: 46 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: select nvl(max(cpmid),0) from x$kcccp                                        where cpsta = 2
 child pin: 0, child lock: 700000d9b9c5bb8, parent lock: 700000d088e0fa0
 xscflg: 1100024, parent handle: 70000031d588d88, xscfl2: 4040401
 bhp size: 160/600
----------------------------------------
Cursor 2 (110360468): CURBOUND  curiob: 1103656f0
 curflg: c7 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: SELECT SUBSTR(VERSION,1,INSTR(VERSION,'.') - 1 )   FROM V$INSTANCE
 child pin: 0, child lock: 700000d21e60930, parent lock: 700000327837ce0
 xscflg: 141024, parent handle: 700000304e2f020, xscfl2: 4000401
 bhp size: 160/600
----------------------------------------
Cursor 3 (1103604b8): CURBOUND  curiob: 1103b6aa8
 curflg: c7 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: SELECT SUBSTR(VERSION,1 + INSTR(VERSION,'.',1,1) ,INSTR(VERSION,'.',1,2) -
 INSTR(VERSION,'.',1,1)  - 1 )   FROM V$INSTANCE
 child pin: 0, child lock: 700000d5e382ee8, parent lock: 700000c93581d40
 xscflg: 141024, parent handle: 700000d73daa1c0, xscfl2: 4000401
 bhp size: 160/600
----------------------------------------
Cursor 4 (110360508): CURBOUND  curiob: 1103b66b8
 curflg: c7 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: SELECT SUBSTR(VERSION,1 + INSTR(VERSION,'.',1,2) ,INSTR(VERSION,'.',1,3) -
 INSTR(VERSION,'.',1,2)  - 1 )   FROM V$INSTANCE
 child pin: 0, child lock: 700000d16de7978, parent lock: 700000c44059d30
 xscflg: 141024, parent handle: 700000259c4a700, xscfl2: 4000401
 bhp size: 160/600
----------------------------------------
Cursor 5 (110360558): CURBOUND  curiob: 1103b3868
 curflg: 46 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: SELECT SYSDATE   FROM SYS.DUAL
 child pin: 0, child lock: 700000d589cea48, parent lock: 70000026b311fb0
 xscflg: 100024, parent handle: 700000d2eaee328, xscfl2: 4600409
 bhp size: 280/632
----------------------------------------
Cursor 6 (1103605a8): CURBOUND  curiob: 1103b3408
 curflg: 46 curpar: 0 curusr: 0 curses 700000c376f5740
 cursor name: SELECT TO_CHAR(SYSDATE,'YYYY','NLS_CALENDAR=Gregorian'),TO_CHAR(SYSDATE,'MM','NLS_CALENDAR=Gregorian'),
TO_CHAR(SYSDATE,'DD','NLS_CALENDAR=Gregorian') FROM X$DUAL
 child pin: 0, child lock: 70000033f1753c8, parent lock: 700000db8c6dd18
 xscflg: 100024, parent handle: 700000cbc6ad8b0, xscfl2: 4600409
 bhp size: 160/600
End of cursor dump
ksedmp: no current context area
----- Dump of the Fixed PGA -----

找到相关文档Note:262996.1,经过分析,产生错误的原因是由在本版本的数据库中SGA管理中存在的漏洞造成,但此错误没有对数据库的数据造成损坏及性能影响.

处理建议
1．当前版本ORACLE已经不再提供补丁支持,建议升级到高版本解决(有sr中介绍10.2中解决);
2．由于此报错并没有对数据库的数据及性能造成损坏及影响,可以忽此错误。

ORA-01075: you are currently logged on

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

标题：ORA-01075: you are currently logged on

rm删除文件后alert中出现错误

Mon Apr 16 21:36:59 2012
Errors in file /home/oracle/oracle/admin/XGS/bdump/xgs_j000_1349.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/home/oracle/oracle/oradata/XGS/sysaux01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/home/oracle/oracle/oradata/XGS/sysaux01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/home/oracle/oracle/oradata/XGS/undotbs02.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

数据库进程还在运行

oracle     779     1  0 21:21 ?        00:00:01 ora_pmon_XGS
oracle     781     1  0 21:21 ?        00:00:10 ora_psp0_XGS
oracle     783     1  0 21:21 ?        00:00:00 ora_mman_XGS
oracle     785     1  0 21:21 ?        00:00:00 ora_dbw0_XGS
oracle     787     1  0 21:21 ?        00:00:00 ora_lgwr_XGS
oracle     789     1  0 21:21 ?        00:00:00 ora_ckpt_XGS
oracle     791     1  0 21:21 ?        00:00:00 ora_smon_XGS
oracle     793     1  0 21:21 ?        00:00:00 ora_reco_XGS
oracle     795     1  0 21:21 ?        00:00:00 ora_cjq0_XGS
oracle     797     1  0 21:21 ?        00:00:01 ora_mmon_XGS
oracle     799     1  0 21:21 ?        00:00:00 ora_mmnl_XGS
oracle     801     1  0 21:21 ?        00:00:00 ora_d000_XGS
oracle     803     1  0 21:21 ?        00:00:00 ora_s000_XGS

尝试登陆数据库

[oracle@dbtest ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 16 21:40:06 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
ERROR:
ORA-01075: you are currently logged on
Enter user-name: sys
Enter password:
ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/home/oracle/oracle/oradata/XGS/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/home/oracle/oracle/oradata/XGS/undotbs02.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

问题原因

Internal triggers are trying to fire but one or more datafiles for the SYSAUX tablespace is offline,
this is preventing the database from allowing new connections.
NOTE: At this point, you cannot connect to verify the status in V$DATAFILE,
but you may find an indication of the offline datafile(s) in the alert.log file.
For example:
In one case, a media problem occurred which made disks unavailable.
This caused several files to be taken offline automatically including a SYSAUX datafile.

解决方法
kill进程,重启数据库到mount状态,然后根据特定情况恢复数据库或者online相关文件

记录一次ORA-00600[kdsgrp1]分析

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

标题：记录一次ORA-00600[kdsgrp1]分析

数据库版本

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

找出报错对象

--方法1
*** SESSION ID:(795.16405) 2012-04-05 09:36:11.958
            row 080095ee.26 continuation at
            file# 32 block# 38382 slot 39 not found
**************************************************
KDSTABN_GET: 0 ..... ntab: 1
curSlot: 39 ..... nrows: 19
**************************************************
SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME
  2    FROM DBA_EXTENTS A
  3   WHERE FILE_ID = &amp;amp;FILE_ID
  4     AND &amp;amp;BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
Enter value for file_id: 32
old   3:  WHERE FILE_ID = &amp;amp;FILE_ID
new   3:  WHERE FILE_ID = 32
Enter value for block_id: 38382
old   4:    AND &amp;amp;BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
new   4:    AND 38382 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE       TABLESPACE_NAME                PARTITION_NAME
------------------ ------------------------------ ------------------------------
AHV8
TBL_IVR_LOG
TABLE PARTITION    CSS_PARTITION                  IVR_LOG_2012_MONTH04
--方法2
*** 2012-04-05 09:36:11.965
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], []
Current SQL statement for this session:
INSERT INTO TBL_CONTACT_INFO_FAILED_TMP
select * from TBL_IVR_LOG
SO: 70000017f954f50, type: 4, owner: 70000017f65a840, flag: INIT/-/-/0x00
(session) sid: 795 trans: 70000017464a1e8, creator: 70000017f65a840, flag: (40110041) USR/- BSY/-/-/-/-/-
              DID: 0002-0067-000305BD, short-term DID: 0002-0067-000305BE
              txn branch: 0
              oct: 2, prv: 0, sql: 70000015180ee98, psql: 700000180d67550, user: 49/AHV8
service name: SYS$USERS
O/S info: user: oracle10, term: UNKNOWN, ospid: 12976218, machine: zwq_kfdb2
              program: oracle@zwq_kfdb2 (J002)
last wait for 'db file sequential read' blocking sess=0x0 seq=226 wait_time=17071 seconds since wait started=1
                file#=20, block#=95ee, blocks=1
--方法3
Block header dump:  0x080095ee
 Object id on Block? Y
 seg/obj: 0x11eeb  csc: 0x6f2.848e814  itc: 2  flg: E  typ: 1 - DATA
     brn: 1  bdba: 0x7c09c89 ver: 0x01 opc: 0
     inc: 0  exflg: 0
SQL> select to_number('11eeb','xxxxxxxx') from dual;
TO_NUMBER('11EEB','XXXXXXXX')
-----------------------------
                        73451
SQL> select owner,object_name,subobject_name,object_type from dba_objects where data_object_id='73451';
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME                 OBJECT_TYPE
------------------------------ -------------------
AHV8
TBL_IVR_LOG
IVR_LOG_2012_MONTH04           TABLE PARTITION

验证是否真的坏块

SQL> select name from v$datafile where file#=32;
NAME
------------------------------------------------------
/dev/rdb1_data27
[zwq_kfdb2:/home/oraeye]dbv file='/dev/rdb1_data27' blocksize=8192
DBVERIFY: Release 10.2.0.4.0 - Production on Fri Apr 13 15:33:10 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /dev/rdb1_data27
DBVERIFY - Verification complete
Total Pages Examined         : 1048448
Total Pages Processed (Data) : 947357
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 4756
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 96335
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 297329920 (1778.297329920)
SQL> select count(*) from AHV8.TBL_IVR_LOG partition(IVR_LOG_2012_MONTH04);
  COUNT(*)
----------
   8798030

总结：很明显这次出现这个问题，因为内存中出现坏块导致，经过一段时间buffer cache中的坏块内容已经被老化，所以现在不能重现(甚至不用做任何操作)。如果内存中出现了坏块，而且还没有被老化掉，可以刷新data buffer;如果是数据块出现坏块，根据实际情况决定处理

ORA-00600[729]分析和处理方法

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

标题：ORA-00600[729]分析和处理方法

alert中ORA-00600[729]

Fri Apr  6 04:30:04 2012
Errors in file /oracle9/app/admin/crm/udump/crm2_ora_2548236.trc:
ORA-00600: internal error code, arguments: [729], [1067976], [space leak], [], [], [], [], []

a. the first bracketed number [729] is the common argument for space leak problems.
b. the second number [1067976] is the number of bytes leaked by the error.
c. the third argument is always [space leak].

分析trace文件

*** 2012-04-06 04:30:04.656
*** SESSION ID:(1361.35607) 2012-04-06 04:30:04.648
******** ERROR: UGA memory leak detected 1067976 ********
******************************************************
HEAP DUMP heap name="session heap"  desc=1103a05f0

a. the memory was leaked from the UGA area
b. the amount leaked is reported again in the text (1067976 bytes).
c. the above few lines describe this dump as SESSION HEAP with the descriptor 0x1103a05f0.

计算泄露内存大小

******************************************************
HEAP DUMP heap name="session heap"  desc=1103a05f0
 extent sz=0xff80 alt=32767 het=32767 rec=0 flg=3 opc=3
 parent=110009628 owner=700000c3b6f5620 nex=0 xsz=0xff80
EXTENT 0 addr=1107dbf50
  Chunk        1107dbf60 sz=    65392    free      "               "
EXTENT 1 addr=1107cbf50
  Chunk        1107cbf60 sz=    65392    free      "               "
EXTENT 2 addr=110541da0
  Chunk        110541db0 sz=    61312    free      "               "
EXTENT 3 addr=11062ae88
  Chunk        11062ae98 sz=   266264    freeable  "kllcqgf:kllsltb"
EXTENT 4 addr=1105dae88
  Chunk        1105dae98 sz=   266264    freeable  "kllcqgf:kllsltb"
EXTENT 5 addr=110550d48
  Chunk        110550d58 sz=   266264    freeable  "kllcqgf:kllsltb"
EXTENT 6 addr=110500d48
  Chunk        110500d58 sz=   266264    freeable  "kllcqgf:kllsltb"
EXTENT 7 addr=1104e1df0
  Chunk        1104e1e00 sz=      200    perm      "perm           "  alo=200
  Chunk        1104e1ec8 sz=    65192    free      "               "
EXTENT 8 addr=1104c1df0
  Chunk        1104c1e00 sz=    40720    perm      "perm           "  alo=40720
  Chunk        1104cbd10 sz=       56    free      "               "
  Chunk        1104cbd48 sz=      408    freeable  "kcbl_structure_"
  Chunk        1104cbee0 sz=     6952    free      "               "
  Chunk        1104cda08 sz=     2424    freeable  "kllcqc:kllcqslt"
  Chunk        1104ce380 sz=    14832    free      "               "
EXTENT 9 addr=1104d1df0
  Chunk        1104d1e00 sz=    65392    free      "               "
EXTENT 10 addr=1104b1df0
  Chunk        1104b1e00 sz=      544    free      "               "
  Chunk        1104b2020 sz=       88    freeable  "kllcqc:kllcq   "
  Chunk        1104b2078 sz=    64760    free      "               "
EXTENT 11 addr=110427390
  Chunk        1104273a0 sz=    65392    free      "               "
EXTENT 12 addr=110417390
  Chunk        1104173a0 sz=    65392    free      "               "
EXTENT 13 addr=110407390
  Chunk        1104073a0 sz=    65392    free      "               "
EXTENT 14 addr=1103f7390
  Chunk        1103f73a0 sz=    65392    free      "               "
EXTENT 15 addr=1103e7390
  Chunk        1103e73a0 sz=    65392    free      "               "
EXTENT 16 addr=1103d7390
  Chunk        1103d73a0 sz=    65392    free      "               "
EXTENT 17 addr=1103c7390
  Chunk        1103c73a0 sz=      408    free      "               "
  Chunk        1103c7538 sz=     2232    perm      "perm           "  alo=2232
  Chunk        1103c7df0 sz=    62752    free      "               "
EXTENT 18 addr=1103b7390
  Chunk        1103b73a0 sz=    65392    free      "               "
EXTENT 19 addr=110370080
  Chunk        110370090 sz=     2008    perm      "perm           "  alo=2008
  Chunk        110370868 sz=    63384    free      "               "
EXTENT 20 addr=110360098
  Chunk        1103600a8 sz=    20424    perm      "perm           "  alo=20424
  Chunk        110365070 sz=    44944    free      "               "
Total heap size    =  2172616
FREE LISTS:
 Bucket 0 size=56
  Chunk        1104cbd10 sz=       56    free      "               "
 Bucket 1 size=88
 Bucket 2 size=152
 Bucket 3 size=168
 Bucket 4 size=280
  Chunk        1103c73a0 sz=      408    free      "               "
 Bucket 5 size=432
 Bucket 6 size=536
  Chunk        1104b1e00 sz=      544    free      "               "
 Bucket 7 size=1048
 Bucket 8 size=2072
 Bucket 9 size=4120
  Chunk        1104cbee0 sz=     6952    free      "               "
 Bucket 10 size=8216
  Chunk        1104ce380 sz=    14832    free      "               "
 Bucket 11 size=16408
 Bucket 12 size=32792
  Chunk        110365070 sz=    44944    free      "               "
  Chunk        110370868 sz=    63384    free      "               "
  Chunk        1104d1e00 sz=    65392    free      "               "
  Chunk        1103b73a0 sz=    65392    free      "               "
  Chunk        1103c7df0 sz=    62752    free      "               "
  Chunk        1103d73a0 sz=    65392    free      "               "
  Chunk        1103f73a0 sz=    65392    free      "               "
  Chunk        1104073a0 sz=    65392    free      "               "
  Chunk        1104b2078 sz=    64760    free      "               "
  Chunk        1103e73a0 sz=    65392    free      "               "
  Chunk        1104e1ec8 sz=    65192    free      "               "
  Chunk        1104273a0 sz=    65392    free      "               "
  Chunk        1104173a0 sz=    65392    free      "               "
  Chunk        1107cbf60 sz=    65392    free      "               "
  Chunk        110541db0 sz=    61312    free      "               "
  Chunk        1107dbf60 sz=    65392    free      "               "
 Bucket 13 size=65560
 Bucket 14 size=131096
 Bucket 15 size=262168
 Bucket 16 size=524312
 Bucket 17 size=2097176
Total free space   =  1039056
UNPINNED RECREATABLE CHUNKS (lru first):
PERMANENT CHUNKS:
  Chunk        1104e1e00 sz=      200    perm      "perm           "  alo=200
  Chunk        1104c1e00 sz=    40720    perm      "perm           "  alo=40720
  Chunk        1103c7538 sz=     2232    perm      "perm           "  alo=2232
  Chunk        110370090 sz=     2008    perm      "perm           "  alo=2008
  Chunk        1103600a8 sz=    20424    perm      "perm           "  alo=20424
Permanent space    =    65584
******************************************************

FREEABLE and RECREATABLE chunks总和等于1067976 byte(leaked memory)

会话状态分析

*** 2012-04-06 04:30:04.658
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [729], [1067976], [space leak], [], [], [], [], []
----- 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               000000001 ? 000000002 ?
                                                   1100610D0 ? 000000000 ?
                                                   00000000A ?
ksesic2+005c         bl       kgesiv               FFFFFFFFFFF9320 ? 1101FAF78 ?
                                                   110006308 ? 1103A0818 ?
                                                   000000009 ?
ksmuhe+026c          bl       ksesic2              2D9000002D9 ? 000000000 ?
                                                   000104BC8 ? 000000001 ?
                                                   00000000A ? 103164968 ?
                                                   12E0BE826D694B2F ?
                                                   000000000 ?
ksmugf+0214          bl       ksmuhe               110002A20 ? 110061238 ?
                                                   000000009 ? 102975DE8 ?
ksuxds+170c          bl       ksmugf               000000000 ? 020000000 ?
                                                   1029754D0 ?
ksudel+006c          bl       ksuxds               700000C3B6F5620 ? 100000001 ?
opilof+03dc          bl       01FD427C             <--表示logoff
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: 700000c3b6f5620, type: 4, owner: 700000c3c987a18, flag: INIT/-/-/0x00
--flag: (41) USR/- BSY/-/-/DEL/-/- shows that the session has been deleted
    (session) trans: 0, creator: 700000c3c987a18, flag: (41) USR/- BSY/-/-/DEL/-/-
              DID: 0002-0927-01D67CAD, short-term DID: 0000-0000-00000000
              txn branch: 0
              oct: 0, prv: 0, sql: 700000caf2c0e30, psql: 700000caf2c0e30, user: 52/MONITOR
    O/S info: user: oracrm, term: , ospid: 1490968, machine: zwq_crm2
              program: exp@zwq_crm2 (TNS V1-V3)
    last wait for 'SQL*Net message from client' blocking sess=0x0 seq=59222 wait_time=1537
                driver id=54435000, #bytes=1, =0
    temporary object counter: 0
    ----------------------------------------

a.在logoff的时候发生UGA中的session heap发生内存泄露
b.该进程是一个exp导出数据库程序，并且该程序已经被释放

出现ORA-00600[729]原因

Memory leak problems generally occur when Oracle is trying to free memory allocated to a process.
The memory leak dump is generally discovered during session logoff,
when Oracle frees the heaps that are allocated for the user process.
When a user connects to Oracle, a user process is created and at that time the heap is allocated.
Every process will have its own memory heap.
The memory is organized in to heaps and every heap consists of one or more extents.
Each extent contains a series of contiguous memory chunks, and these chunks can be
either FREE or ALLOCATED. The Generic Heap Manager takes care of allocating and deallocating
the memory chunks, with the help of FREE LISTS and LRU LISTS.
Chunk types are as follows:
1. FREE
2. FREEABLE
3. RECREATABLE
4. PERMANENT
5. FREEABLE WITH MARK
It is not mandatory that each extent contain only one type of chunk.
Extents can contain various types of chunks. When processes require memory chunks,
they are allocated as needed. Oracle keeps track of the amount of memory allocated for the process internally.
When the process terminates, all of the memory that has been allocated for the process is automatically released.
When the memory is released the allocated heaps are freed. Generally,
when the heap is freed the only chunks that the process should identify
as allocated are the PERMANENT chunks and FREE chunks on the freelist.
If the process finds there are still FREEABLE or RECREATABLE chunks remaining,
then the process has not properly deallocated the memory.
This situation is considered a space leak.

ORA-00600[729]处理方案

1. If there are no other errors reported at the same time,
this may be a case where the error was a rare occurrence and can be safely ignored.
As a rule of thumb, leaks less than 90,000 bytes in size are considered to be of low significance.
The solution in this case is to set event 10262 (see below).
a. Set the following event in init.ora parameter file.
   This example disables reporting for space leaks less than 90000 bytes:
event = "10262 trace name context forever, level 90000"
b. Stop and restart the database.
If the level is set to 1, space leak checking is disabled.
This is not advised because large memory leaks will be missed.
If the event is set to a value greater than 1,
any space leak up to the number specified in the event is ignored.
2. Is the leak in the SGA? The alert.log should be reviewed for additional
errors such as ORA-4030 and ORA-4031 to ensure there are no additional
problems with the shared pool or operating system memory.
3. Does the error reproduce with a given task? If so, this is
a case that should be investigated further because the leak could be a known bug.
See Note 31056.1 ORA-600 [729] UGA Space Leak for a list of known bugs and fixes.

参考:Understanding and Diagnosing ORA-600 [729] Space Leak Errors [ID 403584.1]

ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated

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

标题：ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated

出现如下错误(ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated)

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: 30
Unix process pid: 46531060, image: oracle@zwq_bill_1 (J000)
*** SESSION ID:(218.47085) 2012-04-02 19:30:45.561
*** 2012-04-02 19:30:45.561
ORA-12012: error on auto execute of job 1
ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated
ORA-06512: at "PERFSTAT.STATSPACK", line 1361
ORA-06512: at "PERFSTAT.STATSPACK", line 2471
ORA-06512: at "PERFSTAT.STATSPACK", line 91
ORA-06512: at line 1

这个是oracle的一个Bug 2784796,提供解决方法有
1.run the statspack at level 0
2.restart the instance
3.set cursor sharing to exact (probably not feasible)
4.禁用主键，创建合适非唯一index

ALTER TABLE PERFSTAT.STATS$SQL_SUMMARY MODIFY
CONSTRAINT STATS$SQL_SUMMARY_PK DISABLE NOVALIDATE;

5.修改STATS$V_$SQLXS视图
分析思路如下:
1)根据主键冲突找到主键包含列(spctab.sql)

create table          STATS$SQL_SUMMARY
(snap_id              number(6)        not null
,dbid                 number           not null
,instance_number      number           not null
,text_subset          varchar2(31)     not null
,sql_text             varchar2(1000)
,sharable_mem         number
,sorts                number
,module               varchar2(64)
,loaded_versions      number
,fetches              number
,executions           number
,loads                number
,invalidations        number
,parse_calls          number
,disk_reads           number
,buffer_gets          number
,rows_processed       number
,command_type         number
,address              raw(8)
,hash_value           number
,version_count        number
,cpu_time             number
,elapsed_time         number
,outline_sid          number
,outline_category     varchar2(64)
,child_latch          number
--注意下面5列构成主键
,constraint STATS$SQL_SUMMARY_PK primary key
    (snap_id, dbid, instance_number, hash_value, text_subset)
 using index tablespace &&tablespace_name
   storage (initial 1m next 1m pctincrease 0)
,constraint STATS$SQL_SUMMARY_FK foreign key (snap_id, dbid, instance_number)
                references STATS$SNAPSHOT on delete cascade
)tablespace &&tablespace_name
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;

2)找到该表插入数据(spcpkg.sql)

insert into stats$sql_summary
            ( snap_id
            , dbid
            , instance_number
            , text_subset
            , sharable_mem
            , sorts
            , module
            , loaded_versions
            , fetches
            , executions
            , loads
            , invalidations
            , parse_calls
            , disk_reads
            , buffer_gets
            , rows_processed
            , command_type
            , address
            , hash_value
            , version_count
            , cpu_time
            , elapsed_time
            , outline_sid
            , outline_category
            , child_latch
            )
       select l_snap_id
            , p_dbid
            , p_instance_number
            , substrb(sql_text,1,31)
            , sharable_mem
            , sorts
            , module
            , loaded_versions
            , fetches
            , executions
            , loads
            , invalidations
            , parse_calls
            , disk_reads
            , buffer_gets
            , rows_processed
            , command_type
            , address
            , hash_value
            , version_count
            , cpu_time
            , elapsed_time
            , outline_sid
            , outline_category
            , child_latch
         from stats$v$sqlxs
        where is_obsolete = 'N'
          and (   buffer_gets   > l_buffer_gets_th
               or disk_reads    > l_disk_reads_th
               or parse_calls   > l_parse_calls_th
               or executions    > l_executions_th
               or sharable_mem  > l_sharable_mem_th
               or version_count > l_version_count_th
              );

3)找出stats$v$sqlxs对象(spcusr.sql)

create or replace view STATS$V_$SQLXS as
select max(sql_text)        sql_text
     , sum(sharable_mem)    sharable_mem
     , sum(sorts)           sorts
     , min(module)          module
     , sum(loaded_versions) loaded_versions
     , sum(fetches)         fetches
     , sum(executions)      executions
     , sum(loads)           loads
     , sum(invalidations)   invalidations
     , sum(parse_calls)     parse_calls
     , sum(disk_reads)      disk_reads
     , sum(buffer_gets)     buffer_gets
     , sum(rows_processed)  rows_processed
     , max(command_type)    command_type
     , address              address
     , hash_value           hash_value
     , count(1)             version_count
     , sum(cpu_time)        cpu_time
     , sum(elapsed_time)    elapsed_time
     , max(outline_sid)     outline_sid
     , max(outline_category) outline_category
     , max(is_obsolete)     is_obsolete
     , max(child_latch)     child_latch
  from v$sql
 group by hash_value, address;
create or replace public synonym STATS$V$SQLXS for STATS$V_$SQLXS;

4)通过这里可以看出，要是的STATS$SQL_SUMMARY主键不重复，只要是的STATS$V_$SQLXS查询出来的记录唯一，所以解决方案就是在STATS$V_$SQLXS视图中增加下列条件，确保查询出来的记录唯一，从而不会发生主键冲突

where
( plan_hash_value > 0
or executions > 0
or parse_calls > 0
or disk_reads > 0
or buffer_gets > 0
)

该bug在10g中修复，对于不能及时升级的数据库，建议采用第五种方法解决问题，比较治标治本，对业务基本上无影响

linux中不能ping通hostname可能存在问题

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

标题：linux中不能ping通hostname可能存在问题

不能ping通hostname

[oracle@xifenfei ~]$ hostname
xifenfei
[oracle@xifenfei ~]$ ping xifenfei
ping: unknown host xifenfei
[oracle@xifenfei ~]$ nslookup
> xifenfei
;; connection timed out; no servers could be reached
> exit
[oracle@xifenfei ~]$ more /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1    xifenfei1 localhost.localdomain localhost

10G中存在问题

[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 3 01:54:22 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.
--sqlplus连接非常慢
SQL> startup
ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], []
--ORA-00600[keltnfy-ldmInit]错误

11G中存在问题

[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 3 02:02:29 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected.
SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1521))'
--因为不能ping通xifenfei，提示LOCAL_LISTENER错误
--修改pfile文件
local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))'
--启动数据库
SQL> startup pfile='/tmp/pfile'
ORACLE instance started.
Total System Global Area  368263168 bytes
Fixed Size                  1345016 bytes
Variable Size             306186760 bytes
Database Buffers           54525952 bytes
Redo Buffers                6205440 bytes
Database mounted.
Database opened.
[oracle@xifenfei ~]$ ps -ef|grep ora_
oracle    5960     1  0 02:09 ?        00:00:00 ora_pmon_ora11g
oracle    5964     1  0 02:09 ?        00:00:00 ora_psp0_ora11g
oracle    5970     1  1 02:09 ?        00:00:00 ora_vktm_ora11g
oracle    5976     1  0 02:09 ?        00:00:00 ora_gen0_ora11g
oracle    5980     1  0 02:09 ?        00:00:00 ora_diag_ora11g
oracle    5984     1  0 02:09 ?        00:00:00 ora_dbrm_ora11g
oracle    5988     1  0 02:09 ?        00:00:00 ora_dia0_ora11g
oracle    5992     1  0 02:09 ?        00:00:00 ora_mman_ora11g
oracle    5996     1  0 02:09 ?        00:00:00 ora_dbw0_ora11g
oracle    6000     1  0 02:09 ?        00:00:00 ora_lgwr_ora11g
oracle    6004     1  0 02:09 ?        00:00:00 ora_ckpt_ora11g
oracle    6008     1  0 02:09 ?        00:00:00 ora_smon_ora11g
oracle    6012     1  0 02:09 ?        00:00:00 ora_reco_ora11g
oracle    6016     1  0 02:09 ?        00:00:00 ora_mmon_ora11g
oracle    6020     1  0 02:09 ?        00:00:00 ora_mmnl_ora11g
oracle    6028     1  0 02:09 ?        00:00:00 ora_s000_ora11g
oracle    6055     1  0 02:10 ?        00:00:00 ora_p000_ora11g
oracle    6059     1  0 02:10 ?        00:00:00 ora_p001_ora11g
oracle    6063     1  0 02:10 ?        00:00:00 ora_arc0_ora11g
oracle    6069     1  0 02:10 ?        00:00:00 ora_arc1_ora11g
oracle    6073     1  0 02:10 ?        00:00:00 ora_arc2_ora11g
oracle    6077     1  0 02:10 ?        00:00:00 ora_arc3_ora11g
oracle    6081     1  0 02:10 ?        00:00:00 ora_qmnc_ora11g
oracle    6089     1  0 02:10 ?        00:00:00 ora_q000_ora11g
oracle    6093     1  0 02:10 ?        00:00:00 ora_q001_ora11g
oracle    6141     1  0 02:11 ?        00:00:00 ora_d000_ora11g
oracle    6145     1  2 02:11 ?        00:00:00 ora_cjq0_ora11g
--数据库启动正常
[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 3 02:10:37 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected.
SQL> show parameter local_listener;
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
--sqlplus不能操作，而且sqlplus登录非常慢

监听异常

--监听配置文件
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = ora11g)
     (ORACLE_HOME = /u01/oracle/oracle/product/11.2.0/db_1)
     (SID_NAME = ora11g)
    )
  )
--启动监听
[oracle@xifenfei ~]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 03-APR-2012 02:19:52
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Starting /u01/oracle/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/oracle/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/oracle/diag/tnslsnr/xifenfei/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.10)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.10)(PORT=1522)))
--一直处于等待状态
--客户端登录
C:\Users\XIFENFEI>sqlplus sys/xifenfei@192.168.1.10:1522/ora11g
SQL*Plus: Release 11.2.0.3.0 Production on 星期二 4月 3 12:48:15 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
--一直hang住
[root@xifenfei admin]# netstat -an|grep 1522
tcp        0      0 192.168.1.10:1522           0.0.0.0:*                   LISTEN
tcp      260      0 192.168.1.10:1522           192.168.1.1:51977           ESTABLISHED
tcp        0      0 192.168.1.10:24317          192.168.1.10:1522           ESTABLISHED
tcp      198      0 192.168.1.10:1522           192.168.1.10:24317          ESTABLISHED
--这里显示已经连接

建议:在修改主机名时要慎重，修改的时候要确保/etc/hosts和/etc/sysconfig/network文件都被正确修改

永久表空间出现临时段不能扩展原因探讨

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

标题：永久表空间出现临时段不能扩展原因探讨

数据库版本

SQL> select * from v$version;
BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

创建5M测试表空间

SQL> CREATE TABLESPACE T_1652 DATAFILE '/tmp/t_1652_01.dbf' size 5M
  2  AUTOEXTEND OFF LOGGING PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  3  SEGMENT SPACE MANAGEMENT AUTO blocksize 8192;
Tablespace created.

测试CTAS

SQL> create table CHF.T_XIFENFEI TABLESPACE T_1652 as
  2  select LPAD('XIFENFEI',1024,'F') "C_XFF" from  dual connect by level <=3500;
create table CHF.T_XIFENFEI TABLESPACE T_1652 as
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace T_1652
SQL> create table CHF.T_XIFENFEI TABLESPACE T_1652 as
  2  select LPAD('XIFENFEI',1024,'F') "C_XFF" from  dual connect by level <=3000;
Table created.

测试CREATE INDEX

SQL> create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
  2  tablespace t_1652;
create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
                                   *
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace T_1652
SQL> Select MAX(d.bytes) total_bytes,
  2         nvl(SUM(f.Bytes), 0) free_bytes,
  3         d.file_name,
  4         MAX(d.bytes) - nvl(SUM(f.bytes), 0) used_bytes,
  5  from   DBA_FREE_SPACE f , DBA_DATA_FILES d
  6  where  f.tablespace_name(+) = d.tablespace_name
  7  and    f.file_id(+) = d.file_id
  8  and    d.tablespace_name = 'T_1652'
  9  group by d.file_name;
TOTAL_BYTES FREE_BYTES FILE_NAME                 USED_BYTES
----------- ---------- ------------------------- ----------
    5242880          0 /tmp/t_1652_01.dbf           5242880
SQL> drop table chf.t_xifenfei purge;
Table dropped.
SQL> create table CHF.T_XIFENFEI TABLESPACE T_1652 as
  2  select LPAD('XIFENFEI',1024,'F') "C_XFF" from  dual connect by level <=2000;
Table created.
SQL> Select MAX(d.bytes) total_bytes,
  2         nvl(SUM(f.Bytes), 0) free_bytes,
  3         d.file_name,
  4         MAX(d.bytes) - nvl(SUM(f.bytes), 0) used_bytes,
  5  from   DBA_FREE_SPACE f , DBA_DATA_FILES d
  6  where  f.tablespace_name(+) = d.tablespace_name
  7  and    f.file_id(+) = d.file_id
  8  and    d.tablespace_name = 'T_1652'
  9  group by d.file_name;
TOTAL_BYTES FREE_BYTES FILE_NAME                 USED_BYTES
----------- ---------- ------------------------- ----------
    5242880    1048576 /tmp/t_1652_01.dbf           4194304
SQL> create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
  2  tablespace t_1652;
create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
                                   *
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace T_1652
SQL> ALTER DATABASE DATAFILE '/tmp/t_1652_01.dbf' RESIZE 10M;
Database altered.
SQL> create index chf.i_xifenfei on chf.t_xifenfei(c_xff)
  2  tablespace t_1652;
Index created.

测试MOVE

SQL> drop table chf.t_xifenfei purge;
Table dropped.
SQL> create table CHF.T_XIFENFEI TABLESPACE T_1652 as
  2  select LPAD('XIFENFEI',1024,'F') "C_XFF" from  dual connect by level <=3500;
Table created.
SQL> alter table chf.t_xifenfei move;
alter table chf.t_xifenfei move
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace T_1652
SQL> Select MAX(d.bytes) total_bytes,
  2         nvl(SUM(f.Bytes), 0) free_bytes,
  3         d.file_name,
  4         MAX(d.bytes) - nvl(SUM(f.bytes), 0) used_bytes,
  5  from   DBA_FREE_SPACE f , DBA_DATA_FILES d
  6  where  f.tablespace_name(+) = d.tablespace_name
  7  and    f.file_id(+) = d.file_id
  8  and    d.tablespace_name = 'T_1652'
  9  group by d.file_name;
TOTAL_BYTES FREE_BYTES FILE_NAME                 USED_BYTES
----------- ---------- ------------------------- ----------
   10485760    4194304 /tmp/t_1652_01.dbf           6291456
SQL>  ALTER DATABASE DATAFILE '/tmp/t_1652_01.dbf' RESIZE 15M;
Database altered.
SQL> alter table chf.t_xifenfei move;
Table altered.

这里可以发现CTAS,CREATE INDEX,MOVE操作都有个共同点:需要一次性创建一个较大SEGMENT,但是这个SEGMENT的创建过程是在数据库中逐渐实现(非初始化指定大小)。
也就是说,ORACLE对这些对象的处理方法是：对于这样的segment先当作临时段处理,当处理完成后,再把这些在永久表空间中的临时段转换为永久段;所以当这些永久表空间中的临时段在扩展的时候,遇到该永久表空间不足,而该段目前还是临时段(在永久表空间中的临时段),就出现了ORA-01652提示一个永久表空间unable to extend temp segment

表空间online出现ORA-00600[kcbz_check_objd_typ]处理过程

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

标题：表空间online出现ORA-00600[kcbz_check_objd_typ]处理过程

online表空间出现ORA-00600[kcbz_check_objd_typ]

Fri Mar 30 14:09:24 2012
alter tablespace xff offline
Fri Mar 30 14:09:28 2012
Completed: alter tablespace xff offline
Fri Mar 30 17:49:59 2012
alter tablespace xff rename datafile '/oradataa/xifenfei.dbf' to '/oradatab/xifenfei_bak.dbf'
Fri Mar 30 17:50:03 2012
Completed: alter tablespace xff rename datafile '/oradataa/xifenfei.dbf' to '/oradatab/xifenfei_bak.dbf'
Fri Mar 30 17:50:03 2012
alter tablespace coweb_bak_new online
Fri Mar 30 17:53:08 2012
Errors in file /oracle/ora10/admin/ora10g/udump/ora10g_ora_21275.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ], [0], [0], [1], [], [], [], []

分析trace文件

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/ora10/product
System name:	Linux
Node name:	FH-DB-01
Release:	2.6.18-92.el5
Version:	#1 SMP Tue Apr 29 13:16:15 EDT 2008
Machine:	x86_64
Instance name: ora10g
Redo thread mounted by this instance: 1
Oracle process number: 182
Unix process pid: 21275, image: oracle@FH-DB-01 (TNS V1-V3)
*** 2012-03-30 17:50:16.469
*** ACTION NAME:() 2012-03-30 17:50:15.939
*** MODULE NAME:(sqlplus@FH-DB-01 (TNS V1-V3)) 2012-03-30 17:50:15.939
*** SERVICE NAME:(SYS$USERS) 2012-03-30 17:50:15.939
*** SESSION ID:(921.23041) 2012-03-30 17:50:15.939
*** SESSION ID:(921.23041) 2012-03-30 17:50:15.939
OBJD MISMATCH typ=6, seg.obj=-2, diskobj=294051, dsflg=0, dsobj=294044, tid=294044, cls=1
Input data (nil), 0, 0
Formatted dump of block:
buffer tsn: 10 rdba: 0x0435c094 (1024/70631572)
scn: 0x0b2d.2b4f8874 seq: 0x01 flg: 0x04 tail: 0x88740601
frmt: 0x02 chkval: 0x5626 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000006DBB2000 to 0x000000006DBB4000
06DBB2000 0000A206 0435C094 2B4F8874 04010B2D  [......5.t.O+-...]
06DBB2010 00005626 00000002 00047CA3 2B4F8874  [&V.......|..t.O+]
06DBB2020 00000B2D 00320002 0435C091 00000000  [-.....2...5.....]
06DBB2030 00000000 00000000 00000000 00000000  [................]
        Repeat 2 times
06DBB2060 00000000 02800000 00000000 00240000  [..............$.]
06DBB2070 1F3C1F60 00000000 00000000 00000000  [`.<.............]
06DBB2080 00000000 00001F60 00001F5C 00000000  [....`...\.......]
06DBB2090 00000000 00000000 00000000 00000000  [................]
        Repeat 501 times
06DBB3FF0 00000000 00000000 00000000 88740601  [..............t.]
Block header dump:  0x0435c094
 Object id on Block? Y
 seg/obj: 0x47ca3  csc: 0xb2d.2b4f8874  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x435c091 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

这里可以得出几个结论：
1)版本平台信息
2)通过下面信息都是知道,data_object_id=294044的对象出现异常(seg.obj=-2)

OBJD MISMATCH typ=6, seg.obj=-2, diskobj=294051, dsflg=0, dsobj=294044, tid=294044, cls=1
Block header dump:  0x0435c094
 Object id on Block? Y
 seg/obj: 0x47ca3  csc: 0xb2d.2b4f8874  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x435c091 ver: 0x01 opc: 0
     inc: 0  exflg: 0

3)结合dba_objects视图查询出来是xifenfei_index 索引出现异常

问题原因猜测
因为没有找到权威解释，对于这里在online表空间的时候出现ORA-00600[kcbz_check_objd_typ]错误,个人的猜测可能是在online的时候，验证数据字典中关于该表空间中对象的相关记录和该表空间总的存储数据比较，然后发现不匹配，所以出现了该错误。

处理方法
虽然该表空间离线，但是我们可以使用drop操作直接删除数据字典中index的记录，然后再online表空间，这样可以绕过数据字典和表空间中的存储比较。当表空间online成功后，然后再创建index

ASMM表空间强制终止DML操作导致ORA-600 [ktspfupdst-1]

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

标题：ASMM表空间强制终止DML操作导致ORA-600 [ktspfupdst-1]

发现错误ORA-00600 [ktspfupdst-1]，trace关键内容如下

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 = /oracle9/app/product/9.2.0
System name:    AIX
Node name:      zwq_crm1
Release:        3
Version:        5
Machine:        00C420B44C00
Instance name: crm1
Redo thread mounted by this instance: 1
Oracle process number: 389
Unix process pid: 1896900, image: oracle@zwq_crm1 (TNS V1-V3)
----------------------------------------------
*** 2012-03-31 02:50:48.509
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [ktspfupdst-1], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
Current SQL statement for this session:
INSERT INTO XIFENFEI
SELECT * FROM T_XFF
----- 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
kgerinv+00e8         bl       _ptrgl
kgeasnmierr+004c     bl       kgerinv              000000001 ? 000000000 ?
                                                   000000005 ? 000000001 ?
                                                   000000001 ?
ktspfupdst+0540      bl       kgeasnmierr          110006308 ? 1103994E8 ?
                                                   102A9239C ? 000000000 ?
                                                   000000005 ? 000000010 ?
                                                   000000020 ? 000000006 ?
ktspstchg+00e4       bl       ktspfupdst           000000060 ? 300000004 ?
                                                   FFFFFFFFFFF6E48 ?
                                                   50601CE000000ED ?
                                                   3B401B34C5D02F2A ?
                                                   B92000004000020 ?
kdoiur+062c          bl       ktspstchg            000000000 ? 700000C39D779E8 ?
                                                   000000000 ?
kcoubk+00e4          bl       _ptrgl
ktundo+0988          bl       kcoubk               1010CCD80 ? FFFFFFFFFFF76C0 ?
                                                   100ED51C0 ? FFFFFFFFFFF7150 ?
                                                   1101FAF78 ? 1102567C0 ?
                                                   700000C396A1300 ? 000000002 ?
ktubko+03bc          bl       ktundo               1840DFB30 ?
                                                   3B401B3400000002 ?
                                                   000000000 ? 000000000 ?
                                                   FFFFFFFFFFF85D8 ?
                                                   700000C80A1E880 ? 2FFFF8540 ?
                                                   FFFFFFFFFFF8780 ?
ktuabt+0638          bl       ktubko               DF000000DF ?
                                                   FFFFFFFFFFF8690 ? 000000000 ?
                                                   FFFFFFFFFFF85D8 ? 102973880 ?
                                                   700000C844FA418 ?
ktcrab+02b4          bl       ktuabt               700000C80A1E840 ? 200017CD8 ?
ktcrsp+026c          bl       ktcrab               100F698E4 ? 000000001 ?
ksures+0074          bl       ktcrsp               700000C844FA448 ?
opiexe+3380          bl       01FD4138
opiall0+102c         bl       opiexe               400000000 ? 110002A48 ?
                                                   FFFFFFFFFFFA0A0 ?
kpoal8+0a78          bl       opiall0              5EFFFFBED4 ? 22103A43F8 ?
                                                   FFFFFFFFFFFA5B8 ? 000000000 ?
                                                   FFFFFFFFFFFA508 ? 1103A4B00 ?
                                                   6FF00000738 ?
                                                   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 ---------------------

这里可以得到信息如下：
1)系统平台aix 5.3，数据库版本9.2.0.8 rac
2)这个错误可能和一个insert select操作，然后取消有关系

表空间管理方式

SQL> SELECT tablespace_name, SEGMENT_SPACE_MANAGEMENT　from dba_tablespaces
  2  where tablespace_name=
  3  (select tablespace_name from dba_tables where table_name='XIFENFEI');
TABLESPACE_NAME                SEGMEN
------------------------------ ------
CUSTSERV                       AUTO

查看MOS发现和[ID 388599.1]相符
错误原因:

1. An insert or update on a table causes the addition of a new extent
   and the operation is cancelled.
2. The segment uses Automatic Segment Space Management (ASSM).
3. The call stack in the associated trace file resembles:
    ktspfupdst ktspstchg kdoiur kcoubk ktundo ktubko ktuabt ktcrab ktcrsp

解决方法：
官方给出方案就是升级到新版本，不过可以采取一个比较折中的处理方案，先想办法导出或者备份该对象数据，然后trunate或者重建表和相关index操作，解决该问题

分类目录归档：ORA-xxxxx