Know Current Oracle Database Version Usage

前2周有客户向我咨询现在市面上的Oracle数据库各版本的使用率,考虑到10g将会在2013年超出Extentd Support期,客户希望找到合适的时机升级到11gr2上。

在网上找了一圈,包括IDC似乎都没有公开的调查结果。反而在OTN Database Forum上找到一个”Poll Results: Your production database version”老外搞的生产库版本投票。

这个投票结果在一定程度上反映了国际上Oracle数据库各版本的使用率:10gr2目前使用率最高,11gr2在迅速追上。但因为国外的IT建设要领先于国内,所以我们可以推测国内的实际情况是9ir2和10gr2占主导地位,11gr2仍打酱油。

oracle_database_version_usage_poll

Oracle内部错误:ORA-00600[kgskdecrstat1]一例

一套Itanium HP-UX上的9.2.0.5系统最近出现了ORA-00600: internal error code, arguments: [kgskdecrstat1], [], [], [], [], [],内部错误,其错误日志如下:

Mon Apr 18 12:32:20 2011
ORA-00600: internal error code, arguments: [kgskdecrstat1], [], [], [], [], [], [], []
ARC0: Completed archiving  log 6 thread 1 sequence 831803
Mon Apr 18 12:32:22 2011
ORA-00600: internal error code, arguments: [kgskdecrstat1], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kgskdecrstat1], [], [], [], [], [], [], []
Mon Apr 18 12:32:23 2011
ORA-00600: internal error code, arguments: [kgskdecrstat1], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kgskdecrstat1], [], [], [], [], [], [], []

Trace file
----------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
ORACLE_HOME = /opt/oracle/product/9.2.0.5
System name:	HP-UX
Release:	B.11.23
Version:	U
Machine:	ia64
Redo thread mounted by this instance: 1
Oracle process number: 28
Unix process pid: 14827, image: oracle@(TNS V1-V3)

*** 2011-04-18 02:39:15.043
*** SESSION ID:(276.16183) 2011-04-18 02:39:15.042
DEADLOCK DETECTED
Current SQL statement for this session:
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                      ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00030022-0070d56f        28     276     X            344     259           X
TX-000a0012-0058e4fc       344     259     X             28     276           X
session 276: DID 0001-001C-0000002C	session 259: DID 0001-0158-000020E6
session 259: DID 0001-0158-000020E6	session 276: DID 0001-001C-0000002C
Rows waited on:
Session 259: obj - rowid = 00001F0D - AABNCDAAEAAAAXYAAL
 (dictionary objn - 7949, file - 4, block - 1496, slot - 11)
Session 276: obj - rowid = 00001F0B - AABNCAAAEAAAAXCAAY
 (dictionary objn - 7947, file - 4, block - 1474, slot - 24)
Information on the OTHER waiting sessions:
Session 259:
 pid=344 serial=17280 audsid=106170227 user: 41
           program: JDBC Thin Client
 application name: JDBC Thin Client, hash value=0
 Current SQL Statement:
End of information on OTHER waiting sessions.
*** 2011-04-18 12:32:20.392
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kgskdecrstat1], [], [], [], [], [], [], []
----- Call Stack Trace -----
... kgskdecrstat  kskdecrstat  ktudecrustat  ktcdso  ktcrcm  ktdcmt  k2lcom ...
----- End of Call Stack Trace -----

PROCESS STATE
-------------
   program: JDBC Thin Client
   application name: JDBC Thin Client, hash value=0
   last wait for 'SQL*Net message from client' blocking sess=0x0 seq=39332 wait_time=21242
               driver id=74637000, #bytes=1, =0

可以看到以上引发ORA-00600[kgskdecrstat1]内部错误的进程同时发现了死锁(DEADLOCK),在MOS上搜索可以发现”Bug 2894072: ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [KGSKDECRSTAT1]”,但是该Bug最早发生在9.2.0.3上,已经确定影响9.2.0.x所有版本,并且在9.2.0.5上没有backport的bug fix。

