Oracle databases are now generally 64-bit but the CSE client software is 32-bit. That means the CSE needs to use the 32-bit Oracle client to connect to the database which itself connects via the Oracle TNS Listener.
Gen 8.0, Gen 8.5 & Gen 8.6 CSE running under an Oracle 64-bit database.
The supported version of Oracle 11g or 12c will vary across Gen version but the principles outlined in this article remain the same
The Oracle 32-bit client cannot connect to a Oracle 64-bit database using the local (Bequeath Protocol) connection and must connect via the Oracle TNS Listener. This means that the CSE is subject to the same condition and the Oracle Net Service Name for the CSE database needs to be configured in the tnsnames.ora file for the 32-bit client installation. NOTE: this will be a different location than the 64-bit database installation.
The recommendation is actually to set the Net Service Name in the tnsnames.ora file for both 32-bit and 64-bit Oracle 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 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 userid@<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 DBUSER=ency@DBCSE DBPSWD=ency"