My boss ask to set up a backup server, if the primary server down, user can reconnect to backup server (manually) but he
does not want use different service name in tnsnames.

He wants if user issues

sqlplus user/pass@dba1
will connect to the first server (silver), and if the first one down it will connect to 2nd host (gold).


Here is what I did:

I set up 2 oracle instances and 2 server box. each instance on each box, both instance with the same sid (DBA1).

tnsnames.ora on both box looks like this:

DBA1.WORLD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL= TCP)(Host= silver)(Port= 1521))
(ADDRESS = (PROTOCOL= TCP)(Host= gold)(Port= 1521))
(CONNECT_DATA = (SID = DBA1))
)


I tried, it always connect to 2nd server (gold) and if
it went down (gold), get ORA-01034 oracle not available.

question:
is this doable, if yes, what's the solution?

thanks in advance