Tuesday Oct 28, 2014

ODI 12c and DBaaS in the Oracle Public Cloud

This article illustrates how to connect ODI on premise to Oracle in the cloud (OPC), specifically the Database as a Service (DBaaS, see doc here) offering. You will see how easy it is to configure connectivity from within ODI and the use of familiar tools gives you the same consistency from on premise use to the cloud. A big concern for cloud computing is security and ensuring access is restricted and as secure as possible. For ODI on premise the challenge is how to connect to such a secure service. ODI provides tasks for transferring files to and from locations - databases are generally accessed via JDBC.

The initial state of an Oracle DBaaS service restricts remote access to SSL - so you can't just remotely connect by default to an Oracle database listener for example (it is possible to open that up by configuring this within DBaaS). File transfer to the cloud can be done out of the box using sftp capabilities, access to the database in order to load it with data, to transform data within it and to extract data from it can be done with a small bit of SSL tunneling - let's see how. The examples discussed in this article have been developed with ODI 12.1.3, a copy of the driver which performs the SSL tunneling can be found on java.net here. With this driver it is effortless to work with an on premise ODI and an Oracle database in the cloud.

Before we get to the ODI parts let's look at the basics, this is mentioned in the DBaaS documentation but sometimes it's simpler to read what someone has done than follow the doc.....

If you want to be using ODI or other remote capabilities such as ssh, sftp then before creating the Oracle database instance in the cloud you should generate a secure private key-public key pair. The public key gets used when you create the Oracle database instance in the cloud and the private key is used by SSL tools (such as sftp, ssh or the driver used) to securely connect to the cloud. 

When you create the key using something like PUTTY, then ensure you save the public key, private key and export the key using the OpenSSH key option also. ODI actually needs the OpenSSH format right now as the version of a library it depends on supports this.

You can see where the public key is provided below in the Instance Configuration section.....

The great news about the DBaaS capabilities is that it is all very familiar for Oracle database folks also the service itself can be managed from the command line - so as well as the web pages console and EM etc, you can use the command line and work the way you are used to.

Anyway, back on course... when you have the instance up and running it's time to have some fun with it!

File Transfer to the Oracle Public Cloud

In ODI you can use the OdiSftpPut/Get tool in a package, procedure or KM to transfer data to/from the cloud. You can see in the example below the OdiSftpPut tools is being used to transfer a file 'm.csv' from the local filesystem (d:\data) to the directory (/home/oracle) in the cloud. The private key is specified in the property 'SSH Identity File' and the key file password is specified in 'Remote User Password'. The OS user to use for the ftp is specified as 'oracle' in the property 'Remote User Name'.

Very simple. The DBaaS instance has OS users created when initialized you can read more about the users 'opc' and 'oracle' in the DBaaS documentation.

Transforming Data in the Oracle Public Cloud

Mappings are used to transform data from one representation to another. In this example you will see how the file staged in the Oracle Public Cloud is integrated with an Oracle target - just like standard ODI on premise use cases. It is no different. Below you can see the image has the logical mapping at the top, with the file being mapped to an Oracle target table, then the middle part of the image shows the physical design, the map uses the LKM File to Oracle (External Table) KM to create an external table on top of the file in the cloud and then the target is integrated with the Oracle Insert KM. 

When I execute the mapping all of the statements to transform are executed in the OPC - in this particular design everything is executed inside the Oracle database.

The ODI data server definition is using a custom driver (here) which extends the Oracle JDBC driver. The driver creates a SSH tunnel between the host executing the connect and the instance in the cloud. This means all ODI objects such as procedures, mappings and so forth that execute statements on regular Oracle systems can execute them on the cloud instances too. I actually created my demonstration schemas and granted all the permissions using a procedure in ODI. The procedure is shown below, see the snippet of the statements creating the users - the target logical schema was my DBAAS_INSTANCE.

Let's dig under the covers and have a look at how the physical schema is defined. You'll need the driver, and have it copied into your oracledi/userlib directory (or wherever your agent is installed if using an agent). You can then define the connection, specify the database user name and password for that database user;

Then you specify the driver, the driver you need to download and mentioned above. The URL is of the form of the Oracle JDBC driver. The difference is in how you specify the host, port and sid/service. The sid/service are your actual cloud service details. Since we are using the SSH tunnel technique, we actually specify the local host and a port number (default 5656) on the local host.

The properties to configure the SSH tunnel are defined either in the properties panel or in a file specified in the properties panel. I've decided here to use the file approach, so can specify the file name in the property propertiesFile.