提交sr后,Oracle Gcs给出了解决的solution:
1.升级数据库到10.2.0.5,11.1.0.7,11.2.0.2等目前支持的版本
2.解决引发该bug的ORA-00060 dead lock问题
3.如果在解决ORA-00060后仍出现以上ORA-00600[kgskdecrstat1]内部错误,且启用了9i早期版本中的resource manager的话,可以尝试禁用该特性:
ALTER SYSTEM SET resource_manager_plan=” SCOPE=BOTH;
以便绕过bug 2494790。

Oracle内部错误:ORA-00600[OSDEP_INTERNAL]一例

一套HP-UX上的9.2.0.5系统在shutdown abort时出现ORA-00600: internal error code, arguments: [OSDEP_INTERNAL], [], [], [], [], [], [], []内部错误,伴随有ORA-27302: failure occurred at: skgpwinit4,ORA-27303: additional information: attach to invalid skgp shared ctx,具体日志如下:

/opt/oracle/product/9.2.0.5/rdbms/log/ngende_ora_7669.trc
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
ORACLE_HOME = /opt/oracle/product/9.2.0.5
System name: HP-UX
Node name: yictngd3
Release: B.11.23
Version: U
Machine: ia64
Instance name: nGende
Redo thread mounted by this instance: 0 
Oracle process number: 0
7669

*** 2010-09-08 00:10:02.985
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [OSDEP_INTERNAL], [], [], [], [], [], [], []
ORA-27302: failure occurred at: skgpwinit4
ORA-27303: additional information: attach to invalid skgp shared ctx
Current SQL information unavailable - no session.

Call stack
--------------
ksedmp <- ksfdmp <- kgerinv <- kgerin <- kgerecoserr <- ksucrp <- ksucresg <- kpolna 
<- kpogsk <- opiodr <- ttcpip <- opitsk <- Cannot <- Cannot <- Cannot <- Cannot <- opiino 
<- opiodr <- opidrv <- sou2o <- main <- main_opd_entry

经查该内部错误与操作系统共享内存有关,相关的Note有:

Ora-00600: Internal Error Code, Arguments: [Osdep_internal] [ID 304027.1]
Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.2 to 10.2.0.3 - Release: 9.2 to 10.2
Information in this document applies to any platform.
***Checked for relevance on 03-NOV-2010***

Getting ORA-600 [OSDEP_INTERNAL] errors while starting up the database:

ORA-00600: internal error code, arguments: [OSDEP_INTERNAL],
[], [], [], [], [], [], []
ORA-27302: failure occurred at: skgpwreset1
ORA-27303: additional information: invalid shared ctx
ORA-27146: post/wait initialization failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpsemsper
Symptoms
Getting ORA-600 [OSDEP_INTERNAL]
Accompanied by the following errors
ORA-27302:Failure occured at: skgpwreset1
ORA-27303:additional information: invalid shared ctx
ORA-27146: post/wait initialization failed
ORA-27300: OS system dependent operation: segment failed with error 28
ORA-27301: OS system Failure message: No space left on device
ORA-27302: failure occured at: sskgpsemsper

Cause
The functions in the trace file generated point to the semaphore settings .
Smmns is set too low.

Solution
set semmns 32767
Arrange to make the changes persistent as per the Operating system then restart the server and check if the changes are persistent.
eg: Linux /etc/sysctl.conf

sem = semmsl semmns semopm semmni
kernel.sem = 256 32768 100 228

Getting ORA-00600 [OSDEP_INTERNAL]: Internal Error While Trying To Connect / As Sysdba [ID 253885.1]

Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.3 and later   [Release: 9.2 and later ]
HP-UX PA-RISC (64-bit)
Symptoms
Getting following error while trying to connect as sysdba using sqlplus:

SQL> conn / as sysdba
ERROR:
ORA-01041: internal error. hostdef extension doesn't exist

Alert.log shows:

