Oracle Data Integrator 11g Cookbook

4 (1 reviews total)
By Christophe Dupupet , Denis Gray , Peter C. Boyd-Bowman and 1 more
    What do you get with a Packt Subscription?

  • Instant access to this title and 7,500+ eBooks & Videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Free Chapter
    Installation, Deployment, and Configuration

About this book

Oracle Data Integrator (ODI) is Oracle's strategic data integration platform for high-speed data transformation and movement between different systems. From high-volume batches, to SOA-enabled data services, to trickle operations, ODI is a cutting-edge platform that offers heterogeneous connectivity, enterprise-level deployment, and strong administrative, diagnostic, and management capabilities.

"Oracle Data Integrator 11g Cookbook" will take you on a journey past your first steps with ODI to a new level of proficiency, lifting the cover on many of the internals of the product to help you better leverage the most advanced features.

The first part of this book will focus on the administrative tasks required for a successful deployment, moving on to showing you how to best leverage Knowledge Modules with explanations of their internals and focus on specific examples. Next we will look into some advanced coding techniques for interfaces, packages, models, and a focus on XML. Finally the book will lift the cover on web services as well as the ODI SDK, along with additional advanced techniques that may be unknown to many users.

Throughout "Oracle Data Integrator 11g Cookbook", the authors convey real-world advice and best practices learned from their extensive hands-on experience.

Publication date:
May 2013


Chapter 1. Installation, Deployment, and Configuration

The recipes in this chapter will help users learn the following:

  • Deploying and configuring a standalone agent manually

  • Deploying JEE ODI Agent

  • Configuring a standalone agent with OPMN

  • Deploying JDBC drivers with JEE ODI Agent



Installation, deployment, and configuration are key to every successful software implementation. Oracle Data Integrator is no different; thoroughly understanding the different installation and deployment options will help in planning the overall topology for ODI.

This chapter provides quick-start recipes for installation, deployment, and configuration. For detailed information on each topic, please review the Oracle Fusion Middleware Installation Guide for Oracle Data Integrator at, as well as the Fusion Middleware Developers Guide for Oracle Data Integrator located at

Understanding the ODI Installation

Before jumping into the recipes, some background is needed on the ODI components:

  • ODI repositories: It consists of Master and Work repositories in which ODI metadata and data is stored

  • ODI Studio: It is used to develop ETL mappings as well as to administer and monitor the ODI

  • ODI standalone agent: The ODI runtime process that orchestrates ETL flows

  • JEE components:

    • Enterprise manager fusion middleware control plugin for ODI: It is used to monitor ODI from a web browser

    • Public web services: They are used to start and monitor scenarios through the JEE or a standalone agent

    • ODI console: It is used to execute and monitor ODI jobs as well as browse ODI metadata from a web browser

    • JEE agent: JEE enabled ODI agent that allows the ODI agent to inherently take advantage of the application server's enterprise features, such as high availability, connection pooling, and so on.

Each component can be installed and configured with the Oracle Universal Installer, which is delivered in the installation bundle that was downloaded from The ODI Oracle Universal Installer has three choices for installing ODI. Each option is not exclusive; one option or all three options may be selected.

The first option is Developer Installation; this installation will install and configure ODI Studio and the ODI SDK. As you might guess, any ODI developer will need this installation type.


The ODI Studio agent can be manually installed from the ODI Companion CD.

The second option is Standalone Installation; this installation installs and configures the standalone agent.


The ODI standalone agent can be manually installed from the ODI Companion CD.

The third option is Java EE Installation. This option will install the ODI JEE files in an existing Oracle Middleware home. Each JEE component is configured during another process using the FMW configuration utility.

The ODI installation process has been extensively documented. Each enterprise deployment of ODI will vary, depending on many of the variables. The Oracle Universal Installer automates virtually every aspect of the ODI installation and guides the user through the installation process and configuration process. Additional configuration tasks are needed for the JEE components. Please refer to the ODI Installation guide for more information.

