Topics:
CHECKPOINTRETENTIONTIME
option of the TRANLOGOPTIONS
parameter controls the number of days that Extract in integrated mode retains checkpoints before purging them automatically.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).
Primary key if it does not contain any extended (32K) VARCHAR2/NVARCHAR2
columns. Primary key without invisible columns.
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.
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.
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.
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.
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
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.
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
To replicate timestamp data, follow these guidelines.
Parent topic: Handling Special Data Types
The following are some configuration guidelines for LOBs in both classic capture and integrated capture mode.
Store large objects out of row if possible.
(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
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
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
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 |
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 In the Replicat |
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 |
Redo logs or archives in different locations |
The |
|
To replicate
|
Sequences |
To replicate DDL for sequences ( To replicate just sequence values, use the |
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.
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.
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.