ODIExperts.com - Page 4 of 13 - The blog for Oracle Data Integrator ( ODI )

September 29, 2010
by kdevendr
22 Comments

Categories: Administration , Architecture , Drivers , How to , Logic , ODI , Tips and Tricks

Tags: ODI 11g , ODI Agent , ODI connection

Drivers Location in ODI 11g

Hi….

A small but very helpful tip!

Initially when I was scrolling the documentation there were two location where we would need to place the Drivers one in <WINDOWS_USER_HOME>AppDataRoamingodioraclediuserlib

and the other will be the <ODI_HOME>/agent/oracledi/drivers Folder.

It confused me why two places and this is not feasible architecture , then I turned my attention towards the Global Architecture of Server and Client . So if my ODI Server is located on a UNIX server then I would be placing all my drivers under the agent/oracledi/drivers folder,since Agent will be located on my Server . While my Windows Client machine would be using the AppDataRoamingodioraclediuserlib folder for drivers ,as I would be using the Local Agent for reversing and if I in case I need to use respective RKM automatically Server Agent can be used to connect.

Moreover when we install the ODI Studio namely the ODI client the user wont have the agent folder and so they would have no confusion and would have to use AppDataRoamingodioraclediuserlib for drivers .

See you in the next post!

September 21, 2010
by Cezar Santos
7 Comments

Categories: Administration , Architecture , How to , ODI , Technology , Tips and Tricks

Tags: Agent , agent scheduling , ODI 11g , ODI Agent

11g Oracle Data Integrator – Standalone Agent but, and about the Windows Service???

Hi Guys,

Let me start a new discussion that, for sure, some ODI gurus will commnet! (Please, do it! hehehehehe)

In ODI 11g when I was doing the installation , I got a setup screen to setup the agent and I created the agent called MAIN_AGENT and I initially thought it’s a cool feature and thought that the Agent was created and automatically the ODIPARAMS and other files will get update .

Once the installation is done and the ODI 11g instance is started , I just triggered the agent and I got an Error . Oops I have not started the agent_MAIN_AGENT.bat and when I started it suddenly vanished , I had a feeling that some thing was wrong and something is not setup properly, so I went inside the agent_MAIN_AGENT.bat script and saw this

D:OracleMiddlewareOracle_ODI1oraclediagentbinagent.bat “-NAME=MAIN_AGENT” “-PORT=20910”

In short its just the file calling the agent.bat with the required parameters populated , so started digging around and looked at the odiparams .

The odiparams file was located where the standalone agent is located namely

<ODI_HOME>/Oracledi/agent/bin

I looked at it and saw that its pretty much the same but rather than the HSQL driver I was able to see the parameters for oracle. I started updating and provided the required parameters, at the ODI_MASTER_ENCODED_PASS I need to supply the encoded password , so I started typing in the command prompt

agent encode <password>

and ODI was trying to start the agent <password> ,so I knew some thing is changed again and I looked at the folder and I was able to see the encode.bat . Ah ! new funda . so I tried

encode <password >

Voila ! it worked Smile

After updating the required parameters , I was scrolling and looking at the other option , I saw new options ODI_CONNECTION_RETRY_COUNT , ODI_CONNECTION_RETRY_DELAY

and also the ODI_JAVA_HOME is set with the required jdk path which mean it no more reads from the JAVA_HOME or setting the environment variable ODI_JAVA_HOME. hmm !

Coming back to the above two option I was trying to find out what do they mean and the definition was defined as follows

I thought ok , let me play with these option some time later and let me restart the agent.

So I went to topology and created and updated the physical and logical agents as required .

Now I thought let me start the agent_MAIN_AGENT.bat and start again .

It started . Cool ,but then I thought how about if I want to set up again another standalone agent on 20911, so I went to Topology created the Physical and Logical and called it second agent.

After doing so I made the duplicate of the agent_MAIN_AGENT.bat and called it agent_second_agent.bat and update the required parameters

D:OracleMiddlewareOracle_ODI1oraclediagentbinagent.bat “-NAME=second_agent” “-PORT=20911”

Now I thought let me start that too and slowly the second_agent started too .

