Solved

ORA-12547: TNS:lost contact

Posted on 2008-06-12
26
5,674 Views
Last Modified: 2013-12-19
Hi,

We are having serious issues for our Prod Db 9.2.0.8.0 patch set 13 hosted on windows 2003 sp2 .
Total Memory-15 GB SGA-8GB

Batches to this server are failing with error ORA-12547: TNS:lost contact.
This started to happen alot after i applied april security patch 2008 on out Prod-stdby DB.
raised a SR with Oracle too they asked for sqlnet traces after analyzing that they first blammed the network(which it is not as I hace confirmed that network is working absolutely fine)and than limitaions of 32 bit architecture unable to handle that huge SGA(8 Gb in our case).

I can the same error for sqlplus making a connection from the Prod server too(local connections also fails with this error.

As a workaround I rolled back the patch ,with this now our listner has become unstable it keeps on shutting down with error The OracleOraHome92TNSListener service terminated unexpectedly.  It has done this 1 time(s) and Faulting application TNSLSNR.EXE, version 0.0.0.0, faulting module oranl9.dll, version 9.2.0.8, fault address 0x0001cc90. in the EVENETVWR OF WINDOWS.

There is no firewall betweeb the client and server and also we are having issues on connections made from DB server itself(local connections).


During opatch apply and rollback there were no errors.


PLEASE HELP.



LOGs attached:listner.ora,sqlnet.ora,listner.log,sqlnet.log(server),alert.log,client trace(IT HAS 12547 ERROR IN IT) and others.

logs abstract

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

SQL*Plus: Release 9.2.0.8.0 - Production on Tue Jun 10 11:10:00 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

ERROR:
ORA-12547: TNS:lost contact


Enter user-name:





LISTNER LOG:

TNS-12502: TNS:listener received no CONNECT_DATA from client
13-JUN-2008 01:34:48 * 12502
TNS-12502: TNS:listener received no CONNECT_DATA from client
13-JUN-2008 01:34:49 * 12502
TNS-12502: TNS:listener received no CONNECT_DATA from client
13-JUN-2008 01:34:49 * 12502
TNS-12502: TNS:listener received no CONNECT_DATA from client



Client TRACE(FULL LOG ATTACHED):


4952) [000001 04-JUN-2008 08:41:39:378] nsbal: normal exit
(4952) [000001 04-JUN-2008 08:41:39:378] nsprecv: reading from transport...
(4952) [000001 04-JUN-2008 08:41:39:378] nttrd: entry
(4952) [000001 04-JUN-2008 08:41:39:487] ntt2err: entry
(4952) [000001 04-JUN-2008 08:41:39:487] ntt2err: soc 33888 error - operation=5, ntresnt[0]=517, ntresnt[1]=54, ntresnt[2]=0
(4952) [000001 04-JUN-2008 08:41:39:487] ntt2err: exit
(4952) [000001 04-JUN-2008 08:41:39:487] nttrd: exit
(4952) [000001 04-JUN-2008 08:41:39:487] nsprecv: transport read error
(4952) [000001 04-JUN-2008 08:41:39:487] nsprecv: error exit
(4952) [000001 04-JUN-2008 08:41:39:487] nserror: entry
(4952) [000001 04-JUN-2008 08:41:39:487] nserror: nsres: id=0, op=68, ns=12547, ns2=12560; nt[0]=517, nt[1]=54, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
(4952) [000001 04-JUN-2008 08:41:39:487] snsbitts_ts: entry
(4952) [000001 04-JUN-2008 08:41:39:487] snsbitts_ts: acquired the bit
(4952) [000001 04-JUN-2008 08:41:39:487] snsbitts_ts: normal exit
(4952) [000001 04-JUN-2008 08:41:39:487] snsbitcl_ts: entry
(4952) [000001 04-JUN-2008 08:41:39:487] snsbitcl_ts: normal exit
(4952) [000001 04-JUN-2008 08:41:39:487] nscon: error exit
(4952) [000001 04-JUN-2008 08:41:39:487] snsbitts_ts: entry
(4952) [000001 04-JUN-2008 08:41:39:487] snsbitts_ts: acquired the bit
(4952) [000001 04-JUN-2008 08:41:39:487] snsbitts_ts: normal exit
(4952) [000001 04-JUN-2008 08:41:39:487] nsdo: nsctxrnk=0
(4952) [000001 04-JUN-2008 08:41:39:487] snsbitcl_ts: entry
(4952) [000001 04-JUN-2008 08:41:39:487] snsbitcl_ts: normal exit
(4952) [000001 04-JUN-2008 08:41:39:487] nsdo: error exit
(4952) [000001 04-JUN-2008 08:41:39:487] nscall: unexpected response
(4952) [000001 04-JUN-2008 08:41:39:487] nsclose: entry
(4952) [000001 04-JUN-2008 08:41:39:487] nstimarmed: entry
(4952) [000001 04-JUN-2008 08:41:39:487] nstimarmed: no timer allocated
(4952) [000001 04-JUN-2008 08:41:39:487] nstimarmed: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitts_ts: entry
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitts_ts: acquired the bit
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitts_ts: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitcl_ts: entry
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitcl_ts: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] nsdo: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nsdo: cid=0, opcode=98, *bl=0, *what=0, uflgs=0x440, cflgs=0x2
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitts_ts: entry
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitts_ts: acquired the bit
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitts_ts: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] nsdo: rank=64, nsctxrnk=0
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitcl_ts: entry
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitcl_ts: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] nsdo: nsctx: state=1, flg=0x4201, mvd=0
(4952) [000001 04-JUN-2008 08:41:39:503] nsbfr: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nsbaddfl: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nsbaddfl: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] nsbfr: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] nsbfr: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nsbaddfl: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nsbaddfl: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] nsbfr: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitts_ts: entry
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitts_ts: acquired the bit
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitts_ts: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] nsdo: nsctxrnk=0
(4952) [000001 04-JUN-2008 08:41:39:503] nsdo: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] nsclose: closing transport
(4952) [000001 04-JUN-2008 08:41:39:503] nttdisc: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nttdisc: Closed socket 33888
(4952) [000001 04-JUN-2008 08:41:39:503] nttdisc: exit
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitts_ts: entry
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitts_ts: acquired the bit
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitts_ts: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] snsbittrm_ts: entry
(4952) [000001 04-JUN-2008 08:41:39:503] snsbittrm_ts: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitcl_ts: entry
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitcl_ts: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] nsclose: global context check-out (from slot 0) complete
(4952) [000001 04-JUN-2008 08:41:39:503] nsnadisc: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nadisc: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nacomtm: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nacompd: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nacompd: exit
(4952) [000001 04-JUN-2008 08:41:39:503] nacompd: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nacompd: exit
(4952) [000001 04-JUN-2008 08:41:39:503] nacomtm: exit
(4952) [000001 04-JUN-2008 08:41:39:503] nas_dis: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nas_dis: exit
(4952) [000001 04-JUN-2008 08:41:39:503] nau_dis: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nau_fad: exit
(4952) [000001 04-JUN-2008 08:41:39:503] nau_dis: exit
(4952) [000001 04-JUN-2008 08:41:39:503] naeetrm: entry
(4952) [000001 04-JUN-2008 08:41:39:503] naeetrm: exit
(4952) [000001 04-JUN-2008 08:41:39:503] naectrm: entry
(4952) [000001 04-JUN-2008 08:41:39:503] naectrm: exit
(4952) [000001 04-JUN-2008 08:41:39:503] nagbltrm: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nau_gtm: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nau_gtm: exit
(4952) [000001 04-JUN-2008 08:41:39:503] nagbltrm: exit
(4952) [000001 04-JUN-2008 08:41:39:503] nadisc: exit
(4952) [000001 04-JUN-2008 08:41:39:503] nsnadisc: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitts_ts: entry
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitts_ts: acquired the bit
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitts_ts: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] snsbittrm_ts: entry
(4952) [000001 04-JUN-2008 08:41:39:503] snsbittrm_ts: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitts_ts: entry
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitts_ts: acquired the bit
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitts_ts: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitcl_ts: entry
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitcl_ts: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] nsbfr: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nsbaddfl: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nsbaddfl: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] nsbfr: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] nsbfr: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nsbfr: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] nsmfr: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nsmfr: 2348 bytes at 0x3fe5520
(4952) [000001 04-JUN-2008 08:41:39:503] nsmfr: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitcl_ts: entry
(4952) [000001 04-JUN-2008 08:41:39:503] snsbitcl_ts: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] nsmfr: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nsmfr: 492 bytes at 0x3fb6a40
(4952) [000001 04-JUN-2008 08:41:39:503] nsmfr: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] nsclose: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] nscall: connecting...
(4952) [000001 04-JUN-2008 08:41:39:503] nsclose: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nsclose: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] nladget: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nladget: exit
(4952) [000001 04-JUN-2008 08:41:39:503] nsmfr: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nsmfr: 203 bytes at 0x377b2f0
(4952) [000001 04-JUN-2008 08:41:39:503] nsmfr: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] nsmfr: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nsmfr: 164 bytes at 0x37448c8
(4952) [000001 04-JUN-2008 08:41:39:503] nsmfr: normal exit
(4952) [000001 04-JUN-2008 08:41:39:503] nladtrm: entry
(4952) [000001 04-JUN-2008 08:41:39:503] nladtrm: exit
(4952) [000001 04-JUN-2008 08:41:39:503] nscall: error exit
(4952) [000001 04-JUN-2008 08:41:39:503] nioqper:  error from nscall
(4952) [000001 04-JUN-2008 08:41:39:503] nioqper:    nr err code: 0
(4952) [000001 04-JUN-2008 08:41:39:503] nioqper:    ns main err code: 12547
(4952) [000001 04-JUN-2008 08:41:39:503] nioqper:    ns (2)  err code: 12560
(4952) [000001 04-JUN-2008 08:41:39:503] nioqper:    nt main err code: 517
(4952) [000001 04-JUN-2008 08:41:39:503] nioqper:    nt (2)  err code: 54
(4952) [000001 04-JUN-2008 08:41:39:503] nioqper:    nt OS   err code: 0
(4952) [000001 04-JUN-2008 08:41:39:503] niomapnserror: entry
(4952) [000001 04-JUN-2008 08:41:39:503] niqme: entry
(4952) [000001 04-JUN-2008 08:41:39:503] niqme: reporting NS-12547 error as ORA-12547
(4952) [000001 04-JUN-2008 08:41:39:503] niqme: exit
(4952) [000001 04-JUN-2008 08:41:39:503] niomapnserror: returning error 12547
(4952) [000001 04-JUN-2008 08:41:39:503] niomapnserror: exit
(4952) [000001 04-JUN-2008 08:41:39:503] niotns: Couldn't connect, returning 12547

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

