pat98's always & forever

Itanium Architecture 시스템에서만 나타나는 오류로 /var/adm/messages 에 다음과 같은 오류메세지가 기록된다면 아래와 같이 처리하면 됨.

oracle(9581): floating-point assist fault at ip 40000000068c22c2
...
oracle(13763): floating-point assist fault at ip 40000000072b8081

Solution

It is possible to completely turn off the floating-point assist messages from the console (they are still written to /var/log/messages)
To do this, simply issue the command as "root":

$ dmesg -n4

To eliminate them also from /var/log/messages :

1. edit /etc/syslog.conf changing line:
*.info;mail.none;authpriv.none;cron.none               /var/log/messages
 to
*.error;mail.none;authpriv.none;cron.none               /var/log/messages

This means any facility logging messages below error level will be suppressed

2. restart syslog
service syslog restart

 

32bit <-> 64bit 변환에 대한 문서

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

SCOPE & APPLICATION
-------------------

This document is created to provide all the details for changing word size from
32bit to 64bit. This document is a "cut/paste" of applicable sections from the
Oracle9i Database Migration guide (A96530-02), to quickly provide the needed
details and steps to change the word-size.

This note is applicable to Oracle 8.0.x, Oracle8i, Oracle9i and Oracle10g.

LIMITATIONS OF USE
------------------
This note is not applicable for:

- databases having JVM installed in an Oracle8i, Oracle9i or Oracle10g environment, or
- Oracle Applications installed in an Oracle8i, Oracle9i or Oracle10g environment
 
To migrate these types of database, please check Note 183649.1

CHANGING WORD-SIZE
------------------
You can change the word-size of your Oracle database server during a migration,
upgrade, or downgrade operation. A change in word-size includes the following
scenarios:

You have 32-bit Oracle software installed on 64-bit hardware and want to
change to 64-bit Oracle software.

You have 64-bit Oracle software installed on 64-bit hardware and want to
change to 32-bit Oracle software.

If you are changing word-size during a migration, upgrade, or downgrade
operation then no additional action is required. The word-size is changed
automatically during any of these operations. However, if you want to change
the word-size within the same release, then follow the instructions in
"Changing the Word-Size of Your Current Release" below. For example, if you
have the 32-bit version of Oracle release 9.0.1 and you want to switch to the
64-bit version of Oracle release 9.0.1, then you must complete this procedure.
The following information applies if you are upgrading or downgrading your
hardware from 32-bit to 64-bit or from 64-bit to 32-bit:

If you want to upgrade your hardware, then you should be able to switch
from 32-bit hardware to 64-bit hardware and still use your existing
32-bit Oracle software without encountering any problems.

If you want to downgrade your hardware from 64-bit to 32-bit, then you
must first downgrade your Oracle software to 32-bit software before
downgrading your hardware.

The on-disk format for database data, redo, and undo is identical for the
32-bit and 64-bit installations of Oracle. The only internal structural
differences between the 32-bit and 64-bit Oracle installations are the
following:

The compiled format of PL/SQL is different. The instructions for how and
when to recompile PL/SQL are provided in the appropriate chapters of
the Migration book. The storage format of user-defined types is based on the
release of Oracle that created the database. The existing storage format will
be converted to the correct format transparently when necessary. User-defined
types include object types, REFs, varrays, and nested tables.

Note: For Oracle 9.2

In the first release of the migration guide it is said that changing the
wordsize during upgrade or migration is not supported.  This is incorrect
a documentation bug has been logged for this.  Bug 2590998 explains the
error in the documentation.  This has been fixed in the second release of
Oracle 9I release 2 (9.2) Migration guide where it is correctly written
that changing wordsize during the migration or the upgrade is supported.

It is recomended to apply the latest patchset BEFORE the wordsize conversion.
This would avoid some bugs and also some steps in this note during the wordsize
conversion, like Bug 1867501 and Bug 1926809.

CHANGING THE WORD-SIZE OF YOUR CURRENT RELEASE
----------------------------------------------

