How to handle oracle installations with same SID on same subnet?

Question

I have two Oracle RAC clusters on the same subnet. For this discussion, let's assume one is a QA cluster and one is a DEV cluster. They are both running on RedHat Enterprise Linux 5, 64-bit.

They are configured identically, so they both have an instance named 'MyInstance' and have the same users (both system & DB), passwords, and everything.

Using:

oracle@QA1> sqlplus -S user/password @my_sql_file

actually runs my_sql_file.sql against the DEV instance, even though I'm on the QA1 server. I can try something like:

oracle@QA1> sqlplus -S user/password@QA1/MyInstance @my_sql_file

but this gives me errors about the target host or object not existing (QA1 is in my hosts file and MyInstance is in my 'lsnrctl status' output).

1. What do you do when you've got clusters on the same subnet? Is there something special I should have configured? I've been reading and I think I should have given them different service names. Do you dbas agree? (I'm currently trying to find out how to do this on 11g)

2. Is there a way to force SQLPLUS to only connect to local instances?

3. Do my SIDs or Listeners need to be configured in any special way to allow the user/password@host/sid connection to SQLPLUS to work? Right now it doesn't.

Answer 1

What you have described is a very common scenario. Often you will have a Beta, Dev and Production environment that match each other. You should just need to edit your tnsdames file. See example below

# Generated by Oracle configuration tools. 

QA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = computer.somedomain.com)(PORT = 1521)) ) (CONNECT_DATA = (sid=samesid) ) )

DEV = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = "computer2.somedomain.com")(PORT = 1521)) ) (CONNECT_DATA = (sid=samesid) ) )