Yugabyte: Supported Objects and Operations

The following list describes the supported objects and operations for Oracle GoldenGate for Yugabyte:

• Oracle GoldenGate Extract supports yboutput plugin.

• The Replica Identity CHANGE is the native setting for Yugabyte and is recommended when using the yboutput plugin, as it provides better performance compared to other Replica Identity types.

• Bidirectional replication is supported with yboutput plugin. When using the yboutput plugin for bidirectional replication, the Oracle GoldenGate checkpoint table must be created and included in the publication list before starting the CDC Extract process.

  Additionally, the replication slot should be created only after the checkpoint table has been created and added to the publication.

• Bidirectional replication is supported with test_decoding plugin. All the tables including the checkpoint tables must be created before registering any Extract when using test_decoding plugin. In case of PGOUTPUT plugin, the checkpoint table along with all other interested tables must be included in the publication before Extract registration.

Note: Do not drop any table including checkpoint tables.

• Multiple truncate operations are supported in pgoutput/yboutput plugin only.

• Register command enahanced with the pgplugintype option:

  • The pgplugintype option is available in both the web interface and Admin Client under the REGISTER EXTRACT command. This option allows you to choose either test_decoding or pgoutput as the logical decoding plugin.

  • When you specify pgplugintype pgoutput, then the underlying module automatically uses the yboutput plugin to register the Extract process.

  • If you don’t specify any pgplugintype, then the CDC Extract registers using the test_decoding plugin, by default.

  • To explicitly register with the yboutput plugin in Yugabyte, you must specify the pgoutput plugin type.

    Syntax:

    REGISTER EXTRACT *group_name* PGPLUGINTYPE *plugin_option*;

    Example:

    REGISTER EXTRACT ext_yb PGPLUGINTYPE pgoutput;

• The STREAMINGOPTIONS parameter has been introduced under TRANLOGOPTIONS. This parameter allows you to specify the publication_name for the replication start command.

  Example:

  TRANLOGOPTIONS STREAMINGOPTIONS (publication_names="my*123,my_publication2,123 pub,my_publication";)

  The following guidelines and restrictions must be considered when using this parameter:

  • The publication(s) must be created before registering the Extract process.

    Publication names must not contain special characters including Comma (,) Single quote (‘) Double quote (“).

  • Multiple publication names can be specified as a comma-separated list within publication_names. For example:

    TRANLOGOPTIONS STREAMINGOPTIONS (publication_names="my*123,my_publication2,123 pub,my_publication";)

    The final publication name in the list must end with a semicolon (;)

Yugabyte Limitations

Consider the following limitations for the Yugabyte database, while setting it up for capture and delivery:

• Missing large LOB transactions: When an LOB (Large Object) has a size between 200 MB and 400 MB, the entire transaction, whether an INSERT or UPDATE, may be missing from the CDC stream.

• Data mismatch in downstream due to multiple updates in the same transaction: When multiple updates occur within the same transaction, the CDCSDK module in Yugabyte may fail to send the correct AFTER image if the table’s replica identity is set to FULL or DEFAULT. This can result in data discrepancies for the AFTER image in the downstream system.

• Replication connection terminates after DDL on replicated tables (test_decoding Plugin): When using the test_decoding plugin, all tables in the database are automatically included in the polling list, by default. If a DDL operation (such as a table drop) is performed on any table included in this list, the server terminates the replication connection. However, dropping tables that are part of the polling list is not supported with the test_decoding plugin, so, if you require this flexibility, you should switch to the yboutput plugin.

  Note: Before dropping a table, ensure that it is not part of any publication to avoid replication errors or connection interruptions. Similarly, do not drop the Oracle GoldenGate checkpoint table as it will also result in the same error.

• Replication slot expiration due to extended inactivity: Replication slots retain certain resources to prevent garbage collection of WAL (Write-Ahead Log) records required for replication. If a slot remains inactive for an extended period, it is marked as expired, and the retained resources are released. After expiry, the replication slot becomes unusable and must be recreated.

  Default retention window:

  • 8 hours starting from version 2024.2.1

  • 4 hours in versions prior to 2024.2.1

  This is a known issue as mentioned on the Yugabyte site:

  https://github.com/yugabyte/yugabyte-db/issues/27766

  Recommended Actions to Prevent Expiration:

  • Enable heartbeat functionality to ensure continuous WAL activity and prevent long idle periods.

  • Configure autostart or autorestart options for the CDC process so that it automatically reconnects to the CDCSDK module, minimizing downtime and avoiding slot expiration.

• BatchSQL does not function correctly when BatchMechanism 3 is set with the Progress DD driver, which is the default for the Replicat process for Yugabyte. Therefore, if you need to use BatchSQL, you must configure the **DBOPTIONS _BatchMechanism 2** setting in the Replicat parameter file for proper functionality. This workaround can be applied to the Replicat process in Oracle GoldenGate 23.10 and higher for Yugabyte.

• Tables eligible for publication only: Persistent base tables and partitioned tables can be included in a publication. The following object types cannot be part of a publication:

  • Temporary tables

  • Unlogged tables

  • Foreign tables

  • Materialized views

  • Regular views

