pat98's always & forever

Symptoms

/usr/ccs/bin/make -f ins_net_client.mk mkldflags client_sharedlib install
ORACLE_HOME=/u01/app/oracle/product/9.2.0
/u01/app/oracle/product/9.2.0/bin/genclntsh
genclntsh: Could not locate /u01/app/oracle/product/9.2.0/network/admin/shrept.lst
genclntsh: exiting ...
make: Fatal error:

INFO: genclntsh: genclntsh: Could not locate  
/app/oracle/product/10.2.0/network/admin/shrept.lst
INFO: ***
Error exit code  1

INFO: ld: Can't find library or mismatched ABI for -lclntsh
Fatal error.

INFO: ld: I/O error, file "/app/oracle/product/10.2.0/lib/libclntsh.so": 
Invalid argument
Fatal error.

Changes

Cause

Solution

Please ensure the $ORACLE_HOME is set:

    env | grep ORACLE_HOME

If it is not set, set it and then follow one of the following options:

Option 1:
======
If the file is missing, create $ORACLE_HOME/network/admin/shrept.lst with a text editor and put the following lines into it.

network : snaumihi_inithostinfo
network : snaumbg_gmt
network : naedpwd_encrypt
network : naumbsb_bld_singlebyte
network : ztapis

Using the 'ls -al' command, check that the file has 644 permissions:

-rw-r--r-- $ORACLE_HOME/network/admin/shrept.lst

If necessary, change the permissions using the command:

chmod 644 $ORACLE_HOME/network/admin/shrept.lst

Now manually relink using the command 'relink all'

Option 2:
======
If the file is missing, for Oracle9i extract it from the first 9.2.0.1.0 CD as follows:

cd $ORACLE_HOME/network/admin
jar xvf /cdrom/cdrom0/stage/Components/oracle.rsf.net_rsf/9.2.0.1.0/1/DataFiles/admin.1.1.jar shrept.lst

For Oracle10g extract the missing file from the first 10.2.0.1.0 CD as follows:

cd $ORACLE_HOME/network/admin
jar xvf <10.2.0.1staging_area>/stage/Components/oracle.network.rsf/10.2.0.1.
0/1/DataFiles/filegroup8.jar

Using the 'ls -al' command, check that the file has 644 permissions:

-rw-r--r-- $ORACLE_HOME/network/admin/shrept.lst

If necessary, change the permissions using the command:

chmod 644 $ORACLE_HOME/network/admin/shrept.lst

Now manually relink using the command 'relink all'

Option 3:
======
Manually copy the file from another Oracle9i/10g installation

Using the 'ls -al' command, check that the file has 644 permissions:

-rw-r--r-- $ORACLE_HOME/network/admin/shrept.lst

If necessary, change the permissions using the command:

chmod 644 $ORACLE_HOME/network/admin/shrept.lst

Now manually relink using the command 'relink all'

이런 무모한 짓을 할 사람은 거의 없겠지만 필요하면 참고하시길 ^^
맥에 오라클이라..끔찍해.
=======================================
Installing Oracle Database 10g on Mac OS X

Recently Oracle and Apple announced the availability of Oracle Database 10g for Mac OS X. In in this — longer than usual — blog post, I document my experience installing the Oracle database on my Mac. In a subsequent post, I'll talk about my experience installing Oracle HTML DB. These instructions should not be considered a best practice, but they are steps that worked for me. I tried to incorporate as many screenshots as possible to help you when you are performing the install.

1. Hardware Requirements

The Quick Installation Guide states the following hardware requirements:

• 512 MB RAM
• 1GB or twice the size of RAM swap space
• 400MB of disk space in /tmp
• 2Gb disk space for software files
• 1.2Gb disk space for database files

I performed my installation on an iMac G5 with 1Gb of RAM and 160GB of disk.

2. Operating Sytems Requirements

Oracle Database 10g is only supported on Mac OS X Server. As I was just experimenting, I installed on regular OS X. There are no doubt minimum requirements. You can verify the version of Mac OS X, by executing the following command:

 # sw_vers

Below is what I saw when I executed this.

3. Install Developer Tools