Now I wanted to make them as service so I was looking for agentservice.bat and I looked into the documentation and search for agent service but in vain .I even tried to bring in the Wrapper codes from ODI 10 g into 11g and make it as a service , i was successful in making the service but the conf files calls few java classes which could not be migrated since that would be complicated process.

Looks like we can only use the agentscheduler mode in windows but I can’t believe it… I will try to find out how to start it as service and publish as soon as I discovery!

See you soon!

September 15, 2010
by Cezar Santos
6 Comments

Categories: Administration , Architecture , How to , Knowledge Modules , Logic , ODI , Technology , Tips and Tricks

Tags: ODI 11g , ODI 11g Console , odiconsole , Oracle Data Integrator 11g , oraclediagent , Sunopsis

11g Oracle Data Integrator – Part 5/11g – odiConsole, the new Metada Navigator

Dear Friends,

In ODI 10g we used Metadata Navigator to login which acted as Web portal to Trigger and view the listed Scenarios and also Light weight designer .

In ODI 11g we have odiconsole which can be configured using the Oracle WebLogic.This post shows step by step configuration and initial setup to login into ODI console and setting up of Standalone agent to trigger scenarios from odiconsole.

[ Note – The Server name , Port No and other configuration used is for demonstration only and Please use the appropriate for your environment ]

Launch the Initial configuration Wizard of weblogic.

Select the Following options as shown below.

Give a meaningful domain name.

Provide the Administrator User Name and Password ,required to login into domain account.

Select the required JDK from the options

Provide the details of the MASTER Repository account

Provide the details of the WORK Repository account

Click Next and test the accounts . If you have error in this step , its means the account information is wrong , Please go back and do the required changes and once the account are correct ,you will get a Test successful message.

Click the Following as we need to do few settings changes.

If required change the Listener address to the required IPaddress or hostname you want to domain to be and similarly the Port No . For this post the default is used .

Configure the Machine Name with Node Manager listener address and Port

Assign the Server , although by default the odi_server1 is assigned but I have assigned Adminserver too so that I can access the odiconsole from 7001 port itself.

Make sure you check the option oraclediagent and odiconsole under Application

Check the JDBC Services and odiMasterRepository and odiWorkRepository

Confirm all the setting and options are correct and click on Create.

Finally ,you will get a Domain created with the Admin Server URL . Check mark Start Admin Server

Go to your Web Browser and type in the following address –http://localhost:7001/console/login/LoginForm.jsp

Change the local host and port to whatever is listed in the Admin Server URL above .

Once logged in , Under Environment – > click on Servers

You will able to see all the Server .

Click on Adminserver . You will be taken to a page as shown below , click Deployments and you will be able to see odiconsole , oraclediagent .

Expand odiconsole and you will be able to see odiconsole under Modules . Click that and you will be taken to another page

Go to Testing tab and expand odiconsole there .ah! finally we got our url . Click the url on default. http://localhost:7001/odiconsole

Once logged in you will be able to see the Master and Work Repository.

Use the required SUPERVISOR account to login

Sample Work Repository Console Screen

Sample Master Repository Console Screen

Here I am trying to trigger the Scenario EMPLOYEES from odiconsole and I get the following error.

To Resolve the above error , go your <ODI_HOME>COMMONBIN folder and type in the following command

D:OracleMiddleware_32bitOracle_ODI_32bitcommonbin >wlst.cmd

We need to connect to the above created weblogic connection ,provide the user name , password and url as shown in the image . [ Please find the scripts below the images ]

Once connected , create the credential with the SUPERVISOR user account and password details

Finally disconnect

The scripts is mentioned here , Please change accordingly.