LET ME KNOW IF YOU NEED ANYTHING ELSE?


listner.zip
0
Comment
Question by:Atul_FGS
  • 13
  • 8
  • 5
26 Comments
 
LVL 47

Expert Comment

by:schwertner
ID: 21776419
0. Check the CONNECT_DATA parameter in the connection string. Find it in tnsname.ora.
1. Try to change the Network Card of the computer. Often it is defect and this causes errors.
2. Delete the contents of listener.log and alert.log files. Often they get over the 2 GB boundary.
3. Find hidden second and third firewalls like ZoneAlarm, TrendMicro etc. (server and client machine)
4. Investigate antivirus programs. All should not be only off but also uninstalled. (server and client machine)
5. Publish listener.ora file
0
 

Author Comment

by:Atul_FGS
ID: 21776486
Thanks schwertner for quick reply,

 I am having issues (TNS LOST CONTACT)also on DB server(local to DB) and the client too.

**This was all working fine for us for past few years,issues ecalated few week back and nothing changed on DB except the patch apply /rollback.

LISTENER.ORA(ALREADY ATTACHED)
**Tnsping works fine issue appears sometime not always.
###Prod-server-:PROD-ORACLE1

1-->If network card is faulty than the local connections from server should not fail,still please confirm??
2-->Listner.log and alert log were made less than 50 mb some 2 weeks ago it did not resolved the issue.
3-->Symantic antivirus is installed on server from begining and it never caused the issue ,still uif you insist I can ask teh admin for this.
5-->listner.ora and tnsnames.ora published.



