Tutorial: Updating Connection Pools in OAC & OBIEE 12c

I recently ran into an interesting issue with a client — each RPD connected to different databases based on the environment (Dev, Test and Production). Due to the client’s security policy, OBIEE developers were not permitted to have the passwords for the data sources.

To migrate the RPD, a member of the DBA team must be contacted to input the passwords for the connection pools. At times, a DBA with available bandwidth can be difficult to locate (even for a few minutes), and the existing ticketing system does not lend itself to “on the fly” RPD promotions (such as Dev to Test).

If only we had a way to apply connection pools to the RPD while keeping the connection pool information secure…

Cue the theme music.  And enter datamodel.sh

Most of us are familiar with using datamodel.sh with the uploadrpd argument to deploy an RPD. However, datamodel.sh also contains numerous arguments that can be used to manage and update secure RPD content between environments.

First, we need an actively deployed RPD in each of our environments that contains the correct and functional connection pool information. Yes, test your connections in all environments, just in case.

Now, for each environment, we execute datamodel.sh with the listConnectionpool argument:

datamodel.sh listConnectionpool -SI <service_Instance_Name> -U <admin_username> -P <admin_password> -V true -O <output_file_name>.json

The listConnectionpool argument writes the active connection pools (with encrypted passwords) and all variables that are used in connection pools to a json file. Let’s save this off to a safe directory where we can access it for reuse.

Now that we have saved our connection pools for each environment, we can now use the json file and datamodel.sh updateconnectionpool to update the connection pools for the specific (Dev, Test, Prod) environment:

datamodel.sh updateconnectionpool -C /tmp/connection_pools.json -SI <service_Instance_Name> -U <admin_username> -P <admin_password>

In summary, suppose we are migrating our RPD from the development environment to the test environment. If there have not been any connection pool changes, we will now follow this process:

1. Download the current (to be deployed) RPD from the development environment using datamodel.sh downloadrpd
2. Copy the RPD to the test environment.
3. Deploy RPD in the test environment using datamodel.sh uploadrpd
4. Update the connection pools and variables used in connection pools by executing datamodel.sh with the updateconnectionpool argument as described above.

Presented for your enjoyment is an example of the file generated by the listConnectionpool argument.
Note: The encrypted passwords were shortened in the interests of space. 

Enjoy!

 {

    "Title":"List Connection Pools",
"Conn-Pool-Info":[
     {
              "uid":"0000001b-0bd5-0000-714b-e31d00000000",
              "connPool":"Connection Pool",
              "parentName":"\"DW\"",
              "user":"sa",
              "password":"004D48AA3A7FBB7E9300BC",
              "dataSource":"DW",
              "appServerName":"''"
           }
      ],
      "Variables-In-Conn-Pool":[
      {
           "uid":"0023844e-0bd7-0000-714b-e31d00000000",
           "variable":"OLAP_USER",
           "value":"'OBIA11_DW'"
          },
          {
                "uid":"0023844f-0bd7-0000-714b-e31d00000000",
                "variable":"OLAP_DSN",

"value":"'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=DEVDATA)))'"
                }
         ]
}

You might also be interested in...