Monitor the Openflow Connector for Oracle¶

Note

The Openflow Connector for Oracle is subject to the terms of service in the Openflow Connector for Oracle Addendum.

The connector writes the metrics described on this page to your Openflow event table. A subset of these metrics is also visualized in the Openflow Connectors Dashboard. For generic Openflow telemetry, see Openflow telemetry.

Metrics¶

All metrics include the same resource and connector attributes as application metrics. Table-level metrics also include table attributes. Some metrics also include additional attributes.

Metric	Type	Resolution	Description
db.table.status	Gauge	Table	Current replication status of the table. Reported every 60 seconds for every table in replication. See Replication status values.
db.table.status.count	Gauge	Connector	Count of tables in each status. Reported every 60 seconds, as long as any tables are in replication. See Replication status values.
db.last.ingestion.time	Gauge	Table	Time when data was last written to the destination table, in milliseconds since the UNIX epoch. Reported every 60 seconds for up to 3 days after the last write, even when no new data arrives.
db.records.ingested	Counter	Table	Number of records written to Snowflake in each replication phase, indicated by the `db.replication.phase` attribute. In the `SNAPSHOT` phase, the value represents the number of records written to the destination table. In the `INCREMENTAL` phase, the value represents the number of records written to journal tables. Reported every 60 seconds for every table that received data since the last report.
db.commit.to.merge.latency	Gauge	Table	Highest time, in milliseconds, from when a change was committed in the source database until it was merged into the destination table. Reported every 60 seconds for every table that received data since the last report.
db.commit.to.capture.latency	Gauge	Connector	Highest time, in milliseconds, from when a change was committed in the source database until the connector read it. Reported every 60 seconds while the connector receives data.
db.capture.to.journal.latency	Gauge	Connector	Highest time, in milliseconds, from when the connector read a change until it was written to the journal table in Snowflake. Reported every 60 seconds while the connector receives data.

Table-level metrics support up to 75,000 tables in a single runtime. Above that limit, metrics might be lost.

Source object attributes¶

Table-level metrics identify the source table with the following record attributes:

Attribute	Description
source.database.name	Name of the source database.
source.schema.name	Name of the source schema.
source.table.name	Name of the source table.

Replication status values¶

The db.table.status metric encodes the replication status of a table as a number. The mapping is as follows:

Value	Name	Description
0	-	The table is no longer replicated, for example because it was removed from replication. Reported once.
1	NEW	The table is scheduled for replication, but replication hasn’t started.
2	SNAPSHOT_REPLICATION	The connector is copying the existing data of the table.
3	INCREMENTAL_REPLICATION	The connector is replicating changes.
4	FAILED	Replication has permanently stopped for an unknown reason.
4001	COMMUNICATION_FAILURE	The connector failed to communicate with the source database.
4003	INVALID_FETCH_STRATEGY	The fetch strategy for the table is invalid or unsupported.
4004	OVERSIZED_VALUE	A column value exceeds the maximum allowed size.
4005	MERGE_QUERY_FAILED	The merge query in Snowflake failed after the maximum number of retries.
4006	MERGE_QUERY_UNKNOWN_STATUS	The merge query in Snowflake returned an unknown status.
4008	MISSING_PRIMARY_KEYS	The table can't be replicated without a primary key.
4009	MISSING_REQUIRED_ATTRIBUTES	A FlowFile is missing required attributes, or has invalid values for them.
4010	MISSING_ROWID_RANGE	No ROWID range was provided.
4011	NO_COLUMNS_IN_TABLE	The source table has no columns.
4012	NO_SCHEMA_NAME_PROVIDED	No schema name was provided.
4013	NO_TABLE_NAME_PROVIDED	No table name was provided.
4015	REMOVED_FROM_REPLICATION	The table was removed from the source database, no longer matches the table filter, or is in a permanently failed state.
4016	SCHEMA_CONVERSION_ERROR	The table schema couldn’t be converted to the expected format.
4017	SCHEMA_NOT_FOUND	The schema of the table couldn't be found.
4019	SNOWFLAKE_OBJECT_OPERATION_FAILED	The connector failed to create, update, or manage a Snowflake object.
4020	SNOWPIPE_UPLOAD_FAILED	The connector failed to upload data with Snowpipe Streaming.
4021	VALUE_MAPPING_ERROR	A value couldn't be mapped during data transformation.
4022	FLOWFILE_READ_ERROR	FlowFile content couldn’t be read or parsed.
4024	SNOWPIPE2_UPLOAD_FAILED	Snowflake rejected data sent with Snowpipe Streaming because of invalid record content.
4025	SCHEMA_RECONCILE_FAILED	Before a new snapshot, the connector failed to reconcile the destination table schema with the source table schema.
4026	CLONE_TO_ARCHIVE_FAILED	Before a new snapshot, the connector failed to clone the destination table to its archive.
4027	TRUNCATE_DESTINATION_FAILED	Before a new snapshot, the connector failed to clear the destination table.
4030	REPLICATION_KEY_INVALIDATED	A replication key column was dropped or renamed. Update the key configuration, and then remove the table from replication and add it again.
4031	LOGICAL_KEY_CONFIG_INVALID	The user-defined logical key configuration is invalid. For example, the key references a column that doesn't exist, contains duplicate columns, or is empty. Correct the table key configuration.
4046	LOGICAL_KEY_BEFORE_IMAGE_MISSING	A change didn't include the previous values of all logical key columns, because supplemental logging in the source database doesn't cover the logical key. Add supplemental logging for the logical key columns, and then remove the table from replication and add it again.

Values of 4001 and higher mean that replication has permanently stopped for the listed reason.

Alerts¶

The following alert templates in the OPENFLOW template group are specific to this connector:

Template	Description	Settings
Table replication failure alert	Fires for each table whose replication failed during the lookback window. Can be configured for the account, a deployment, a runtime, or a connector.	Lookback window (hours): Default: 1. Table names filter: Optional. A comma-separated list of the tables to monitor. Leave empty to monitor all tables.
Openflow data staleness alert	Fires for each of the specified tables when no new data was written to its destination table for longer than the staleness threshold. Can be configured for the account, a deployment, a runtime, or a connector.	Table names filter: Required. A comma-separated list of the tables to monitor. Staleness threshold (hours): Default: 4. Lookback window (hours): Default: 6.

For more information and generic Openflow alerts, see Set up alerts for Openflow.

Example queries¶

The following queries assume that Openflow sends telemetry to the default event table, SNOWFLAKE.TELEMETRY.EVENTS. If you use a different event table, substitute its name.

Find replication failures¶

This query returns the tables whose latest status in the last hour is failed:

SELECT
  record_attributes:"connector.name"::STRING AS connector,
  record_attributes:"source.database.name"::STRING AS source_database,
  record_attributes:"source.schema.name"::STRING AS source_schema,
  record_attributes:"source.table.name"::STRING AS source_table,
  value::NUMBER AS status,
  timestamp
FROM SNOWFLAKE.TELEMETRY.EVENTS
WHERE timestamp > DATEADD(hour, -1, SYSDATE())
  AND record_type = 'METRIC'
  AND record:metric:name = 'db.table.status'
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY connector, source_database, source_schema, source_table
    ORDER BY timestamp DESC) = 1
  AND status >= 4;

Find destination tables without recent data¶

This query returns the tables that had no data written to the destination table in the last hour. A table without changes in the source database also appears in the results.

SELECT
  record_attributes:"connector.name"::STRING AS connector,
  record_attributes:"source.database.name"::STRING AS source_database,
  record_attributes:"source.schema.name"::STRING AS source_schema,
  record_attributes:"source.table.name"::STRING AS source_table,
  TO_TIMESTAMP_LTZ(MAX(value::NUMBER), 3) AS last_ingestion_time
FROM SNOWFLAKE.TELEMETRY.EVENTS
WHERE timestamp > DATEADD(hour, -2, SYSDATE())
  AND record_type = 'METRIC'
  AND record:metric:name = 'db.last.ingestion.time'
GROUP BY connector, source_database, source_schema, source_table
HAVING last_ingestion_time < DATEADD(hour, -1, CURRENT_TIMESTAMP());

Plot replication latency over time¶

This query returns the latency from source commit to destination table for each table over the last 24 hours. To plot it, show the results as a line chart with timestamp on the X-axis and latency_seconds on the Y-axis.

SELECT
  timestamp,
  record_attributes:"connector.name"::STRING AS connector,
  record_attributes:"source.database.name"::STRING AS source_database,
  record_attributes:"source.schema.name"::STRING AS source_schema,
  record_attributes:"source.table.name"::STRING AS source_table,
  value::NUMBER / 1000 AS latency_seconds
FROM SNOWFLAKE.TELEMETRY.EVENTS
WHERE timestamp > DATEADD(day, -1, SYSDATE())
  AND record_type = 'METRIC'
  AND record:metric:name = 'db.commit.to.merge.latency'
ORDER BY timestamp;

Count records written per replication phase¶

This query returns the number of records written for each table per hour and replication phase in the last 24 hours:

SELECT
  DATE_TRUNC(hour, timestamp) AS hour,
  record_attributes:"connector.name"::STRING AS connector,
  record_attributes:"source.database.name"::STRING AS source_database,
  record_attributes:"source.schema.name"::STRING AS source_schema,
  record_attributes:"source.table.name"::STRING AS source_table,
  record_attributes:"db.replication.phase"::STRING AS phase,
  SUM(value::NUMBER) AS records
FROM SNOWFLAKE.TELEMETRY.EVENTS
WHERE timestamp > DATEADD(day, -1, SYSDATE())
  AND record_type = 'METRIC'
  AND record:metric:name = 'db.records.ingested'
GROUP BY hour, connector, source_database, source_schema, source_table, phase
ORDER BY hour, connector, source_schema, source_table;