Using the RAC Scan IP for a Non RAC instance

I found a really odd config on a server today.

I found a situation where there's a 2 node RAC cluster built.

A new database was created on node 2 as a standard instance.

When whoever built the TNSNAMES.ORA file built it, the HOST part of the TNSNAMES entry used the SCAN host name instead of the host name of node 2.

The service of the Application on the app server kept stopping.  The vendor says that application service is finicky and even a fraction of a second of DB disconnection will cause the app service to fall over.

I would assume that if you've setup a tnsnames alias using the RAC SCAN alias that if the engine decided to point you to node 1 and there's no node 1 configured that it might generate a brief database "not there" before falling back to node 2.

I'm just running here on a gut instinct that this could be the problem, wondering if anyone has any real life experience to back this thought up ?
LVL 23
Steve WalesSenior Database AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark GeerlingsDatabase AdministratorCommented:
No, I can't say that I have seen this exactly.  I do know that Oracle offers "RAC 1-node" as a way to have a second RAC node defined, but not active.  This makes failover to a second node faster than a non-RAC failover (at least according to the marketing claims).  And, this is cheaper than paying for database licenses on two nodes, since you only have to pay for the Oracle database license on one server this way.  But, you do have to pay the upcharge for RAC on the one active node.
Steve WalesSenior Database AdministratorAuthor Commented:
I think you may have misunderstood the question.

The database in question is not a "RAC 1-node".  It's a standard, non RAC instance, that just happens to sit on a server that's also configured as a part of the RAC node.

When the tnsnames was set up, whoever set it up used the SCAN name in the HOST= part of the tnsnames alias instead of just the server name.

I'm wondering if that causes confusion.  Since the application service is so fragile (I love it when vendors tell you how finicky their stuff is), I was wondering if there's any thoughts on whether using the SCAN name on a standard non RAC instance might cause issues with temporary "database not available" type of scenarios when the RAC stuff tries to send a connection request to a node where there are no instance processes.

Does that make sense ?
Is the non-rac instance registered with the cluster?

If not, then it would make sense that it would get sporadic success/failure as the connection rotates through remote (fail) and local (success) listeners.

If that instance is in the ocr, then connections to the scan listener should direct to only the correct node.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mark GeerlingsDatabase AdministratorCommented:
OK, so you have two Oracle database instances on this server, one part of a RAC cluster, and one stand-alone instance?  Is that correct?  And you have two separate Oracle_home directories, with a different tnsnames.ora file for each instance?

Are you sure that the tnsnames.ora file on this server gets used much (if at all)?  Where is the application server software installed?  Is that also on this database server?  Or, is that on a separate server?  If that is on a separate server, it is the tnsnames.ora there that is used to connect to the database, and not the tnsnames.ora file on the database server.
Steve WalesSenior Database AdministratorAuthor Commented:
Mark - yes that is the setup, except both are running out of the same ORACLE_HOME.

ststuber - I don't know if it's registered with the cluster, I would assume not.  This seems to answer the question though, it is what I thought it was,

This is not my machine, it belongs to one of my customers, and they asked me to have a look at it after continued issues - the scan vs local host thing stood out to me as a possible cause of the problem.
Mark GeerlingsDatabase AdministratorCommented:
If there is just one Oracle_home, then there is just one tnsnames.ora file on the database server.  But that does't answer my question about where the application server software is installed.  Where is that?  (On this same server, or on a different server?)
Steve WalesSenior Database AdministratorAuthor Commented:
Oh that is on a different server.

The tnsnames I was checking was in the client home in the separate app server.
Mark GeerlingsDatabase AdministratorCommented:
That's clearer.  Yes, I agree with your assessment that a tnsnames.ora file on a client for the stand-alone database should *NOT* be pointing to the scan listener for the RAC DB.  This should be easy to test a change to  (and to save a copy of the tnsnames.ora file there first just in case you need to go back to it.)  

I would start by adding an entry with an alias different from what the application uses, but with the hostname, port and SID (or service_name)  that you believe is correct.  You can test that outside of the app, to confirm that it works at least for "tnsping" and preferably also with SQL*Plus.  Then if that works, change the alias with the incorrect entry to something different (or remove that incorrect entry entirely) and change the alias on your new entry to what the application expects the alias to be.
I'm not sure why you want to avoid the scan listener.

That listener doesn't "belong" to the rac db,  it's an entity of the cluster not the database.

I understand the intent was to service the rac db, but that doesn't make it inappropriate for use by other resources on that cluster, including non-rac dbs.

You certainly can change your tns file (hopefully just a few) to point to the local listener directly; but that seems like an extra maintenance effort for no gain in functionality.
Steve WalesSenior Database AdministratorAuthor Commented:
Thanks for the comments.  Word is that the application service has not fallen over since making the change to tnsnames.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.