SmartConnector for Oracle DB connection persistence / multiple idle connection issue
After we've upgraded the SmartConnector to version 7.1.2, Oracle DBAs reported that there are 5 concurrent connections per connector from which one is active and four seems to be idle. This is making performance impact on DB side.
Based on the information we received from support, from connector version 7.1.2 :
"Arcsight leaves the optimizations and the management of the connection pool/sessions mostly to Apache Tomcat and does not try to control it too much. There is no single parameter that can control closing inactive or sniped session. However they can be controlled programmatically (through api) by controlling multiple parameters/apis with questionable/little benefit."
Did anyone face this issue and is there any possibility for having only one active DB session instead of several ?
Thanks and regards,
First of all, sorry by replying to an old post, but I can see that this situation still has no fix.
Regarded to this issue, we've seen this behaviour last year that actually caused heavy degradation on our DB servers; for instance the "dormant sessions" causes that the undo segments gets bigger and bigger that ends filling up the FS and crashes the server ..
This is unaceptable in PROD environments, and we do have a large DB infraestructure - of course I do not want to have a connector that causes this - that is why I share the workarround that we found:
1. switch to flex time based connector
Despite the fact that supports argued
"Migration has been initiated because of superior and flawless functionality of Tomcat, also is compatible with more products on the market compared to BitMechanics. This however led to loosing of control over DB polls as it is done by Apache Tomcat Connection Pool Manager. As looking into online forums I came across on multiple discussions how certain control could be established programatically"
So, theoritically, all the connectors are affected by this, however we have tested that the FlexDB connector do follows properly the configuration, so you can limit the number of sessions used with:
You can also play with the other DB settings. This is the key one for the issue described.
2. Ask to support for the oficial oracle UAT parser(oracle_unified_audit_trail.sdktbdatabase.properties). I can not share it because it was developed by them, sorry.
3. Cofigure the flexconnector to use the parser and connection to DBs. Mind about the proper rights needed (basically the UAT table and sys.all_audited_system_actions are a must).
4. Optional but totally recomended: restrict the sessions used by the user on the DB side: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6010.htm
we've been playing with this and found that the proper max value is 2. Just 1 as maximum is just not a good idea and will causes always errors when retrieving the audit entries.
Hope that this helps to the ones that faced these errors. Even simpler would be just do not upgrade the connector (it seems that previous to 7.1.2 worked fine, but this was not our case).
We've escalated this to MF but still no solution (since 8 months ago), if you want to refer to this issue, feel free to refer to this Service Request ID - SD02002539