Oracle BI Apps 11.1.1.7.1 – GoldenGate Integration

In my previous post, I introduced the steps necessary for integrating Oracle BI Applications 11.1.1.7.1 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.

-- Create OGG User on the source
CREATE USER ogg_user IDENTIFIED BY Password01
DEFAULT TABLESPACE ggs_data QUOTA UNLIMITED ON ggs_data;
GRANT CREATE SESSION TO ogg_user;

GRANT ALTER SESSION TO ogg_user;

GRANT SELECT ANY DICTIONARY TO ogg_user;

GRANT FLASHBACK ANY TABLE TO ogg_user;

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.

GRANT ALTER ANY TABLE TO ogg_user;

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

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

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.

-- Create OGG User
CREATE USER ogg_target
IDENTIFIED BY Password01
DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;
GRANT CREATE SESSION TO ogg_target;

GRANT ALTER SESSION TO ogg_target;

GRANT SELECT ANY DICTIONARY TO ogg_target;

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.

GRANT CREATE TABLE TO ogg_target;

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.

-- Create tablespace.
CREATE TABLESPACE BIAPPS_SDS_PSFT_90_CS_20_TS
DATAFILE '/u01/app/oracle/oradata/orcldata/BIAPPS_SDS_PSFT_90_CS_20..dbf' SIZE 100M AUTOEXTEND ON NEXT 10M
LOGGING
DEFAULT COMPRESS FOR OLTP;

-- Create SDS User
CREATE USER BIAPPS_SDS_PSFT_90_CS_20
IDENTIFIED BY Password01
DEFAULT TABLESPACE BIAPPS_SDS_PSFT_90_CS_20_TS QUOTA UNLIMITED ON BIAPPS_SDS_PSFT_90_CS_20_TS;

-- Required Grants
GRANT CREATE SESSION TO BIAPPS_SDS_PSFT_90_CS_20;
GRANT CREATE TABLE TO BIAPPS_SDS_PSFT_90_CS_20;

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

-- OGG user must be granted Quota to insert and update data
ALTER USER ogg_target QUOTA UNLIMITED ON BIAPPS_SDS_PSFT_90_CS_20_TS;

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.

--stop manager on target db dblogin userid ogg_target, password Password01 stop mgr

--stop gg processes
stop rep_20
delete rep_20

--delete CHECKPOINTTABLE
DELETE CHECKPOINTTABLE ogg_target.OGGCKPT

--delete previous trail files
SHELL rm ./dirdat/*

--start manager on target db
start mgr

--create CHECKPOINTTABLE in target db
dblogin userid ogg_target, password Password01
ADD CHECKPOINTTABLE ogg_target.OGGCKPT

add replicat rep_20, exttrail ./dirdat/tr, CHECKPOINTTABLE ogg_target.OGGCKPT

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.

REVOKE CREATE TABLE FROM ogg_target;

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 11.1.1.7.1 – GoldenGate Integration

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