*****************************
listner.ora
# LISTENER.ORA Network Configuration File: E:\oracle\ora92\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = prod-oracle1)(PORT = 1720))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = E:\oracle\ora92)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = CTIACOM.reged.com)
      (ORACLE_HOME = E:\oracle\ora92)
      (SID_NAME = CTIACOM)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = OEMREP.reged.com)
      (ORACLE_HOME = E:\oracle\ora92)
      (SID_NAME = OEMREP)
    )
  )

TNS>ORA (CLIENT)
# TNSNAMES.ORA Network Configuration File: D:\oracle\ora92\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
CTIAPT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = GWCTTEST1)(PORT = 1720))
    )
    (CONNECT_DATA =
      (SID = CTIAPT)
      (SERVER = DEDICATED)
    )
  )

CTIA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = PROD-ORACLE1)(PORT = 1720))
    )
    (CONNECT_DATA =
      (SID = CTIACOM)
    )
  )

STDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DR-ORACLE1)(PORT = 1720))
    )
    (CONNECT_DATA =
      (SID = STDBY)
    )
  )
**********************************************************************************
**********************************************************
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21777037
Possibly using NETCA you can recreate the Listener.
Save all files in .../network/admin directory before.
Creating the new listener possibly will help to avoid the problems.

Check also the version of the components:

set linesize 10000
SELECT SUBSTR(comp_id,1,15) comp_id, status, SUBSTR(version,1,10)
        version, SUBSTR(comp_name,1,30) comp_name
        FROM dba_registry ORDER BY 1;

0
 

Author Comment

by:Atul_FGS
ID: 21777146
Thanks schwertner,

This is something i did not thought about before.I can  recreate the listner using windows net configuration manager to see if it helps.
How do I check if there are any errosr in the listner(listner.log does not says much)

COMP_ID         STATUS          VERSION        COMP_NAME
--------------- --------------- -------------- -----------------------------
CATALOG         VALID           9.2.0.8.0      Oracle9i Catalog Views
CATPROC         VALID           9.2.0.8.0      Oracle9i Packages and Types

dropping Listner and creating a nwe one will require downtime,I will work on that.
0
 
LVL 16

Expert Comment

by:rolutola
ID: 21777712
On windows, Event Viewer could log an error.

R.
0
 
LVL 16

Expert Comment

by:rolutola
ID: 21777726
Also note that listener only works to establish new connections. Existing connections should be fine while you recreate your listener.
However, as this is windows, it may now allow you to create a new one with the same name immediately. I'm only guesing here because of the funny things that windows and its registry sometimes demands. However, you could create a new one with a different name. Just make sure you specify the same port so that you don't need to make any changes on the clients.

R.
0
 

Author Comment

by:Atul_FGS
ID: 21778048
But do you think it will resolve ORA-12547: TNS:lost contact ??
0
 
LVL 16

Expert Comment

by:rolutola
ID: 21778340
Do you get this when trying to establish a new connection or do you get it with existing session? I suspect with new sessions.
 
If you are using SID=<SID> in your tnsnames.ora, try changing to SERVICE_NAME=<SID> and try this.

R.
0
 
LVL 16

Expert Comment

by:rolutola
ID: 21778347
You can test with tnsping <alias>

R.
0
 

Author Comment

by:Atul_FGS
ID: 21778425
Some of the batches running have lsot contact error in between and also the new local connections from the DB serevr also fails.

But this was all working fine few weeks ago still a change in current tns is required???
0
 
LVL 16

Expert Comment

by:rolutola
ID: 21778722
Since you've applied patches and rolled back, this is a SIMPLE change that could resolve the issue you're facing now. As you must have read on other forums, if you encountered this in linux/unix you may have been advised to relink Oracle !!!
So I won't complain if changing tnsnames.ora resolves it would you not agree?

R.
0
 

Author Comment

by:Atul_FGS
ID: 21784425
Hi Rolutola,thanks for response.

I did read about relinking liberaries in Unix based system but as you are already aware this cannot be done in windows(if anyhow please let me know?).
I am applying patch 18 this weekend as I had simillar isssue 6 months back on our stagging db and this resolved it.
But my main worry is ORA-12547 Lost contact error,
i will also create a separate listner this weekened and test the application this week,
will update as soon as results are out.

Thanks.
0
 

Author Comment

by:Atul_FGS
ID: 21791813
Hi,

Applied 9.2.0.8 patch set 18 on the Prod DB,listner is up from last 30 + hours and has not crashed.
But the error ORA-12547: TNS:lost contact still continue to appear.


please see the Application log for the same.Also when I try to run the sql query manually (through sqlplus),it runs fine.

Please advice?



****************************************************************************************************
(I) 06/15/2008 00:15:03 XXDBMONTHLYEXPORT -> Start FormatXML
(I) 06/15/2008 00:15:03 XXDBMONTHLYEXPORT -> Get XSLPath
(I) 06/15/2008 00:15:03 XXDBMONTHLYEXPORT -> Agent ID237079
(I) 06/15/2008 00:15:03 XXDBMONTHLYEXPORT -> Start06152008121503
(E) 06/15/2008 00:15:03 : -> [Microsoft][ODBC driver for Oracle][Oracle]ORA-12547: TNS:lost contact
(E) 06/15/2008 00:15:03 : -> Description :
(I) 06/15/2008 00:15:03 XXDBMONTHLYEXPORT -> End06152008121503
(I) 06/15/2008 00:15:03 XXDBMONTHLYEXPORT -> Start FormatXML
(I) 06/15/2008 00:15:03 XXDBMONTHLYEXPORT -> Get XSLPath
(E) 06/15/2008 00:15:03 XXDBMONTHLYEXPORT -> The root element is missing.
(I) 06/15/2008 00:15:03 XXDBMONTHLYEXPORT -> Agent ID237080
(I) 06/15/2008 00:15:03 XXDBMONTHLYEXPORT -> Start06152008121503
(I) 06/15/2008 00:15:07 : -> LOA Retrieval Query :  SELECT A.LICENSE_ID_C,B.PROMPT_BUS_CODE_C,B.TERM_DATE_D  FROM TPRODUCER_LICENSE A, TPRODUCER_LOA_LICENSE B  WHERE A.AGENT_ID_C = ?  and A.LICENSE_ID_C = B.LICENSE_ID_C AND B.END_Z IS NULL order by A.LICENSE_ID_C
(I) 06/15/2008 00:15:07 : -> License Retrieval Query :  SELECT A.LICENSE_ID_C , A.AGENT_ID_C , A.LICENSE_TYPE_C , A.LICENSE_NO_C , A.STATE_ID_C , A.RES_STATE_I , A.LIC_STATUS_C ,  A.LIC_REQ_DATE_D , A.LIC_EFF_DATE_D , A.LIC_TERM_IND_I , A.RENEWAL_DATE_D , A.USER_DEFINED_C,  LIC_CUST_1 , LIC_CUST_2, LIC_CUST_3, LIC_CUST_4, LIC_CUST_5, LIC_CUST_DATE_1, LIC_CUST_DATE_2,A.TERM_DATE_D,A.END_Z,A.PE_IND_I  FROM TPRODUCER_LICENSE A  WHERE A.AGENT_ID_C = ?  AND A.LIC_STATUS_C IN (7028,7126,7128,7103,7107,7113,7114,7116,7012,7018,7011,7012)  AND A.AGENCY_ID_C IS NULL  order by A.LICENSE_ID_C
(I) 06/15/2008 00:15:07 : -> Inputs to GetLicXML method : Agent_id = 237080 *** State =  *** GrpLOA Flag = False


(I) 06/15/2008 00:17:03 XXDBMONTHLYEXPORT -> Start FormatXML
(I) 06/15/2008 00:17:03 XXDBMONTHLYEXPORT -> Get XSLPath
(I) 06/15/2008 00:17:03 XXDBMONTHLYEXPORT -> Agent ID237948
(I) 06/15/2008 00:17:03 XXDBMONTHLYEXPORT -> Start06152008121703
(I) 06/15/2008 00:17:04 : -> LOA Retrieval Query :  SELECT A.LICENSE_ID_C,B.PROMPT_BUS_CODE_C,B.TERM_DATE_D  FROM TPRODUCER_LICENSE A, TPRODUCER_LOA_LICENSE B  WHERE A.AGENT_ID_C = ?  and A.LICENSE_ID_C = B.LICENSE_ID_C AND B.END_Z IS NULL order by A.LICENSE_ID_C
(I) 06/15/2008 00:17:04 : -> License Retrieval Query :  SELECT A.LICENSE_ID_C , A.AGENT_ID_C , A.LICENSE_TYPE_C , A.LICENSE_NO_C , A.STATE_ID_C , A.RES_STATE_I , A.LIC_STATUS_C ,  A.LIC_REQ_DATE_D , A.LIC_EFF_DATE_D , A.LIC_TERM_IND_I , A.RENEWAL_DATE_D , A.USER_DEFINED_C,  LIC_CUST_1 , LIC_CUST_2, LIC_CUST_3, LIC_CUST_4, LIC_CUST_5, LIC_CUST_DATE_1, LIC_CUST_DATE_2,A.TERM_DATE_D,A.END_Z,A.PE_IND_I  FROM TPRODUCER_LICENSE A  WHERE A.AGENT_ID_C = ?  AND A.LIC_STATUS_C IN (7028,7126,7128,7103,7107,7113,7114,7116,7012,7018,7011,7012)  AND A.AGENCY_ID_C IS NULL  order by A.LICENSE_ID_C
(I) 06/15/2008 00:17:04 : -> Inputs to GetLicXML method : Agent_id = 237948 *** State =  *** GrpLOA Flag = False
(M) 06/15/2008 00:17:04 XEnDeCrypt -> Failure Of Init Method - -2147467259 - XPref : GetAcctPrefXPath strAcctId = 9200159213 strModuleDesc = ENC strXPath =  XPref : GetAcctPrefText strAcctId = 9200159213 strModuleDesc = ENC [CTISDataAccess2K.XCTDataFactory.fetchRS() - Failed to fetch Recordset.]
[CTISDataAccess2K - Database connection failed.]
[Oracle][ODBC][Ora]ORA-12547: TNS:lost contact

ADO Error 2:
[0x80004005][0x00000000][IM006]
SRC: Microsoft OLE DB Provider for ODBC Drivers
MSG: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ADO Error 1:
[0x80004005][0x00003103][S1000]
SRC: Microsoft OLE DB Provider for ODBC Drivers
MSG: [Oracle][ODBC][Ora]ORA-12547: TNS:lost contact


(E) 06/15/2008 00:17:04 : -> Failed to initialize the XEnDeCrypt component
(E) 06/15/2008 00:17:04 : -> Description :
(I) 06/15/2008 00:17:04 XXDBMONTHLYEXPORT -> End06152008121704
(I) 06/15/2008 00:17:04 XXDBMONTHLYEXPORT -> Start FormatXML
(I) 06/15/2008 00:17:04 XXDBMONTHLYEXPORT -> Get XSLPath
(E) 06/15/2008 00:17:04 XXDBMONTHLYEXPORT -> The root element is missing.
(I) 06/15/2008 00:17:04 XXDBMONTHLYEXPORT -> Agent ID237949
(I) 06/15/2008 00:17:04 XXDBMONTHLYEXPORT -> Start06152008121704
(I) 06/15/2008 00:17:04 : -> LOA Retrieval Query :  SELECT A.LICENSE_ID_C,B.PROMPT_BUS_CODE_C,B.TERM_DATE_D  FROM TPRODUCER_LICENSE A, TPRODUCER_LOA_LICENSE B  WHERE A.AGENT_ID_C = ?  and A.LICENSE_ID_C = B.LICENSE_ID_C AND B.END_Z IS NULL order by A.LICENSE_ID_C
(I) 06/15/2008 00:17:04 : -> License Retrieval Query :  SELECT A.LICENSE_ID_C , A.AGENT_ID_C , A.LICENSE_TYPE_C , A.LICENSE_NO_C , A.STATE_ID_C , A.RES_STATE_I , A.LIC_STATUS_C ,  A.LIC_REQ_DATE_D , A.LIC_EFF_DATE_D , A.LIC_TERM_IND_I , A.RENEWAL_DATE_D , A.USER_DEFINED_C,  LIC_CUST_1 , LIC_CUST_2, LIC_CUST_3, LIC_CUST_4, LIC_CUST_5, LIC_CUST_DATE_1, LIC_CUST_DATE_2,A.TERM_DATE_D,A.END_Z,A.PE_IND_I  FROM TPRODUCER_LICENSE A  WHERE A.AGENT_ID_C = ?  AND A.LIC_STATUS_C IN (7028,7126,7128,7103,7107,7113,7114,7116,7012,7018,7011,7012)  AND A.AGENCY_ID_C IS NULL  order by A.LICENSE_ID_C
(I) 06/15/2008 00:17:04 : -> Inputs to GetLicXML method : Agent_id = 237949 *** State =  *** GrpLOA Flag = False
******************************************************************************************************************


