Diagnose issues

This can happen for various reasons. To troubleshoot this error, perform the following actions:

1. Ensure that the source database is up and reachable.
2. Navigate to the source connection profile from the Streams or Connection profiles pages.
3. Verify that the connection profile connectivity information is correct.
4. Verify that the username and password match.
5. Verify that the username exists on the database and has the required privileges.
6. Save any changes that you made on the Connection profiles page.

The stream resumes automatically.

1. Make sure that you have completed all of the prerequisites in Before you begin.

2. After creating the private connectivity configuration, verify that the route which contains the internal IP address of the database appears in the Exported routes tab of the VPC Network Peering page.

   To do this, go to the VPC Network Peering page, and then search for the peering that was added (the name is peering-[UUID]). The route can be found in the Exported routes tab. If this route doesn't exist, then add it manually.

3. Datastream doesn't check for overlapping with dynamic peering routes. Providing a subnet that overlaps with a dynamic route may lead to connectivity issues. Therefore, we don't recommend using a subnet that's part of a dynamic route.
4. Make sure that the custom routes for Datastream IP address ranges are advertised correctly. If the custom routes are missing, see Custom advertised routes.
5. If you're still having issues connecting to the source database, then see Set up a reverse proxy.

You selected the public IP allowlist or Forward SSH tunnel network connectivity methods for the connection profile that you're creating. However, the Block Public Connectivity Methods organization policy for Datastream is enabled. Therefore, you can't select public connectivity methods for your connection profile.

To resolve this issue, either select the private VPC peering network connectivity method or disable the organization policy.

To disable the organization policy:

1. Go to the Organization policies page in the Google Cloud Console.
2. Select the Datastream - Block Public Connectivity Methods organization policy.

3. Click EDIT.

4. In the Applies to section of the page, select Customize.

5. In the Enforcement section, select Off.

6. Click SAVE.
7. Return to the Oracle connection profile that you're creating, and then click CREATE.

This can happen if your database has thousands of tables and schemas. Some of them might not be included in the list of objects to pull when configuring the source for the stream in the Google Cloud console. Instead of selecting Specific schemas and tables in the Select objects to include section, select Custom. In the Object matching criteria field, enter the schemas and tables that you want Datastream to pull.

Datastream might drop unsupported events during streaming. You can perform the following actions to address the issue:

• Manually trigger a backfill of the entire table. This works if the discarded events are UPSERT events only. If the dropped events include DELETE events, then you need to truncate the table in BigQuery before performing the backfill.

  For information about how to perform a backfill, see Initiate backfill.

• Contact Google Support and ask them to perform a partial backfill. This is possible only if you can identify the dropped events with a SQL WHERE clause, and if none of the events are DELETE events.
• Disregard the issue if the number of discarded events is low or if the discarded events aren't significant.

An error fetching ongoing, change data capture (CDC) data can happen if the supplemental logging configuration isn't correct on the source database. Verify that supplemental logging is configured correctly. Specifically, confirm that supplemental logging is turned on for the database tables that are being streamed from the source into the destination. The stream resumes automatically.

The log files may have been deleted. Oracle purges log files as soon as they can unless you specify a minimum rotation period to keep them around. In the Oracle server, set how long the log files should be retained. For example, use CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS; to keep the log files around for at least 4 days.

For an RDS deployment, use exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours',96);

This can happen for continuous MySQL streams if the binlog configuration is incorrect on the source database. To troubleshoot this error, perform the following actions:

1. Verify that binlog is configured correctly.
2. Confirm that the binary log format of the MySQL database is set to ROW.
3. Restart the stream.

This can happen when the source database doesn't adhere to the version support matrix. To troubleshoot this error, perform the following actions:

1. Ensure that the source database adheres to the matrix.
2. Recreate the stream with the updated source database.

Increase the Datastream ability to read from the source database:

• Reduce the maximum binary log (binlog) file size for your source database. Reducing the size increases the number of binlog files.
• If there are multiple binlog files, set the number of maximum concurrent CDC tasks for the stream accordingly. Having multiple binlog files lets Datastream read source database events concurrently, up to the number set in the maxConcurrentCdcTasks field.

Verify that logical decoding is configured correctly. See Configure a source PostgreSQL database.

1. Change the replica identity to DEFAULT:

   ALTER TABLE TABLE_NAME REPLICA IDENTITY DEFAULT

   Replace TABLE_NAME with the name of the table for which you want to change the replica identity.
2. Remove the table from the stream's list of objects to include. For more information, see Modify configuration information about the source database.
3. Delete the table from BigQuery. For more information, see Delete tables.
4. In Datastream, add the table to the stream again by editing your source configuration.

Change data capture (CDC) must be enabled for the database. Datastream needs direct read access to transaction logs in order to replicate real time changes to the source database, and to get complete log information. Enable CDC for the database and try again.

For information about enabling CDC for a database, see Configure a source SQL Server database.

Change data capture (CDC) must be enabled for all the tables included in the stream. Datastream needs direct read access to transaction logs in order to replicate real time changes to source tables, and to get complete log information. Enable CDC for the tables included in the stream and try again.

For information about enabling CDC for source tables, see Configure a source SQL Server database.

When you select the Append-only write mode for your stream, your data is appended in BigQuery as a stream of INSERT, UPDATE-INSERT, UPDATE-DELETE and DELETE events, without any consolidation. This might cause duplicate rows to be written to BigQuery when you perform backfill, or when an issue occurs and the BigQuery writer re-tries the write operations. To address the issue, we recommend that you run a de-duplication query similar to the following on a regular basis:

Verify that there's a primary key in your source table. BigQuery needs it to merge the changes into the destination table.

If there's no primary key, consider adding one in either the source or destination table. To add a primary key in your destination BigQuery table, follow these steps:

1. Pause the stream.
2. Truncate the table in BigQuery.
3. Add the primary key to the table definition.
4. Resume the stream.
5. Trigger backfill for the table.

By default, Datastream doesn't support adding a primary key to a table that's already replicated to BigQuery without a primary key or removing a primary key from a table that's replicated to BigQuery with a primary key. You can, however, change the primary key definition for a source table replicated to BigQuery that already has a primary key:

1. Check the total latency metric for the stream and wait at least as long as the current latency to ensure that any in-flight events are written to the destination. This allows all events with the original primary key to be successfully streamed.
2. Pause the stream.
3. Copy the CREATE TABLE data definition language (DDL) command for the table in BigQuery:

       SELECT ddl FROM PROJECT_ID.DATASET.INFORMATION_SCHEMA.TABLES
         WHERE table_name='TABLE_NAME';

   Replace the following:

   • PROJECT_ID: the identifier of your Google Cloud project.
   • DATASET_ID: the identifier of the dataset in BigQuery.
   • TABLE_NAME: the name of the table for which you want to copy the DDL command.
4. Drop the table in BigQuery.
5. Adjust the CREATE TABLE DDL command with the new primary keys. Include the partition and cluster keys, and the max_staleness OPTION:

       CREATE TABLE `[PROJECT_ID].[DATASET_ID].[TABLE_NAME]`
       (
         product_id INT64 NOT NULL,
         product_name STRING,
         datastream_metadata STRUCT,
         PRIMARY KEY (product_id) NOT ENFORCED
       )
       CLUSTER BY dept_no
       OPTIONS(
         max_staleness=INTERVAL '0-0 0 0:MINS:0' YEAR TO SECOND
       );
       ;
       

   Replace the following:

   • PROJECT_ID: the identifier of your Google Cloud project.
   • DATASET_ID: the identifier of the dataset in BigQuery.
   • TABLE_NAME: the name of the table for which you copied the DDL command.
   • MINS: the number of minutes that you want to set for the max_staleness option, for example 15.
6. Run the adjusted query to recreate the table in BigQuery.
7. Resume the stream.
8. Initiate backfill for the table.