11 Configuring JDBC Active GridLink Data Sources

This chapter describes how to configure the Oracle Communications MetaSolv Solution (MSS) JDBC Active GridLink (AGL) data sources to work in an Oracle Real Application Cluster (RAC) environment for MSS.

About Oracle RAC

Oracle RAC consists of two or more Oracle database instances that run on two or more clustered machines and access a shared storage device using cluster technology. The host machines for the database instances must be linked by a high-speed interconnection to form the cluster and enable communications among the nodes in the cluster.

Note:

For more information on configuring Oracle RAC, your clustering software, your operating system, and storage solution, see the documentation from the respective vendors.

For information using Oracle RAC with Oracle WebLogic Servers, refer to the Oracle Fusion Middleware Administering JDBC Data Sources for Oracle Weblogic Server documentation.

Creating the JDBC Active GridLink Data Sources

A single Active GridLink (AGL) data source provides connectivity between WebLogic Server and an Oracle Database service, which may include one or more Oracle RAC clusters. An Oracle Database service represents a workload with common attributes that enables administrators to manage the workload as a single entity. You scale the number of AGL data sources as the number of services increases in the database, independent of the number of nodes in the Oracle RAC clusters.

Note:

MSS supports only the failover algorithm (with remote listener enabled).

You can create JDBC Active GridLink data sources by doing one of the following:

JDBC Data Source Properties

JDBC data source properties include options on the data source identify and the handling of data for the database connection. Before creating data sources, you need to be familiar with this topic. For detailed information on JDBC data sources and their properties, see Fusion Middleware Administering JDBC Data Sources for Oracle WebLogic Server on the Oracle Help Center website at:

https://docs.oracle.com/en/middleware/fusion-middleware/index.html

Manually Creating the JDBC Active GridLink Data Sources

You must create the JDBC Active GridLink data sources manually. By default, the MSS installer creates the multi data sources when the Oracle RAC database is selected. A multi data source provides an ordered list of data sources to use to satisfy connection requests.

To create the JDBC Active GridLink data sources, perform the following tasks in the WebLogic Remote Console:

  • Deleting the existing multi data sources. See "Deleting Multi Data Sources" for more information.

  • Creating the following JDBC Active GridLink data sources:

    • mslvDataSource

    • mslvDbTraceDataSource

    • mslvNoneTxDataSource

    • mslvProcDataSource

    • mslvPSDataSource

    • mslvWSDataSource

    See "Creating Active GridLink Data Sources" for more information.

  • Creating the following persistent stores:

    • mssJMSStore (and point it to mslvPSDatasource)

    • pluginstore (and point it to mslvPSDatasource)

    See "Creating Persistent Stores" for more information.

  • Creating the following JMS servers:

    • mmsJMSServer (and point it to mssJMSStore)

    • pluginJMSServer (and point it to pluginstore)

    See "Creating JMS Servers" for more information.

  • Creating the following JMS modules:

    • mssJMSModule

    • pluginModule

      After you create mssJMSModule and pluginModule, you must create JMS module resources (such as queues and connection factories) and create the subdeployments for those resources. A subdeployment is a process for grouping JMS module resources and targeting them to a server instance or cluster.

    See "Creating JMS Modules" for more information.

Deleting Multi Data Sources

To remove a multi data source, perform the following:

  1. Log in to the WebLogic Server Remote Console.

  2. In the Edit Tree, expand Services, and then click Data Sources.

    The Summary of JDBC Data Sources page appears.

  3. Select the check box beside each data source that you want to delete and click Delete.

Creating Active GridLink Data Sources

To create an Active GridLink data source:

  1. Log in to the WebLogic Server Remote Console.

  2. In the Edit Tree, expand Services, and then click Data Sources.

    The Summary of JDBC Data Sources page appears.

  3. Click New.

  4. In the Name field, enter mslvDataSource.

  5. In the JNDI Names field, enter the JNDI path to where this data source is bound. By default, the JNDI name is the name of the data source. For example, MSLVPool.

  6. In Targets, select the servers or clusters on which you want to deploy the data source.

  7. Select GridLink Data Source from the Data Source Type drop-down list.

  8. From the Database Driver list, select any one of the following database drivers, depending on the Active GridLink data source that you are creating:

    • When creating mslvDataSource and mslvWSDataSource, select Oracle's Driver (Thin XA) for GridLink Connections Versions:Any.

    • When creating mslvDbTraceDataSource, mslvNoneTxDataSource, mslvProcDataSource, and mslvPSDataSource, select Oracle's Driver (Thin) for GridLink Connections; Versions:Any.

  9. Optional: If you selected a non-XA driver, then select a Global Transactions Protocol from the following values:

    • Logging Last Resource

    • Emulate Two-Phase Commit

    • One-Phase Commit

    For XA drivers, the system automatically selects the Two-Phase Commit protocol for global transaction processing.

  10. Enter the connection details for the database that you want to connect to:
    1. Listeners: Enter the host and port; separate the host and the port of each listener with a colon. Enter each listener on a new line.
    2. Service Name: Specify the service name of the database to which you want to connect.
    3. Database User Name: Enter the database user account name that you want to use for each connection in the data source.
    4. Password: Enter the password for the database user account.
    5. Protocol: If required, change the value from TCP to SDP. To use Socket Direct Protocol (SDP), your database network must be configured to use Infiniband.

  11. Select the Fan Enabled check box to subscribe to Oracle Fan Events.

  12. In the ONS host and port field, enter the host and port for each ONS node separated by a comma.

  13. If ONS is configured to use the SSL protocol, do the following:

    1. In the ONS Wallet File Directory field, enter the directory of the Oracle wallet file in which the SSL certificates are stored.

    2. In the ONS Wallet Password field, enter and confirm the ONS wallet password.

    3. In the Confirm ONS Wallet Password field, enter the ONS wallet password again to confirm it.

  14. Click Create.

  15. Repeat this procedure to create the remaining Active GridLink data sources: mslvDbTraceDataSource, mslvNoneTxDataSource, mslvProcDataSource, mslvPSDataSource, and mslvWSDataSource.

  16. Proceed with "Tuning the Active GridLink Data Source".

Tuning the Active GridLink Data Source

After you create the Active GridLink data sources, you must tune each data source.

To tune each data source:

  1. Log in to the WebLogic Remote Console.

  2. In the Edit Tree, expand Services, and then click Data Sources.

    The Summary of JDBC Data Sources page is displayed.

  3. Select the target data source (for example, mslvDataSource1).

    The Settings for <DataSource> page is displayed.

  4. Click the Connection Pool tab.

    The Connection Properties are displayed.

  5. Click the Advanced tab to display additional configuration items.

  6. In the Test Table Name field, clear the SQL SELECT 1 FROM DUAL entry and enter SQL ISVALID.

  7. In the Inactive Connection Timeout field, enter 300.

  8. Click Save.

  9. Click Shopping Cart, then select Commit Changes.

  10. Repeat steps 3 through 9 to tune each data source.

Creating Persistent Stores

To create a persistent store:

  1. Log in to the WebLogic Server Remote Console.

  2. In the Edit Tree, expand Services, and then click JDBC Stores.

    The Summary of JDBC Stores page appears.

  3. Click New.

    The Create a New JDBC Store page appears.

  4. Do the following:

    1. In the Name field, enter mssJMSStore.

    2. From the Target list, select a server instance on which you want to deploy the JDBC store.

    3. From the Data Source list, select mslvPSDataSource.

    4. In the Prefix Name field, specify a prefix that is added to the table name in this JDBC store.

    5. Click Create.

    6. Repeat this procedure to create the pluginstore persistent store and point it to mslvPSDatasource.

Creating JMS Servers

To create a JMS server

  1. Log in to the WebLogic Server Remote Console.

  2. In the Domain Structure tree, expand Services, then expand Messaging, and then click JMS Servers.

    The Summary of JMS Servers page appears.

  3. Click New.

    The Create a New JMS Server Properties page appears.

  4. Do the following:

    1. In the Name field, enter mssJMSServer.

    2. From the Persistent Store list, select mssJMSStore.

    3. Click Finish.

    4. Repeat this procedure to create the pluginJMSServer JMS server and point it to pluginstore.

Creating JMS Modules

To create a JMS module:

  1. Log in to the WebLogic Server Remote Console.

  2. In the Edit Tree, go to Services, then JMS Modules.

    The Summary of JMS Modules page appears.

  3. Click New.

    The Create JMS System Module page appears.

  4. In the Name field, enter mssJMSModule.

  5. Click Create.

    The JMS Modules details page appears.

  6. On the Target tab, from the Target drop-down list, select a server instance or cluster on which to deploy the JMS system module.

  7. Click Save.

    The mssJMSModule JMS module is created. You can create the resources for the newly created JMS module.

  8. To add resources, select the JMS system module that you want to configure resources for.

  9. In the Navigation Tree, as child nodes of JMS system module that you selected, click the resource(such as Connection Factory, Queue, Topic, and so on) that you want to configure.

  10. Enter the required information for the selected resource.

    Certain resources may encourage you to configure an appropriate subdeployment. A subdeployment is the mechanism by which targetable JMS module resources (such as queues, topics, and connection factories) are grouped and targeted to a server resource (such as JMS servers, server instances, or cluster).

    Most JMS resources have additional parameters that can be modified after they are created. For example, you can modify the default message threshold values or enable message logging for queues, topics, and templates.

    Note:

    When creating a JMS module resource, do not specify a sub deployment; instead, after creating the JMS resource, edit the resource and then specify a sub deployment for that resource. See "Configuring Sub Deployments for JMS Module Resources" for more information.

  11. Click Create.

    Click Shopping Cart, then select Commit Changes, which activates these changes.

  12. Repeat this procedure to create the pluginModule JMS module.

