Re: Advanced Replication of Procedures, Functions, and Packages

Have a look at Procedural Replication. It may be what you need to use.

Pete

"Chris R. Donnelly" wrote:

> Basically what I want to do is cluster several worker databases and be able
> to use them interchangeably to run large batch jobs, distributing the load
> evenly across the machines. Each job would perform its data processing on a
> single worker machine (without affecting the others), and the output is
> written to permanent tables replicated on each of the worker databases. To
> simplify updates to PL/SQL code, I would like to be able to alter it on one
> database only, and have the changes automatically propagate to the other
> machines.
>
> I really appreciate your help here -- especially since I tend to explain
> things poorly...
>
> // Chris
>
> Pete Sharman <psharman_at_us.oracle.com> wrote in message
> news:37D84D22.794C5A18_at_us.oracle.com...
> > I think we need to get back to the business requirements here. Why are
> you
> > attempting to replicate a user? Replication is normally used to replicate
> data,
> > rather than an entire user. It may be that you're using a sledge hammer
> to
> > break a walnut open, if you'll excuse the analogy.
> >
> > Pete
> >
> > "Chris R. Donnelly" wrote:
> >
> > > You are correct in that I am trying to replicate modifications to the
> > > procedure by compiling it directly (i.e., running "create or replace
> > > procedure foo" directly). Do the changes only replicate if I use the
> > > procedure you specified? If so, that will complicate what I am trying
> to do
> > > (which is, essentially, to replicate a user between two sites).
> > >
> > > Is there any other way I can replicate changes to a schema? Although I
> > > would prefer not to replicate the entire schema, I will do that if
> > > necessary...
> > >
> > > Pete Sharman <psharman_at_us.oracle.com> wrote in message
> > > news:37D82EB6.850942FA_at_us.oracle.com...
> > > > CHris
> > > >
> > > > I'm not 100% clear on what you're trying to achieve here, but it seems
> > > like
> > > > you're testing something which is going to become more complex. If
> you're
> > > > attempting to replicate a procedure to do something like data purging,
> you
> > > need
> > > > to investigate procedural replication. If you're attempting to simply
> > > replicate
> > > > a modification to a procedure, then you're using the wrong approach to
> > > change
> > > > the procedure. It looks like in step 4 you're changing the procedure
> > > directly,
> > > > rather than using the DBMS_REPCAT.ALTER_MASTER_REPOBJECT procedure, in
> > > which
> > > > case you haven't registered the change in the replication data
> dictionary
> > > > views. This will mean no replication of the changes, as you are
> seeing.
> > > >
> > > > HTH.
> > > >
> > > > Pete
> > > >
> > > > "Chris R. Donnelly" wrote:
> > > >
> > > > > Unfortunately, I don't have a script for most of it because I am
> using
> > > > > Oracle Replication Manager (version 2.0). All I am doing is as
> follows:
> > > > >
> > > > > 1. At the masterdef site, I use Replication Manager to create the
> > > master
> > > > > group with one other master site.
> > > > > 2. I compile my stored procedure to the masterdef site. At this
> point
> > > the
> > > > > other site does not have the procedure (or any similarly named
> object).
> > > > > The procedure is very simple (e.g., "begin null; end;")
> > > > > 3. I then use Replication Manager to add the stored procedure to
> the
> > > master
> > > > > group. The procedure is created on the other site as a result.
> > > > > 4. I then make a trivial change to my stored procedure (e.g., add a
> > > > > comment) and compile it to the masterdef site. The procedure is
> never
> > > > > replicated to the other site, regardless of what changes are made to
> the
> > > > > masterdef site.
> > > > >
> > > > > Both servers are set to the same time zone and have the same time,
> so
> > > that
> > > > > is not an issue. The timestamp of the changed procedure on the
> > > masterdef
> > > > > site is later than the timestamp of the original procedure on the
> other
> > > > > site. In addition, the procedure will not replicate to the other
> site
> > > even
> > > > > if the procedure is dropped from the other site without dropping it
> from
> > > the
> > > > > masterdef site.
> > > > >
> > > > > At one point we did have a problem where Replication Manager created
> > > public
> > > > > database links, which the jobs could not use (they kept returning
> > > ORA-01004
> > > > > because no username was specified in the link). We substituted
> private
> > > > > database links for those and at that point, any tables we also added
> to
> > > the
> > > > > master group (independent of the stored procedure) replicated data
> > > changes.
> > > > >
> > > > > Pete Sharman <psharman_at_us.oracle.com> wrote in message
> > > > > news:37D7D58E.95F28066_at_us.oracle.com...
> > > > > > OK, so no errors anywhere, including the snapshot trace files?
> Then
> > > can
> > > > > you
> > > > > > send us the steps you're going through? If you've got it all in a
> > > script,
> > > > > send
> > > > > > that rather than describing it in words, because it's easier to
> see
> > > what's
> > > > > > missing from a script.
> > > > > >
> > > > > > Pete
> > > > > >
> > > > > > "Chris R. Donnelly" wrote:
> > > > > >
> > > > > > > It simply won't replicate the procedure on updates. There are
> no
> > > error
> > > > > > > messages from either Oracle Replication Manager or in the
> databases'
> > > > > dump
> > > > > > > directories (i.e., the database jobs are running successfully).
> The
> > > > > > > procedure is replicated only when it is first added to the
> master
> > > group
> > > > > and
> > > > > > > no other time (even if the procedure is later dropped on the
> > > > > non-masterdef
> > > > > > > server).
> > > > > > >
> > > > > > > // Chris
> > > > > > >
> > > > > > > Pete Sharman <psharman_at_us.oracle.com> wrote in message
> > > > > > > news:37D597B6.DB1B1496_at_us.oracle.com...
> > > > > > > > Chris
> > > > > > > >
> > > > > > > > Bit difficult to tell what's going wrong from what you've
> > > described.
> > > > > Is
> > > > > > > there
> > > > > > > > an error message you're getting, or is it just not replicating
> the
> > > > > > > procedure?
> > > > > > > >
> > > > > > > > Pete
> > > > > > > >
> > > > > > > > "Chris R. Donnelly" wrote:
> > > > > > > >
> > > > > > > > > Hi everyone,
> > > > > > > > >
> > > > > > > > > I have set up multimaster replication between two Oracle 8i
> > > > > databases.
> > > > > > > The
> > > > > > > > > objects I am replicating between the two masters are a table
> and
> > > a
> > > > > > > stored
> > > > > > > > > procedure. The table data is replicating normally (i.e., a
> > > change
> > > > > on
> > > > > > > one
> > > > > > > > > database propagates to the other as expected). However,
> when I
> > > make
> > > > > a
> > > > > > > > > change to the stored procedure on the masterdef database, it
> > > does
> > > > > not
> > > > > > > > > replicate the changes in the procedure to the other
> database,
> > > either
> > > > > > > > > automatically or by manual execution. I have tried using
> both
> > > > > > > Replication
> > > > > > > > > Manager as well as directly executing the procedures in the
> > > > > DBMS_REPCAT
> > > > > > > > > package to no avail, as well as both synchronous and
> > > asynchronous
> > > > > > > > > replication (preferably using asynchronous). What do I need
> to
> > > do
> > > > > to
> > > > > > > > > replicate the stored procedure's DDL?
> > > > > > > > >
> > > > > > > > > // Chris
> > > > > > > >
> > > > > >
> > > >
> >

Received on Fri Sep 10 1999 - 13:39:50 CDT