Antonio Romero

Subscribe to Antonio Romero feed
Getting the most out of OWB and the Oracle Database
Updated: 16 hours 5 min ago

OWB-TO-ODI MIGRATION PATCH FOR OWB 11.2.0.4 TO ODI 12.2.1 released

Fri, 2015-12-04 12:52
 The OWB to ODI Migration now supports migration from OWB version 11.2.0.4 to ODI 12.2.1
 It is available as "Patch 21977765 : OWB-TO-ODI MIGRATION PATCH (MP3) FOR OWB 11.2.0.4 TO ODI 12.2.1" and can be downloaded from the support website.

 This patch(21977765) only supports migration from Linux 64-bit and Windows 64-bit standalone OWB 11.2.0.4 to ODI 12.2.1.
 For Migrating to ODI 12.1.2 and ODI 12.1.3 please use patch no 18537208

 More information about the migration utility is here
 http://docs.oracle.com/middleware/1221/odi/install-migrate-owb-odi/toc.htm

ODI 12c - Migrating from OWB to ODI - PLSQL Procedures

Thu, 2014-09-25 12:42

 

Check the blog post below for supporting PLSQL procedures within ODI and how you can get 100s of percentage fold improvements; 

https://blogs.oracle.com/dataintegration/entry/odi_12c_migrating_from_owb

Cheers
David 

OWB to ODI 12c Migration in action

Wed, 2014-08-06 12:00

The OWB to ODI 12c migration utility provides an easy to use on-ramp to Oracle's strategic data integration tool. The utility was designed and built by the same development group that produced OWB and ODI. 

Here's a screenshot from the recording below showing a project in OWB and what it looks like in ODI 12c;


There is a useful webcast that you can play and watch the migration utility in action. It takes an OWB implementation and uses the migration utility to move into ODI 12c.

http://oracleconferencing.webex.com/oracleconferencing/ldr.php?RCID=df8729e0c7628dde638847d9511f6b46

It's worth having a read of the following OTN article from Stewart Bryson which gives an overview of the capabilities and options OWB customers have moving forward.
http://www.oracle.com/technetwork/articles/datawarehouse/bryson-owb-to-odi-2130001.html

Check it out and see what you think!

OWB to ODI Migration Utility now available for OWB 11.2.0.3 +CP3 Linux and Windows 64-bit standalone installation.

Tue, 2014-01-14 16:33

The OWB to ODI Migration now supports migration from OWB version 11.2.0.3+CP3 to ODI 12c.

It is available as "Patch 17224695: OWB-ODI MIGRATION PATCH FOR 11.2.0.3 64BIT STANDALONE INSTALL" and can be downloaded from the support website.

This patch(17224695) supports migration from Linux 64-bit and Windows 64-bit standalone OWB 11.2.0.3 + CP3( Patch 16568042) to ODI 12.1.2.0.0 + Patch 17053768.

More information about the migration utility is here http://docs.oracle.com/middleware/1212/odi/ODIMG/index.html

OWB to ODI Migration Utility released for Windows 64 bit.

Wed, 2013-12-11 15:13

The OWB to ODI Migration Utility is now available for Windows 64-bit platforms. It can be downloaded from the Oracle support site. It is available as Patch number17830453. It needs to be applied on top of  a OWB 11.2.0.4 standalone install.

More information about the Migration Utility is available here.

OWB to ODI Migration Utility Webcast - Thu 12th December

Wed, 2013-12-11 13:33

On Thursday 12th December there is a webcast on the OWB to ODI 12c migration utility, there will be a demo and drill down into the utility. Check the meeting URL here - its at 10am PST on 12th December. Check out the blog post here on getting the utility. Good chance to get the inside scoop on the utility and ask questions to the PM and development team.


Planning the journey from Oracle Warehouse Builder to Oracle Data Integrator

Mon, 2013-12-02 10:31

Check out the blog post here on the OWB integration and migration capabilities in ODI 12c. Julien and myself give an overview of what you can see and expect in the recent release. This is an overview which will give you some insights and pointers to the documentation. 

OWB to ODI Migration Utility released.

Fri, 2013-11-15 14:30

