Re: snapshots question

From: Andy Tasker <andy_at_spirocom.demon.co.uk>
Date: Fri, 17 Apr 1998 17:58:40 +0100
Message-ID: <892832944.24379.0.nnrp-02.c2de7651@news.demon.co.uk>

Carole Turney wrote in message <353679BF.F2CFD276_at_cts.ucla.edu>...
>Hi,
>
>We're running Oracle 7.3.4 on two Sun servers. One server is our
>production machine, and we want to be able to duplicate, in read-only
>mode, the tables to the second machine to offload system resources for
>users who only need to look at, not change the data.
>
>Simple snapshots look like they *should* do the trick nicely (at least
>in theory). I figured we'd have to play with time intervals to find
>optimal refresh times. Are there any other alternatives which may work
>just as well (or better)? Anyone have any experience/advice with
>snapshots with gotcha info?
>
>thanks in advance,
>Carole (cturney_at_cts.ucla.edu)
>

I've used snapshots before; and they have work v. well.

Advantages :

Being able to horizontally and vertically partition the tables in question. Being able to create snapshots that are summaries of more complex data.

Disadvantages :

You can only fast refresh simple snapshots. If you have v. large tables that a loaded in bulk (ie. one off loads of Gb of data),
then the snapshots may need to be rebuilt, causing outages and loading of the
master site (a way round this is to implement such tables using partitions).

Are the two machines attached by a very fast network ?

If you are taking a pure carbon copy for an operational data store, then another
(brave !) option is to run OPS on the two different machines (if they are physically close together, linked by a fast network) with remote mirroring between
the disk packs of the two machines. Because the one machine is query only, then
you should not suffer the dreaded block pinging problem (ensure that the machine
that is query only can only run query only apps !). eg.

Server 1                                               Server 2

OPS                                                      OPS
   |                                                              |

disks ----- remote mirroring -------- disks

Effectively, even though the database is on two physical disks, the remote mirroring
keeps them in tandem; as far as OPS is concerned, it is one disk pack, one database.

This gives the added advantage of having a high availablity fail over machine if the
production OLTP box fails (hardware only; software failure would be copied between
the disks).

I know that HP can support this (using SRDF), but I don't know about Sun.

Andy Tasker
Spirocom Analysts Ltd. Received on Fri Apr 17 1998 - 11:58:40 CDT