Oracle BI Apps 11.1.1.7.1 – GoldenGate Integration

The release of Oracle Business Intelligence Applications 11.1.1.7.1 includes a major change in components, with Oracle Data Integrator replacing Informatica as the ETL application. The next logical step was to integrate Oracle’s data replication tool, Oracle GoldenGate, for a real-time load of source system data to the data warehouse. Using GoldenGate replication rather than a conventional extract process, contention on the source is essentially eliminated and all of the source OLTP data is stored locally on the data warehouse, eliminating network bottlenecks and allowing ETL performance to increase. In this series of posts, I'm going to walk through the architecture and setup for using GoldenGate with OBIA 11.1.1.7.1.

GoldenGate and the Source Dependent Data Store

For those of you not familiar with Oracle GoldenGate (OGG), it is the standard Oracle product for data replication, providing log-based change data capture, distribution, and delivery in real-time.

GoldenGate captures transactional data changes from the source database redo log and loads the changes into its own log file, called a Trail File, using a platform-independent universal data format. The Extract process understands the schemas and tables from which to capture changes based on the configuration set in the Extract parameter file. The data is then read from the Source Trail File and moved across the network to the Target Trail File using a process called a Data Pump, also driven by a parameter file. Finally, the transactions are loaded into the target database tables using the Replicat parameter file configuration, which maps source tables and columns to their target. The entire process occurs with sub-second latency and minimal impact to the source and target systems.

In my previous blog posts regarding Oracle GoldenGate, I described how to replicate changes from the source to the Staging and Foundation layers of the Oracle Reference Architecture for Information Management. In OBIA, GoldenGate is used for pure replication from the source database to the target data warehouse, into what is known as the Source Dependent Data Store (SDS) schema.

The SDS is setup to look exactly like the source schema, allowing the Oracle Data Integrator pre-built Interfaces to change which source they are using from within the Knowledge Module by evaluating a variable (IS_SDS_DEPLOYED) at various points throughout the KM (we'll look at this in more detail later on). Using this approach, the GoldenGate integration can be easily enabled at any point, even after initial configuration. In fact, that is exactly what I did - making the switch to using OGG after my first full data load from the source without GoldenGate. The Oracle BI Apps team did a great job of utilizing the features of ODI that allow the logical layer to be abstracted from the physical layer and data source connection.

Getting Started - High Level Steps

To begin, we must first install Oracle BI Applications 11.1.1.7.1, if it is not already up and running in your environment. I followed the recently published OTN article, “Cookbook: Installing and Configuring Oracle BI Applications 11.1.1.7.1″, written by Mark Rittman and Kevin McGinley. Rather than use Windows, though, I decided to go with Linux as my host operating system for OBIA. This had its own challenges, but nothing’s worth doing if there isn't a bit of learning involved! After generating the "Source Extract and Load" Load Plan, it's time to setup GoldenGate.

Before we dig into the details of the GoldenGate integration, let's review the necessary steps at a high-level. The process follows Oracle's documentation on administering GoldenGate and OBIA Source Dependent Schema.

1. Configure the source and target database schemas. We need to create a GoldenGate user on both the source and target databases, as well as the Source Dependent Data Store schema on the target, along with the appropriate grants, etc.

2. Install Oracle GoldenGate on the source and target servers. Download and install GoldenGate on each server. The Oracle BI Applications documentation shows an example on how to get the configuration started.

3. Configure the Source Dependent Data Store. Enable the SDS in the OBIA Configuration Manager and create a new Physical Schema for the SDS in Oracle Data Integrator.

4. Generate and execute the DDL to create tables in the SDS schema on the target database. As part of the OBIA installation, many "standard" ODI Packages and Procedures were created, including GENERATE_SDS_DDL. By entering the appropriate values into the Options during execution, the process will generate a SQL script that can then be executed against the SDS.

5. Generate the initial load script. Yet another OBIA delivered Procedure will generate a SQL script for the initial load from the source to SDS schema. The script will contain INSERT statements using a database link from target to source. This script may be useful if an outage were called on the source application during OBIA and GoldenGate integration setup. But, if transactions are still flowing into the source application, a different approach will need to be used. We'll get into more details on this later (hint: it involves the source SCN).

6. Generate and deploy the GoldenGate parameter files. This is where we might expect to see the "JKM Oracle to Oracle Consistent (OGG)" Journalizing Knowledge Module put to use, correct? But no, the OBIA product team decided to go with a custom Procedure rather than the JKM, as the ODI Change Data Capture (aka Journalizing) is not put to use. Execute the GENERATE_SDS_OGG_PARAM_FILES Scenario, copy the parameter files to the appropriate locations, and complete the GoldenGate configuration.

7. Start GoldenGate replication. Again, if there is not a source outage we will probably need to customize our start replicat statement to ensure we do not miss any transactions from the source.

Once GoldenGate replication has begun and you can continue to use the same "Source Extract and Load" Load Plan that was originally generated to pull data from the source database into the data warehouse. But now, instead of reaching out to the source database, this Load Plan will pull data from the SDS schema into the staging area.

In the next post, we'll dive deeper into the setup and configuration details, working through each of the steps listed above.

Oracle BI Apps 11.1.1.7.1 – GoldenGate Integration

1. Introduction
2. Setup and Configuration
3. SDS and Parameter File Setup
4. Initial Load and Replication