1. Microservices Architecture Documentation
  2. Prepare
  3. Prepare Databases
  4. Oracle
  5. Prepare Database Connection, System, and Parameter Settings

Prepare Database Connection, System, and Parameter Settings

Learn about configuring database connection, system, and parameter settings for Oracle GoldenGate for Oracle.

Enable GoldenGate Replication and Archive Mode

Step 1: Enable GoldenGate Replication

The database services required to support Oracle GoldenGate Extract and Replicat must be enabled explicitly for Oracle database.

To enable Oracle GoldenGate replication, set the following database initialization parameter. All instances in Oracle RAC must have this value set to TRUE if using Oracle GoldenGate on any of the nodes.

ENABLE_GOLDENGATE_REPLICATION=true

For more information about this parameter, see Initialization Parameters.

Steps 2: Enable the Archive Mode

Oracle Databases must be in ARCHIVELOG mode so that Extract can process the log files. To switch on the ARCHIVELOG mode, follow the steps provided in Changing the Database Archiving Mode.

Setting Flashback Query

To know about the data that Oracle GoldenGate fetches, see Details of Support for Oracle Data Types and Objects.

By default, Oracle GoldenGate uses Flashback Query to fetch the values from the undo (rollback) tablespaces. That way, Oracle GoldenGate can reconstruct a read-consistent row image as of a specific time or SCN to match the redo record.

For best fetch results, configure the source database as follows:

  1. Set a sufficient amount of redo retention by setting the Oracle initialization parameters UNDO_MANAGEMENT and UNDO_RETENTION as follows (in seconds).
    UNDO_MANAGEMENT=AUTO

UNDO_RETENTION=86400 

UNDO_RETENTION can be adjusted upward in high-volume environments.
  2. Calculate the space that is required in the undo tablespace by using the following formula. 
    undo_space = UNDO_RETENTION * UPS + overhead

    Where:

    • undo_space is the number of undo blocks.

    • UNDO_RETENTION is the value of the UNDO_RETENTION parameter (in seconds).

    • UPS is the number of undo blocks for each second.

    • overhead is the minimal overhead for metadata (transaction tables, etc.).

    Use the system view V$UNDOSTAT to estimate UPS and overhead.

  3. For tables that contain LOBs, do one of the following:

    • Set the LOB storage clause to RETENTION. This is the default for tables that are created when UNDO_MANAGEMENT is set to AUTO.

    • If using PCTVERSION instead of RETENTION, set PCTVERSION to an initial value of 25. You can adjust it based on the fetch statistics that are reported with the STATS EXTRACT command. If the value of the STAT_OPER_ROWFETCH CURRENTBYROWID or STAT_OPER_ROWFETCH_CURRENTBYKEY field in these statistics is high, increase PCTVERSION in increments of 10 until the statistics show low values.

Oracle GoldenGate provides the following parameters to manage fetching.

Parameter or Command Description

STATS EXTRACT command with REPORTFETCH option

Shows Extract fetch statistics on demand.

STATOPTIONS parameter with REPORTFETCH option

Sets the STATS EXTRACT command so that it always shows fetch statistics.

MAXFETCHSTATEMENTS parameter

Controls the number of open cursors for prepared queries that Extract maintains in the source database, and also for SQLEXEC operations.

MAXFETCHSTATEMENTS parameter

Controls the default fetch behavior of Extract: whether Extract performs a flashback query or fetches the current image from the table.

FETCHOPTIONS parameter with the USELATESTVERSION or NOUSELATESTVERSION option

Handles the failure of an Extract flashback query, such as if the undo retention expired or the structure of a table changed. Extract can fetch the current image from the table or ignore the failure.

REPFETCHEDCOLOPTIONS parameter

Controls the response by Replicat when it processes trail records that include fetched data or column-missing conditions.

Handling Other Database Properties

There are some database properties that may affect Oracle GoldenGate and the parameters used to resolve or work around certain conditions.

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.

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.

Sequences

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.

Configure a Multitenant Container Database

Oracle GoldenGate 23ai with Oracle Database allows the implementation of pluggable databases (PDBs) for source and target. Extract is registered for a specific PDB, which is called a per-PDB Extract.

Note:

Starting with Oracle GoldenGate 23ai, root-level Extract is not supported. This implies that the user privileges are assigned at the PDB level only and the c##ggadmin user is not used with Oracle GoldenGate 23ai.