Configuring Sub Deployments for JMS Module Resources

After you create the resources for the JMS module, edit the resource, and then specify a sub deployment for that resource.

To configure a sub deployment for a JMS module resource:

  1. Log in to the WebLogic Remote Console.

  2. In the Edit Tree, expand Services, and then click JMS Modules.

    The Summary of JMS Modules page appears.

  3. Click mssJMS module.

    The Settings for mssJMSModule page appears.

  4. In the navigation tree, select Sub Deployments.

  5. Click New.

    The Create a new Sub Deployment page opens.

  6. Enter Name and click Create.

  7. Click Save.

  8. In Shopping Cart, select Commit Changes.

  9. In the Navigation Tree, click the resource (Queues, Connection Factories and so on) for which you want to configure sub deployment.

  10. In the General tab, enter the corresponding sub deployment in Sub Deployment Name.

  11. Click Save.

  12. Repeat this procedure to configure sub deployments for the resources of pluginModule.

Creating JDBC Active Data Sources by Updating the Configuration Files

Creating the JDBC Active GridLink data sources by updating configuration files involves doing the following tasks in the WebLogic Remote Console:

Note:

Before converting Multi Data Sources to Active GridLink Data Sources, you must do the following:

  1. Take a backup of all Multi Data Sources

  2. Take a backup of the config.xml file that is located in the mslvdomain\config directory, where mslvdomain is the name of the domain directory you specified during domain configuration.

Encrypting Clear Text Passwords in WebLogic Server Domain Configuration Files

Each Active GridLink data source is represented by a configuration XML file for the RAC domain in the mslvdomain\config\jdbc directory. For example, mslvWSDataSource-8065-jdbc.xml, mslvProcDataSource-9573-jdbc.xml, and so on.

where:

mslvdomain is the name of the domain directory you specified during domain configuration.

You must encrypt the clear text passwords in each domain configuration XML file for all the data sources to protect them from unauthorized access. You encrypt passwords using the java weblogic.security. Encrypt utility, which you must run from within the mslvdomain folder.

Ensure that you encrypt passwords for the following database users: APP_MSLV, APP_API, and APP_INT.

To encrypt clear text passwords in WebLogic Server domain configuration files:

  1. Change directory to your domain's bin folder.

    For example:

    cd MW_HOME\user_projects\domains\mslvdomain\bin

  2. Run the setDomainEnv script. For example, setDomainEnv.cmd (Windows) or setDomainEnv.sh (UNIX/Linux).

  3. Navigate to the mslvdomain directory and run the java weblogic.security.Encrypt utility. This utility prompts you for a password, and then displays the encrypted password.

    The following is a sample output from running this utility:

    C:\MW_HOME\user_projects\domains\mslvdomain>java weblogic.security.Encrypt
Password:
{3DES}9HWsf87pJTw=

  4. Navigate to the mslvdomain\config\jdbc directory.

  5. Copy the encrypted passwords for all the data sources and paste these passwords in the <password> element within the XML file for each data source (mslvWSDataSource, mslvProcDataSource, mslvPSDataSource, mslvNoneTxDataSource, mslvDbTraceDataSource, and mslvDataSource).

Note:

You can generate a new password or you can copy the password from the Multi Data Source backup.

Deleting Multi Data Source Instances from the config.xml File

To delete the multi data sources instances from the config.xml file:

  1. Edit the mslvdomain\config\config.xml file.

  2. Remove all the instances of multi data sources.

    For example, remove the entries for the following data sources: mslvDataSource1, mslvDbTraceDataSource1,mslvNoneTxDataSource1, mslvProcDataSource1, mslvWSDataSource1, mslvPSDataSource1, mslvDataSource2, mslvDbTraceDataSource2, mslvNoneTxDataSource2, mslvProcDataSource2, mslvWSDataSource2, mslvPSDataSource2, and so on:

    <jdbc-system-resource>
   <name>mslvDataSource1</name>
   <target>RacConfigServer</target>
   <descriptor-file-name>jdbc/mslvDataSource1-0700-jdbc.xml
   </descriptor-file-name>
</jdbc-system-resource>
<jdbc-system-resource>
   <name>mslvDbTraceDataSource1</name>
   <target>RacConfigServer</target>
   <de-scriptor-file-name>jdbc/mslvDbTraceDataSource1-8405-jdbc.xml
   </descriptor-file-name>
