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.
Create a connection to Oracle OCI by navigating to the Connections page in the Sync App application and selecting the corresponding icon in the Add Connections panel. If the Oracle OCI icon is not available, click the Add More icon to download and install the Oracle OCI connector from the CData site.
Required properties are listed under the Settings tab. The Advanced tab lists connection properties that are not typically required.
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. 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. Afterwards, restart the application to load the Oracle native libraries.
After you have added the appropriate libraries to your PATH, set the following to connect:
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. Your system administrator may allow anonymous LDAP logins, in which case 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; see the SSLServerCert property under "Connection String Options" for more information.
Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies. You can also set up tunnel connections.
See Logging for an overview of configuration settings that can be used to refine CData logging. For basic logging, you only need to set two connection properties, but there are numerous features that support more refined logging, where you can select subsets of information to be logged using the LogModules connection property.
By default, the Sync App attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.
To specify another certificate, see the SSLServerCert property for the available formats to do so.
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 | The Oracle OCI user account used to authenticate. |
| Password | The password used to authenticate the user. |
| DataSource | Oracle Net Services Name, Connect Descriptor (known also as TNS Connect String), or an easy connect naming that identifies the database to which to connect. |
| 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. |
| Property | Description |
| LogModules | Core modules to be included in the log file. |
| Property | Description |
| Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
| BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC. |
| Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC. |
| Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC. |
| IncludeSynonyms | Query meta data for synonyms as they are being the reqular 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 | Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time. |
| Other | These hidden properties are used only in specific use cases. |
| 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 | The Oracle OCI user account used to authenticate. |
| Password | The password used to authenticate the user. |
| DataSource | Oracle Net Services Name, Connect Descriptor (known also as TNS Connect String), or an easy connect naming that identifies the database to which to connect. |
| 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. |
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.
The Oracle OCI user account used to authenticate.
Together with Password, this field is used to authenticate against the Oracle OCI server.
The password used to authenticate the user.
The User and Password are together used to authenticate with the server.
Oracle Net Services Name, Connect Descriptor (known also as TNS Connect String), or an easy connect naming that identifies the database to which to connect.
Oracle Net Services Name, Connect Descriptor (known also as TNS Connect String), or an easy connect naming that identifies the database to which to connect.
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.
This section provides a complete list of the Logging properties you can configure in the connection string for this provider.
| Property | Description |
| LogModules | Core modules to be included in the log file. |
Core modules to be included in the log file.
Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.
See the Logging page for an overview.
This section provides a complete list of the Schema properties you can configure in the connection string for this provider.
| Property | Description |
| Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
| BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC. |
| Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC. |
| Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC. |
| IncludeSynonyms | Query meta data for synonyms as they are being the reqular 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. |
A path to the directory that contains the schema files defining tables, views, and stored procedures.
The path to a directory which contains the schema files for the Sync App (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) 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" with %APPDATA% being set to the user's configuration directory:
This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.
This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the Sync App.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
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 that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.
Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the Sync App.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
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 that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Query meta data for synonyms as they are being the reqular tables.
By default, the Sync App maps synonyms to its parent tables and queries columns and primary/foreign key meta data for them like they are being the regular tables. If property is set to false, then the Sync App is only listing synonyms and is not mapping them to the regular tables.
Setting the property to false is increasing meta-data performance. Consider it if you don't need to request meta-data 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 | Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time. |
| Other | These hidden properties are used only in specific use cases. |
| 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.
Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
These hidden properties are used only in specific use cases.
The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.
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.