Using Shared Pooling Data Sources

8 Using Shared Pooling Data Sources

Shared pooling provides the ability for Multi Data Source definitions to share an underlying connection pool. This feature improves connection utilization and density when data sources are not heavily used by applications, or are not participating in long running transaction processing. When configured to connect to an Oracle Container Database (CDB) environment, the shared pool can easily manage connections to multiple Pluggable Database (PDB) services.

How shared Pooling Works
When an application component requests a connection from a data source, the shared pool attempts to locate a connection that matches the database name and service for the data source.
Requirements and Considerations when using Shared Pooling Data Sources
Learn and understand the requirements for using shared pooling.
Configuring Shared Pooling
You can configure shared pooling by setting WebLogic Server-specific driver properties and configuring Database properties.

How shared Pooling Works

When an application component requests a connection from a data source, the shared pool attempts to locate a connection that matches the database name and service for the data source.If an existing connection is found, it is returned to the application. Otherwise, an available connection associated with a different database is reserved and re-purposed for the requesting data source.

Note:

If there are no available connections in the shared pool, and if capacity is available, a new connection will be created for the common service and repurposed for the shared data source requesting the connection.

Parent topic: Using Shared Pooling Data Sources

Requirements and Considerations when using Shared Pooling Data Sources

Learn and understand the requirements for using shared pooling.

Shared pool feature requires the Oracle 12.2 database and the 12.2 JDBC/UCP/ONS client libraries.
All sharing data source definitions that specify a particular shared pool JNDI name must have compatible configuration attributes with the shared pooling data source definitions.
Separate ONS subscriptions need to be managed for each sharing data source that defines a unique PDB service.
The shared pool feature provides support for Generic and Active GridLink (AGL) data source types.

Note:
A Generic sharing data source cannot be used as a member data source in a Multi Data Source configuration, as this would result in an exception being raised at runtime causing the Multi Data Source deployment to fail.
The shared connection pool supports connection matching based on PDB name, service name and RAC instance name (AGL).
Shared pooling does not provide support for Pinned-to-thread Optimization, Application Invoked PDB Switch, and Identity-based pooling and BI Impersonation identity options.

Parent topic: Using Shared Pooling Data Sources

Configuring Shared Pooling

You can configure shared pooling by setting WebLogic Server-specific driver properties and configuring Database properties.

Parent topic: Using Shared Pooling Data Sources

Configuring WebLogic Server-Specific Driver Properties for Shared Pooling

To configure shared pooling in your environment you need to set the following properties in the data source:

Setting the Shared Pool Definition

Set the shared pool attribute weblogic.jdbc.sharedPool=true.

Note:
This attribute indicates whether the data source definition represents a shared connection pool. When this attribute is set to false (default) any data source referencing the data source as a shared pool will result in a deployment exception.

Setting the Sharing Data Source Definition

Set the shared pool JNDI Name.
```
weblogic.jdbc.sharedPoolJNDIName=<jndiname>
```
Note:
If the data source bound under JNDI Name is not configured as a shared pool then it will result in deployment exception.
Set the name of the PDB that is associated with the sharing data source.
```
weblogic.jdbc.pdbName=<pdb>
```
Note:
If a sharing data source does not specify the PDB name property then getConnection() invocations will return a JDBC connection associated with the root container, or the default service of the shared pool configuration.
You can set the PDB Service Name to specify the name of the service set on the JDBC connection when the connection is repurposed for a specific PDB.

weblogic.jdbc.pdbServiceName=<service>

Note:
The PDB service name attribute is optional. When not specified by the sharing data source configuration a connection will be associated with the default service of the shared pool data source.
You can set Proxy User property to specify the name of the proxy user and password to set on the JDBC connection when the connection is repurposed for the sharing data source.

weblogic.jdbc.pdbProxy.<proxy-user>=<proxy-password>

Note:
Proxy user is only set when a connection is switched to a PDB/service. When both Proxy User and Role Name attributes are specified, the Proxy User takes precedence and the role is not set on the database session.
You can also set the Role property to specify a password-protected role to be set on the JDBC connection when it is repurposed for the sharing data source. There can be any number of password-protected roles configured for a sharing data source.

weblogic.jdbc.pdbRole.<role-name>=<role-password>

