Connect to remote Oracle database

General discussion forum about all databases other than SqlBase.
Ben187

Connect to remote Oracle database

Post by Ben187 » 22 Sep 2015, 10:42

I have the following problem:
A CRM program is running on a client PC. On the same PC, an Oracle Server 10g is running.
On the oracle server environment called "XE" the database for the CRM software is running.
In SQL.ini it looks like this:

[win32client]
clientname=VALusr32
COUNTRY=GERMANY
setzerolengthstringstonull=On

[win32client.dll]
comdll=sqlora32
comdll=sqlodb32
comdll=sqlws32

[win32client.ws32]
serverpath=bassrv5,192.168.4.79/statsoft,valsoft,helbak,mwst,valtest,mtupr,valdb

[win32client.wsspx]

[win32client.spx32]

[win32client.apipe]

[sqltalk]
COUNTRY=GERMANY

[sqlapiw]
COUNTRY=GERMANY


[odbcrtr]

remotedbname=rbsc,dsn=rbsc,srvr=blah2;db=robe;
remotedbname=rbstat,dsn=valstat,srvr=blah2; db=robe;


odbctrace=off
odbctracefile=odbc.log
longbuffer=32000
enablemultipleconnections=on

[oragtwy]

remotedbname=MTUNEU,@XE

;Bytes für BLOBS z.B 1,2 MB
longbuffer=1200000

;substitute=@,
substitute=SYSSQL.,

fetchrow=20


When starting the CRM program, everything is fine. The program finds the database and connects
properly. Also I can work with the software.
Now I need to migrate the database to a different machine, but I do not know how to change
settings in sql.ini for the software to find the right server with the database.
Can anyone tell me, what I need to change or which settings I need to make in order
for the software to find the database on a different machine than itself (localhost)?

Jeff Luther
Site Admin
Site Admin
United States of America
Posts: 2370
Joined: 04 Mar 2017, 18:34
Location: Palm Springs, California

Re: Connect to remote Oracle database

Post by Jeff Luther » 22 Sep 2015, 19:20

Generally, the TD Books (in PDF format) that ship with the product is useful for understanding DB connecting issues. (There is a separate PDF for SQLBase; CCOD.PDF covers non-SQLBase DB connectivity.) The TD book called: CCOD.PDF

Here's where you can download a "Books Online" zip that contains the TD books: http://www.guptatechnologies.com/Servic ... loads.aspx

In your case, the Oracle DB "MTUNEU" is what you are connecting to at the moment, as shown in your INI file. Now look at your local TNSNAMES.ORA file on your machine. That file is what's used by TD at runtime to find where "MTUNEU" is located on your network. The .ORA file is part of the Oracle client installation.

If the DB location changes, TNSNAMES.ORA needs to be updated. So, modify that file to change where MTUNEU will will be located. For what you want, your SQL.INI should not need to change at all, unless the DB name is changed as well.

FYI, I just see in CCOD.PDF for Oracle that the doc says:
Refer to the Oracle documentation for more information about CONFIG.ORA, TNSNAMES.ORA, and ORACLE.INI and how to set up these files.
So you will want to do that. (I recall only needing to modify TNSNAMES.ORA when a new or changed Oracle DB location and/or name changed.)

As I always mention, BE SURE TO save backups of your ORA and INI files before you make any changes!
Jeff Luther @ PC Design
Palm Springs, California

wardies
Great Britain
Posts: 86
Joined: 21 Mar 2017, 10:44
Location: UK

Re: Connect to remote Oracle database

Post by wardies » 23 Sep 2015, 15:11

Just adding to Jeff's response with a quick tip which will work in the majority of cases (but not all) if you already know the TNS entry for the remote service:

On the client PC, do Start -> All Programs -> Oracle Database Express Edition -> Run SQL Command Line

Type:

Code: Select all

host notepad ..\network\admin\tnsnames.ora
In Notepad, do not delete or modify the XE entry, nor change its name from XE to anything else (as this will surely break more things than it fixes)!!! If unsure what you're doing here, make a backup and/or refer to the Oracle manual.

Add the relevant remote entry at the bottom of the file, ensuring you separate with one or more new lines. Change the first part of the new piece of text (the service name*) to something unique (e.g. ORA2). Save and exit.

Edit your client's SQL.INI and change the line remotedbname=MTUNEU,@XE to read:

Code: Select all

remotedbname=MTUNEU,@ORA2
*If you're lost and don't know what the remote PC's Oracle service Net Service entry should look like, either try copying the XE entry above, pasting below and changing the details (assuming it's XE on the remote PC too) or on the remote PC try issuing the following from a CMD prompt:

Code: Select all

tnsping SERVICE_NAME
(e.g. tnsping xe)

This tells you the location of the folder where the sqlnet.ora lives on that machine (you'll need this if you want to find the tnsnames.ora; it lives in the same place). You can copy the relevant TNS Entry from that machine's TNSNames.ora and append it to the contents of the other Tnsnames.ora on your client pc (again, giving it a unique service name).

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests