Re: Oracle TNS error

carol.mcalear_at_pulsion.co.uk (Carol) wrote in message news:<5fdaf21e.0305030347.75467d5b_at_posting.google.com>...
> Hi
>
> I am developer who has recently been giving the job of looking after 2
> oracle databases.
>
> Details are:
> Win 2K SP3
> Oracle9i Enterprise Edition Release 9.2.0.3.0
> On Compaq Proliant DL580 G2 with 2GB Ram and 2*1595mhz Processors.
> I have two databases running on this server one is accessed only via
> an internal network and the other is accessed via the internal network
> and separate network (only real detail I know is that there are Cisco
> routers and the size of the line is 2mb). The main access to the
> database is via a Delphi application, which sits on the client. The
> error only occurs on the database that is being accesses by both the
> networks. I have spoken to the network admin and he is adamant that
> there is nothing wrong with the networks (if there is something wrong
> I will have to prove it and even then he will probably not take any
> notice). The databases are set up in dedicated mode and I have approx
> 100 users accessing the database at any one time. I have spent time
> tuning the databases the best I can and the CPU seems to run between
> 20% and 40%, users are not complaining about speed until this error
> occurs and then the system seems to slow down (Only on the one
> database). It doesn't seem to recover and the best way I have of
> clearing it is to shutdown and restart the database and this is
> causing a lot of grief.
> In the alert log I get:
> Errors in file d:\oracle\admin\dbname\udump\dbname_ora_3960.trc:
> ORA-12571: TNS:packet writer failure
> When I look in the trace file I get:
> *** SESSION ID:(49.246) 2003-05-02 15:53:06.000
> FATAL ERROR IN TWO-TASK SERVER: error = 12571
> *** 2003-05-02 15:53:06.000
> ksedmp: internal or fatal error
> ORA-12571: TNS:packet writer failure
> Further on in the trace file I get:
> Argument/Register addr=0x0268ED54.
> Dump of memory from 0x0268ED14 to 0x0268EE54
> 268ED10 3A20736C 62647520 73666564 [ls : udbdefs]
> 268ED20 00000000 7069706F 693A736C 726F636E [....opipls:incor]
> 268ED30 74636572 2E6F6E20 20666F20 646E6962 [rect no. of bind]
> 268ED40 00000073 7069706F 733A736C 74736C71 [s...opipls:sqlst]
> 268ED50 00000072 41544146 5245204C 20524F52 [r...FATAL ERROR ]
> 268ED60 54204E49 542D4F57 204B5341 56524553 [IN TWO-TASK SERV]
> 268ED70 203A5245 6F727265 203D2072 000A6425 [ER: error = %d..]
> 268ED80 626F7250 656C6261 72726520 2820726F [Probable error (]
> 268ED90 2D41524F 34353832 69202936 656E206E [ORA-28546) in ne]
> 268EDA0 726F7774 6461206B 696E696D 61727473 [twork administra]
> 268EDB0 6E6F6974 0000000A 7774654E 206B726F [tion....Network ]
> 268EDC0 746F7270 6C6F636F 72726520 6F20726F [protocol error o]
> 268EDD0 6966206E 20747372 61746164 74666120 [n first data aft]
> 268EDE0 6E207265 63207765 656E6E6F 000A7463 [er new connect..]
> 268EDF0 7469706F 315F6B73 00000000 7469706F [opitsk_1....opit]
> 268EE00 325F6B73 00000000 2D2D2D2D 2D2D2D2D [sk_2....--------]
> 268EE10 2D2D2D2D 2D2D2D2D 2D2D2D2D 65520A2D [-------------.Re]
> 268EE20 65746F6D 4C515320 5253442D 61747320 [mote SQL-DSR sta]
> 268EE30 656D6574 0A3A746E 00000000 7869706F [tement:.....opix]
> 268EE40 312D6272 00000038 7869706F 312D6272 [rb-18...opixrb-1]
> 268EE50 00000037 [7...]
>
> This error occurs on different SQL sometimes select statement and
> sometimes insert or update statements also different tables are
> involved.
>
> Please Help
>
> Thanks in Advance
>
> Carol

Firstly, If you are a newbie with Oracle DBA stuff there are some excellent areas on the net in the Oracle.com website, in particular AskTom allows you to look up, submit questions relating to just about all aspects of Oracle technologies SLQ, PL/SLQ, DBA stuff etc. Tom Kite the Oracle guru provides answers on all aspects of config, backup strategies, errors.

Also, I would assume that as the databases are Oracle 9i you should have support sorted out with Oracle. If that is the case you can register with Oracle Metalink, which allows you to make support calls to Oracle engineers via the net. Just go to the Oracle.com website and search for Metalink. I've used it on a number of occassions and its very useful and Oracle are 'usually' very helpful.

I think your problem could be network related, I have an Oracle 8i database which exibits a similar problem - when clients connect/re-connect sometimes the database server CPU goes into a 100% CPU spin, thus client transactions slow down (similar to your prob). This CPU spin sometimes clears itself or clients need restarting.

The problem with this system appears to lie in the network connectivity between clients and database server (TCP/IP), your network guy should be able to provide you with some info which will be of use;

1. What's the speed of your network 10Mbs/100Mbs etc (you quote 'size of line 2Mb' but that seems weird to me standard network speeds are as above. If you are on a 10Mb network with 100 clients I reckon you are having network saturations which is causing your database error.
2. You should request your network support to provide some stats wrt to network usages, errors which you may be able to marry up with the occurrences of your database problem.
3. Do you have access to the developers of the Delphi client software, what components do they use to 'talk' to the database ODAC etc, if you contact Oracle they will want to know this sort of info.

I would agree that a good idea would be to enable trace on the clients and probably set up trace on the TNSListener on the server as well, which will log client connections.

Regards

Andorz

PS the network guy probably totally believes that his network is OK...from a networking techie perspective but its the interaction of it with Oracle that matters. Put the boot on the other foot, get him to prove that his network is OK. Received on Sat May 03 2003 - 22:34:49 CEST