Hi all,

Some, not all, of the tables in my schema contain component library
data. In a multi-site deployment, I want to have one site designated
as the master and be able to propogate all the changes that take place
in the master's component library to other sites, without messing with
whatever non-component related data they may have. It's possible - but
not guaranteed - that the remote sites will be able to "see" the
master over the network (so near-instant replication isn't necessarily
a requirement). They could be running any version of 9i and/or 10g.
How can this be achieved?

TIA
Tarby

Tarby777 wrote:
> Hi all,
>
> Some, not all, of the tables in my schema contain component library
> data. In a multi-site deployment, I want to have one site designated
> as the master and be able to propogate all the changes that take place
> in the master's component library to other sites, without messing with
> whatever non-component related data they may have. It's possible - but
> not guaranteed - that the remote sites will be able to "see" the
> master over the network (so near-instant replication isn't necessarily
> a requirement). They could be running any version of 9i and/or 10g.
> How can this be achieved?
>
> TIA
> Tarby
>

Hi,

have you looked for materialized views in Oracle? Materialized Views
offer the possibility to replicate data even over the network.

Look for Materialized Views (or Snapshots) in google and in the oracle
documentation and you will find a solution.

Uwe

Tarby777 wrote:
> Hi all,
>
> Some, not all, of the tables in my schema contain component library
> data. In a multi-site deployment, I want to have one site designated
> as the master and be able to propogate all the changes that take place
> in the master's component library to other sites, without messing with
> whatever non-component related data they may have. It's possible - but
> not guaranteed - that the remote sites will be able to "see" the
> master over the network (so near-instant replication isn't necessarily
> a requirement). They could be running any version of 9i and/or 10g.
> How can this be achieved?
>
> TIA
> Tarby

Streams.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Thanks Daniel and Uwe for your replies. I'll look into the things you
suggested. One particular scenario is concerning me, and I'd be
interested to hear how materialised views and streams would handle it:

Say an organisation has several installations of the application. We
want to introduce component library replication into the organisation.
The component libraries are currently unsync'd, but are likely to
contain many components (part numbers) that are common to both,
although the UIDs will be different. The UID is the primary key on the
table, and can't be changed in the target DB, because data in other
tables references the components by their UID. The replication process
would have to identify components in the target DB by their part
numbers rather than by their UIDs, and would have to be able to
replicate certain columns in the component tables while leaving others
untouched.

Can mat'd views and/or streams handle this scenario?

TIA
Tarby

Tarby777 wrote:
> Thanks Daniel and Uwe for your replies. I'll look into the things you
> suggested. One particular scenario is concerning me, and I'd be
> interested to hear how materialised views and streams would handle it:
>
> Say an organisation has several installations of the application. We
> want to introduce component library replication into the organisation.
> The component libraries are currently unsync'd, but are likely to
> contain many components (part numbers) that are common to both,
> although the UIDs will be different. The UID is the primary key on the
> table, and can't be changed in the target DB, because data in other
> tables references the components by their UID. The replication process
> would have to identify components in the target DB by their part
> numbers rather than by their UIDs, and would have to be able to
> replicate certain columns in the component tables while leaving others
> untouched.
>
> Can mat'd views and/or streams handle this scenario?

I think that materialized views can handle this...

A materialized view is a view at the first glance. Therefore if you can
build a view for your needs the half way is done.

A materialized view is a view that materializes his data into a table
and the table is replicated at defined points in time, even between
different databases.

As far as I understand your requirements I think materialized views can
handle it.

Uwe

Tarby777 wrote:
> Thanks Daniel and Uwe for your replies. I'll look into the things you
> suggested. One particular scenario is concerning me, and I'd be
> interested to hear how materialised views and streams would handle it:
>
> Say an organisation has several installations of the application. We
> want to introduce component library replication into the organisation.
> The component libraries are currently unsync'd, but are likely to
> contain many components (part numbers) that are common to both,
> although the UIDs will be different. The UID is the primary key on the
> table, and can't be changed in the target DB, because data in other
> tables references the components by their UID. The replication process
> would have to identify components in the target DB by their part
> numbers rather than by their UIDs, and would have to be able to
> replicate certain columns in the component tables while leaving others
> untouched.
>
> Can mat'd views and/or streams handle this scenario?
>
> TIA
> Tarby

Define "component libraries" this is not an Oracle term.

Materialized view (older method) and Streams (newer 9i and 10g) and
intended for data replication.

