Q: How to handle oracle installations with same SID on same subnet? 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).


*

*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)

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

*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.

A: 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)
    )
  )
