OraFAQ Forum: Networking and Gateways » Not able to connect to Oracle(12c) to progress using Oracle database gateway(12c) for ODBC

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Not able to connect to Oracle(12c) to progress using Oracle database gateway(12c) for ODBC [message #671786]

Wed, 19 September 2018 08:55

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

Report message to a moderator

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

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

Report message to a moderator

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

John Watson
Messages: 8976
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.

Report message to a moderator

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

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.

Report message to a moderator

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

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

So upgrade your Oracle client software. As I said.

Report message to a moderator

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

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

Report message to a moderator

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

John Watson
Messages: 8976
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.

Report message to a moderator

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

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

Report message to a moderator

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

John Watson
Messages: 8976
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?

Report message to a moderator

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

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

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

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.

Report message to a moderator

Previous Topic:	Connections to this server version are no longer supported
Next Topic:	Listener not connecting to database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 17:21:43 CDT 2025