The much awaited OWB to ODI Migration utility has been released.

The migration utility is a command-line tool that enables you to migrate design-time metadata from OWB 11.2.0.4 to ODI 12c. The migration utility is provided in patch number 17547241 for your OWB 11gR2 (11.2.0.4) installation.

Migration is supported on Linux 64-bit x86 systems only. Before migrating, ensure that the following requirements are met:

  • OWB 11.2.0.4 installed (OWB 11.2.0.4 plus patch number 17547241)

  • ODI 12.1.2.0.0 installed (ODI 12.1.2.0.0 plus patch number 17053768)

ODI is Oracle's strategic product for heterogeneous data integration. Because many Oracle Database customers have significant investment in OWB, Oracle supports a phased migration from OWB 11gR2 (11.2.0.4) to ODI 12c (12.1.2).

  • ODI 12c supports an easier mapping between OWB 11gR2 concepts and objects and their ODI 12c counterparts. A migration utility is provided that automatically translates many OWB objects and mappings into their ODI equivalents. More information about the migration utility is here

http://docs.oracle.com/middleware/1212/odi/ODIMG/index.html

In addition to enabling the migration, the ODI 12c patch also comes with
new  components such as Pivot, Unpivot, Subquery filter  and Table Functions.
They're documented in the following appendix:
http://docs.oracle.com/middleware/1212/odi/ODIDG/app_12c_patch.htm


OWB - 11.2.0.4 Windows standalone client released

Wed, 2013-11-06 13:37

The 11.2.0.4 release of OWB containing the 32 bit and 64 bit standalone Windows client is released today, I had previously blogged about the Linux standalone client here. Big thanks to Anil for spearheading that, another milestone on the Data Integration roadmap.

Below are the patch numbers;

  • 17743124 - OWB 11.2.0.4 STANDALONE CLIENT FOR Windows 64 BIT
  • 17743119 - OWB 11.2.0.4 STANDALONE CLIENT FOR Windows 32 BIT

This is the terminal release of OWB and customer bugs will be resolved on top of this release. We are excited to share information on the Oracle Data Integration 12c release in our upcoming launch video webcast on November 12th.

ODI 12c is GA

Thu, 2013-10-17 10:53

Great news today, ODI 12c is GA! See the Welcome Oracle Data Integration 12c posting from Irem. Great to see the hard work from our development teams come to light. Looking forward to MUCH MUCH MORE TOO!

Happy Birthday ODI 12c!


OWB - Making the Move to ODI

Thu, 2013-09-26 12:10

Yesterday's session on Making the Move to Oracle Data Integrator at OOW13 after the America's Cup finale was a great way to round off the day (the garlic at the Stinking Rose was even better but we will stick with the technology here), the session was hosted by Julien Testut from product management and was under the Oracle safe harbor statement below. There was an overview by Jyotin Gautam the ODI development director and then some demonstrations from Oracle partners Stewart Bryson and Holger Friedrich.

The session lay down the options for OWB customers moving forward; 

  • execute and monitor OWB jobs within ODI
  • migrate all or parts of OWB mappings with the migration utility
  • develop new ETL in ODI

...or a mixture of all of these. Stewart demonstrated the execution of OWB jobs from within ODI and highlighted the immediate value-add of doing this. ODI load plans provide a restartability capability that is a key part of an ETL infrastructure (this had to be manually constructed in OWB). Its great hearing the genuine excitement and enthusiasm Stewart presents with and using words like 'beautiful' are icing on the cake (I can still hear his comments a few years ago of 'all I want is a mapper' ringing in my ears). Holger demonstrated the migration utility that migrates the OWB design metadata into ODI, migrating a complex mapping with many operators and multiple targets into a ODI 12c mapping and like many was surprised with the simplicity of the solution and the maintainability of the new design in ODI.

There was a bunch of tweeting going on in the session and Mark Rittman quick succinctly summarized the session below;

It was a well attended session with a mix of OWB and ODI customers and customers looking to move to ODI in general. The guys in development were happy to see this finally getting their. Sure and steadfast ;-)

The session was under Oracle's safe harbor statement...'The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.'