Although the Oracle Universal Installer does automate most ODI installation and configuration tasks, there are use cases when it is not feasible to use the Oracle Universal Installer, such as when the server where the ODI component is installed does not have graphical capabilities or if there is no platform specific installation for an OS. For this type of installation, many of the ODI components can be deployed manually. Deploying the ODI standalone agent is outlined in the next recipe.


Deploying and configuring a standalone agent manually

Performing a manual installation is required when the server on which you would like to set up your ODI standalone agent does not possess the graphical capabilities required to run the Oracle Universal Installer for Oracle Data Integrator, or when there is no installer available for the operating system platform you are working with. Manually installing a standalone agent should be done only when necessary, as bypassing the installer will not allow the application of patches, so proceed with caution.

Getting ready

In this recipe, we will be using files included in the ODI Companion CD. At the time of writing, it can be downloaded at from the Oracle Technology Network. You can follow the instructions using your own repository. No other prerequisites are required.

How to do it...

  1. Unzip the content of the ODI Companion CD and then open up the /agent_standalone folder. Unzip the file to the location in which you would like to deploy your standalone agent.

  2. Go to the directory in which you extracted the files required to run your standalone agent, then navigate to the oracledi/agent/bin directory, and open up the or odiparams.bat file using your favorite text editor.

  3. We will now modify some of the parameters included in the odiparams file starting with the Master repository information. Edit the file so that ODI_MASTER_DRIVER has the correct JDBC driver class name and the ODI_MASTER_URL value is a valid JDBC URL for the database hosting your Master repository. Finally, complete the Master repository configuration by providing the database username and its password using the ODI_MASTER_USER and ODI_MASTER_ENCODED_PASS parameters. We use the following values in this recipe:

    REM #
    REM # Repository Connection Information
    REM #
    set ODI_MASTER_DRIVER=oracle.jdbc.OracleDriver
    set ODI_MASTER_URL=jdbc:oracle:thin:@localhost:1521:orcl
  4. Next we need to provide the name of the ODI Supervisor user along with its encoded password using the ODI_SUPERVISOR and ODI_SUPERVISOR_ENCODED_PASS variables.

    REM #
    REM # User credentials for agent startup program
    REM #


    The encoded passwords such as ODI_MASTER_ENCODED_PASS or ODI_SUPERVISOR_ENCODED_PASS can be obtained using the encode.bat or command provided in the agent/bin directory.

  5. Subsequently, we set the value of ODI_SECU_WORK_REP to the name of the Work repository that will be used by this standalone agent. The Work repository is named WORKREP in this recipe.

    REM #
    REM # Work Repository Name
    REM #
  6. Optionally we can also change the Java Virtual Machine settings for this agent using the ODI_INIT_HEAP and ODI_MAX_HEAP parameters. Out of the box, the JVM settings are quite low, so it is a good idea to increase their values; the values below are not example settings:

    REM #
    REM # Other Parameters
    REM #
    set ODI_INIT_HEAP=256m
    set ODI_MAX_HEAP=1024m


    The ODI_INIT_HEAP and ODI_MAX_HEAP are recommended settings for this exercise. The required settings for each parameter depend on the actual aggregate memory requirements for all the integrations run on an agent.

  7. Now save the or odiparams.bat file and close it.

  8. Open up ODI Studio and go to Topology Navigator.

  9. In Physical Architecture, right-click on the Agents node and select New Agent.

  10. In the Agent panel, specify the agent's name along with the machine hostname and its port. Save the settings.

  11. Open up a terminal and navigate to your agent's installation /bin directory.

  12. Start your standalone agent using the or agent.bat script and by providing its name (same as the one specified in Topology) and its port. In this recipe we use the following command:

  13. Go back to Topology, open up your newly created Physical Agent, and click on Test to verify if all the parameters were entered correctly.

How it works...

The or odiparams.bat file contains all the parameters required for a standalone agent to connect to an existing pair of Master and Work repositories. We will now highlight some of the parameters included in the file that were used in this recipe:

  • ODI_MASTER_DRIVER stores the class name of the JDBC driver used to access the Master repository database

  • ODI_MASTER_URL holds the JDBC URL utilized to connect to the Master repository database

  • ODI_MASTER_USER is the name of the database account for your Master repository

  • ODI_MASTER_ENCODED_PASS contains the encoded password of your Master repository database account

  • ODI_SUPERVISOR represents the name of the ODI Supervisor user

  • ODI_SUPERVISOR_ENCODED_PASS stores the encoded password of the ODI Supervisor user