In my example, this file contains;

  • sslUser=oracle
  • sslHost=<my_cloud_ip_address>
  • sslRHost=<my_cloud_ip_address>
  • sslRPort=1521
  • sslPassword=my_private_key_password
  • sslPrivateKey=D:\\credentials\\dbcloud12c_private_key_openssh.ppk

That is all that is needed and you can be very creative using all the powers of Oracle in the cloud and ODI for integration. Here is a summary of the properties supported by the driver.

Property Name Property Description
sslUser The OS user to connect via SSL with.
sslHost The address of the host to SSL to.
sslRHost The tunnel can be made between the client through the SSL host to this host. In my case this was the same as the SSL host.
sslRPort The port to tunnel to. The Oracle listener is often run on 1521, so this is the default if this property is not specified.
sslPassword The password for the private key file. In ODI you must use OpenSSH formatted private key file.
sslPrivateKey The SSL private key file location.
sslLPort By default the local port used is 5656, it can be changed with this property. You must reference this port number in the URL also.

The driver is a fairly simple wrapper around the Oracle JDBC driver, it leverages SSL tunneling to forward requests on a secure port to the Oracle TNS listener. This technique enables a very familiar way of connecting and interacting with the Oracle database, the driver is on java.net and is available to try and get feedback on. So try it and let us know what you think. Familiarity and consistency are very important both from the stance of the tooling and leveraging existing knowledge (including modules). This allows ODI users to work with the Oracle Public Cloud DBaaS instance just as they do with their on premise systems. Have fun!

Wednesday May 21, 2014

Zero Downtime Consolidation to Oracle Database 12c: Webcast Recap

As companies move to private cloud implementations to increase agility and reduce costs, and start their plans with consolidating their databases, a major question arises: how do we move our systems without causing major disruption to the business. At the end, the most important systems that need to move to this new architecture are the ones that cannot tolerate extensive downtime for any reason. In last week’s webcast “Zero Downtime Consolidation to Oracle Database 12c with Oracle GoldenGate 12c” we tackled this specific dilemma that IT organizations face. The webcast is now available on demand via the link above in case you missed it last week.

In the webcast, we started discussing the benefits companies achieve when they consolidate to a private database cloud, critical database capabilities to deliver database as a service, and a quick overview Oracle Database 12c features that enable private database clouds deployments. Nick Wagner, director of product management for Oracle Database High Availability, talked about the new Global Data Services feature in Oracle Database Maximum Availability Architecture (MAA) as well. Global Data Services helps organizations to handle challenges involved with managing multiple database replicas across different sites. The product manages database replicas of Active Data Guard and Oracle GoldenGate products, and offers workload balancing to maximize performance as well as intelligent handling of outages using all available replicas. Nick also discussed the database features available for upgrading, migrating, and consolidating into Oracle Database 12c and when it makes sense to use Oracle GoldenGate for these efforts.

After Nick, Chai Pydimukkala from senior director of product management for GoldenGate discussed some of the key new features of Oracle GoldenGate 12c for Oracle Database and its expanding heterogeneity that supports consolidation from all major databases. Chai continued his presentation with GoldenGate’s solution architecture for zero downtime consolidation and explained how it minimizes risk with failback option as well as via the phased migration option by running old and new environments concurrently.

Chai also gave an example on how Oracle Active Data Guard customers can seamlessly use GoldenGate for a zero downtime migration without affecting their standby system for high ongoing high availability and disaster recoery. After customer examples we had a long Q&A section. For the full presentation and Q&A with the experts you can watch the on-demand version of the webcast via the following link.

Zero Downtime Consolidation to Oracle Database 12c with Oracle GoldenGate 12c

In the rest of this blog post, I provided  answers to some of the questions we could not take in the live event. 

1) Additional difference in features between GoldenGate 11g and 12c?

Please take a look at our previous blog posts below where we discussed many of the new features of GoldenGate 12c.

· Advanced Replication for The Masses – Oracle GoldenGate 12c for the Oracle Database.

· GoldenGate 12c - What is Coordinated Delivery?

· GoldenGate 12c - Coordinated Delivery Example

· Oracle GoldenGate 12c - Announcing Support for Microsoft and IBM

You can also listen to our podcast: Unveiling Oracle GoldenGate 12c

More comprehensive information can be found in the webcast replay  Introducing Oracle GoldenGate 12c: Extreme Performance Simplified or GoldenGate 12c resource kit.

2) Does GoldenGate have a term license that customer can purchase for migration purposes?

We do offer 1 year license that customer can be used for this purposes.

3) Is there a Oracle Tutorial on configuring and testing Zero downtime upgrade using GoldenGate?

Here is a high level outline/tutorial for the migration. It will require some knowledge of how to setup GoldenGate and configuration.

Oracle GoldenGate Best Practice: Oracle Migrations and Upgrades 9i and up

A more broad discussion of this topic can be found in the following white paper. Zero Downtime Database Upgrades using Oracle GoldenGate 12c

4) If the number of updates to the database is very high, does GoldenGate give some kind of compression to transfer the Trail Files and be up to date with the changes?

Oracle GoldenGate does provide support of compress of change data in the Trail File to better the transfer throughput from source to target with the usage of COMPRESS and COMPRESSTHRESHOLD. 


{host_name | IP_address}

{, MGRPORT port | PORT port}



[, ENCRYPT algorithm [KEYNAME key_name]]

[, PARAMS collector_parameters]


[, TCPBUFSIZE bytes]


[, TIMEOUT seconds]


This option is valid for online or batch Capture processes and any Oracle GoldenGate initial-load method that uses Trails. Compresses outgoing blocks of records to reduce bandwidth requirements. Oracle GoldenGate decompresses the data before writing it to the Trail. COMPRESS typically results in compression ratios of at least 4:1 and sometimes better. However, compressing data can consume CPU resources.


This option is valid for online or batch Capture processes and any Oracle GoldenGate initial-load method that uses trails. Sets the minimum block size for which compression is to occur. Valid values are from 0 and through 28000. The default is 1,000 bytes.

5) Can the Source system utilize 12C GoldenGate for DML/DDL capture and target system utilize GoldenGate 11g?

Yes.  You can replicate from a higher version of GoldenGate to a lower version using FORMAT RELEASE option. 

FORMAT RELEASE <major>.<minor>

In the on demand replay of the webcast you will find more questions answered by our product management experts. So make sure to watch the on demand webcast as well.

Tuesday May 06, 2014

No Way Out, But to Consolidate

IT teams receive pressures on multiple fronts: Business groups demand new application services delivered faster and continuous, high-quality IT service to support highly dynamic and competitive business operations. In the meanwhile IT teams are asked to reduce costs and improve ROI from existing systems in highly complex and siloed environments.

In the midst of this conundrum, the only way out for IT leadership is to consolidate and leverage cloud architecture. Especially consolidation at the database level and implementing a private database-as-a-service (DBaaS) environment is a transformative approach to delivering database functionality to end users in an agile, efficient, and scalable way.  It helps organizations improve resource utilization, and lower both capital and operational expenditures.

Oracle Database 12c is designed to support database consolidation with many new features for deploying shared database environment. The new multitenant architecture, simplified management capabilities, improvements to security and compliance, along with robust and flexible Maximum Availability Architecture, are only a few of the key differentiators.

While this all sounds good and logical for IT teams, when it comes to moving to a private database cloud, they face yet another major challenge: How do we move our critical systems to a new environment without interrupting our operations. In today’s 24/7 world, especially customer-facing systems cannot tolerate hours or days of downtime. For some of them, even few minutes of downtime can bring high costs to the business. As in each major project, risk involved with moving to a new system is a real deterrent factor too.

Oracle GoldenGate 12c offers the core capabilities to enable this major move to a database as a service environment. With its optimized support for Oracle Database 12c multitenant architecture and heterogeneous data replication capabilities, Oracle GoldenGate allows organizations to avoid business disruption while the systems are moving to a consolidated, DBaaS environment. In addition, Oracle GoldenGate offers a failback option to the old environment, or a phased migration option by running old and new systems simultaneously in active-active mode, to minimize risks. Oracle GoldenGate’s heterogeneity includes all major database vendors and operating systems including SQL Server, DB2 (LUW, z/Os, iSeries), Sybase ASE, HP NonStop and more. 

In our webcast Zero Downtime Consolidation to Oracle Database 12c with Oracle GoldenGate 12c on May 13th 10am PT we will present this solution with specific architecture examples and comparison to other database upgrade options.

Zero Downtime Consolidation to Oracle Database 12c with Oracle GoldenGate 12c

May 13th, 10am PT/ 1pm ET

If your organization needs to improve agility, increase innovation, and reduce costs, I invite you to join this webcast to learn how to remove the risk and business interruption barriers to move to an agile and cost-efficient private database cloud.


Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality


« March 2015