| Author |
Message
|
| ajomillar |
 Posted: Thu Jun 23, 2005 2:07 pm Post subject: MQSI v2.1 and database connections |
 |
|
Centurion
Joined: 22 Aug 2003
Posts: 121
Location: Milwaukee, WI
|
| We have experienced an abnormal logoff (logoff on cleanup) captured in the Oracle audit logs. There is a firewall rule allowing traffic from the broker to the database server but it is only open for 1 hour. The connection is mostly idle since the messages are processed infrequently. How does MQSI v2.1 (running on AIX 5) handle database connections? Does it attempt to reconnect after it is dropped? We capture the oracle exception in an error handling subflow. The Oracle exception: ORA-03113 end of file on communication channel. |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Thu Jun 23, 2005 2:31 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
The handling of database connections is pretty well documented, actually.
It keeps the database connection open as long as it can.
If it notices that the database connection it needs is "stale" or "bad", then it will drop that connection.
If it finds it needs a connection, and there aren't any available, it will open a new connection.
So, basically, if you set a Backout Retry Count of at least 1 on your queues, and do not otherwise interrupt normal error/backout handling, then it will see and log a database error, back the message out, retry it, establish a new (hopefully good) database connection, and succeed in processing.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Ian |
| Posted: Fri Jun 24, 2005 5:30 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Jeff has given a good summary of this.
The is point is also discussed in number of other posts in this forum, including here.
An ORA-03113 (Communication Error with Oracle) error is one of the conditions for which the broker detects and releases the existing database connection.
_________________
Regards, Ian |
|
| Back to top |
|
|
| ajomillar |
| Posted: Fri Jun 24, 2005 6:02 am Post subject: |
|
|
Centurion
Joined: 22 Aug 2003
Posts: 121
Location: Milwaukee, WI
|
| Thanks guys! We have an error handling flow that captures system exceptions so we'll have to modify the coding to facilitate recycling through the message flow. The flow's input queue has a Backout Threshold of 0 (zero) but the error handling flow deserves the attention. |
|
| Back to top |
|
|
| JT |
| Posted: Fri Jun 24, 2005 6:25 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
| Keep in mind when you recycle the message that it can encounter more than one 'stale' database connection/ORA-03113 condition before a new database connection is established. |
|
| Back to top |
|
|
| ajomillar |
| Posted: Fri Jun 24, 2005 6:31 am Post subject: |
|
|
Centurion
Joined: 22 Aug 2003
Posts: 121
Location: Milwaukee, WI
|
| on second thought...the backout threshold should be set to greater than zero to allow additional attempts through the message flow. I'll not touch the error handling flow. |
|
| Back to top |
|
|
| JohnMetcalfe |
| Posted: Fri Jun 24, 2005 8:01 am Post subject: |
|
|
Apprentice
Joined: 02 Apr 2004
Posts: 40
Location: Edinburgh, Scotland
|
My advice is to be careful in this area to avoid spurious production 'incidents' .
We have a development standard here to set the backout threshold to 8 on input Qs for flows that do database access.
This may seem high, but it is not unsual for us to have complex flows that access 3 databases.
In addition to the ORA3113 error, we have also found that we can get a exception thrown if a an oracle package is recompiled. Like the ORA3113, it occurs the first time though, then WMQI re-initialises the connection and it works second time.
Our backout threshold of 8 was based on the calculation of a worse case scenario where we had a flow that accessed 3 databases, and we hit ORA3113 and then package recompilation error for each one - this is 6 times round. We then added 2 more for luck...  |
|
| Back to top |
|
|
| ajomillar |
| Posted: Wed Jun 29, 2005 6:00 am Post subject: |
|
|
Centurion
Joined: 22 Aug 2003
Posts: 121
Location: Milwaukee, WI
|
| In your experience, is the "stale" database connection caused by the Broker or an external factor such as a firewall (eg. an idle connection is dropped by a firewall)? |
|
| Back to top |
|
|
| JohnMetcalfe |
| Posted: Wed Jun 29, 2005 7:24 am Post subject: |
|
|
Apprentice
Joined: 02 Apr 2004
Posts: 40
Location: Edinburgh, Scotland
|
| external factors (to the broker) seemed to be making the connection 'invalid' e.g Oracle database being brought down or or an oracle package being recompiled. |
|
| Back to top |
|
|
|
|
