Since our customers are forced to move to Oracle Database 12.1, I am in the role to support their upgrades. Recently I upgraded a Windows environment that used the Heterogeneous Services to connect to an external ODBC datasource via a database link. The configuration is a little tricky, but pretty straight forward once you get the idea.
First, there must be an ODBC datasource. That is a simple System DSN which was already in place.
Second, you have to choose a kind of “virtual” SID for your connection to this ODBC datasource.
Third, create an init.ora in the $ORACLE_HOME/hs/admin directory and put at least one parameter into it:
HS_FDS_CONNECT_INFO="<Name of ODBC System DSN>"
Fourth, add static SID to the listener by modifying the listener.ora file:
SID_LIST_LISTENER_STD = (SID_LIST = (SID_DESC= (SID_NAME=<virtual SID>) (ORACLE_HOME=c:\oracle\product\184.108.40.206\dbprod) (PROGRAM=dg4odbc) ) )
Then reload the listener configuration to make it active.
Fifth, create a tnsnames.ora entry that points to the listener and the virtual SID. Make sure, you specify “HS=OK”.
MYODBCDS = (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = <listener host>)(PORT = 1521)) (CONNECT_DATA = (SID = <virtual SID>) ) (HS = OK) )
Sixth and finally, create a database link that points to the tnsnames entry:
SQL> create database link myodbcds connect to <user> identified by <pwd> using "MYODBCDS"; Database link created.
All those steps were done years ago in the old 11.2 environment. Everything worked fine there. So I simply copied all the configuration details to the new 12.1 Oracle home. Then I moved the listener to the new 12.1 home and upgraded the database. But when testing the HS connection, the following happened:
SQL*Plus: Release 220.127.116.11.0 Production on Tue Mar 28 11:30:45 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Tue Mar 28 2017 11:06:02 +02:00 Connected to: Oracle Database 12c Standard Edition Release 18.104.22.168.0 - 64bit Production SQL> select * from dual@myodbcds ; select * from dual@myodbcds * ERROR at line 1: ORA-28545: error diagnosed by Net8 when connecting to an agent Unable to retrieve text of NETWORK/NCR message 65535 ORA-02063: preceding 2 lines from MYODBCDS
I checked the listener services, the connection seemed to be established successfully.
C:\>lsnrctl services listener LSNRCTL for 64-bit Windows: Version 22.214.171.124.0 - Production on 28-MAR-2017 11:39:05 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=1521))) Services Summary... Service "<virtual SID>" has 1 instance(s). Instance "<virtual SID>", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:1 refused:0 LOCAL SERVER The command completed successfully
So I created a new listener in the old 11.2 home that used another port to test things. The 11.2 listener worked fine. So what is the difference between both listeners?
After a little research I stumbled upon the Windows services. The database service was not running as LocalSystem but as a domain user. This is because the database writes it’s backups to an UNC share. Both, 11.2 and 12.1, listeners used the LocalSystem account. So I changed the 12.1 listener to use the domain account too and this solved the problem.
SQL*Plus: Release 126.96.36.199.0 Production on Tue Mar 28 11:39:10 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Tue Mar 28 2017 11:36:53 +02:00 Connected to: Oracle Database 12c Standard Edition Release 188.8.131.52.0 - 64bit Production SQL> select * from dual@myodbcds; D - X
I have no idea why the 11.2 listener can run as LocalSystem whilst the 12.1 listener cannot. But generally I think it is a good practice to run listener and database services with the same user credentials.