OBIA 11g Oracle Goldengate Replication Troubleshooting guide for OBIA Errors (Doc ID 1956718.1)

Last updated on JULY 11, 2017

Applies to:

Business Intelligence Applications Consumer - Version 11.1.1.8.1 and later
Information in this document applies to any platform.

Symptoms

Created from SR 3-10042015351[This section is not visible to customers.]


Applies to:

Business Intelligence Applications Consumer - Version 11.1.1.8.1 and later
Information in this document applies to any platform.

Purpose

This document provides steps to troubleshoot issues when using Oracle Golden Gate with OBIA 11.1.1.8.1

The process to implement Oracle golden Gate can be found in following documentation:
Oracle® Business Intelligence Applications
Administrator's Guide
11g Release 1 (11.1.1.8.1)
E51483-02

Chapter 7 details setting up Oracle Goldengate, creating SDS DDL, creating OGG parameter files, and generating load script from OLTP to SDS.

Troubleshooting Steps

 1. Create the SDS Tables

After initial populating the SDS tables, errors similar to the following can be seen:

"com.sunopsis.tools.core.exception.SnpsSimpleMessageException: com.sunopsis.tools.core.exception.SnpsSimpleMessageException: Exception getSchemaName("DS_EBS11510", "D") : SnpPschemaCont.getObjectByIdent : SnpPschemaCont does not exist"

Solution: Make sure the logical schema (DS_EBS11510 in this example) is mapped in the context you are executing the procedure with

"java.lang.Exception: The application script threw an exception: java.lang.Exception: Model with code 'Oracle E-Business Suite 11.5.10' does not exist"

Solution: Make sure the logical schema associated with your model (in this case, the model 'Oracle E-Business Suite 11.5.10' is associated with the logical schema DS_EBS11510) has been assigned a value for the DATASOURCE_NUM_ID        flexfield

"java.lang.Exception: The application script threw an exception: java.lang.Exception: Can't find physical schema for connection for DW_BIAPPS11G with DSN 310 in context Global"

Solution: Make sure you have created a physical schema under the Data Warehouse physical server and assigned the DATASOURCE_NUM_ID the same value as assigned to the OLTP.

As a recommendation, If you encounter any issues with the script generated by the 'GENERATE_SDS_DDL' procedure, verify the following have been correctly set:

  • The model you are specifying (eg Oracle E-Business Suite R12.1.3) is associated with a logical schema (eg. DS_EBSR1213)
  • The logical schema's DATASOURCE_NUM_ID flexfield is assigned a numeric value (eg. 310 - a value is automatically assigned when a datasource is registered in BIA Configuration Manager)
  • The logical schema is mapped to a physical schema (eg ORACLE_EBS11510.APPS) in the context (eg. Global) you are executing the procedure with (the physical schema is automatically mapped to the Global context when you register the datasource is registered in BIA Configuration Manager)
  • The physical schema's DATASOURCE_NUM_ID flexfield is assigned the same numeric value as the logical schema (eg. 310 - a value is automatically assigned when a datasource is registered in BIA Configuration Manager)
  • Under the same context, a physical schema is mapped to the DW_BIAPPS11G logical schema (eg. BIAPPS_DW.OLAP)
  • A new SDS physical schema has been added to the same physical server (eg. BIAPPS_DW.SDS_EBS_12_1_3_310 - this physical schema is manually added)
  • This physical schema's DATASOURCE_NUM_ID flexfield is assigned the same numeric value as used previously (eg. 310 - this value is manually assigned)

 

2. Using the DML option to perform an initial Load

 For errors like:

"ODI-1228: Task Copy SDS Data (Procedure) fails on the target ORACLE connection BI_APPLICATIONS_DEFAULT.

PL/SQL: ORA-00942: table or view does not exist
PLS-00364: loop index variable 'COL_REC' use is invalid"

          Solution: Verify a database link named DW_TO_OLTP exists in the schema owned by the database user associated with the data warehouse Data Server (ie BIAPPS_DW) has been created.

 Or if the Insert statement only populates the CDC$ columns, where the script has statements such as the following :

truncate table SDS_EBS11510_FULL.HR_LOCATIONS_ALL;
INSERT /*+ APPEND */ INTO SDS_EBS11510_FULL.HR_LOCATIONS_ALL (CDC$_SRC_LAST_UPDATE_DATE, CDC$_RPL_LAST_UPDATE_DATE, CDC$_DML_CODE) SELECT SYSDATE, SYSDATE, 'I' FROM HR_LOCATIONS_ALL@DW_TO_OLTP;

          Solution: Verify the database link DW_TO_OLTP in the schema belonging to the database user associated with the data warehouse Data Server (ie BIAPPS_DW) is pointing to the correct OLTP database. The procedure gets a column list from the data dictionary on the OLTP database for the tables that correspond to the SDS tables. If the database link points to the wrong database, a column list will not be retrieved.

 

