Getting a Tivoli Data Warehouse to communicate with an Oracle Database

by bluemedora_editor on July 16, 2009

Hello again, Brian Demers here. This is my second contribution to the Blue Medora Blog. Today I will be covering how to get a Tivoli Data Warehouse to communicate with an Oracle Database.

Versions

  • Windows Server 2003 SP2 running in a VM on a VMware vSphere 4 Server
  • IBM Tivoli Monitoring v.6.2.1 IF2
  • Oracle v.10g R2 Database
  • OJDBC v.1.4

Pre-requisites

This guide assumes that you have already successfully installed ITM and Oracle v.10g R2. At the time of this post, IBM claimed the following version of Oracle to be suitable for use with ITM: v9.2, v9.2 Release (R) 1, V9.2 R2, v10g, v10g R1, v11g R2.

Disclaimer

This guide does not cover the steps required to configure the Tivoli Enterprise Portal Server (TEPS) to use Oracle. Also note that this is not the definitive way to setup Oracle and make it work with ITM, rather this is an example of how I got it working.

Walkthrough

1. Setting up the Database

Navigate your browser to:

http://<IP_OF_SERVER>:1158/em

Login using the following credentials:

  • User Name: sys
  • Password: password
  • Connect As: SYSDBA

[vc_single_image image=”2968″ alignment=”center” border_color=”grey” img_link_large=”” img_link_target=”_self” img_size=”full”]2. Navigate to Administrator, scroll down to and select the hyperlink titled Users. Create a user calledITMUser with a password of itmpswd1.[vc_single_image image=”2969″ alignment=”center” border_color=”grey” img_link_large=”” img_link_target=”_self” img_size=”full”]3. Navigate again to Administrator, scroll down to and select the hyperlink titled Roles. Create a roll called ITM_DW.[vc_single_image image=”2970″ alignment=”center” border_color=”grey” img_link_large=”” img_link_target=”_self” img_size=”full”]4. Log into SQL*Plus via Windows command line interface, via: sqlplus / as SYSDBA, or the Windows utility, ala the shortcut laid down on the Start Menu.[vc_single_image image=”2971″ alignment=”center” border_color=”grey” img_link_large=”” img_link_target=”_self” img_size=”full”]Once in, grant the permissions to the recently created role via the following command:

GRANT CONNECT TO “ITM_DW”;

GRANT ALTER SESSION TO “ITM_DW”;

GRANT CREATE SESSION TO “ITM_DW”;

GRANT CREATE TABLE TO “ITM_DW”;

GRANT CREATE ANY INDEX TO “ITM_DW”;

GRANT CREATE ANY PROCEDURE TO “ITM_DW”;

GRANT CREATE VIEW TO “ITM_DW”;[vc_single_image image=”2972″ alignment=”center” border_color=”grey” img_link_large=”” img_link_target=”_self” img_size=”full”]NOTE: It is important to realize that your mileage may vary. Realize that I am using Oracle 10g, and commands may be subject to change depending on the version.

5. Give the user ITMUser the role ITM_DW.[vc_single_image image=”2973″ alignment=”center” border_color=”grey” img_link_large=”” img_link_target=”_self” img_size=”full”]6. Registering the Oracle driver with Windows’ ODBC Data Source Administrator

Next up, we will need to register the Oracle driver under Windows Server’s ODBC Data Source Administrator. Go to the Start Menu, navigate to Administrative Tools, then select Data Sources (ODBC). Select the System DSN tab. If your Oracle driver is not listed, which should be the case, selectAdd, scroll down to where your Oracle driver is listed—in my case it says Oracle OraDb10g_home1—then select Next. The next window is going to need you to input information regarding the ODBC connection you are going to create. For Data Source Name enter ITM Warehouse, for TNS Service Name enter WAREHOUS, then for User ID enter ITMUser.[vc_single_image image=”2974″ alignment=”center” border_color=”grey” img_link_large=”” img_link_target=”_self” img_size=”full”]Give the user ITMUser the role ITM_DW.

7. Creating the TNS Service Name

Navigate to Oracle’s Net Configuration Assistant, whose shortcut should be in the Start Menu somewhere. Select Local Net Service Name configuration then select Add. In the Service Name field, enter WAREHOUS. For the network protocol, select TCP. Next, enter the host name and port number of the computer on which the Database resides. The default port number is 1521. Perform the connection test to verify you can actually connect to it. You should then be prompted to configure another net service name, select No. Once back at the original screen, click Finish to exit.

8. Verifying the connection of the Summarization and Pruning Agent

Open up the shortcut to the Manage Tivoli Enterprise Monitoring Services application which should be laid down by default in the Start Menu. Right-click on Warehouse Summarization and Pruning Agent and click either Configure Using Defaults or Reconfigure. Click OK through the first two windows then click Yes when it prompts to see if you Would like to configure this Summarization and Pruning Agent? Select Oracle from the Database type pull down menu. In Warehouse URL, replace <server> with the name of your machine, <port> with the default of 1521 and <database> with the default of WAREHOUS. My Warehouse URL ended up looking as follows:

jdbc:oracle:thin:@tcr-oracle:1521:WAREHOUS

Next, specify the Warehouse User to ITMUser with a Warehouse Password of itmpswd1.[vc_single_image image=”2975″ alignment=”center” border_color=”grey” img_link_large=”” img_link_target=”_self” img_size=”full”]Verify that you can connect to the database by clicking Test database connection.I had funny issues with it automatically selecting a different JDBC driver than the one which loads with into the system with the Oracle installation. If there is a driver different than the one located at:C:\oracle\product\10.2.0\db_1\jdbc\lib\ojdbc14.jar make sure to select it in the JDBC Drivers list and then click the Delete button. At this point, I would recommend clicking Save, clicking Close, and then restarting the process of configuring the connection. If the Oracle JDBC Driver does not end up loading, trying adding it manually. If that should not work, I have provided a link to Oracle’s website which has JDBC drivers readily available—the only stipulation is that you have to register (for free).

If you have any questions, please do not hesitate to contact me at brian.demers@bluemedora.com.

Useful links

Oracle JDBC Driver downloadshttp://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html[vc_single_image image=”2976″ alignment=”center” border_color=”grey” img_link_large=”” img_link_target=”_self” img_size=”full”][vc_column width=”1/3″ el_class=”sidebar”][ultimatesocial_false count=”” align=”left” skin=”default” share_text=”Share: ” networks=”facebook, twitter, linkedin, reddit”]

Get started

Try BindPlane for free. No credit card required.

Sign up
True Visibility
BindPlane for VMware vRealize Operations

True Visibility allows cloud management teams to use VMware vRealize’s powerful machine learning and capacity planning engine across their entire hybrid cloud environment.

Azure Monitor...everything
BindPlane for Microsoft Azure Monitor

Make Azure Monitor your first-pane-of-glass across your entire multi-cloud, multi-database or hybrid platform environment.

Thank you for contacting us. Your information was received. We'll be in touch shortly.