Oracle Data Integrator 12c is finally available. Now the hard part begins – learning how to use it. Some things to consider as you go through the installation of ODI 12c are:

  1. Will your ODI 11g objects work in ODI 12c?
  2. Should you upgrade your existing ODI 11g environment or install a brand new environment?
  3. What is the best way to learn about the new features of ODI 12c?
  4. And you probably have a hundred other questions that need to be answered.
In this tutorial, my goal is to install ODI Studio 12c on my laptop and configure the repositories in an Oracle Database. 

Step 1: Download ODI 12c

Download the installation media for ODI 12c from Oracle.

Step 2: Unzip the Installation Media

Unzip the installation media into a directory. My old school way of naming directories is to avoid spaces in the directory name. I also use as short of a path to the installation media as possible.

Step 3: Design a Topology

ODI 12c consists of several components – ODI Studio, WebLogic (Agent, Console), Standalone Agent. In a production environment, these components will be installed on different computers. For my tutorial, I plan to install ODI Studio on the same computer as the Oracle Database. If time allows then I will also install a standalone agent on the same computer.

Step 4: Install Java SDK 7 

You need to install the JDK before you can install ODI 12c. Why? The ODI 12c is a jar file that requires the SDK to run. Install Java SDK 7 on each computer where you will need to install an ODI component. This example will install everything on a single computer. 

Step 5: Configure a Database

A database serves two purposes. The first purpose is to hold the ODI repositories that ODI uses to store metadata (aka configuration information and source code). The second is to hold data used for data integration flows (source and target).
There are two types of repositories that ODI uses – a master repository and a work repository. The space requirement for a master repository is 200 MB. The space requirement for a work repository is 400 MB. Make sure that you have enough hard disk space to hold the repositories you need. Consider one master repository, a development work repository, a test work repository, a hot fix work repository and a production work repository. This configuration supports a basic SDLC workflow.
You should be safe with Oracle Database 11g or Oracle Database 12c. For any other database you will need to check the Fusion Middleware 12c certification matrix to see if it is compatible with Fusion Middleware 12c.
Once your database is ready, make note of the credentials for the sys user or any sysdba account. You will need to have access to the sys schema when you create the ODI repositories.

  1. Set the database character set to AL32UTF8
  2. SHARED_POOL_SIZE >= 144 MB
  3. SGA_MAX_SIZE >= 144 MB
  4. DB_BLOCK_SIZE = 8 KB
  5. SESSION_CACHED_CURSORS >= 100
  6. PROCESSES >= 500
  7. OPEN_CURSORS >= 800
  8. DB_FILES >= 600
  9. Database JVM is enabled
Check database parameter settings by executing the following query as SYS:

SELECT *
FROM v$parameter2 p
WHERE p.name IN (
‘shared_pool_size’,
‘sga_max_size’,
‘db_block_size’,
‘session_cached_cursors’,
‘processes’,
‘open_cursors’,
‘db_files’
)
;

Check the database features that have been installed by executing the following query as SYS:

select * from all_registry_banners;

Look for the following entry:

JServer JAVA Virtual Machine Release 11.2.0.1.0 – Development

Step 6: Configure ODI Repositories

ODI repositories reside in the database that you configured in the previous step. There are two methods of creating repositories – using the Oracle Repository Creation Utility (RCU) or using ODI Studio. Both methods have the same result.

I use ODI Studio to create/populate the repositories after the installation of ODI 12c is complete.

Step 7: Install ODI 12c

Open a command prompt (on Windows 7) and launch the ODI 12c installer.
C:Downloadsoracleodi12cinstall_imagesofm_odi_generic_12.1.2.0.0_disk1_1of1>”C:Program FilesJavajdk1.7.0binjava” -jar odi_121200.jar
Screenshots of the installation process are as follows…

ODI 12c Installer Splash Page
ODI 12c Installer Welcome Page
ODI 12c Installer Home Selector
ODI 12c Installer Installation Type Selection

ODI 12c Installer Prerequisite Checks

ODI 12c Installer Interview Summary

ODI 12c Installer Beginning of Installation at 0 percent
ODI 12c Installer Ending of Installation at 100 percent
ODI 12c Installer Installation Report

Step 8: Launch ODI 12c

For some reason, the ODI 12c installer did not create any shortcuts for me. If this happens to you then it is important to know where to find the ODI Studio 12c executable. My ODI Studio executable is located here:
C:OracleMiddlewareOracle_Homeodistudioodi64.exe
Create a shortcut if one was not created automatically for you. This will make it easier to launch ODI Studio the next time you need it.
ODI Studio will ask you which JDK you would like to use. If you ever wish to change the selected JDK then  there is a configuration file that you can modify at a later time. I am using the 64-bit version and Java JDK 1.7. On Windows 7 the configuration file,odi.conf, is located in theC:OracleMiddlewareOracle_Homeodistudiobin directory.

