Data and applications integration is always a technical challenge for most of the companies. All data integration tool ensures that information is timely, accurate, and consistent across complex systems.
Oracle Data Integrator (ODI) is different than traditional data integration tools in the sense it integrates data in a different orders.
ODI is known as en E-LT because it Extracts data from the source system and Loads and Transforms data into the target system while traditional tools transform the data in an intermediate layer (Staging) between the Source (where the data is Extract) and the Target (where the data is Loaded).
Oracle Data Integrator Cloud removes the complexity in creating real-time operation reporting schema enabling a novice user to perform the creation, initial load, and real time synchronization in just a few clicks. Usually, these types of data integration required assistance from ELT developers and Database administrators.
The goal of this blog is to show you how install ODI in the Cloud.
Sign up for a free credit promotion or purchase an Oracle Cloud subscription and your sign-in credentials.
There are two types of ODI components:
- Oracle Data Integrator Studio used by Administrators, Developers and Operators for administering the infrastructure (security and topology), reverse-engineering the metadata, developing projects, scheduling, operating and monitoring executions.
- Oracle Data Integrator Console used (in read only) to access the repositories, peform topology configuration and production operations.
The first step is to create an Oracle Database Cloud Service Instance (Database As A Service)
In the top right menu, go to service user console:
Click on Service User Console
Click On Oracle Database Cloud Service
Click on Go to Console
Click on Create Instance
Choose Instance Name and Database Version and Click Next
Choose Database Name, Administration Password (leave all others default value) and Click Next
Create an SSH key, public and private key
ssh-keygen -t rsa -N "" -b "2048" -C "key comment" -f /drives/c/Users/lfe/Documents/dbi/technique/blog/ODI_cloud/id_rsa WARNING: You should not store anything in MobaXterm HOME directoy (/home/mobaxterm): with your current settings, this folder is not "persistent", so it will be cleared at each MobaXterm restart. If you want to set a "persistent" HOME directory which will not be cleared at each MobaXterm startup, go to MobaXterm settings window and choose a folder in which to store MobaXterm home files. Generating public/private rsa key pair. Your identification has been saved in /drives/c/Users/lfe/Documents/dbi/technique/blog/ODI_cloud/id_rsa. Your public key has been saved in /drives/c/Users/lfe/Documents/dbi/technique/blog/ODI_cloud/id_rsa.pub. The key fingerprint is: SHA256:S0Z6qE+BSdANABj3RDOdvEq1hxF5ts3jA4MbG8lGGv4 key comment The key's randomart image is: +---[RSA 2048]----+ |+o++*=.+ | |.. +ooX o | | .o* X.+ | | [email protected]+= + | | .o++*S+ . | | ..E= .o | | . . . . | | o | | . | +----[SHA256]-----+
Two keys are created :
- public key : id_rsa.pub
- private key : id_rsa
Click on “SSHPublic Key Files” and Upload the Public key files just created.
Click on Create
The Instance Creation fails:
Let’s go back to the previous screen to replace Oracle Database 19c by Oracle Database 12c Release 2
Now the instance can be created:
The instance is being created…
Wait some minutes till the instance is created (Status Ready)
Get the Public IP address and let’s add the private key created previously into Mobaxterm
Now we can connect to the database :
[[email protected] ~]$ sudo su - oracle [[email protected] ~]$ sudo su - oracle Last login: Fri Mar 26 10:58:13 UTC 2021 [[email protected] ~]$ [[email protected] ~]$ ps -ef | grep pmon oracle 16565 1 0 10:53 ? 00:00:00 ora_pmon_ODI oracle 23105 23056 0 10:58 pts/0 00:00:00 grep --color=auto pmon [[email protected] ~]$ sqlplus / as sysdba SQL*Plus: Release 220.127.116.11.0 - Production on Fri Mar 26 10:59:00 2021 Version 18.104.22.168.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 12c EE High Perf Release 22.214.171.124.0 - Production Version 126.96.36.199.0 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ODI READ WRITE NO SQL> alter session set container=ODI; Session altered.
The second step is to provisioning an Oracle Java Cloud Instance
The goal is to configure the Oracle Java Cloud Instance on which we want to install and run Oracle Data Integrator Cloud.
Click On Java Services
Click on Create Instance
Enter the service details:
- Service Level — From the dropdown, select Oracle Java Cloud Service for Fusion Middleware – Oracle Data Integrator
- Software Release — Select Oracle Weblogic Server 12c (188.8.131.52)
Choose the Compute Shape (VM.Standard – 2.0 OCPU, 30 GB of Ram).
Choose the public key created previously
Choose the Instance ODI created previously
Enter SYS credentials
For our case do no configure backup. Of course in a production environment, you have always to set Backup Destination.
Wait a moment for the Instance creation…
The Oracle Java Cloud Service Instance (JAAS) is now created and associated with the Oracle Database Cloud Service Instance (DBAAS).
Configure the SSH connection from MobaXterm with the same private key created previously for the database instance machine
The next step is to install Oracle Data Integrator
The good news with the Cloud is that Oracle WebLogic Server is already installed in your Java Cloud Service instance, there is no need to install the Fusion Middleware Infrastructure. It is already there!!!
To interact with the graphical interface into the Java Cloud Server Instance, we have to configure VCN viewer.
Turn Off the lock screen and start the VNC Server
[[email protected] upperstack]$ gconftool-2 -s -t bool /apps/gnome-screensaver/lock_enabled false [[email protected] upperstack]$ vncserver -nolisten local -geometry 1680x1050 You will require a password to access your desktops. Password: Verify: Would you like to enter a view-only password (y/n)? y Password: Verify: xauth: file /u01/app/oracle/tools/home/oracle/.Xauthority does not exist New 'oditest-wls-1:1 (oracle)' desktop is oditest-wls-1:1 Creating default startup script /u01/app/oracle/tools/home/oracle/.vnc/xstartup Creating default config /u01/app/oracle/tools/home/oracle/.vnc/config Starting applications specified in /u01/app/oracle/tools/home/oracle/.vnc/xstartup Log file is /u01/app/oracle/tools/home/oracle/.vnc/oditest-wls-1:1.log
Open a new local terminal to create an SSH tunnel on the VNC server port on the Administration Server VM
ssh -i C:/Users/lfe/Documents/dbi/technique/blog/ODI_cloud/id_rsa -L 5901:127.0.0.1:5901 [email protected] -N
To have the graphical access for the Oracle Java Cloud Server instance,use the VNC Viewer on the local machine to connect to localhost:5901. You will be prompted for the password you entered in the previous step.
Start VNC Viewer locally
From vncviewer, navigate to the /u01/zips/upperstack directory and install ODI
[[email protected] upperstack]$ cd /u01/zips/upperstack/ [[email protected] upperstack]$ ls -ltr total 4323160 -r-xr-xr-x. 1 oracle oracle 2027199017 Aug 22 2017 fmw_184.108.40.206.0_odi_generic.jar -r-xr-xr-x. 1 oracle oracle 186404495 Aug 22 2017 fmw_220.127.116.11.0_odi_generic2.jar -rwxr-xr-x. 1 oracle oracle 2213306744 Dec 17 2019 ODI.zip [[email protected] upperstack]$ unzip ODI.zip Archive: ODI.zip inflating: fmw_18.104.22.168.0_odi_generic.jar inflating: fmw_22.214.171.124.0_odi_generic2.jar [[email protected] upperstack]$ java -jar fmw_126.96.36.199.0_odi_generic.jar Launcher log file is /tmp/OraInstall2021-03-19_09-14-31PM/launcher2021-03-19_09-14-31PM.log. Extracting the installer . . . . . . . . . Done Checking if CPU speed is above 300 MHz. Actual 1995.309 MHz Passed Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed Checking swap space: must be greater than 512 MB. Actual 8191 MB Passed Checking if this platform requires a 64-bit JVM. Actual 64 Passed (64-bit not required) Checking temp space: must be greater than 300 MB. Actual 26195 MB Passed Preparing to launch the Oracle Universal Installer from /tmp/OraInstall2021-03-19_09-14-31PM
The installation starts
Skip Auto Updates and click Next
Choose Enterprise Edition and Click Next
Now, let’s create the Oracle Data Integrator Repository
[[email protected] bin]$ cd /u01/app/oracle/middleware/oracle_common/bin/ [[email protected] bin]$ ./rcu
Choose your PDB database and SYS credentials (DBaaS) created previously and click Next
Check Oracle Data Integrator, leave by default all option checked and click Next
Enter password for main and auxiliary schemas and click Next
Click Create Tablespaces and Schemas DEV1_*
Update the Java Cloud Instance Domain
est-wls-1 bin]$ cd /u01/app/oracle/tools/home/oracle/Oracle/Middleware/Oracle_Home/oracle_common/common/ [[email protected] bin]$ ./config.sh
Select Oracle Data Integrator Components and click Next
Leave all default value checked and click Next for all screens
The last task is to start ODI Studio and create ODI Agents
[[email protected] studio]$ cd /u01/app/oracle/tools/home/oracle/Oracle/Middleware/Oracle_Home/odi/studio/ [[email protected] studio]$ ./odi.sh . . .
Create your connection, connect to the Work Repository
Go to Topology/Physical Architecture/Agent (right click)/New Agent and create the Physical Agent
Go to Topology/Logical Architecture/Agent (right click)/New Agent and create the Logical Agent
Now you are ready to create your first mapping from source database to target database.
To install Oracle Data Integrator into Oracle Cloud Infrastructure, we need to :
- To access to OCI Generation 1 – OCI Classic (Menu Profile/Service User Console), the OCI generation 2 is reachable from the top left menu
- Create a database instance (DBaaS) provisioning a machine with Oracle Database already installed used to store the ODI repository.
- Create a Java instance (JaaS) provisioning a machine with Oracle Fusion Middleware Infrastructure already installed,
- Install ODI into the JaaS machine.