9 Additional Oracle GoldenGate Configuration Considerations

This chapter contains additional configuration considerations that may apply to your database environment.

Topics:

9.1 Ensuring Row Uniqueness in Source and Target Tables

Oracle GoldenGate requires a unique row identifier on the source and target tables to locate the correct target rows for replicated updates and deletes.

Unless a KEYCOLS clause is used in the TABLE or MAP statement, Oracle GoldenGate selects a row identifier to use in the following order of priority, depending on the number and type of constraints that were logged (see Configuring Logging Properties).

  1. Primary key if it does not contain any extended (32K) VARCHAR2/NVARCHAR2 columns. Primary key without invisible columns.

  2. Unique key. Unique key without invisible columns.

    In the case of a nonintegrated Replicat, the selection of the unique key is as follows:

    • First unique key alphanumerically with no virtual columns, no UDTs, no function-based columns, no nullable columns, and no extended (32K) VARCHAR2/NVARCHAR2 columns. To support a key that contains columns that are part of an invisible index, you must use the ALLOWINVISIBLEINDEXKEYS parameter in the Oracle GoldenGate GLOBALS file.

    • First unique key alphanumerically with no virtual columns, no UDTs, no extended (32K) VARCHAR2/NVARCHAR2 columns, or no function-based columns, but can include nullable columns. To support a key that contains columns that are part of an invisible index, you must use the ALLOWINVISIBLEINDEXKEYS parameter in the Oracle GoldenGate GLOBALS file.

  3. If none of the preceding key types exist (even though there might be other types of keys defined on the table) Oracle GoldenGate constructs a pseudo key of all columns that the database allows to be used in a unique key, excluding virtual columns, UDTs, function-based columns, extended (32K) VARCHAR2/NVARCHAR2 columns, and any columns that are explicitly excluded from the Oracle GoldenGate configuration by an Oracle GoldenGate user.

    Unless otherwise excluded due to the preceding restrictions, invisible columns are allowed in the pseudo key.

Note:

If there are other, non-usable keys on a table or if there are no keys at all on the table, Oracle GoldenGate logs an appropriate message to the report file. Constructing a key from all of the columns impedes the performance of Oracle GoldenGate on the source system. On the target, this key causes Replicat to use a larger, less efficient WHERE clause.

If a table does not have an appropriate key, or if you prefer the existing key(s) not to be used, you can define a substitute key if the table has columns that always contain unique values. You define this substitute key by including a KEYCOLS clause within the Extract TABLE parameter and the Replicat MAP parameter. The specified key will override any existing primary or unique key that Oracle GoldenGate finds. For more information, see Reference for Oracle GoldenGate.

Parent topic: Additional Oracle GoldenGate Configuration Considerations

9.2 Installing Support for Oracle Sequences

To support Oracle sequences, you must install some database procedures.

These procedures support the Oracle GoldenGate FLUSH SEQUENCE command, which you issue immediately after you start the Oracle GoldenGate processes for the first time (typically when you perform the initial data synchronization procedure).

To Install Oracle Sequence Objects

You will perform steps on the source and target systems.

  1. In SQL*Plus, connect to the source and target Oracle systems as SYSDBA.
  2. If you already assigned a database user to support the Oracle GoldenGate DDL replication feature, you can skip this step. Otherwise, in SQL*Plus on both systems create a database user that can also be the DDL user. 
    CREATE USER DDLuser IDENTIFIED BY password; 
GRANT CONNECT, RESOURCE, DBA TO DDLuser;
  3. From the Oracle GoldenGate installation directory on each system, run GGSCI.
  4. In GGSCI, issue the following command on each system. 
    EDIT PARAMS ./GLOBALS
  5. In each GLOBALS file, enter the GGSCHEMA parameter and specify the schema of the DDL user that you created earlier in this procedure. 
    GGSCHEMA schema
  6. Save and close the files.
  7. In SQL*Plus on both systems, run the sequence.sql script from the root of the Oracle GoldenGate installation directory. This script creates some procedures for use by Oracle GoldenGate processes. (Do not run them yourself.) You are prompted for the user information that you created in the first step. 
    @sequence.sql
  8. In SQL*Plus on the source system, grant EXECUTE privilege on the updateSequence procedure to a database user that can be used to issue the DBLOGIN command. Remember or record this user. You use DBLOGIN to log into the database prior to issuing the FLUSH SEQUENCE command, which calls the procedure. 
    GRANT EXECUTE on DDLuser.updateSequence TO DBLOGINuser;
  9. In SQL*Plus on the target system, grant EXECUTE privilege on the replicateSequence procedure to the Replicat database user. 
    GRANT EXECUTE on DDLuser.replicateSequence TO Replicatuser;
  10. In SQL*Plus on the source system, issue the following statement in SQL*Plus. 
    ALTER TABLE sys.seq$ ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