The following diagram shows the configuration for different approaches in a multitenant container database configuration:

Description of pdb_extract_feedbk.png follows
Description of the illustration pdb_extract_feedbk.png

Adding Extract directly from the PDB captures from isolated PDBs, managing ownership and responsibility at the PDB level.

Using a per-PDB Extract, you can connect as the local PDB user (for example, ggadmin) and then register this Extract with the database. As you are already logged in as the PDB user, an additional container clause is not required. Similarly, the SOURCECATALOG or a three-part naming convention is also not needed.

To set up an Extract, see Add an Online Extract.

Considerations for Multitenant Container Database Configuration

Consider the following guidelines when configuring a multitenant container databases for data replication using Oracle GoldenGate:

  • The different pluggable databases in the multitenant container database can have different character sets. Oracle GoldenGate captures data from any multitenant database with different character sets into one trail file and replicates the data without corruption due to using different character sets.

  • To create and register a per-PDB Extract, you will need to connect to the PDB user such as ggadmin created for PDB-level access. Use the USERIDALIAS parameter to configure a SQL*Net connection string such as ggadmin@pdbeast. You do not need the container clause or the SOURCECATALOG to set up the per-PDB Extract.

  • To support source CDB 12.2, Extract must specify the trail format as release 12.3. Due to changes in the redo logs, to capture from a multitenant database that is Oracle 12.2 or higher, the trail format release must be 12.3 or higher.

  • DDL replication works as a normal replication for multitenant databases.

See Add Database Connections to add a multitenant container database user in Oracle GoldenGate credentials. See Grant User Privileges for Oracle Database 21c and Lower or Grant User Privileges for Oracle Database 23ai and Higher depending on the Oracle database installation that you need to configure.

Flush Sequence for Multitenant Container Database

You can only use the FLUSH SEQUENCE command within Oracle GoldenGate, if the sequence.sql script applies the database procedures into the GoldenGate Admin schema of the database.

Use the FLUSH SEQUENCE command immediately after you start Extract for the first time during an initial synchronization or a re-synchronization. This command updates an Oracle sequence, so that initial redo records are available at the time that Extract starts to capture transaction data. Normally, redo is not generated until the current cache is exhausted. The flush gives Replicat an initial start point with which to synchronize to the correct sequence value on the target system. From then on, Extract can use the redo that is associated with the usual cache reservation of sequence values.

  1. The following Oracle procedures are used by FLUSH SEQUENCE:

    Database Procedure User and Privileges

    Source

    updateSequence

    Grants EXECUTE to the owner of the Oracle GoldenGate DDL objects, or other selected user if not using DDL support.

    Target

    replicateSequence

    Grants EXECUTE to the Oracle GoldenGate Replicat user.

    The sequence.sql script installs these procedures. Normally, this script is run as part of the Oracle GoldenGate installation process, but make certain that was done before using FLUSH SEQUENCE. If sequence.sql was not run, the flush fails and an error message similar to the following is generated: 

    Cannot flush sequence {0}. Refer to the Oracle GoldenGate for Oracle 
documentation for instructions on how to set up and run the sequence.sql 
script. Error {1}.

  2. Before using FLUSH SEQUENCE, connect to the database using the DBLOGIN command.

FLUSH SEQUENCE must be issued at the PDB level, to create an Oracle GoldenGate user in each PDB for which the sequence replication is required. Use DBLOGIN to log into that PDB, and run the FLUSH SEQUENCE command.

It is recommended that you use the same schema in each PDB, so that it works with the GGSCHEMA GLOBALS parameter file.

In the following example, the environment setup is for Oracle 21c to Oracle 21c Replication, with integrated Extract, parallel Replicat using Oracle GoldenGate 21c (21.3.0).

The following table lists the names of source and target CDB, PDBs, and their corresponding user credentials for connecting to the database.
Source CDB Target CDB

NORTH

 SOUTH

PDB Name: DBEAST

PDB Name: DBWEST

Common user: c##ggadmin

PDB user for sequences: ggate

PDB User: ggadmin 

