Oracle Data Integrator can be used to transform data from source to target databases.
Oracle Data Integrator is fully integrated with Oracle Golden Gate, Oracle Database and with E-TL architecture, that is Extract data on source and Transform/Load data on target server.
ODI supports data conversion from/to different technologies as well.
In this blog I will describe the process of installing ODI and creating the master and work repositories. At the end of this blog you should be able of having an instance of Oracle Data Integrator installed and configured and ready to transform the data.
- Oracle VirtualBox 5.0.2
- Oracle Linux 6.7
- Oracle Database 12c – 22.214.171.124
- Oracle Data Integrator 12c – 126.96.36.199
The source database is srcdb and the target database is stgdb.
B.- Preparing for Installation of Oracle Data Integrator
We have to create a database instance which will be used as a repository for ODI. The ODI repository is composed of a master and work repositories that contain information such as: topology, metadata, data models, projects, etc.
1.- We then create an Oracle database instance on server sourcedb. Log on source server as oracle and run database configuration assistant dbca:
[oracle@srcdb ~]$ cd $ORACLE_HOME/bin
Select Create Database and press Next.
Choose Advanced Mode and press Next.
Select General Purpose… and press Next.
Fill in Global Database Name and SID then press Next.
Choose the same listener as for the srcdb database and press Next.
For the purpose of this blog I do not enable archiving, but consider enabling archiving in production environments. Press Next.
Here I have selected default values except of Character set which I chose Unicode – AL32UTF8:
2.- Download the Oracle Data Integrator software from http://otn.oracle.com.
Choose Downloads à Middleware à Data Integrator and Suite
We then download the Oracle Data Integrator 12cR1 (188.8.131.52).
We have then downloaded the file: fmw_184.108.40.206.0_odi_Disk1_1of1.zip
We unzip the file above and we see the folder:
that contains the file: fmw_220.127.116.11.0_odi.jar
I will install ODI on my virtual pc Windows 7 x86_64, but the repository will be installed on the Linux server database created on step1: odirep.
I have chosen a virtual Windows 7 machine, since ODI is not supported on Windows 8. You may of course try installing ODI on Linux as well.
3.- Before installing Oracle Data Integrator we check we have the right version of jdk installed.
The minimum required version of jdk for ODI is 7. So the java version in my computer is ok. I recommend installing java jdk 7 as well. Later on the ODI studio will claim using jdk 7.
C.- Installing Oracle Data Integrator 12cR1
1.- We open a cmd window as administrator to run the jar file we downloaded in Step B-2.
2.- Then we start the installation of ODI on Windows:
Choose Oracle Home and press Next.
Select Standalone Installation and press Next.
Be sure the requirements have status Passed and press Next.
Review the summary and press Install.
We wait until the installation is finished:
D.- Creating master and work repositories
At this point, the software of Oracle Data Integrator has been succesfully installed on my virtual Windows 7 computer.
In order to complete the ODI setup we have to create the master and work repositories on database odirep.
The repositories are necessary to store the metadata associated with the environment and ODI objects such as: projects, diagrams, mappings, data stores, etc.
1.- Preparing the repository database. We create a tablespace and then some repository database users:
Log on odirep database as sys and run:
create tablespace odi_repo_data logging
size 512M reuse autoextend on next 256M maxsize unlimited
extent management local
segment space management auto;
create user odi_repo identified by oracle
default tablespace odi_repo_data
temporary tablespace temp;
grant resource, connect to odi_repo;
grant unlimited tablespace to odi_repo;
2.- Creating the master repository.
We create the master repository in ODI Studio.
I log on my Windows 7 computer and start ODI Studio from the Start Menu:
As I started ODI Studio it complained about the java version 8. I have to install jdk 7 and then I finally managed to start ODI Studio.
In ODI studio click on the icon just below File on the menu and select Create a New Master Repository:
In the repository connection screen we fill in the following data:
- JDBC URL: jdbc:oracle:thin:@192.168.56.102:1521:odirep
- User: ODI_REPO (the user created in step 1 above)
Of course if you are trying this for yourself you have to change the server name, port number and database name.
Press the button Test Connection and verify the connection to repository database is ok.
Fill in password for SUPERVISOR and press Next.
After the repository is succesfully created we create a connection.
3.- Connecting to master repository.
On ODI studio click on the link Connect to Repository…
Then click on the green plus sign icon and fill in the fields as shown below:
Press the button test to verify the connection is ok and press OK.
4.- Creating a work repository.
Start ODI studio and connect to the master repository created in step 2.
Select the tab Topology and expand the Repositories accordion.
Right click on Work Repositories and create a new work repository.
Press the button Test Connection. After succesful connection press Next.
I entered password oracle, the same as Supervisor. Press Finish.
At this point, the Oracle Data Integrator is installed and ready to use.