Parent topic: Additional Oracle GoldenGate Configuration Considerations

9.3 Handling Special Data Types

It addresses special configuration requirements for different Oracle data types

This section applies whether Extract operates in classic or integrated capture mode, unless otherwise noted.

Parent topic: Additional Oracle GoldenGate Configuration Considerations

9.3.1 Multibyte Character Types

Multi-byte characters are supported as part of a supported character set. If the semantics setting of an Oracle source database is BYTE and the setting of an Oracle target is CHAR, use the Replicat parameter SOURCEDEFS in your configuration, and place a definitions file that is generated by the DEFGEN utility on the target. These steps are required to support the difference in semantics, whether or not the source and target data definitions are identical. Replicat refers to the definitions file to determine the upper size limit for fixed-size character columns.

For more information about character-set support, see Administering Oracle GoldenGate..

For information about SOURCEDEFS and the DEFGEN utility, see Administering Oracle GoldenGate.

Parent topic: Handling Special Data Types

9.3.2 Oracle Spatial Objects

To replicate tables that contain one or more columns of SDO_GEORASTER object type from an Oracle source to an Oracle target, follow these instructions to configure Oracle GoldenGate to process them correctly.

  1. Create a TABLE statement and a MAP statement for the georaster tables and also for the related raster data tables.
  2. If the METADATA attribute of the SDO_GEORASTER data type in any of the values exceeds 1 MB, use the DBOPTIONS parameter with the XMLBUFSIZE option to increase the size of the memory buffer that stores the embedded SYS.XMLTYPE attribute of the SDO_GEORASTER data type. If the buffer is too small, Extract abends. See XMLBUFSIZE in Reference for Oracle GoldenGate.
  3. To ensure the integrity of the target georaster tables and the spatial data, keep the trigger enabled on both source and target. Use the REPERROR option of the MAP parameter to handle the "ORA-01403 No data found" error that occurs as a result of keeping the trigger enabled on the target. It occurs when a row in the source georaster table is deleted, and the trigger cascades the delete to the raster data table. Both deletes are replicated. The replicated parent delete triggers the cascaded (child) delete on the target. When the replicated child delete arrives, it is redundant and generates the error. To use REPERROR, do the following:

    • Use a REPERROR statement in each MAP statement that contains a raster data table.

    • Use Oracle error 1403 as the SQL error.

    • Use any of the response options as the error handling.

A sufficient way to handle the errors on raster tables caused by active triggers on target georaster tables is to use REPERROR with DISCARD to discard the cascaded delete that triggers them. The trigger on the target georaster table performs the delete to the raster data table, so the replicated one is not needed.

MAP geo.st_rdt, TARGET geo.st_rdt, REPERROR (-1403, DISCARD) ;

If you need to keep an audit trail of the error handling, use REPERROR with EXCEPTION to invoke exceptions handling. For this, you create an exceptions table and map the source raster data table twice:

  • once to the actual target raster data table (with REPERROR handling the 1403 errors).

  • again to the exceptions table, which captures the 1403 error and other relevant information by means of a COLMAP clause.

For more information about using an exceptions table, see Administering Oracle GoldenGate for Windows and UNIX.

For more information about REPERROR options, see Reference for Oracle GoldenGate.

Parent topic: Handling Special Data Types

9.3.3 TIMESTAMP

