While integration with DB using DB adapter, we often face issues, if DB API at target end is recompiled. An error is received as below:
ORA-04068: existing state of packages has been discarded ORA-04061: existing state of package body “XYZ_PKG” has been invalidated ORA-04065: not executed, altered or dropped package body “XYS_PKG” ORA-06508: PL/SQL: could not find program unit being called: “XYS_PKG” ORA-06512: at “XYZ_WRP”, line ORA-06512: at line 1 ; nested exception is: BINDING.JCA-11811 Stored procedure invocation error
This post tries to put solution to it.
Ideally, in order to avoid these errors, we have below settings configured on our data source :
Initial Connection Count = 0
Statement Cache = 0
And if our data source is not compatible with these settings we should restart server. (https://docs.oracle.com/cd/E15523_01/relnotes.1111/e10132/adapter_tech.htm#CEGDGHEJthough it refers to different error, it is still applicable for our case as well).
Yet, we know, this is very ideal situation and more often we end up using settings Statement cache and in some cases initial connection count as well. Actually, reducing statement cache to 0 may lead to Performance Issues (higher CPU utilization, please refer http://www.theserverside.com/news/1365244/Why-Prepared-Statements-are-important-and-how-to-use-them-properlyfor more understanding on why statement caches are important and https://docs.oracle.com/cd/E23943_01/web.1111/e13737/ds_tuning.htm#JDBCA171). Some places where transaction volume is really very high, we have to set connection count to be some value rather than 0.
Also, scenarios where same data source might be used for invocation of multiple objects, PL/SQL code or db table in more than one composite. In those scenario, it might be possible even if you have initial connections set to 0, there are some connections which are already in use. This is typically case, in you SIT or UAT.
Yes, server restart would be solution in that case, but then it is not always a solution as it is time consuming. Also, restart might impact other integration which do not use data source.
So, the solution, we are trying out is basically– clear statement cache which will make sure all the statement cache is cleared at the same time resetting will ensure all the connections are closed and then recreated which at that point will be equivalent to Initial Connection Count to 0 and statement cache also to be 0.
Step 1: Clear Statement Cache (login to admin console, Service –> Data Sources –> Select Data Source –> Control Tab –> Select Server –> Click on Clear Statement Cache.
Step 2: Reset Connection (on same page as in Step 1 , click on Reset button).
Note: In case you are using any events based on DB, then it would be ideal to disable event subscribers before running script and once it is done, you can enable it.
Automating Clear Cache and connection reset: Sometimes navigating to screens and then manually using the solution might be cumbersome, especially if you would like to clear cache for more than 1 data source. Yes, this can be automated using WSLT. Basic steps are as below:
Commands would be as below:
JDBCRunTimeServer = cmo.getJDBCDataSourceRuntimeMBeans()
for datasource in JDBCRunTimeServer:
Same thing could be achieved for multi-data source in that case you will need to use
JDBCMultiRunTimeServer = cmo.getJDBCMultiDataSourceRuntimeMBeans()
Solution is tested on 188.8.131.52.0 and 184.108.40.206
Hope this post helps.