RE: Distributed Option

Michael - I don't claim to be a replication expert, just read enough on the subject to avoid going in half-cocked. One thing I have read from the experts though is that any time you have the same data in multiple locations and try to keep it current, you are performing data replication. The tool you use may be home grown, a part of Oracle or another database vendor, or purchased from a third-party vendor, or not even performed at the database level (like the app server, for example). But the problems faced are the same either way, issue like frequency of synchronization, how to resynchronize when everything goes wrong, and it WILL go wrong.

   Back to your message, do you still have a question and I'm so slow on a Sunday morning I missed it, or do you have the information you need? If installing the distributed option into 7.3.4 is not feasible, you may want to review the 3rd party tools. A popular one is Quest Shareplex, but I believe it replicates entire rows which may not meet your requirements. Could be a good time to push your management to upgrade to a more modern Oracle which would give them more built-in replication capabilities.

Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Saturday, December 28, 2002 9:04 AM To: Multiple recipients of list ORACLE-L

It's not quite the same, but very similar....

Let's say that "web" contains simply a name and address, maybe a cust number, but the cust numbers on database A and B are probably different, most likely...

Now A contains not only this, but tons of other things that are pretty much in line with a typical subscription system.

Now B contains not only name and address, but many things typically in line with something like Oracle Open World membership, meetings, suggestions, etc...

Now all of those in B are most likely in A, but A contains a bunch of things that are not in B...

Operations:
If "web" is down, nothing works, but that's okay.

If A does not happen to be available, then the change is put into Queue Table and it will be tried again on a periodic basis...

Same with B.

Now if A is down, and a change of a new record is needed on A and the record is not yet on "WEB", then that change will not be allowed... (that could be programmed in with a full load and some sort of sync of the names and addresses.)

Now if changes occur to A or B, then those changes would be brought to the WEB database, again using queue table if WEB can't be reached at the time.

Other Options:
A "service machine" in C, VB or what ever, could also be used to sync the databases. Changes would be done via the Queue table, and the "service machine" would have FULL links to all the databases. This would allow operations to occur with no care if the databases are all up or not. As soon as they are, the "service machine" would make sure everything was sync'd.

This would also allow for the possible building of extensive audit reports, and even email messages should "situations" occur like two changes at once or a "blank" change, etc. If a change was sensed on A with "123 any street" and on B with "123b any street", it could report that it's the same customer, but with different addresses.

