Skip to main content

Replicating Oracle data to Azure Cosmos DB

CosmosDBWriter can continuously replicate one or many Oracle tables to Cosmos DB collections.

You must create the target collections in Cosmos DB manually. Each partition key name must match one of the column names in the Oracle source table.

If you wish to run the following examples, adjust the Oracle Reader properties and Cosmos DB Writer properties to reflect your own environment.OracleReader properties

  1. In Cosmos DB, create database MyDB containing the following collections (note that the collection and partition names are case-sensitive, so when replicating Oracle data they must be uppercase):

    • SUPPLIERS with partition key /LOCATION

    • CUSTOMERS with partition key /COUNTRY

  2. In Oracle, create tables and populate them as follows:

    CREATE TABLE SUPPLIERS(ID INT, NAME VARCHAR2(40), LOCATION VARCHAR2(200), PRIMARY KEY(ID));
    CREATE TABLE CUSTOMERS(ID INT, NAME VARCHAR2(40), EMAIL VARCHAR2(55), COUNTRY VARCHAR2(75),
      PRIMARY KEY(ID));
    COMMIT;
    INSERT INTO SUPPLIERS VALUES(100036492, 'Michelle', 'michelle@example.com', 'West Virginia');
    INSERT INTO CUSTOMERS VALUES(23004389, 'Manuel', 'manuel@example.com', 'Austria');
    COMMIT;
  3. In Striim, run the following application to perform the initial load of the existing data using DatabaseReader:

    CREATE APPLICATION Oracle2CosmosInitialLoad;
     
    CREATE SOURCE OracleJDBCSource USING DatabaseReader (
      Username: '<Oracle user>',
      Password: '<Oracle user password>',
      ConnectionURL: '<Oracle connection URL>',
      Tables: 'MYSCHEMA.%'
    )
    OUTPUT TO OracleStream;
     
    CREATE  TARGET CosmosTarget USING CosmosDBWriter  (
      ServiceEndpoint: '<Cosmos DB connection string>',
      AccessKey: '<Cosmos DB account read-write key>',
      Collections: 'MYSCHEMA.%,MyDB.%',
      ConnectionPoolSize: 3
     )
    INPUT FROM OracleStream;

    After the application is finished, the Cosmos DB collections should contain documents similar to the following.

    MyDB.SUPPLIERS:

    {
        "LOCATION": "West Virginia",
        "ID": "100036492",
        "NAME": "Example Inc.",
        "id": "100036492",
        "_rid": "CBcfAKX3xWACAAAAAAAACA==",
        "_self": "dbs/CBcfAA==/colls/CBcfAKX3xWA=/docs/CBcfAKX3xWACAAAAAAAACA==/",
        "_etag": "\"00008000-0000-0000-0000-5bacc99b0000\"",
        "_attachments": "attachments/",
        "_ts": 1538050459
    }
    

    MyDB.CUSTOMERS:

    {
        "COUNTRY": "Austria",
        "ID": "23004389",
        "EMAIL": "manuel@example.com",
        "NAME": "Manuel",
        "id": "23004389",
        "_rid": "CBcfAJgI4eYEAAAAAAAACA==",
        "_self": "dbs/CBcfAA==/colls/CBcfAJgI4eY=/docs/CBcfAJgI4eYEAAAAAAAACA==/",
        "_etag": "\"d600b243-0000-0000-0000-5bacc99c0000\"",
        "_attachments": "attachments/",
        "_ts": 1538050460
    }
    {
        "COUNTRY": "Austria",
        "ID": "23908876",
        "EMAIL": "michelle@example.com",
        "NAME": "Michelle",
        "id": "23908876",
        "_rid": "CBcfAJgI4eYFAAAAAAAACA==",
        "_self": "dbs/CBcfAA==/colls/CBcfAJgI4eY=/docs/CBcfAJgI4eYFAAAAAAAACA==/",
        "_etag": "\"d600b443-0000-0000-0000-5bacc99c0000\"",
        "_attachments": "attachments/",
        "_ts": 1538050460
    }
    
  4. In Striim, run the following application to continuously replicate new data from Oracle to Cosmos DB using OracleReader:

    CREATE APPLICATION Oracle2CosmosIncremental;
    
    CREATE SOURCE OracleCDCSource USING OracleReader (
      Username: '<Oracle user>',
      Password: '<Oracle user password>',
      ConnectionURL: '<Oracle connection URL>',
      Tables: 'DB.ORDERS;DB.SUPPLIERS;DB.CUSTOMERS'
    )
    OUTPUT TO OracleStream;
    
    CREATE  TARGET CosmosTarget USING CosmosDBWriter  (
      ServiceEndpoint: '<Cosmos DB connection string>',
      AccessKey: '<Cosmos DB account read-write key>',
      Collections: 'DB.%,MyDB.%',
      ConnectionPoolSize: 3
     )
    INPUT FROM OracleStream;
     
    END APPLICATION Oracle2CosmosIncremental;
  5. In Oracle, enter the following:

    INSERT INTO SUPPLIERS VALUES(100099786, 'Example LLC', 'Ecuador');
    UPDATE CUSTOMERS SET EMAIL='msanchez@example.com' WHERE ID='23004389';
    DELETE FROM CUSTOMERS WHERE ID='23908876';
    COMMIT;

    Within 30 seconds, those changes in Oracle should be replicated to the corresponding Cosmos DB collections with results similar to the following.

    MyDB.SUPPLIERS:

    {
        "LOCATION": "West Virginia",
        "ID": "100036492",
        "NAME": "Example Inc.",
        "id": "100036492",
        "_rid": "CBcfAKX3xWACAAAAAAAACA==",
        "_self": "dbs/CBcfAA==/colls/CBcfAKX3xWA=/docs/CBcfAKX3xWACAAAAAAAACA==/",
        "_etag": "\"00008000-0000-0000-0000-5bacc99b0000\"",
        "_attachments": "attachments/",
        "_ts": 1538050459
    }
    {
        "LOCATION": "Ecuador",
        "ID": "100099786",
        "NAME": "Example LLC",
        "id": "100099786",
        "_rid": "CBcfAKX3xWADAAAAAAAADA==",
        "_self": "dbs/CBcfAA==/colls/CBcfAKX3xWA=/docs/CBcfAKX3xWADAAAAAAAADA==/",
        "_etag": "\"0000e901-0000-0000-0000-5bacc99b0000\"",
        "_attachments": "attachments/",
        "_ts": 1538050559
    }
    

    MyDB.CUSTOMERS:

    {
        "COUNTRY": "Austria",
        "ID": "23004389",
        "EMAIL": "msanchez@example.com",
        "NAME": "Manuel",
        "id": "23004389"
        "_rid": "CBcfAJgI4eYEAAAAAAAACA==",
        "_self": "dbs/CBcfAA==/colls/CBcfAJgI4eY=/docs/CBcfAJgI4eYEAAAAAAAACA==/",
        "_etag": "\"d600b243-0000-0000-0000-5bacc99c0000\"",
        "_attachments": "attachments/",
        "_ts": 1538050460
    }
    

Striim provides a template for creating applications that read from Oracle and write to Cosmos DB. See Creating an application using a template for details.