Hello
Hoping someone can help me, or point me in the right direction.
I have installed the latest winx64_12102_client/gateway on a windows server, running Windows Server 2016 and MSSQL 2014 R2, my tnsnames looks as follows:
DEVEL12 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.26.32)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DEVEL12)
)
)
dg4msql =
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST = mag-sagetest01)
(PORT = 1522))
(CONNECT_DATA=(SID=DG4MSQL))
(HS=OK)
)
And my listener file, which is in the same directory as my tnsnames files looks as follows:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mag-sagetest01)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=DG4MSQL)
(ORACLE_HOME=C:\oracle\product\12.1.0\tghome_1)
(PROGRAM=dg4msql)
)
)
My initdg4msql config file looks as follows:
HS_FDS_CONNECT_INFO=mag-sagetest01/X3MAG/X3MAG
HS_FDS_TRACE_LEVEL=255
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_FDS_FETCH_ROWS=1
I can tnsping to dg4msql with no issues:
C:\Windows\system32>tnsping dg4msql
TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 16-JUL-2018 14:20:34
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
C:\oracle\product\12.1.0\client_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST = mag-sagetest01) (PORT = 1522)) (CONNECT_DATA=(SID=DG4MSQL)) (HS=OK))
OK (10 msec)
And the listener seems to be up and running OK:
C:\Windows\system32>lsnrctl status
LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 16-JUL-2018 14:21:06
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mag-sagetest01)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
Start Date 16-JUL-2018 13:46:01
Uptime 0 days 0 hr. 35 min. 9 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\oracle\product\12.1.0\client_1\network\admin\listener.ora
Listener Log File C:\oracle\diag\tnslsnr\mag-sagetest01\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mag-sagetest01.maginus.local)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
Services Summary...
Service "DG4MSQL" has 1 instance(s).
Instance "DG4MSQL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
And my DB link has been created as follows on my oracle database:
CREATE DATABASE LINK x3mag
CONNECT TO "sa" IDENTIFIED BY "password" USING 'DG4MSQL';
However, when I SQL Plus to my Oracle database (Devel12 referenced above, running Oracle 12) and then try to query the MS SQL Database I have linked, I get the following error:
SQL> select * from x3.abank@x3mag;
select * from x3.abank@x3mag
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from X3MAG
I have done the usual google searching, and tried pretty much all things, as far as I can tell then my config files all appear to be correct but I can't for the life of me get it to talk...
Hoping someone can help!
Cheers
Andy