backup server

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

Yes, it can be done. Read up on Standby databases.

I use replication to keep this 2 box in the sync.
question is how to let user use the same connection string
to switch between 2 instances.

The best is to go with the standby database and it would be more easier to handle with less hassel. Here is a pot hole on the approach that you are about to try. Solution Description:
=====================

When a user requests to connect to an , SQL*Net will randomly choose one
of the addresses in the address list. If that address fails for any reason,
SQL*Net will not choose another address in the address list, it will return
the appropriate error.

Please note that in this example all three addresses use the same protocol and
host. If you are running Parallel Server, then your addresses in the address
list can use different hosts. Also, each address in the address list can use
different protocols.

If you are using Parallel Server and want to use load balancing across
multiple servers, the sid name on each server must be the same.



Example:

=
(description_list=
(description=
(address_list=
(address=
(protocol=tcp)
(host=)
(port=1521)
)
)
(connect_data =
(sid = sidname)
)
)
(description =
(address_list =
(address=
(protocol=tcp)
(host=)
(port=1522)
)
)
(connect_data =
(sid = sidname)
)
)
(description =
(address_list =
(address=
(protocol=tcp)
(host=)
(port=1523)
)
)
(connect_data=
(sid=)
)
)
)
.

Good luck
Sam

yes, I did use standby database.

I have a primary DB on box1. 2 standby DB on box2 and 3.

user will connect to either box2 or box3. our plan is if box2
fail, user can reconnect to box3 using the same connection
string (user/pass@dba1) from application.

based on Sam's answer, I have to use parallel to implement it.
what if I do not have parallel server, can I do it?

It can be done without using parallel server, all you have to do is modify your tnsnames.ora file to look something like this...

net_service_name=
(description=
(failover=on)
(address=(protocol=tcp)(host=sales1-svr)(port=1521))
(address=(protocol=tcp)(host=sales2-svr)(port=1521)))
(connect_data=(service_name=sales.us.acme.com)))


hth.

Talkin about backup server my boss asked me what high availability does Oracle offer, I mentioned Parallel Server & Advanced Replication. Our enviroment is already using Sun Cluster technology in a data center so I told him that OPS would offer availability + load balancing now he asks me about Replication I went a bit stoned because I think Replication is really for disasters like the whole data center gets blown up and we would have another replication in another city. And I dont see much point of having two server replicating each other in the same building.
Anyone have a suggestion of using OPS or Replication? I would guess OPS is cheaper to setup?

I didnt even mention standby database because if OPS is used we get availability and load balancing, with Raid 5 we have redundant disks in our situation so in case of disk corruption we can still recover, would standby database provide any improvement in case we lose a disk for whatever reason in the enviroment we are?

By the way since I am not an expert with Solaris does anyone know if Sun Cluster offers load balancing for database servers? (As far as I know it only offers availability)

[Edited by pando on 03-06-2001 at 03:44 PM]

Check the metalink's technical library and it has number of articles regarding these issues under networking. I suggest you go thorough that for a good undestanding.

Sam

to UNA,

Based on your suggestion, I modified tnsnames.ora

DBSP.WORLD =
(DESCRIPTION =
(FAILOVER=ON)
(ADDRESS = (PROTOCOL= TCP)(Host= purple)(Port= 1521))
(ADDRESS = (PROTOCOL= TCP)(Host= silver)(Port= 1521))
(CONNECT_DATA = (SID = DBU2))
)

then I issue

sqlplus system/manager@DBSP
> select * from v$instance;
select INSTANCE_NAME, HOST_NAME from v$instance;
INSTANCE_NAME HOST_NAME
---------------- ---------------------------------------------------------------
DBU2 silver


(still connect to the last one)

then I shutdown instance on silver, try to reconnect get
ORA-01034: ORACLE not available

What I did wrong?

This will only work if you are using 8i databases, the instance should register itself with the listener.

Do not set the GLOBAL_DBNAME parameter in the SID_LIST_listener_name section of the listener.ora file. A statically configured global database name disables connect-time failover.

thanks, LND.

I am using 8.0.5. I guess I have to implement it outside oracle or upgrade to 8i.