Oracle databases are now generally 64-bit but the Windows CSE software is still 32-bit.
Therefore the CSE needs to use the 32-bit Oracle client to connect to the 64-bit database.
An Oracle 32-bit client cannot connect to a Oracle 64-bit database using a local (Bequeath Protocol) connection and instead must connect via the Oracle TNS Listener using an Oracle Net Service Name.
Gen 8.0, Gen 8.5 & Gen 8.6 CSE running under an Oracle 64-bit database.
The supported version of Oracle 11g, 12c or 19c will vary across Gen version but the principles outlined in this article remain the same.
An Oracle Net Service Name for the CSE database needs to be configured in the ...\network\admin\tnsnames.ora file for the 32-bit Oracle client installation. NOTE: this will be a different location than the 64-bit Oracle database installation.
The recommendation is actually to set the Oracle Net Service Name in the ...\network\admin\tnsnames.ora file for both the Oracle 32-bit client and 64-bit database software installations. This will ensure that both 32-bit and 64-bit Oracle clients (including SQL*Plus) can resolve the Net Service Name and are able connect to the database. SQL*Plus is used by the CSE configuration program to check the database connection and configuring both clients avoids any PATH order problems i.e. it will not matter if the 64-bit or 32-bit version of SQL*Plus is picked up first.
In this example the ...\network\admin\tnsnames.ora file has the Net Service Name DBCSE defined as follows:
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
There are 2 options to force the use of a Net Service Name to connect to the CSE database:
1. On Windows set the system environment variable LOCAL to the Net Service Name. On Unix set the environment variable TWO_TASK to the Net Service Name. For the above example tnsnames.ora file, LOCAL or TWO_TASK should be set to the value DBCSE.
Setting LOCAL or TWO_TASK will ensure that any Oracle connection will default to using the Net Service Name defined and go via the Listener, instead of the bequeath connection. This is the cleanest option if the CSE is running on a dedicated server and connections to other Oracle databases are not a concern. The CSE configuration program should execute seamlessly and the resulting iefmd.ini file will contain the standard connection parameters as follows:
"DBNAME=DBCSE DBUSER=ency DBPSWD=ency".
The environment variable (LOCAL or TWO_TASK) will need to remain in place for the iefmd to execute successfully.
2. Use the syntax [email protected]<Net_Service_Name> for the Database User IDs in the CSE configuration program. This provides more flexibility if the CSE is sharing the machine with other Oracle databases and setting LOCAL or TWO_TASK is undesirable. The resulting iefmd.ini file will contain the Net Service Name syntax in the DBUSER field for the connection parameters. For the above example tnsnames.ora file where the Net Service Name is DBCSE, the resulting iefmd.ini file will contain connection parameters as follows:
"DBNAME=DBCSE [email protected] DBPSWD=ency"