The release of Oracle Business Intelligence Applications 184.108.40.206.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 220.127.116.11.1.
GoldenGate and the Source Dependent Data StoreFor 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.
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.
Getting Started - High Level StepsTo begin, we must first install Oracle BI Applications 18.104.22.168.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 22.214.171.124.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 126.96.36.199.1 – GoldenGate Integration