Replicate Data

Use data replication to import data from Oracle Cloud applications into high-performant data stores, such as Oracle Database Cloud Service and Oracle Big Data Cloud, for visualization and analysis in Oracle Analytics Cloud.

With data replication, you can import and transform your data without using additional extract-transform-load (ETL) tools.

Replicate Your Data

Use a replication flow to copy data from a data source to a data target for analysis in Oracle Analytics Cloud. For example, you might copy data from Oracle Fusion Applications to Oracle Big Data Cloud.
  1. Set up a connection for your data source:
    1. From the Home page, click Create, then Replication Connection, and then select the type of data source you want to copy.
      For example, to replicate data from Oracle Fusion Applications, click Oracle Fusion Application Storage.
    2. At the Create Connection dialog, specify the connection details.
      For example, to replicate data from Oracle Fusion Applications, specify connection details for your Oracle Cloud Infrastructure Object Storage or Object Storage Classic instance. See Create a Replication Connection for Oracle Fusion Applications.
  2. Set up a connection for your data target:
    1. From the Home page, click Create, Replication Connection, and then select the type of data source you want to copy the data into.
    2. In the Create Connection dialog, specify the connection details of your data target.
      For example, to replicate to Oracle Autonomous Data Warehouse, click Oracle Autonomous Data Warehouse Cloud.
  3. From the Home page, click Create, then click Data Replication.
  4. In the Create Data Replication-Select Source Connection dialog, select the source connection that you created in Step 1.
  5. In the Create Data Replication-Select Target Connection dialog, select the target connection that you created in Step 2.
  6. If the replication target has multiple schemas, use the Schema list to select the schema to use.
  7. In the Replicate Objects area, select the object that you want to replicate:
    • Click the check box next to each object that you want to replicate.

      For Fusion Applications data sources, if the view that you want to replicate isn’t displayed in the list, click the Add a custom view object option below the list. Enter the full path and name of the view, for example, FscmTopModelAM.TaskDffBIAM.FLEX_BI_TaskDFF, then click Add.

    • When you select a table, you include all attributes by default. Use the check boxes on the right-hand pane to select or deselect attributes.

    • To change a primary key, click the key icon and select Assign Primary Key or Reorder Primary Key. The primary key is used for upsert operations to determine whether a record is inserted or updated.

      To improve indexing, it’s best practice to order the columns so that the most selective columns are first and the least selective columns are last. Do this by clicking the Reorder Primary Key option from the context menu of any of the primary key columns.

    • To use multiple columns as a primary key, select the key icon next to each column to include in the key.

    • To replicate a subset of data based on a filter, click Edit Filter to display the filter editor and specify a filter expression (without the closing semicolon). The expression format you use depends on the filter language that your data source supports. Common filter languages include SQL, XML, and so on. Refer to the documentation for your data source for details.

      Data Source Type Example filter expressions
      Oracle Fusion Applications "__DATASTORE__.LookupType not in ('GROUPING_SEPARATOR','HZ_FORMAT_DELIMITERS','ICX_NUMERIC_CHARACTERS')"
      Oracle RightNow

      lookupname like 'Admin%'

      id > 2

      Eloqua '{{Account.Field(M_Annual_Revenue1)}}' > '2000’

      Use the Validate option to verify the expression before you click OK to save the filter.

    • To replicate a subset of data based on a timestamp, click the Replicate From calendar icon and specify a start date.

      The Replicate From option only applies to tables that have at least one incremental identifier column defined.

    • Use the Load Type to specify whether to perform an incremental load or a full load.

      If you select Incremental, you replicate all data on the first run and on subsequent runs you replicate only new data. Incremental updates require tables with a primary key and at least one incremental identifier column.

      If you select Full, the target table is initialized and you replicate all data.

  8. Save your replication project.
  9. To start the data load, click Run Replication Flow.

Create a Replication Connection for Oracle Fusion Applications

