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.processstagingto
destinatio
n(parentid
in => :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)(HO
ST=xx.xx.x
x.115)(POR
T=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=list
ener11.trc
TRACE_DIRECTORY_LISTENER11
=/u5/app/o
racle/prod
uct/11.1.0
/network/t
race
and to add this parameter as well : ( with no success )
SUBSCRIBE_FOR_NODE_DOWN_EV
ENT_LISTEN
ER11=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.
Start Free Trial
