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 http://docs.oracle.com/cd/E23943_01/core.1111/e16453/toc.htm, as well as the Fusion Middleware Developers Guide for Oracle Data Integrator located at http://docs.oracle.com/cd/E23943_01/integrate.1111/e12643/toc.htm.
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 Oracle.com. 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 second option is Standalone Installation; this installation installs and configures the standalone agent.
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.
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.
In this recipe, we will be using files included in the ODI Companion CD. At the time of writing, it can be downloaded at http://www.oracle.com/technetwork/middleware/data-integrator/downloads/index.html from the Oracle Technology Network. You can follow the instructions using your own repository. No other prerequisites are required.
Unzip the content of the ODI Companion CD and then open up the
/agent_standalone
folder. Unzip theoracledi-agent-standalone.zip
file to the location in which you would like to deploy your standalone agent.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 theodiparams.sh
orodiparams.bat
file using your favorite text editor.We will now modify some of the parameters included in the
odiparams
file starting with the Master repository information. Edit the file so thatODI_MASTER_DRIVER
has the correct JDBC driver class name and theODI_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 theODI_MASTER_USER
andODI_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 set ODI_MASTER_USER=ODI_REPO_11G set ODI_MASTER_ENCODED_PASS=dpfHbipjYbBAfm7P,Dt69kPBV
Next we need to provide the name of the ODI Supervisor user along with its encoded password using the
ODI_SUPERVISOR
andODI_SUPERVISOR_ENCODED_PASS
variables.REM # REM # User credentials for agent startup program REM # set ODI_SUPERVISOR=SUPERVISOR set ODI_SUPERVISOR_ENCODED_PASS=d,yXMMv0Rk6rgNVKEnBb1xE.E
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 namedWORKREP
in this recipe.REM # REM # Work Repository Name REM # set ODI_SECU_WORK_REP=WORKREP
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
Now save the
odiparams.sh
orodiparams.bat
file and close it.Open up ODI Studio and go to Topology Navigator.
In Physical Architecture, right-click on the Agents node and select New Agent.
In the Agent panel, specify the agent's name along with the machine hostname and its port. Save the settings.
Open up a terminal and navigate to your agent's installation
/bin
directory.Start your standalone agent using the
agent.sh
oragent.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:agent.bat –NAME=LOCAL_STANDALONE_AGENT –PORT=20910
Go back to Topology, open up your newly created Physical Agent, and click on Test to verify if all the parameters were entered correctly.
The odiparams.sh 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 databaseODI_MASTER_URL
holds the JDBC URL utilized to connect to the Master repository databaseODI_MASTER_USER
is the name of the database account for your Master repositoryODI_MASTER_ENCODED_PASS
contains the encoded password of your Master repository database accountODI_SUPERVISOR
represents the name of the ODI Supervisor userODI_SUPERVISOR_ENCODED_PASS
stores the encoded password of the ODI Supervisor user
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.
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.
Note
Weblogic Server 10.1.3.6.0 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.
Note
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.
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 port8001
.To deploy and configure domains with WLS, execute
config.bat
orconfig.sh
from within the ODI Home Install atMiddleware_HOME\ODI_HOME\common\bin
.Tip
Downloading the example code
You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
Select the Create a new Weblogic domain option and click on Next.
Select Oracle Data Integrator - Agent - 11.1.1.0 [Oracle_ODI1], which will additionally select the two options as shown in the following screenshot, and click on Next:
For this recipe, accept the defaults and click on Next.
Accept the default name as
weblogic
, set the password asweblogic1
, and click on Next.Select SUN JDK and click on Next.
Enter the appropriate connection information to connect to the Master repository and click on Next.
Ensure that the test connection was successful and click on Next.
Check the Managed Servers, Clusters and Machines option and click on Next.
Set the port to
8001
, accept the defaults, and click on Next.Click on the Next button on the Configure Clusters screen.
Accept the defaults and click on Next on the Configure Machines screen.
Click on Next on the Assign Servers to Machines screen.
Review the Configuration Summary screen and click on Create.
Do not click on the Start Admin Server check box. Then, click on Done.
Start the ODI WLS admin server for this domain from the command shell. Execute
startweblogic.cmd
orstartweblogic.sh
fromMiddlware_Home\user_projects\domains\base_doman\bin
.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:
Execute WLST, connect to our running admin server, and add the credential store.
Start WLST from a command shell and change the directory to
Middleware_home\odi_home\oracle_common\common\bin
.Execute WLST.
Execute the following command to connect to the running admin server:
connect ('weblogic','weblogic1','t3://localhost:7001').
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")
Enter
weblogic
as the username andweblogic1
as the password.Verify
base_domain
is in the running mode and that there are no stack trace errors.Verify connectivity to Java EE Agent through ODI Studio Topology.
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.
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 http://docs.oracle.com/cd/E14571_01/core.1111/e10106/odi.htm.
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 http://docs.oracle.com/cd/E21764_01/core.1111/e16453/configure.htm#autoId9.
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.
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 (https://edelivery.oracle.com/) 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.
Start the Oracle Fusion Middleware Web Tier Utilities 11g installer from its installation directory.
In the Install and Configure section, select Install and Configure and click on Next.
In the Configure Components part of the installer, check the Oracle HTTP Server and Oracle Web Cache checkboxes and then click on Next.
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:
Click on Next to go to the next installer screen.
In the Web Cache Administrator Password screen, enter the password of your choice. In this recipe, we use
welcome1
and click on Next.In the Configure Ports step, select Auto Port Configuration and click on Next.
Finally, click on Install in the Installation Summary screen to start the Oracle Web Tier Utilities installation process.
Once OPMN is installed, go to your
ODI_HOME/oracledi/agent/bin/
directory and open up theagentcreate.properties
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:ORACLE_ODI_HOME=C:/fmw/Oracle_ODI_1 INSTANCE_HOME=C:/fmw/Oracle_WT1/instances/instance1 COMPONENT_TYPE=odiagent COMPONENT_NAME=ODI_OPMN_AGENT ODI_MASTER_DRIVER=oracle.jdbc.OracleDriver ODI_MASTER_URL=jdbc:oracle:thin:@localhost:1521:orcl ODI_MASTER_USER=ODI_REPO_11G ODI_MASTER_ENCODED_PASS=dpfHbipjYbBAfm7P,Dt69kPBV ODI_SECU_WORK_REPO=ODI_11G_WREP ODI_SUPERVISOR_ENCODED_PASS=d,yXMMv0Rk6rgNVKEnBb1xE.E PORTNO=20920 JAVA_HOME=C:/Java/jdk1.6.0_24 ORACLE_OPMN_HOME=C:/fmw/Oracle_WT1 JMXPORTNO=21920
The path values to provide such as
ORACLE_ODI_HOME
orINSTANCE_HOME
must be by using forward slashes ('/
') and not backward slashes ('\
').Next, open up the
odi_opmn_addagent.bat
orodi_opmn_addagent.sh
script and enter the path values forODI_HOME
andOPMN_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
Go to
OPMN_HOME/instances/INSTANCE_NAME/bin
, in whichINSTANCE_NAME
needs to be replaced by the OPMN instance name created earlier with the Web Tier Utilities installer (instance1
in our recipe).Then run the following command to start OPMN:
opmnctl.bat start
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:odi_opmn_addagent.bat
Subsequently, go back to
OPMN_HOME/instances/INSTANCE_NAME/bin
and enter the following command to verify that the ODI standalone agent namedODI_OPMN_AGENT
is started:opmnctl.bat status
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.
The agentcreate.properties
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 directoryCOMPONENT_NAME
is the name of the Physical Agent that will be managed through OPMNODI_MASTER_DRIVER
andODI_MASTER_URL
respectively represent the class name of the JDBC driver and the JDBC URL used to connect to the Master repositoryODI_MASTER_USER
andODI_MASTER_ENCODED_PASS
are the database username and encoded password required to access the Master repositoryODI_SUPERVISOR_ENCODED_PASS
stores the encoded ODI Supervisor user passwordPORTNO
corresponds to the port number the agent will be listening toJMX_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.
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 11.1.1.6.0 was released. Although there is no specific support listed in the ODI Certification Matrix for ODI 11.1.1.6.0 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.
This recipe we will use the Microsoft SQL Server 2012 JDBC driver as an example. This file can be downloaded from Microsoft.com and deployed to ODI as outlined in the following section. No other prerequisites are required.
Download the Microsoft JDBC Driver SQL 4.0 with SQL Server 2012 support from Microsoft.com.
First, make sure to exit all ODI processes on the machine where the driver will be deployed.
Copy the
.jar
file to theodi\oracledi\userlib
path, which is located in theappdate
environment variable. For example, on Windows 2008 Server the directory isC:\Users\dsgray\AppData\Roaming\odi\oracledi\userlib\
.Restart ODI Studio, go to topology, and add a new data sever to Microsoft SQL Server Technology.
Set Name, User, and Password to match your SQL 2012 Server settings.
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.
Note
This driver shows SQL Sever 2005, however this is from the description embedded in the driver, not ODI.
Set the JDBC URL appropriately for your SQL 2012 Server.
Click on the Test Connection button.
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.
Click on the Test Connection button.
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.Once the domain is completely shut down, copy the SQLServer2012 JDBC jar to the
Middleware_Home\user_projects\domains\base_domain\lib
path.Restart the ODI JEE Agent domain, base domain.
Within ODI Studio, click on the Test Connection button again.
The new JDBC driver has now been successfully set up on the ODI JEE Agent.
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 SalesForce.com. Setting up the additional JDBC drivers for these types of technologies would follow the same steps as outlined for the SQL Server 2012 driver.