So it's not quite "replication" as it may only be 5-7 fields of a 40-100 column table. Nothing has been mentioned to this point if they are going to play with "ship to", "bill to" and some of the other addresses...

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of DENNIS
> WILLIAMS
> Sent: Friday, December 27, 2002 11:04 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Distributed Option
>
>
> Michael - Okay, this is a form of replication, known as "synchronous
> replication". That means that the updates occur synchronously, or within a
> 2-phase commit. This is implemented through database links. The
> drawback is
> that the transaction is as slow as the slowest database. If one
> database is
> unavailable, no transactions can complete. This can be okay for
> non-mission-critical situations. Someone earlier (I'm sorry, I didn't keep
> that message) pointed out that this may be an extra pay option back in
> 7.3.4. I think maybe you could query but to update you needed the extra
> option. Details are available in the Oracle manuals, which you can view
> online. I'm sorry, but I haven't worked on 7 in quite a few years.
> Based on your description of your situation, you may want to consider
> letting the application server do the honors. A simple app server like
> Tomcat can simply connect to each database. A more sophisticated
> app server
> like Oracle9iAS or WebLogic is capable of maintaining separate message
> queues, so you aren't limited by the slowest database and if a database is
> down, can maintain the queue of messages and apply them when it becomes
> available again. I'm speaking a little beyond my knowledge at this point,
> but you get the general idea and if this sounds promising you can
> investigate further with someone that actually knows what they are talking
> about.
>
> -----Original Message-----
> Sent: Friday, December 27, 2002 5:14 PM
> To: Multiple recipients of list ORACLE-L
>
>
> I believe this is different than replication, though many of
> the ideas and transactions would be the same.
>
> In this particular case, they are going to allow Name and address
> changes over the web. Those changes will cause updates to two some
> what different customer files on two different applications on two
> different "other" databases.
>
> What happens now is when they tried to do the update over the
> database link, and commit, they get "distributed option" not
> installed.
>
> This is further confused that one database has replication but
> still gets an error message about "distributed option" not
> installed. And the v$option shows "distributed option" as
> false or what ever.
>
> Connected to:
> Oracle7 Server Release 7.3.4.0.1 - Production
> With the parallel query option
> PL/SQL Release 2.3.4.0.0 - Production
>
> This is the sign on for one of the databases missing the option.
>
> My personal oracle shows:
>
> Connected to:
> Personal Oracle7 Release 7.2.2.3.1 - Production
> With the distributed and replication options
> PL/SQL Release 2.2.2.3.1 - Production
>
> Maks.
>
> > -----Original Message-----
> > From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of DENNIS
> > WILLIAMS
> > Sent: Friday, December 27, 2002 4:19 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: Distributed Option
> >
> >
> > Michael
> > By distributed, I assume you mean "replication"?
> > From what I can tell, basic replication is included with
> > Standard Edition
> > and advanced replication is included with Enterprise Edition.
> > I think you run a script, something like "catrep.sql" in
> > rdbms/admin, so
> > you should be able to get the pieces installed.
> > Replicating between different Oracle versions could be
> challenging, but
> > doable. Others on the list can probably offer specific advice
> on pitfalls.
> > We haven't implemented replication here, just studied. From
> what I can
> > tell, planning for replication is everything. I have only been
> > able to find
> > 2 books on it. The prize is Marie Buretta's Database Replication.
> > http://www.amazon.com/exec/obidos/ASIN/0471157546/qid%3D1041023613
> > /sr%3D11-1
> > /ref%3Dsr%5F11%5F1/102-1511927-6720101
> > It really tells you everything you need as an organization to
> prepare for
> > replication. Replication takes a lot of administration so it should be a
> > gold mine since you are consulting.
> > The other book is Oracle Distributed Systems by Charles Dye
> > http://www.amazon.com/exec/obidos/ASIN/0471157546/qid%3D1041023613
> > /sr%3D11-1
> > /ref%3Dsr%5F11%5F1/102-1511927-6720101
> >
> >
> > Dennis Williams
> > DBA, 40%OCP
> > Lifetouch, Inc.
> > dwilliams_at_lifetouch.com
> >
> > -----Original Message-----
> > Sent: Friday, December 27, 2002 2:39 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > I've got a client that needs "distributed
> > option" installed on several databases,
> > versions 7.3.4, 8.0.5, and 8.1.7...
> >
> > Problem may be I'm not sure we'll have all
> > the CD's as vendors of applications did
> > most of the installs and we think we'll
> > find that they took the CD's with them.
> > After all, if it's up and running and
> > vendor supported(or was), why would the
> > client need the CD?
> >
> > Anyhow, is it just a "free" option that will
> > need to be selected with the Oracle installer,
> > or is it an "add on" that one is supposed to
> > contact Oracle on? Or perhaps it just involves
> > running one of those all but undocumented
> > packages.
> >
> > There are many ways to work around this if
> > we have to, but would like to know what all
> > is involved if we can get the distributed
> > option installed on all of their databases.
> >
> > It's on my Personal Oracle, but seems to
> > be part of a general set of things that
> > get installed.
> >
> >
> >
> > Kline's Consulting
> > Michael Alan Kline, Sr., Owner
> > 13308 Thornridge Court; Midlothian, VA 23112, USA.
> > Work: 804-744-1545 Cell: 804-314-6262
> > Pager: 877-705-1155 ICQ: 1009605, 975313
> > mkline1_at_comcast.net; maklinesr_at_earthlink.net;
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Michael Kline
> > INET: mkline1_at_comcast.net
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: DENNIS WILLIAMS
> > INET: DWILLIAMS_at_LIFETOUCH.COM
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Michael Kline
> INET: mkline1_at_comcast.net
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.COM
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Michael Kline
  INET: mkline1_at_comcast.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).