I would suggest you ignore Materialized Views if, as you previously
stated, the connection may be down. Streams is asynchronous and can
play catch-up. Materialized views are a maintenance nightmare.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

DA Morgan schreef:

> Materialized view (older method) and Streams (newer 9i and 10g) and
> intended for data replication.

Daniel, are you not mixing up Advanced Replication and MV's?
>
> I would suggest you ignore Materialized Views if, as you previously
> stated, the connection may be down. Streams is asynchronous and can
> play catch-up. Materialized views are a maintenance nightmare.
AR can be - MV's - nope

--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Frank van Bortel wrote:
> DA Morgan schreef:
>
>> Materialized view (older method) and Streams (newer 9i and 10g) and
>> intended for data replication.
>
> Daniel, are you not mixing up Advanced Replication and MV's?

I don't think so. A materialized view, on its own, will replicate data
but it is not asynchronous and the OP indicated the connection, at
times, may be down. Or at least that's my recollection and I'm sticking
with it. ;-)

>> I would suggest you ignore Materialized Views if, as you previously
>> stated, the connection may be down. Streams is asynchronous and can
>> play catch-up. Materialized views are a maintenance nightmare.
> AR can be - MV's - nope

My point. And AR is not in the same galaxy, maintenance-wise as Streams.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

DA Morgan schreef:
> Frank van Bortel wrote:
>> DA Morgan schreef:
>>
>>> Materialized view (older method) and Streams (newer 9i and 10g) and
>>> intended for data replication.
>>
>> Daniel, are you not mixing up Advanced Replication and MV's?
>
> I don't think so. A materialized view, on its own, will replicate data
> but it is not asynchronous and the OP indicated the connection, at
> times, may be down. Or at least that's my recollection and I'm sticking
> with it. ;-)

Erhm... MV's are refreshed when you like them to be refreshed.
That's async to me (apart from refresh groups, that can refresh
synchronously/asynchronously - internal to the group).
Same for AR - AR uses MV's, or at least; it can.
>
>>> I would suggest you ignore Materialized Views if, as you previously
>>> stated, the connection may be down. Streams is asynchronous and can
>>> play catch-up. Materialized views are a maintenance nightmare.
>> AR can be - MV's - nope
>
> My point. And AR is not in the same galaxy, maintenance-wise as Streams.

Streams would be a valid choice for the OP, but I find it a
bigger nightmare than MV's - even with additional logging.

MV's seem out of the question, as the OP wants parts of tables
(this column yes, this not) only - not sure Streams can handle
that, nor how conflict resolution works (which does work
rather well with Advanced Replication)
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Frank van Bortel wrote:
> DA Morgan schreef:
>> Frank van Bortel wrote:
>>> DA Morgan schreef:
>>>
>>>> Materialized view (older method) and Streams (newer 9i and 10g) and
>>>> intended for data replication.
>>> Daniel, are you not mixing up Advanced Replication and MV's?
>> I don't think so. A materialized view, on its own, will replicate data
>> but it is not asynchronous and the OP indicated the connection, at
>> times, may be down. Or at least that's my recollection and I'm sticking
>> with it. ;-)
>
> Erhm... MV's are refreshed when you like them to be refreshed.
> That's async to me

If you believe in manual intervention and always manually test to see
that the connection is up and always do a complete refresh.

(apart from refresh groups, that can refresh
> synchronously/asynchronously - internal to the group).

The difference is that if the DB Link is down it doesn't automatically
catch up ... streams does.


> Same for AR - AR uses MV's, or at least; it can.
>>>> I would suggest you ignore Materialized Views if, as you previously
>>>> stated, the connection may be down. Streams is asynchronous and can
>>>> play catch-up. Materialized views are a maintenance nightmare.
>>> AR can be - MV's - nope
>> My point. And AR is not in the same galaxy, maintenance-wise as Streams.
>
> Streams would be a valid choice for the OP, but I find it a
> bigger nightmare than MV's - even with additional logging.

You've got to be kidding. ;-)

Streams is easy and there are a large number of live-demos on the web,
such as mine at at www.psoug.org that are literally cut-and-paste jobs
to put into use.

> MV's seem out of the question, as the OP wants parts of tables
> (this column yes, this not) only - not sure Streams can handle
> that, nor how conflict resolution works (which does work
> rather well with Advanced Replication)

Streams can handle it with ease.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org