Stream data from Oracle databases

This section contains information about:

  • The behavior of how Datastream handles data that's being pulled from a source Oracle database
  • The versions of Oracle database that Datastream supports
  • An overview of how to setup a source Oracle database so that data can be streamed from it to a destination
  • Known limitations for using Oracle database as a source

Behavior

Datastream supports two methods of extracting changes to the data from online redo log files: the Oracle binary log reader (Preview) and the Oracle LogMiner.

With the binary log reader method (Preview), the following behavior is observed:

  • If there's a read lag when extracting the changes from the online log files, Datastream extracts the changes from archived log files.

  • Datastream replicates only committed changes into the destination. Uncommitted or rolled back transactions aren't replicated.

  • The binary reader supports replicating Oracle VARCHAR2 columns longer than 4000 characters.

Datastream also supports the Oracle LogMiner feature for exposing changes to the data. The method has the following behavior:

  • All schemas or specific schemas from a given database, as well as all tables from the schemas or specific tables, can be selected.
  • All historical data is replicated.
  • All data manipulation language (DML) changes, such as inserts, updates, and deletes from the specified databases and tables, are replicated.
  • Datastream replicates both committed and, in some cases, uncommitted changes into the destination. Datastream reads uncommitted changes. In case of a rollback, the Datastream output records also include the opposite operation. For example, if there's a rolled-back INSERT operation, then the output records will also contain a corresponding DELETE operation. In this case, the event will appear as a DELETE event with only the ROWID.

ROWID based backfill

In Oracle, ROWID is a pseudocolumn that stores unique identifiers for rows in a table. Datastream uses the ROWID values for its backfill operations. Because of this, we recommend that you don't perform any actions that could change the ROWID values in your source Oracle database until the backfill operation completes.

The actions that can change the ROWID values include:

  • Physical movement of rows:

    • Export and import operations: when you export a table, and then you import it back, the physical location of rows might change, resulting in new ROWID values.
    • ALTER TABLE (...) MOVE command: moving a table to a different tablespace can change the physical storage and lead to ROWID changes.
    • ALTER TABLE (...) SHRINK SPACE command: this command compresses the table, potentially moving rows and affecting their ROWID values.
    • Partitioning operations: splitting, merging, or moving partitions can change the physical placement of rows and their ROWID values.

  • Flashback operations:

    • FLASHBACK TABLE command: restoring a table to a previous state involves deleting and re-inserting rows, thus creating new ROWID values.
    • FLASHBACK_TRANSACTION_QUERY: Similar to FLASHBACK TABLE. Rolling back a transaction can cause ROWID changes if rows were deleted or updated within the transaction.

Versions

Datastream supports the following versions of Oracle database:

  • Oracle 11g, Version 11.2.0.4 (supported only with the Logminer CDC method)
  • Oracle 12c, Version 12.1.0.2
  • Oracle 12c, Version 12.2.0.1
  • Oracle 18c
  • Oracle 19c
  • Oracle 21c

Datastream supports the following types of Oracle database:

  • Self-hosted on-premises or on any cloud provider
  • Amazon RDS for Oracle
  • Oracle Cloud
  • Oracle Exadata
  • Oracle RAC
  • Oracle Active Data Guard standby database

Setup

To set up a source Oracle database so that data from it can be streamed into a destination, you must configure the database to grant access, set up logging, and define a retention policy.

See Configure a source Oracle database to learn how to configure this database so that Datastream can pull data from it into a destination.

Known limitations

Known limitations for using Oracle database as a source include:

  • Streams are limited to 10,000 tables. If a stream includes more than 10,000 tables, then it might run into errors.
  • Datastream supports Oracle multi-tenant architecture (CDB/PDB), however, you can only replicate a single pluggable database in a stream.
  • Oracle Autonomous Database isn't supported.
  • For tables that don't have a primary key, Datastream uses the row's ROWID to perform a merge operation on the consumer side. Note that the ROWID might not be unique. If you delete and reinsert a row with Oracle's Export/Import utility, for example, then the row's ROWID might change. If you delete a row, then Oracle can reassign its ROWID to a new row inserted later.
  • Index-organized tables (IOTs) aren't supported.
  • Temporary tables aren't supported.
  • Columns of data types ANYDATA, BFILE, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, LONG/LONG RAW, SDO_GEOMETRY, UDT, UROWID, XMLTYPE aren't supported, and are replaced with NULL values.
  • To stream columns of large object data types, such as binary large objects (BLOB), character large objects (CLOB) and national character large objects (NCLOB), you need to include the streamLargeObjects flag in your stream configuration. If you don't include the flag, Datastream doesn't stream such columns and they're replaced with NULL values in the destination. For more information, see Enable streaming of large objects for Oracle sources.
  • For Oracle 11g, tables that have columns of data types ANYDATA or UDT aren't supported, and the entire table won't be replicated.
  • Oracle Label Security (OLS) isn't replicated.
  • Datastream periodically fetches the latest schema from the source as events are processed. If a schema changes, then some events from the new schema might be read while the old schema is still applied. In this case, Datastream detects the schema change, triggers a schema fetch, and reprocesses the failed events.
  • Not all changes to the source schema can be detected automatically, in which case data corruption may occur. The following schema changes may cause data corruption or failure to process the events downstream:
    • Dropping columns
    • Adding columns to the middle of a table
    • Changing the data type of a column
    • Reordering columns
    • Dropping tables (relevant if the same table is then recreated with new data added)
    • Truncating tables
  • Datastream doesn't support replicating views.
  • Datastream supports materialized views. However, new views created while the stream is running aren't backfilled automatically.
  • When using the Oracle LogMiner method, SAVEPOINT statements aren't supported and can cause data discrepancy in case of a rollback.
  • When using the Oracle LogMiner method, Datastream doesn't support replicating tables and columns whose names exceed 30 characters.
  • Datastream supports the following character set encodings for Oracle databases:
    • AL16UTF16
    • AL32UTF8
    • IN8ISCII
    • IW8ISO8859P8
    • JA16SJIS
    • JA16SJISTILDE
    • KO16MSWIN949
    • US7ASCII
    • UTF8
    • WE8ISO8859P1
    • WE8ISO8859P9
    • WE8ISO8859P15
    • WE8MSWIN1252
    • ZHT16BIG5
  • Datastream doesn't support replicating zero date values. Such dates are replaced with NULL values.
  • Datastream doesn't support direct connectivity to databases using the Single Client Access Name (SCAN) feature in Oracle Real Application Clusters (RAC) environments. For information about potential solutions, see Oracle source behavior and limitations.
  • If the source is an Oracle Active Data Guard standby database, Datastream doesn't support replicating encrypted data.

Additional limitations when using the binary reader

  • Binary reader doesn't support the following features:

    • Transparent Database Encryption (TDE)
    • Hybrid Columnar Compression
    • Secure files
    • ASM isn't supported for Amazon RDS sources.
    • The binary reader CDC method doesn't support Oracle 11g and earlier versions.

What's next