The instructions in this section guide you through changing the word-size of
your current release (switching from 32-bit software to 64-bit software or
vice versa).

Complete the following steps to change the word-size of your current release:

1. Start SQL*Plus.

2. Connect to the database instance AS SYSDBA.

3. Run SHUTDOWN IMMEDIATE on the database:

   SQL> SHUTDOWN IMMEDIATE

   Issue the command for all instances if you are running Oracle Parallel
   Server.

=============================================================================
   Note:

   NCHAR columns in user tables are not changed during the upgrade.
   To change NCHAR columns in user tables, see "Upgrade User NCHAR
   Columns" in the Migration guide. 

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

4. Perform a full offline backup of the database (optional, but highly
   recommended)

   See Also:

   Oracle9i User-Managed Backup and Recovery Guide for more information. 

5. If you are using the same Oracle home for your current release and the
   release to which you are switching, then deinstall your current release
   using the Oracle Installer. You do not need to deinstall your current
   release if you are using separate Oracle home directories.

6. If you currently have a 32-bit installation, then install the 64-bit
   version of the same release. Or, if you currently have a 64-bit
   installation, then install the 32-bit version of the same release.

=============================================================================
   Note:

   Installation and deinstallation are operating system-specific. For
   installation and deinstallation instructions, see your
   Oracle9i operating system-specific installation documentation and
   the Oracle9i README for your operating system.

   Installation documentation can also be found at technet.oracle.com
 
=============================================================================

7. Copy configuration files to a location outside of the old Oracle home:

   a. If your initialization parameter file resides within the old
      environment's Oracle home, then copy it to a location outside of the
      old environment's Oracle home. The initialization parameter file can
      reside anywhere you wish, but it should not reside in the old
      environment's Oracle home after you switch to the new release.

   b. If your initialization parameter file has an IFILE (include file)
      entry and the file specified in the IFILE entry resides within the
      old environment's Oracle home, then copy the file specified by the
      IFILE entry to a location outside of the old environment's Oracle
      home.  The file specified in the IFILE entry has additional
      initialization parameters. After you copy this file, edit the IFILE
      entry in the initialization parameter file to point to its new
      location.

   c. If you have a password file that resides within the old Oracle home,
      then move or copy the password file to the Oracle9i Oracle home.
      The name and location of the password file are operating
      system-specific; for example, on UNIX operating systems, the default
      password file is ORACLE_HOME/dbs/orapwsid, but on Windows platforms,
      the default password file is ORACLE_HOME\database\pwdsid.ora.
      In both cases, sid is your Oracle instance ID.

=============================================================================
      Note:

      For Oracle9i Real Application Clusters, perform this step on
      all nodes. Also, if your initdb_name.ora file resides within
      the old environment's Oracle home, then move or copy the
      initdb_name.ora file to a location outside of the old
      environment's Oracle home. 

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

8. Change your environment to point at the new 64Bit ORACLE_HOME.

     Note: Check with platform specific documentation if other env variables
           need to be changed e.g. LD_LIBRARY_PATH

9. If you are migrating an Oracle 8.0, Oracle8i or Oracle9i 9.0.x database
   then please make the following changes in the 64-bit ORACLE_HOME/dbs
   init$ORACLE_SID.ora file to prepare for migration:

    aq_tm_processes=0   
    job_queue_processes=0
    _system_trig_enabled= false

    Changing the first two parameters will avoid the problems detailed in
    Bug 1421476 and Bug 1816609

    The last parameter should be set to FALSE for scripts which perform
    dictionary operations as the objects on which the triggers depend may
    become invalid or be dropped, causing the triggers to fail and thus
    preventing the scripts from running successfully.

    See Note 149948.1 'IMPORTANT: Set "_SYSTEM_TRIG_ENABLED=FALSE" When
    Upgrading / Downgrading / Applying Patch Sets' for more info.

   If you are migrating an Oracle9i 9.2.0.x or Oracle10g database, go to
   step 10.

