Error
ORA-12518: TNS:listener could not hand off client connection
----------------------
-----------------ALERT LOG--------------------
Thu Sep 09 11:13:39 2010
Process startup failed, error stack:
Thu Sep 09 11:13:39 2010
Errors in file d:\oracle\product\10.2.0\admin\sns1011\bdump\sns6_psp0_7760.trc:
ORA-27300: OS system dependent operation:CreateThread failed with status: 8
ORA-27301: OS failure message: Not enough storage is available to process this command.
ORA-27302: failure occurred at: ssthrddcr
-------------------sns6_psp0_7760.trc---------------
CPU : 8 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:2050M/8186M, Ph+PgF:49980M/56271M, VA:24M/3071M
Instance name: sns6
Redo thread mounted by this instance: 1
Oracle process number: 3
Windows thread id: 7760, image: ORACLE.EXE (PSP0)
*** 2010-09-09 11:13:41.458
Process startup failed, error stack:
ORA-27300: OS system dependent operation:CreateThread failed with status: 8
ORA-27301: OS failure message: Not enough storage is available to process this command.
ORA-27302: failure occurred at: ssthrddcr
------------------udump\sns6_ora_5440.trc-------------
Windows Server 2003 Version V5.2 Service Pack 2
CPU : 8 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1784M/8186M, Ph+PgF:49813M/56271M, VA:32M/3071M
Instance name: sns6
Redo thread mounted by this instance: 1
Oracle process number: 270
Windows thread id: 5440, image: ORACLE.EXE (SHAD)
*** 2010-09-09 11:12:27.050
*** ACTION NAME:() 2010-09-09 11:12:27.003
*** MODULE NAME:(ld.exe) 2010-09-09 11:12:27.003
*** SERVICE NAME:(SYS$USERS) 2010-09-09 11:12:27.003
*** SESSION ID:(345.13810) 2010-09-09 11:12:27.003
kxfpg1srv
could not start local P001
kxfpg1srv
could not start local P001
kxfpg1srv
could not start local P001
kxfpg1srv
could not start local P004
kxfpg1srv
could not start local P006
kxfpg1srv
could not start local P006
-----------------LISTENER LOG--------------------
TNS-12518: TNS:listener could not hand off client connection
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
32-bit Windows Error: 233: Unknown error
-------------------------
Windows 233 error means "No process is on the other end of the pipe."
-----client machine-------------
ping 10.100.0.65 -t
tnsping 10.100.0.65 1521
nslookup 10.100.0.65
--------------
lnsrctl startus
memory is efficient for process and make connnection
SGA
RAM 8 GB
virtual memory 8 GB * 1.5 = 12GB or System managed for c: will be good
D:\oracle\product\10.2.0\db_1\network\log\listener.log size
System Properties> Startup Recovery> settings>boot.ini
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /PAE /3GB /NoExecute=OptOut
----------------
exclude database files folders from McAfee on-access scan
--------------
select username,count(*) from v$session group by username having count(*) > 1 order by 2 ;
SQL> select * from v$resource_limit where resource_name in ('sessions','processes');
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
processes 233 339 500 500
sessions 241 345 555 555
SQL> select name, value from v$sysstat where name like '%logon%';
NAME VALUE
---------------------------------------------------------------- ----------
logons cumulative 89876
logons current 216
SQL> select count(*) from v$session;
COUNT(*)
----------
260
SQL> select count(*) from v$process;
COUNT(*)
----------
261
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 35
log_archive_max_processes integer 2
processes integer 500
SQL> show parameter sessions
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
logmnr_max_persistent_sessions integer 1
sessions integer 555
shared_server_sessions integer
SQL> SHOW PARAMETER SGA
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1600M
sga_target big integer 1600M
SQL> select name,display_value from v$parameter where name in
2 (
3 'job_queue_processes',
4 'open_cursors',
5 'pga_aggregate_target',
6 'processes',
7 'sessions',
8 'sga_target',
9 'sga_max_size'
10 );
NAME DISPLAY_VALUE
-------------------------------------------------------------------------------- -------------------
processes 500
sessions 555
sga_max_size 1600M
sga_target 1600M
job_queue_processes 35
open_cursors 300
pga_aggregate_target 322M
7 rows selected.
SQL> select round(sum(bytes)/1024/1024,2) total_sga,
2 round(sum(decode(name,'free memory',bytes,0))/1024/1024,2) free,
3 round((sum(decode(name,'free
4 memory',bytes,0))/1024/1024)/(sum(bytes)/1024/1024)*100,2) free_per
5 from
6 v$sgastat;
TOTAL_SGA FREE FREE_PER
--------- ---------- ----------
1627.04 80.69 0
SQL> select sum(pins) "Executions",sum(reloads) "Cache Misses while Executing",round(sum(reloads)/sum(pins)*100,2)
2 AS "Misses Ratio, %" from V$LIBRARYCACHE;
Executions Cache Misses while Executing Misses Ratio, %
---------- ---------------------------- ---------------
159472017 3209055 2.01
SQL> select sum(pins) "Executions",sum(reloads) "Cache Misses while Executing",round((sum(pins)/(sum(reloads)+sum(pins)))*100,
2 "Hit Ratio, %" from V$LIBRARYCACHE;
Executions Cache Misses while Executing Hit Ratio, %
---------- ---------------------------- ------------
159472378 3209055 98.03
----------------------------------------------------------------------------------------------------------------
Thu Sep 09 11:13:39 2010
Process startup failed, error stack:
Thu Sep 09 11:13:39 2010
Errors in file d:\oracle\product\10.2.0\admin\sns1011\bdump\sns6_psp0_7760.trc:
ORA-27300: OS system dependent operation:CreateThread failed with status: 8
ORA-27301: OS failure message: Not enough storage is available to process this command.
ORA-27302: failure occurred at: ssthrddcr
Cause
On Windows, each session creates a new thread, and by default 1MB is allocated for the session when it connects. How much memory is in the server, and is this the 32 bit or 64 bit version of Windows 2003. The server might be hitting the upper limit for maximum memory per process, which is either 2GB or 3GB depending on the settings in the server's boot.ini.
OS resource issue.
The maximum amount of addressable memory by a process ('oracle.exe' for example) running in a
32-Bit Windows environment has been reached.
In a 32-Bit Windows environment, the total addressable memory by a single process is a 4GB. On
Windows, the OS reserves half of this memory by default for kernel memory, leaving 2GB of
addressable memory for a user process. By placing the '/3GB' switch in the 'boot.ini' file, this
changes the ratio of kernel memory and user memory. When the '/3GB' switch is in place, processes (such as 'oracle.exe’) can address 3GB of virtual memory out of the total 4GB of addressable memory. However, once the 3GB of virtual memory is depleted, the process will fail.
Solution
To implement the solution, please execute the following steps:
Tune the application running on the 32-bit environment so that it will not consume greater than
2.7GB of virtual memory when the '/3GB' switch is set in the 'boot.ini' file.
In the case of Oracle encountering the OS resource issue, the options are:
1) Reduce the amount of SGA needed to be allocated for the database.
2) Limit the number of dedicated connections to the database and the amount of memory each user
process will consume.
3) Change from dedicated connections to multi-threaded server (MTS) connections as MTS only
uses a fraction of the amount of memory allocated to each user process when initial connection to
the database is established.
----------------
1.
Enable 4GB RAM Tuning (4GT). 32-bit Windows 2000 Server (Advanced and Data center editions) and 32-bit Windows Server 2003 (Enterprise and Data center editions) include a feature called 4GB RAM Tuning (4GT). This feature allows memory-intensive Windows applications to directly access up to 3GB of memory as opposed to the standard 2GB that is allowed in other Windows editions. The obvious benefit to the Oracle database is that 50% more memory becomes available for database use, which can increase SGA sizes or connection counts. . The only configuration change required is to ensure that the /3GB flag is used in Windows 'boot.ini file. See http://support.microsoft.com/?kbid=291988 Enable 4GB RAM Tuning (4GT). 32-bit Windows 2000 Server (Advanced and Data center editions) and 32-bit Windows Server 2003 (Enterprise and Data center editions) include a feature called 4GB RAM Tuning (4GT). This feature allows memory -intensive Windows applications to directly access up to 3GB of memory as opposed to the standard 2GB that is allowed in other Windows editions. The obvious benefit to the Oracle database is that 50% more memory becomes available for database use, which can increase SGA sizes or connection counts.. The only change required is to ensure Configuration that the / 3GB flag is used in Windows' Boot.ini File. See http://support.microsoft.com/?kbid=291988
2. 2.
On Windows 2003 we can address more than 4gb of memory, via PAE and USE_INDIRECT_DATA_BUFFERS On Windows 2003 we can address more than 4gb of memory, via PAE and USE_INDIRECT_DATA_BUFFERS
USE_INDIRECT_DATA_BUFFERS controls how the system global area (SGA) uses memory. USE_INDIRECT_DATA_BUFFERS controls how the system global area (SGA) uses memory.
It enables or disables the use of the extended buffer cache mechanism for 32- bit platforms that can support more than 4 GB of physical memory. See Note 342080.1 It enables or disables the use of the extended buffer cache mechanism for 32 - bit platforms that can support more than 4 GB of physical memory. See Note 342080.1
3. 3.
Decrease the size of the database SGA (decrease parameters like shared_pool_size, sort areas parameters, large_pool_size, etc.) giving memory back to the O/S, allowing more connections to be established. A good example is the sga_max_size parameter. Oracle must 'reserve' this size in the process address space. If it does not, then something else may come along, use the space and then the SGA cannot be expanded. But if the SGA is never increases to the value of sga_max_size, then the memory is wasted. Reserved for no reason. Decrease the size of the database SGA (decrease parameters like shared_pool_size, sort areas parameters, large_pool_size, etc.) Giving memory back to the O / S, allowing more connections to be established. A good example is the sga_max_size parameter. Oracle must 'reserve 'this size in the process address space. If it does not, then something else may come along, use the space and then the SGA cannot be expanded. But if the SGA i
------------------
----------------------------------------------Turn On Listener Tracing--------------------------------------------
TRACE_LEVEL_LISTENER= USER
TRACE_TIMESTAMP_listener=true
Reload
Turn on listener tracing and re-execute the operation. Verify that the
listener and database instance are properly configured for direct
handoff.
At times, when troubleshooting connectivity issues or performance issues, you might need to run listener trace (server side) and/or a sqlnet trace (client side trace). Oracle support staff will also ask for these files when you enlist their help in troubleshooting these issues. Here are the steps on how you can do that:
To enable sqlnet trace (client side trace):
Add the following to the sqlnet.ora file on the application server(s) from where the connections are being made to the Oracle database:
Trace_level_client=16
Trace_directory_client=
Trace_unique_client=on
Trace_timestamp_client=on
To enable listener trace (server side):
Add the following to the listener.ora file on the db server before starting the listener:
TRACE_TIMESTAMP_
TRACE_DIRECTORY_<>=
Here
lsnrctl> set current_listener
lsnrctl> set trc_level support
You can run both trace at the same time for 15 minutes or so in order to get some meaningful data set.
To stop the sqlnet trace:
Remove the following entries from sqlnet.ora or comment them out by using the # sign:
Trace_level_client=16
Trace_directory_client=
Trace_unique_client=on
Trace_timestamp_client=on
To stop the listener trace:
lsnrctl> set trc_level off
TRACE_LEVEL:It specifies the level of detail the trace facility records for the listener.The trace level value can either be a value within the range of 0 (zero) to 16 (where 0 is no tracing and 16 represents the maximum amount of tracing) or a value of off, admin, user, or support.
i))off (equivalent to 0) provides no tracing.
ii))user (equivalent to 4) traces to identify user-induced error conditions.
iii)admin (equivalent to 6) traces to identify installation-specific problems.
iv)support (equivalent to 16) provides trace information for troubleshooting information for Oracle Support Services.
------------------
LOGGING_LISTENER = on
TRACE_LEVEL_LISTENER= USER
TRACE_FILE_LISTENER=listener.trc
TRACE_DIRECTORY_LISTENER=d:\oracle\product\10.2.0\db_1\network\trace
--------------------------
