Data Management,

Using ODI Loading Knowledge Modules on the Oracle Database Cloud Service (DBCS)

August 25, 2016 | 9 minute read

Text Size 100%:

－＋

Introduction

This article discusses how to use Loading Knowledge Modules (LKMs) in Oracle Data Integrator (ODI) to upload data into instances of the Oracle Database Cloud Service (DBCS). LKMs are re-usable code templates within the ODI framework that can be used in ODI mappings to perform data upload operations.

On cloud computing, Oracle offers Platform as a Service (PaaS), which provides a shared and elastically scalable platform for the consolidation of existing applications and the development of new applications. Under PaaS, Oracle offers data management services such as the Oracle Database Cloud Service (DBCS), which offers the power and flexibility of the Oracle database in the cloud.

On the data integration space, ODI offers a variety of LKMs for the Oracle technology that can be used for both on-premise Oracle databases and instances of DBCS. This article discusses three LKMs that can be used to exchange data between instances of DBCS. Also, the article discusses how to upload data from on-premise datastores such as Oracle databases and text files into instances of DBCS. Finally, the article extends the usability of these three LKMs to Amazon Web Services (AWS), which supports the Oracle database as well.

Using ODI Loading Knowledge Modules on Oracle Database Cloud Service (DBCS)

In ODI, loading knowledge modules (LKMs) are required when the source datastore and the target datastore are from different technologies, or when the source datastore and the target datastore are from the same technology, but they are not located on the same data server. On cloud computing, LKMs are required when uploading data from an on-premise data server into a cloud data service, or when both the source and the target datastores are from the same cloud service, but each datastore is located in a different instance of the service or hosted on separate services.

ODI offers a variety of LKMs for the Oracle technology. For instance, when loading data between Oracle databases, ODI offers the LKM Oracle to Oracle (datapump), and the LKM Oracle to Oracle (DBLINK), among others. The LKM Oracle to Oracle (datapump) uses the Oracle Data Pump technology to upload data – in parallel - between two Oracle databases. This technology offers the fastest method for uploading data between two Oracle databases. The LKM Oracle to Oracle (DBLINK) uses the Oracle DBLINK technology to connect two Oracle databases and perform the data upload operation. For external sources such as text files, ODI offers – among others – the LKM File to Oracle (EXTERNAL TABLE). This LKM uses the Oracle External Table technology to upload text files into the Oracle database.

These three LKMs can be used on both on-premise Oracle databases and instances of DBCS. The following sections of this article illustrate some examples.

LKM Oracle to Oracle (datapump) on DBCS

Figure 1 below illustrates an example of how the LKM Oracle to Oracle (datapump) can be used on DBCS.

Figure 1 - LKM Oracle to Oracle (datapump) on DBCS

Figure 1 above illustrates four instances of cloud services on the Oracle Public Cloud (OPC). Instance A, Instance C, and Instance D are all DBCS instances. Instance A contains a database that is used as a metadata repository – the ODI repository is located on this database. Instance B is an instance of the Java Cloud Service (JCS) – the ODI agent is located on this instance. Instance C contains a database that is used as a source database. Instance D contains a database that is used as a target database.

Using this example, the ODI agent launches an ODI mapping that uses the LKM Oracle to Oracle (datapump) to export data from the source database and import it into the target database. The datapump export operation is performed by the source database, and the datapump files are created on Instance C. The datapump files are then copied from Instance C to Instance D using the ODI tool called OdiScpPut. This ODI tool uses the Secure Copy (SCP) protocol to copy files between two data servers. The datapump import operation is performed by the target database, located on Instance D.

On Figure 1 above, the LKM Oracle to Oracle (datapump) has been customized with a new step to invoke the ODIScpPut tool. Alternatively, the ODIScpPut tool can be invoked from an ODI procedure or ODI package.

Oracle Cloud services such as JCS and DBCS require a public/private key-pair to access the cloud instances of these services via a secure shell (SSH). When using the ODI OdiScpPut tool to copy files from on-premise data servers to instances of DBCS or between instances of DBCS, a private key is required. For additional information on how to create a SSH public/private key-pair for Oracle Cloud services, go to “Creating SSH Keys for Use with Oracle Cloud Services.”

LKM Oracle to Oracle (DBLINK) on DBCS

The LKM Oracle to Oracle (DBLINK) can also be used to copy data between two instances of DBCS. Figure 2 below shows an example.

Figure 2 - LKM Oracle to Oracle (DBLINK) on DBCS

In this example, on Figure 2 above, the ODI agent launches an ODI mapping that uses the LKM Oracle to Oracle (DBLINK) to select data from the source database (DBCS Instance C) and insert it into the target database (DBCS Instance D). In this example, the data-transfer is performed by the DBLINK technology. The database link is created by the LKM at runtime, or it can be configured by a database cloud administrator prior the execution of the ODI mapping.

