Home » RDBMS Server » Networking and Gateways » Not able to connect to Oracle(12c) to progress using Oracle database gateway(12c) for ODBC (Oracle12c , Oracle database gateway for ODBC 12c , Progress Version 11)
Not able to connect to Oracle(12c) to progress using Oracle database gateway(12c) for ODBC [message #671786] Wed, 19 September 2018 08:55 Go to next message
saterp
Messages: 4
Registered: September 2018
Junior Member

Need help to connect the Oracle and Progress Database using Oracle gateway for ODBC.

Server1: I configured the database server TNS.ora and listner.ora entries like below.

Listener.ora:
--start of listener file---
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = E:\app1\SM00444837\product\12.1.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:E:\app1\SM00444837\product\12.1.0\dbhome_1\bin

\oraclr12.dll")
)
(SID_DESC =
(PROGRAM = dg4odbc)
(SID_NAME = GW_TO_PROG)
(ORACLE_HOME = E:\app1\SM00444837\product\12.1.0\dbhome_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1host)(PORT = 1521))
)
)

ADR_BASE_LISTENER = E:\app1\SM00444837\product\12.1.0\dbhome_1\log
---end of listener file--------
tnsnames.ora:
--start of tns file---
# tnsnames.ora Network Configuration File: E:\app1\SM00444837\product

\12.1.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
#This is the oracle database in local system
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1host)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST)
)
)
#This is the target system DSN name where this DSN connects to Progress db in target system
GW_TO_PROG =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST= server2host)(PORT=1522))
(CONNECT_DATA= (SID=GW_TO_PROG))
(HS=OK)
)
--------End of tns file-------------

initGW_TO_PROG.ora:
--start of initGW_TO_PROG.ora file--
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
# HS init parameters
#"GW_TO_PROG" is target system DSN which connects to progress in target system
HS_FDS_CONNECT_INFO = "GW_TO_PROG"
HS_FDS_TRACE_LEVEL = off


#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

-----end of initGW_TO_PROG.ora file--

Server2: I have configured the Oracle database gateway for ODBC and DSN entry to connect to

the progress DB, Progress db is also installed in the server2.

Listener.ora:
---Start of listener.ora file------
# listener.ora Network Configuration File: D:\app1\tg\HM00495608\product

\12.1.0\tghome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\app1\tg\HM00495608\product\12.1.0\tghome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\app1\tg\HM00495608\product\12.1.0\tghome_1\bin

\oraclr12.dll")
)
(SID_DESC =
(PROGRAM = dg4odbc)
(SID_NAME = GW_TO_PROG)
(ORACLE_HOME = D:\app1\tg\HM00495608\product\12.1.0\tghome_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Server2host)(PORT = 1522))
)
)
-----end of listener.ora file----

tnames.ora:

# tnsnames.ora Network Configuration File: D:\app1\tg\HM00495608\product

\12.1.0\tghome_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
#TEST is the oracle database SID hosted in server1
TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1host)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TEST)
)
)
GW_TO_PROG =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=server2host)(PORT=1522))
(CONNECT_DATA= (SID=GW_TO_PROG))
(HS=OK)
)
---end of tnames.ora file for gateway----

---Start of InitGW_TO_PROG.ORA file in server2---

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = "GW_TO_PROG"
HS_FDS_TRACE_LEVEL = off
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
--End of initGW_TO_PROG.ora---

I configured the oracle gateway in the target system where the Progress db is installed and

trying to connect the gateway and oracle(installed in local system)
.
I am trying to build the communication between the oracle(server1)=> Oracle gateway

(server2)=>progress DB(server2) but unable to establish the connection.

getting below error while trying to access to progress table from oracle server .

"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 SP2 "

SP2 is a DB link pointing to GW_TO_PROG service name.

I would like to know the what changes i need to do in each of the home(oracle and gateway)

with respect to tnsname.ora , listener.ora and initdg4odbc.ora files in each.

Please review and suggest me the changes requried in these files.

do i need to add any library files to initGW_TO_PROG.ora file in both server1(Oracle server)

and server2(gateway file) like below, if so , any idea where do i find this in windows

system(both server1 and server2 are windows systems).

HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so

I would be grateful for your help.

Regards,
Sathish