There's more...

It is also possible to install a standalone agent using the Oracle Universal Installer for an ODI if you select the Standalone Installation option. The Oracle Data Integrator Installer has the capability to deploy a standalone agent and configure it automatically for a given Master and Work repositories pair.


Deploying a JEE ODI Agent

The ODI Agent can be deployed as a Java EE component within an application server. This installation type allows the ODI agent to take advantage of the benefits of an enterprise application server. When the ODI JEE Agent is deployed within Oracle WebLogic Server, the ODI JEE Agent can leverage the WebLogic's enterprise features, such as clustering and connection pooling for high availability and enterprise scalability. This Java EE Agent exposes an interface enabling lifecycle operation (start/stop) from the application server console and metrics that can be used by the application server console to monitor the agent activity and health.

The ODI 11g Java EE Agent can be deployed to an existing domain or deployed automatically when creating a new domain.


Weblogic Server is required for this recipe. Please review the latest Oracle Data Integrator Certification Matrix for the latest version compatibility.

This recipe will create a new WebLogic domain by deploying the Java EE Agent template.


The standard ODI Java EE Agent template will be used to deploy our Java EE Agent. A template for any agent can also be generated from within ODI Studio and then used to deploy this agent.

How to do it...

  1. The Java EE Agent must exist in the ODI topology before the WLS domain server is started for the agent. Connect to ODI Studio, expand ODI Agents within the topology, and add an agent with the name OracleDIAgent and with the port 8001.

  2. To deploy and configure domains with WLS, execute config.bat or from within the ODI Home Install at Middleware_HOME\ODI_HOME\common\bin.


    Downloading the example code

    You can download the example code files for all Packt books you have purchased from your account at If you purchased this book elsewhere, you can visit and register to have the files e-mailed directly to you.

  3. Select the Create a new Weblogic domain option and click on Next.

  4. Select Oracle Data Integrator - Agent - [Oracle_ODI1], which will additionally select the two options as shown in the following screenshot, and click on Next:

  5. For this recipe, accept the defaults and click on Next.

  6. Accept the default name as weblogic, set the password as weblogic1, and click on Next.

  7. Select SUN JDK and click on Next.

  8. Enter the appropriate connection information to connect to the Master repository and click on Next.

  9. Ensure that the test connection was successful and click on Next.

  10. Check the Managed Servers, Clusters and Machines option and click on Next.

  11. Set the port to 8001, accept the defaults, and click on Next.

  12. Click on the Next button on the Configure Clusters screen.

  13. Accept the defaults and click on Next on the Configure Machines screen.

  14. Click on Next on the Assign Servers to Machines screen.

  15. Review the Configuration Summary screen and click on Create.

  16. Do not click on the Start Admin Server check box. Then, click on Done.

  17. Start the ODI WLS admin server for this domain from the command shell. Execute startweblogic.cmd or from Middlware_Home\user_projects\domains\base_doman\bin.

  18. Security has to be set up for the JAVA EE application to have access to the ODI repository. For this access, an entry needs to be created within the credential store that will allow the JAVA EE Agent to authenticate itself and consume the resources that are needed. This user must already be set up in the ODI Security. To do this, we will do the following:

    1. Execute WLST, connect to our running admin server, and add the credential store.

    2. Start WLST from a command shell and change the directory to Middleware_home\odi_home\oracle_common\common\bin.

    3. Execute WLST.

  19. Execute the following command to connect to the running admin server:

    connect ('weblogic','weblogic1','t3://localhost:7001').
  20. Execute the following command to add the correct credential store for ODI Supervisor:

    createCred(map="oracle.odi.credmap", key="SUPERVISOR", user="SUPERVISOR", password="SUNOPSIS", desc="ODI SUPERVISOR Crendential")


    During runtime, the JAVA EE Agent will access this credential store to authenticate itself.

    1. Type exit() to close WLST.

    2. Start a command shell and change the directory to the user_projects directory of the Middleware_Home - Middleware_HOME\user_projects\domains\base_domain\bin.

    3. Execute StartManagedWeblogic odi_server1.

  21. Enter weblogic as the username and weblogic1 as the password.

  22. Verify base_domain is in the running mode and that there are no stack trace errors.

  23. Verify connectivity to Java EE Agent through ODI Studio Topology.