Full disclosure: Since the environment that I am installing is for learning purposes only I took a couple of calculated shortcuts. First, my JDK is 1.7.0 and just shy of the build 10 JDK that is recommended. To save on time I did not install a newer JDK than what was already installed on my laptop. Second, my database character set is not AL32UTF8. If you are installing in a production environment then please follow the directions exactly and do not take any shortcuts. 

Launch ODI Studio. During startup I was prompted to transfer settings from my ODI Studio 11g installation – which I did. If you do not have a previous installation of ODI Studio on your workstation then I do not expect that you will be prompted to transfer settings.

We are getting closer. Launching ODI Studio 12c is the first measure of success. But, we still need to create and connect to a master repository and a work repository.

Create a master work repository using ODI Studio. You can also use the Repository Creation Utility (RCU). The RCU is a middleware repository creation tool that is used to create repositories for many different middleware applications such as ODI and OBIEE.

NOTE: The RCU is installed in the following directory.

C:OracleMiddlewareOracle_Homeoracle_commonbin

I created a new database account named ODI12C_MASTER_REP before launching the Master Repository Creation Wizard. ODI12C_MASTER_REP will house the ODI master repository.

To create a new master work repository, select File > New… > Master Repository Creation Wizard and click the OK button.

Enter values for the fields and click the Next button as prompted.

And now we need to create one or more work repositories. Each repository needs its own schema in the database. Create the database accounts before creating the work repositories. For learning purposes, a single work repository is sufficient. Outside of a tutorial environment you will need development, test, hot fix and production work repositories.
To create a work repository you need to connect to the master repository first. 
Create a new ODI repository login by selecting File > New… > Create a New ODI Repository Login.
Create a profile name (mine is named ODI12C MASTER) and enter the credentials for the master repository that you defined when you created the master repository. The profile name is important only for ease of reference. I like to name mine based on the repository that I will connect to. For example, DEVELOPMENT, TEST, HOTFIX, PRODUCTION, MASTER, etc.
Test the connection. If successful then click the OK button. If not successful then play around with the credentials and the JDBC URL until you get it right.
Click the Connect to Repository… link to connect to the master repository. You may need to enter the wallet password to proceed.
Once connected to the master repository, select the Topology tab and expand the Repositories accordion.

Right-click on Work Repositories and select the New Work Repository option. Database connection details will be picked up from the master repository connection details. While the different repositories can reside in different databases, they typically reside in the same database in different schemas. My work repository schema is named ODI12C_WORK_REP_DEV. I created this database account prior to launching the New Work Repository Wizard.

Test the connection. Click the Next button. On the next page you name your work repository and select the repository type – DEVELOPMENT or EXECUTION. In this case, create a DEVELOPMENT work repository. A DEVELOPMENT work repository is the type of repository used to write code, model data stores and other stuff. An EXECUTION work repository is a repository where you run compiled code.

The creation process begins.

Once complete, you are prompted to create a login for the repository. I was also prompted to enter my wallet password.

Make sure that you can see the new work repository in the Repositories accordion pane.

Only one more step…to connect to the new work repository. Select ODI > Disconnect to disconnect from the master repository.
Click on the Connect to Repository… link. Select the new work repository and connect to it.
Once connected to the work repository, select the Designer tab. You are now ready to use ODI Studio 12c and create projects and models. Have fun!

What is DDL sync and how to do it?

DDL Sync is one of the most important step as part of code migration to new environment.
So here we will discuss

  • why do we perform DDL sync?
  • What are the steps involved in this process? and
  • How can we do this?



DDL Sync
DDL sync is used to have a sync between your Siebel Repositories & physical schema in Oracle DB. ddlsync compares the logical schema (schema definitions in your repository) with your physical schema (i.e. physical db table/column definitions) and applies the differential changes on the physical schema so that they are both in sync 

Steps involved
DDL Sync is 3 steps process which includes

  • ddldict to export customer schema definitions from the repository into schema.ddl file .
  • ddlimp to apply the schema definitions in schema.ddl to the physical schema ignoring error .
  • ddlimp to apply the schema definitions in schema.ddl and stop on error to ensure that logical and physical DB are in sync. 
How to perform DDL Sync
DDL sync can be done through Wizard which can be invoked by 

[ ] 1 – Install Database


[ ] 2 – Upgrade Database


[ ] 3 – Apply Additive Schema Changes


[ ] 4 – Import/Export Repository

[ ] 5 – Migrate Repository

[X] 6 – Run Database Utilities

or we can do DDL sync with separate commands.
Navigate to bin directory in siebsrvr.
ddldict /u sadmin /p ###### /c Enterprise_DSN /d siebel /f /#####/81/siebsrvr/schema.ddl /e y /a y /l /######/81/siebsrvr/log/sch_dict.log /n “Siebel Repository” /t dcir

Above command will generate Schema.ddl at the specified location.Now we have to apply the schema changes to Physical DB.

ddlimp /u SIEBEL /p ###### /c Enterprise_DSN /g SSE_ROLE /f /#####/81/siebsrvr/schema.ddl /e n /B SIEBELDATA /X SIEBELINDEX /R Y /W Y /s Y /l /#####/81/siebsrvr/log/ddlsync/output/ddlsync.log