pat98's always & forever

발렌타인 데이에 뜬  도자기녀 라고 뒤늦게 그냥 한번 퍼와 봤음.^^

8i ->9i 로 업그레이드 후 아래와 같은 에러 발생시 check 사항
==========================================================
8i 에서 쓰던 드라이버를 쓰다가 9i로 올리고 9i버전으로 업데이트 안해 주면 나는거 같은데.

udump 화일에 이런 에러메세지가 막 떨어질 것임.

*** SESSION ID:(119.40422) 2007-02-13 10:53:10.517
*** 2007-02-13 10:53:10.517
ksedmp: internal or fatal error
ORA-00600: 내부 오류 코드, 인수 : [ttcgcshnd-1], [0], [], [], [], [], [], []
Current SQL statement for this session:
SELECT VALUE FROM NLS_INSTANCE_PARAMETERS WHERE PARAMETER ='NLS_DATE_FORMAT'
----- Call Stack Trace -----
calling              call     entry                argument values in hex     
location             type     point                (? means dubious value)    
-------------------- -------- -------------------- ----------------------------
ksedmp()+328         CALL     ksedst()             00000000B ? 000000000 ?
                                                   000000000 ? 102FFB548 ?
                                                   00000003E ?
                                                   FFFFFFFF7FFF6658 ?

조치 방법
1. 아래와 같이 조치해 볼것
SQL> alter system set events '10841 trace name context forever';

요게 안되면 2번

2. 버전에 맞는 JDBC driver 로의 교체
ojdbc14.jar
Java classes when using the JDBC Thin and OCI client-side driver - with Java 1.4 or 5.0 VM. With Java 5.0 VM, you can use this library if the JDBC version is 10.2.

classes12.jar
Same as ojdbc14.jar except for use with with Java 1.2 or 1.3 VM.

classes12.zip
Same as classes12.jar except in zip format. This file will almost certainly not be available in future releases. You should use classes12.jar instead.

classes111.jar, classes111.zip
Classes for the Thin and OCI drivers when using a Java 1.1 VM.

- $ORACLE_HOME/jdbc/lib 밑에 최신 화일 업로드
- CLASSPATH에 추가 시켜줄것. (ex) export CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc14.jar

3. OS 에 설치된 JDK 버전의 down or upgrade 확인

주로 10g에서 발생한다고 하는데..아무 로그도 없고 , 이유없이 listener 가 Hang 이 걸릴때 조치방법입니다. listener 를 하나 띄워도 OS에서 계속 spawn 시켜서 listener가 4개가 생성되는 증상이 있을때 해보면 됨.
 
10.2.0.3 에서 fix 되었다고 나와있네요.

==================================================================================


Oracle Net Services - Version: 10.1.0.2.0 to 10.2.0.3.0
This problem can occur on any platform.
All new connections via TNS listener hang, no errors reported

Symptoms

Intermittently the TNS listener hangs and new connections to the database are not possible.

Listener process can also consume high amount of CPU

Child TNS listener process is seen when doing a ps on the listener process, eg.:

Killing the child process allows new connections to work until the problem reoccurs

Cause

This is a known problem addressed via non-published bug:4518443 (Abstract: Listener Gets Hung Up)

The issue is that the TNS listener can hang under load while spawning a process

Solution

Bug 4518443 is fixed in 10.2.0.3

 - OR -

Apply Patch 4518443  for the problem (if a patch is available)

 - OR -

As a workaround, the following parameter can be added to listener.ora

Where <listener_name> should be replaced with the actual listener name configured in the LISTENER.ORA file.

For example, if the listener name is LISTENER (default), the parameter would be:

This will prevent the listener from registering against ONS (Oracle Notification Services), which is the area affected by bug:4518443. For more information on ONS, please refer to eg. the Oracle10g Release 2 documentation ("Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide").

Please note, adding SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name> to listener.ora file on RAC, will mean that FAN (fast application notification) will not be possible. See Note 220970.1 RAC: Frequently Asked Questions for further information on FAN

Oracle Server - Enterprise Edition - Version: 10.2.0.2 to 10.2.0.2
This problem can occur on any platform.
SymptomsThe following error is consistently appearing in the alert.log:

ORA-00600: internal error code, arguments: [kkqcby: qbcfkkc not zero], [], [], [], [], [], [], []

The Call Stack trace will include:  kkqcbyCountJoinKeys kkqcbydrv qksqbApplyToQbcLoc
ChangesPatchset 10.2.0.2 has been applied.

Cause
This problem is due to unpublished Bug 4639977.

Solution
This bug fixed in 10.2.0.3 and 11i.
1. A one off patch exists for some 10.2.0.2 platforms on MetaLink:
   Patch 4639977

-or-

2. The Workaround is to set the following parameter:
  _optimizer_connect_by_cost_based = false

이미 패치셋이 적용되었는데, 뒤늦게 Partitoning 기능을 추가해서 사용해야 한다면?

다음과 같이 하면 된다. runInstaller 를 띄워서 custum option 에서 Partitioning 기능을 추가해주고 9.2.0.7 patchset 띄워서 다시 patch 를 New install 해주면 된다. 당연히 catproc.sql, catalog.sql은 한번씩 돌려줘야 겠지.

Goal
When the software in ORACLE_HOME was installed partition option was not included.
The 9.2.0.6 patchset has been installed to this ORACLE_HOME.
Now you would lilke to install the partition option at this time

Solution
You can install the Oracle Partitioning option by invoking the runInstaller from the 9.2.0.1
software kit and then select the existing Oracle Home to which you have applied the 9.2.0.7
patchset and then go for the 'Custom Install' option. In the custom install option you will be
able to select the Oracle Partitioning option and then proceed to install the partitioning option.
After installing the partitioning option from the 9.2.0.1 base software kit, invoke the installer from the 9207 patchset and then select the same Oracle Home as before and if you scroll down and see the list of components that will be installed, you will see "installed" for all the components that are already installed and patched to 9207. Whereas if you expand 'Oracle Partitioning' you will see "installed" for 9201 and "new install" for 9207 patch, just proceed and click on install and wait for the installation to complete.

The same procedure holds good for 10gR1 and 10gR2 also.