Gen CSE Configuration "Error 1 while running Oracle connectonly.sql" (ORA-12560)

Products

Gen

Issue/Introduction

Gen 8.6 Client Server Encyclopedia (CSE).
Gen 8.6 Complete (WKS86200/SO09618) with "ORACLE 19C CERTIFICATION FOR CSE" PTF are installed (CSN86204/SS14886)

Running the CSE Configuration program (cse_config.exe) and the Encyclopedia Database Connection fails with "Error 1 while running Oracle connectonly.sql":

The corresponding log file C:\ProgramData\CA\Gen 8.6\logs\CSE\connectonly.log shows:
*****
Run CSE Oracle command file Connect.sql on database dbcse for user ency

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 5 02:42:29 2021
Version 19.3.0.0.0

ERROR:
ORA-12560: TNS:protocol adapter error
*****

Oracle 19c 64-bit database and Oracle 19c 32-bit client (for the 32-bit CSE configuration/CSE/ software) are installed per Gen 8.6 >Technical Requirements > Development Platform Environments - see "CSE & Host Encyclopedia".
In the Windows PATH, the Oracle 32-bit client bin directory is also ahead of the Oracle 64-bit database bin directory.

Environment

Release: Gen 8.6 (Complete)
Component: Gen Client Server Encyclopedia (CSE)

Cause

Gen Windows CSE software is still 32-bit and needs to use the 32-bit Oracle client to connect to the 64-bit database.
In general 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 defined in the ...\network\admin\tnsnames.ora file.

The CSE Configuration initially uses the Oracle sqlplus.exe program and later 32-bit initialization programs (initdir.exe and initdb.exe) to connect to the Oracle database and the following factors need to be considered:
- If the 32-bit Oracle client bin directory is in the PATH before the 64-bit Oracle database bin directory then only the 32-bit ...\network\admin\tnsnames.ora file needs to have the Oracle Net Service Name defined because the 32-bit sqlplus.exe will be automatically found first and corresponding tnsnames.ora file used.
- If instead the 64-bit Oracle database bin directory is in the PATH before the 32-bit Oracle client bin directory then both 32-bit and 64-bit ...\network\admin\tnsnames.ora files will also need to have the Oracle Net Service Name defined because the 64-bit sqlplus.exe will be automatically found first and corresponding tnsnames.ora file used.
(If the Oracle NET Service Name is defined in both tnsnames.ora files then the PATH order does not matter, which is the simplest and suggested option).

The reported error "Error 1 while running Oracle connectonly.sql" here is due to the fact that the 32-bit version of sqlplus.exe is being picked up first from the PATH but cannot connect to the database using the simple userid/password syntax and must include the @net_service_name in the connection string syntax.

Resolution

1. Ensure the 32-bit client ...\network\admin\tnsnames.ora file has an Oracle Net Service Name defined for the target database
e.g. in this example, DBCSE is the Oracle Net Service Name for target database dbcse (SERVICE_NAME):
DBCSE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbcse)
)
)

2. Then to resolve the connection error, use that Oracle Net Service Name in the User ID field with syntax userid@net_service_name e.g. for the above example DBCSE use ency@dbcse (NOTE: the Oracle Net Service Name is case-independent)

The corresponding log file C:\ProgramData\CA\Gen 8.6\logs\CSE\connectonly.log will then show:
*****
Run CSE Oracle command file Connect.sql on database dbcse for user ency@dbcse

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 5 03:41:06 2021
Version 19.3.0.0.0

Connected.
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
*****

Additional Information

Gen Windows Oracle CSE Configuration with a 64-bit Oracle Database

Gen 8.6 Windows CSE support for Oracle 19c

Gen 8.6 Solutions & Patches