8.1 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
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.
password; GRANT CONNECT, RESOURCE, DBA TO
From the Oracle GoldenGate installation directory on each system, run GGSCI.
In GGSCI, issue the following command on each system.
EDIT PARAMS ./GLOBALS
GLOBALSfile, enter the
GGSCHEMAparameter and specify the schema of the DDL user that you created earlier in this procedure.
Save and close the files.
In SQL*Plus on both systems, run the
sequence.sqlscript 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.
In SQL*Plus on the source system, grant
EXECUTEprivilege on the
updateSequenceprocedure to a database user that can be used to issue the
DBLOGINcommand. Remember or record this user. You use
DBLOGINto log into the database prior to issuing the
FLUSH SEQUENCEcommand, which calls the procedure.
GRANT EXECUTE on
In SQL*Plus on the target system, grant
EXECUTEprivilege on the
replicateSequenceprocedure to the Replicat database user.
GRANT EXECUTE on
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.
sequence.sqlscript 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
FLUSH SEQUENCEcommand 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
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
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
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.
- Create a
TABLEstatement and a
MAPstatement for the georaster tables and also for the related raster data tables.
- If the
METADATAattribute of the
SDO_GEORASTERdata type in any of the values exceeds 1 MB, use the
DBOPTIONSparameter with the
XMLBUFSIZEoption to increase the size of the memory buffer that stores the embedded
SYS.XMLTYPEattribute of the
SDO_GEORASTERdata type. If the buffer is too small, Extract abends. See
XMLBUFSIZEin Reference for Oracle GoldenGate.
- To ensure the integrity of the target georaster tables and the spatial data, keep the trigger enabled on both source and target. Use the
REPERRORoption of the
MAPparameter 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:
REPERRORstatement in each
MAPstatement 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
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
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
REPERRORhandling the 1403 errors).
again to the exceptions table, which captures the 1403 error and other relevant information by means of a
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
SOURCETIMEZONE parameter in the Replicat parameter file.
To force Replicat to set its session to the target time zone, use the
To prevent Oracle GoldenGate from abending on
TIMESTAMP WITH TIME
TZR, use the Extract parameter
TIMESTAMP WITH TIMEZONE as
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.
Store large objects out of row if possible.
(Applies only to integrated capture) Integrated capture captures LOBs from the redo log. For
UPDATEoperations 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
TRANLOGOPTIONSparameter with the
FETCHPARTIALLOBoption 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.
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
UPDATEoperations on an XML document, only the changed portion of the XML is logged if it is stored as
BINARY. To force whole XML documents to be written to the trail when only the changed portion is logged, use the
TRANLOGOPTIONSparameter with the
FETCHPARTIALXMLoption 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 188.8.131.52.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
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
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
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.
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
To replicate DDL for sequences (
To replicate just sequence values, use the
8.4 Controlling the Checkpoint Frequency
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:
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:
SETTAGoption 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
TRANLOGOPTIONSparameter with the
EXCLUDETAGoption in a classic or integrated Extract parameter file. The logmining server associated with that Extract excludes redo that is tagged with the
EXCLUDETAGstatements can be used to exclude different tag values, if desired.
For Oracle to Oracle, this is the recommended method.
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
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
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
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.