Oracle TNS problems on failover

[John Eaton]

I've read a few threads, but have not found one to address this issue. Hopefully someone out there can help.
I have configured a 2 node cluster between a pair of rp74xx's, on which there are 3 DB's, and 3 applications. NodeA has 1 DB and 2 apps, nodeB has 2 DBs and 1 App.
If we failover a DB from one node to the other, it's app can no longer talk to the DB. Same deal if we failover the app.
We've gone round and round with the DBA team over this and all I seem to get is a song and dance about how TNS works differently at 9i. Since I'm not a DBA, I'm sure I can't describe the TNS configuration correctly on my own. What I do know is that on 7.3.4 we used the same TNS entry regardless of where the DB was running (via floating IP). We still have the floating IP, but TNS seems to be different if the DB is not local. The DBA's can only tell us it's different for 9i, but until it works, it's broken. I can't even complete my SG set up and testing until we resolve this.
The only soulution the DBA's have offered so far is that we have to change our application's method of connecting to the DB when either fails over, and that requires stopping the app, changing the env variables, and restarting. Far from seamless failover.
Can anyone cut through the DBA mumbo-jumbo we seem to be getting and describe how one can connect to the DB regardless of where it is running?
Thanks!

[melvyn burnard]

Well I would go back and discuss it again with them.
In the listener config file you specfiy the hostname/ip that is used for the listener
I have just recently installed and configured a n Oracle 9.2.0 installation and the listener is configured to use the floating hostname/ip address and works fine. And I am not an Oracle DBA....

[Gary L. Paveza, Jr.]

We have a 2 node cluster, one package on each node. Our production node has 8 databases and our test package has 74 (all Oracle 9i). We have no trouble with TNS at all - the entries are defined as using the virtual hostname. We do have applications which connect to the DBs that need to be restarted (WebSphere), but whether that's a configuration issue or an application issue I don't know. We have other applications which work just fine. One of the biggest keys to to make sure your application is using the virtual name, and not the physical name.

[Duncan Edmonstone]

Hi John,

I find that the easiest thing to do in these situations is actually run a seperate listener on a seperate port for each database (one on the usual 1521, one on 1522 etc...), then use seperate listener startup scripts pointing to seperate TNS_ADMIN locations on the shared disk associated with the package in question... This way the listener is started/stopped along with the database.

Works for me.

Duncan

[Gary L. Paveza, Jr.]

Hmm...good point above. Question - where are your oracle binaries stored? Are they in a filesystem that is part of the package (so that it moves along with the databases)? Does your listener get started by the package or is it outside the package. In my example above, the oracle software moves with the package and all listeners are started by the package.

[John Eaton]

Thanks for the various points.
I'd sooner have needles in my eyes than talk to the dba's again, before I learn more. They are a painful group to talk to.
We do use a seperate listener port for each DB, and none are actually under SG control yet, and they all have floating IP's, which are staticly assigned for now.
I have to finish before the OK to put it 'into production' is given.
It seems that the issue is with ORACLE_SID, TWO_TASK and TNS_ADMIN. From my basic undestanding there is a different TNS file for each ORACLE_SID, and TNS_ADMIN points to that TNS file location. This suggests that if you have 4 different DBs on a server, there are 4 different TNS names files, each with some, or all?? the applicable entries? TWO_TASK has been some sort of 'cure-all' in the old 7.3.4 environment. I really don't undestand it's purpose.
Because the app can DB can failover independantly, there are 4 possible combinations for the 2 on 2 servers. If they are both on the same one, it's OK, but when they are on 2 different ones, it's no-go.
Maybe it's time I buy a book and study it myself so I can understand how it's supposed to work....then try to figure out if they've done it that way?

[Gary L. Paveza, Jr.]

There isn't a need to have separate TNS_ADMIN files for different databases. You could use one file for all databases - regardless of what host they are running on.

[John Eaton]

Gary,
That's our understanding, too, but the DBAs insist that this is 'the new way'. Is it possibly related to securing access? One idea that my cubemate (he's the application administrator) suggested is that it keeps a user from 'leap-frogging' from 1 DB to another, and another, etc.

[Gary L. Paveza, Jr.]

I've never heard of that. I suppose it's possible that you can secure the files so that only certain people can read them, and thus set the variable to them, but what's to prevent a user from creating their own file and pointing TNS_ADMIN to it? Sounds like security through obscurity - which is destined to fail.

[TwoProc]

Well, one fixed I used - is this - when the package fails over - just run the "hostname" command before starting up all the Oracle stuff. So, I've failed nodeA over to nodeB -
just run "hostname nodeA" on nodeB - then run all of your database startup commands. Or if you just need it for the tnslistener - just prior to that part. Not pretty - but it's how I fixed mine, and I can't seem to come up with anything particularly wrong with it - since it's just in use during the failover...

[Christopher Caldwell]

We've got floating IPs on 9i. There's no problems there.

LAN to LAN (single box) you get stateful failover (i.e. connected applications don't need to reconnect). If you fail host to host, you don't get stateful failover; the connected applications will have to reconnect.

If your applications don't automatically re-connect, you will have to rewrite them to re-connect automatically on failover.