To replicate timestamp data, follow these guidelines.

  1. To prevent Oracle GoldenGate from abending on TIMESTAMP WITH TIME ZONE as TZR, use the Extract parameter TRANLOGOPTIONS with one of the following:

    • INCLUDEREGIONID to replicate TIMESTAMP WITH TIME ZONE as TZR from an Oracle source to an Oracle target of the same version or later.

    • INCLUDEREGIONIDWITHOFFSET to replicate TIMESTAMP WITH TIMEZONE as TZR from an Oracle source that is at least v10g to an earlier Oracle target, or from an Oracle source to a non-Oracle target.

    These options allow replication to Oracle versions that do not support TIMESTAMP WITH TIME ZONE as TZR and to database systems that only support time zone as a UTC offset. .

  2. Oracle Database normalizes TIMESTAMP WITH LOCAL TIME ZONE data to the local time zone of the database that receives it, the target database in the case of Oracle GoldenGate. To preserve the original time stamp of the data that it applies, Replicat sets its session to the time zone of the source database. You can override this default and supply a different time zone by using the SOURCETIMEZONE parameter in the Replicat parameter file. To force Replicat to set its session to the target time zone, use the PRESERVETARGETTIMEZONE parameter.

    You can also use the SOURCETIMEZONE parameter to specify the source time zone for data that is captured by an Extract that is earlier than version 12.1.2. Those versions do not write the source time zone to the trail.

Parent topic: Handling Special Data Types

9.3.4 Large Objects (LOB)

The following are some configuration guidelines for LOBs in both classic capture and integrated capture mode.

  1. Store large objects out of row if possible.

  2. (Applies only to integrated capture) Integrated capture captures LOBs from the redo log. For UPDATE operations on a LOB document, only the changed portion of the LOB is logged. To force whole LOB documents to be written to the trail when only the changed portion is logged, use the TRANLOGOPTIONS parameter with the FETCHPARTIALLOB option in the Extract parameter file. When Extract receives partial LOB content from the logmining server, it fetches the full LOB image instead of processing the partial LOB. Use this option when replicating to a non-Oracle target or in other conditions where the full LOB image is required.

Parent topic: Handling Special Data Types

9.3.5 XML

The following are tools for working with XML within Oracle GoldenGate constraints.

  • Although both classic and integrated capture modes do not support the capture of changes made to an XML schema, you may be able to evolve the schemas and then resume replication of them without the need for a resynchronization, see Supporting Changes to XML Schemas.

  • (Applies only to integrated capture) Integrated capture captures XML from the redo log. For UPDATE operations on an XML document, only the changed portion of the XML is logged if it is stored as OBJECT RELATIONAL or BINARY. To force whole XML documents to be written to the trail when only the changed portion is logged, use the TRANLOGOPTIONS parameter with the FETCHPARTIALXML option in the Extract parameter file. When Extract receives partial XML content from the logmining server, it fetches the full XML document instead of processing the partial XML. Use this option when replicating to a non-Oracle target or in other conditions where the full XML image is required.

Parent topic: Handling Special Data Types

9.3.6 User Defined Types

If Oracle Database is compatible with releases greater than or equal to 12.0.0.0.0, then integrated Extract captures data from redo (no fetch), see Setting Flashback Query.

If replicating source data that contains user-defined types with the NCHAR, NVARCHAR2, or NCLOB attribute to an Oracle target, use the HAVEUDTWITHNCHAR parameter in the Replicat parameter file. When this type of data is encountered in the trail, HAVEUDTWITHNCHAR causes Replicat to connect to the Oracle target in AL32UTF8, which is required when a user-defined data type contains one of those attributes. HAVEUDTWITHNCHAR is required even if NLS_LANG is set to AL32UTF8 on the target. By default Replicat ignores NLS_LANG and connects to an Oracle Database in the native character set of the database. Replicat uses the OCIString object of the Oracle Call Interface, which does not support NCHAR, NVARCHAR2, or NCLOB attributes, so Replicat must bind them as CHAR. Connecting to the target in AL32UTF8 prevents data loss in this situation. HAVEUDTWITHNCHAR must appear before the USERID or USERIDALIAS parameter in the parameter file.

Parent topic: Handling Special Data Types

9.4 Handling Other Database Properties

This topic describes the database properties that may affect Oracle GoldenGate and the parameters that you can use to resolve or work around the condition.

The following table lists the database properties and the associated concern/resolution.

Database Property Concern/Resolution

Table with interval partitioning

To support tables with interval partitioning, make certain that the WILDCARDRESOLVE parameter remains at its default of DYNAMIC.

Table with virtual columns

Virtual columns are not logged, and Oracle does not permit DML on virtual columns. You can, however, capture this data and map it to a target column that is not a virtual column by doing the following:

Include the table in the Extract TABLE statement and use the FETCHCOLS option of TABLE to fetch the value from the virtual column in the database.

In the Replicat MAP statement, map the source virtual column to the non-virtual target column.

Table with inherently updateable view

