Oracle

This section describes details for configuring Oracle GoldenGate for Oracle.

• Prepare Database Users and Privileges
  
• Prepare Database Connection, System, and Parameter Settings
  
• Configure Secure Connections to Oracle Database from Oracle GoldenGate
  
• Configure Logging Properties
  
• Oracle: Supported Data Types, Objects, and Operations for DDL and DML
  

Prepare Database Users and Privileges

Learn about creating database users and assigning privileges for Oracle GoldenGate for Oracle.

• Grant User Privileges for Oracle Database 21c and Lower
  

Grant User Privileges for Oracle Database 21c and Lower

The user privileges that are required for connecting to Oracle database from Oracle GoldenGate depend on the type of user.

Privileges should be granted depending on the actions that the user needs to perform as the GoldenGate Administrator User on the source and target databases. For example, to grant DML operation privileges to insert, update, and delete transactions to a user, use the GRANT ANY INSERT/UPDATE/DELETE privileges and to further allow users to work with tables and indexes as part of DML operations, use the GRANT CREATE/DROP/ALTER ANY TABLE/INDEX privileges.

If the GoldenGate Administrator user has the DBA role, additional object privileges are not needed. However, there might be security constraints granting the DBA role to the GoldenGate Administration user. The DBA role is not necessarily required for Oracle GoldenGate.

If there are many objects being replicated, you might consider using the ANY privilege for DML and DDL operations. This simplifies the provision of privileges to the GoldenGate Administrator users, as you only need to grant a few privileges depending on the database operations.

The following table describes some of the essential privileges for GoldenGate Administrator user for Oracle database. For explanation purposes, the table uses c##ggadmin as an example of a common user for a multitenant container database and ggadmin as the pluggable database (PDB) user. PDBEAST and PDBWEST are used as examples of PDB names.

The following table describes the essential privileges for GoldenGate Administrator user for using Oracle GoldenGate with on source and target Oracle databases:

