Month: January 2009

Introduction

Oracle Data Integrator delivers unique next-generation Extract Load and Transform (E-LT) technology that improves performance, reduces data integration costs, even across heterogeneous systems. Oracle Data Integrator offers the productivity of a declarative design approach, as well as the benefits of an active integration platform for seamless batch and real-time integration. In addition, hot-pluggable Knowledge Modules provide modularity, flexibility, and extensibility. Use Oracle Data Integrator together with Oracle Data Profiling and Data Quality for advanced data governance capabilities. Use Oracle Data Integrator together with Oracle SOA Suite to enable service-oriented data integration and management that gives companies even greater flexibility for their data services.

You can explore more on this product at OTN : http://www.oracle.com/technology/software/index.html

Downloading the software

You can down the ODI software from OTN : http://www.oracle.com/technology/software/products/odi/index.html

We are going to see here the installation of ODI 10.1.3.4.0 on Linux x86.

Install Pre-reqs

For installing and configuring ODI, you need to have a SOA 10.1.3 basic installation. When I mean basic installation, it should have a web server and a OC4J container. Basically ODI is going to provide some WAR file which we are going to deploy in OC4J container provided by SOA. So SOA installation is not actually a pre-requisite, but a requisite. You can carry out SOA installation even after installing ODI.

Installing ODI

Once you download the software unzip the same and go to the unziped directory. But inside this directory you wont be able to see runInstaller. Navigate to setup/Linux/Disk1/install directory. Here you will find runInstallation. Follow the below screens to install ODI.

./runInstaller -invPtrLoc $HOME/odi/oraInventory/oraInst.loc

Here you can select first option which will install all the components.

Again here select the complete installation.

Select the ORACLE_HOME name and path that you want to give

Provide the repository port and scheduler port. This option you will see only if you are going for all options along with ODI as select on screen 1 above.

Finally you will see the summary screen. Click on install.

At the end of install it will ask you to run root.sh script as a root user. Once run, installation completes and you can exit the isntaller.

Add the following environment variables for the user who has installed Oracle Data Integrator:

• ODI_JAVA_HOME=<ODI_HOME>/jre/1.4.2
• TS_QUALITY=<ODI_HOME>/oracledq/quality_server/tsq11r5s/Software
• LD_LIBRARY_PATH=<ODI_HOME>/oracledq/quality_server/tsq11r5s/Software/bin

Installing SOA

You can check the SOA installation using my previous post Installing SOA 10g (10.1.3.4) – Fusion Middleware

The only change required in that installation is on the screen 3)

Screen 3) select “J2EE Server, Web Server and SOA Suite” for complete installation.

Instead of selecting “J2EE Server, Web Server and SOA Suite”, please select “J2EE Server and Web Server” as we want only the basic install.

After installing SOA next step comes the deployment part. Following below steps for deploying each application of ODI in SOA.

Deploying ODI Applications

Once the SOA installation completes, login to SOA Enterprise Manager

http://hostname.domain:http_port/em/

in my case its

http://rws60076rems.us.oracle.com:7777/em/

When you login as oc4jadmin in EM you will see home as a default container on the home page. This is the container we are going to use for deploying out ODI applications.

Deploying MetaData Navigator

• To install metadata navigator copy oracledimn.war from software dump to the SOA deployment directory.
• oracledimn.war will be present in <software dump dir>/setup/Manual
• Copy this file to SOA_ORACLE_HOME/j2ee/home/applications directory
• Login to SOA EM console and click on home (oc4j instance)
• go to Applications tab

• Click on Deploy button

On the deployment page under the Archive section select the second dropdown “Archive is already present on the server where Application Server Control is running.” and give the location of server as applications/oracledimn.war

• Click on Next

• Give the application name and the context for the same. This can be any thing that you want, but should be distinct  from other applications.

• Click on Next and then click on Deploy.

• Once deployed click on “Return” button, you will be able to see the application under home oc4j instnace.

• You can click on the application -> on next page, click on the module name and on the next page click on the icon “Test Web Module”. You should be able to open the application.

In my case the URL was http://rws60076rems.us.oracle.com:7777/oracledimn

Installing Lightweight Designer