</jdbc-system-resource>
.
.
.
<jdbc-system-resource>
   <name>mslvWSDataSource2</name>
   <target>RacConfigServer</target>
   <descriptor-file-name>jdbc/mslvWSDataSource2-5599-jdbc.xml
   </descriptor-file-name>
</jdbc-system-resource>
<jdbc-system-resource>
   <name>mslvPSDataSource2</name>
   <target>RacConfigServer</target>
   <de-scriptor-file-name>jdbc/mslvPSDataSource2-5450-jdbc.xml
   </descriptor-file-name>
</jdbc-system-resource>

  3. Save and close the config.xml file.

  4. Restart the application server.

Modifying the Classpath for the WebLogic Server Domain

To update the classpath for the WebLogic domain:

  1. Edit the mslvdomain\bin\setDomainEnv.cmd file.

  2. Add the path for the ons.jar file to the CLASSPATH environment variable.

    For example:

    set CLASSPATH=%CLASSPATH%;C:\Oracle\Middleware\Oracle_Home\oracle_common\lib\ons.jar;

  3. Save and close the file.

Configuring the Connection Pool Parameters

To configure the connection pool parameters:

  1. Navigate to the mslvdomain\config\jdbc folder.

  2. Edit each XML file for the mslvWSDataSource, mslvDbTraceDataSource, mslvNoneTxDataSource, mslvProcDataSource, mslvPSDataSource, and mslvWSDataSource data sources, and update the <jdbc-connection-pool-params> element as follows:

    <jdbc-connection-pool-params>
    <initial-capacity>10</initial-capacity>
    <max-capacity>100</max-capacity>
    <min-capacity>2</min-capacity>
    <highest-num-waiters>1000</highest-num-waiters>
    <connection-reserve-timeout-seconds>60</connection-reserve-timeout-seconds>
    <test-connections-on-reserve>true</test-connections-on-reserve>
    <inactive-connection-timeout-seconds>14400
    </inactive-connection-timeout-seconds>
    <test-table-name>SQL ISVALID</test-table-name>
    <statement-timeout>0</statement-timeout>
</jdbc-connection-pool-params>

  3. Save and close the XML file for each data source.

Example 11-1 shows sample XML for one data source.

Example 11-1 Sample of One Data Source

<jdbc-data-source>
   <name>DataSourceName</name>
   <datasource-type>AGL</datasource-type>
   <jdbc-driver-params>
      <url>jdbc:oracle:thin:@server:port/DB</url>
      <driver-name>oracle.jdbc.xa.client.OracleXADataSource</driver-name>
      <properties>
         <property>
            <name>user</name>
            <value>user_name</value>
         </property>
      </properties>
      <password-encrypted></password-encrypted>
   </jdbc-driver-params>
   <jdbc-connection-pool-params>
      <test-table-name>SQL ISVALID</test-table-name>
   </jdbc-connection-pool-params>
   <jdbc-data-source-params>
      <jndi-name>JNDIName</jndi-name>
      <global-transactions-protocol>TwoPhaseCommit
         </global-transactions-protocol>
   </jdbc-data-source-params>
   <jdbc-oracle-params>
      <fan-enabled>true</fan-enabled>
      <ons-node-list />
      <ons-wallet-file />
      <active-gridlink>true</active-gridlink>
   </jdbc-oracle-params>
</jdbc-data-source>

Note:

Some lines in the example are wrapped for the display.

Tuning JDBC Multi Data Source

For each data source perform the steps in this procedure to tune the data sources.

To tune each data source:

  1. Ensure you are logged into the WebLogic Remote console.

  2. In the Domain Structure tree, expand Services, and then click Data Sources.

    The Summary of JDBC Data Sources page is displayed.

  3. Select the target data source (for example, mslvDataSource1).

    The Settings for <DataSource> page is displayed.

  4. Click the Connection Pool tab.

    The Connection Properties are displayed.

  5. Click Lock and Edit.

  6. Scroll down to Initial Capacity and Maximum Capacity.

  7. In the Initial Capacity field, enter 1.

  8. In the Maximum Capacity field, enter 15.

    Note:

    The values of the Initial Capacity and Maximum Capacity fields depend on their particular MSS deployment.

  9. Scroll down to the bottom of the page and click Save.

    The Settings for <DataSource> page is displayed.

  10. Click Advanced to display additional configuration items.

  11. Select the Test Connections On Reserve check box.

  12. Click Save.

    The Settings for <DataSource> page is displayed.

  13. Click the Transaction tab.

    The Transaction Properties are displayed.

  14. Select the Set XA Transaction Timeout check box.

  15. In the XA Transaction Timeout field, enter 3600.

  16. In the XA Retry Duration field, enter 300.

  17. Click Save.

  18. Click Activate Changes.

    The Settings updated successfully message is displayed.

  19. Repeat steps 1 through 18 to tune each data source.