Load Balancing in odbc.ini and does the oracle enterprise

[highpoint]

Hi,

Currently we are using ODBC stages to connect to our oracle source and oracle target database.

My understanding is ODBC stages will use odbc.ini and will not use tnsnames.ora

I would like to use the load balancing the way it is used in tnsnames.ora

Can i do load balancing using odbc.ini file ??

or will i have to use oracle enterprise stage for load balancing ??

I see in odbc.ini entries like:
HostName=XXX
AlternateServers=YYY

The load will first go to Host XXX and if the XXX is in failed state then the load will go to YYY Host.

I want the process to go to node that is least busy.

I have read other threads, but didn't get much as i am new to RAC concept.

Would appreciate clear lucid reply.

[chulett]

My understanding is ODBC stages will use odbc.ini and will not use tnsnames.ora

Not true, it will need both for Oracle.

Would appreciate clear lucid reply.

'Lucid' as in rational and sane? As opposed to our normal mad rantings?

[highpoint]

So, odbc stage uses both odbc.ini and tnsnames.ora files.
If yes, could you please explain, which is read first and order of execution, and how to implement load balancing using odbc.ini

[ray.wurlod]

The .odbc.ini file is read first, to determine which ODBC driver to load into memory. It is the ODBC driver for Oracle that needs to consult tnsnames.ora.

[chulett]

Stick with letting Oracle do your 'load balancing' for you.

[highpoint]

The .odbc.ini file is read first, to determine which ODBC driver to load into memory. It is the ODBC driver for Oracle that needs to consult tnsnames.ora.

All the connection details like
Hostname, Alternateservers, portnumber that is available in tnsnames.ora is available in odbc.ini.

Then why does it need tnsnames.ora??

And also using oracle enterprise stage instead of odbc stage will not require us to maintain odbc.ini file, as oracle enterprise stage uses tnsnames.ora only and not odbc.ini.
And we can use load balancing that is mentioned in tnsnames.ora
Am i correct on this??

Appreciate reply

[chulett]

My understanding is that it needs it because it needs it, that's just the way it works. And while you may have documented things like the hostname (etc) in the odbc file I don't believe they are actually used. Some of this may depend on if you are using the 'thick' or 'thin' drivers as well, I'm honestly not sure. Probably best to go to the horse's mouth, so to speak, and ask your official support provider to be certain how everything interacts.

[highpoint]

Appreciate experts advise on the above questions.

[chulett]

Rather than mine, it would seem.

Why not run some simple tests yourself? One example - put bad values in the odbc configuration file (things like hostname that exist in tnsnames) and see if the connection still works. If it does then you'll know it got what it needed from the tnsnames.ora entry.

From what I recall, we left ours empty as it took what it needed either from the stage itself (credentials) or from the tnsnames.ora entry (host, sid, port, etc).

[jwiles]

Being that the ODBC drivers are supplied by DataDirect, it certainly makes sense that the information might exist in their documentation and can easily be found with a little bit of effort. Matter of fact, here 'tis an example:

Server Name
Attribute
ServerName (SRVR)
Description
Specifies a net service name that exists in the TNSNAMES.ORA file. The corresponding net service name entry in the TNSNAMES.ORA file is used to obtain Host, Port Number, and Service Name or SID information.
NOTE: This option is mutually exclusive with the Host, Port Number, SID, and Service Name options.
Valid Values
server_name
where server_name is a net service name in the TNSNAMES.ORA file.
Default
None

And, just for kicks:

Alternate Servers
Attribute
AlternateServers (ASVR)
Description
A list of alternate database servers to which the driver tries to connect if the primary database server is unavailable. Specifying a value for this option enables connection failover for the driver. The value you specify must be in the form of a string that defines the physical location of each alternate server. All of the other required connection information for each alternate server is the same as what is defined for the primary server connection.
Valid Values
(HostName=hostvalue:PortNumber=portvalue:{SID=sidvalue | ServiceName=servicevalue}[, . . .])
You must specify the host name, port number, and either the SID or service name of each alternate server.
Example
The following Alternate Servers value defines two alternate database servers for connection failover:
(HostName=AccountingOracleServer:PortNumber=1521:
SID=Accounting,HostName=255.201.11.24:PortNumber=1522:
ServiceName=ABackup.NA.MyCompany)
Default
None

Oh, what the hay:

Load Balancing
Attribute
LoadBalancing (LB)
Description
Determines whether the driver uses client load balancing in its attempts to connect to the database servers (primary and alternate). You can specify one or multiple alternate servers by setting the Alternate Servers option.
Valid Values
0 | 1
If set to 1 (Enabled), the driver uses client load balancing and attempts to connect to the database servers (primary and alternate servers) in random order.
If set to 0 (Disabled), the driver does not use client load balancing and connects to each server based on their sequential order (primary server first, then, alternate servers in the order they are specified).
NOTE: This option has no effect unless alternate servers are defined for the Alternate Servers connection option.
Default
0 (Disabled)

You're certainly welcome to go browse through the document yourself at their website. I will say this: It's likely for their current shipping commercial version, which is probably newer than those included with IS and therefore all bets are off as to which options work and which options don't. As the "non-expert" Craig has suggested, test, try things out, experiment. When you have eliminated the impossible...

Edit: Documentation for the branded odbc drivers (included with IS) is found in the $DSHOME/../branded_odbc/docs folder

Regards,

[chulett]

Even with all that, in your shoes I would just stick with the devil you know - Oracle load balancing, especially if that's already working for you.