Tutorial: OTM - Setup DB Connection Pool Refresh

[chrisplough]

OTM v5.5 - Setup DB Connection Pool Refresh

This post shows how to enable the OTM DB Connection Pool refresh, so that connections are tested periodically. This also keeps them alive and prevents a firewall from killing them.

Note: The latest version of this tutorial can be found on our Tutorials site at the link below:
ScreenSteps Live

Login to OTM as DBA.ADMIN




Select the "Data Source Manager" from the menu




Search to pull up all DB connection pools




Select the "PRIMARY_JTS" connection pool




Select the "Pool" tab




Update the screen as noted and click "Finished"




Congrats - you're done!

[smansour]

Re: Tutorial: OTM - Setup DB Connection Pool Refresh

Hi Chris,

Thanks so much for the link.

Please tell me how can i access the live session screenshots, and the password for DBA.ADMIN user

Thanks

[chrisplough]

Re: Tutorial: OTM - Setup DB Connection Pool Refresh

You'll have to access your own instance to test or make these changes. The default password for DBA.ADMIN is CHANGEME.

--Chris

[smansour]

Re: Tutorial: OTM - Setup DB Connection Pool Refresh

Hi Chris,
The password isn't working, and i dont have any instance.
I only need to access OTM online to do some practice and tutorials

Thanks

[smansour]

Re: Tutorial: OTM - Setup DB Connection Pool Refresh

Also when i access this link https://otmfaq.screenstepslive.com/login i cant access using my smansour account, and i dont know why ?

[smansour]

Re: Tutorial: OTM - Setup DB Connection Pool Refresh

the password CHANGEME isn't working on Error

when i use the DBA.ADMIN user

Thanks

[chrisplough]

Re: Tutorial: OTM - Setup DB Connection Pool Refresh

Hello,

That is our internal TEST OTM v5.5 instance and we can't legally provide access to it outside of the company. I forgot that the URL was in the screenshots - so I may have to go back and edit them to blur that out.

As for Screen Steps -- this service has been discontinued because the OTM Wiki (http://www.otmwiki.com/) was created -- which is a better site for documenting and posting tutorials.

Thanks,
--Chris

[smansour]

Re: Tutorial: OTM - Setup DB Connection Pool Refresh

Hi Chris,

Thanks so much for yr reply, and sorry for my misunderstanding
Appreciated ur support

Is there any online free demo instance for OTM ?

Thanks

[chrisplough]

Re: Tutorial: OTM - Setup DB Connection Pool Refresh

Not currently, but we're in negotiations to be able to provide. I can't give many details yet, but as soon as I'm able, I'll announce it here.

--Chris

[jlgottlieb]

Re: Tutorial: OTM - Setup DB Connection Pool Refresh

Chris, Thanks so much for the info.

Since we went live, we have had no timeout on our DB connections. Our Connection Pools in OTM were configured to:

Initialize at: Static Wait Timeout: 2 Min
Refresh When: Never Refresh Every: 5 Min (assume this is ignored)
Shrink at: LRU Refresh Every: 5 Min (assume this is ignored)


After our recent internal audit, we are required to put account resource limitations on all databases - which means connections will now timeout.

As a test, I just let our QA environment run without changing the connection pool configuration and as expected I am getting Connection Pool exhausted errors. I assume the pool is exhausted because connections are dropped and never tested/refreshed.

In your tutorial, you show Refresh at: Both Refresh every 5 min. Why refresh at getting a connection from the pool and putting it back? Doesn't this add overhead? Is there a reason to Shrink at all?

Your tutorial shows changing the PRIMARY_JTS, but I assume I should modifiy the settings for all the connection pools. However, based on the CPDiagServlet it looks like we don't use DBAONLY, ODS, and PRIMARY_OCI as the current connections are always 0. Should I just leave them alone as it seems that setting a refresh is a waste of resources?

I understand that the number of connections will vary for each implementation. I guess what I am looking for is general best practices.

I'd appreciate any suggestions anyone might have.

Janice

[chrisplough]

Re: Tutorial: OTM - Setup DB Connection Pool Refresh

Janice,

You bring up some good points. The reason for the double refresh is just to ensure that it happens often enough, so that they're unlikely to timeout. I don't know if it was a bug or expected behaviour, but if we just refreshed on one condition, we'd still end up with connection pool issues.

In addition to PRIMARY_JTS (the main connection pool for OTM), you should also address DEDICATED_DBA and DEDICATED_SECURITY. These are the 3 main pools used by OTM. Incidently, many installations need to increase the final two pools. Generally we use a start of 1 and a max of 10 for most installations.

As for shrinking, it's usually helpful when a customer has a shared DB server (multiple DBs from multiple apps), so that it only expands at points of high demand.

--Chris

--Chris

[Darren M]

Re: Tutorial: OTM - Setup DB Connection Pool Refresh

Chris,
We are running on 6.x of OTM and having some odd database issues that imply connection pool problems. Based upon your experience, would you consider these settings suggested for 6.x versions as well as the 5.5 version you have documented? If not, is there an updated 6.x relevant?
Thanks,
Darren

[chrisplough]

Re: Tutorial: OTM - Setup DB Connection Pool Refresh

Hi Darren - yes, I would still consider these good settings for 6.x also.

--Chris