OWB - 11.2.0.4 Documentation Updates for 12c database

Wed, 2013-09-04 11:01
The OWB 11.2.0.4 documentation is updated on OTN with information on operating with 12c databases including install/upgrade and pluggable databases. It's worth starting with the release notes for further information. You will need either 11.2.0.3 plus CP2 (CP3 advised) or 11.2.0.4 of OWB to operate with 12c database.

OWB - 11.2.0.4 standalone client released

Fri, 2013-08-30 16:29

The 11.2.0.4 release of OWB containing the 32 bit and 64 bit clients is released today. Big thanks to Anil for spearheading that, another milestone on the Data Integration roadmap.

Below are the patch numbers;

  • 17389934 - OWB 11.2.0.4 STANDALONE CLIENT FOR LINUX X86 64 BIT
  • 17389949 - OWB 11.2.0.4 STANDALONE CLIENT FOR LINUX X86 32 BIT

The windows releases will come in due course. This is the terminal release of OWB and customer bugs will be resolved on top of this release.

Sure and Stedfast has been a steady motto through my life, it came from way back in my old Boys Brigade days back in Scotland. Working in Oracle I have always reflected back on that over the years, can still hear 'Will your anchor hold in the storms of life' ringing in my ear. The ride through different development organizations from Oracle Tools, through Oracle Database and Oracle Middleware groups, from buildings 200, to 400 to 100, 7th floor, 9th floor, 5th floor, countless acquisitions and integrations. Constant change in some aspects, but zeroes and ones remain zeroes and ones, for our group the direction and goals were well understood. Whilst its been quiet on the OWB blogging front, the data integration development organization has been busy, very busy releasing versions of OWB and ODI over the past few years and building the 12c release.

So to 12c... our data integration product roadmap has been a strong focal point in our development over the past few years and that's what we have been using to focus our energy and and our direction. Like personal life we need a goal, a vision and a roadmap for getting there. There have been plenty of challenges along the way; technical, political and personal - its been a tough and challenging few years on all of those fronts, its when you are faced with momentous personal challenges that the technical ones look trivial. The most gratifying aspect is when you see light at the end of the tunnel. It's that light at the end of the tunnel that gives you added strength to finish the job at hand. Onwards and upwards!

Database 11.2.0.4 Patchset Released

Wed, 2013-08-28 14:06

The 11.2.0.4 database patchset was released today, checking twitterland you can see news is already out. Tom Kyte tweeted 'look what slipped into 11.2.0.4' amongst others. There will be a standalone OWB 11.2.0.4 image also based on the database 11.2.0.4 components coming soon so I am told.

How to Configure The SSL Certificate For Oracle Warehouse Builder Repository Browser

Tue, 2013-08-27 22:09

  The Repository Browser is a browser-based tool that generates reports from data stored in Oracle Warehouse Builder (OWB) repositories. It use OC4j as the web server. Users need to use HTTPS to access the web interface. ( HTTP on top of the SSL/TLS protocol)

If the Repository Browser Listener is running on a computer named owb_server, then typing the following address will start the Repository Browser:

   https://owb_server:8999/owbb/RABLogin.uix?mode=design

   or

   https://owb_server:8999/owbb/RABLogin.uix?mode=runtime


On the server side, the SSL certificate for the browser is required. Users can create it by themselves.

First, uses can user the JRE's util "keytool" to generate a keystore, name it keystore.jks.

For example: keytool -genkey -keyalg RSA -alias mykey -keystore keystore.jks -validity 2000 -storepass  welcome1

Please pay attention to the password of the store, it need to be the same as the credentials of keystoreadmin in the file called "system-jazn-data.xml".


If the password is not the same, the error message like "Keystore was tampered with, or password was incorrect" will be generated.