ORA-00600: internal error code, arguments: [OSDEP_INTERNAL], [], [], [], [], [],[], []
ORA-27302: failure occurred at: skgpwinit4
ORA-27303: additional information: attach to invalid skgp shared ctx
Cause
- Database was shutdown using "shutdown abort" option.
- Shared memory segment was not removed even though the instance was down.
Solution
+ Check which shared memory segments are owned by the oracle owner

Use the ipcs -bm command:

% ipcs -bm

m 34034336 0xf8f18468 --rw-r----- ORACLE dba 16777216

+ Delete the 'orphan' shared memory segments:

% ipcrm -m 34034336

If there is more than one instance running on the server and you are not sure how to identify the shared
memory segments then please contact support.

不恰当的设置OS VM参数可能导致该问题,而在HP-UX PA-RISC平台上使用'shotdown abort'命令时可能因为共享内存未能正常移除而出现该内部错误;因为实例还是以'abort'方式关闭的,仅仅是共享内存未能释放,所以只需要以ipcs->ipcrm等os命令将相应的共享内存段释放就可以了,不会造成其他影响。

ORA-00600:[kclchkinteg_2]及[kjmsm_epc]内部错误一例

一套AIX上的9.2.0.6 RAC系统,alert日志中最初报LMS进程(Lock Manager Server process,即锁服务管理进程,仅出现在RAC系统中)遭遇ORA-00600[kclchkinteg_2],继而出现ORA-00600[[kjmsm_epc]内部错误导致实例crash(instance crashed)。相关日志如下:

Tue Dec 1 01:20:25 2009
Errors in file /oracle/admin/crmdb/bdump/crmdb1_lms7_2143190.trc:
ORA-00600: internal error code, arguments: [kclchkinteg_2], [], [], [], [], [], [], []
Tue Dec 1 01:20:25 2009
Errors in file /oracle/admin/crmdb/bdump/crmdb1_lms7_2143190.trc:
ORA-00600: internal error code, arguments: [kclchkinteg_2], [], [], [], [], [], [], []
Tue Dec 1 01:20:26 2009
Trace dumping is performing id=[cdmp_20091201012026]
Tue Dec 1 01:20:40 2009
Errors in file /oracle/admin/crmdb/bdump/crmdb1_lms7_2143190.trc:
ORA-00600: internal error code, arguments: [kjmsm_epc], [], [], [], [], [], [], []
Tue Dec 1 01:20:41 2009
Errors in file /oracle/admin/crmdb/bdump/crmdb1_lms7_2143190.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kjmsm_epc], [], [], [], [], [], [], []
Tue Dec 1 01:20:43 2009

经过和metalink确认,认为此次实例意外终止时由于”Bug 3671867 – OERI[kclchkinteg_2] possible in RAC environment”:

Hdr: 3671867 10.1.0.2 RDBMS 10.1.0.2 RAC PRODID-5 PORTID-212 ORA-600
Abstract: INSTANCE TERMINATED WITH ORA-600 [KCLCHKINTEG_2]
PROBLEM:
--------
During testing, one instance was terminated with ORA-600 [KCLCHKINTEG_2]

DIAGNOSTIC ANALYSIS:
--------------------
Alert log contains:

Thu Jun  3 12:57:26 2004
Private_strands 0 at log switch
Thread 1 advanced to log sequence 153
  Current log# 1 seq# 153 mem# 0: +EMDBD1/dbotazc0/onlinelog/group_1.267.1
  Current log# 1 seq# 153 mem# 1: +EMDBD1/dbotazc0/onlinelog/group_1.268.1
Thu Jun  3 13:41:45 2004
Private_strands 0 at log switch
Thread 1 advanced to log sequence 154
  Current log# 2 seq# 154 mem# 0: +EMDBD1/dbotazc0/onlinelog/group_2.269.1
  Current log# 2 seq# 154 mem# 1: +EMDBD1/dbotazc0/onlinelog/group_2.270.1