• To install lightweight designer copy oracledilwd.war from software dump to the SOA deployment directory.
• oracledimn.war will be present in <software dump dir>/setup/Manual
• Copy this file to SOA_ORACLE_HOME/j2ee/home/applications directory
• Login to SOA EM console and click on home (oc4j instance)
• go to Applications tab
• Click on Deploy button

On the deployment page under the Archive section select the second dropdown “Archive is already present on the server where Application Server Control is running.” and give the location of server as applications/oracledilwd.war

• Click on Next

• Give the application name and the context for the same. This can be any thing that you want, but should be distinct  from other applications.

• Click on Next and then click on Deploy.

• Once deployed click on “Return” button, you will be able to see the application under home oc4j instnace.

• You can click on the application -> on next page, click on the module name and on the next page click on the icon “Test Web Module”. You should be able to open the application.

In my case the URL was http://rws60076rems.us.oracle.com:7777/oracledilwd

Configure the repository connections for Lightweight Desiginer

Configuring the Datasources for OC4J

You can configure the data-sources either from the frontend using SOA EM or from the backend by updating data-sources.xml file present in $SOA_ORACLE_HOME/j2ee/home/config directory.

You can make following entry in data-sources.xml file to configure the datasource.

<span class="x24">  <managed-data-source name="Test_datasource"
    connection-pool-name="</span><span class="x24">Test_pool</span><span class="x24">"
    jndi-name="jdbc/test_datasource"/>

  <connection-pool name="</span><span class="x24">Test_pool</span><span class="x24">">
    <connection-factory factory-class="oracle.jdbc.pool.OracleDataSource"
      user="system"
      password="sys123"
      url="jdbc:oracle:thin:@//rws60066rems:1617/prod4fmw">
    </connection-factory>
  </connection-pool>
</span>

Installing the Oracle Data Integrator Public Web Services

This component can be deployed in any web service container normally a Java application server.
It is provided as a Axis2 Archive File.

So to deploy this application you need to first have the Axis application either installed on your host or deplyoed as a J2EE application in your SOA OC4J instance. In this case I am just going to deploy the Axis application in my SOA instnace and then use that application to deploy Oracle Data Integrator Public Web Services.

You can download Axis application from Apache.org site : http://ws.apache.org/axis2/download.cgi

Download the latest version and unzip the same on your linux host. After unzipping you will get axis2.war

• To install axis2 application, copy axis2.war from axis dump to the SOA deployment directory.
• Copy axis2.war to SOA_ORACLE_HOME/j2ee/home/applications directory
• Login to SOA EM console and click on home (oc4j instance)
• go to Applications tab
• Click on Deploy button

• Click on Next

• Give the application name and the context for the same. This can be any thing that you want, but should be distinct  from other applications.

• Click on Next and then click on Deploy.

• Once deployed click on “Return” button, you will be able to see the application under home oc4j instnace.

• You can click on the application -> on next page, click on the module name and on the next page click on the icon “Test Web Module”. You should be able to open the application.

In my case the URL was http://rws60076rems.us.oracle.com:7777/axis2

• Now we can install Oracle Data Integrator Public Web Services which is provided as Axis2 Archive File.
• First ftp the odi-public-ws.aar file to your windows box as you have to browse in the Axis application and give the file
• The .aar will be present under <ODI dump Directory>/oracledi/tools/web_services
• On the Axis applicaiton http://rws60076rems.us.oracle.com:7777/axis2 click on home link and then click on Administration link.
• login as admin/axis2
• On the left side of the page under “Tools” you will see “Upload Service”
• Browse the .aar file you ftped to your windows box and click on upload

You will see the message “File odi-public-ws.aar successfully uploaded”

After deploying these application next and the last thing that is remaining is creating the repository.

Oracle Data Integrator Repositories

There are two types of repositories:

• Master Repository: Data structure containing information on the topology of the company’s IT
  resources, on security and on version management of projects and data models. This
  repository is stored on a relational database accessible in client/server mode from the different
  Oracle Data Integrator modules.
  In general, you need only one master repository.
  

• Work Repository: Data structure containing information on data models, projects, and their
  use. This repository is stored on a relational database accessible in client/server mode from
  the different Oracle Data Integrator modules.
  Several work repositories can be designated with several master repositories if necessary.
  However, a work repository can be linked with only one master repository for version
  management purposes.

The steps to follow to create repositories are detailed below:

1. Creating repository storage spaces
2. Creating the master repository
3. Connecting to the master repository
4. Creating a work repository
5. Connecting to the Work repository

1) Creating repository storage spaces

This involves creating 2 schemas in the database, one for master repository and one for work repository. Use the following commands to create repository in Oracle database

Master Repository:

create user master_rep identified by welcome1 default tablespace users temporary tablespace temp;

grant connect, resource to master_rep;

Work Repository:

create user work_rep identified by welcome1 default tablespace users temporary tablespace temp;

grant connect, resource to work_rep;

2) Creating the master repository

Navigate to the installation directory of ODI and go to oracledi/bin directory

run repcreate.sh

Master repository creation wizard will open. Enter the details for Driver, URL, User and password. Below image shows the same. For selecting driver and URL you have a button in front of the field, click on that and then select the same. For URL details fill in the appropriate JDBC URL info corresponding to your database.

Choose the technology as Oracle if you are using Oracle database.

ID:  A specific ID for the new repository, rather than the default 0. This will affect imports and exports between repositories.

As soon as you click on OK, it will create all the objects in database schema – master_rep.

After the objects are created in repository, you will get the message “Repository Creation successful. Do you want to quite Oracle Data Integrator wizard?”.

Click on OK.

3) Connecting to the master repository

After creating master repository, next we have to create a work repository. For that we have to connect to master repository first.

For connecting to master repository use topology.sh script present in oracledi/bin directory. Following screen will open

Click on the New icon just in front of dropdown to configure the master repository to connect to.

Oracle Data Integrator Connection:

• Login name: A generic alias (for example: Repository:
• User: SUPERVISOR (use capitals)
• Password: SUNOPSIS (use capitals)

DBMS Connection (Master Repository):

• User: User id / login of the owner of the tables you have created for the master repository
• Password: This user’s password
• Drivers’ List: choose the driver required to connect to the DBMS supporting the master
• repository you have just created
• URL: The complete path of the data server hosting the repository. For more information,
• refer to the section JDBC URL Sample

The example of values in my case is as shown below. Please TEST the connection before clicking on OK

Once the connection is successful, click on OK and connect to master repository using SUPERVISOR/SUNOPSIS

You will see following big window

4) Creating a work repository

On the above screen click on 5th icon on the bottom left corner of screen.

Right click on work repository and select “Insert Work Repository”

In the connection window, complete the following parameters:

• Name: Type the name for your work repository connection.
• Technology: Choose the technology of the server to host your work repository.
• User: User id / login of the owner of the tables you are going to create and host of the work repository.
• Password: This user’s password.

• Tab JDBC -> JDBC Driver: The driver required for the connection to the DBMS to host the work repository.

• Tab JDBC -> URL JDBC: The complete path of the data server to host the work repository.

Always check the connection using TEST button and once successful then only click on OK.

Once you click on OK it will ask for the ID, Type and Name.

• ID: give a unique number to your repository, from 1 to 998 included.
• Name: give a unique name to your work repository (for example: work).
• Type: Choose “Development” in the list.

Click on OK and it will create all the objects in Work repository.

After creating work repository you can then exit the topology.sh GUI.

5) Connecting to the Work repository

For connecting to work repository, you have to use desiginer.sh script present in same directory oracledi/bin

Again click on new icon just in front of dropdown.

Oracle Data Integrator Connection:

• Login name: A generic alias (for example: Repository)
• User: SUPERVISOR (in capitals)
• Password: SUNOPSIS (in capitals)

DBMS connection (Master Repository):

• User: User id/login of the owner of the tables you have created for the master repository (not the work repository)
• Password: This user’s password.
• List of drivers: choose the driver required to connect to the DBMS hosting the master repository you have just created.
• URL: The complete path for the data server hosting the master repository.

Work Repository:

• Work repository name: The name you gave your work repository in the previous step(WORK in this example). You can display the list of work repositories available in your master repository by clicking on the button to the right of this field.

Once you click on OK, you will see the previous screen with

Login Name : work

User : SUPERVISOR

Password : SUNOPSIS

Click on OK and you will be connected to work repository.

This completes all the step for ODI installation and configuration, including the repository creation.

Hope this helps !!

References :

Oracle® Data Integrator Installation Guide, 10g Release 3 (10.1.3) – Installation docs (PDF Format)