0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Expert Comment

by:schwertner
ID: 21792835
Please try to change the Network card.
I just changed a defect network card on one of our servers.
Investigate if you have access to machine from client:

ping IP

See if the listener is accessable:

tnsping db_alias
0
 

Author Comment

by:Atul_FGS
ID: 21792875
ping and tnsping works fine.network monitoring tolls confirms that there is no load on network.
I think now I will upgrade the oracle client (9.2.0.1) as db is at 9.2.0.8.odbc drivers are already new(2007)
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21792951
Good idea! 9.2.0.1 is very bugy!
0
 

Author Comment

by:Atul_FGS
ID: 21793010
Just found out the issue is at DB end only,please see below(don't think client upgradation will help now):

Any advice??
I think creating a new listner is good idea??


***********************************************************************
C:\Documents and Settings\atul.purswani>lsnrctl
LSNRCTL for 32-bit Windows: Version 9.2.0.8.0 - Production on 16-JUN-2008 08:38:01
Copyright (c) 1991, 2006, Oracle Corporation.  All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prod-oracle1)(PORT=1720)))
TNS-12547: TNS:lost contact
 TNS-12560: TNS:protocol adapter error
  TNS-00517: Lost contact
   32-bit Windows Error: 54: Unknown error
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
TNS-01153: Failed to process string:
 NL-00313: null pointer or null size
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prod-oracle1)(PORT=1720)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 9.2.0.8.0 - Production
Start Date                14-JUN-2008 23:29:23
Uptime                    1 days 9 hr. 8 min. 45 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   E:\Oracle\Ora92\network\admin\listener.ora
Listener Log File         E:\Oracle\Ora92\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prod-oracle1.reged.com)(PORT=1720)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))
Services Summary...
Service "CTIACOM.reged.com" has 1 instance(s).
  Instance "CTIACOM", status UNKNOWN, has 1 handler(s) for this service...
Service "OEMREP.reged.com" has 2 instance(s).
  Instance "OEMREP", status UNKNOWN, has 1 handler(s) for this service...
  Instance "OEMREP", status READY, has 1 handler(s) for this service...
Service "OEMREPXDB.reged.com" has 1 instance(s).
  Instance "OEMREP", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>
*******************************************************************************************
0
 

Author Comment

by:Atul_FGS
ID: 21793150
**(don't think client upgradation will NOT help now):
0
 

Author Comment

by:Atul_FGS
ID: 21793305
ignore last comment

**(don't think client upgradation will NOT help now):
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21793730
see the file listener.ora size.
If it is big (2 GB) clear it.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21793797
See your connect descriptor.
Try also to change SID with SERVICE_NAME:

CTIA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = PROD-ORACLE1)(PORT = 1720))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = CTIACOM)
    )
  )



Oracle Error : TNS-12502: TNS:listener received no CONNECT_DATA from client

Cause: No CONNECT_DATA was passed to the listener.

Action: Check that the service name resolved from TNSNAMES.ORA has the CONNECT_DATA component of the connect descriptor.

0
 

Author Comment

by:Atul_FGS
ID: 21828772
Now we have found that PTE for windows server is too low ,currently few of the non required services
have been disabled and will configure the lock pages in memeory option.
last day the error did not occured.So moitoring the issue.
Will update soon in few days.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21828952
Check if there is a service TNSLSNR.EXE that consumes most
of the CPU resources. There are two opinions, but it seems
that this is a virus masked under this name.

Also check the free ports range.
Oracle Listener redirects the ports after starting the user process.
So it needs a range of free available  ports to do this.
0
 

Author Comment

by:Atul_FGS
ID: 21931967
Now we have stopped few of the windows servieces and stopped the catalog db on the same server.with this issue is resolved.
It looks like OS is incapable to use the memeory.
Also found:


http://www.microsoft-oracle.com/assets/pdf/oracle_performance_em64t_6850.pdf

0
 
LVL 47

Expert Comment

by:schwertner
ID: 21932486
8 GB SGA seems to be to big for me.
Big memories need specials keys in Windows.
0
 

Accepted Solution

by:
Atul_FGS earned 0 total points
ID: 21932649
Agree ,the /pae and 3 gb option still does not support,we are struck in this I think moving to 64 bit should resolve this.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now