• Publication deletion impact on CDC Extract: If a publication is deleted while the CDC Extract is still running, there is a risk of replication slot corruption, causing it to stop functioning. In such cases, the affected replication slot becomes unusable, and simply recreating the same publication will not resolve the issue. There is no solution to resume on the existing stream and only way is to create a new publication and associate it with a new replication slot.

• Limitations with Timezone (TZ):The WAL sender process always transmits committed timestamps in UTC. If the Oracle GoldenGate session timezone differs from UTC, then discrepancies may occur while positioning by timestamp in the CDC Extract process. To prevent this issue, you must set the environment variable TZ=UTC at the deployment layer. This limitation applies specifically to the yboutput plugin.

• pgoutput plugin is not supported for Yugabyte.

• Globalization is not supported with yboutput plugin

• Primary key on table: Only tables with a primary key are supported for CDC streaming in Yugabyte. The table that does not have the primary key, will not be captured by Oracle GoldenGate CDC Extract.

• Primary Key updates in Yugabyte: A PK UPDATE in Yugabyte will be logged as DELETE followed by INSERT. Oracle GoldenGate Extract will show the statistics for a PK UPDATE as DELETE and INSERT only. Although the statistics will not be shown as update operation in the Extract statistics, but there will not be any discrepancy in the data on source and target.

• Replica identity behavior: Replica identity of tables present at the time of slot creation cannot be changed after the slot is created. However, replica identity of all new tables created after slot creation can be set using ysql_yb_default_replica_identity, cdcsdk_publication_list_refresh_interval_secs and csdk_enable_dynamic_table_support tserver flag. These flags need to be set in the master as well as t-server before new tables are created.

  By default, cdcsdk_publication_list_refresh_interval_secs is set to 900 sec, which is 15 minutes. You need to set this as per your requirement. Decreasing its value will reduce the newly created table to be refreshed in the CDCSDK module quickly, which will help Oracle GoldenGate capture any records for these new tables. The following example shows the flags that are set for dynamically created tables after the SLOT is created:

  ysql_yb_default_replica_identity=FULL,cdcsdk_publication_list_refresh_interval_secs=120,cdcsdk_enable_dynamic_table_support=true

• Positioning by End of Log (EOL) or End of File (EOF) is not supported: Instead of EOL/EOF, you can alter the slot position by current timestamp, which will filter the existing records.

• Truncate Operation: Parameters such as GETTRUNCATES/IGNORETRUNCATES are not supported as the database does not allow user to truncate the table if they are enabled for change data capture.

• WAL Retention: Yugabyte normally purges WAL segments after some period of time. This means that Oracle GoldenGate might lose the WAL log data if the logs are truncated when the Extract is down for longer duration. The WAL retention is not guaranteed in Yugabyte until a couple of parameters such as cdc_intent_retention_ms and cdc_wal_retention_time_secs are adjusted to retain the WAL logs for longer duration. This should be adjusted in all the nodes or cluster where the Oracle GoldenGate Extract process is running.

  The recommended values are as follows:

  cdc_wal_retention_time_secs=86400

  cdc_intent_retention_ms = 86400000

• UNREGISTER EXTRACT command: Oracle GoldenGate uses the UNREGISTER EXTRACT command to drop the replication slot. A replication slot can only be dropped after the client consuming from it has been stopped for at least ysql_cdc_active_replication_slot_window_ms duration. The default value of the flag is 5 minutes, which means you must wait for the maximum time window for which a replication slot can remain inactive before being considered stale.

  In case of a cluster setup, this parameter needs to be adjusted in all the nodes with the same value.

• BLOB/CLOB limitations: The data type such as text/image has the limitation to capture only 200MB of data. The row size should not exceed more the 32MB and each column containing the lob data should be kept up to 2MB.

  https://docs.yugabyte.com/stable/develop/best-practices-ysql/#row-size-limit

• Limitations with SAVEPOINT and Rollback in DML Operations: Oracle GoldenGate CDC Extract does not support the SAVEPOINT rollback operations. The CDC test_decoding plugin does not honor ROLLBACK TO SAVEPOINT commands. As a result, records that should have been excluded due to a rollback are still captured and processed by the Extract.

  This behavior is a known limitation of the Yugabyte CDC SDK module. To know more, refer to the following links:

  https://yugabyte.atlassian.net/browse/DB-11714

  https://docs.yugabyte.com/stable/develop/change-data-capture/using-logical-replication/#limitations

• Drop Table limitation: Once a slot is created, user must not drop any table from the database until the slot is dropped. If the slot is active and user tries to drop any table from the database, then CDC Extract will not be able to capture any records from other CDC tables which are part of replication. Also, it is suggested that when CDC extract is running then also user should not drop any table on the database where the slot is created. If do not follow this, then CDC Extract will abend with the disconnection error similar to the following:

  Error: The server has ended the streaming connection.

• Limitation on statement_timeout: Yugabyte parameter statement_timeout should not be modified at the user or the database level, and should be kept to default. If it is set to lower value, then the CDC Extract will not be able to read the records from the WAL. If required, it should always be set at the session level only.

• Creation of slot in the replica node with read only mode is not supported: The CDC Extract will not able to capture the records from the replica node. This is because the version that we support like 2024.2.1.0 or 2024.2.2.0 are build on top of PostgreSQL 11.2, which does not support creating the slot in the replica node.