Re: Not able to connect to Oracle(12c) to progress using Oracle database gateway(12c) for ODBC [message #671787 is a reply to message #671786] Wed, 19 September 2018 09:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://lmgtfy.com/?q=connect+oracle+to+progress+database
it can be done when done correctly
Re: Not able to connect to Oracle(12c) to progress using Oracle database gateway(12c) for ODBC [message #671788 is a reply to message #671786] Wed, 19 September 2018 09:19 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
This could be your problem,Quote:
"ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
It may be possible to configure release 12 and release 8 to work together, see here:
http://www.orafaq.com/forum/m/663661/?srch=SQLNET.ALLOWED_LOGON_VERSION_SERVER#msg_663661
but it may not be. You really need to upgrade the software you have on the Progress side to 12.
Re: Not able to connect to Oracle(12c) to progress using Oracle database gateway(12c) for ODBC [message #671792 is a reply to message #671787] Wed, 19 September 2018 11:28 Go to previous messageGo to next message
saterp
Messages: 4
Registered: September 2018
Junior Member
Thank you for Prompt reply.

I need to Achieve two way communication.. I mean i need to push data from oracle to Progress and Progress to Oracle.

If i Understand correctly Progress Data server is to pull the data into Progress database.
Re: Not able to connect to Oracle(12c) to progress using Oracle database gateway(12c) for ODBC [message #671793 is a reply to message #671792] Wed, 19 September 2018 11:29 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
So upgrade your Oracle client software. As I said.
Re: Not able to connect to Oracle(12c) to progress using Oracle database gateway(12c) for ODBC [message #671794 is a reply to message #671788] Wed, 19 September 2018 11:30 Go to previous messageGo to next message
saterp
Messages: 4
Registered: September 2018
Junior Member
HI John,

Are you talking about oracle release 12 and Progress Release 8.

I need to connect Oracle 12c and Progress 11.

Regards,
Sathish
Re: Not able to connect to Oracle(12c) to progress using Oracle database gateway(12c) for ODBC [message #671795 is a reply to message #671794] Wed, 19 September 2018 11:34 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
It seems that your Oracle client on the Progress side is release 8. You cannot have proper communications between a release 8 Oracle client and a release 12 Oracle server. I have no idea what your Progress software is. Oracle does not know or care, either. All it knows is the Oracle client software that you have installed. And, if you can be bothered to read my first post, you see that it is release 8. You need to upgrade it, so that you can join the twenty first century.

Re: Not able to connect to Oracle(12c) to progress using Oracle database gateway(12c) for ODBC [message #671796 is a reply to message #671795] Wed, 19 September 2018 11:40 Go to previous messageGo to next message
saterp
Messages: 4
Registered: September 2018
Junior Member
HI John,

Thank you.

Server 1 (Windows 7): Oracle database server(12.1.0)

Server 2(Windows 7): Oracle database gateway for ODBC(12.1.0) and Progress Database 11
I have created a DSN entry in server2 which will connect to progress.

I have not installed any oracle client. as per the oracle documentation there is no mention of oracle client software.
It is only oracle server and oracle database gateway.

Please suggest.

Regards,
Sathish
Re: Not able to connect to Oracle(12c) to progress using Oracle database gateway(12c) for ODBC [message #671797 is a reply to message #671796] Wed, 19 September 2018 11:44 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Now, I wonder if this message that you posted but appear to have ignored refers to Oracle Net release 8 or to Oracle Net release 99?
Quote:
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
My guess is release 8. What do you think?
Re: Not able to connect to Oracle(12c) to progress using Oracle database gateway(12c) for ODBC [message #671798 is a reply to message #671786] Wed, 19 September 2018 12:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

ORA-28545: error diagnosed by Net8 when connecting to an agent
 *Cause:   An attempt to call an external procedure or to issue SQL
           to a non-Oracle system on a Heterogeneous Services database link
           failed at connection initialization.  The error diagnosed
           by Net8 NCR software is reported separately.
 *Action:  Refer to the Net8 NCRO error message.  If this isn't clear,
           check connection administrative setup in tnsnames.ora
           and listener.ora for the service associated with the
           Heterogeneous Services database link being used, or with
           'extproc_connection_data' for an external procedure call.
Re: Not able to connect to Oracle(12c) to progress using Oracle database gateway(12c) for ODBC [message #671824 is a reply to message #671786] Thu, 20 September 2018 06:47 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
You posted same to my blog yesterday.
We'll deal with it here instead, as this will get more eyeballs on your issue. For the sake of the other participants, here is my response that I posted on the blog:

Quote:


Ok, I have several questions.

First, if your gateway is installed on Server 2, then you have no need of gateway related entries in the listener.ora on Server 1. Why have you created initGW_TO_PROG.ora on server 1? Why do you have a SID_DESC for GW_TO_PROG in the listener.ora on server 1? All that does is confuse the analysis of the problem.

Second, why is the listener on server 2 using port 1522 instead of the default of 1521?

Third, the error message "Unable to retrieve text of NETWORK/NCR message 65535" was particularly curious, so I googled ORA-65535 and found this:

ORA-65535: Oracle client cannot handle error code exceeding 65535. Actual error code and message follow:\n
Cause: Oracle server encountered an error whose error code exceeded 65535. Older Oracle clients (prior to version 12) cannot handle error codes larger than 65535.
Action: Actual Oracle error code and error message are a part of the error message for ORA-65535. Upgrade Oracle client to version 12 client libraries or higher.
(see https://docs.oracle.com/database/121/ERRMG/ORA-60001.htm#ERRMG-GUID-9B78A028-D760-4810-9CFC-9013FBD1FCC9).

Fourth, you state "SP2 is a DB link pointing to GW_TO_PROG service name.". No, "SP2" is not a db link, it is a stored procedure. There is a reference to a db link somewhere in the stored procedure. You've shown me all your config files but you haven't shown the actual code that initiates the call. If I were sitting next to you I'd want to see both the actual sql statement that references the db-link and the properties of the db link, as shown by 'SELECT * FROM DBA_DB_LINKS
WHERE DB_LINK = ''

The path from the sql statement making the call, to the target non-oracle database is a very clear an precise chain, as described in the article. To debug any issues, we simply follow each link in the chain. That begins with the SQL statement in your stored procedure.

Also, what version of OTG for ODBC is installed on Server2?
BTW, as of yesterday evening I relocated most of the original content of my blog to my older site edstevensdba.wordpress.com. I am close to retirement and have lost interest in maintaining the blog (and incurring the expense of self-hosting), so have moved content back to a free platform where it can remain for future reference.
Previous Topic: Connections to this server version are no longer supported
Next Topic: Listener not connecting to database
Goto Forum:
  


Current Time: Thu Mar 28 17:12:49 CDT 2024