Solved

Oracle Client hangs when running a long pl/sql procedure

Posted on 2008-10-10
5
2,085 Views
Last Modified: 2012-05-05
We have a problem running a long pl/sql procedure on our UPGRADED 11.1.0.6 database on solaris 9 64bit machine. ( it was upgraded from 9, which the same logic run properly on ).
the client side just hangs and waits for a return signal from the database, although it seems like the database logic run and ended, after a little more then an hour.

the call is a simple call for a procedure :
newimport.processstagingtodestination(parentidin => :parentidin,
                                                   publishertoppccidin => :publishertoppccidin,
                                                   subparentidin => :subparentidin,
                                                   subcategoryname => :subcategoryname,
                                                   companygroupidin => :companygroupidin,
                                                   vendorsyncidprefixin => :vendorsyncidprefixin,
                                                   importidin => :importidin,
                                                   instanceidin => :instanceidin)

The database alert log shows the following :

Fatal NI connect error 12170.

  VERSION INFORMATION:
    TNS for Solaris: Version 11.1.0.6.0 - Production
    Oracle Bequeath NT Protocol Adapter for Solaris: Version 11.1.0.6.0 - Production
    TCP/IP NT Protocol Adapter for Solaris: Version 11.1.0.6.0 - Production
  Time: 10-OCT-2008 13:29:19
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535

TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505

TNS-00505: Operation timed out
    nt secondary err code: 145
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xx.xx.xx.115)(PORT=3185))

the host ID is the host that ran the process.
there is no other error/ORA message on the database trace files.

the sqlnet.ora file looked like that :

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

and we tried to add/change the following parameters without any success :
NAMES.CONNECT_TIMEOUT=0  ( also 30 did not work)
SQLNET.EXPIRE_TIME=1 ( also 10 and 0 did not work )
TCP.NODELAY=yes

the listener.ora file looked like that :
LISTENER11 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = XX)(PORT = 1523))
    )
  )

SID_LIST_LISTENER11 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = UPGTEST)
      (ORACLE_HOME = /u5/app/oracle/product/11.1.0)
      (SID_NAME = UPGTEST)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = FPROD1)
      (ORACLE_HOME = /u5/app/oracle/product/11.1.0)
      (SID_NAME = FPROD1)
    )
)


we started the listener with trace level 16 :
TRACE_LEVEL_LISTENER11=16
TRACE_FILE_LISTENER11=listener11.trc
TRACE_DIRECTORY_LISTENER11=/u5/app/oracle/product/11.1.0/network/trace

and to add this parameter as well : ( with no success )
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER11=OFF

the initial call for the procedure was from the application server which used oracle client 9.
then we tried it with 10 and 11 as well ( from the application server and from the sqlplus directly ) -
again we were getting the same error.

I will mention again that the database is upgraded from 9 to 11.
we have another fresh 11g database installation on linux and we don't get those errors.
the listener is listening on port 1523 ( and not the standard 1521 port, because the old listener that still serve version 9 database is listening on 1521 port ).

from the listener trace file, the only thing that looks a little suspicious is ( but i don't know if its related ) :

2008-10-10 14:02:46.831771 : nsnainit:"or" info flags: 0xd  Translations follow:
    native service(s) is (are) wanted
    NA disabled remotely for this connection
    NA services unavailable on both processes - negotiation not needed

2008-10-10 14:02:46.831805 : nsnainit:"or" info flags: 0xd  Translations follow:
    native service(s) is (are) wanted
    NA disabled remotely for this connection
    NA services unavailable on both processes - negotiation not needed
"and" info flags: 0xd   Translations follow:
    native service(s) is (are) wanted
    NA disabled remotely for this connection
    NA services unavailable on both processes - negotiation not needed

if anyone encountered this kind of behavior, or have a thought on how to find the problem, it will be highly appreciated.
0
Comment
Question by:structuredweb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 27

Expert Comment

by:sujith80
ID: 22700284
Are you accessing any DB links?
If so, have you checked whether they are working fine?
0
 
LVL 1

Author Comment

by:structuredweb
ID: 22702842
Hi sujith, thanks for the comment.
we found the  parameter that solves the issue....

In sqlnet.ora file we defined SQLNET.EXPIRE_TIME=1 which solve the disconnections.
The firewall probably disconnected "hanged" sessions, and although we tried this parameter with value of 10 and 0, it did not solve the problem till we changed it to 1.

I will be happy if someone can provide explanation for that, just for every ones knowledge.

Thanks.
0
 
LVL 1

Author Comment

by:structuredweb
ID: 22706670
The parameter that we are talking about above (EXPIRE_TIME in the sqlnet.ora file) was not defined for our oracle 9i version ( which mean the default was suppose to be 0 ). this should also work, but it didn't.

if someone can provide an explanation for this behavior, cause it seems that there is a change of behavior between the Oracle 9i and 11g enterprise database.

We are about to upgrade the production, but do not want to take any risk with this issue.

we have a firewall configured to disconnect TCP connection after 30 minutes of idle time.

Thanks
0
 

Accepted Solution

by:
ee_auto earned 0 total points
ID: 23269153
Question PAQ'd, 500 points refunded, and stored in the solution database.
0

Featured Post

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.  …
SSL is a very common protocol used these days when browsing the web.  The purpose is to provide security to communication, but how does it do it?  There are several pieces at work that have to be setup before SSL will even work and it requires both …
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

751 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