Thu Jun  3 13:55:06 2004
Errors in file /DBA/nest/oracle/DBOTA1/logs/dbota1_dbw0_82244.trc:
ORA-600: internal error code, arguments: [kclchkinteg_2], [], [], [], [],
[], [], []
Thu Jun  3 13:55:08 2004
Errors in file /DBA/nest/oracle/DBOTA1/logs/dbota1_dbw0_82244.trc:
ORA-600: internal error code, arguments: [kclchkinteg_2], [], [], [], [],
[], [], []
Thu Jun  3 13:55:08 2004
DBW0: terminating instance due to error 471
Thu Jun  3 13:55:08 2004
Trace dumping is performing id=[cdmp_20040603135508]
Thu Jun  3 13:55:10 2004
Dump system state for local instance only
Thu Jun  3 13:55:10 2004
Trace dumping is performing id=[cdmp_20040603135510]
The only trace file we have been sent is the dbw trace.
WORKAROUND:
-----------
None
RELATED BUGS:
-------------
There are several which end in KCLCHKINTEG but not KCLCHKINTEG_2
REPRODUCIBILITY:
----------------
Appears only to have happened once so far.
TEST CASE:
----------
N/A
STACK TRACE:
------------
ksedmp ksfdmp kgerinv kgeasnmierr kclassertle kclchkinteg kclfwrite1 kcbbic1
kcbbiop kcbbdrv ksbabs ksbrdp opirip opidrv sou2o main start

SUPPORTING INFORMATION:
-----------------------
Bruce Carter has looked at this and suggested a bug be raised.
Affects:
    Product (Component)	Oracle Server (Rdbms)
    Range of versions believed to be affected	Versions < 10.2
    Versions confirmed as being affected
        * 10.1.0.3
    Platforms affected	Generic (all / most platforms affected)
Fixed:
    This issue is fixed in
        * 9.2.0.7 (Server Patch Set)
        * 10.1.0.4 (Server Patch Set)
        * 10.2.0.1 (Base Release)
Symptoms:
Related To:
    * Internal Error May Occur (ORA-600)
    * Instance May Crash
    * ORA-600 [kclchkinteg_2]
    * RAC (Real Application Clusters) / OPS
Description
    ORA-600 [kclchkinteg_2] possible in RAC environment

Oracle开发部分确认该3671867 bug已在9.2.0.8中得到修复,建议通过应用9.2.0.8补丁集或者打上bug 3671867的one-off patch来修复该问题;该Bug可能导致实例意外终止,因此其Severity也极高,值得手头仍有9i RAC系统需要管理的dba注意。

ORA-00600: [7005], [192]内部错误一例

一套AIX上的9.2.0.6系统,应用的某条查询语句执行时频繁报ORA-00600:[7005]错误,alert告警日志内容如下:

Errors in file /oracle/admin/BIDW/udump/bidw2_ora_3252288.trc:
ORA-00600: internal error code, arguments: [7005], [192], [], [], [], [], [], []
Mon Dec  7 15:20:27 2009
Errors in file /oracle/admin/BIDW/udump/bidw2_ora_3252288.trc:
ORA-00600: internal error code, arguments: [7005], [192], [], [], [], [], [], []
Mon Dec  7 15:20:27 2009
Trace dumping is performing id=[cdmp_20091207152027]
Mon Dec  7 15:20:28 2009
Thread 2 advanced to log sequence 909143
 Current log# 7 seq# 909143 mem# 0: /oradata2/bidw/BIDW/redo2_3
...............
Mon Dec  7 15:21:10 2009
Errors in file /oracle/admin/BIDW/udump/bidw2_ora_3600486.trc:
ORA-00600: internal error code, arguments: [7005], [192], [], [], [], [], [], []
Mon Dec  7 15:21:11 2009
Errors in file /oracle/admin/BIDW/udump/bidw2_ora_3600486.trc:
ORA-00600: internal error code, arguments: [7005], [192], [], [], [], [], [], []

[Read more…]

沪ICP备14014813号-2

沪公网安备 31010802001379号