To install the Oracle database, you need a C compiler. I didn't have one installed, so I signed up for a (free) Apple Developer Connection (ADC) account to download the Developer tools. To do this, sign in to ADC then click on Download Software then Developer Tools. First, download and install Xcode Tools v1.1 then download and install Dec 2003 gccLongBranch Tools Installing Xcode 1.1

After installing these packages you, verify you have the correct version of gcc installed by issuing the following command:

# gcc -v

Make sure you have at least the version as shown below.

4. Create Required User and Groups

The Quick Installation Guide suggests to start the Workgroup Manager. I didn't find one on my system, so I imagine this is a OS X Server thing. I used the command line instructions from the Database Installation Guide 10g for Mac OS X instead.

First, make sure you're root by executing in a Terminal:

# sudo sh

Determine Available Group ID

Execute the following command:

# nireport . /groups gid name | more

You'll see a list similar to the one below.

...
70      www     
74      mysql   
75      sshd    
76      qtss    
78      mailman 
79      appserverusr    
80      admin   
81      appserveradm    
99      unknown 
...

Choose an unused group ID. I chose 600.

Create oinstall Group

Using the available group ID, create a group called oinstall by executing the following three commands:

# nicl . -create /groups/oinstall
# nicl . -append /groups/oinstall gid 600
# nicl . -append /groups/oinstall passwd "*"

Create dba Group

Repeat the steps above to determine another available group ID. I chose 601. Then, create a dba group by executing these commands:

# nicl . -create /groups/dba
# nicl . -append /groups/dba gid 601
# nicl . -append /groups/dba passwd "*"

Create the Oracle Software Owner User

First, find an unused user ID, or UID, by executing the following command:

# nireport . /users uid name | more

You'll see a list similar to the following:

...
27      postfix 
70      www     
71      eppc    
74      mysql   
75      sshd    
76      qtss    
77      cyrus   
78      mailman 
79      appserver       
502     sleuniss 
...

I chose 601 as an available UID. Create the oracle user by executing these commands. Substitiute the gid and uid you chose where appropriate:

# nicl . -create /users/oracle
# nicl . -append /users/oracle uid 601
# nicl . -append /users/oracle gid 600
# nicl . -append /users/oracle shell /bin/bash
# nicl . -append /users/oracle home /Users/oracle
# nicl . -append /users/oracle realname "Oracle software owner"

Here's what it looked like.

Next, add the oracle user to the dba group:

# nicl . -append /groups/dba users oracle

Create a home directory and change the owner and group:

# mkdir /Users/oracle
# chown oracle:oinstall /Users/oracle

Finally, set the password for the oracle user:

# passwd oracle

5. Create Required Directories

Based on the suggestions in the Quick Installation Guide, I created two directories, one for the Oracle software, and one for the datafiles. Strictly speaking two separate directories are not required here.

Create the Oracle Base Directory

Create the Oracle base directory as follows:

# mkdir -p /Volumes/u01/app/oracle

Create Directory for Datafiles

Create the directory for datafile as follows:

# mkdir -p /Volumes/u02/oradata

Change Groups and Permissions for Directories

Change the groups and permissions for the directories you just created by executing these commands:

# chown -R oracle:oinstall /Volumes/u01/app/oracle 
# chown -R oracle:oinstall /Volumes/u02/oradata

then:

# chmod -R 775 /Volumes/u01/app/oracle
# chmod -R 775 /Volumes/u02/oradata

6. Configure Kernel Parameters

As is often the case when installing Oracle on Linux or other flavors or UNIX, certain kernel parameters will have to be adjusted for Oracle to run properly. The Quick Installation Guide specifies the proper minimum values.

To verify the kernel parameter values, execute:

# /usr/sbin/sysctl -a | grep corefile
# /usr/sbin/sysctl -a | grep sem
# /usr/sbin/sysctl -a | grep maxproc

Here's what I saw:

I only had to adjust two values by executing these commands:

To make these values stick, that is, presist after a reboot, edit /etc/sysctl.conf and add lines for values that need changing. I used vi to edit the file, but you can use any text editor.

Set Shell Limits

For performance reasons, shell limits need to be adjusted. Begin by navigating to the directory that contains the IPServices script.

# cd /System/Library/StartupItems/IPServices

Using any text editor, edit the file IPServices and add the following ulimit commands inside the StartService() function:

ulimit -Hu 2068
ulimit -Su 2068
ulimit -Hn 65536
ulimit -Sn 65536

Here's where I put them:

7. Configure Oracle User's Environment

The installer is run from the oracle user. Before you can run the installer, however, the environment for oracle has to be set properly.

Create .bash_profile

While logged in as oracle, I created a file called .bash_profile using a text editor.

Execute .bash_profile

Run the .bash_profile as follows:

oracle$ . ./.bash_profile

8. Add Hostname to /etc/hosts

Because I performed my installation on machine at home, which gets its IP address through DHCP from a wireless router, I had to make sure the installer could find this IP address by adding it to my /etc/hosts file. You may not need to do this, depending on how your machine is networked. If, during the install process, you see an error related to the installer not being able to determine the IP address, stop the installer, perform these steps and launch the installer again. As Root, edit the file /etc/hosts and add an entry with your hostname and IP address. I determined my IP address by executing the following commands as root:

sh-2.05b# hostname
sh-2.05b# ifconfig -a

The first command returned sergio-g5.local. The second returned configuration details for each network device in my iMac. The one I'm using is en1, an Airport card. The IP address for that is 192.168.0.5. So, I added a line to my /etc/hosts file to make it look like this:

9. Install Oracle

Download the Files

If you're like me and you don't have the CDs, you can download the necessary files from OTN. To install the database, you'll need the file ship_mac_db.cpio.gz While you're there, you might as well get the Companion CD, ship_mac_companioncd.cpio.gz and HTML DB 1.6 as well. I downloaded the files to the desktop while logged using my own account, so for the oracle user to access these files, I first moved the file from the Desktop to the /tmp directory and then copied them there to a stage directory in /Volumes/u01/app/oracle/, owned by oracle by executing the following commands from within a Terminal:

sleuniss% cd Desktop
sleuniss% mv ship_mac_db.cpio.gz /tmp
sleuniss% su - oracle
oracle$ mkdir /Volumes/u01/app/oracle/stage
oracle$ cd /Volumes/u01/app/oracle/stage
oracle$ cp /tmp/ship_mac_db.cpio.gz .

Uncompress the File

While in the stage directory, issue the following commands to uncompress the file:

oracle$ gunzip ship_mac_db.cpio.gz
oracle$ cpio -idm < ship_mac_db.cpio

When I did this, I was presented with a message after the first command, Operation not permitted. Not sure what that was about, but it seemed harmless.

Launch Installer

To launch the installer, run the script runInstaller in the Disk1 directory.

oracle$ cd Disk1
oracle$ ./runInstaller

Keep clicking Next until you are asked to run a script as root. To do so, launch a new Terminal and become root. Then execute these commands:

sh-2.05b# cd /Volumes/u01/app/oracle/oraInventory/
sh-2.05b# ./orainstRoot.sh 

Accept all the default values, and keep clicking Next.

Choose Edition

When prompted to choose a database edition, I selected Enterprise.

Choose Starter Database

I chose to create a General Purpose starter database

Choose Character Set

I changed from the default to AL32UTF8. This is not necessary unless you plan to to use Unicode or multibyte characters in your database.

Database File Storage

Earlier, during preparation for this installation, I set up a location for database files in /Volumes/u02/oradata. When prompted for the location on the file system for database files, change the default value.

Database Schema Passwords

The installer allows you to set all password to be the same.

Install

After a quick glance at the install summary, it's time to kick off the installation.

Run root.sh

When prompted to run root.sh, run the following commands as root:

sh-2.05b# cd /Volumes/u01/app/oracle/product/10.1.0/g5db/
sh-2.05b# ./root.sh

End of Installation

You've reached the end of the installation

Logging in

To run SQL*PLUS, you'll need to configure the PATH to the executables. While logged in as oracle, execute these commands:

oracle$ cd /Volumes/u01/app/oracle/product/10.1.0/g5db
oracle$ export ORACLE_HOME=`pwd`
oracle$ export PATH=$PATH:$ORACLE_HOME/bin

Now you can run SQL*PLUS. You may want to add these environment variable settings to the oracle user's .bash_profile for subsequent sessions.

That's it, the 10g database is installed. In the next few weeks, I'll write up some instructions to install Oracle HTML DB as well.

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:

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