8 Additional Oracle GoldenGate Configuration Considerations
Topics:
- Installing Support for Oracle Sequences
To support Oracle sequences, you must install some database procedures. - Handling Special Data Types
It addresses special configuration requirements for different Oracle data types - 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. - Controlling the Checkpoint Frequency
TheCHECKPOINTRETENTIONTIME
option of theTRANLOGOPTIONS
parameter controls the number of days that Extract in integrated mode retains checkpoints before purging them automatically. - 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. - Advanced Configuration Options for Oracle GoldenGate
You may need to configure Oracle GoldenGate with advanced options to suit your business needs.
Installing Support for Oracle Sequences
To support Oracle sequences, you must install some database procedures.
To Install Oracle Sequence Objects
-
In SQL*Plus, connect to the source and target Oracle systems as
SYSDBA
. -
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 BYpassword
; GRANT CONNECT, RESOURCE, DBA TODDLuser
; -
From the Oracle GoldenGate installation directory on each system, run GGSCI.
-
In GGSCI, issue the following command on each system.
EDIT PARAMS ./GLOBALS
-
In each
GLOBALS
file, enter theGGSCHEMA
parameter and specify the schema of the DDL user that you created earlier in this procedure.GGSCHEMA
schema
-
Save and close the files.
-
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
-
In SQL*Plus on the source system, grant
EXECUTE
privilege on theupdateSequence
procedure to a database user that can be used to issue theDBLOGIN
command. Remember or record this user. You useDBLOGIN
to log into the database prior to issuing theFLUSH SEQUENCE
command, which calls the procedure.GRANT EXECUTE on
DDLuser
.updateSequence TODBLOGINuser
; -
In SQL*Plus on the target system, grant
EXECUTE
privilege on thereplicateSequence
procedure to the Replicat database user.GRANT EXECUTE on
DDLuser
.replicateSequence TOReplicatuser
; -
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;
To capture the sequence from a multitenant database
-
Create an Oracle GoldenGate user in each PDB that you need to capture sequences from.
-
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.
-
Run the
sequence.sql
script on each PDB using the user created in step 1. -
Log into Admin Client or GGSCI.
-
Connect to the root container on the source using
DBLOGIN
. -
Issue the
FLUSH SEQUENCE
command for each PDB.
-
On the target, create a user as created in step 1 in the previous section, for each PDB you are replicating sequences into.
-
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
GLOBALS
GGSCHEMA GGATE
Flush Sequence
GGSCI> DBLOGIN USERIDALIAS GGADMIN DOMAIN GOLD_QC_CDB$ROOT
GGSCI> FLUSH SEQUENCE CERTMISSN.SRCSCHEMA1.
Target OGG Configuration
PDB User: GGATE
Run @sequence
sqlplus / as sysdba
SQL> alter session set container=CERTDSQ;
SQL> @sequence
When prompted enter GGATE
.
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.
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.
Parent topic: Handling Special Data Types
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.
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
TIMESTAMP
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.
Parent topic: Handling Special Data Types
Large Objects (LOB)
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 theTRANLOGOPTIONS
parameter with theFETCHPARTIALLOB
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
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 asOBJECT RELATIONAL
orBINARY
. To force whole XML documents to be written to the trail when only the changed portion is logged, use theTRANLOGOPTIONS
parameter with theFETCHPARTIALXML
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
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
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 |
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 |
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.
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 theSETTAG
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 forSETTAG
is 00. -
Use the
TRANLOGOPTIONS
parameter with theEXCLUDETAG
option in a classic or integrated Extract parameter file. The logmining server associated with that Extract excludes redo that is tagged with theSETTAG
value. MultipleEXCLUDETAG
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.
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.