4 Configuring Oracle GoldenGate Replicat

This chapter contains instructions for configuring the Replicat apply process in either nonintegrated or integrated mode.

Topics:

• Choosing from Different Replicat Modes
  
• Prerequisites for Configuring Replicat
  This topic provides the best practices for configuring Replicat.
• About Checkpoint Table
  
• Configuring Replicat
  Configure a Replicat process to configure Replicat for a pluggable database (PDB). Replicat can operate in any of the available modes within an Oracle multitenant container database.
• Additional Configuration Steps For Using Nonintegrated Replicat
  This chapter contains instructions that are specific only to Replicat when operating in nonintegrated mode. When Replicat operates in nonintegrated mode, triggers, cascade constraints, and unique identifiers must be properly configured in an Oracle GoldenGate environment.
• 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.

Choosing from Different Replicat Modes

Learn about the different Replicat modes for your database environment.

Topics:

• Deciding Which Apply Method to Use
  The Replicat process is responsible for the application of replicated data to an Oracle target database.
• Using Different Replicat Modes with Extract
  The recommended Oracle GoldenGate configuration, when supported by the Oracle version, is to use one Extract on an Oracle source and one parallel Replicat per source database on an Oracle target.

Deciding Which Apply Method to Use

The Replicat process is responsible for the application of replicated data to an Oracle target database.

For an Oracle target database, you can run Replicat in parallel, non-integrated or integrated mode. Oracle recommends that you use the parallel Replicat unless a specific feature requires a different type of Replicat.

The following table lists the features supported by the respective Replicats.

Feature	Parallel Replicat	Integrated Replicat	Coordinated Replicat	Classic Replicat
Batch Processing	Yes	Yes	Yes	Yes
Barrier Transactions	Yes	Yes	Yes	No
Dependency Computation	Yes	Yes	No	No
Auto-parallelism Note: Auto-parallelism is disabled, by default. Only four threads are used in the default settings. If you want to change Replicat to use MIN_PARALLELISM and MAX_PARALLELISM then auto-parallelism is used.	Yes	Yes	No	No
DML Handler	Yes, Integrated mode	Yes	No	No
Procedural Replication	Yes, used for integrated Parallel Replicat (iPR)	Yes	No	No
Auto CDR	Yes, used by iPR only	Yes	No	No
Dependency-aware Transaction Split	Yes	No	No	No
Cross-RAC-node Processing	Yes	No	Yes	No
ALLOWDUPTARGETMAP See ALLOWDUPTARGETMAP | NOALLOWDUPTARGETMAP	No. Oracle Database with iPR	No, Oracle Database	Yes	Yes

Topics:

• About Parallel Replicat
  
• About Non-integrated Replicat
  
• About Integrated Replicat
  

About Parallel Replicat

Parallel Replicat is another variant of Replicat that applies transactions in parallel to improve performance.

It takes into account dependencies between transactions, similar to Integrated Replicat. The dependency computation, parallelism of the mapping and apply is performed outside the database so can be off-loaded to another server. The transaction integrity is maintained in this process. In addition, parallel Replicat supports the parallel apply of large transactions by splitting a large transaction into chunks and applying them in parallel.

Note:

For best performance for an OLTP workload, parallel Replicat in non-integrated mode is recommended.

Only Oracle database supports parallel Replicat and integrated parallel Replicat. However, parallel Replicat supports all databases when using the non-integrated option.

To use parallel Replicat, you need to ensure that you have the following values, which are also the default values:

• Metadata in the trail (which means you can't use parallel Replicat if your trails are formatted below 12.1.

• Scheduling columns in the trail file.

• UPDATERCORDFORMAT COMPACT parameter.

With integrated parallel Replicat, the Replicat sends the LCRs to the inbound server, which applies the data to the target database, and in regular parallel Replicat, Oracle GoldenGate applies the LCR as a SQL statement directly to the database, similar to how the other non-integrated Replicats work.

The components of parallel Replicat are:

• Mappers operate in parallel to read the trail, map trail records, convert the mapped records to the Integrated Replicat LCR format, and send the LCRs to the Merger for further processing. While one Mapper maps one set of transactions, the next Mapper maps the next set of transactions. The the trail information is split and the trail file is untouched because it orders trail information in order.

• Master processes have two threads, Collater and Scheduler. The Collater receives mapped transactions from the Mappers and puts them back into trail order for dependency calculation. The Scheduler calculates dependencies between transactions, groups transactions into independent batches, and sends the batches to the Appliers to be applied to the target database.

• Appliers reorder records within a batch for array execution. It applies the batch to the target database and performs error handling. It also tracks applied transactions in checkpoint tables.

Note:

Parallel Replicat requires that any foreign key columns are indexed.

• Benefits of Parallel Replicat
  
• Parallel Replication Architecture
  Parallel replication processes leverage the apply processing functionality that is available within the Oracle Database in integrated mode.

Benefits of Parallel Replicat

The following are the benefits of using parallel Replicat.

• Integrated Parallel Replicat enables heavy workloads to be partitioned automatically among parallel apply processes that apply multiple transactions concurrently, while preserving the integrity and atomicity of the source transaction. Both a minimum and maximum number of apply processes can be configured with the PARALLELISM and MAX_PARALLELISM parameters. Replicat automatically adds additional servers when the workload increases, and then adjusts downward again when the workload lightens.

• Integrated Parllel Replicat requires minimal work to configure. All work is configured within one Replicat parameter file, without configuring range partitions.

• High-performance apply streaming is enabled for integrated parallel Replicat by means of a lightweight application programming interface (API) between Replicat and the inbound server.

• Barrier transactions are coordinated by integrated parallel Replicat among multiple server apply processes.

• DDL operations are processed as direct transactions that force a barrier by waiting for server processing to complete before the DDL execution.

• Transient duplicate primary key updates are handled by integrated parallel Replicat in a seamless manner.

• Parallel Replicat can break a single large transaction into smaller chunks and apply those chunks in parallel. See the SPLIT_TRANS_RECS for details.

Parallel Replication Architecture

Parallel replication processes leverage the apply processing functionality that is available within the Oracle Database in integrated mode.

Within a single Replicat configuration, multiple inbound server child processes, known as apply servers, apply transactions in parallel while preserving the original transaction atomicity.

The following architecture diagram depicts the flow of change records through the various processes of a parallel replication from the trail files to the target database, for a non-integrated parallel Replicat.

Description of the illustration para_rep_arch.png

The Mappers read the trail file and map records, forward the mapped records to the Master. The batches are sent to the Appliers where they are applied to the target database.

The Master process consists of two separate threads, Collater and Scheduler. The Collater is responsible for managing and communicating with the Mappers, along with receiving the mapped transactions and reordering them into a single in-order stream. The Scheduler is responsible for managing and communicating with the Appliers, along with reading transactions from the Collater, batching them, and scheduling them to Appliers.

The Scheduler controller communicates with the Scheduler to gather any necessary information (such as, the current low watermark position). The Scheduler controller is required for CDB mode for Oracle Database because it is responsible for aggregating information pertaining to the different target PDBs and reporting a unified picture. The Scheduler controller is created for simplicity and uniformity of implementation, even when not in CDB mode. Every process reads the parameter file and shares a single checkpoint file.

About Non-integrated Replicat

In non-integrated mode, the Replicat process uses standard SQL to apply data directly to the target tables. In this mode, Replicat operates as follows:

• Reads the Oracle GoldenGate trail.

• Performs data filtering, mapping, and conversion.

• Constructs SQL statements that represent source database DML or DDL transactions (in committed order).

• Applies the SQL to the target through Oracle Call Interface (OCI).

The following diagram illustrates the configuration of Replicat in non-integrated mode.

Description of the illustration nonintegratedapply.png

Use non-integrated Replicat when you want to make heavy use of features that are not supported in integrated Replicat mode, see About Integrated Replicat.

You can apply transactions in parallel with a non-integrated Replicat by using a coordinated Replicat configuration.

About Integrated Replicat

In integrated mode, the Replicat process leverages the apply processing functionality that is available within the Oracle Database. In this mode, Replicat operates as follows:

• Reads the Oracle GoldenGate trail.

• Performs data filtering, mapping, and conversion.

• Constructs logical change records (LCR) that represent source database DML transactions (in committed order). DDL is applied directly by Replicat.

• Attaches to a background process in the target database known as a database inbound server by means of a lightweight streaming interface.

• Transmits the LCRs to the inbound server, which applies the data to the target database.

The following figure illustrates the configuration of Replicat in integrated mode.

Description of the illustration integratedapply.png

Within a single Replicat configuration, multiple inbound server child processes known as apply servers apply transactions in parallel while preserving the original transaction atomicity. You can increase this parallelism as much as your target system will support when you configure the Replicat process or dynamically as needed. The following diagram illustrates integrated Replicat configured with two parallel apply servers.

Description of the illustration integratedapply_detail.png

Integrated Replicat applies transactions asynchronously. Transactions that do not have interdependencies can be safely executed and committed out of order to achieve fast throughput. Transactions with dependencies are guaranteed to be applied in the same order as on the source.

A reader process in the inbound server computes the dependencies among the transactions in the workload based on the constraints defined at the target database (primary key, unique, foreign key). Barrier transactions and DDL operations are managed automatically, as well. A coordinator process coordinates multiple transactions and maintains order among the apply servers.

If the inbound server does not support a configured feature or column type, Replicat disengages from the inbound server, waits for the inbound server to complete transactions in its queue, and then applies the transaction to the database in direct apply mode through OCI. Replicat resumes processing in integrated mode after applying the direct transaction.

The following features are applied in direct mode by Replicat:

• DDL operations

• Sequence operations

• SQLEXEC parameter within a TABLE or MAP parameter

• EVENTACTIONS processing

• UDT

  Note:

  By default, UDT's are applied with the inbound server. Only if NOUSENATIVEOBJSUPPORT is in place, then Extract handling is done by Replicat directly.

Because transactions are applied serially in direct apply mode, heavy use of such operations may reduce the performance of the integrated Replicat mode. Integrated Replicat performs best when most of the apply processing can be performed in integrated mode, see Monitoring and Controlling Processing After the Instantiation in Using Oracle GoldenGate for Oracle Database.

Note:

User exits are executed in integrated mode. However, user exit may produce unexpected results, if the exit code depends on data in the replication stream.

Note:

Integrated Replicat requires that any foreign key columns are indexed.

• Benefits of Integrated Replicat
  
• Integrated Replicat Requirements
  

Benefits of Integrated Replicat

The following are the benefits of using integrated Replicat versus nonintegrated Replicat.

• Integrated Replicat enables heavy workloads to be partitioned automatically among parallel apply processes that apply multiple transactions concurrently, while preserving the integrity and atomicity of the source transaction. Both a minimum and maximum number of apply processes can be configured with the PARALLELISM and MAX_PARALLELISM parameters. Replicat automatically adds additional servers when the workload increases, and then adjusts downward again when the workload lightens.

• Integrated Replicat requires minimal work to configure. All work is configured within one Replicat parameter file, without configuring range partitions.

• High-performance apply streaming is enabled for integrated Replicat by means of a lightweight application programming interface (API) between Replicat and the inbound server.

• Barrier transactions are coordinated by integrated Replicat among multiple server apply processes.

• DDL operations are processed as direct transactions that force a barrier by waiting for server processing to complete before the DDL execution.

• Transient duplicate primary key updates are handled by integrated Replicat in a seamless manner.

Integrated Replicat Requirements

To use integrated Replicat, the following must be true.

• Supplemental logging must be enabled on the source database to support the computation of dependencies among tables and scheduling of concurrent transactions on the target. Instructions for enabling the required logging are in Configuring Logging Properties. This logging can be enabled at any time up to, but before, you start the Oracle GoldenGate processes.

• Integrated Parallel Replicat is supported on Oracle Database 12.2.0.1 and greater.

Using Different Replicat Modes with Extract

The recommended Oracle GoldenGate configuration, when supported by the Oracle version, is to use one Extract on an Oracle source and one parallel Replicat per source database on an Oracle target.

One integrated Replicat configuration supports all Oracle data types either through the inbound server or by switching to direct apply when necessary, and it preserves source transaction integrity. You can adjust the parallelism settings to the desired apply performance level as needed.

Each Extract group must process objects that are suited to the processing mode, based on table data types and attributes. No objects in one Extract can have DML or DDL dependencies on objects in the other Extract. The same type of segregation must be applied to the Replicat configuration.

If the target database is an Oracle version that does not support integrated Replicat, or if it is a non-Oracle database, you can use a coordinated or parallel Replicat configuration.

Prerequisites for Configuring Replicat

This topic provides the best practices for configuring Replicat.

The guidelines to follow before configuring Replicat are:

1. Preparing the Database for Oracle GoldenGate.

2. Establishing Oracle GoldenGate Credentials.

3. Choosing from Different Replicat Modes.

4. Create the Oracle GoldenGate instance on the target system by configuring the Manager process.

See Prerequisites for Configuring Replicat.

Also see, Quickstart Bidirectional Replication to learn about using the active-active replication process in case of bidirectional replication.

About Checkpoint Table

The checkpoint table is a required component of Replicat.

A Replicat maintains its recovery checkpoints in the checkpoint table, which is stored in the target database. Checkpoints are written to the checkpoint table within the Replicat transaction. Because a checkpoint either succeeds or fails with the transaction, Replicat ensures that a transaction is only applied once, even if there is a failure of the process or the database. See Before You Add a Replicat in the Oracle GoldenGate Microservices Documentation to learn to create checkpoint tables from the Microservices web interface.

Note:

Oracle recommends using checkpoint tables. Multiple classic or coordinated Replicats can share the same checkpoint table, but that may not result in the best performance. With high volume environments, you must ensure that the checkpoint tables do not reside on different drives to become a point of conflict.

See Instantiating Oracle GoldenGate Replication for more information.

• Include the Checkpoint Table in the GLOBALS File
  
• Disabling Default Asynchronous COMMIT to Checkpoint Table
  
• Controlling the Checkpoint Retention
  The CHECKPOINTRETENTIONTIME option of the TRANLOGOPTIONS parameter controls the number of days that Extract retains checkpoints before purging them automatically.

Include the Checkpoint Table in the GLOBALS File

To specify the checkpoint table in the Oracle GoldenGate configuration:

1. Create a GLOBALS file (or edit the existing one).

   EDIT PARAMS ./GLOBALS

   Note:

   EDIT PARAMS creates a simple text file. When you save the file after EDIT PARAMS, it is saved with the name GLOBALS in upper case, without a file extension. It must remain as such, and the file must remain in the root Oracle GoldenGate directory.

2. In the GLOBALS file, enter the CHECKPOINTTABLE parameter.

   CHECKPOINTTABLE [container.]schema.table
   

3. Save and close the GLOBALS file.

Disabling Default Asynchronous COMMIT to Checkpoint Table

When a nonintegrated Replicat uses a checkpoint table, it uses an asynchronous COMMIT with the NOWAIT option to improve performance. Replicat can continue processing immediately after applying this COMMIT, while the database logs the transaction in the background. You can disable the asynchronous COMMIT with NOWAIT by using the DBOPTIONS parameter with the DISABLECOMMITNOWAIT option in the Replicat parameter file.

Note:

When the configuration of a nonintegrated Replicat group does not include a checkpoint table, the checkpoints are maintained in a file on disk. In this case, Replicat uses COMMIT with WAIT to prevent inconsistencies in the event of a database failure that causes the state of the transaction, as in the checkpoint file, to be different than its state after the recovery.

Controlling the Checkpoint Retention

The CHECKPOINTRETENTIONTIME option of the TRANLOGOPTIONS parameter controls the number of days that Extract 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.

Configuring Replicat

Configure a Replicat process to configure Replicat for a pluggable database (PDB). Replicat can operate in any of the available modes within an Oracle multitenant container database.

To add a Replicat from the command line interface, see the ADD REPLICAT from GGSCI.

Use the following steps to configure the parameters for different Replicat modes.

1. On the target system, create the Replicat parameter file using GGSCI command line interface.

   EDIT PARAMS name

   Where: name is the name of the Replicat group.

2. Enter the Replicat parameters in the order shown, starting a new line for each parameter statement.

   Basic parameters for the Replicat group in nonintegrated mode:

   REPLICAT repe
   USERIDALIAS ggeast
   ASSUMETARGETDEFS
   MAP hr.*, TARGET hr2.*;
   

   Basic parameters for the Replicat group in integrated Replicat mode:

   REPLICAT repw
   DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
   USERIDALIAS ggwest
   ASSUMETARGETDEFS
   MAP hr.*, TARGET hr2.*;
   

   Parameter	Description
   REPLICAT group	group is the name of the Replicat group.
   DBOPTIONS DEFERREFCONST	Applies to Replicat in nonintegrated mode. DEFERREFCONST sets constraints to DEFERRABLE to delay the enforcement of cascade constraints by the target database until the Replicat transaction is committed. See DBOPTIONS for additional important information.
   DBOPTIONS INTEGRATEDPARAMS (parameter[, ...])	This parameter specification applies to Replicat in integrated mode. It specifies optional parameters for the inbound server. See Optional Parameters for Integrated Modesfor additional important information about these DBOPTIONS options.
   USERIDALIAS alias	Specifies the alias of the database login credential of the user that is assigned to Replicat. This credential must exist in the Oracle GoldenGate credential store. For more information, see Establishing Oracle GoldenGate Credentials
   MAP [container.]schema.object, TARGET schema.object;	Specifies the relationship between a source table or sequence, or multiple objects, and the corresponding target object or objects. MAP specifies the source table or sequence, or a wildcarded set of objects. TARGET specifies the target table or sequence or a wildcarded set of objects. container is the name of a container, if the source database is a multitenant container database. schema is the schema name or a wildcarded set of schemas. object is the name of a table or sequence, or a wildcarded set of objects. Terminate this parameter statement with a semi-colon. To exclude objects from a wildcard specification, use the MAPEXCLUDE parameter. For more information and for additional options that control data filtering, mapping, and manipulation, see MAP in Reference for Oracle GoldenGate.

   Basic parameters for the Replicat group in parallel Replicat mode:

   REPLICAT repe
   USERID ggadmin, PASSWORD ***
   MAP_PARALLELISM 3
   MIN_APPLY_PARALLELISM 2
   MAX_APPLY_PARALLELISM 10
   SPLIT_TRANS_RECS 60000
   MAP *.*, TARGET *.*;

   Parameter	Description
   MAP_PARALLELISM	Configures number of mappers. This controls the number of threads used to read the trail file. The minimum value is 1, maximum value is 100 and the default value is 2.
   APPLY_PARALLELISM	Configures number of appliers. This controls the number of connections in the target database used to apply the changes. The default value is four.
   MIN_APPLY_PARALLELISM MAX_APPLY_PARALLELISM	The Apply parallelism is auto-tuned. You can set a minimum and maximum value to define the ranges in which the Replicat automatically adjusts its parallelism. There are no defaults. Do not use with APPLY_PARALLELISM at same time.
   SPLIT_TRANS_REC	Specifies that large transactions should be broken into pieces of specified size and applied in parallel. Dependencies between pieces are still honored. Disabled by default.
   COMMIT_SERIALIZATION	Enables commit FULL serialization mode, which forces transactions to be committed in trail order.
   Advanced Parameters
   LOOK_AHEAD_TRANSACTIONS	Controls how far ahead the Scheduler looks when batching transactions. The default value is 10000.
   CHUNK_SIZE	Controls how large a transaction must be for parallel Replicat to consider it as large. When parallel Replicat encounters a transaction larger than this size, it will serialize it, resulting in decreased performance. However, increasing this value will also increase the amount of memory consumed by parallel Replicat.

3. If using integrated Replicat or parallel Replicat in integrated mode, add the following parameters to the Extract parameter file:

   • LOGALLSUPCOLS: This parameter ensures the capture of the supplementally logged columns in the before image. It's the default parameter and shouldn't be turned off or disabled. It is valid for any source database that is supported by Oracle GoldenGate. For Extract versions older than 12c, you can use GETUPDATEBEFORES and NOCOMPRESSDELETES parameters to satisfy the same requirement. The database must be configured to log the before and after values of the primary key, unique indexes, and foreign keys.

   • The UPDATERECORDFORMAT parameter set to COMPACT: This setting causes Extract to combine the before and after images of an UPDATE operation into a single record in the trail. This is the default option and it is recommended that you don't change the default setting.

4. Enter any optional Replicat parameters that are recommended for your configuration. You can edit this file at any point before starting processing by using the EDIT PARAMS command. See Optional Parameters for Integrated Modes for additional configuration considerations..
5. Save and close the file.

Additional Configuration Steps For Using Nonintegrated Replicat

This chapter contains instructions that are specific only to Replicat when operating in nonintegrated mode. When Replicat operates in nonintegrated mode, triggers, cascade constraints, and unique identifiers must be properly configured in an Oracle GoldenGate environment.

This chapter is a supplement to the basic configuration requirements that are documented in Configuring Oracle GoldenGate Replicat.

Topics:

• Disabling Triggers and Referential Cascade Constraints on Target Tables
  Triggers and cascade constraints must be disabled on Oracle target tables when Replicat is in nonintegrated mode.
• Deferring Constraint Checking on Target Tables
  When Replicat is in nonintegrated mode, you may need to defer constraint checking on the target.

Disabling Triggers and Referential Cascade Constraints on Target Tables

Triggers and cascade constraints must be disabled on Oracle target tables when Replicat is in nonintegrated mode.

Oracle GoldenGate provides some options to handle triggers or cascade constraints automatically, depending on the Oracle version:

• For Oracle 11.2.0.2 and later 11gR2 versions, Replicat automatically disables the work performed by triggers during its session. It does not disable a trigger, but instead prevents the trigger body from executing. The WHEN portion of the trigger must still compile and execute correctly to avoid database errors. To enable triggers to fire, or to disable them manually, use the NOSUPPRESSTRIGGERS option of DBOPTIONS and place the statement after the USERIDALIAS parameter. To allow a specific trigger to fire, you can use the following database procedure, where trigger_owner is the owner of the trigger and trigger_name is the name of the trigger. Once the procedure is called with FALSE for a particular trigger, it remains set until the procedure is called with TRUE.

• dbms_ddl.set_trigger_firing_property(trigger_owner "trigger_name", FALSE)
  

• For Oracle 11.2.0.2 and later 11gR2 versions, you can use the DBOPTIONS parameter with the DEFERREFCONST option to delay the checking and enforcement of cascade update and cascade delete constraints until the Replicat transaction commits.

• For other Oracle versions, you must disable triggers and integrity constraints or alter them manually to ignore the Replicat database user.

Constraints must be disabled in nonintegrated Replicat mode because Oracle GoldenGate replicates DML that results from the firing of a trigger or a cascade constraint. If the same trigger or constraint gets activated on the target table, it becomes redundant because of the replicated version, and the database returns an error. Consider the following example, where the source tables are emp_src and salary_src and the target tables are emp_targ and salary_targ.

1. A delete is issued for emp_src.

2. It cascades a delete to salary_src.

3. Oracle GoldenGate sends both deletes to the target.

4. The parent delete arrives first and is applied to emp_targ.

5. The parent delete cascades a delete to salary_targ.

6. The cascaded delete from salary_src is applied to salary_targ.

7. The row cannot be located because it was already deleted in step 5.

Deferring Constraint Checking on Target Tables

When Replicat is in nonintegrated mode, you may need to defer constraint checking on the target.

Perform the following steps to defer the constraints:

1. If constraints are DEFERRABLE on the source, the constraints on the target must also be DEFERRABLE. You can use one of the following parameter statements to defer constraint checking until a Replicat transaction commits:

   • Use SQLEXEC at the root level of the Replicat parameter file to defer the constraints for an entire Replicat session.

     SQLEXEC ("alter session set constraint deferred")

   • Use the Replicat parameter DBOPTIONS with the DEFERREFCONST option to delay constraint checking for each Replicat transaction.

2. You might need to configure Replicat to overcome integrity errors caused by transient primary-key duplicates. Transient primary-key duplicates are duplicates that occur temporarily during the execution of a transaction, but are resolved by transaction commit time. This kind of operation typically uses a SET x = x+n formula or some other manipulation that shifts values so that a new value equals an existing one.

   The following illustrates a sequence of value changes that can cause a transient primary-key duplicate if constraints are not deferred. The example assumes the primary key column is CODE and the current key values (before the updates) are 1, 2, and 3.

   update item set code = 2 where code = 1;
   update item set code = 3 where code = 2;
   update item set code = 4 where code = 3;
   

In this example, when Replicat applies the first update to the target, there is an ORA-00001 (unique constraint) error because the key value of 2 already exists in the table. The Replicat transaction returns constraint violation errors. By default, Replicat does not handle these violations and abends.

• Handling Transient Primary-key Duplicates in Version 11.2.0.4 or Later
  

Handling Transient Primary-key Duplicates in Version 11.2.0.4 or Later

For versions later than 11.2.0.4, a nonintegrated Replicat by default tries to resolve transient primary-key duplicates automatically by using a workspace in Oracle Workspace Manager. In this configuration, Replicat can defer the constraint checking until commit time without requiring the constraints to be explicitly defined as deferrable.

The requirements for automatic handling of transient primary-key duplicates are:

• Grant the Replicat database user access to the following Oracle function:

  DBMS_XSTREAM_GG.ENABLE_TDUP_WORKSPACE()

• The target tables cannot have deferrable constraints; otherwise Replicat returns an error and abends.

To handle tables with deferrable constraints, make certain the constraints are DEFERRABLE INITIALLY IMMEDIATE, and use the HANDLETPKUPDATE parameter in the MAP statement that maps that table. The HANDLETPKUPDATE parameter overrides the default of handling the duplicates automatically.The use of a workspace affects the following Oracle GoldenGate error handling parameters:

• HANDLECOLLISIONS

• REPERROR

When Replicat enables a workspace in Oracle Workspace Manager, it ignores the error handling that is specified by Oracle GoldenGate parameters such as HANDLECOLLISIONS and REPERROR. Instead, Replicat aborts its grouped transaction (if BATCHSQL is enabled), and then retries the update in normal mode by using the active workspace. If ORA-00001 occurs again, Replicat rolls back the transaction and then retries the transaction with error-handling rules in effect again.

Note:

If Replicat encounters ORA-00001 for a non-update record, the error-handling parameters such as HANDLECOLLISIONS and REPERROR handle it.

A workspace cannot be used if the operation that contains a transient primary-key duplicate also has updates to any out-of-line columns, such as LOB and XMLType. Therefore, these cases are not supported, and any such cases can result in undetected data corruption on the target. An example of this is:

update T set PK = PK + 1, C_LOB = 'ABC'; 

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.

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 an 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, you could use the Extract TRANLOGOPTIONS parameter with the EXCLUDEUSER or EXCLUDEUSERID option to ignore the Replicat 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.