sqlplus system/manager 
ALTER SESSION SESSION SET CONTAINER=CERTMISSN;
CREATE USER ggate IDENTIFIED BY password DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON USERS CONTAINER=CURRENT;
Run @sequence.sql
sqlplus system/manager
ALTER SESSION SET CONTAINER=DBEAST;
@sequence.sql
When prompted enter the following:
GGADMIN
Run the FLUSH SEQUENCE command:
DBLOGIN USERIDALIAS ggeast DOMAIN OracleGoldenGate
FLUSH SEQUENCE DBEAST.HR.*
Target Oracle GoldenGate Configuration:
sqlplus system/manager
ALTER SESSION SET CONTAINER =PDBWEST;
@sequence.sql

When prompted, enter the PDB user name ggadmin.

This also applies to the @sequence.sql script, which you must also run on each PDB from where you are going to capture.

Configure the Auto Capture Mode for Extract

The auto capture mode allows automatically capturing the tables that have been enabled for Oracle GoldenGate auto capture.

See How to Capture Supplemental Logging for Oracle GoldenGate in the Oracle Database Utilities guide.

Here are some benefits of using the auto capture mode:

  • Easy to configure captured table set

  • No requirement to update TABLE/TABLEEXCLUDE parameter

  • No need to stop or restart Extract when captured table set changes

Enabling Auto Capture Mode for Extract

Enable the auto capture mode using TRANLOGOPTIONS: 
TRANLOGOPTIONS ENABLE_AUTO_CAPTURE | DISABLE_AUTO_CAPTURE

When Extract is running in the auto capture mode, don't filter an LCR if the object is not part of exclusion list set by TABLE EXCLUDE parameter or any inclusion list set by TABLE parameter.

The LIST TABLES command shows the list of tables enabled for AUTO_CAPTURE.

Note:

Auto capture is available from Oracle GoldenGate 21c with Oracle Database 19.18 data patch and higher. In case of database upgrade , any Extract which was registered prior to Oracle Database 19.18 cannot be converted to auto capture. Only new Extracts that are created after upgrateding to Oracle Database 19.18 and later, can be converted to auto capture Extract.

See DML Auto Capture and Details of Support for Objects and Operations in Oracle DDL to know about the DML and DDL considerations.

Also see this article Oracle GoldenGate 21c: Auto Capture of Tables to learn more.

Managing Server Resources

Extract interacts with an underlying logmining server in the source database and Replicat interacts with an inbound server in the target database. This section provides guidelines for managing the shared memory consumed by the these servers.

The shared memory that is used by the servers comes from the Streams pool portion of the System Global Area (SGA) in the database. Therefore, you must set the database initialization parameter STREAMS_POOL_SIZE high enough to keep enough memory available for the number of Extract and Replicat processes that you expect to run in integrated mode. Note that Streams pool is also used by other components of the database (like Oracle Streams, Advanced Queuing, and Datapump export/import), so make certain to take them into account while sizing the Streams pool for Oracle GoldenGate.

By default, one Extract requests the logmining server to run with MAX_SGA_SIZE of 1GB. Thus, if you are running three Extracts in the same database instance, you need at least 3 GB of memory allocated to the Streams pool. As a best practice, keep 25 percent of the Streams pool available. For example, if there are 3 Extracts, set STREAMS_POOL_SIZE for the database to the following value: 

3 GB * 1.25 = 3.75 GB

Support for Oracle Sequences

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

From the SQL prompt, run the script $OGG_HOME/lib/sql/legacy/sequence.sql on the source and target database as a DBA.

In a container database (CDB), connect as a local user with DBA privileges in the pluggable database (PDB).

In a non-CDB, connect as DBA for the database.

The Oracle GoldenGate Admin User does not necessarily need DBA privileges. However, the Oracle GoldenGate Admin User must have the SELECT ANY DICTIONARY and the [CREATE |ALTER|DROP] ANY SEQUENCE privileges in addition to the privileges granted by the OGG_CAPTURE | OGG_APPLY role for Oracle Database 23ai and higher or through the procedure call DBMS_GOLDEN_GATE_AUTH.GRANT_ADMIN_PRIVILEGE for earlier database versions.

The following example shows how to login to a CDB as the system user and run the sequence.sql script: 

sqlplus system/***@cdb23_pdbeast
@sequence.sql
You will be prompted to provide the Oracle GoldenGate Admin User, such as ggadmin.
When the script successfully finishes, it returns the status for sequence replication:
STATUS OF SEQUENCE SUPPORT
--------------------------------------------------------------
SUCCESSFUL installation of Oracle Sequence Replication support