Data Transforms is an easy-to-use graphical user interface that Oracle Autonomous Database users can use to design graphical data transformations for data integration.
Data Transforms allows you to design data transformations in the form of data loads, data flows, and workflows, without requiring you to write any code. Data loads provide a convenient way of loading data into Autonomous Database, data flows define how the data is moved and transformed between different systems, while the workflows define the sequence in which the data flows are executed.
Note
If you do not see the Data Transforms card then your database user is missing the required DATA_TRANSFORM_USER role.
After your data flows and workflows are ready, you can execute the mappings immediately or schedule to execute them at a later time. Oracle Data Transforms run-time agent orchestrates the execution of jobs. On execution, Oracle Data Transforms generates the code for you.
You can launch Data Transforms in any of the following ways:
Oracle Cloud Marketplace: Create a Data Transforms instance from Oracle Cloud Marketplace. Data Transforms is available as a separate listing on Marketplace called Data Integrator: Web Edition.
Database Actions Data Studio: Navigate to Database Actions Data Studio Page, and click Data Transforms in the Database Actions page.
If you have already registered a Data Transforms instance from OCI Marketplace with the Autonomous Database, the Data Transforms card on the Database Actions page will continue to take you to your Marketplace instance.
Access to the standard set of Data Transforms features may depend on where you launch Data Transforms from. In this documentation, certain topics could include any of the following badges to indicate features that may or may not be available for use:
APPLIES TO: Data Transforms that is available as a separate listing on Marketplace
called Data Integrator: Web Edition.
APPLIES TO: Data Transforms instance that is registered with Autonomous Database.
APPLIES TO: Data Transforms that is part of the suite of data tools built into Oracle Autonomous Database.
Access Oracle Data Transforms From Data Studio Data Transforms combines all the elements of data integration - data movement, data synchronization, data quality, and data management to ensure that information is timely, accurate, and consistent across complex systems.
Work with Projects A project is the top-level container, which can include multiple folders to organize your data flows or work flows into logical groups.
Create a Data Flow A data flow defines how the data is moved and transformed between different systems.
Monitor Status of Data Loads, Data Flows, and Workflows When you run a data load, data flow, or workflow Oracle Data Transforms runs jobs in the background to complete the request. You can view the status of the job in the panel on the bottom right of the Data Load Details, the Data Flow Editor, and the Workflow Editor page.
Introduction to Workflows A workflow is made up of multiple flows organized in a sequence in which they must be executed.
Create and Manage Jobs When you execute a data load, data flow or workflow Oracle Data Transforms creates a job to complete the process in the background.
This section includes additional information about using Oracle Data Tranforms on an
Autonomous Database.
Enable Access to Private Data Sources from Autonomous Database The Autonomous Database from where you are accessing Data Transforms should be configured to use a private endpoint to be able to communicate with private database sources. Otherwise when you try to create and test such a connection, you might get a "failed to connect" error.
Troubleshoot Mismatch with Server Cert DN Error For a connection on an Autonomous Database that is configured to use private endpoints, you must specify the distinguished name (DN) of the database server in the JDBC URL so that the Oracle Connection Manager (CMAN) can accept the request. Otherwise when you try to create and test such a connection, you might get a "Mismatch with Server Cert DN" error.
Increase the Memory of ODI Agent If the data that you are loading from the source schema is huge, then you may want to increase the memory of the ODI Agent to avoid OutOfMemory exception errors.
Enable Access to Private Data Sources from Autonomous Database 🔗
APPLIES TO: Data Transforms that is part of the suite of data tools built into Oracle Autonomous Database.
The Autonomous Database from where you are accessing Data Transforms should be configured to use a private endpoint to be able to communicate with private database sources. Otherwise when you try to create and test such a connection, you might get a "failed to connect" error.
If your Autonomous Database is configured to use a Private Endpoint, then you can only access private data sources from clients in the same Virtual Cloud Network (VCN). See Configuring Network Access with Private Endpoints for detailed instructions.
After you have configured network access using private endpoints, you need to do the following additional configurations for Oracle Data Transforms to communicate with private data sources. This chapter includes the following topics:
The VCN DNS resolver should know about the FQDN and be able to resolve it to a Type A IP address.
If you are not able to test a connection in Data Transforms on an Autonomous Database that is configured to use private endpoints, then do the following:
Make sure that the FQDN is used as the host so that the framework resolves the FQDN to the required IP address.
Ensure that the data source VM has the FQDN set up with the required security rules.
If test connection fails even after using an FQDN, then do the following:
From the OCI services menu, click DNS Management under Networking. Configure private DNS zones views and resolvers.
Create and publish a record using the FQDN and the private IP (Type A) of the target database.
Note
Do not use *.oraclecloud.com as the domain name when you set up the FQDN because it is a reserved domain name.
Retry test connection.
The test connection operation should complete successfully.
Configuring RCE Proxy on the Private Endpoint
For Autonomous Databases that are configured to use a private endpoint, the Autonomous Database service supports RCE to access resources in the customer subnet through a private route.
If you encounter a "failed to connect" error when you try to test a connection, contact your database administrator to check whether the database is RCE-enabled.
Note that RCE proxy can forward the reverse traffic only to the default OCI DNS resolver. If there is any overridden resolver, then connectivity will fail.
APPLIES TO: Data Transforms that is part of the suite of data tools built into Oracle Autonomous Database.
For a connection on an Autonomous Database that is configured to use private
endpoints, you must specify the distinguished name (DN) of the database server in the
JDBC URL so that the Oracle Connection Manager (CMAN) can accept the request. Otherwise
when you try to create and test such a connection, you might get a "Mismatch with Server
Cert DN" error.
First, specify ssl_server_dn_match=yes in the JDBC URL
for the CMAN to accept the request. Next, use the
ssl_server_cert_dn parameter to specify the DN of the database
server. Note that the order in which the keys are placed in
ssl_server_cert_dn is important. The sequence should be
CN, O, L,
ST, C.
For example:
jdbc:oracle:thin:@(description=
(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=xxxxx.adb.us-phoenix-1.oraclevcn.com))(connect_data=(service_name=xxxxx.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com,
O=Oracle Corporation, L=Redwood City, ST=California, C=US")))
To get the values for ssl_server_cert_dn:
Login to the VM that is in the same subnet where your Autonomous
Database lies.
APPLIES TO: Data Transforms that is available as a separate listing on Marketplace
called Data Integrator: Web Edition.
If the data that you are loading from the source schema is huge, then you may want to increase the memory of the ODI Agent to avoid OutOfMemory exception errors.
To increase the memory of the ODI Agent:
Edit the /u01/oracle/transforms_home/common/scripts/jettyServer.sh file.
Add the java -Xms1024m -Xmx4096m parameter.
Restart the jetty server. Log in as OPC user and execute the following commands:
ssh -i <path to id_rsa> opc@<Instance IP>
sudo su
systemctl stop|start jettyserver.service
exit