Privilege	Extract	Replicat All Modes	Purpose
RESOURCE	Yes	Yes	Required to create objects In Oracle Database 12cR1 and later, instead of RESOURCE, grant the following privilege: ALTER USER user QUOTA {size | UNLIMITED} ON tablespace;
CONNECT	Yes	Yes	Common user SYSTEM connects to the root container. This privilege is essential when the DBA role is not assigned to the user. See an example of Permissions granted to an Oracle mutitenant database common user.
CREATE SESSION	Yes	Yes	Required to connect to the database.
CREATE VIEW	Yes	Yes	Required to add the heartbeat table view. If you want to be specific to each object, you can also provide the privileges for each object individually. You may consider creating a specific database role to maintain such privileges.
ALTER SYSTEM	Yes	Yes	Perform administrative changes, such as enabling logging.
ALTER USER	Yes	Yes	Required for multitenant architecture and GGADMIN should be a valid Oracle GoldenGate administrator schema.
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE ('REPUSER', CONTAINER=>'PDBEAST');	Yes	Yes	Required for Autonomous Databases (ATP and ADW) Extract and Replicat. Extracts in the root container (CDB$ROOT)) might require a value of ALL or a specific PDB (example: pdbeast). Grant privileges for Extract and Replicat users. See Example: Grant privileges using the DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE package Grant privilges to capture from Virtual Private Database Grants privilges to capture redacted data
Grant DV_GOLDENGATE_ADMIN and DV_GOLDENGATE_REDO_ACCESS privileges connected as SYS user to the Extract and the Replicat user.	Yes	Yes	Capture from Data Vault. See Privileges for Capturing from Oracle Data Vault.
Grant Replicat privileges in DBMS_MACADM.ADD_AUTH_TO_REALM if applying to a realm.	NA	Yes	Capture from Data Vault. See Privileges for Capturing from Oracle Data Vault.
INSERT, UPDATE, DELETE on target tables	NA	Yes	Apply replicated DML to target objects. See Details of Support for Objects and Operations in Oracle DML
GRANT INSERT ANY TO... GRANT UPDATE ANY TO... GRANT DELETE ANY TO...	NA	Yes	Grant these privileges to the Replicat user, instead of granting INSERT, UPDATE, DELETE to every table, if replicating every table.
If DDL replication is performed, grant the following as Database Vault owner: EXECUTE DBMS_MACADM.AUTHORIZE_DDL(‘GGADMIN USER', ‘SCHEMA FOR DDL’);	No	No	Capture from Data Vault. See Privileges for Capturing from Oracle Data Vault.
DDL privileges on target objects (if using DDL support)	NA	Yes	Issue replicated DDL on target objects. See Details of Support for Objects and Operations in Oracle DDL.
GRANT [CREATE|ALTER|DROP] ANY [TABLE|INDEX|VIEW|PROCEDURE] to GGADMIN;	Yes	Yes	Grants privileges for DDL Replication for tables.
CREATE ANY TABLE	Yes	Yes	Grants privileges for creating table in any schema. To allow creating tables only in a specific schema, use the CREATE TABLE privilege.
CREATE ANY VIEW	Yes	Yes	Grants privilges to create view in any database schema. To allow creating views in a specific schema, use the CREATE VIEW privilege.
SELECT ANY DICTIONARY	Yes	Yes	Allow all privileges to work properly on dictionary tables.

Example: Permissions granted for the Oracle database common user

Privilges granted for the Oracle database common user, which is c##ggadmin in the following example:

CREATE USER c##ggadmin IDENTIFIED BY passw0rd CONTAINER=all DEFAULT
TABLESPACE GG_DATA TEMPORARY TABLESPACE temp;
GRANT RESOURCE to c##ggadmin;
GRANT CREATE SESSION to c##ggadmin;
GRANT CREATE VIEW to c##ggadmin;
GRANT CREATE TABLE to c##ggadmin;
GRANT CONNECT to c##ggadmin CONTAINER=all; 
GRANT DV_GOLDENGATE_ADMIN; –-- for data vault user
GRANT DV_GOLDENGATE_REDO_ACCESS; –-- for data vault user
GRANT ALTER SYSTEM to c##ggadmin;
GRANT ALTER USER to c##ggadmin;
ALTER USER c##ggadmin SET CONTAINER_DATA=all CONTAINER=current;
ALTER USER c##ggadmin QUOTA unlimited ON GG_DATA;
GRANT SELECT ANY DICTIONARY to c##ggadmin;
GRANT SELECT ANY TRANSACTION to c##ggadmin;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('c##ggadmin');

In this example, DBA privilege is not provided. If privileges are missing, then the DBA has to grant necessary privileges additionally.

Privileges granted for PDB user ggadmin are provided in the following example:

ALTER SESSION SET CONTAINER=dbwest; 
CREATE USER ggadmin IDENTIFIED BY PASSWORD CONTAINER=CURRENT; 
GRANT CONNECT, RESOURCE, DBA TO ggadmin CONTAINER=CURRENT; 
GRANT CREATE SESSION TO ggadmin CONTAINER=CURRENT; 
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ggadmin');

Note:

Granting DBA role is not mandatory for every user. Privileges should be granted depending on the actions that the user needs to perform on the database. For example, to grant DML operation privileges to insert, update, and delete transactions to ggadmin, use the GRANT ANY INSERT/UPDATE/DELETE privileges and to further allow users to work with tables and indexes as part of DML operations, use the GRANT CREATE/DROP/ALTER ANY TABLE/INDEX privileges.

Example: Grant privileges using the DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE package

This procedure grants the privileges needed by a user to be an Oracle GoldenGate administrator The following example grants explicit privileges for Extract on Oracle multitenant database:

BEGIN  
DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE 
(GRANTEE => 'c##ggadmin',  PRIVILEGE_TYPE => 'CAPTURE',  GRANT_SELECT_PRIVILEGES => TRUE,  DO_GRANTS => TRUE,  CONTAINER => 'ALL'  ); 
END;

See DBMS_GOLDENGATE_AUTH in Oracle Database PL/SQL Packages and Types Reference for more information.

• Privileges for Capturing from Oracle Data Vault
  

Privileges for Capturing from Oracle Data Vault

Grant the following privileges connected as SYS user in Oracle database. These privileges are set for Extract and Replicat user credentials:

• EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE ('userID','*',
  GRANT_OPTIONAL_PRIVILEGES=>'*');
  GRANT DV_GOLDENGATE_ADMIN, DV_GOLDENGATE_REDO_ACCESS to userID;

• Grant Replicat the privileges in DBMS_MACADM.ADD_AUTH_TO_REALM if applying to a realm.

  Connect as Database Vault owner and execute the following scripts:

  
  BEGIN
  DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM(
  REALM_NAME => 'Oracle Default Component Protection Realm',GRANTEE => 'userID',AUTH_OPTIONS => 1) ;
  END ;
  /
  EXECUTE_DBMS_MACADM.AUTHORIZE_DDL('SYS', 'SYSTEM');

• For DDL replication, grant the following as the Database Vault owner:

  EXECUTE DBMS_MACADM.AUTHORIZE_DDL
  (‘userID', ‘SCHEMA FOR DDL’);

Prepare Database Connection, System, and Parameter Settings

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

• Enable Oracle GoldenGate for Oracle
  
• Setting Flashback Query
  
• Handling Other Database Properties
  
• Configure a Multitenant Container Database
  
• Configure the Auto Capture Mode for Extract
  
• Managing Server Resources
  
• Ensuring Row Uniqueness in Source and Target Table
  
• Support for Oracle Sequences
  

Enable Oracle GoldenGate for Oracle

The database services required to support Oracle GoldenGate capture and apply must be enabled explicitly for all Oracle database versions. This is required for Extract and all Replicat modes.

To enable Oracle GoldenGate, set the following database initialization parameter. All instances in Oracle RAC must have the same setting.

ENABLE_GOLDENGATE_REPLICATION=true

This parameter alters the DBA_FEATURE_USAGE_STATISTICS view. For more information about this parameter, see Initialization Parameters.

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. For more information, see Reference for Oracle GoldenGate.
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. For more information, see Reference for Oracle GoldenGate.

Configure a Multitenant Container Database

Oracle GoldenGate with Oracle Database allows each pluggable database (PDB) to have Extract registered for a specific PDB, which is called a per-PDB Extract.

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

Description of the illustration pdb_extract_feedbk.png

There are two approaches to configure an Extract for an Oracle multitenant database.

• Approach 1: Adding Extract directly from the PDB. This approach is useful when Extract captures from isolated PDBs, managing ownership and responsibility at the PDB level.

• Approach 2: Adding Extract in the root container and referencing the associated PDBs This approach is useful when Extract captures data from multiple PDBs.

If you use Approach 1, you can create a per-PDB Extract by connecting as the local PDB user (for example, ggadmin) and then register this Extract with the PDB. As you are already logged in as the PDB user, the container clause is not required. Similarly, the SOURCECATALOG and two-part naming convention is adequate.

If you use Approach 2, you can connect to the root container with the common database user c##ggadmin and create Extract for specific PDBs. This Extract needs to be registered for the specific PDB using the container clause. The container clause might contain a single or multiple PDBs.

Note:

Even if you use a root-level Extract, you need the user credentials for each PDB from which you need to capture. The hearbeat table also resides in the individual PDBs.

To set up an Extract, see Add a Primary 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 add a user for the root container, Extract must connect to the root container (cdb$root) as a common user in order to interact with the logmining server. To specify the root container, use the appropriate SQL*Net connect string for the database user that you specify with the USERIDALIAS parameter, such as c##ggadmin@dbeast.

• 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 Credentials to add a multitenant container database user in Oracle GoldenGate credentials. See Grant User Privileges for Oracle Database 21c and Lower depending on the Oracle database installation that you need to configure.

• Flush Sequence for Multitenant Container Database
  

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. The GLOBALS file must contain a GGSCHEMA parameter that specifies the schema in which the procedures are installed. This user must have CONNECT, RESOURCE, and DBA privileges.

3. Before using FLUSH SEQUENCE, issue the DBLOGIN command as the database user that has EXECUTE privilege on the updateSequence procedure. If logging into a multitenant container database, log into the pluggable database that contains the sequence that is to be flushed.

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 / as sysdba 
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 / as sysdba
ALTER SESSION SET CONTAINER=DBEAST;
@sequence.sql

When prompted enter the following:

GGADMIN GLOBALS
GGSCHEMA GGADMIN

Run the FLUSH SEQUENCE command:

DBLOGIN USERIDALIAS ggeast DOMAIN OracleGoldenGate
FLUSH SEQUENCE DBEAST.HR.*
 

Target Oracle GoldenGate Configuration:

sqlplus / as sysdba
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

Ensuring Row Uniqueness in Source and Target Table

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 Configure Logging Properties).

1. Primary key if it does not contain any extended (32K) VARCHAR2/NVARCHAR2 columns. Primary key without invisible columns.

2. Unique key: Unique key without invisible columns.

   In the case of a non-integrated 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.

3. Not Nullable Unique keys: At least one column from one of the unique keys must be not nullable. This is because NOALLOWNULLABLEKEYS is the default.

   Note:

   ALLOWNULLABLEKEYS is not valid for integrated Replicat.

4. 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.

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 23c 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

Configure Secure Connections to Oracle Database from Oracle GoldenGate

To specify database connection string in a secure manner while configuring Oracle GoldenGate connections to the database, the following options are available:

• Include the USERIDALIAS option in the Extract and Replicat parameter files

• Set up a Bequeath connection

Security Options for Specifying the Connection String in the Extract and Replicat Parameter Files

The following are the security options for specifying the connection string in the Extract or Replicat parameter file.

Credential store method:

USERIDALIAS ggeast

In the case of USERIDALIAS, the alias ggeast is stored in the Oracle GoldenGate credential store with the actual connection string. The following example uses the INFO CREDENTIALSTORE command to display the details of the credentials configured in Oracle GoldenGate:

INFO CREDENTIALSTORE DOMAIN OracleGoldenGate

Output:

Domain: OracleGoldenGate
  Alias: ggeast
  Userid: [e-mail]:1521/DBEAST.example.com

Setting up a Bequeath connection

Oracle GoldenGate can connect to a database instance without using the network listener if a Bequeath connect descriptor is added in the tnsnames.ora.

The following example shows the configuration for connecting to a database using Bequeath connect descriptor:

dbbeq =  (DESCRIPTION=   
      (ADDRESS=(PROTOCOL=beq)     
          (ENVS='ORACLE_SID=sales,ORACLE_HOME=/app/db_home/oracle,LD_LIBRARY_PATH=/app/db_home/oracle/lib')     
          (PROGRAM=/app/db_home/oracle/bin/oracle)     
      (ARGV0=oraclesales)     
      (ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))'))   
        (CONNECT_DATA=(SID=sales))) 

In this example:

/app/db_home is the target Oracle database installation directory

sales is the database service name

The ORACLE_SID, ORACLE_HOME, and LD_LIBRARY_PATH in the ENVS parameter refers to the target.

Note:

Make sure that there is no white space between these environment variable settings.

Configure Logging Properties

Oracle GoldenGate relies on the redo logs to capture the data that it needs to replicate source transactions. The Oracle redo logs on the source system must be configured properly before you start Oracle GoldenGate processing.

This section addresses the following logging levels that apply to Oracle GoldenGate. The logging level that you use depends on Oracle GoldenGate features that you are using.

Note:

Redo volume is increased as the result of this required logging. You can wait until you are ready to start Oracle GoldenGate processing to enable the logging.

This table shows the Oracle GoldenGate use cases for the different logging properties.

Logging option	Command Name	What it does	Use case
Forced logging mode	ALTER DATABASE FORCE LOGGING;	Forces the logging of all transactions and loads.	Strongly recommended for all Oracle GoldenGate use cases. FORCE LOGGING overrides any table-level NOLOGGING settings.
Minimum database-level supplemental logging	ALTER DATABASE ADD SUPPLEMENTAL LOG DATA	Enables minimal supplemental logging to add row-chaining information to the redo log.	Required for all Oracle GoldenGate use cases
Schema-level supplemental logging, default setting See Enable Subset Database Replication Logging.	ADD SCHEMATRANDATA	Enables unconditional supplemental logging of the primary key and conditional supplemental logging of unique key(s) and foreign key(s) of all tables in a schema. All of these keys together are known as the scheduling columns.	Enables the logging for all current and future tables in the schema. If the primary key, unique key, and foreign key columns are not identical at both source and target, use ALLCOLS.
Schema-level supplemental logging with unconditional logging for all supported columns. (See Enable Schema-level Supplemental Logging for non-supported column types.)	ADD SCHEMATRANDATA with ALLCOLS option	Enables unconditional supplemental logging of all of the columns in a table, for all of the tables in a schema.	Used for bidirectional and active-active configurations where all column values are checked, not just the changed columns, when attempting to perform an update or delete. This takes more resources though allows for the highest level of real-time data validation and thus conflict detection. This method should also be used if they are going to be using the HANDLECOLLISIONS parameter for initial loads.
Schema-level supplemental logging, minimal setting	ADD SCHEMATRANDATA with NOSCHEDULINGCOLS option	Enables unconditional supplemental logging of the primary key and all valid unique indexes of all tables in a schema.	Use only for nonintegrated Replicat. This is the minimum required schema-level logging.
Table-level supplemental logging with built-in support for integrated Replicat See Enable Table-level Supplemental Logging	ADD TRANDATA	Enables unconditional supplemental logging of the primary key and conditional supplemental logging of unique key(s) and foreign key(s) of a table. All of these keys together are known as the scheduling columns.	Required for all Oracle GoldenGate use cases unless schema-level supplemental logging is used. If the primary key, unique key, and foreign key columns are not identical at both source and target, use ALLCOLS.
Table-level supplemental logging with unconditional logging for all supported columns. (See Enable Table-level Supplemental Logging for non-supported column types.)	ADD TRANDATA with ALLCOLS option	Enables unconditional supplemental logging of all of the columns of the table.	Used for bidirectional and active-active configurations where all column values are checked, not just the changed columns, when attempting to perform an update or delete. This takes more resources though allows for the highest level of real-time data validation and thus conflict detection. It can also be used when the source and target primary, unique, and foreign keys are not the same or are constantly changing between source and target.
Table-level supplemental logging, minimal setting	ADD TRANDATA with NOSCHEDULINGCOLS option	Enables unconditional supplemental logging of the primary key and all valid unique indexes of a table.	Use for nonintegrated Replicat and non-parallel Replicat. This is the minimum required table-level logging.

• Enable Subset Database Replication Logging
  
• Enable Schema-level Supplemental Logging
  
• Enable Table-level Supplemental Logging
  
• Remove Table-level Supplemental Logging
  

Enable Subset Database Replication Logging

Oracle strongly recommends putting the Oracle source database into forced logging mode. Forced logging mode forces the logging of all transactions and loads, overriding any user or storage settings to the contrary. This ensures that no source data in the Extract configuration gets missed.

There is a fine-granular database supplemental logging mode called Subset Database Replication available in LogMiner, which is the basic recommended mode for all Oracle GoldenGate and XStream clients. It replaces the previously used Minimum Supplemental Logging mode.

To know more, see ALTER DATABASE in the Oracle Database SQL Language Reference.

The subset database replication logging is enabled at CDB$ROOT (and all user-PDBs inherit it) currently.

Note:

Database-level primary key (PK) and unique index (UI) logging is only discouraged if you are replicating a subset of tables. You can use it with Live Standby, or if Oracle GoldenGate is going to replicate all tables, like to reduce the downtime for a migration or upgrade.

Perform the following steps to verify and enable, if necessary, subset database replication logging and forced logging.

1. Log in to SQL*Plus as a user with ALTER SYSTEM privilege.
2. Issue the following command to determine whether the database is in supplemental logging mode and in forced logging mode. If the result is YES for both queries, the database meets the Oracle GoldenGate requirement.

   SELECT SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING FROM V$DATABASE;

3. If the result is NO for either or both properties, continue with these steps to enable them as needed:

   ALTER PLUGGABLE DATABASE pdbname ADD SUPPLEMENTAL LOG DATA SUBSET DATABASE REPLICATION;;
   ALTER DATABASE FORCE LOGGING;

4. Issue the following command to verify that these properties are now enabled.

   SELECT SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING FROM V$DATABASE;

   The output of the query must be YES for both properties.

5. Switch the log files.

   ALTER SYSTEM SWITCH LOGFILE;

Enable Schema-level Supplemental Logging

Oracle GoldenGate supports schema-level supplemental logging. Schema-level logging is required for an Oracle source database when using the Oracle GoldenGate DDL replication feature. In all other use cases, it is optional, but then you must use table-level logging instead (see Enable Table-level Supplemental Logging).

By default, schema-level logging automatically enables unconditional supplemental logging of the primary key and conditional supplemental logging of unique key(s) and foreign key(s) of all tables in a schema. Options enable you to alter the logging as needed.

Note:

Oracle strongly recommends using schema-level logging rather than table-level logging, because it ensures that any new tables added to a schema are captured if they satisfy wildcard specifications. This method is also recommended because any changes to key columns are automatically reflected in the supplemental log data too. For example, if a key changes, there is no need to issue ADD TRANDATA.

Perform the following steps on the source system to enable schema-level supplemental logging.

1. Start the command line on the source system.
2. Issue the DBLOGIN command with the alias of a user in the credential store who has privilege to enable schema-level supplemental logging.

   DBLOGIN USERIDALIAS alias

   See USERIDALIAS in Reference for Oracle GoldenGate for more information about USERIDALIAS and additional options.

3. When using ADD SCHEMATRANDATA or ADD TRANDATA on a multitenant database, you can either log directly into the PDB and perform the command. Alternately, if you are logging in at the root level (using a C## user), then you must include the PDB. Issue the ADD SCHEMATRANDATA command for each schema for which you want to capture data changes with Oracle GoldenGate.

   ADD SCHEMATRANDATA pdb.schema [ALLCOLS | NOSCHEDULINGCOLS]

   Where:

   • Without options, ADD SCHEMATRANDATA schema enables the unconditional supplemental logging on the source system of the primary key and the conditional supplemental logging of all unique key(s) and foreign key(s) of all current and future tables in the given schema. Unconditional logging forces the primary key values to the log whether or not the key was changed in the current operation. Conditional logging logs all of the column values of a foreign or unique key if at least one of them was changed in the current operation. The default is optional to support nonintegrated Replicat but is required to support integrated Replicat because primary key, unique keys, and foreign keys must all be available to the inbound server to compute dependencies.

   • ALLCOLS can be used to enable the unconditional supplemental logging of all of the columns of a table and applies to all current and future tables in the given schema. Use to support integrated Replicat when the source and target tables have different scheduling columns. (Scheduling columns are the primary key, the unique key, and the foreign key.)

   • NOSCHEDULINGCOLS logs only the values of the primary key and all valid unique indexes for existing tables in the schema and new tables added later. This is the minimal required level of schema-level logging and is valid only for Replicat in nonintegrated mode.

   In the following example, the command enables default supplemental logging for the hr schema.

   ADD SCHEMATRANDATA pdbeast.hr ALLCOLS

   In the following example, the command enables the supplemental logging only for the primary key and valid unique indexes for the HR schema.

   ADD SCHEMATRANDATA pdbeast.hr NOSCHEDULINGCOLS

Enable Table-level Supplemental Logging

Enable table-level supplemental logging on the source system in the following cases:

• To enable the required level of logging when not using schema-level logging (see Enable Schema-level Supplemental Logging). Either schema-level or table-level logging must be used. By default, table-level logging automatically enables unconditional supplemental logging of the primary key and conditional supplemental logging of unique key(s) and foreign key(s) of a table. Options enable you to alter the logging as needed.

• To prevent the logging of the primary key for any given table.

• To log non-key column values at the table level to support specific Oracle GoldenGate features, such as filtering and conflict detection and resolution logic.

• If the key columns change on a table that only has table-level supplemental logging, you must perform ADD TRANDATA on the table prior to allowing any DML activity on the table.

Perform the following steps on the source system to enable table-level supplemental logging or use the optional features of the command.

1. Run the command line on the source system.

2. Issue the DBLOGIN command using the alias of a user in the credential store who has privilege to enable table-level supplemental logging.

   DBLOGIN USERIDALIAS alias

   See USERIDALIAS in Reference for Oracle GoldenGatefor more information about DBLOGIN and additional options.

3. Issue the ADD TRANDATA command.

   ADD TRANDATA [PDB.]schema.table [, COLS (columns)] [, NOKEY] [, ALLCOLS | NOSCHEDULINGCOLS]

   Where:

   • PDB is the name of the root container or pluggable database if the table is in a multitenant container database.

   • schema is the source schema that contains the table.

   • table is the name of the table. See Specifying Object Names in Oracle GoldenGate Input for instructions for specifying object names.

   • ADD TRANDATA without other options automatically enables unconditional supplemental logging of the primary key and conditional supplemental logging of unique key(s) and foreign key(s) of the table. Unconditional logging forces the primary key values to the log whether or not the key was changed in the current operation. Conditional logging logs all of the column values of a foreign or unique key if at least one of them was changed in the current operation. The default is optional to support nonintegrated Replicat (see also NOSCHEDULINGCOLS) but is required to support integrated Replicat because primary key, unique keys, and foreign keys must all be available to the inbound server to compute dependencies. For more information about integrated Replicat, see unresolvable-reference.html#GUID-6635228F-F992-4DAE-AD5E-0BE5A36542CC.

   • ALLCOLS enables the unconditional supplemental logging of all of the columns of the table. Use to support integrated Replicat when the source and target tables have different scheduling columns. (Scheduling columns are the primary key, the unique key, and the foreign key.)

   • NOSCHEDULINGCOLS is valid for Replicat in nonintegrated mode only. It issues an ALTER TABLE command with an ADD SUPPLEMENTAL LOG DATA ALWAYS clause that is appropriate for the type of unique constraint that is defined for the table, or all columns in the absence of a unique constraint. This command satisfies the basic table-level logging requirements of Oracle GoldenGate when schema-level logging will not be used. See Ensuring Row Uniqueness in Source and Target Table for how Oracle GoldenGate selects a key or index.

   • COLS columns logs non-key columns that are required for a KEYCOLS clause or for filtering and manipulation. The parentheses are required. These columns will be logged in addition to the primary key unless the NOKEY option is also present.

   • NOKEY prevents the logging of the primary key or unique key. Requires a KEYCOLS clause in the TABLE and MAP parameters and a COLS clause in the ADD TRANDATA command to log the alternate KEYCOLS columns.

4. If using ADD TRANDATA with the COLS option, create a unique index for those columns on the target to optimize row retrieval. If you are logging those columns as a substitute key for a KEYCOLS clause, make a note to add the KEYCOLS clause to the TABLE and MAP statements when you configure the Oracle GoldenGate processes.

Remove Table-level Supplemental Logging

If a table is no longer required to be captured by Oracle GoldenGate and the TABLE parameter for the table has been removed from the Extract parameter file, or TABLEEXCLUDE is used to exclude the table from a wildcard statement, then supplemental logging can be removed from the table.

Note:

If the Extract resolves a table that does not have supplemental logging enabled, it will abend depending on the type of DML operation.

Using DELETE TRANDATA to remove supplemental logging sets the Replicat Identity level of the table to NOTHING. Supplemental logging can be disabled using the Microservices Architecture web interface from the Administration Service, Configuration page, under the Credential created for a source database, or can be issued with the DELETE TRANDATA command.

The following is the syntax for issuing DELETE TRANDATA.

DBLOGIN USERIDALIAS alias_name
DELETE TRANDATA schema.tablename

To check the level of supplemental logging:

INFO TRANDATA schema.tablename