Re: Replication w/ read-only snapshots. Ick.

> We are running Oracle 7.3 Workgroup Server(WGS) on several different
> platforms: Solaris SPARC, Solaris x86, and Windoze NT. We have been told
> that the advanced repolication option is not available for the WGS at all,
> and essentially that we are stuck using read-only snapshots and two-phase
> commit for data replication purposes.
 

> We feel that we can keep a master site running almost 24/7, and when we
> can't we're willing to forego any updates(but slaves must still be
> available for read-only). Even so, I do have a few questions...
 

> Two-phase commit(2PC) is fine if both servers are working. But if one of
> the machines is down, the transaction is rolled back and not allowed to
> take place anywhere. This will guarantee data consistency, but is not very
> fault tolerant. Are there any tricks that can be played with in-doubt
> transactions that make them update locally, but wait a while if they can't
> propagate through?

If the 2PC tx's are uni-directional, you can do something similar with snapshot replications and a very frequent snapshot refresh interval. If the tx's are bi (or more) directional, you may be out of luck. Depending on the complexity of the system and transactions, you might be able to write something that mimics the functionality in Adv Rep Option that allows for something like this. I think this is an option only if you are dealing with very simple tx's.

> While a snapshot is being refreshed (complete), what happens to queries
> that try to access that snapshot'ed data? If I have 100MB of old data,
> will that continue to be available until the new data is completly
> propagated over? (i.e. no downtime of a table on the slave?)

The queries run just as identical to the situation of queries running against any generic table that is being updated by another user. Oracle will handle all the read consistency issues and no readers get blocked out. The typical problems with doing consistent reads on a table with heavy DML activity also exists (specifically, I'm referring to the "snapshot too old" error messages when undo to build a consistent image is overwritten by other tx's).

> I know indexes and related objects are not snapshot'ed over, but if I
> create them on the slave machine, do they stay there between complete
> refreshes? Take the 100MB table again... I build an index on it on the
> slave, it gets a complete refresh, what happens to that index?

Again, this is no different then if a user updates a local table with an index on it: The index gets updated, etc...Keep in mind that a slave read-only snapshot is basically just another table that is named with a SNAP$_ prefix and has a bunch of views to allow it to be updated via snapshot refreshes initiated by the master server. All the logic is done via SQL or PL/SQL, so all the tx's obey standard Oracle rules.

> And, sort of a mix of the two above questions: what happens to the queries
> on the slave while the index is being built? Are they slooow? Is the old
> data with the old index used?

Unless you actually drop the index on the slave table, you would never have to build it except when during initial setup. The index behaves like any other index in Oracle, it gets updated as rows in the index's table gets updated. During a standard database refresh, the indexes on the slave table remain and are updated as the refreshed rows from the master are inserted/updated into the slave table.

> I've heard people talk about fast refresh times as quick as 2 seconds. Is
> that practical if 10-20 rows need to be updated in that time(i.e. if the
> first refresh doesn't finish before the next, problemsproblemsproblmes)?

10-20 rows per snapshot refresh is fairly trivia for read-only slave refreshes. The duration of the refresh will depend on a lot of factors like ave size of rows, layout of SNAP$_, INDX$_ and snapshot logs (if you are doing incremental refreshes), and other generic tuning issues.

good luck,

Johnny Chan
Independent Oracle Specialist Received on Tue Dec 31 1996 - 00:00:00 CST