In order to change the credentials, there are two files you can edit.


  • http-web-site.xml: It is in the path of %OWB_HOME%/j2ee/config. The password is stored as clear text in the http-web-site.xml, Users can change it to fit the password they use to generate the keysotre. For the security reason, if users don't want to store clear text, they can use the point (->keystoreadmin) to point another file named system-jazn-data.xml.


  • system-jazn-data.xml: User can find "system-jazn-data.xml" in the %OWB_HOME%/j2ee/config. There is a entry in it called "keystoreadmin".  Password store in this file is encrypted password. The pointer mentioned above is pointing to this place. In order to change the password, you can edit "system-jazn-data.xml",  change the value "<credentials>" of the entry "keystoreadmin". Please added "!" in front of your password. For example, if you want to change the password to welcome,change it to <credentials>!welcome</credentials>

The next time OC4J reads "system-jazn-data.xml", it will rewrite the file with all passwords obfuscated and unreadable.(So  your clear text like "!welcome" will become encrypted password, something like '{903}dnHlnv/Mp892K8ySQan+zGTlvUDeFYyW'

OWB - 11.2.0.3 CP3

Mon, 2013-07-15 11:10

The OWB 11.2.0.3 Cumulative Patch 3 has just been released, the bug/patch number is 16568042. This cumulative patch wraps a number of bugs from customers using the OWB 11gr2 release. See the patch readme for details. 

Oracle Database 12c

Tue, 2013-06-25 17:30

Exciting day today as Oracle Database 12c is released. You can find lots of information on the release on OTN here. With this release comes another milestone on Oracle's Data Integration roadmap - OWB is no longer shipped with the database. You will notice that the OWB documentation is no longer included with the Oracle Database documentation, you can compare and contrast the 11.2 and 12.1 documentation below.

OWB 11gR2 is still supported with Oracle Database 12c, you will need 11.2.0.3 plus at least CP2 which has been certified with Oracle Database 12c. The 11.2.0.4 release will wrapper this into one install.

OWB - Compressing Files in Parallel using Java Activity

Wed, 2013-03-13 12:36

Yesterday I posted a user function for compressing/decompressing files using parallel processes in ODI. The same code you can pick up and use from an OWB process flow. Invoking the java function from within a Java activity from within the flow.

The JAR used in the example below can be downloaded here, from the process flow OWB invokes the main method within the ZipFile class for example - passing the parameters to the function for the input, output directories and also the number of threads. The parameters are passed as a string in OWB, each parameter is wrapped in ?, so we have a string like ?param1?param2?param3? and so on. In the example I pass the input directory d:\inputlogs as the first parameter and d:\outputzips as the second, the number of processes used is 4 - I have escaped my backslash in order to get this to work on Windows.

 The classpath has the JAR file with the class compiled in it and the classpath value can be specified specified on the activity, carefully escaping the path if on windows.

Then you can define the actual class to use;

That's it, pretty easy. The return value from the method will use the exit code from your java method - normally 0 is failure and other values are error (so if you exit the java using a specific error code value you can return this code into a variable in OWB or perform a complex transition condition). Any standard output/error is also capture from within the OWB activity log in the UI, for example below you can see an exception that was thrown and also messages output to the standard output/error;

 That's a quick insight to the java activity in OWB.

OWB Repository Install on RAC using OMBPlus

Thu, 2013-03-07 17:33

There are few documents on the Oracle Support site http://support.oracle.com  to check if OWB is installed correctly on RAC and Exadata (Doc ID 455999.1) and How to Install a Warehouse Builder Repository on a RAC (Doc ID 459961.1).

 This blog will just show you how to install the OWB repository on RAC using OMBPlus.

The steps are:

  • Database preparation
  • Repository installation on the first Node
  • Copy of the rtrepos.properties File on all Nodes
  • Registration of all the other Nodes
  • Check the installation
Step 1: Database preparation

UNIQUE Service Name
Make sure that EACH Node in the RAC has a UNIQUE service_name. If this is not the case, then add a unique service_name with the following command:

srvctl add service -d dbname -s instn -r instn

The resulting service name is instn.clusterdomainname. For example, if the instance name is racsrvc1,then the service name could be racsrvc1.us.oracle.com.

"srvctl" can be used to manage the RAC services:
srvctl [status|stop|start] service -d <db> -s <service-name>

Details are described in the OWB Installation Guide:
Paragraph "Ensuring the Availability of Service Names for Oracle RAC Nodes"

LISTENER Configuration
Make sure that EACH Node has the LISTENER configured correctly. The listener on each Node should be able to manage connections to the unique database service of each Node.

Step 2: Repository installation on the first Node

We assume that RAC has 2 Nodes: NODE1 and NODE2, the database instance is setup and the OWB software has been installed on all Nodes of the RAC. 

Start the OMBPlus shell on the primary node say Node 1 from <OWB_HOME>/owb/bin/unix/OMBPlus.sh

Execute the following command

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;}

