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.
- 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.
- 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.
-
- 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.
- 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 tokafkaconnect.properties
and copy it to${agent_unzip_loc}/dicloud/gghomebigdata/dirprm
. Review the provided example file for reference.
gghomebigdata
folder , you must have already downloaded your agent with the Big Data (OGG) component and registered it with Oracle Data Integration Platform Cloud. -
- 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:
The#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
batch.size
property is the maximum number of bytes to buffer before sending data to Kafka. Thelinger.ms
property is the maximum milliseconds to wait before sending data. Settinglinger.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. - For Avro, uncomment the Avro section of the kafkaconnect.properties file and comment out the JSON section.
Create the Connection
-
From the Home page or from the Create menu in the Catalog, select Create Connection.
-
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.
-
-
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/*
-
-
- 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. -
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 tableX
, then the Kafka producer will create a Kafka topic namedABC.X
and add that inserted data to theABC.X
topic.In case of multiple databases, such as databases with PDBs, then the
${fullyQualifiedTableName}
isdatabase_name.schema_name.table_name
. For example, there is a salesdb.contacts table and the contacts table has a column calledfirst_name
. Then the Kafka topic will besalesdb.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 valueI
for insert,U
for update,D
for delete andT
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
andtruncateOpKey
.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 chooseop
for message formatting, then you can't include the operation type (insert, abend, update, delete
) in your messages.Insert Op Key
I
I
is forinsert
. Keep this default, or enter a value to be used for theinsert
operations. This value will be included with your message if the operation on the delivered data isinsert
. Ensure that includeOpType property is already selected. This value will be included in a field calledop_type
.Leave this field blank if you don't want
insert
operations included in the Kafka topicUpdate Op Key
U
U
is forupdate
. Keep this default, or enter a value to be used for theupdate
operations. This value will be included with your message if the operation on the delivered data isupdate
. Ensure that includeOpType property is already selected. This value will be included in a field calledop_type
.Leave this field blank if you don't want
update
operations included in the Kafka topicDelete Op Key
D
D
is fordelete
. Keep this default, or enter a value to be used for thedelete
operations. This value will be included with your message if the operation on the delivered data isdelete
. Ensure that includeOpType property is already selected. This value will be included in a field calledop_type
.Leave this field blank if you don't want
delete
operations included in the Kafka topicTruncate Op Key
T
T
is fortruncate
. Keep this default, or enter a value to be used for thetruncate
operations. This value will be included with your message if the operation on the delivered data istruncate
. Ensure that includeOpType property is already selected. This value will be included in a field calledop_type
.Leave this field blank if you don't want
truncate
operations included in the Kafka topicTreat 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 be1527284026
and an RFC 2822 format would beFri, 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 chooseabend
(abnormal end),update
ordelete
.Note:
If you have selectedop
(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
ortruncate
.${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
-
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
- 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.
- 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
.
-
- 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
-
- 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 a Schema Name. If not specified, then username will be populated as schema.
- In the Properties section, add JDBC properties to establish SQL server connection.
- Click Test Connection. If successful, click Save.
Create a MySQL Connection
Learn to register a connection to MySQL in Oracle Data Integration Platform Cloud
- 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.
- 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.
-
- 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
-
- 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
- In the Getting Started section, click Create. You can also create a Connection from any Create Task screen.
- 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.
- Complete the Connection Settings.
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.
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.
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 theUSERID
orUSERIDALIAS
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 thecontainer
parameter, which must be set toALL
, 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:
- 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.
- 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
.
-
- 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.
-
- 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.
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
.
Create an Oracle Database Connection
Learn to register your Oracle Database connection in Oracle Data Integration Platform Cloud.
Perform Prerequisites:
-
Set up your users and schemas on your Oracle data source.
-
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.
- 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.
- 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.
- 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.
- 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,
-
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
.
- For Synchronize or Replicate Data task's source connection, enter the schema you created for the source user. For example,
- 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.
-
- 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
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.
-
Enable GoldenGate Replication:
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;
-
Enable
ARCHIVELOG
mode:-
Check whether
archivelog
is enabled:sqlplus > archive log list
-
Enable
archivelog
mode:sqlplus > shutdowm immediate sqlplus > startup mount sqlplus > alter database archivelog; sqlplus > alter database open;
-
Disable
archivelog
mode (for clean up later)sqlplus > shutdown immediate sqlplus > startup mount sqlplus > alter database noarchivelog; sqlplus > alter database open;
-
-
Enable logging:
-
Check if logging is enabled:
sqlplus > SELECT supplemental_log_data_min, force_logging FROM v$database;
-
Enable logging:
sqlplus > ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; sqlplus > ALTER DATABASE FORCE LOGGING;
-
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
-
Create schemas and grant privileges. You can use existing schemas, just make sure you grant privileges:
-
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;
-
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;
-
-
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');
-
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;
-
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.
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%
PATH=%12CGGHOME%;%12CGGHOME%\crypto;%PATH%
- 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.
- 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.
-
- 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.
-
- Click Test Connection. If successful, click Save.