CODEWITHSUNDEEP
oracle-databaseodbclistenerdblinktnsnames
dba
dba

Reputation: 1

Error in creating a DBLINK Oracle to SQL Server

I'am create DBLINK the Oracle(11gR2)(Windows Server 2008) to SQL Server(Windows 10).

I have Oracle Gateway installed.

I created a font of data in ODBC Data Source Administrator and a connection is Ok. His name is Integra.

I altered the files listener.ora, tnsnames.ora, sqlnet.ora e initIntegra.ora.

Follow my settings:

Server Oracle no IP: 192.168.1.22

Server SQLServer no Ip: 192.168.1.18

Listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
(SID_DESC =
  (SID_NAME = CLRExtProc)
  (ORACLE_HOME = D:\app\product\11.2.0\dbhome_1)
  (PROGRAM = extproc)
  (ENVS = "EXTPROC_DLLS=ONLY:D:\app\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
  (SID_NAME = CLRExtProc)
  (ORACLE_HOME = D:\app\product\11.2.0\dbhome_1)
  (PROGRAM = extproc)
  (ENVS = "EXTPROC_DLLS=ONLY:D:\app\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
  (SID_NAME = )
  (ORACLE_HOME = D:\app\product\11.2.0\dbhome_1)
  (PROGRAM = dg4odbc)
)
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Serverlab3)(PORT = 1521))
    )
  )
ADR_BASE_LISTENER = D:\app

tnsnames.ora

Integra =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = Integra)
    )
  )

initIntegra.ora

# 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= Integra
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

sqlnet.ora

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES = (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Creating DBLINK

CREATE DATABASE LINK INTEGRA CONNECT TO "sa" IDENTIFIED BY "senha" USING 'Integra';

select * from tabela@INTEGRA;

SQL Error [28546] [99999]: ORA-28546: connection initialization failed, probable Net8 admin error. ORA-02063: preceding line from INTEGRA*

Note: the name of my data source in the ODBC Administrator is Integra. And the connection test by the ODBC windows administrator works perfectly.

One Question: In TNSNAMES the HOST must be LOCALHOST and PORT 1521?

Thanks.

Upvotes: -2

Views: 657

Answers (1)

EdStevens
EdStevens

Reputation: 3872

I see too many problems to list in a comment. Some having to do with your problem, and some simply an indication of carelessness or not understanding.

Is your gateway installed on the Oracle machine or the MSSQL machine, or some other machine? I ask because it is quite possible to install it anywhere. In my shop, where I have multiple oracle links pointing to multiple MSSQL databases, I have my gateway on a 'neutral' machine. Like I say, it doesn't matter, be we need to keep straight were each link of the communications path is going.

  1. In your listener.ora's SID_LIST section, you have two identical entries for 'SID_NAME = CLRExtProc'

  2. In your listener.ora's SID_LIST section, you have no SID_NAME "(SID_NAME = )" for what appears to be the gateway entry.

  3. In listener.ora, you configured the listener with (HOST=Serverlab3). Does Serverlab3 resolve to IP address?

  4. Is the tnsnames.ora in ORACLE_HOME/network/admin of the database with the db link?

  5. The tnsnames entry you show has "(HOST = localhost)". This means the request will loop back to the requesting machine and goes to address 127.0.0.1. You probably should correct that to specify the actual host name or IP address of the listener.

  6. In your tnsames you specified connection by service name. When using a gateway, this is one case where you should specify SID instead.

You asked "One Question: In TNSNAMES the HOST must be LOCALHOST and PORT 1521". The HOST must specify the host where the listener and gateway are running. The PORT must be the port that listener is configured on, and there is very very rarely a good reason for that to be anything other than 1521.

I have a more lengthy write-up, with pictures, at https://edstevensdba.wordpress.com/2018/09/19/the-oracle-transparent-gateway/

Upvotes: 0

Related Questions