How it works...

Oracle has documented the overall process of installation and configuring the ODI JEE Agent. However, installation and deployment are broken into different sections of the documentation. This recipe gives a quick walk-through of the steps needed to easily install the JEE ODI Agent and also the steps necessary to configure the agent on the WebLogic server as well as the updates required within the ODI repository.

There's more...

The ODI JEE Agent can easily take advantage of the enterprise scalability features of Weblogic Server. Setting up clustering with the ODI JEE Agent is straightforward and follows the same setup as above. However, two agents would be created and then clustered. This is outlined in the Fusion Middleware High Availability Guide at

There are also many more exciting ODI JEE components that are also easily installed and deployed, including the ODI Console, the ODI Admin Plug-in for the FMW EM Control, and the SDK Web Services, which are outlined at


Configuring a standalone agent with OPMN

Oracle Process Manager and Notification Server (OPMN) provides the ability to manage the lifecycle of ODI standalone agents. Using OPMN Oracle Data Integrator, users can control and monitor the status of standalone agents in Oracle Enterprise Manager. In addition, OPMN can automatically restart ODI standalone agents in case of failures.

Getting ready

In this recipe, we will be using the Oracle Web Tier Utilities installer, which contains OPMN. At the time of writing, the installer for Oracle Fusion Middleware Web Tier Utilities 11g can be downloaded from the Oracle Software Delivery Cloud ( as part of the 'Oracle Fusion Middleware 11g Media Pack'. Make sure to download the right version for your operating system.

An agent named ODI_OPMN_AGENT needs to be defined as a Physical Agent in your ODI Topology.

No other prerequisites are required.

How to do it...

  1. Start the Oracle Fusion Middleware Web Tier Utilities 11g installer from its installation directory.

  2. In the Install and Configure section, select Install and Configure and click on Next.

  3. In the Configure Components part of the installer, check the Oracle HTTP Server and Oracle Web Cache checkboxes and then click on Next.

  4. In the Specify Component Details step, we use the default installation setting for the Instance Home Location, Instance Name, OHS Component Name, and Web Cache Component Name options:

  5. Click on Next to go to the next installer screen.

  6. In the Web Cache Administrator Password screen, enter the password of your choice. In this recipe, we use welcome1 and click on Next.

  7. In the Configure Ports step, select Auto Port Configuration and click on Next.

  8. Finally, click on Install in the Installation Summary screen to start the Oracle Web Tier Utilities installation process.

  9. Once OPMN is installed, go to your ODI_HOME/oracledi/agent/bin/ directory and open up the file to edit it. The parameters in this file need to be modified to correspond to your ODI and OPMN configuration settings. In this recipe we use the following values:


    The path values to provide such as ORACLE_ODI_HOME or INSTANCE_HOME must be by using forward slashes ('/') and not backward slashes ('\').


    The encoded passwords, such as ODI_MASTER_ENCODED_PASS or ODI_SUPERVISOR_ENCODED_PASS, can be obtained using the encode.bat or command provided in the ODI_HOME/oracledi/agent/bin directory.

  10. Next, open up the odi_opmn_addagent.bat or script and enter the path values for ODI_HOME and OPMN_HOME. We use the following values in this recipe:

    if "%ODI_HOME%" == "" set ODI_HOME=C:/fmw/Oracle_ODI_1/oracledi/agent
    REM call "%ODI_HOME%\bin\odiparams.bat"
    if "%OPMN_HOME%" == "" set OPMN_HOME=C:/fmw/Oracle_WT1
    if "%INSTANCE_HOME%" == "" set INSTANCE_HOME=C:/fmw/Oracle_WT1/instances/instance1
  11. Go to OPMN_HOME/instances/INSTANCE_NAME/bin, in which INSTANCE_NAME needs to be replaced by the OPMN instance name created earlier with the Web Tier Utilities installer (instance1 in our recipe).

  12. Then run the following command to start OPMN:

    opmnctl.bat start
  13. Once OPMN is started, navigate to ODI_HOME/oracledi/agent/bin/ in your command prompt and run the following command to add a standalone agent to OPMN:

  14. Subsequently, go back to OPMN_HOME/instances/INSTANCE_NAME/bin and enter the following command to verify that the ODI standalone agent named ODI_OPMN_AGENT is started:

    opmnctl.bat status
  15. The agent ODI_OPMN_AGENT has now been successfully started and is now managed by OPMN. You can test the connection to the agent from Topology Navigator in ODI Studio.

How it works...

The file contains all the required information for OPMN to manage a standalone agent. We'll take a look at some of those parameters:

  • ORACLE_ODI_HOME represents the path to the ODI installation directory

  • INSTANCE_HOME points to the OPMN instance home directory

  • COMPONENT_NAME is the name of the Physical Agent that will be managed through OPMN

  • ODI_MASTER_DRIVER and ODI_MASTER_URL respectively represent the class name of the JDBC driver and the JDBC URL used to connect to the Master repository

  • ODI_MASTER_USER and ODI_MASTER_ENCODED_PASS are the database username and encoded password required to access the Master repository

  • ODI_SECU_WORK contains the name of the Work Repository

  • ODI_SUPERVISOR_ENCODED_PASS stores the encoded ODI Supervisor user password

  • PORTNO corresponds to the port number the agent will be listening to

  • JMX_PORTNO is the Java Management Extensions (JMX) port number the agent will be using. JMX is used to propagate events from the agent to monitoring applications such as Oracle Enterprise Manager.

There's more...

The odi_opmn_deleteagent command allows users to remove agents that were previously added to the OPMN configuration settings. It is also possible to stop and restart the ODI agents' processes using the opmnctl command.


Deploying JDBC drivers with a JEE ODI Agent

In this recipe, we will be looking at how to deploy additional JDBC drivers to a JEE ODI Agent. ODI is delivered out of the box with support for many relational databases, however there are times when additional data sources may be needed. The ODI generic knowledge modules support virtually any Type 4 JDBC drivers. If the new source does have a Type 4 JDBC driver, this driver can be used to extract data from the source and can also be used to load to a target.

In this recipe, we will use a JDBC driver that is not shipped out of the box with ODI. Microsoft SQL Server 2012 was released after ODI was released. Although there is no specific support listed in the ODI Certification Matrix for ODI and SQL Server 2012, the new JDBC driver can be deployed to ODI Studio. Once the driver is deployed to the ODI Studio and the ODI JEE Agent, a SQL Server 2012 data server can be set up in the ODI topology and then used within an integration.

Getting ready

This recipe we will use the Microsoft SQL Server 2012 JDBC driver as an example. This file can be downloaded from and deployed to ODI as outlined in the following section. No other prerequisites are required.

How to do it…

  1. Download the Microsoft JDBC Driver SQL 4.0 with SQL Server 2012 support from

  2. First, make sure to exit all ODI processes on the machine where the driver will be deployed.

  3. Copy the .jar file to the odi\oracledi\userlib path, which is located in the appdate environment variable. For example, on Windows 2008 Server the directory is C:\Users\dsgray\AppData\Roaming\odi\oracledi\userlib\.

  4. Restart ODI Studio, go to topology, and add a new data sever to Microsoft SQL Server Technology.

  5. Set Name, User, and Password to match your SQL 2012 Server settings.

  6. On the JDBC tab, click the search icon to get the list of JDBC drivers. Notice that the new driver now shows in the list Microsoft SQL Server 2005 Driver for JDBC. Select this driver and click on OK.


    This driver shows SQL Sever 2005, however this is from the description embedded in the driver, not ODI.

  7. Set the JDBC URL appropriately for your SQL 2012 Server.

  8. Click on the Test Connection button.

  9. We have now successfully tested the new driver within ODI Studio. However, we can also test the ODI JEE agent here by selecting OracleDIAgent that was setup in the previous Deploying a JEE ODI Agent recipe.

  10. Click on the Test Connection button.


    Notice there was an error during this test. This was expected as the ODI JEE Agent could not load the appropriate class since the SQL Server 2012 JDBC .jar file has not been copied to the WebLogic server.

  11. Before copying the SQLServer2012 JDBC .jar file to the Weblogic server, the ODI domain needs to be shut down. Shut down the ODI JEE Agent base domain.

  12. Once the domain is completely shut down, copy the SQLServer2012 JDBC jar to the Middleware_Home\user_projects\domains\base_domain\lib path.

    1. Restart the ODI JEE Agent domain, base domain.

    2. Within ODI Studio, click on the Test Connection button again.

  13. The new JDBC driver has now been successfully set up on the ODI JEE Agent.

There's more...

This recipe demonstrated adding a new relational JDBC driver to the ODI JEE Agent. JDBC drivers do exist for non-relational targets and sources such as Microsoft Excel, or also for applications such as Setting up the additional JDBC drivers for these types of technologies would follow the same steps as outlined for the SQL Server 2012 driver.

About the Authors

  • Christophe Dupupet

    Christophe Dupupet is a Director in the Fusion Middleware Architects Team, where he leads the expertise on ODI. The team works closely with strategic customers that implement ODI, and helps define best practices on the product in terms of architecture, implementation, and operations. Prior to Oracle, Christophe was part of the team that started the operations for Sunopsis in the US, where he lead the technical team (presales, support, and training). Sunopsis was acquired by Oracle in 2006. Christophe holds an Operations Research degree from EISTI in France, a Masters Degree in Operations Research from Florida Tech, and a certificate in Management from Harvard University.

    Browse publications by this author
  • Denis Gray

    Denis Gray is a Director of Product Management for Data Integration at Oracle. Denis has over 15 years of experience in the data-integration field. For the past seven years, Denis has been an integral part of Oracle Development Organization as a Product Manager within Fusion Middleware, delivering data integration solutions. Prior to this, Denis was a data integration consult for Hyperion Solutions (Oracle). Here, Denis worked at many of the largest Fortune 100 companies, building data warehouses and implementing business intelligence solutions. Denis has a Bachelor's Degree in Computer Science from the University of Missouri and currently resides in St. Louis, MO. Denis also co-authored the Packt book Getting Started with Oracle Data Integrator 11g: A Hands-on Tutorial.

    Browse publications by this author
  • Peter C. Boyd-Bowman

    Peter C. Boyd-Bowman is a Technical Director and Consultant with the Oracle Corporation. He has over 30 years of software engineering and database management experience, including 12 years of focused interest in data warehousing and business intelligence. Capitalizing on his extensive background in Oracle database technologies dating back to 1985, he has spent recent years specializing in data migration. After many successful project implementations using Oracle Warehouse Builder, and shortly after Oracle's acquisition of the Sunopsis Corporation, he switched his area of focus over to Oracle's flagship ETL product: Oracle Data Integrator. Peter holds a BS degree in Industrial Management and Computer Science from Purdue University and currently resides in North Carolina. Peter is a co-author of the book Getting Started with Oracle Data Integrator 11g: A Hands-on Tutorial.

    Browse publications by this author
  • Julien Testut

    Julien Testut is a Product Manager in the Oracle Data Integration group focusing on Oracle Data Integrator. Julien has an extensive background in Data Integration and Data Quality solutions and is a co-author of Getting Started with Oracle Data Integrator 11g: A Hands-on Tutorial. Prior to joining Oracle, he was an Applications Engineer at Sunopsis, which was then acquired by Oracle. Julien holds a Masters degree in Software Engineering.

    Browse publications by this author

Latest Reviews

(1 reviews total)
Oracle Data Integrator 11g Cookbook
Unlock this book and the full library FREE for 7 days
Start now