8 Additional Oracle GoldenGate Configuration Considerations

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


8.1 Installing Support for Oracle Sequences

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

To Install Oracle Sequence Objects

  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.
  3. From the Oracle GoldenGate installation directory on each system, run GGSCI.

  4. In GGSCI, issue the following command on each system.

  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.

  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.


To capture the sequence from a multitenant database

  1. Create an Oracle GoldenGate user in each PDB that you need to capture sequences from.

  2. Add the user to the GLOBALS parameter file. It is easier if you use the same user for each PDB, if you don't then you need to change the GLOBALS file each time you do step 3.

  3. Run the sequence.sql script on each PDB using the user created in step 1.

  4. Log into Admin Client or GGSCI.

  5. Connect to the root container on the source using DBLOGIN.

  6. Issue the FLUSH SEQUENCE command for each PDB.

If replicating sequences into a multitenant database:
  1. On the target, create a user as created in step 1 in the previous section, for each PDB you are replicating sequences into.

  2. Connect to the PDB using that user and run the sequence.sql script.

If you don't want to keep these database accounts, you can drop the user or deactivate the account.

Here is an example of the entire process:

Environment information
            OGG 19.1 Oracle 12c to Oracle 12c Replication, Integrated 
            Extract, Parallel Replicat
            Source: CDB GOLD, PDB CERTMISSN 
            Target: CDB PLAT, PDB CERTDSQ 
            Source Oracle GoldenGate Configuration
            Container User: C##GGADMIN 
            PDB User for Sequences: GGATE 
sqlplus / as sysdba 
            SQL> alter session set container=CERTMISSN; 
            SQL> create user ggate identified by password default tablespace 
users temporary tablespace temp quota unlimited on users container=current;
Run @sequence 
    sqlplus / as sysdba 
    SQL> alter session set container=CERTMISSN; 
    SQL> @sequence

When prompted enter GGATE

            GGSCHEMA GGATE 
         Flush Sequence 
Target OGG Configuration 
         PDB User: GGATE 
         Run @sequence 
                sqlplus / as sysdba 
                SQL> alter session set container=CERTDSQ;
                SQL> @sequence

When prompted enter GGATE.

8.2 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.

8.2.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.

8.2.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.


To replicate timestamp data, Oracle Database normalizes TIMESTAMP WITH LOCAL TIME ZONE data to the local time zone of the database that receives it, the target database in 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.

To prevent Oracle GoldenGate from abending on TIMESTAMP WITH TIME ZONE as TZR, use the Extract parameter TRANLOGOPTIONS with INCLUDEREGIONIDWITHOFFSET to replicate TIMESTAMP WITH TIMEZONE as TZR from an Oracle source that is at least version 10g to an earlier Oracle target, or from an Oracle source to a non-Oracle target. This option allows replicating 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.

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.

8.2.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.

8.2.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.

8.2.6 User Defined Types

If Oracle Database is compatible with releases greater than or equal to, 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.

8.3 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..


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.

8.4 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.

8.5 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.

8.6 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.