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.

ALTER SYSTEM SET enable_goldengate_replication = true SCOPTE=both;

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:

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.
```
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.
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 with Oracle AI 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: Root level access with the c##ggadmin user is not recommended, except in case of downstream Extract configuration. Use Extract at the per-PDB level instead of root level Extract.

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

Description of pdb_extract_feedbk.png follows

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 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 SQLNet connection string such as ggadmin@pdbeast. You do *not need the container clause or the SOURCECATALOG to set up the per-PDB Extract.

If the PDB user name, CDB user name, or GLOBAL_NAME contains a dash in it, then Oracle GoldenGate throws the following error:

ERROR   OGG-08221  Cannot register or unregister EXTRACT <extract_name> because of the
following SQL error: OCI Error ORA (status = 44004-ORA-44004: invalid qualified SQL name

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 AI Database 26ai 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. |

 {: summary="This table lists the Oracle procedures used by FLUSH SEQUENCE. It gives the database, procedure, and user and privileges." }

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:

<pre class="copy"><code>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}.</code></pre>

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

Source CDB	Target CDB
`NORTH`	`SOUTH`
PDB Name: `DBEAST`	PDB Name: `DBWEST`
Common user: `c##ggadmin` PDB user for sequences: `ggate`	PDB User: `ggadmin`

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](/pls/topic/lookup?ctx=en/database/goldengate/core/26/coredoc&id=GWURF-GUID-B6ADFEC9-10E6-456D-9477-088513E113AF):

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](/pls/topic/lookup?ctx=en/database/goldengate/core/26/coredoc&id=GCLIR-GUID-23CC9E51-1030-40F6-B051-20A9A1040FF9) 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.

When Automatic (Shared) Memory Management is enabled (recommended), there is no need to set the STREAMS_POOL_SIZE environment variable for Oracle Database. This is because no minimum memory is required for the Streams Pool.

If Automatic (Shared) Memory Management is not enabled, then the Streams Pool can use up to 10% of the Shared Pool if STREAMS_POOL_SIZE is not specified and a root-level Extract (for Downstream Capture) or integrated Replicat (deprecated in Oracle GoldenGate 26ai) is in use.

For per-PDB Extract and parallel Replicat, there is no need to set the STREAMS_POOL_SIZE parameter in Oracle Database explicitly. For higher efficiency and preventing memory wastage, it is recommended not to explicitly set this parameter.

The following table summarizes components and the use of Streams Pool:

Process Type	Use of Streams Pool	Description
Integrated Extract (per-PDB-Level)	No	Per-PDB Extract is the recommended Extract to use.
Integrated Extract (Root-Level)	Yes	Root-Level-Extract is only needed by Downstream Capture. By default, up to 1G is allocated from the Streams Pool.
Classic Replicat	No	-
Coordinated Replicat	No	-
Parallel Replicat (non-integrated mode)	No	-
Parallel Replicat (integrated mode)	No	-
Integrated Replicat	Yes	It is recommended to use Parallel Replicat rather than integrated Replicat. If Integrated Replicat still in use: Set parameter `MAX_SGA_SIZE` to limit Streams Pool utilization.

If using root-level Extract or integrated Replicat, the size of the Streams Pool depends on the MAX_SGA_SIZE parameter defined in the Extract and Replicat parameter file:

For root-level Extract, the default MAX_SGA_SIZE is 1 GB.
For integrated Replicat, the default MAX_SGA_SIZE is unbound (INFINITE).

If there are multiple processes, it is recommended to set the MAX_SGA_SIZE for each integrated Replicat.

The size of Streams Pool size should be 25% higher than the sum of the MAX_SGA_SIZE settings of root-level Extract, Integrated Replicats, and XStream processes.

Note: Streams pool is also used by other components of the database (like Oracle Stream and Advanced Queuing), so make certain to take them into account while sizing the Streams pool for Oracle GoldenGate.

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 AI Database 26ai and higher. For earlier Oracle database versions, you can use the procedure call DBMS_GOLDEN_GATE_AUTH.GRANT_ADMIN_PRIVILEGE.

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

sqlplus system/***@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