10. When migrating from a 32-bit Oracle version to a 64-bit Oracle version,
    Oracle recommends doubling the size of parameters such as:

    SHARED_POOL_SIZE
    SHARED_POOL_RESERVED_SIZE
    LARGE_POOL_SIZE

    This is mainly due to an increase in the size of internal data structures.
    For an in-depth explanation of this, please see Note 209766.1
    'Memory Requirements of Databases Migrated from 32-bit to 64-bit'

11. At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.

12. Start SQL*Plus.

13. Connect to the database instance AS SYSDBA.

14. If you are migrating an Oracle 8.0, Oracle8i or Oracle9i 9.0.x database,
    run STARTUP RESTRICT:

    SQL> STARTUP RESTRICT

    You may need to use the PFILE option to specify the location of your
    initialization parameter file.

    If you are migrating an Oracle9i 9.2.0.x database, run STARTUP MIGRATE:

    SQL> STARTUP MIGRATE

    If you are migrating an Oracle10g database, run STARTUP UPGRADE:

    SQL> STARTUP UPGRADE

15. Run the following script:

    SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql

16. Check the validity of the DBMS_STANDARD package:

    SQL> select status from dba_objects
    where object_name='DBMS_STANDARD'
    and object_type='PACKAGE'
    and owner='SYS';

17. If the package is invalid, recompile it:

    SQL> alter package dbms_standard compile;

18. Run the following script:

    SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql

    After running this script, check for invalid objects:

    SQL> select owner, object_name, object_type from dba_objects
    where status <> 'VALID';

    Recompile any invalid objects to avoid problems while running the
    utlirp.sql script (in step 20)

19. Set the system to spool results to a log file for later verification of
    success:

    SQL> SPOOL catoutw.log

    If you want to see the output of the script you will run on your screen,
    then you can also issue a SET ECHO ON statement:

    SQL> SET ECHO ON

20. Run utlirp.sql:

    SQL> @$ORACLE_HOME/rdbms/admin/utlirp.sql

    The utlirp.sql script recompiles existing PL/SQL modules in the format
    required by the new database. This script first alters certain
    dictionary tables. Then, it reloads package STANDARD and DBMS_STANDARD,
    which are necessary for using PL/SQL. Finally, it triggers a
    recompile of all PL/SQL modules, such as packages, procedures, types,
    and so on.

21. Turn off the spooling of script results to the log file:

    SQL> SPOOL OFF

    Then, check the spool file and verify that the packages and procedures
    compiled successfully. You named the spool file in Step 12; the suggested
    name was catoutw.log. Correct any problems you find in this file.

    If you specified SET ECHO ON, then you may want to SET ECHO OFF now:

    SQL> SET ECHO OFF

22. If you are migrating an Oracle 8.0, Oracle8i or Oracle9i 9.0.x database,
    disable the restriction on sessions:

    SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

23. Shutdown the database. If you are migrating an Oracle 8.0, Oracle8i or
    Oracle9i 9.0.x database, remove the following parameter from init.ora

    aq_tm_processes=0
    job_queue_processes=0
    _system_trig_enabled=false

The word-size of your database is now changed.

You can open the database for normal use.

RELATED DOCUMENTS
-----------------

Note 214242.1 ORA-600 [17069] while running utlirp.sql converting to
           8.1.7.4 64-Bit

Oracle 9i Database Migration Release 2 (9.2) Part Number A96530-01 (HTML) -
   http://download.oracle.com/docs/cd/B10501_01/server.920/a96530/toc.htm

Oracle 9i Datbase Migraiton Release 1 (9.0.1) Part Number A90191-02 (HTML) -
   http://download.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90191/toc.htm

Oracle8i Migration Release 3 (8.1.7) Part Number A86632-01 (HTML) -
   http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a86632/toc.htm

Oracle8 Migration Release 8.0 Part Number A58243-01 (HTML) -
   http://download.oracle.com/docs/cd/A64702_01/doc/server.805/a58243/toc.htm

Oracle Documentation Master Index -
   http://www.oracle.com/technology/documentation/index.html

Applies to:

Oracle Net Services - Version: 10.2.0.1.0
This problem can occur on any platform.

Symptoms

The Oracle Net 10g parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername default to 0 (indefinite) in 10.1.  To address Denial of Service (DOS) issues,  the parameters were set to have a default of 60 (seconds) in Oracle 10.2.

If applications are longer than 60 secs to authenticate with the Oracle database, the errors occur.

The following may be seen in the alert log: WARNING: inbound connection timed out (ORA-3136)

SQLNET.INBOUND_CONNECT_TIMEOUT is set to a value in seconds and determines how long a client has to provide the necessary authentication information to a database.

INBOUND_CONNECT_TIMEOUT_listenername is set to a value in seconds and determines how long a client has to complete its connect request to the listener after the network connection has been established.

To protect both the listener and the database server, Oracle Corporation recommends setting INBOUND_CONNECT_TIMEOUT_listenername in combination with the SQLNET.INBOUND_CONNECT_TIMEOUT parameter.

Cause

Whenever default timeouts are assigned to a parameter, there may be cases where this default does not work well with a particular application. However, some type of timeout on the connection establishment is necessary to combat Denial of Service attacks on the database.  In this case, SQLNET.INBOUND_CONNECT__TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername were given default values of 60 seconds in Oracle 10.2.  It is these timeout values that can cause the errors described in this note.

Also note that it is possilbe the reason the database is slow to authenticate, may be due to an overloaded Oracle database or node.

Solution

Set the parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername to 0 (indefinite) or to an approprate value for the application yet still combat DOS attacks (120 for example). 

These parameters are set on the SERVER side:
listener.ora: INBOUND_CONNECT_TIMEOUT_listenername
sqlnet.ora:   SQLNET.INBOUND_CONNECT_TIMEOUT

Further tuning of these parameters may be needed is the problem persists.

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

PURPOSE
-------

The purpose of this article is to explain why the 12500 error is raised
and mainly how the tuning of resource in the platform is very important
to solve this issue.
This a short and quick checklist to understand and solve this error.
An ORA/TNS-12540 could be related to 12500 error.

SCOPE & APPLICATION
-------------------

Microsoft platforms ( windows machines)


Understanding and Diagnostics for 12500 error on Windows Platform
-----------------------------------------------------------------
TNS-12500: TNS:listener failed to start a dedicated server process
TNS-12540: TNS:internal limit restriction exceeded

These errors are related to physical usage of Oracle and OS resources :
Memory, Swap, Open files, Open sockets ...ect

The client receive this message error from the listener

The following actions may help to solve the problem when the error appear
after a longtime running:
- Increase memory and swap in the system
- Configure MTS and DCD
- Tune SGA / shared_pool_size parameter
Reduce the SGA size to a reasonable figure which allows the user
process to have enough memory to run.
- Increase processes parameter in the init.ora file
- Restart Listener/DB
- Check for any memory leak
- Tune microsoft TCP parameters in :
HKEY_LOCAL_MACHINE\System\CurrectControlSet\services\Tcpip\Parameters
TcpTimedWaitDelay, MaxUserPortData and TcpMaxDataRetransmissions
- Try to set the /3GB switch in the BOOT.INI file to enable the 4GB feature.
Allowing a process to address 3GB and reserving 1GB for the kernel.
- Install the latest Windows Service Pack

If the error is persistent :
- Check the user who start the listener privileges.
- Check Oracle_Home and Oracle_SID
- The Oracle Service may be corrupt : Recreate The Oracle service by oradim command


RELATED DOCUMENTS
-----------------
Note 46001.1 Oracle Database and the Windows NT memory architecture
Note 171636.1 TNS-12500 TNS-12540 TNS-12560 TNS 510 Windows Error 8: Exec Format Error
Note 108180.1 Intermitent TNS-12540 Errors When Trying to Connect to oracle
Note 2064864.102 Troubleshooting TNS-12500 On Microsoft Windows NT
Note 118322.1 Basic MTS setup
Note 151972.1 Dead Connection Detection (DCD)

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.