The CData Sync App provides a straightforward way to continuously pipeline your Oracle data to any database, data lake, or data warehouse, making it easily available for Analytics, Reporting, AI, and Machine Learning.
The Oracle OCI connector can be used from the CData Sync application to pull data from Oracle OCI and move it to any of the supported destinations.
The Sync App can connect Oracle Database 11.2 or later. The Sync App requires the Oracle OCI Library, which is shipped alongside it.
The correct environment variable depends upon your platform:
If you are using the Application Data-Source wizard to download the Oracle connector, the native libraries are downloaded along with the Oracle driver. These libraries are placed in the www/bin directory of your application folder. To include the Oracle OCI directory, you must either edit the environment variable to include the Oracle OCI directory or copy the Oracle OCI directory to an existing path in this environment variable. After that is done, restart the application to load the Oracle native libraries.
For required properties, see the Settings tab.
For connection properties that are not typically required, see the Advanced tab.
For example,
DataSource='(DESCRIPTION=(ADDRESS=(protocol_address_information))(CONNECT_DATA= (SERVICE_NAME=service_name)))'
Your connection string should look similar to this:
LDAPUri=ldap://myldap.com:389/SERVICE_EXMPL,cn=OracleContext,dc=example,dc=com; User=ORACLEUSER; Password=OracleUserPassword;Note that this connection type requires both your Oracle and LDAP usernames and passwords. If your system administrator allows anonymous LDAP logins, your LDAP username and password are not needed.
Oracle offers 2 different methods for tracking the changes from your source database:
Oracle Flashback is a lightweight solution that provides an efficient tracking mechanism for CData Sync. Once configured on your tables, Sync can execute Flashback queries on the source table to determine what changes have been made to the table since the previous run.
Oracle Logminer tracks changes in the redo logs allowing Sync to read those logs to determine the changes to a specfic table. The main difference vs. Orcale Flahsback is Sync does not interact with the source table. Instead, Sync reads directly from logs which has lower impact on the source database.
--Check if ArchiveLog is enabled already
SELECT LOG_MODE FROM V$DATABASE
--If LOG_MODE does not equal ARCHIVELOG
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; --Database level
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS
--Table level
ALTER TABLE <schema_name>.<table_name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNSIn CData Sync, create a Job with you Oracle source and select the Use Change Data Capture option.
CREATE TABLESPACE <tablespace_name> DATAFILE SIZE 100M AUTOEXTEND ON MAXSIZE 200M; CREATE FLASHBACK ARCHIVE <archive_name> TABLESPACE <tablespace_name> RETENTION 7 DAY;
RETENTION specifies the time period for which archive information is kept in your database. It is best to set a larger window to give Sync time to resolve conflicts and errors. If the last successful Job run is outside the retention period (i.e. RETENTION is set to 7 Days but the last successful sync was 8 days previous), Sync will automatically replicate the full table to ensure no changes were missed. ALTER TABLE <table_name> FLASHBACK ARCHIVE <archive_name>;
Note: To use Flashback, each table must have at least 1 Primary Key.In CData Sync, create a Job with you Oracle source and select the Use Oracle Flashback option.
This section details a selection of advanced features of the Oracle OCI Sync App.
Use SSL Configuration to adjust how Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats;. For further information, see the SSLServerCert property under "Connection String Options" .
Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies. You can also set up tunnel connections.
By default, the Sync App attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.
To specify another certificate, see the SSLServerCert connection property.
Set the following properties:
The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.
For more information on establishing a connection, see Establishing a Connection.
| Property | Description |
| ConnectionType | Connection properties to use for authentication. Accepted entries are Server, OracleTNS, LDAP. |
| Server | The host name or IP of the server hosting the Oracle database. |
| Port | The port used to connect to the server hosting the Oracle database. |
| ServiceName | The service name of the Oracle database. |
| User | Specifies the user ID of the authenticating Oracle OCI user account. |
| Password | Specifies the password of the authenticating user account. |
| DataSource | Specifies the Oracle database to connect to, using the Net Services Name, Connect Descriptor (TNS Connect String), or connection name. |
| LDAPUri | LDAP uri to connect the LDAP server. |
| LDAPUser | The User account for LDAP server. |
| LDAPPassword | The password for the LDAP user. |
| LDAPVersion | The LDAP version used to connect to and communicate with the server. |
| Wallet | The location of the client's Oracle Wallet. |
| Property | Description |
| LogModules | Specifies the core modules to include in the log file. Use a semicolon-separated list of module names. By default, all modules are logged. |
| Property | Description |
| Location | Specifies the location of a directory containing schema files that define tables, views, and stored procedures. Depending on your service's requirements, this may be expressed as either an absolute path or a relative path. |
| BrowsableSchemas | Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC . |
| Tables | Optional setting that restricts the tables reported to a subset of all available tables. For example, Tables=TableA,TableB,TableC . |
| Views | Optional setting that restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC . |
| IncludeSynonyms | Query metadata for synonyms as though they are the original tables. |
| ShowMetadataDescriptions | Controls whether table and column descriptions are returned via the platform metadata APIs and sys_tables / sys_views / sys_tablecolumns. |
| UseDBAMetadataViews | Query meta data from DBA_.. system views instead of ALL_.. system views. |
| Property | Description |
| AllowPreparedStatement | Prepare a query statement before its execution. |
| MaxLobSize | The volume in numbers of bytes or UTF-8 chars which is allowed to query by non-parameterized SELECT query. |
| MaxRows | Specifies the maximum rows returned for queries without aggregation or GROUP BY. |
| NativeFolder | The path to the directory containing the native dlls the provider works with. This property is effective on the Windows and macOS platforms only. |
| Other | Specifies additional hidden properties for specific use cases. These are not required for typical provider functionality. Use a semicolon-separated list to define multiple properties. |
| QueryPassthrough | This option passes the query to the Oracle OCI server as is. |
| ReconnectTimeout | The sleep time, in seconds, before retrying to reconnect to the server on a maximum idle time exceeded error. |
| ReconnectTries | The number of retry to connect server when a maximum idle time exceeded error is reported by server. |
This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.
| Property | Description |
| ConnectionType | Connection properties to use for authentication. Accepted entries are Server, OracleTNS, LDAP. |
| Server | The host name or IP of the server hosting the Oracle database. |
| Port | The port used to connect to the server hosting the Oracle database. |
| ServiceName | The service name of the Oracle database. |
| User | Specifies the user ID of the authenticating Oracle OCI user account. |
| Password | Specifies the password of the authenticating user account. |
| DataSource | Specifies the Oracle database to connect to, using the Net Services Name, Connect Descriptor (TNS Connect String), or connection name. |
| LDAPUri | LDAP uri to connect the LDAP server. |
| LDAPUser | The User account for LDAP server. |
| LDAPPassword | The password for the LDAP user. |
| LDAPVersion | The LDAP version used to connect to and communicate with the server. |
| Wallet | The location of the client's Oracle Wallet. |
Connection properties to use for authentication. Accepted entries are Server, OracleTNS, LDAP.
Together with Password and User, this field is used to choose connection properties that are used to authenticate against the server. Server is the default option. Use the following options:
The host name or IP of the server hosting the Oracle database.
The host name or IP of the server hosting the Oracle database.
The port used to connect to the server hosting the Oracle database.
The port used to connect to the server hosting the Oracle database.
The service name of the Oracle database.
The service name of the Oracle database, such as XE.
You can obtain this value by querying global_name from the Oracle SQL command line.
Specifies the user ID of the authenticating Oracle OCI user account.
The authenticating server requires both User and Password to validate the user's identity.
Specifies the password of the authenticating user account.
The authenticating server requires both User and Password to validate the user's identity.
Specifies the Oracle database to connect to, using the Net Services Name, Connect Descriptor (TNS Connect String), or connection name.
Specifies the Oracle database to connect to, using the Net Services Name, Connect Descriptor (TNS Connect String), or connection name. This value is found in your tnsnames.ora file at ORACLE_HOME/network/admin/tnsnames.ora.
For example: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=my.company.net)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCLPDB1.localdomain)))
LDAP uri to connect the LDAP server.
LDAP uri to connect the LDAP server and find out the Oracle's service name stored on LDAP server in the TNS record format.
The User account for LDAP server.
Use it when your LDAP server requires authorization to bind. Leave it empty otherwise.
The password for the LDAP user.
Use it when your LDAP server requires authorization to bind. Leave it empty otherwise.
The LDAP version used to connect to and communicate with the server.
Set this property to 2 or 3. The Sync App connects to a standard LDAP client as specified in RFC 1777, 2251, and other LDAP RFCs.
The location of the client's Oracle Wallet.
This property is required to set up a secure TCPS connection.
When you use a TNS connection string in the DataSource connection property, the Sync App first checks the TNS string for WALLET_LOCATION. If the string contains no WALLET_LOCATION value, the Sync App uses the wallet specified in this connection property (Wallet) instead.
This section provides a complete list of the Logging properties you can configure in the connection string for this provider.
| Property | Description |
| LogModules | Specifies the core modules to include in the log file. Use a semicolon-separated list of module names. By default, all modules are logged. |
Specifies the core modules to include in the log file. Use a semicolon-separated list of module names. By default, all modules are logged.
This property lets you customize the log file content by specifying the logging modules to include. Logging modules categorize logged information into distinct areas, such as query execution, metadata, or SSL communication. Each module is represented by a four-character code, with some requiring a trailing space for three-letter names.
For example, EXEC logs query execution, and INFO logs general provider messages. To include multiple modules, separate their names with semicolons as follows: INFO;EXEC;SSL.
The Verbosity connection property takes precedence over the module-based filtering specified by this property. Only log entries that meet the verbosity level and belong to the specified modules are logged. Leave this property blank to include all available modules in the log file.
For a complete list of available modules and detailed guidance on configuring logging, refer to the Advanced Logging section in Logging.
This section provides a complete list of the Schema properties you can configure in the connection string for this provider.
| Property | Description |
| Location | Specifies the location of a directory containing schema files that define tables, views, and stored procedures. Depending on your service's requirements, this may be expressed as either an absolute path or a relative path. |
| BrowsableSchemas | Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC . |
| Tables | Optional setting that restricts the tables reported to a subset of all available tables. For example, Tables=TableA,TableB,TableC . |
| Views | Optional setting that restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC . |
| IncludeSynonyms | Query metadata for synonyms as though they are the original tables. |
| ShowMetadataDescriptions | Controls whether table and column descriptions are returned via the platform metadata APIs and sys_tables / sys_views / sys_tablecolumns. |
| UseDBAMetadataViews | Query meta data from DBA_.. system views instead of ALL_.. system views. |
Specifies the location of a directory containing schema files that define tables, views, and stored procedures. Depending on your service's requirements, this may be expressed as either an absolute path or a relative path.
The Location property is only needed if you want to either customize definitions (for example, change a column name, ignore a column, etc.) or extend the data model with new tables, views, or stored procedures.
If left unspecified, the default location is %APPDATA%\\CData\\OracleOci Data Provider\\Schema, where %APPDATA% is set to the user's configuration directory:
| Platform | %APPDATA% |
| Windows | The value of the APPDATA environment variable |
| Linux | ~/.config |
Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .
Listing all available database schemas can take extra time, thus degrading performance. Providing a list of schemas in the connection string saves time and improves performance.
Optional setting that restricts the tables reported to a subset of all available tables. For example, Tables=TableA,TableB,TableC .
Listing all available tables from some databases can take extra time, thus degrading performance. Providing a list of tables in the connection string saves time and improves performance.
If there are lots of tables available and you already know which ones you want to work with, you can use this property to restrict your viewing to only those tables. To do this, specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.
Note: If you are connecting to a data source with multiple schemas or catalogs, you must specify each table you want to view by its fully qualified name. This avoids ambiguity between tables that may exist in multiple catalogs or schemas.
Optional setting that restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC .
Listing all available views from some databases can take extra time, thus degrading performance. Providing a list of views in the connection string saves time and improves performance.
If there are lots of views available and you already know which ones you want to work with, you can use this property to restrict your viewing to only those views. To do this, specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.
Note: If you are connecting to a data source with multiple schemas or catalogs, you must specify each view you want to examine by its fully qualified name. This avoids ambiguity between views that may exist in multiple catalogs or schemas.
Query metadata for synonyms as though they are the original tables.
By default, the Sync App maps synonyms to their parent tables. It queries columns and their primary/foreign key metadata as if they were the regular tables.
If this property is set to false, then the Sync App does not list table and view synonyms.
Setting this property to false improves metadata performance. Consider doing so if you do not need to request metadata of synonyms automatically.
Controls whether table and column descriptions are returned via the platform metadata APIs and sys_tables / sys_views / sys_tablecolumns.
By default table and column descriptions are not shown, since the Oracle OCI requires an extra join in meta-query beyond what is usually required to read system views describing the object comments.
Enabling this option will show table and column descriptions. This can slow down metadata operations on large datasets.
Query meta data from DBA_.. system views instead of ALL_.. system views.
By default, the Sync App queries meta data for the objects accessible to the current user. If the UseDBAMetadataViews property is set to true, then the Sync App will query meta data for all respective objects in the database.
This option will not work if current user has insufficient privileges to access DBA_.. system views.
This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.
| Property | Description |
| AllowPreparedStatement | Prepare a query statement before its execution. |
| MaxLobSize | The volume in numbers of bytes or UTF-8 chars which is allowed to query by non-parameterized SELECT query. |
| MaxRows | Specifies the maximum rows returned for queries without aggregation or GROUP BY. |
| NativeFolder | The path to the directory containing the native dlls the provider works with. This property is effective on the Windows and macOS platforms only. |
| Other | Specifies additional hidden properties for specific use cases. These are not required for typical provider functionality. Use a semicolon-separated list to define multiple properties. |
| QueryPassthrough | This option passes the query to the Oracle OCI server as is. |
| ReconnectTimeout | The sleep time, in seconds, before retrying to reconnect to the server on a maximum idle time exceeded error. |
| ReconnectTries | The number of retry to connect server when a maximum idle time exceeded error is reported by server. |
Prepare a query statement before its execution.
If the AllowPreparedStatement property is set to false, statements are parsed each time they are executed. Setting this property to false can be useful if you are executing many different queries only once.
If you are executing the same query repeatedly, you will generally see better performance by leaving this property at the default, true. Preparing the query avoids recompiling the same query over and over. However, prepared statements also require the Sync App to keep the connection active and open while the statement is prepared.
The volume in numbers of bytes or UTF-8 chars which is allowed to query by non-parameterized SELECT query.
If the MaxLobSize property is set to 0 or negative value, the default of 2000 bytes will be used instead.
Specifies the maximum rows returned for queries without aggregation or GROUP BY.
This property sets an upper limit on the number of rows the Sync App returns for queries that do not include aggregation or GROUP BY clauses. This limit ensures that queries do not return excessively large result sets by default.
When a query includes a LIMIT clause, the value specified in the query takes precedence over the MaxRows setting. If MaxRows is set to "-1", no row limit is enforced unless a LIMIT clause is explicitly included in the query.
This property is useful for optimizing performance and preventing excessive resource consumption when executing queries that could otherwise return very large datasets.
The path to the directory containing the native dlls the provider works with. This property is effective on the Windows and macOS platforms only.
The path to the directory containing the native dlls the Sync App works with. By default Sync App relies on the standard system policy for loading the dynamic libaries. Apply full path or the path relative to your executable.
Specifies additional hidden properties for specific use cases. These are not required for typical provider functionality. Use a semicolon-separated list to define multiple properties.
This property allows advanced users to configure hidden properties for specialized scenarios. These settings are not required for normal use cases but can address unique requirements or provide additional functionality. Multiple properties can be defined in a semicolon-separated list.
Note: It is strongly recommended to set these properties only when advised by the support team to address specific scenarios or issues.
Specify multiple properties in a semicolon-separated list.
| DefaultColumnSize | Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000. |
| ConvertDateTimeToGMT | Determines whether to convert date-time values to GMT, instead of the local time of the machine. |
| RecordToFile=filename | Records the underlying socket data transfer to the specified file. |
This option passes the query to the Oracle OCI server as is.
When this is set, queries are passed through directly to Oracle OCI.
The sleep time, in seconds, before retrying to reconnect to the server on a maximum idle time exceeded error.
The ReconnectTries property must be greater than 0 for this value to have any effect.
The number of retry to connect server when a maximum idle time exceeded error is reported by server.
Setting this value will allow the Sync App to autmotically reconnect if an 'ORA-02396: exceeded maximum idle time' error is received from the server. The default value the ReconnectTries property is set to 0 meaning the request will not be retried.