Re: Help required for Data replication from local to central database

Lets imagine we have one central table T and many peripherial tables T in branch's databases.

create snapshot logs for peripherial tables.

If you want to keep solid central table(s) create wherever set of jobs which will mimic a snapshot replication BEGIN
SET TRANSACTION READ ONLY;
INSERT INTO T_at_CENTRAL
SELECT * FROM T_at_PERIF_1 T, MLOG$_T_at_PERIPH_1 L WHERE T.PK=L.PK(+) AND L.PK IS NULL;
DELETE MLOG$_T_at_PERIPH_1;
COMMIT;
END; Or, even better, to have separate snapshots (T_1, T_2...) in central database correspondent to peripherial T tables. For reports you can get them together in UNION ALL view. CREATE VIEW T AS
SELECT * FROM T_1 UNION ALL
SELECT * FROM T_2 UNION ALL
....

Get snapshots together into refresh groups if you warry about consistency and/or reference integrity. Rule is - for all tables having FK relationships, their snapshots should be in one refresh group.

You can set up refreshes on central DB in timely manner. Or refresh them "manually" from periferial nodes calling DBMS_REFRESH.REFRESH_at_CENTRAL('REFGRP_1'); Mark Malakanov,
DBA Binay Pandey wrote:

> Hi,
>
> We have a requirement where daily transactions will be held at
> local databases located at different centres having same set of tables
> and at the end of the day INCREMENTAL data from all centres needs to
> be merged at central server. So Central server will be a superset of
> all the centres data.
>
> Here all the transactions will be done at local database level only.
> In central databse we want the complete organisations data for
> reporting purpose.
>
> A dedicated link of central database with the local databases may not
> be available all the time because of network bottlenecks.
>
> Can any one guide me on the approach which is best suited for this
> scenario.
>
>
>
> Regards
> Binay
>
Received on Wed Jan 09 2002 - 00:12:14 CST