1. Using Data Integration Platform Cloud
  2. Reference Information
  3. Data Source Connection Details

17 Data Source Connection Details

Prepare and create connections to your source and target data sources for your tasks in Oracle Data Integration Platform Cloud.

Topics:

Create a File Connection

Learn to create a connection to a file in Oracle Data Integration Platform Cloud.

  1. From the Getting Started section of the Home page, click Create from the Connection tile or from the Create menu in the Catalog, select Connection. You can also create Connections from any Task screen.
  2. Complete the fields in the General Information section. The Identifier is to identify this connection through a string with no spaces, but is not currently used. If you want to edit this auto-generated field, then you must only include capital letters, numbers, and underscores (_) with no space.

    • For Agent Name, select the agent you have created for the this connection.

    • For Type, select File.

  3. Click Test Connection. If successful, click Save.

Create a Kafka Connect Connection

Learn to create a connection to Kafka Connect in Oracle Data Integration Platform Cloud.

Set up Format for Kafka Topics

Before you create a Kafka Connect connection in Oracle Data Integration Platform Cloud, you must choose a format for your Kafka topics in the Kafka producer properties file.

  1. Save your Kafka producer properties file in the ${agent_unzip_loc}/dicloud/gghomebigdata/dirprm  directory:

    • To use the provided example file:

      cp ${agent_unzip_loc}/dicloud/gghomebigdata/AdapterExamples/big-data/kafka_connect/kafkaconnect.properties$ ${agent_unzip_loc}/dicloud/gghomebigdata/dirprm/kafkaconnect.properties

    • To use your own producer file, rename your producer.properties file to kafkaconnect.properties and copy it to ${agent_unzip_loc}/dicloud/gghomebigdata/dirprm. Review the provided example file for reference.

    In order to see the gghomebigdata folder , you must have already downloaded your agent with the Big Data (OGG) component and registered it with Oracle Data Integration Platform Cloud.
  2. Update the kafkaconnect.properties file to set the delivery format for Kafka topics to either JSON or Avro.
    • For Avro, uncomment the Avro section of the kafkaconnect.properties file and comment out the JSON section.

      Having an Avro format, by only uncommenting the JSON section sends schema information in JSON and Avro formats. To omit the JSON schema information from the messages set the following: 

      key.converter.schemas.enable=false
value.converter.schemas.enable=false

      Note:

    • For JSON leave the Avro section commented out, as is.

    Here's the example kafkaconnect.properties file for messages to be delivered in a JSON format: 

    #JSON Converter Settings
key.converter=org.apache.kafka.connect.json.JsonConverter
key.converter.schemas.enable=true
value.converter=org.apache.kafka.connect.json.JsonConverter
value.converter.schemas.enable=true
 
#Avro Converter Settings
#key.converter=io.confluent.connect.avro.AvroConverter
#value.converter=io.confluent.connect.avro.AvroConverter
#key.converter.schema.registry.url=http://localhost:8081
#value.converter.schema.registry.url=http://localhost:8081
 
 
#Adjust for performance
buffer.memory=33554432
batch.size=16384
linger.ms=0
    The batch.size property is the maximum number of bytes to buffer before sending data to Kafka. The linger.ms property is the maximum milliseconds to wait before sending data. Setting linger.ms to zero causes messages to be sent immediately to Kafka. If you set both properties, data is sent to Kafka according to whichever property reaches its limit first.

Create the Connection

  1. From the Home page or from the Create menu in the Catalog, select Create Connection.

  2. Complete the fields in the General Information section.

    • The Identifier is to identify this connection through a string with no space. If you want to edit this auto-generated field, then you must only include capital letters, numbers, and underscores (_) with no space.

    • For Agent, select the agent you have created for the this connection.

    • For Type, select Kafka Connect.

  3. In the Connection Settings, enter the location of the following files to append to the system's classpath.

    • For JSON format

      • Kafka client libraries

    • For Avro format

      • Kafka client libraries

      • Confluent Avro converters

      • Schema registry

      The Kafka Connect client libraries do not ship with the Big Data (OGG) component that you download and register with the Data Integration Platform Cloud remote agent. You must obtain the Kafka Connect client libraries that match the version of Kafka in Kafka Connect and point to them in the Java Classpath field in the Connection Settings. For a list of the required client JAR files by version, see Kafka Connect Client Libraries.

      The default location of the Kafka Connect client JARs is the {kafka_connect_install_dir}/libs/* directory. Use the * wildcard character to include all of the JAR files from each of the associated directories. Do not use *.jar. And make sure you don’t have spaces in the classpath. Here is an example:
      dirprm:{kafka_connect_install_dir}/share/java/kafka-serde-tools/*:{kafka_connect_install_dir}/share/java/kafka/*:{kafka_connect_install_dir}/share/java/confluent-common/*
  4. In the Properties section, for Kafka Producer Config file, enter kafkaconnect.properties or the file name you created under /gghomebigdata/dirprm if you used your own file or changed it from kafkaconnect.properties to something else. Either way, this file must be located in ${agent_unzip_loc}/dicloud/gghomebigdata/dirprm for the connection to work.

  5. Complete the rest of the properties listed in the Properties section based on the following explanations. Oracle Data Integration Platform Cloud's Kafka Connect handler will publish messages to Kafka topics based on these properties.

    Table 17-1 Kafka Connect Connection Properties

    Property Default Value Shown in the Field Explanation

    Topic Mapping Template

    ${fullyQualifiedTablename}

    This value will be the name of your topic. If you choose ${fullyQualifiedTablename}, the fully qualified table name includes a period (.) delimiter between the schema and table names.

    For example, if there's a transaction with an insert operation in a data source with schema ABC, in table X, then the Kafka producer will create a Kafka topic named ABC.X and add that inserted data to the ABC.X topic.

    In case of multiple databases, such as databases with PDBs, then the ${fullyQualifiedTableName} is database_name.schema_name.table_name. For example, there is a salesdb.contacts table and the contacts table has a column called first_name. Then the Kafka topic will be salesdb.contacts.first_name.

    You can either select one of the values from the drop-down menu or customize your topic names. This field is editable, For example, a Kafka topic name can be ${schemaName}_${tableName}

    Note:

    Custom topic names are not validated. If you enter something that is not in a predefined template keywords, then the value will not be resolved. For example, {date}_{table} is not valid and all your data will go to one topic that is called {date}_{table}.

    For correct template names and an explanation of all your options for this field, field, see Template Keywords for Kafka Connect Connection table on this page.

    You can override the value of Topic Mapping Template later, when you set up a Replicate Data Task with a Kafka Connect target. This field is called Mapping Pattern in the Replicate Data Task.

    Key Mapping Template

    ${primaryKeys}

    When topics have keys attached to them, messages with the same key go to the same partition in a topic. Kafka offers order within a partition, but not across partitions in a topic, so having keys provides order in the partitions.

    Choose a key for the partitions in your topic. The default key for a topic is the primary key. That means that the data in each partition have the same primary key. You can either select the default primary key value or one of the values from the drop-down menu or customize your key and decide how you want your topics to be partitioned. This field is editable, For example, a Kafka topic key can be ${opType} and all the inserts will go in one partition and all the updates in another one, for each topic.

    Note:

    Custom topic names are not validated. If you enter something that is not in a predefined template keywords, then the value will not be resolved. For example, {date}_{table} is not valid and all your data will go to one topic that is called {date}_{table}.

    Custom topic names are not validated. If you enter something that is not in a predefined template keywords, then the value will not be resolved. For example, {date}_{table} is not valid and all your data will go to one topic that is called {date}_{table}.

    For correct template names and an explanation of all your options for this field, see Template Keywords for Kafka Connect Connection table on this page.

    You can override the value of Key Mapping Template later, when you set up a Replicate Data Task with a Kafka Connect target. This field is called Key Mapping Pattern in the Replicate Data Task.

    Include Table Name

    (Selected)

    If you want the fully qualified table name to be included in the messages that are delivered to your topics, then select this option. Your messages will then include a field called table for which the value is the fully qualified table name.

    Include Op Type

    (Selected)

    If you want the type of source operation to be included in the messages that are delivered to your topics, select this option. Your messages will then include a field called op_type with the value I for insert, U for update, D for delete and T for truncate.

    You can change the letters that represent these operations to other letters or words in the following fields available in the Properties section of the Kafka Connect Connection dialog: insertOpKey, updateOpKey, deleteOpKey and truncateOpKey.

    Include Op Timestamp

    (Selected)

    If you want a time stamp for when the source operation was committed to be included with your messages, then select this option. Your messages will then include a field called op_ts for which the value is the operation timestamp (commit timestamp) from the source transaction.

    Include Current Timestamp

    (Selected)

    If you want a time stamp for when the message was delivered to your topic, then select this option. Your messages will then include a field called current_ts for which the value is the current timestamp of when the handler delivered the message.

    Include Position

    (Selected)

    When a Replicate Data Task starts, Oracle Data Integration Platform Cloud creates a starting position with a unique sequence number in a file on the source called a trail file. Then with every transaction, it moves its position to the position of last record read in the data source.

    Select this option to create a field in the output messages called pos for which the value is the position (sequence number + offset) of the transactions from the data source trail file.

    Include Primary Keys

    (Deselected)

    Select this option to include a field in your messages called primary_keys. The value of this field is an array of names of the primary key columns.

    Include Tokens

    (Deselected)

    Select to include a map field in your messages called maps. The key is tokens and the value is a map where the keys and values are the token keys and values from the trail file described in the IncludePosition property.

    Each change record written by Oracle Data Integration Platform Cloud to a trail file includes a header area, a data area, and possibly a user token area. The record header contains information about the transaction environment, and the data area contains the actual data values that were extracted. The token area contains information that is specified by users for use in column mapping and conversion.

    Message Formatting

    row

    Select row for the output messages to be modeled as a row in each topic. Set to op for the output messages to be modeled with an operation format. Row applies to both JSON and Avro. Op only applies to Avro. If you have chosen JSON for the delivery format, then op will be ignored, even if you select it.

    Note:

    If you choose op for message formatting, then you can't include the operation type (insert, abend, update, delete) in your messages.

    Insert Op Key

    I

    I is for insert. Keep this default, or enter a value to be used for the insert operations. This value will be included with your message if the operation on the delivered data is insert. Ensure that includeOpType property is already selected. This value will be included in a field called op_type.

    Leave this field blank if you don't want insert operations included in the Kafka topic

    Update Op Key

    U

    U is for update. Keep this default, or enter a value to be used for the update operations. This value will be included with your message if the operation on the delivered data is update. Ensure that includeOpType property is already selected. This value will be included in a field called op_type.

    Leave this field blank if you don't want update operations included in the Kafka topic

    Delete Op Key

    D

    D is for delete. Keep this default, or enter a value to be used for the delete operations. This value will be included with your message if the operation on the delivered data is delete. Ensure that includeOpType property is already selected. This value will be included in a field called op_type.

    Leave this field blank if you don't want delete operations included in the Kafka topic

    Truncate Op Key

    T

    T is for truncate. Keep this default, or enter a value to be used for the truncate operations. This value will be included with your message if the operation on the delivered data is truncate. Ensure that includeOpType property is already selected. This value will be included in a field called op_type.

    Leave this field blank if you don't want truncate operations included in the Kafka topic

    Treat All Columns As Strings

    (Deselected)

    Select to treat all output fields as strings. If you don't select this field, then the handler will map the data type for the source data that is being delivered to the best corresponding Kafka Connect data type.

    Map Large Numbers As Strings

    (Deselected)

    Large numbers are delivered to Kafka topics with a double-precision floating point format, which only has 64 bits. You may lose precision on your large numbers with this 64 bit limitation. You can select this field to save your large numbers as strings to preserve their precision.

    ISO8601 Format

    (Deselected)

    Select to output the current date in the ISO8601 format. An example of this format is 2018-05-25T14:33:46-07:00, whereas a Unix format would be 1527284026 and an RFC 2822 format would be Fri, 25 May 2018 14:33:46 -07:0

    PK Update Handling

    insert

    This option is for handling Primary Key (PK) updates and only applies for the row message formatting. Choose insert, if you want insert for the updated key. For example, if the row with the old key is not found in the target, the change data will be considered an insert. Otherwise, you can choose abend (abnormal end), update or delete.

    Note:

    If you have selected op (operation) for the Message Formatting field, then this option will be ignored.

    Table 17-2 Template Keywords for Kafka Connect Connection

    Keyword Explanation

    ${catalogName}

    Resolves to the catalog name.

    ${columnValue[]}

    Resolves to a column value where the key is the fully-qualified table name and the value is the column name to be resolved. For example:

    ${staticMap[dbo.table1=col1,dbo.table2=col2]}

    ${currentTimestamp} or ${currentTimestamp[]}

    Resolves to the current timestamp. You can control the format of the current timestamp using the Java based formatting for the SimpleDateFormatclass. For example:

    ${currentDate} or ${currentDate[yyyy-mm-dd hh:MM:ss.SSS]}

    ${custom[]}

    It is possible to write a custom value resolver. If required, contact Oracle Support.

    ${emptyString}

    Resolves to “”.

    ${fullyQualifiedTableName}

    Resolves to the fully qualified table name including the period (.) delimiter between the catalog, schema, and table names. For example:

    test.dbo.table1.

    ${groupName}

    Resolves to the name of the Replicat process. If using coordinated delivery, it resolves to the name of the Replicat process with the Replicate thread number appended.

    ${null}

    Resolves to a NULL string.

    ${opTimestamp}

    The operation timestamp from the source trail file.

    ${opType}

    Resolves to the type of the operation: insert, update, delete or truncate.

    ${position}

    The sequence number of the source trail file followed by the offset (RBA).

    ${primaryKeys}

    Resolves to the concatenated primary key values delimited by an underscore (_) character.

    ${schemaName}

    Resolves to the schema name.

    ${staticMap[]}

    Resolves to a static value where the key is the fully-qualified table name. The keys and values are designated inside of the square brace in the following format: ${staticMap[dbo.table1=value1,dbo.table2=value2]}

    ${tableName}

    Resolves to the short table name.

    Table 17-3 Example Templates

    Example Template Resolved Value

    ${currentDate[yyyy-mm-dd hh:MM:ss.SSS]}

    2018-10-17 11:45:34.254

    ${groupName}_{fullyQualfiedTableName}

    KAFKA001_dbo.table1

    prefix_${schemaName}_${tableName}_suffix

    prefix_dbo_table1_suffix

  6. Click Test Connection. If successful, click Save.

Create a Microsoft SQL Server Connection

Learn to create a connection to Microsoft SQL Server in Oracle Data Integration Platform Cloud

  1. From the Get Started with Integration section of the Home page or from the Create menu in the Catalog, select Connection. You can also create Connections from any Create Task screen.
  2. Complete the fields in the General Information section. The Identifier is to identify this connection through a string with no spaces, but is not currently used. If you want to edit this auto-generated field, then you must only include capital letters, numbers, and underscores (_) with no space.

    • For Agent Name, select the agent you’re running for the Microsoft SQl connection from the list of available agents.

    • For Type, select Microsoft SQL Server

      .
  3. In the Connection Settings complete the following fields:

    • Hostname:  Enter the Host/IP, where SQL server is running.

    • Service: Either port or Instance Name is mandatory. Both cannot be given together.

    • Port: The port where SQL Server is listening.

    • Username: The database user

    • Password: The database password

  4. Click the Database drop-down, and select a Database. If not specified, it will populate the default database.
  5. Click the Schema Name drop-down, and select a Schema Name. If not specified, then username will be populated as schema.
  6. In the Properties section, add JDBC properties to establish SQL server connection.
  7. Click Test Connection. If successful, click Save.

Create a MySQL Connection

Learn to register a connection to MySQL in Oracle Data Integration Platform Cloud

  1. From the Get Started with Integration section of the Home page or from the Create menu in the Catalog, select Connection. You can also create Connections from any Create Task screen.
  2. Complete the fields in the General Information section. The Identifier is to identify this connection through a string with no spaces, but is not currently used. If you want to edit this auto-generated field, then you must only include capital letters, numbers, and underscores (_) with no space.

    • For Agent Name, select the agent you’re running for MySQL, from the list of available agents.

    • For Type, select MySQL.

  3. In the Connection Settings, complete the following fields:

    • Hostname: Enter the DNS name/IP.

    • Port:Enter the port number on the host specified for accessing the database.

    • Username:The username to connect to MySQL.

    • Password: The database password

    • After entering these details, click the Database drop-down, and select a Database. If not specified, it will populate the default database.

    • Click the Schema Name drop-down, and select aSchema. If not specified, then username will be populated as schema.

    • In the Properties section, three default Properties are available. You can add more JDBC properties.

      For more information, see the MySQL JDBC driver documentation at https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html

  4. Click Test Connection. If successful, click Save.

Create an Autonomous Data Warehouse Cloud (ADWC) Connection

Autonomous Data Warehouse Cloud (ADWC) as a connection type. Oracle ADWC provides an easy-to-use, fully autonomous database that scales elastically and delivers fast query performances.

Before you begin

Before you create an ADWC Connection, do the following:

  • Create an ADWC instance. For more information, see Provisioning Autonomous Data Warehouse Cloud.

  • Download the client credentials zip file from ADWC console. This zip file will contain cwallet.sso, ewallet.p12, keystore.jks, ojdbc.properties, sqlnet.ora, tnsnames.ora, truststore.jks files.

Create the ADWC Connection

  1. In the Getting Started section, click Create. You can also create a Connection from any Create Task screen.
  2. Complete the fields in the General Information section.
    • For Type, select Oracle Autonomous Data Warehouse Cloud.
    • For Agent, select the Agent you've downloaded and registered.

      See Set up an Agent.
  3. Complete the Connection Settings.
    1. ADWC has a pre-existing user created for Oracle GoldenGate On Premises called ggadmin that DIPC will be using as well. The ggadmin user has been granted the right set of privileges for Oracle GoldenGate On Premises Replicat to work. By default, this user is locked. To unlock the ggadmin user, connect to your Oracle Autonomous Data Warehouse Cloud database as the ADMIN user using any SQL client tool. Run the alter user command to unlock the ggadmin user and set the password for it:
      alter user ggadmin identified by password account unlock;

      For Username, enter ggadmin.

      For Password, enter the password for ggadmin.

    2. For Credentials, select the downloaded client credential zip file.
    3. For Service, select the required service.
    4. For JDBC Setting, a URL will automatically populate after selecting the service. You need to update the proxy details in this URL, if needed.
    5. For Schema Name, select the primary schema name.
    6. Click Test Connection and then Save if successful.

Create an Oracle BI Cloud Connector Connection

Harvest ERP Cloud objects using Oracle BI Cloud Connector to view in Data Integration Platform Cloud's Catalog as Data Entities.

To create an Oracle BI Cloud Connection Connection:
  1. Click Create in the Connection tile of the Getting Started section of the Home page, or from the Create menu in the Catalog, select Connection.
  2. In the Connection dialog, complete the General Information fields.
    • For Identifier, enter a name that includes only capital letters, numbers, and underscores (_) with no space, or leave it as is. This auto-generated value is used to identify this Connection through a string with no spaces.
    • For Agent, select the appropriate agent.
    • For Type, select BI Cloud Connector.
  3. Complete the Connection Settings fields.
    • For Service URL, enter your BI Cloud Connector service URL.
    • For Username, enter the user name associated with your BI Cloud Connector Service URL.
    • For Password, enter the password associated with the user name for your BI Cloud Connector account.
    • For Schema, select from a list of available schemas associated with the Service URL and account information for BI Cloud Connector.
  4. Click Test Connection. If successful, click Save, otherwise, review your Connection details and try again.

Create a PDB Connection

If you want to use a Pluggable Database as a Connection for a Replicate Data Task or a Synchronize Data Task with the Replication advanced option in Data Integration Platform Cloud, you must create Container Database Connection also.

Topics:

Handle Pluggable Multitenant Databases

Oracle Data Integration Platform Cloud enables you to replicate Pluggable Databases (PDBs) with its extensive multitenant architecture.

About Multitenant Databases

The multitenancy option in Oracle Database 12c enables you to create a virtual database for each schema. Each virtual database behaves like an independent database; but runs on top of a real, physical database, which may be hidden from end users. These virtual databases are called Containers. The physical database that houses these containers is in effect, a database of containers and is known as a Container Database (CDB). You can pull out (or “unplug”) a container from one CDB and place it (or “plug” it) into another CDB. This is why a container is also known as a Pluggable Database (PDB). For all practical purposes, from the perspective of clients, PDBs are just regular databases. With the release of Oracle Database 12c, comes a radically new multitenant architecture. With the release of Oracle GoldenGate 12c, you now have a tool to replicate PDBs.

Oracle Data Pump with PDBs

You can use Data Pump to migrate all, or portions of, a database from a non-CDB into a PDB, between PDBs within the same or different CDBs, and from a PDB into a non-CDB. In general, using Data Pump with PDBs is identical to using Data Pump with a non-CDB.

In Oracle Database 12c Release 1 (12.1), Data Pump does not support any CDB-wide operations. Data Pump produces the following warning if you’re connected to the root or seed database of a CDB:

ORA-39357: WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Oracle GoldenGate with PDBs

In most ways, Oracle GoldenGate operates in a multitenant container database the same way it operates in a regular Oracle database.

The following are the special requirements that apply to replication to and from multitenant container databases.

  • The different pluggable databases in the multitenant container database can have different character sets. Oracle GoldenGate captures data from any multitenant database with different character sets into one trail file and replicates the data without corruption due to using different character sets.

  • Extract must operate in integrated capture mode.

  • Extract must connect to the root container (cdb$root) as a common user in order to interact with the logmining server. To specify the root container, use the appropriate SQL*Net connect string for the database user that you specify with the USERID or USERIDALIAS parameter. For example: C##GGADMIN@FINANCE.

  • To support source CDB 12.2, Extract must specify the trail format as release 12.3.

  • The dbms_goldengate_auth.grant_admin_privilege package grants the appropriate privileges for capture and apply within a multitenant container database. This includes the container parameter, which must be set to ALL, as shown in the following example: 

    dbms_goldengate_auth.grant_admin_privilege('C##GGADMIN',container=>'all')

REGISTER EXTRACT Command

REGISTER EXTRACT sales DATABASE CONTAINER (sales, finance, hr)

CONTAINER (container[, ...]) here

Applies the registration to a list of one or more pluggable databases (containers) of a multitenant CDB. Specify one or more PDBs as a comma-delimited list within parentheses, for example, CONTAINER (pdb1, pdb2, pdb3). All of the PDBs must exist in the database, and all names must be explicit, not wildcarded.

Create User ID Alias

As mentioned, Extract must connect to the root container (cdb$root) as a common user in order to interact with the logmining server. 

ALTER CREDENTIALSTORE ADD USER CDBUSER@HOST:PORT/CDB_SERVICE_NAME PASSWORD *** ALIAS GGALIASSRC

DBLOGIN Command

DBLOGIN USERIDALIAS GGALIASSRC
OR
DBLOGIN CDBUSER@HOST:PORT/CDB_SERVICE_NAME PASSWORD ***

Privileges Required for CDB User for GoldenGate

CREATE EVALUATION CONTEXT
INSERT ANY TABLE
CREATE RULE
DEQUEUE ANY QUEUE
SELECT ANY TABLE
EXECUTE ANY RULE SET
CREATE TABLE
BECOME USER
CREATE RULE SET
FLASHBACK ANY TABLE
UNLIMITED TABLESPACE
ALTER SESSION
CREATE JOB
SET CONTAINER
CREATE SESSION

To assign these privileges, you need to execute the following grant commands while logged in as SYSDBA:

exec dbms_goldengate_auth.grant_admin_privileges('cdbuser','capture',container=>'all');
grant create session to cdbuser;
grant alter session to cdbuser;
grant set container to cdbuser;

Oracle Data Integration Platform Cloud with PDB

For Oracle Data Integration Platform Cloud, only CDB user and User ID Alias should be created using the information above. All others are generated or executed by Oracle Data Integration Platform Cloud.

Create an Oracle CDB Connection

You need an Container Database (CDB) connection in Oracle Data Integration Platform Cloud only if you're using an Oracle PDB as a source for a Synchronize Data or Replicate Data task.

First, create an Oracle CDB connection and then create an Oracle Database Connection and select the CDB connection in the connection settings. If you're using the PDB as a target data source, then skip these instructions and create an Oracle Database Connection.

Create an Oracle Database Connection.

To create a connection to a cloud or on-premises Oracle CDB, you must first have your agent ready and running. When you download your agent, ensure that you download the Oracle 12c component with it and follow agent instructions to set it up.

For more information see Set up an Agent. After your agent is running, then:

  1. From the Getting Started section of the Home page, click Create in the Connection tile or from the Create menu in the Catalog, select Connection. You can also create Connections from any Create Task screen.
  2. Complete the fields in the General Information section. The Identifier is to identify this connection through a string with no spaces, but is not currently used. If you want to edit this auto-generated field, then you must only include capital letters, numbers, and underscores (_) with no space.

    • For Agent, select the agent for your Oracle CDB from the list of available agents.

    • For Type, select Oracle CDB

      .
  3. In the Connection Settings complete the following fields:

    • Hostname: The DNS name or IP address of your database. For example, example.com or 192.0.2.1.

    • Port: The port number on the host specified for accessing the database.

    • Username:

      Note:

      The Container Database (CDB) username is different from the Pluggable Database (PDB) username. Use the CDB common username which has the prefix C##, such as C##GGSRC.

      For more information, see Create Users for Oracle Database Connections.

    • Password: The password associated with the username.

    • Service Name: The service name for your PDB database. There is no SID option here.

  4. Click Test Connection. If successful, click Save.

If you want to use your PDB connection as a source for a Synchronize Data or Replicate Data task, then must create another connection and select the type as Oracle. Then select this CDB Connection in the CDB dropdown menu in the Connection Settings.

See Create an Oracle Database Connection

Set up PDB as the Source and Target

Perform the following steps to set up PDB as a source or target for your Synchronize Data Task. The steps are similar to setting up a classic database as a source, but there are requirements for using the CDBROOT as ggaliassrc.

To set up PDB as a source or target:
  1. Log in to Database Cloud Service 12.1 as sys: 
    sql> conn sys/password as sysdba
  2. Enable GoldenGate replication: 
    sql> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;
  3. Turn on ARCHIVELOG mode:
    1. Check whether archivelog is on: 
      sqlplus> archive log list
    2. Enable archivelog mode: 
      sqlplus> shutdown immediate
sqlplus> startup mount
sqlplus> alter database archivelog;
sqlplus> alter database open;
    3. Turn on logging

      1. Check whether logging is enabled:

        sqlplus> SELECT supplemental_log_data_min, force_logging FROM v$database;

      2. Enable logging:

        sqlplus> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
sqlplus> ALTER DATABASE FORCE LOGGING;

      3. Disable logging (for clean up later):

        sqlplus> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
sqlplus> ALTER DATABASE NO FORCE LOGGING;
  4. For the source CDB, enable Open Transaction:
    sqlplus > exec DBMS_CAPTURE_ADM.BUILD;
  5. Create the common GoldenGate user with the following grants (logged in to CDB):
    create user C##GGSRC identified by password;
grant connect, resource, create  session, select any table, create ANY table, CREATE ANY  view,  create ANY procedure, CREATE database link, SELECT ANY dictionary, exp_full_database, imp_full_database to C##GGSRC;
grant read, write on directory DATA_PUMP_DIR to C##GGSRC;
GRANT CREATE SESSION TO C##GGSRC CONTAINER=ALL;
exec dbms_goldengate_auth.grant_admin_privilege('C##GGSRC',container=>'ALL');
exec dbms_goldengate_auth.grant_admin_privilege('C##GGSRC','capture');
GRANT alter session to C##GGSRC;
GRANT set container to C##GGSRC;
GRANT dba to C##GGSRC CONTAINER=ALL;
  6. Create a source user in PDB with the following grants (first connect to PDB: sys/password#@pdb1 as sysdba):
    create user dipc_src identified by password;
grant connect, resource, create  session, select any table, create ANY table, CREATE ANY view, create ANY procedure, CREATE database link, SELECT ANY dictionary, exp_full_database, imp_full_database to dipc_src;
grant read, write on directory DATA_PUMP_DIR to dipc_src;
GRANT UNLIMITED TABLESPACE TO dipc_src;
grant select on v_$database to dipc_src;
grant select on v_$transaction to dipc_src;
grant select on v_$archived_log to dipc_src;
exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('dipc_src');
  7. Create a target user in PDB with the following grants:
    create user dipc_tgt identified by password;
grant connect, resource, create session, select any table, create ANY table, CREATE ANY view, create ANY procedure, CREATE database link, SELECT ANY dictionary, exp_full_database, imp_full_database to dipc_tgt;
grant read, write on directory DATA_PUMP_DIR to dipc_tgt;
GRANT UNLIMITED TABLESPACE to dipc_tgt;
exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('dipc_tgt');
  8. Test the login. Log in as source to PDB and create a table, and then push some data into it and commit.
  9. Log in to Data Integration Platform Cloud and create Connections for the CDB root user (C##GGuser), PDB source and target schemas.

    Note:

    The source Connection does not use C##GGSRC as the Connection username or schema. It is only for ggaliassrc. Use a PDB source user or schema.
    1. Create an Oracle CDB Connection with C##GGrootuser as the user and CDB service name.
    2. Create an Oracle Database Connection for srcSchema user and PDB service.
    3. Create an Oracle Database Connection for tgtSchema user.
  10. Create a Synchronize Data Task between the source and target Connections.

    Note:

    If you’re using PDB as a source connection, you must also have an associated CDB connection to support replication.

  11. Run the Job.
  12. Log in as source to PDB and perform a few inserts and updates. Every operation is updated on the Jobs page. A snapshot is taken every 30 seconds.
For more information, see:

Create an Oracle Database Connection

Learn to register your Oracle Database connection in Oracle Data Integration Platform Cloud.

Perform Prerequisites:

  1. Set up your users and schemas on your Oracle data source.

    See Create Users for Oracle Database Connections.

  2. Download Data Integration Platform Cloud's Agent Installer bundled with Oracle 11g or Oracle 12c.

    Note:

    If this agent is not installed on the same machine with Oracle 11g or 12c, then the agent must be on a machine that has access to the data source that you are connecting to.

    See Set up an Agent.

Create a Connection
  1. From the Getting Started section of the Home page, click Create from the Connection tile or from the Create menu in the Catalog, select Connection. You can also create Connections from any Create Task screen.
  2. Complete the fields in the General Information section. The Identifier is to identify this connection through a string with no spaces, but is not currently used. If you want to edit this auto-generated field, then you must only include capital letters, numbers, and underscores (_) with no space.
    • For Agent Name, select the agent for your Oracle CDB from the list of available agents.
    • For Type, select Oracle Database.
  3. In the Connection Settings complete the following fields:

    • Hostname: Enter the DNS name or IP address of your database. For example, example.com or 192.0.2.1.

    • Port: Enter the port number on the host specified for accessing the database.

      Note:

      For Oracle Database Cloud Service, port number 1521 should be open or on your system to enable metadata discovery.
    • Username:
      • For Synchronize or Replicate Data task's source connection, enter the Data Integration Platform Cloud admin username you created for the setup tables. For example, dipc_admin.
      • For Synchronize or Replicate Data task's target connection, enter the Data Integration Platform Cloud username you created for the target. For example, dipc_tgt.
      • For other tasks, select a username to connect to this data source.

    • Password: Enter the password associated with the username.

    • Service: Choose one of the following options for your Database Cloud Service deployment or Oracle Database:
      • Service Name
      • SID Name
    • Schema Name:
      • For Synchronize or Replicate Data task's source connection, enter the schema you created for the source user. For example, dipc_src.
      • For Synchronize or Replicate Data task's target connection, enter the schema for the target. For example, dipc_tgt.
    • CDB Connection: If you want to use a PDB as a source, you must first create a CDB connection to that source, and then select that CDB connection here to support your Synchronize Data or Replicate Data task. If the PDB is used for target, then just leave the CDB Connection field blank.

      See Create a PDB Connection.

  4. Click Test Connection. If successful, click Save.

Create Users for Oracle Database Connections

When both Initial Load and Replication are selected for a Synchronize Data task, you may discover more records are processed during Initial Load than you have in your source Connection. This occurs if you don't have a separate user created for the setup process.

Oracle Data Integration Platform Cloud uses a plugin to capture data from an Oracle source. This plugin creates tables in the data source that's defined in the source connection. When the Initial Load process starts, the tables created by the plugin are copied to the target. If you create some transactions in the source, then those changes will also be copied to the target. As a result, there's a discrepancy with the number of records processed. For example, Initial Load action in the job detail will show the number four, when you only have one row in your table. This issue only happens when run your Synchronize Data task with both Initial Load and Replication selected.

To solve this issue, create a separate user for the plugin setup on the source. For example, call it dipc_admin. You can then use this admin as the username in the source connection parameters. After that, Data Integration Platform Cloud plugin creates the setup tables in this user's default schema. The user schema specified in the connection details is used for the Synchronize Data source schema. No separate user is required for the target Connection. First follow the instructions here to set up the users and then set up your connections. The link to connections is at the end of this article.

Set up Users on Oracle Source without PDB

Create schemas and grant privileges. You can use existing schemas, just make sure you grant the users the following privileges:

SQL > create user dipc_admin identified by password default tablespace USERS temporary TABLESPACE temp;
User created.
SQL > create user dipc_src identified by password default tablespace USERS temporary TABLESPACE temp;
User created.

Then, grant privileges to these source users:

SQL > grant connect, resource, create session, select any table, create ANY table, CREATE ANY view, create ANY procedure, CREATE database link, SELECT ANY dictionary, exp_full_database, imp_full_database to dipc_admin;
Grant succeeded.
 
SQL > grant connect, resource to dipc_src;
Grant succeeded.

SQL > exec dbms_goldengate_auth.grant_admin_privilege('DIPC_ADMIN','CAPTURE');

Grant DIPC plugin privileges to dipc_admin. 

SQL > grant select on v_$database to dipc_src;
Grant succeeded

Set up User on Oracle Target without PDB

Create schemas and grant privileges. You can use existing schemas, just make sure you grant the users the following privileges:

SQL > create user dipc_tgt identified by password default tablespace USERS temporary TABLESPACE temp;
User created.

SQL > exec dbms_goldengate_auth.grant_admin_privilege('DIPC_TGT','APPLY');

Then, grant privileges to the target user:

SQL > grant connect, resource, create session, select any table, create ANY table, CREATE ANY view, create ANY procedure, CREATE database link, SELECT ANY dictionary, exp_full_database, imp_full_database to dipc_tgt;
Grant succeeded.

Now, to assign these users in the connection parameters, see Create an Oracle Database Connection

Set up the On-Premises Source and Target

This section describes prerequisite on-premises source and target database configurations that you need to complete before you run your Synchronize Data Task.

Now that you have your Connections created for your source and target databases, you need to set up both the source and target databases before creating and executing your synchronize task. To read from the database transaction log, you may need to enable certain settings in your database and provide replication users with proper privileges. You’ll perform the steps outlined here for both the source and target.

Note:

Database Cloud Service port (1521 in this case) is not accessible from outside by default.

When you’re performing a Test Connection through a remote Agent to a Database Cloud Service instance, you are essentially trying to access a Database Cloud Service port (1521 in this case) from outside, which is not accessible.

For this to work:

  • You must create an access rule in the Database Cloud Service instance, for the specific port (1521 in this case).

  • Make sure to mention the SOURCE IP (remote agent host), instead of PUBLIC_INTERNET.

Set up the source database

To capture open transactions in your Synchronize Data Task, your database administrator should run the command: 
DBMS_CAPTURE_ADM.BUILD
This creates and dumps a dictionary build in the archived log so that the GoldenGate Capture process can understand the metadata of tables. You also have to run the following commands:
grant select on v_$transaction to dipc_source
grant select on v_$archived_log to dipc_source

If you run your Synchronize Data Task with Initial Load and Replication separately, then you have to make sure your data is in sync between your source and target. This means that any transaction that starts before Initial Load that ends during the Initial Load process, or before you start the Replication process, is not replicated from source to target.

  1. Enable GoldenGate Replication:

    ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;

  2. Enable ARCHIVELOG mode:

    1. Check whether archivelog is enabled: 

      sqlplus > archive log list

    2. Enable archivelog mode: 

      sqlplus > shutdowm immediate
sqlplus > startup mount
sqlplus > alter database archivelog;
sqlplus > alter database open;

    3. Disable archivelog mode (for clean up later) 

      sqlplus > shutdown immediate
sqlplus > startup mount
sqlplus > alter database noarchivelog;
sqlplus > alter database open;

  3. Enable logging:

    1. Check if logging is enabled:

      sqlplus > SELECT supplemental_log_data_min, force_logging FROM v$database;

    2. Enable logging:

      sqlplus > ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
sqlplus > ALTER DATABASE FORCE LOGGING;

    3. Disable logging (for cleanup later)

      sqlplus > ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
sqlplus > ALTER DATABASE NO FORCE LOGGING;

Set up the target database

Enable GoldenGate replication:

sqlplus > ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;

Configure source and target schemas

  1. Create schemas and grant privileges. You can use existing schemas, just make sure you grant privileges:

    1. First, create schemas if you’re not using existing ones:

      sqlplus > create user dipc_src identified by dipc_src;
sqlplus > create user dipc_tgt identified by dipc_tgt;

    2. Then, grant privileges:

      sqlplus > grant connect, resource, create session, select any table, create ANY table, CREATE ANY view, create ANY procedure, CREATE database link, SELECT ANY dictionary, exp_full_database, imp_full_database to dipc_src;
sqlplus > grant connect, resource, create session, select any table, create ANY table, CREATE ANY view, create ANY procedure, CREATE database link, SELECT ANY dictionary, exp_full_database, imp_full_database to dipc_tgt;
sqlplus > grant read, write on directory DATA_PUMP_DIR to dipc_src;
sqlplus > grant read, write on directory DATA_PUMP_DIR to dipc_tgt;

  2. Grant GoldenGate privileges to source and target schemas:

    sqlplus > exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('dipc_src');
sqlplus > exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('dipc_tgt');

  3. Grant privileges to source schema:

    sqlplus > grant select on v_$database to dipc_src;
sqlplus > grant select on v_$transaction to dipc_src;
sqlplus > grant select on v_$archived_log to dipc_src;
sqlplus > grant select on v_$instance to dipc_src;

  4. Grant tablespace on USERS. For example:

    sqlplus > Alter user dipc_src quota 50g on USERS;
sqlplus > Alter user dipc_tgt quota 50g on USERS;

    Note:

    Apply a sufficient tablespace amount to avoid errors in Data Pump. See Insufficient tablespace quota leads to error in Data Pump.

Create an Oracle Object Storage Connection

Create a Connection to Oracle Object Storage.

To create an Oracle Object Storage Connection:
  1. Obtain the keys and OCIDs.

    You can follow the steps in this tutorial the RSA key pair in PEM format, public key fingerprint, Tenancy's OCID, and user's OCID: Required Keys and OCIDs.

  2. Set up a proxy for the agent in its agent.properties file, and then restart the agent:

    For example:

    agentUseProxy=true
proxyHost=www-proxy.example.com
proxyPort=80
  3. Create and configure a default OCS Connection.
  4. Click Create from the Connection tile in the Getting Started section of the Home page, or select Connection from the Create menu in the Catalog.
  5. Complete the General Information fields. Be sure to select Oracle Object Storage for Type.
  6. Complete the Connection Settings fields.
  7. Click Test Connection. If successful, click Save, otherwise review your connection details and try again.

Create an Oracle Object Storage Classic Connection

Learn to create a connection to Oracle Object Storage Classic in Oracle Data Integration Platform Cloud.

If required, you can set proxy details in agent.properties to connect to Object Storage Connection.

  • Go to dicloud/agent/dipcagent/conf/agent.properties.

  • Add proxy details in this format:

    • agentUseProxy=

    • proxyHost=

    • proxyPort=

Note:

The Agent ports should be configured as unique port of the system, especially when there are more than one agent on the same operating system.

Make sure to set gghome in the path to access ggsci prompt :

PATH=%12CGGHOME%;%12CGGHOME%\crypto;%PATH%
For Windows , you must set it as 
PATH=%12CGGHOME%;%12CGGHOME%\crypto;%PATH%
To create an Oracle Object Storage Classic connection:
After setting the parameters, and successful testing, you can connect to Oracle Object Storage Classic.
  1. From the Getting Started section of the Home page, click Create from the Connection tile or from the Create menu in the Catalog, select Connection. You can also create Connections from any Create Task screen.
  2. Complete the fields in the General Information section. The Identifier is to identify this connection through a string with no spaces, but is not currently used. If you want to edit this auto-generated field, then you must only include capital letters, numbers, and underscores (_) with no space.

    • For Agent Name, select the agent you’re running for the Oracle Object Storage Classic from the list of available agents.

    • For Type, select Oracle Object Storage Classic.

  3. In the Connection Settings complete the following fields:

    • Domain: Enter the domain of your Object Storage Classic container. For example, Storage-example00001234

    • Service URL: Enter the Service URL of your Object Storage Classic container. For example, https://example00001234.storage.oraclecloud.com.

    • Container: Enter your Object Storage Classic container name. For example, DIPC_EXPORT_TEST.

    • Username: Enter the username used to connect to your Object Storage Classic container.

    • Password: Enter the password associated with the username used to connect to your Object Storage Classic container.

  4. Click Test Connection. If successful, click Save.

Create an Amazon S3 Connection

Harvest data from your S3 Cloud Storage Service for use with Data Integration Platform Cloud.

To create an S3 Connection:
  1. Click Create from the Connection tile in the Getting Started section of the Home page, or select Connection from the Create menu in the Catalog.
  2. In the Connection dialog, complete the General Information fields. Be sure to select S3 for Type.
  3. Complete the Connection Settings fields.

    For information on how to obtain your Access Key ID and Secret Access Key, see Access Keys (Access Key ID and Secret Access Key)

    .
  4. Click Test Connection. If successful, click Save, otherwise review your connection details and try again.

Create a Salesforce Connection

Learn to create a connection to Salesforce in Data Integration Platform Cloud

To create a Salesforce connection:
  1. From the Getting Started section of the Home page click Create from the Connection tile, or from the Create menu in the Catalog, select Connection. You can also create Connections from any Task screen.
  2. Complete the fields in the General Information section. The Identifier is to identify this connection through a string with no spaces, but is not currently used. If you want to edit this auto-generated field, then you must only include capital letters, numbers, and underscores (_) with no space.

    • For Agent Name, select the agent you’re running for Salesforce from the list of available agents.

    • For Type, select Salesforce.

  3. In the Connection Settings, complete the following fields:

    • Hostname: Salesforce server hostname.

    • Username: The username to connect to Salesforce.

    • Password: The database password.

    • After entering these details, click the Database drop-down, and select the Salesforce database name. If not specified, it will populate the default database.

    • In the Properties section, the default Properties are available. You can add more JDBC properties.

  4. Click Test Connection. If successful, click Save.