Note:
When the Proxy User attribute is also set, it takes precedence and the role is not set.

Note:

You can set proxy authentication or password protected roles to secure the sharing data source. For any given shared pool, you can use only one of these options.

Parent topic: Configuring Shared Pooling

Configuring Database for Shared Pooling

To configure your Oracle database to support shared pooling, you need to specify a common database user in the shared pooling data source configuration attributes. This common user must exist in all PDBs that are connected to the sharing data sources.

This common user must have the following privileges:

grant execute on dbms_service_prvt to c##user;
grant set container to c##user;

The shared pooling data source configuration should specify a URL that includes a common service for the CDB.

The password-protected roles need to be defined for the configured common user in each PDB connected to by a sharing data source

Example 8-1 WLST Script for Configuring Shared Pooling

import os
def createJDBCSystemResource(owner, resourceName, driver, url, user, password):
  systemResource=owner.createJDBCSystemResource(resourceName)
  systemResource.setName(resourceName)
  jdbcResource=systemResource.getJDBCResource()
  jdbcResource.setName(resourceName)
  driverParams=jdbcResource.getJDBCDriverParams()
  if driver:
    driverParams.setDriverName(driver)
  if url:
    driverParams.setUrl(dburl)
  properties = driverParams.getProperties()
  if user:
    properties.createProperty('user', user)
  if password:
    driverParams.setPassword(dbpassword)
  return systemResource
def createSharedPoolDS(owner, resourceName, driver, url, user, password):
   systemResource = createJDBCSystemResource(owner, resourceName, driver, url, user, password)
   systemResource.getJDBCResource().getJDBCDriverParams().getProperties().createProperty('weblogic.jdbc.sharedPool', 'true')
   return systemResource
def createSharingDS(owner, resourceName, sharedPoolJNDIName, pdbName, pdbServiceName, roleName, rolePassword):
  systemResource = createJDBCSystemResource(owner, resourceName, driver=None, url=None, user=None, password=None)
  properties=systemResource.getJDBCResource().getJDBCDriverParams().getProperties()
  properties.createProperty('weblogic.jdbc.sharedPoolJNDIName', sharedPoolJNDIName)
  if pdbName:
    properties.createProperty("weblogic.jdbc.pdbName", pdbName)
  if pdbServiceName:
    properties.createProperty("weblogic.jdbc.pdbServiceName", pdbServiceName)
  if roleName:
    roleprop=properties.createProperty("weblogic.jdbc.pdbRole."+roleName)
  if rolePassword:
     roleprop.setEncryptedValue(rolePassword)
  return systemResource
servername='myserver'
sharedpoolname='sharedpool'
sharingds1name='sharingds1'
sharingds2name='sharingds2'
driver='oracle.jdbc.OracleDriver'
dburl='jdbc:oracle:thin:@host:1521/orcl'
dbuser='c##1'
dbpassword='xyzzy'
pdb1='pdb1'
pdb1service='coke'
pdb1role='cokerole'
pdb1rolepwd='cokepwd'
pdb2='pdb2'
pdb2service='pepsi'
pdb2role='pepsirole'
pdb2rolepwd='pepsipwd'
connect('weblogic', 'weblogic', 't3://localhost:7001')
edit()
# create shared pool datasource
startEdit()
spds=createSharedPoolDS(cmo, sharedpoolname, driver, dburl, dbuser, dbpassword)
spds.addTarget(getMBean('/Servers/'+servername))
activate()
startEdit()
# create sharing datasource 1
sharingds1=createSharingDS(owner=cmo, resourceName=sharingds1name, sharedPoolJNDIName=sharedpoolname, pdbName=pdb1, pdbServiceName=pdb1service, roleName=pdb1role, rolePassword=pdb1rolepwd)
sharingds1.addTarget(getMBean('/Servers/'+servername))
# create sharing datasource 2
sharingds2=createSharingDS(owner=cmo, resourceName=sharingds2name, sharedPoolJNDIName=sharedpoolname, pdbName=pdb2, pdbServiceName=pdb2service, roleName=pdb2role, rolePassword=pdb2rolepwd) 
sharingds2.addTarget(getMBean('/Servers/'+servername))
activate()
exit()

Parent topic: Configuring Shared Pooling