If you encounter any issues with the script generated by the 'COPY_OLTP_TO_SDS' procedure, verify the following have been correctly set:

  • A database link with the name DW_TO_OLTP has been created in the database user's schema used by the data warehouse Data Server (ie BIAPPS_DW) that points to the OLTP database.  
  • The procedure is executed by this user and so Oracle looks for this database link in this user's schema, not the SDS schema (you still need the a database link with this name in the SDS schema for other reasons so you have a total of two database links to the same source database).

3.- Create SDS Indexes and Analyze the SDS schema

When executing the script to create indexes and primary key constraints on the SDS tables, you may see some of the following error or warning messages.

"such column list is already indexed"

        When executing the script that creates the indexes, you may see the error message 'such column list is already indexed'.  This message can be ignored. 

Oracle GoldenGate works best when a primary key is defined on the target table in order to determine which record to update.  If a primary key is not found, the replicat process will search for a unique index to determine which record to update. The definition of the tables in the SDS is based on the definition in the source system (leveraging both the application dictionary and data dictionary).  If the table does not have a primary key in the source system but does have multiple unique indexes, a primary key may be added in the ODI definition to ensure Oracle GoldenGate can correctly identify the record to be updated. This primary key may be defined on the same column that a unique index is already defined on.  The DDL script creates the primary key as an index and a constraint before creating the unique index - when creating the unique index, the database will report that the column is already indexed.

"column contains NULL values; cannot alter to NOT NULL"

This error can occur when a primary key constraint is being created. This error can occur for a couple of reasons.

We introduce primary key constraints in ODI when a primary key is defined in the OLTP system. We may also introduce a primary key constraint in ODI when there is no primary key in the OLTP system for the table but the table has multiple unique indexes - in this case, we introduce a primary key constraint to ensure Oracle GoldenGate does not use a unique index that may not correctly identify a record for updates.

OLTP table has Primary Key Constraint

Due to differences in patches and subversions, it is possible the OLTP instance used to originally import the datastores from had a primary key constraint that differs from the OLTP version you are using. If the OLTP table has a primary key constraint, ensure the definition in ODI matches this primary key. If there is a difference, you can modify the Index DDL script to use the proper definition to create the primary key constraint in the target database. You should also update the constraint in ODI to match this definition.

If the OLTP and ODI definitions of the primary key constraint match, it is possible the initial load process did not populate one or more of the columns that make up the primary key. If the primary key includes a LOB or LONG datatype, we do not replicate data in these columns which would leave the column empty. In this case, no unique index or primary key can be created and without data in this column, the record cannot be uniquely identified. Any ETL task that extracts from this table will need to be modified to extract directly from the OLTP system. This is done by modifying the load plan step for this task, overwriting the IS_SDS_DEPLOYED parameter for that load plan step to 'N'.

If the OLTP and ODI definitions of the primary key constraint match and the key does not include a column that has either the LOB or LONG datatype, review the initial load files and verify the column is populated or not. Refer to the 'Copy OLTP Data' troubleshooting section for more details.

OLTP table does not have Primary Key Constraint

As previously mentioned, we introduce primary key constraints in the ODI model when a primary key may not exist in the original table. This primary key generally matches an existing unique index. Due to differences in patch and subversions for a given OLTP version, it is possible that the instance used when importing a unique index had a column that is not nullable but in another OLTP version, that column may be nullable. Unique indexes allow null values but primary keys do not. In this case, a unique index will be created for the SDS table but the primary key constraint will fail to be created. Oracle GoldenGate will use the first unique index it finds (based on the index name) to identify a record for update - if the index that the primary key constraint is based on is not the first index, rename this index in ODI to ensure it will be the first. Generally, the first unique index is the correct index to use to identify a record in which case this error can be ignored.

"cannot CREATE UNIQUE INDEX; duplicate keys found"

Due to differences in patch and subversions for a given OLTP version, it is possible that the instance used when importing a unique index uses a different combination of columns than are used in your particular version of the same OLTP. For example, the OLTP subversion used to import an index uses 3 columns to define the unique index but a later subversion uses 4 columns and you are using this later subversion. Check the definition of the unique index in your OLTP instance and modify the Index script and corresponding constraint definition in ODI to match.

4.- Oracle Golden Gate errors

WARNING OGG-08869: "No unique key is defined for table '%'.  All viable columns will be used to represent the key, but may not guarantee uniqueness."

Ensure indexes were created on the SDS tables after they were populated but before Oracle Golden Gate started replicating changes.  See the following section:
Business Intelligence Applications Administrators Guide (11.1.1.8.1)
7 Administering Oracle GoldenGate and Source Dependent Schemas
7.4.4 Setup Step: generate, Deploy and Populate the Source Dependent Schema tables on Target Database

References

Internal Bug:20264393 - ORACLE GOLDENGATE REPLICATION ERRORS USING ODI W/OBI 11.1.1.8.1

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms