13 replies to this topic

[Rory]

Hi

Our users are getting Oracle error ORA-12516 while testing a new ProIV screen (see attached).

I looked up Oracle error ORA-12516 and some of the comments on it are as follows

"You are saturating your available connections.
Increase in your instance the parameters: "sessions" and "processes" (if
you increase sessions then processes should autoincrease accordingly)."

"Check to make sure that the service handlers (e.g. dispatchers)
for the given SERVICE_NAME are registered with the listener, are accepting
connections, and that they are properly configured to support the desired
protocols."

Is it definitely an Oracle problem or could something in Proiv be causing it?

Thanks
Rory

[Neil Hunter]

Sounds like Oracle to me.

Have you tried increasing the processes and sessions parameters on your init[database].ora file?

[andykay]

Hi Rory,

There could be couple of reasons for "TNS-12516 (See [url="http://TNS-12516.ora-code.com)""]http://TNS-12516.ora-code.com)"[/url] .

+ This is usually seen in case of RAC with multiple instances running and when one of the
instances crashes or becomes unavailable for some time.

+ Due to this, the instance status does not get registered INSTANTLY with the listener
service being used for connectivity.
Because of this delay the TNS error is reported and once the registration happens the error goes off .

+ One can check whether the the status of the service when error comes up using
"lsnrctl service <SERVICENAME>"
- error gets reported if the connections are in a blocked status.

+ Possible ways of fixing is
a) execute command
"alter system register;" - Instance will force service updates with the listener.
bounce listener.


HTH,

AK

[Neil Hunter]

Hi Rory,

There could be couple of reasons for "TNS-12516 (See http://TNS-12516.ora-code.com)" .

+ This is usually seen in case of RAC with multiple instances running and when one of the
instances crashes or becomes unavailable for some time.

+ Due to this, the instance status does not get registered INSTANTLY with the listener
service being used for connectivity.
Because of this delay the TNS error is reported and once the registration happens the error goes off .

+ One can check whether the the status of the service when error comes up using
"lsnrctl service <SERVICENAME>"
- error gets reported if the connections are in a blocked status.

+ Possible ways of fixing is
a) execute command
"alter system register;" - Instance will force service updates with the listener.
bounce listener.


HTH,

AK

Might be going cuckoo, but isn't Rory having an ORA error rather than a TNS problem

[Chris Mackenzie]

Hi Rory,

There could be couple of reasons for "TNS-12516 (See http://TNS-12516.ora-code.com)" .

AK

Might be going cuckoo, but isn't Rory having an ORA error rather than a TNS problem

Ah, the first of the year. Always wonderful to hear.

[Neil Hunter]

Ah, the first of the year. Always wonderful to hear.

Too many years of coding in ProIV, the nervous tic doesn't help matters either

[Rory]

Thanks for the replies..

If I put in UMSGs to narrow down the problem, the error does not happen.

I think it is me who is going cuckoo....

[Chris Mackenzie]

Thanks for the replies..

If I put in UMSGs to narrow down the problem, the error does not happen.

I think it is me who is going cuckoo....

What is your function actually doing? Any connecting to databases,
db links, logical databases involved etc?

[andykay]

Might be going cuckoo...

No question about it Neil...

It's obvious, that with such helpful hints such as this for Rory, that I am overshaddowed by your knowledge on this subject, and I should not have posted my feeble attempts to help someone who was asking for help so you can post your brilliant wisdom here.

