Connect to Oracle Database

The following steps are necessary for the TimeXtender ODX Server to successfully connect to an Oracle data source. EZCONNECT and TNSNAMES are the two common ways of configuring an Oracle database connection. The EZCONNECT method is perhaps more straightforward, but the TNSNAMES method may be more common. Both connection methods and described below, and as a prerequisite both require the installation and configuration of the Oracle Database Access Components (ODAC) on the ODX Server.

Installing the Oracle Data Access Components.

Due to the discontinuation of direct Oracle support in the Microsoft .NET Framework, the Oracle Data Access Components are necessary for TimeXtender to connect to an Oracle database.

These components should be installed on the system that is running the ODX Server service according to the following steps.

1. For installation on Windows Server Operating systems, open the Server Manager -> Dashboard -> Add Roles and Features, and check the box to add the NET Framework 3.5 (includes 2.0 and 3.0). This feature should be added prior to installing the ODAC components.
2. Download the Oracle Data Access Components from the following webpage. https://www.oracle.com/database/technologies/odac-downloads.html (Scroll to the bottom of the webpage to find the needed 11.2 Release 6 version.

   

    

3. Run the Command Prompt as an administrator (Right click -> Run as Administrator). This is very important!
4. Navigate to the folder that has the unzipped installation files, and then run the installation by entering the following at the command prompt: "install.bat all c:\Oracle odac"

After the ODAC components are installed, the TimeXtender Oracle Data Source provider supports the following two connections methods.

1. TNSNAMES
2. EZCONNECT

The EZCONNECT method foregoes the need for a TNSNAMES.ORA file by entering the server, port, and database information in the TimeXtender portal under the data source. However, using the TNSNAMES.ORA file to connect to the Oracle database may be the more common approach. These two methods are outlined as follows.

TNSNAMES connection method.

It is common for Oracle database administrators to provide the following two files to end users who need to connect to an Oracle database.

1. TNSNAMES.ORA
2. SQLNET.ORA

These two files need to be saved in a folder on the user’s system and environment variables need to be created in the machine context pointing to this folder as well as to the Oracle ODAC components installation directory. The following configurations need to be completed on the ODX Server in order for the Oracle ODAC components to properly enable the Oracle database connection.

1. Provided the Oracle ODAC Components are installed into the C:\Oracle directory as described above, then the TNSNAMES.ORA and SQLNET.ORA files can be saved in the C:\Oracle\TNS directory.
2. Create an Environment Variable in the machine context with the name of “TNS_ADMIN” and the value of “C:\Oracle\TNS”.
3. Add the following two entries to the “Path” Environment Variable in the machine context:
   1. “C:\Oracle”
   2. “C:\Oracle\bin”
4. Reboot the system.

The following outlines how the Oracle Data Source in the TimeXtender Portal relates to the information in the TNSNAMES.ORA file.

Adding the TimeXtender Oracle Data Source that uses the TNSNAMES.ORA file.

In the TimeXtender Portal, open Data Source Connections and click the button to add a new data source. For the provider, select the most recent version of the TimeXtender Oracle Data Source provider.

Use the following for the specific provider settings.

1. Data Source: Set this to the Schema name, which corresponds to “GML” in the TNSNAMES.ORA file above.
2. User ID: Set this to the username, which corresponds to “GML” in the TNSNAMES.ORA file above.
3. Password: Set this to the password for the username above.

   

4. Out of range date conversion: Set this to “MS SQL min/max date”, which will convert dates that exceed the minimum or maximum value supported by SQL Server to the SQL Server minimum/maximum.
5. Round numbers: Set this to “Yes”, which will use the Round() function on numeric and decimal values to ensure they fit the SQL Server data types.

   

    

After creating the Oracle Data Source in the TimeXtender Portal and the Oracle Data Access Components have been installed and configured on the TimeXtender ODX Server system, the TimeXtender Desktop can be used to successfully implement the Synchronization and Transfer tasks for the new Oracle Data Source.

In the example above, TimeXtender will use the Data Source setting of “GML” to read the TNSNAMES.ORA file to get the following information:

1. Host name: tx-app-server-4ca944bda8.westus3.cloudapp.azure.com
2. Port number: 1521
3. Pluggable database: XEPDB1

EZCONNECT method

It is common for Oracle database administrators to provide the following two files to end users who need to connect to an Oracle database.

1. TNSNAMES.ORA
2. SQLNET.ORA

For the EZCONNECT method, only the SQLNET.ORA file needs to be saved in a folder on the user’s system and environment variables need to be created in the machine context pointing to this folder as well as to the Oracle ODAC components installation directory. These configurations can be done manually or the TimeXtender Oracle Data Source ODAC Configuration Utility can be downloaded to expedite the creation of the environment variables and the .ORA files if needed.

1. Provided the Oracle ODAC Components are installed into the C:\Oracle directory, then the SQLNET.ORA files can be saved in the C:\Oracle\TNS directory.
2. Create an Environment Variable in the machine context with the name of “TNS_ADMIN” and the value of “C:\Oracle\TNS”.
3. Add the following two entries to the “Path” Environment Variable in the machine context:
   1. “C:\Oracle”
   2. “C:\Oracle\bin”
4. Reboot the system.

Adding the TimeXtender Oracle Data Source that uses EZCONNECT.

In the TimeXtender Portal, open Data Source Connections and click the button to add a new data source. For the provider, select the most recent version of the TimeXtender Oracle Data Source provider.

1. Data Source: Set this to the <HostName>:<PortNumber>/<DataBaseName>, which corresponds to “#########-4ca944bda8.westus3.cloudapp.azure.com:1521/XEPDB1”.
2. User ID: Set this to the username, which corresponds to “GML” in the TNSNAMES.ORA file above.
3. Password: Set this to the password for the username above.

   

4. Out of range date conversion: Set this to “MS SQL min/max date”, which will convert dates that exceed the minimum or maximum value supported by SQL Server to the SQL Server minimum/maximum.
5. Round numbers: Set this to “Yes”, which will use the Round() function on numeric and decimal values to ensure they fit the SQL Server data types.

   

    

In the example above, TimeXtender will use the Data Source setting get the following information:

1. Host name: ########-4ca944bda8.westus3.cloudapp.azure.com
2. Port number: 1521
3. Pluggable database: XEPDB1

After creating the Oracle Data Source in the TimeXtender Portal and the prerequisite Oracle Data Access Components have been installed and configured on the TimeXtender ODX Server system, the TimeXtender Desktop can be used to successfully execute the Synchronization and Transfer tasks for the new Oracle Data Source.

Default Schemas

Oracle has a number of schemas that are categorized as default schemas and these are listed below. If your Oracle Data Source schema matches one of the default schemas, then you will need to configure the “Include default schemas” to “Yes” in order for your schema tables to be included in the TimeXtender data source synchronization task. Note that including default schemas may also return a large number of unwanted tables, so the default for this setting is “No”.

Default schemas are defined in the Oracle Data Source as the follows:

All owners starting with "APEX_"

and "ANONYMOUS", "APPQOSSYS", "AUDSYS", "BI", "CTXSYS", "DBSFWUSER", "DBSNMP", "DIP", "DVF","DVSYS", "EXFSYS", "FLOWS_FILES", "GGSYS", "GSMADMIN_INTERNAL", "GSMCATUSER", "GSMUSER", "HR", "IX", "LBACSYS", "MDDATA", "MDSYS", "MGMT_VIEW", "OE", "OJVMSYS", "OLAPSYS", "ORACLE_OCM", "ORDDATA", "ORDPLUGINS", "ORDSYS", "OUTLN", "OWBSYS", "OWBSYS_AUDIT", "PM", "REMOTE_SCHEDULER_AGENT", "SCOTT", "SH", "SI_INFORMTN_SCHEMA", "SPATIAL_CSW_ADMIN_USR", "SPATIAL_WFS_ADMIN_USR" ,"SYS", "SYSBACKUP", "SYSDG", "SYSKM", "SYSMAN", "SYSRAC", "SYSTEM", "SYS$UMF", "WMSYS", "XDB", "XS$NULL"