When the ODI agent connects to a database instance of DBCS, ODI uses the Oracle JDBC driver to establish the connection. This JDBC connection should be secured. To secure this connection, the Oracle JDBC driver should first create a SSH tunnel between the host – where the Agent is located – and the database instance. To learn how to establish a secured connection between the ODI Agent and DBCS, go to “Connect ODI to Oracle Database Cloud Service (DBCS)” – this article describes how to use a customized version of the Oracle JDBC driver to establish secured connections between the ODI agent and instances of DBCS.

LKM Oracle to Oracle (Datapump & DBLINK) – On-Premise to DBCS

LKM Oracle to Oracle (datapump) and LKM Oracle to Oracle (DBLINK) can also be used with ETL architectures that have both on-premise Oracle databases and instances of DBCS. Figure 3 below shows an example of how these two LKMs can be used to upload data from on-premise Oracle databases into several instances of DBCS.

Figure 3 - LKM Oracle to Oracle (Datapump & DBLINK) – On-Premise to DBCS

Figure 3 above shows two environments: an on-premise environment, and an OPC environment. The on-premise environment has two components: an ODI agent, and an Oracle database server. The ODI agent, Agent A, is a J2EE agent, but it can be a standalone or collocated agent instead. The database server, Datastore A, has an Oracle database, which is used as an online transaction processing (OLTP) database.

On the OPC environment, there are four instances of the Oracle cloud services: three instances of DBCS, and one instance of JCS. The DBCS instances called Instance A, Instance C, and Instance D contain each a database to host – respectively – an ODI repository, an operational datastore (ODS), and a data warehouse. Instance B, the JCS instance, hosts the ODI agent, Agent B.

Using this example, Agent A, the on-premise agent, launches an ODI mapping that uses the LKM Oracle to Oracle (datapump) to copy data from the OLTP database (on-premise) to the ODS database (cloud). The OLTP database performs the datapump export operation and creates the datapump files on Datastore A. The ODI OdiScpPut tool securely copies the datapump files from Datastore A (on-premise) to Instance C (cloud). On Instance C, the ODS database performs the datapump import operation. Notice that this entire operation is orchestrated by the on-premise agent, Agent A. Then, Agent B, the agent on the JCS instance, launches another ODI mapping that uses the LKM Oracle to Oracle (DBLINK) to copy data from the ODS database to the warehouse database. Notice that both agents, Agent A and Agent B, use the same ODI repository, located on Instance A.

LKM File to Oracle (EXTERNAL Table) – On-Premise to DBCS

LKMs can also be used to upload text files from on-premise data servers to DBCS instances. Figure 4 below shows an example.

Figure 4 - Using ODI Loading Knowledge Modules - On-Premise to DBCS – External Tables

Figure 4 above shows two environments: an on-premise environment, and an OPC environment. The on-premise environment has an ODI agent, and a data server. The data server, File Server A, has text files that represent operational data. The OPC environment includes two instances of DBCS, and one instance of JCS. The DBCS instances called Instance A and Instance C contain each a database to host the ODI repository, and the warehouse database, respectively. Instance B, the JCS instance, hosts the ODI agent, Agent B.

Using this example, Agent A, the on-premise agent, launches an ODI procedure that uses the ODI OdiScpPut tool to copy the operational text files from File Server A (on-premise) to Instance C (cloud). On the OPC environment, Agent B launches an ODI mapping that uses the LKM File to Oracle (EXTERNAL TABLE) to upload the text files into the warehouse database. Notice that the upload operation is done by the warehouse database via Oracle external tables – Agent B only orchestrates the upload operation. Both agents, Agent A and Agent B, use the same ODI repository, located on Instance A.

Using ODI Loading Knowledge Modules on Amazon Web Services (AWS)

The use of LKMs can be extended to other cloud services such as the Amazon web services (AWS). Figure 5 below illustrates an example.

Figure 5 - Using ODI Loading Knowledge Modules - Amazon Web Services (AWS)

On Figure 5 above, the ODI agent is located on an instance of the Amazon Elastic Compute Service (EC2). The LKM Oracle to Oracle (datapump) can be used in ODI mappings to perform data upload operations between two instances of the Oracle database located on the Amazon Relational Database Service (RDS). The LKM Oracle to Oracle (DBLINK) can be used in ODI mappings to perform data upload operations between two Oracle databases, each of them located on Amazon RDS and Amazon EC2. Also, the LKM File to Oracle (EXTERNAL TABLE) can be used in ODI mappings to upload text files into Oracle databases located on Amazon EC2. The text files and the Oracle database are both located on the same instance of the Amazon EC2. The ODI agent only orchestrates the executions – the actual data upload operations are done by the Oracle tools.

Conclusion

ODI Loading Knowledge Modules are re-usable code templates within the ODI framework that perform data upload operations for both on-premise data servers and cloud data services. This article presented an overview of how to use ODI LKMs to upload data into instances of the Oracle database as a service (DBaaS).

For more Oracle Data Integrator best practices, tips, tricks, and guidance that the A-Team members gain from real-world experiences working with customers and partners, visit “Oracle A-team Chronicles for Oracle Data Integrator (ODI).”