In initdg4msql.ora you have -
but do you get an error with the format -
I assume in the file on your system you have put in the actual IP address and port number ?
If you still have problems could you set up gateway 255 level tracing -
and selelct from a new SQLPLUS session.
Either post the trace to somewhere off site we can view it or post any obvious looking errors in the thread if the complete file is too large to post here.
This note available in My Oracle Support has more setup details -
How to Configure DG4MSQL (Database Gateway for MS SQL Server) on Windows 32bit post install (Doc ID 466267.1)
ORA-28545 is a configuration issue - what has changes as above it was already working?
You also only mentioned that the listener service summary shows a service, but to diagnose I need the full listener status output. Could you please post it (lsnrctl status)?
please find the lsnrctl status output below:
Connessione a (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.176.111.127)(PORT=152
STATO del LISTENER
Versione TNSLSNR for 64-bit Windows: Version 184.108.40.206.0 - Produ
Data di inizio 08-NOV-2012 06:08:42
Tempo di attivitÓ 0 giorni 0 ore 0 min. 9 sec.
Livello trace off
Sicurezza ON: Local OS Authentication
File di parametri listenerC:\product\11.2.0\tg_1\network\admin\listener.ora
File di log listener C:\product\11.2.0\tg_1\diag\tnslsnr\w2k8r2it-lk03\list
Summary table degli endpoint di ascolto...
Summary table dei servizi...
Il servizio "dg4msql" ha 1 istanze.
L'istanza "dg4msql", stato UNKNOWN, ha 1 handler per questo servizio...
Il comando Þ stato eseguito
Above changes couldn't help me in resolving the issue.
Here is it
Oracle Corporation --- GIOVED╠ NOV 08 2012 06:51:20.156
Heterogeneous Agent Release 220.127.116.11.0 - 64bit Production Built with
Oracle Database Gateway for MSSQL
The tns alias dg4msql you posted above as your tnsnames.ora entry, is that the tns entry from the gateway or from the database home?
The reason why I'm asking is, that the tnsping output you posted earlier:
output of tnsping
Parameter file used:
C: \ product \ 11.2.0 \ tg_1 \ network \ admin \ sqlnet.ora
was used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.176.111.127) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = dg4msql)) (HS = OK))
OK (20 msec)
indicates you tested the tnsping from the gateway home (C: \ product \ 11.2.0 \ tg_1). But as the Oracle database is calling the gateway listener, you have to test the tnsping from the Oracle database home. Could you please clarify if C: \ product \ 11.2.0 \ tg_1 also contains an Oracle database and if not, please call tnsping from the <Oracle database home>/bin directory.
There is only gateway installed on that machine. There is no oracle db installed.
My oracle_home points to the oracle gateway home which is C: \ product \ 11.2.0 \ tg_1 \
so, tnsping which i am using is from gateway home only
You're Oracle database can't connect to the gateway listener, so you need to check the connection from the Oracle database that causes the ORA-28545 error message when you try to use the gtaeway. On the machine with the Oracle database, please check with tnsping the gateway connectivity.
On the machine where oracle client is isntalled, i am able to connect to sql db through gateway. All the queries are getting executed. But, on Gateway machine, it is throwing error.
Is there anything specific that i should do on gateway machine?
Please explain your last comment.
The Oracle gateway can never be used directly - it always needs an Oracle database.
Let me copy the posting from above:
SQL*Plus: Release 18.104.22.168.0 Production on Mer Nov 7 13:11:35 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> conn system/Manager1@obiee
SQL> create public database link dblink connect to "sa" identified by "Manager1"
Creato database link.
SQL> select count(*) from "prefer"@dblink;
select count(*) from "prefer"@dblink
ERRORE alla riga 1:
ORA-28545: errore diagnosticato da Net8 durante la connessione a un agente
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: precedente 2 lines da DBLINK
So you're here using a certain Oracle database, where is this database located (machine and Oracle_Home dircetory). In the OH of this Oracle database there's a network/admin directory and in this directory a tnsnames.ora file must exist which contains the tns alias dg4msql which refers to the gateway.
So from this certain database home, please execute the tnsping using the gateway alias you defined in this database home for the gateway connection.