connect(‘weblogic’,’odiexperts01′,’t3://localhost:7001′)
createCred(map=”oracle.odi.credmap”, key=”SUPERVISOR”, user=”SUPERVISOR”, password=”SUNOPSIS”, desc=”Key for Supervisor”)
disconnect()

Here the weblogic is the username , odiexperts01 is the password to login into weblogic and localhost:7001 is the listener address and port number

In the createCred , provide your SUPERVISOR user id and password

Learn more about the above scripts here in this Oracle Link –http://download.oracle.com/docs/cd/E14571_01/core.1111/e16453/install.htm#ODING412

I triggered the same Scenario again and this time the execution was successful.

Successfully executed in the Operator

Successful execution report in odiconsole.

We are trying to bring more post and blog on ODI 11g to make your transition to ODI 11g as smooth as possible. Smile

Keep looking for odiexperts for more tips , tricks on ODI 11g .

Thank you for visit us!

September 10, 2010
by Cezar Santos
8 Comments

Categories: Architecture , How to , ODI , Tips and Tricks

Tags: CFD , Common Fornat Designer , ODI , ODI 11g , ODI Experts , Oracle Data Integrator

Using one of ODI’s lesser-known secrets: the Common Format Designer (CFD) – by Craig Stewart

Hi Reader,

It’s a great pleasure starts a new “sector” from ODI Experts. It is our “Invited Experts” and we will start with one of the main ODI Guru in the world. Craig Stewart!!!

Thank you Craig!

Please, enjoy his post!

————————————————————————————————————————————————————————–

As I’m essentially in pre-sales, I like tools which I can use to rapidly do tasks which might otherwise take a lot of grind. Things like the Knowledge Modules in Oracle Data Integrator (ODI) are well known to take complex tasks and to make them simple – like integrating slowly changing dimensions: Simple with the IKM. One of the things which was introduced into ODI (actually into Sunopsis, before the acquisition by Oracle, was the Common Format Designer, In the latest release of ODI (11g) it is actually much better documented (even if it has not evolved much!) than in earlier versions – but what I’d like to show you briefly here is how it can be used to rapidly implement a new schema and to generate the interfaces to populate it.

To start with, let’s say I have a set of tables in my source database, in my case I’ll use MySQL. If I want to move that set of tables, with data across to Oracle, I’ll need to have a schema in Oracle where I can put the tables. In ODI’s Topology Navigator (see, I’m already using the new 11g terminology!) I need to define the Data Servers, the physical schemas and associate them with the relevant logical schemas. This is the point at which I’ll begin, I have a data model in ODI which has my “source” data defined in it:

I want to put this into my Oracle schema which I’ll call ORCL_TMP, so in Desgner, I create a new, empty data model in the Oracle technology for this schema:

Now I create a diagram, by using the Right-Mouse-Button on the Diagrams Node:

I must simply give this a name and any description, and naturally, the documentation…

Then, we go to the Diagram tab so we can start doing the business:

Now we drag & drop the datastores from our MySQL model, onto the new ORCL_TMP diagram, ODI will prompt us with a dialog box:

Which of course we know, and accept. The table is then added to the model, automatically using the metadata about datatype translations to construct the target table.

This will then show in the diagram:

We can then drag across additional datastores to make our model:

Note that in 11g, you are able to select multiple datastores and drag them across to the diagram. One drawback to this is that when referential integrity is defined, using this technique won’t get you the referential integrity, whereas if you drag one at a time, you will. It will give you all keys, indexes foreign keys as defined in the source system, and applicable in the target.

Now for some magic: if you right-mouse-button the Data Model, one of the optrions is to “Generate DDL”:

If we select this option, ODI will ask if we want to process tables not in the moidel (i.e. generate deletes to synchronize the two, or just to generate the delta code for the tables we have in the model. I’ll select “No” for this:

This will bring up a dialog where I can choose which of the differences I want synchronized:

If I select all, and press the OK, it will generate an ODI procedure for me in the Default Folder I entered when I defined the model:

I can press the “Play”/”Execute” button to get ODI to execute the generated code, which I can then follow in the Operator Navigator:

Often, you will get errors with the tables which contain DATE/DATETIME/TIMESTAMP fields as ODI is not always clever about translating them. In this instance I just edited the generated code to take out the TIMESTAMP(23) and changed it to TIMESTAMP. So now I have a fully created data model. The next step is to get some data moved in, so I need interfaces. ODI will of course remember where the data stores were dragged from, and to, so will generate for me an interface which just does a one-to-one mapping using the default knowledge modules:

I select the “Generate Interfaces In” and ODI brings up a dialog for the tables:

The required interfaces are then generated in that default directory:

I need to go into each one in order to execute it (If you try to execute without going in and saving it, you will get an error) You have to force a change, so that ODI can go through the process of saving it, which in turn makes it do some internal stuff, enabling execution. Then execute it.

Now you have data in your newly created data structure!

Craig Stewart

September 7, 2010
by Cezar Santos
16 Comments

Categories: Administration , Architecture , How to , Logic , ODI , Technology , Tips and Tricks

Tags: ODI , ODI 10G TO 11G , ODI 11g , ODI Adminstration , ODI Experts , ODI Metadata , ODI UPGRADE , Oracle Data Integrator , repository , Sunopsis

11g Oracle Data Integrator – Part 4/11g – Upgrading from 10g

Friends,

Following our 11g post series, a very important step is upgrade from 1og. Here is our thoughts.

The post tries to cover the method and issues related to it . I am sure this will give our viewers best idea and steps on how to move forward to newer version.

ODI 11g have an UPGRADE ASSISTANT that will help to upgrade the repository from ODI 10g to ODI 11g.

In case if you trying to upgrade by logging into the Master Repository and upgrade you will encounter the following error.

There is a Upgrade Assistant available in /bin folder . When I clicked it directly nothing happen so I knew , I need to call it using command prompt.

So I called the ua.bat being in windows and after a while an error . oops !! . hmm no idea what it is .?

I went to that path and saw that since I was using the Generic Version so I dint see any WIN 32 bit and saw only WIN64 , although I had OraInstaller.dll . I thought to myself is it a bug or can we upgrade only with 32 bit ODI version . I am sure that should not be the case , so I started looking at the certification (http://www.oracle.com/technetwork/middleware/data-integrator/odi-1111x-cert-matrix-163773.xls#’ODI ) and I found that for windows its only 32 bit which is supported.

To move on I changed the win32 to win64 and saved ua.bat I again got the error Smile , I thought let me go for win32 ODI installation .

Moved to 32 bit ODI Installation

Launched the Upgrade Assistant from <ODI_HOME>/bin/ua.bat

I am presently upgrading only the ODI repositories and not other component.

The third options – Check that Upgrade Occurs only to Cloned Repositories , caught my attention. Still I just clicked Next and moved ahead.

In this screen I checked Database Backup completed , so I thought to myself , looks like I need to do something or I am missing something , so I searched for the Upgrade from ODI 10g to ODI 11g documentation and I found this

http://download.oracle.com/docs/cd/E14571_01/upgrade.1111/e12642/tasklist.htm#CIHDDJAH

Here they have mentioned to do the cloning or backup of the Repository of Master and Work under the Master . Rather than creating the scripts I used a simple process through SQL Developer –> Database Copy

Before doing so Create the required ODI 11g Master and Work Repository schema and assign the required tablespaces to them .Once done using the Database copy , copy the Tables and the data.

In Next step , SQL Developer lists all the steps and finally click Finish to start the copy Process. Once completed , you will able to find all the Master Repository tables created in the next Master Repository schema with the required data in it.

Keep repeating the Process for all the Work Repository under that above Master Repository. Once completed Click Next in the Upgrade Assistant to move to Step 4.

Here in Step 4 list all the details required and provide the Master Repository just created above and provide the SUPERVISOR username and Password used in ODI 10g Repository.

Once the above screen is successful , Upgrade Assistant will lists all the Work Repository under the Master . Now provide the username and password of the schema of Cloned ODI 11g schema .

[ Note in case , any of the above mentioned steps is wrong or incorrect you will get an error screen as shows below

Use the First or the Third option but never the second option as there might be chances of corruption causing issues or improper Repository setup ]

Once Work Repository schema authentication is done , Click Upgrade to start the Upgradation Process.

Finally the required tables , and LKM are upgraded and the logs can be visited by click on the link mentioned in the Upgrade Assistant .

Now login into the ODI Studio and provide the Master Repository detail and select the appropriate Work Repository and log in , you don’t need to provide the internal ID since it will use the Internal Id specified during ODI 10g setup .

As you can see all the Interface, Model , Topology, agents are all created but you would need to create the appropriate Standalone Agent script for the migrated Agent.

I wanted to test the migration so I executed an Interface and it worked.

I also had another EWR setup under my Master Repository , I logged inside it too.

I can see all the required Scenarios, topology and other ODI objects.

This are some of the simple Upgrade process from ODI 10g DWR and EWR to ODI 11g .

Hope to hear from you all how was your experience with the Upgrade Assistant and what kind of errors you encountered .

Keep looking into odiexperts for more ODI 11g series Posts.