OMB+> OMBSEED DATA_TABLESPACE 'USERS' INDEX_TABLESPACE 'INDX' TEMPORARY_TABLESPACE 'TEMP' SNAPSHOT_TABLESPACE 'USERS' USING CREDENTIAL OWBSYS/PASSWORD@hostname:1521:servicename

OWB repository seeding completed.

OMB+> exit

 Step 3: Copy of the rtrepos.properties File on all Nodes

 During the Repository seeding, a file rtrepos.properties is created/updated on Node 1 at location  <OWB_HOME>\ owb\bin\admin directory. This file should be copied to all RAC Nodes to the same location. In this case to Node 2 at  <OWB_HOME>\ owb\bin\admin.

Step 4: Registration of all the other Nodes

After the Repository installation, all RAC Nodes should be registered. This to enable the OWB Runtime Service to fail over to one of the other Nodes when required (e.g. because of a node crash). This registration process consists of an update in tables OWBRTPS and WB_RT_SERVICE_NODES. These tables will be updated with Node specific details like the Oracle_Home where the OWB software has been installed on the Node and and host, port, service connection details for the instance running on the Node.  

OMB+>OMBINSTALL OWB_RAC USING CREDENTIAL OWBSYS/OWBSYS@localhost:1521:service_name

RAC instance has beeb registered.

Step 5: Check the installation

Check the owb home values in the following tables are correct.

Select * from owbsys.owbrtps

Select * from owbsys.wb_rt_service_nodes.

Connect as the OWBSYS to the unique service net_service on each node and execute the script located in the <OWB_HOME>\ owb\rtp\sql directory

SQL>@show_service.sql
Available
PL/SQL procedure successfully completed. 

If the service is not available start the service using the following script

SQL>@start_service.sql
Available

Your installation of the OWB repository is now complete.

You can also use the following OMBplus commands to create a OWB WORKSPACE and workspace owner.

In SQL*Plus as sysdba

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;}

create user WORKSPACE_OWNER identified by PASSWORD;

grant resource, connect to WORKSPACE_OWNER;

grant OWB_USER to WORKSPACE_OWNER;

grant create session to WORKSPACE_OWNER;

In OMBPlus

OMB+> OMBINSTALL WORKSPACE 'WORKSPACE_WH' USING CREDENTIAL WORKSPACE_OWNER/PASSWORD@hostname:1521:service_name

Workspace has been created.

OMB+> exit



OWB - Securing your data with Transparent Data Encryption

Thu, 2013-03-07 12:40

Oracle provides a secure and convenient functionality for securing data in your datawarehouse, tables can be designed in OWB utilizing the Transparent Data Encryption capability. This is done by configuring specific columns in a table to use encryption.

When users insert data, the Oracle database transparently encrypts it and stores it in the column.  Similarly, when users select the column, the database automatically decrypts it.  Since all this is done transparently without any change the application code, the feature has an appropriate name: Transparent Data Encryption. 

Encryption requires users to apply an encryption algorithm and an encryption key to the clear-text input data. And to successfully decrypt an encrypted value, users must know the value of the same algorithm and key. In Oracle database, users can specify an entire tablespace to be encrypted, or selected columns of a table. From OWB we support column encryption that can be applied to tables and external tables.

We secure the capture of the password for encryption in an OWB location, just like other credentials. This is then used later in the configuration of the table.

We can configure a table and for columns define any encryption, including the encryption algorithm, integrity algorithm and the password.

 Then when the table is deployed from OWB, the TDE information is incorporated into the DDL for the table;

When data is written to this column it is encrypted on disk. Read more about this area in the Oracle Advanced Security white paper on Transparent Data Encryption Best Practices here.

Pages