Home » Developer & Programmer » Precompilers, OCI & OCCI » Pro C program fails with (Pro*C/C++: Release 11.2.0.4.0)
Pro C program fails with [message #683471] Wed, 20 January 2021 03:29 Go to next message
brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom
Member
We have a pro*c process continually running that every 5 minutes reads from a materialised view 132,000 rows, does some processing and writes to another table. Approximately once a day (at different times) this is crashing with "ora-3114 not connected to oracle".

The Oracle documentation is very vague (network issue, Oracle shared pool size etc) but our other similar processes do not suffer the same issue apart from one which happens very rarely about once every few months.

Can anyone suggest a way forward please.

Report message to a moderator

Re: Pro C program fails with [message #683472 is a reply to message #683471] Wed, 20 January 2021 03:31 Go to previous messageGo to next message
brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom
Member
brown_zzz wrote on Wed, 20 January 2021 03:29
We have a pro*c process continually running that every 5 minutes reads from a materialised view 132,000 rows, does some processing and writes to another table. Approximately once a day (at different times) this is crashing with "ora-3114 not connected to oracle".

The Oracle documentation is very vague (network issue, Oracle shared pool size etc) but our other similar processes do not suffer the same issue apart from one which happens very rarely about once every few months.

Can anyone suggest a way forward please.
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 and Pro*C/C++: Release 11.2.0.4.0

Report message to a moderator

Re: Pro C program fails with [message #683473 is a reply to message #683472] Wed, 20 January 2021 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

This means your program disconnected or has been disconnected from Oracle and so is no more connected.
Do you use connection pooling or multi-threading?
Do you disconnect between each access to the database?
Check alert.log and trace files.

Report message to a moderator

Re: Pro C program fails with [message #683474 is a reply to message #683473] Wed, 20 January 2021 06:14 Go to previous messageGo to next message
brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom
Member
Thanks for your response Michel.

It's connected to a local database on the same server using EXEC SQL CONNECT <connection string> and is not multi-threaded. We do not disconnect between database accesses and all our processes do the same with a permanent connection for a period of months without the same issue. The dbas have checked the alert log and trace files without finding anything and Oracle Support are not being helpful

Report message to a moderator

Re: Pro C program fails with [message #683475 is a reply to message #683474] Wed, 20 January 2021 07:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

So you connect with just username and password without any connection string?
Is there any idle time or connect time set to the profile of the user?
(Replace MICHEL by the list of your users.)
select u.username, p1.resource_name, decode(p1.limit, 'DEFAULT',p2.limit, p1.limit) limit
from dba_users u, dba_profiles p1, dba_profiles p2
where u.username in ('MICHEL')
  and p1.profile = u.profile
  and p1.resource_type = 'KERNEL'
  and p2.profile = 'DEFAULT'
  and p2.resource_name = p1.resource_name
order by 1, 2
/

[Updated on: Wed, 20 January 2021 07:48]

Report message to a moderator

Re: Pro C program fails with [message #683485 is a reply to message #683475] Sat, 23 January 2021 00:37 Go to previous messageGo to next message
NevemTeve
Messages: 25
Registered: November 2017
Junior Member
Network connections can be lost for different reasons, this fact cannot be solved programmatically.
Nonetheless, you can start every periodic run with an `EXEC SQL COMMIT WORK` just to check the connection. It it fails (sqlca.sqlcode!=0), perform a disconnect+connect sequence. [And if that fails, too, then go back to sleep.]

[Updated on: Sat, 23 January 2021 00:39]

Report message to a moderator

Re: Pro C program fails with [message #683486 is a reply to message #683485] Sat, 23 January 2021 02:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
Network connections can be lost for different reasons, this fact cannot be solved programmatically.

OP said:
Quote:
's connected to a local database on the same server using EXEC SQL CONNECT <connection string> and is not multi-threaded. We do not disconnect between database accesses and all our processes do

So network?


Quote:
Nonetheless, you can start every periodic run with an `EXEC SQL COMMIT WORK` just to check the connection. It it fails (sqlca.sqlcode!=0), perform a disconnect+connect sequence

And what is the difference with do the actual work ("every 5 minutes reads from a materialised view") and if it fails (with code 3114) then try to reconnect? No need to add something more.
In addition, just checking "sqlca.sqlcode!=0" is not a good idea, imagine the mview is droppped then your program will just indefinitely loop disconnecting/connecting dropping down the server performances and impacting all other users (and maybe instances if several are in the same server).

I think OP's question is "why?" not "how to workaround it?".
Waiting for his/her feedback...

Report message to a moderator

Re: Pro C program fails with [message #683541 is a reply to message #683486] Thu, 28 January 2021 01:47 Go to previous message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

@brown_zzz,

Any feedback?

Report message to a moderator

Previous Topic: OCI thread safe mode ?
Next Topic: Pro C compile error
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Dec 21 19:26:36 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.