If the only reason you posted a comment to this thread was to bump up your response counter, which must be the case since you didn't have anything helpful to post to this thread, then perhaps you should've Googled "Oracle Error Link" ( http://forums.oracle...D=305117#968947 ) first and then perhaps you could've given Rory (remember him...the person seeking the help) a little more insight than you gave me.

In the future, Neil, I'll make sure to cross reference any posts regarding Oracle errors and only post reponses to threads where I find a 100% match on all web sites to that as the subject title in the threads header so as not to waste the time you took to post your helpful comments.


AK

[George Macken]

Hi All

now I dont want any further fighting in the nest !!!!

Thanks all for the replies, some more info that may be relevant below

Site/Application is running pro-iv 5.5 and ORACLE 9i
The Servers and SAN have not had a stop-start re-boot since Mid Feb (anyone any guidelines or advice in this area - are regular restarts advised/required ?)

They have increated the process setting from 150 to 200 and the problem remains.
What/how is the optimum process setting arrived at ?

We have narrowed the problem down to 1 function - its a screen function
1LS
17 file accesses (1 in Change Mode and 16 in Lookup)
4 input fields - these are globals screens behind these fields which may be used to select/add the record that is required for these input fields

the function is relatively stright forward

apart from in the logic out the functions calls a no. of Global Update functions that create data records for the transactions that are about to occur in some following screens. As Rory mentions, in the logic out after putting in some UMSGs or routine that log messages to a text file to try and narrow donw the cause then the problem goes away. As this problem is happening at a client site this flukey-fix will have to make do for the moment until we get some more down-time to take a further look.

Thanks all for the assistance please keep the comments coming

Rgds

George

[Mike Schoen]

Have you tried doing a SQL trace of the function?

If you remove the UMSG for your specific login, and enable tracing, this may give you a sql statement trace up to the point of death. Trace files are a bit of a bear, but you can at least see the sql pro-iv is issuing before it dies.

if you are on a windows application server, I dont know how to turn on tracing for one session, so you may need to do this after-hours.

For a unix application server,
export TR_PATH=/tmp/myfile.trace
export TRACESQL=8
export TRACEGEN=3
This will give you a detailed tracefile of the sql statements. The TRACEGEN should give you a trace of function/global functions as they are loaded, so that you can narrow down the code section.

Mike

[fatboy996]

Might be going cuckoo...

No question about it Neil...

It's obvious, that with such helpful hints such as this for Rory, that I am overshaddowed by your knowledge on this subject, and I should not have posted my feeble attempts to help someone who was asking for help so you can post your brilliant wisdom here.

If the only reason you posted a comment to this thread was to bump up your response counter, which must be the case since you didn't have anything helpful to post to this thread, then perhaps you should've Googled "Oracle Error Link" ( http://forums.oracle...D=305117#968947 ) first and then perhaps you could've given Rory (remember him...the person seeking the help) a little more insight than you gave me.

In the future, Neil, I'll make sure to cross reference any posts regarding Oracle errors and only post reponses to threads where I find a 100% match on all web sites to that as the subject title in the threads header so as not to waste the time you took to post your helpful comments.


AK

Hi, this is a long shot. but could you be running out of cursors with this function as the oracle error message is weird. Try running with SQL_CURSORS=AUTO (needs pro32srv restart if windows).

[Neil Hunter]

Might be going cuckoo...

No question about it Neil...

It's obvious, that with such helpful hints such as this for Rory, that I am overshaddowed by your knowledge on this subject, and I should not have posted my feeble attempts to help someone who was asking for help so you can post your brilliant wisdom here.

If the only reason you posted a comment to this thread was to bump up your response counter, which must be the case since you didn't have anything helpful to post to this thread, then perhaps you should've Googled "Oracle Error Link" ( http://forums.oracle...D=305117#968947 ) first and then perhaps you could've given Rory (remember him...the person seeking the help) a little more insight than you gave me.

In the future, Neil, I'll make sure to cross reference any posts regarding Oracle errors and only post reponses to threads where I find a 100% match on all web sites to that as the subject title in the threads header so as not to waste the time you took to post your helpful comments.


AK

Sorry for asking a valid question, instead I get some snide remarks. From my limited Oracle experience TNS and ORA errors were totally different from each other, guess I was wrong

[Rory]

Hi

We seem to have figured out the cause of this problem
- our client had added a trigger to a table (without telling us!)
- the trigger called a stored procedure on another database
- the database connection is either unavailable / slow due to network latency / initially slow until the db user has been authorised. This would explain the intermittent occurrence of the problem, and also the problem reproducing it when UMSGs are introduced - they are stopping processing long enough to prevent the timeouts on the db link incorrectly making the relevant process think the stored proc is unavailable.

Thanks for your replies, hope we're all still friends.

Rory