we had an issue in our live system. All JDBC sessions are going to inactive state and following error is coming server logs.
2016-01-07 16:34:42 GMT [ISS.0128.0001E] Unable to get JDBC connection. Exception:Error Message: [sag-cjdbc42-0011][Oracle JDBC Driver][Oracle]ORA-00604: error occurred at recursive SQL level 1
ORA-00018: maximum number of sessions exceeded
; Error Code: 604; SQLState: HY008
2016-01-07 16:34:42 GMT [ISS.0137.0018E] Scheduler: Exception while invoking ‘wm.server.dispatcher:deleteExpiredUUID’: Failed to remove the expired entries from the document history table. See server log for details.
For the error “ORA-00604: error occurred at recursive SQL level 1” I suspect the issue could be with the JDBC driver. Can you share more details about the driver version and adapter fix levels.
Regarding your error related to document history table - What is the minimum JDBC connection set in JDBC pool?
Minimum Pool Size 0
Maximum Pool Size 580
Pool Increment Size 20
Block Timeout (msec) 1000
Expire Timeout (msec) 1000
Startup Retry Count 0
Startup Backoff Timeout (sec) 10
and adpater vesrion is 6.5
Updates JDBC_6.5_Fix33
JCA Spec Version 1.0
As DB team reported nearly 550 sessions are went to inactive state and session limit is reached.
Thats the reason why we are getting below error.
2016-01-07 16:34:42 GMT [ISS.0128.0001E] Unable to get JDBC connection. Exception:Error Message: [sag-cjdbc42-0011][Oracle JDBC Driver][Oracle]ORA-00604: error occurred at recursive SQL level 1
ORA-00018: maximum number of sessions exceeded
And as per configuration property (Expire Timeout (msec) 1000) if a session is idle for 1000 msec, Application (WM JDBC adpater) needs to be arrest/terminate the session but it didn’t happen.
i’m thinking that why we got below error.
2016-01-07 16:34:42 GMT [ISS.0137.0018E] Scheduler: Exception while invoking ‘wm.server.dispatcher:deleteExpiredUUID’: Failed to remove the expired entries from the document history table. See server log for details.
We have already restarted IS and DB in order to resolve the issue.
As you suggested,I’ve checked JDBC pools settings. Please find the below.
Minimum Connections 0
Maximum Connections 10
Idle Timeout 60000 milliseconds
And also I would like to highlight one more thing that our DB TableSpace is increasing continuously(Presently at 75%) and WMERROR and WMSESSION tables are occupying more space and having huge records.
Do you think issue is occurring becoz of this ?
We never faced this issue before and recently it started coming, at some point of time all sessions will go to inactive state and we need to restart both IS and DB to resolve the issue and DB team is reporting Application/wM needs to arrest these sessions whenever those will be inactive which is not happening.
do you require the data to stay in the life database for some reason?
If the data is not really required in life database, you can run archiving services to move the data to the archive schema or just delete it.
You can run these archiving Services on regular basis thru scheduler and specifying a reasonable “days to retain” period.
See Monitor Build-In-Services Guide and Monitor Users Guide for further informations.
You will require an additional schema filled with the DCC component Archive.
Monitor package needs to be installed and configured in IS.
Configure an additional JDBC Pool under Settings for the archive schema and mapt it to the Archive function.
BTW:
whats your IS Version (incl. Fix Level)?
Take a look at /WmRoot/updates.dsp in IS Admin.
Thanks for the info about archiving. I will try to delete data from both WMSESSION and WMERROR tales for time being until i’ve configured DCC component to archive.
I think we need to shutdown the IS before doing any changes(truncate/archiving ) to these tables ?.
As I mentioned earlier do you think session inactive issue is happening becoz of this ?
Please find the IS version and latest fix is :
Version 8.2.2.0
Updates IS_8.2_SP2_Core_Fix5
I’ve a doubt about Expire Timeout and Idle Timeout which we will configure in JDBC adapter and JDBC pools respectively.
which configuration will take care of removing inactive sessions from connection pool ?
As per JDBC_Installer_Guide doc Expire Timeout will take care and please tell me where JDBC pool connection part will come ?
please try to check whether the connections belong to JDBC Adapter Connection (Pools) or from wM internal JDBC Pools (under Settings).
These two are using different drivers (mostly) as well as different Pooling mechanisms (I guess).
When editing the JDBC Pools under Settings → JDBC Pools, remember to restart the functions they are associated to.
This will free and recreate the the connection pools per function.
Can you share us a list of installed Fixes from Update Manager for this environment?
Please check for the exact Oracle version of your database and if this needs to be updated by your DBA.
Do you see anything abnormal when checking your system (where IS runs) with “netstat -na|grep ”?
you can run the mentioned Monitor archiving services from IS Admin directly:
Packages → Management → WmMonitor → Browse Services → Service Name
Enter at least the following info:
Period (either a Date or Number of Days)
– todate entries before this date will be considered
– days the last n days will be retained, older entries will be considered (n=0: all entries except today; n=-1: all entries)
Action
– DELETE deletes the data from the live tables
– ARCHIVE moves the data from the live tables to the archive tables
Status
– COMPLETED only completed entries are considered
– COMPLETED-FAILED all entries with final status are considered (Completed, Failed, Stopped)
It is advisable to begin with smaller chunks from oldest to newest.
See Monitor Build-In-Services Guide for informations on the parameters.