Oracle Data Integrator 11g Cookbook

Unzip the content of the ODI Companion CD and then open up the /agent_standalone folder. Unzip the oracledi-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 the odiparams.sh or odiparams.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 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
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 and ODI_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 named WORKREP 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 or odiparams.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 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:

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 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.

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

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 as weblogic1, 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 or startweblogic.sh from Middlware_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 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 and weblogic1 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.

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 the agentcreate.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 or INSTANCE_HOME must be by using forward slashes ('/') and not backward slashes ('\').

Next, open up the odi_opmn_addagent.bat or odi_opmn_addagent.sh 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

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).

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 named ODI_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.

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 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\.

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.

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.

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