To replicate to an inherently updateable view, define a key on the unique columns in the updateable view by using a KEYCOLS clause in the same MAP statement in which the associated source and target tables are mapped.

Redo logs or archives in different locations

The TRANLOGOPTIONS parameter contains options to handle environments where the redo logs or archives are stored in a different location than the database default or on a different platform from that on which Extract is running. These options may be required when Extract operates in classic capture mode. For more information, see Reference for Oracle GoldenGate.

TRUNCATE operations

To replicate TRUNCATE operations, choose one of two options:

  • Standalone TRUNCATE support by means of the GETTRUNCATES parameter replicates TRUNCATE TABLE, but no other TRUNCATE options. Use only if not using Oracle GoldenGate DDL support.

  • The full DDL support replicates TRUNCATE TABLE, ALTER TABLE TRUNCATE PARTITION, and other DDL. To install this support, see Installing Trigger-Based DDL Capture..

Sequences

To replicate DDL for sequences (CREATE, ALTER, DROP, RENAME), use Oracle GoldenGate DDL support.

To replicate just sequence values, use the SEQUENCE parameter in the Extract parameter file. This does not require the Oracle GoldenGate DDL support environment. For more information, see Reference for Oracle GoldenGate.

Parent topic: Additional Oracle GoldenGate Configuration Considerations

9.5 Controlling the Checkpoint Frequency

The CHECKPOINTRETENTIONTIME option of the TRANLOGOPTIONS parameter controls the number of days that Extract in integrated mode retains checkpoints before purging them automatically.

Partial days can be specified using decimal values. For example, 8.25 specifies 8 days and 6 hours. The default is seven days. For more information about this parameter, see Reference for Oracle GoldenGate.

Parent topic: Additional Oracle GoldenGate Configuration Considerations

9.6 Excluding Replicat Transactions

In a bidirectional configuration, Replicat must be configured to mark its transactions, and Extract must be configured to exclude Replicat transactions so that they do not propagate back to their source.

There are two methods to accomplish this as follows:

Method 1

Valid only for Oracle to Oracle implementations.

When Extract is in classic or integrated mode (Replicat can be in either integrated or nonintegrated mode), use the following parameters:

  • Use DBOPTIONS with the SETTAG option in the Replicat parameter file. The inbound server tags the transactions of that Replicat with the specified value, which identifies those transactions in the redo stream. The default value for SETTAG is 00.

  • Use the TRANLOGOPTIONS parameter with the EXCLUDETAG option in a classic or integrated Extract parameter file. The logmining server associated with that Extract excludes redo that is tagged with the SETTAG value. Multiple EXCLUDETAG statements can be used to exclude different tag values, if desired.

    For Oracle to Oracle, this is the recommended method.

Method 2

Valid for any implementation; Oracle or heterogeneous database configurations.

Alternatively, when Extract is in classic or integrated capture mode, you could also use the Extract TRANLOGOPTIONS parameter with the EXCLUDEUSER or EXCLUDEUSERID option to ignore Replicat the DDL and DML transactions based on its user name or ID. Multiple EXCLUDEUSER statements can be used. The specified user is subject to the rules of the GETREPLICATES or IGNOREREPLICATES parameter.

For more information, see Reference for Oracle GoldenGate.

Parent topic: Additional Oracle GoldenGate Configuration Considerations

9.7 Advanced Configuration Options for Oracle GoldenGate

You may need to configure Oracle GoldenGate with advanced options to suit your business needs.

See the following:

  • For additional configuration guidelines to achieve specific replication topologies, see Administering Oracle GoldenGate. This guide includes instructions for the following configurations:

    • Using Oracle GoldenGate for live reporting

    • Using Oracle GoldenGate for real-time data distribution

    • Configuring Oracle GoldenGate for real-time data warehousing

    • Using Oracle GoldenGate to maintain a live standby database

    • Using Oracle GoldenGate for active-active high availability

    That guide also contains information about:

    • Oracle GoldenGate architecture

    • Oracle GoldenGate commands

    • Oracle GoldenGate initial load methods

    • Configuring security

    • Using customization features

    • Configuring data filtering and manipulation

  • If either the source or target database is non-Oracle, follow the installation and configuration instructions in the Oracle GoldenGate installation and setup guide for that database, and then refer to the Oracle GoldenGate administration and reference documentation for further information.

Parent topic: Additional Oracle GoldenGate Configuration Considerations