Two Servers running databases with same Oracle SIDs ?

[Massimo_37]

Is it feasible to have on a network, two different servers(both HPUX 10.2) with one database each (Oracle 7.3.4), but the databases having the same ORACLE_SIDs on both machines. Will this cause a conflict ? I have a third server, which has a database link with one of them. We also have SQL*Net 2 and data is replicated on a daily basis over the network to a Windows based server.
It would be easier for us, if the machines could have the same SIDs, as one of the servers in question will be retired shortly. But will this cause problems with listeners etc till then? Many Thanks

[Steven E. Protter]

You should be able to do that. You lose however any oracle functioanlity that would let you use a sqplplus to connect and transfer data between the machines.

The unexpected behavior is probably due to inconsistencies in tnsnames.ora or sqlnet.ora, maybe even listener configuration.

Two databases with the same sid is something my dba doesn't do because of of fears that connectivity would be affected.

SEP

[Sanjay_6]

Hi,

Though it is not recommended, but you can still have that. The oracle db to which one gets connected to depends on which db location / server is referred to in the tnsname.ora file on the server / client from where the connection is being made. Lot of times we configure the db as a dns entry, for eg,

db_name.some_company.com

In this type of situation you'll be able to config only one db in the dns.

It is best to have different names to avoid confusing situations.

Hope this helps.

Regds

[Indira Aramandla]

Hi,

Yes you can have the same SID names on two different servers. It should not be an issue. Because in you tnsnames.ora and listener.ora files in the address lists you specify the hostname which are different.

When you need to refresh on a regular basis, having the same name helps. We have an environment here where the database names on two servers with HP-UX 11i are the same and we do a complete refresh of the database on one server from the database on the other server every month.

Indira A

[R. Allan Hicks]

You should be able to have the same SID on different servers. We have been in the same sitituation where we were retiring a server and it worked ok. We set the tnsnames.ora on the clients to have an A service and a B service (i.e. ORCLA and ORCLB), plus a service that pointed to the production machine (ORCL).

We were able to set up db_links so that we could access either table from either server. Then, we created scripts to move the last pieces of data from the old server to the new server just before will pulled the plug. Changed the hostname in the tnsnames.ora on all the clients and we were done.

The listener is listening for a connection to a service on it's host. It doesn't know about the other hosts. So as long as the SID HOSTNAME combination is unique, you should be in good shape.