Oracle BI Apps – GoldenGate Integration – Part 2: Setup and Configuration

In my previous post, I introduced the steps necessary for integrating Oracle BI Applications and GoldenGate (OGG). Now, I’m going to dive into the details and describe how to complete the setup and configuration of GoldenGate and the Source Dependent Data Store schema. As I mentioned before, this process will closely follow Oracle’s documentation on “Administering Oracle GoldenGate and Source Dependent Schemas“, providing additional information and insight along the way.

User and Schema Setup

The first step is to manually create the GoldenGate user on the source and target databases. These users, along with the Source Dependent Data Store schema, are not created by the BI Apps installer like the other standard schemas. This will be a dedicated user for OGG, and will have privileges specific to the needs of the extract process on the source and the replicat process on the target.

Create Source GoldenGate User

Beginning with the source, create the user and grant the initial privileges. Be sure your tablespace has already been created.

The specific table grants will not be made until later on via a script generated by an ODI Procedure, as the GoldenGate user does not need SELECT ANY TABLE privileges. On the other hand, the user does temporarily need ALTER ANY TABLE in order to set up supplemental logging for individual tables. Later on, this privilege can be revoked.

Finally, we’ll setup supplemental logging at the database level, ensuring the necessary information is logged for each transaction.

Create Target GoldenGate User

Next, we’ll go out to the target server and create the GoldenGate user with target-specific privileges. Since GoldenGate performs the DML on the target, based on the change made in the source database, the user will need to be granted privileges to INSERT, UPDATE, DELETE. Again, rather than grant INSERT ANY TABLE, etc., the specific table grants will be generated as a script via an ODI Procedure.

We’ll be creating the checkpoint table via GoldenGate, so this user will temporarily need to be granted the CREATE TABLE privilege. The checkpoint table will keep track of the latest position in the target trail file, allowing a clean recovery should the target database go offline.

Create SDS User

Now we’ll create the SDS user and schema. A separate SDS schema must be created for each OLTP source application, as the SDS schema will essentially act as the source schema. We’ll follow the recommended naming conventions for the schema: <BIAPPS>SDS<Model Code>_<DSN>. BIAPPS is the user defined code signifying this is a BI Applications schema. To keep it simple, we’ll use BIAPPS. The Model Code is the unique code assigned to the data source and the DSN is the data source number for that source application.


In this example using Peoplesoft Campus Solutions, the SDS schema name is BIAPPS_SDS_PSFT_90_CS_20. Not a very friendly name to type, but serves its purpose in identifying the source of the schema data.

Finally, the GoldenGate target user must be granted access to use the SDS tablespace for inserts/updates.

Install and Configure GoldenGate

The schemas are in place, so the next part of the setup is to install and configure the GoldenGate application on both the source and target servers. The GoldenGate installation process is pretty well documented on Gavin Soorma’s blog, so I won’t go into much detail here. The Oracle BI Applications documentation also has some example scripts, which take you through the setup of the extract, data pump, and replicat group processes.

The naming standards for the parameter files are fairly straightforward, with DSN being the same data source number we used in the SDS schema name.

  • Extract: EXT_DSN
  • Data Pump: DP_DSN
  • Replicat: REP_DSN

Following the OBIA documentation examples, you will end up with each process group setup and the checkpoint table created in the target GoldenGate schema. I prefer to create an obey file for both the source and target setup scripts, similar to the following example.

Using an obey script allows me to rerun the process should there be any sort of issue or failure and also provides me with a template that I can use for additional sources and SDS targets. The result should be process groups setup and ready to roll (once the parameter files are in place, of course).


Remember to revoke the CREATE TABLE privilege from the target GoldenGate user once the checkpoint table has been created.

In the next post, I’ll walk through the SDS setup in OBIA and ODI, as well as the ODI Procedures that help generate the GoldenGate parameter files, SDS schema DDL, and initial load scripts.

Oracle BI Apps – GoldenGate Integration

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