Oracle Database Discussions

Hi

We have a 11g (11.2.0.2.0) on a Win2K8 R2 and since a few weeks our clients have very often some 03114 errors and the ERP is aborting.
First I realized the Open_Cursors were set too low on the server so I increased it to 2000 but it looks like it wasn't the only one problem.

TNSPING normaly has 0 or 10 msec but every 10 seconds it has a very long delay with more than 2000 or sometimes 3000 msec!!!

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 172.17.37.70)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PCERP)))
OK (10 msec)

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 172.17.37.70)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PCERP)))
OK (2010 msec)

I thought we have maybe a Network problem and that's why we get some 03114 from time to time on the client side, but I have the same on the Database sever istself! So a TNSPIN on the 2008 R2 host should not go out to the LAN, right? So if I have the same behaviour on the Server, sometimes 3000msec it must be a Oracle DB or Listener problem, agree?

I checked the Listener.log but nothing looks like an error.
The Listener Alert log has every second an entry like below:

msg time='2012-06-08T12:14:45.978+02:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='PROCONCEPT'
host_addr='::1'>
<txt>08-JUN-2012 12:14:43 * (CONNECT_DATA=(SERVICE_NAME=PCERP)(CID=(PROGRAM=D:\PCS\services.erp\SVC_exeExchangeDataIn.exe)(HOST=PROCONCEPT)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=52410)) * establish * PCERP * 0
</txt>

or

msg time='2012-06-08T09:14:39.254+02:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='PROCONCEPT'
host_addr='::1'>
<txt>08-JUN-2012 09:14:39 * service_update * pcerp * 0
</txt>
</msg>

These ::1 entries are looking like IPV6 adresses, but we have disabled the V6 protocol on the Win2k8 server and what means 'UNKNOWN level=16'?

The rdbms/instance/alert log looks very similar:

***********************************************************************
</txt>
</msg>
<msg time='2012-05-24T16:53:24.134+02:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='PROCONCEPT'
host_addr='::1'>
<txt>
Fatal NI connect error 12170.
</txt>
</msg>
<msg time='2012-05-24T16:53:24.134+02:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='PROCONCEPT'
host_addr='::1'>
<txt>
VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
</txt>
</msg>
<msg time='2012-05-24T16:53:24.135+02:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='PROCONCEPT'
host_addr='::1'>
<txt> Time: 24-MAY-2012 16:53:24
</txt>
</msg>
<msg time='2012-05-24T16:53:24.135+02:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='PROCONCEPT'
host_addr='::1'>
<txt> Tracing not turned on.
</txt>
</msg>
<msg time='2012-05-24T16:53:24.135+02:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='PROCONCEPT'
host_addr='::1'>
<txt> Tns error struct:
</txt>
</msg>
<msg time='2012-05-24T16:53:24.135+02:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='PROCONCEPT'
host_addr='::1'>
<txt> ns main err code: 12535
</txt>
</msg>
<msg time='2012-05-24T16:53:24.136+02:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='PROCONCEPT'
host_addr='::1'>
<txt>
</txt>
</msg>
<msg time='2012-05-24T16:53:24.136+02:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='PROCONCEPT'
host_addr='::1'>
<txt>TNS-12535: TNS:operation timed out
</txt>
</msg>
<msg time='2012-05-24T16:53:24.136+02:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='PROCONCEPT'
host_addr='::1'>
<txt> ns secondary err code: 12606
</txt>
</msg>
<msg time='2012-05-24T16:53:24.137+02:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='PROCONCEPT'
host_addr='::1'>
<txt> nt main err code: 0
</txt>
</msg>
<msg time='2012-05-24T16:53:24.137+02:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='PROCONCEPT'
host_addr='::1'>
<txt> nt secondary err code: 0
</txt>
</msg>
<msg time='2012-05-24T16:53:24.137+02:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='PROCONCEPT'
host_addr='::1'>
<txt> nt OS err code: 0
</txt>
</msg>
<msg time='2012-05-24T16:53:24.137+02:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='PROCONCEPT'
host_addr='::1'>
<txt> Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=60540))
</txt>
</msg>
<msg time='2012-05-24T16:53:24.138+02:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='PROCONCEPT'
host_addr='::1' pid='6432'>
<txt>WARNING: inbound connection timed out (ORA-3136)
</txt>
</msg>
<msg time='2012-05-24T17:01:24.155+02:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='PROCONCEPT'
host_addr='::1'>
<txt>

***********************************************************************


ORACLE_HOME and ORACLE_SID isn't set on the variables, but I guess it's not needed anymore on Win2k8, right?

How can I do further tests to find out where the problem is?

Thank you very much for any suggestion.

Regards