To replicate data from Oracle Fusion Applications, you set up a data replication connection in Oracle Analytics Cloud.

  1. In Oracle Analytics Cloud, click Create, then Replication Connection.
  2. Click Oracle Fusion Application Storage.
  3. Specify these connection details:
    • Storage Type - Select OCI for Oracle Cloud Infrastructure Object Storage or Classic for Oracle Cloud Infrastructure Object Storage Classic.
    • Storage Region - Specify the OCI region where the storage bucket resides (for example, us-ashburn-1). In the Object Storage API endpoint, the region is specified immediately before oraclecloud.com. For example, https://objectstorage.us-ashburn-1.oraclecloud.com.
    • Storage Tenancy OCID - Specify the Oracle Cloud Identifier (OCID) for the tenancy where the bucket resides.
    • Storage User OCID - Specify the Oracle Cloud Identifier (OCID) for the user that will access the storage bucket.
    • Storage Bucket - Specify the name of the storage bucket.
    • URL - Specify the API endpoint for the Fusion Enterprise Scheduler Web Service. For example, https://<fa-host>/bi/ess/esswebservice or just the hostname <fa-host>.
    • Username - Specify the username of the Fusion Applications user with permissions to access BI Cloud Connector.
    • Password - Specify the password of the Fusion Applications user with permissions to access BI Cloud Connector.
    • Storage API Key - Click Generate, then click Copy to create an API signing key. Oracle Analytics Cloud Data Replication uses this key to authenticate when it accesses the object storage bucket.
    • Storage Connection - Specify the storage connection in BI Cloud Connector Console to use when writing the extracted data. The BI Cloud Connector storage connection must point to the same bucket as the Oracle Analytics Cloud connection.
  4. In separate browser window or tab, navigate to OCI Console, then Identity, then Users, then User Details for your replication user account.
  5. Under the API Keys section, add these keys:
    • Add the public key for the Data Replication connection that you copied to the clipboard in Step 4.
    • Add the public key that was saved when you created the storage connection in BI Cloud Connector Console on the Configure External Storage page.
  6. Return to the Oracle Analytics Cloud browser window or tab, and on the Oracle Fusion Application Storage dialog click Save. If you've entered the information correctly, the connection is saved.

Replicate Your Data Regularly

You can schedule replication flows to run regularly. For example, if your source data changes weekly, you might replicate your data once per week to keep it up-to-date.

  1. In the Home page, click Navigator, then click Data, then click Data Replications.
    A list of replication flows that you can schedule is displayed. If you haven’t already created a replication flow, do that first.
  2. Right-click the replication flow that you want to execute regularly and click New Schedule.
  3. In the Schedule dialog, specify when to start the replication flow and how often you want it to run.
  4. To monitor the progress of your scheduled jobs, in the Home page, click Navigator, then click Jobs.
  5. To change the schedule, right-click the replication flow that you scheduled, click Inspect, then click Schedule and make your changes.

Modify a Replication Flow

You can change how your data is replicated by modifying the replication flow that loads your data.
  1. In the Home page, click Navigator, then Data, then Data Replications.
  2. Right-click the replication flow that you want to modify, and click Open, and make your changes.

Monitor and Troubleshoot a Replication Flow

You can monitor a replication flow to check progress and troubleshoot issues.

If an error occurs during a replication flow and the replication is run again, then the replication starts from where the previous error was encountered and any duplicate rows are removed.
  1. To monitor jobs associated with a replication flow:
    1. In the Home page, click Navigator, then Jobs.
    2. Review the current status of your job in the Status column.
    3. To view job history, right-click the job, and click History.
    4. To stop a job, right-click the job and click Cancel. To remove it completely, click Delete.
  2. To investigate or troubleshoot the last data load for a replication flow:
    1. In the Home page, click Navigator, then Data, then Data Replications.
    2. Right-click the replication flow that you want to investigate, and click Run Details.
    The Run Details dialog shows detailed information about the last replication operation. For example, you can see how many rows were loaded for each table and detailed error messages for each table.

Move Replicated Data to a Different Target Database

If you change the target database for data replication, you can migrate the current data to the new database, and reconfigure your connections to replicate to your new database.

For example, you might need to do this if your organization migrates from Oracle Cloud Infrastructure - Classic to Oracle Cloud Infrastructure.

  1. Make sure that your new target schema has the required privileges and permissions. See What Privileges and Permissions Are Required?.
  2. Copy the replicated tables and the following replication system tables to the new target schema.
    • All replicated tables (along with corresponding indexes, constraints)
    • REPL$_ERR_SUMMARY
    • E$_*
    • SDS_*
  3. Configure a replication connection for the new target database.
    • If your new target database is of the same type as your old target database, then simply edit your existing replication connection and update the connection details.

      In the Connections page, locate the replication connection, click Inspect, and use the General tab to update the details for the new target database.

    • If your new target database is of a different type, then create a new replication connection for that type and specify the connection details.

      Click Create, then Replication Connection, select the appropriate type, and specify the details.

  4. Update each data replication entry that is configured to use the old target database connection details.
    1. Open the Data Replications page, and select the data replication you want to edit.
    2. In the Replication Target area:
      • If your new target database is of the same type as your old target database, make sure that Schema is set correctly for the new database.
      • If your new target database is of a different type, click Select and select the new target connection, then click Schema and set correctly for the new database.
  5. From the Home page, navigate to Data and then Connections. Locate the replication connection for your target database, click Inspect, and use the Tables tab to verify the table information for the new target schema.

You can now resume data replication in incremental mode into the new database.