Toad World® Forums

Besides initDBLinkName and tnsnames.ora and listener.ora and ODBC configuration what do I need to configure in a windows server to set the DBLink on Toad

Besides initDBLinkName and tnsnames.ora and listener.ora files and ODBC configuration what do I need to configure in a windows server to set the DBLink on Toad? The credentials are working on ODBC configuration

Good Morning Javierabo,

The user account, which requires to create the DB Link, needs to have CREATE DATABASE LINK privileges granted by the SYS user.

Cheers,
Norm. [TeamT]

Hi Norm thanks for the heads-up, the problem is with the tnsnames.ora file, I used to work on a similar project with Oracle 12c and windows server 2012, now I'm using Oracle 19c and windows server 2019, I already configure the tnsnames.ora, the listener.ora, the initDBLinkName.ora, and the ODBC where the credentials were succesfull, but in toad still show me the "ora-12154" error, I already have everything like in the old project but I'm having trouble with the new versions, really appreciate any help

I have never heard of any need to edit an initDBLinkName.ora or ODBC with DB Links. And If you make the DB Link so that it includes the TNS Info, then you don't need to do anything with TNSNames.ora. Like this:

CREATE DATABASE LINK db_link_name
 CONNECT TO user
 IDENTIFIED BY password
 USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=name_of_server)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=name_of_database)))';

If you make it like that, then the only thing other thing you need to make sure is that the database where the DB Link lives has network access to the target database.

edit: Oops I did "reply to Norm", I meant "reply to Javierabo"

What John said!

I've never heard of that file either and when I Googled for it, the only two hits were on this thread!

Where I disagree with John is in using the full tnsnames configuration in the creation of the link. It has caused problems in the past where a production database was cloned to test/QA etc, and the database links were all still pointing at production. Not good when testers are testing and updating production! Even when there are SOPs defining how to clone databases between environments, some "fat fingered DBAs" think they don't need to follow procedures. (No, it wasn't me - I had to fix it!)

For best results, use a generic name for the link, and a tnsnames alias for the remote database. In each of the dev, qa, sit, uat and production tnsnames files, the same alias point to the appropriate database for that environment. No changes required when cloning between envionments.

Getting back to the original problem, I'm not sure why a link is required when creating an ODBC connection. As far as I recall, you don't need a link, only the tnsnames alias to create the ODBC thingy. (Technical term!)

What exactly are you attempting to do please?

Cheers,
Norm. [TeamT]

I find using that info in the DBLink definition handy for a short-term DB Link when I don't want to visit the server to configure TNSNames.

But I agree with you Norm, for production work (or if TNSNames is already set up on the server) then it's better to use tnsnames.ora.

If you are going Oracle to Oracle, all you need is a TNSNAMES entry that points to the remote database.

If you are using Heterogeneous Services to connect to SQL Server, Javierabo is correct. You need

  1. ODBC entry to point to SQL Server
  2. Tnsnames entry that references SID of ODBC
  3. InitXXXX.ora file in hs\admin folder where XXXX is the sid of the ODBC connection, and inside the file it references the SID of the SQL Server
  4. Entry in the listener.ora file that references program DG4odbc and SQL Server SID

I use a lot of these (25 on one server). They are very quirky to set up. I have found at times that I have to reboot the server (restarting DB doesn't do it) to have a new entry recognized. Once set up, they have been rock solid. We have critical systems exchanging data between Oracle and SQL Server using this method. Heterogeneous Services are included in the basic licensing, there is no additional charge for using it.

It makes life really simple to be able to execute statements against SQL Server (select * from dual@hssql127.world). One thing to remember , Oracle by default translates everything to upper case, SQL Server does not. That means that multi-case table names and column names must be enclosed in double quotes. I have found it simpler to create a view on SQL Server where the view name and the column names are all in upper case. Then I don't have to be concerned with case.

ODBC Connection
HSSQL077

TNS Entry:
HSSQL077.WORLD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = LOCALHOST)(PORT = 1521))
(CONNECT_DATA =
(SID = HSSQL077)
)
(HS = OK)
)
Listener.ora Entry
(SID_DESC =
(GLOBAL_NAME = HSSQL077.world)
(ORACLE_HOME = d:\Oracle\19)
(PROGRAM = dg4odbc)
(SID_NAME = HSSQL077)
)

initHSSQL077.ora Entry:
HS_FDS_CONNECT_INFO = HSSQL077

1 Like

Hi Brian,

Thanks a lot for your answer, is not SQL Server but Informix but I think it's the same (to a non Oracle DB), I already have exactly what you mentioned and shared, actually I used to work on a similar project (Oracle 12 and Windows Server 2012), this is kind of an upgrade (Oracle 19 and Windows Server 2019) and my concern is if there´s some restrictions with this new versions

Not sure what could be wrong

ODBC.ini
CMS70

TNSnames:
CMS70 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SERVERNAME)(PORT = 1521))
(CONNECT_DATA =
(SID = CMS70)
)
(HS = OK)
)

Listener.ora
(SID_DESC =
(ORACLE_HOME = D:\app\oracle\db_home)
(SID_NAME = CMS70)
(PROGRAM = dg4odbc)
)

initCMS70.ora
HS_FDS_CONNECT_INFO = CMS70

...
Not sure what could be wrong

ODBC.ini
CMS70

I assume this specifies the tnsnames alias to be used when connecting?

TNSnames:
CMS70 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SERVERNAME)(PORT = 1521))
(CONNECT_DATA =
(SID = CMS70)
)
(HS = OK)
)

Maybe it's the formatting in my (received) email, but:

In tnsnames.ora, you never have the opening brackets in column 1. There
is/was/might still be a bug in that it would not work (or work
correctly) as an alias in this case. I am not sure it it still is a bug,
so best avoided to be sure.

I wonder if "SID = " has finally been deprecated? It was supposed to be
being removed since 9i. Perhaps it will work if you change "SID =" to
"SERVICE_NAME ="? Just a thought. The default service name is the same
as the old SID, so that should be all that's required.

Other than that, I see nothing amiss in the tnsnames entry.

Listener.ora
(SID_DESC =
(ORACLE_HOME = D:\app\oracle\db_home)
(SID_NAME = CMS70)
(PROGRAM = dg4odbc)
)

initCMS70.ora
HS_FDS_CONNECT_INFO = CMS70

According to the docs, this entry should be
"HS_FDS_CONNECT_INFO=host_name:port_number/server_name/database_name"

Where:

Host_name is the host name or IP address of the machine hosting the
Informix database.

Port_number is the port number of the Informix database server.

Server_name specify the Informix database server name.

Database_name is the Informix database name.

Plus, the filename for this should be:

$ORACLE_HOME/GATEWAY_SID_NAME/admin/initGATEWAY_SID_NAME.ora

$ORACLE_HOME is where the Oracle Gateway software is installed.
GATEWAY_SID_NAME is the, ahem, gateway SID name.

The Oracle docs for a connection to Informix is at


if required.

Hi Brian, Thanks a lot for your quick response.

Let me review what you sent, I already test with the service_name but let me try again, at the moment I'm getting this error:

ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: 2 lines preceding CMS70