| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: FW: snapshot and referential integrity
Richard,
I've interspersed my remarks...
>I don't
> know if the
> restrictions on snapshot changed much in 8i since 8.
The restrictions regarding constraints haven't changed from 8.0 to 8.1.
> At this point I am
> more leaning towards master/master(asynchronous)
> replication for the
> following reasons:
> 1. Setting up master/snapshot sites isn't any
> easier than master/master
> sites.
Hmm, that's debatable. I haven't had a chance to work with templates yet, but they're designed for rapid deployment of snapshot sites.
> 2. By using Asynchronous replication, tables in
> STAGE and LIVE are both
> real tables that don't have the restrictions
> snapshot has.
In 8i the snapshot is a "real" table too (there's no longer a view against a base table). By restrictions I'm assuming you're referring to the deferrable constraints.
Actually, I'd consider the biggest restriction with updateable snapshots to be the exclusive table lock on the snapshot during the refresh process.
Another major restriction is that any alteration of the structure of the master table requires recreating the snapshot (either drop and recreate, or offline instantiate).
Keep in mind that a multi-master table has restrictions as well:
> 3. By using Asynchronous replication I can easily
> break up the relationship
> between STAGE and LIVE if I want to and they will be
> self-contained and can
> function on its own.
Hmm, define easy. Removing a site from a
replication group requires quiescing the group which
means all DML against the replicated objects in that
group is not allowed. Of course you can just break
the push jobs, but txs will continue queueing in
deftran to push to the other site(s) and you'll have
to eventually get your sites back in sync which will
usually involve quiescing the rep group.
>
> Would love to hear your thoughts about this.
I think you need to do a bit more analysis on your business needs. Do updates need to be bi-directional? If so, how do you intend to manage conflicts? How do you intend to control when changes from STAGE are pushed to LIVE if you use multi-master? If you're essentially cloning tables from STAGE to LIVE and never sending changes back to STAGE, replication may not be the best method.
Advanced Replication is a very powerful feature of the db, but its configuration and administration need to be carefully planned and designed.
HTH,
> -----Original Message-----
> Sent: Wednesday, May 31, 2000 8:01 AM
> To: ORACLE-L_at_fatcity.com; richard_at_letsplay.com
>
>
> Richard,
>
> It depends on the version of Oracle used for the
> snapshot site. O7 snapshots do not support PK or
> unique constraints because Oracle doesn't guarantee
> that the records will be refreshed in the same order
> as the DML operations were performed on the master
> table, only that after refresh the rows will be the
> same. You can create indexes on the O7 snapshots,
> just not unique indexes. With the introduction of
> deferrable constraints in O8, PK and unique
> constraints are supported on snapshots as long as
> they
> are created DEFERRABLE.
>
> BTW, this is relevant only to updateable snapshots.
> If you have read-only snapshots there's no point in
> having a PK constraint on the snapshot as DML
> operations aren't allowed on it anyway. You
> guarantee
> RI among snapshots by refreshing them together via
> refresh groups.
>
> HTH,
>
> -- Anita
>
> --- Richard Ji <richard_at_letsplay.com> wrote:
> > Hi all,
> > I am planning to setup the following: a staging
> > database STAGE and a live
> > database LIVE. Some tables in the LIVE database
> are
> > just regular tables
> > (that will get its data directly from web site)
> > others are snapshots from
> > the STAGE database. The reason for that is for
> > those tables we can enter
> > data into it's STAGE's master table and after QA
> it
> > then can be released
> > into LIVE via snapshot refresh.
> > So my question is: For those snapshots in the
> LIVE
> > database, can I have
> > referential integrity to it. For example, if DEPT
> > is a snapshot in LIVE,
> > can EMP has a foreign key to DEPT in the LIVE?
> > Thanks.
> >
> > Richard Ji
> >
> >
> > --
> > Author: Richard Ji
> > INET: richard_at_letsplay.com
> >
> > Fat City Network Services -- (858) 538-5051
> FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
>
 |
 |