synchronisation of oracle database using snapmirror

We are considering synchronisation of records within oracle databases
at two site in different countries. Currently, access to the remote
site is via ftp or telnet. These oracle instances are similar but not
identical and probably never will be. Now we have a project where we
wish to synchronise selected records within selected tables of one of
the oracle database instances (site A) to the other instance (site B).
Conversely we also wish to synchronise a different set of records
within selected tables of the site B oracle database instance to site
A. The two sets would be clearly identify records to be synchronised
in a give direction by a naming convention.

We do have a project to achieve this goal with some bespoke software.
However, we see that there is a product on the market called Snapmirror
which might be able to do what we require. Does anybody know whether
Snapmirror can synchronise at the oracle record level or have I
completely missed the point of what Snapmirror is supposed to do.

Many thanks for any help.

flyingleon wrote:
We are considering synchronisation of records within oracle databases
at two site in different countries. Currently, access to the remote
site is via ftp or telnet. These oracle instances are similar but not
identical and probably never will be. Now we have a project where we
wish to synchronise selected records within selected tables of one of
the oracle database instances (site A) to the other instance (site B).
Conversely we also wish to synchronise a different set of records
within selected tables of the site B oracle database instance to site
A. The two sets would be clearly identify records to be synchronised
in a give direction by a naming convention.

We do have a project to achieve this goal with some bespoke software.
However, we see that there is a product on the market called Snapmirror
which might be able to do what we require. Does anybody know whether
Snapmirror can synchronise at the oracle record level or have I
completely missed the point of what Snapmirror is supposed to do.

I think you are probably on the wrong track. The snapshotting and
mirror technology is dasd (disk) based and usually ( well at least the
EMC stuff ) works by keeping track of which "disk" blocks have changed
( or tracks that have blocks that have changed ).

So usually you have "one" database that is mirrored somewhere else.

Sounds like you are potentially looking at an application using oracle
replication and perhaps multi master replication. Oracle has renamed
this capability as streams recently.

But maybe you don't need something that sophisticated. Maybe you have
use tables in one site and periodically copy ( add in ) records from
the other site, and vice versa.

How quickly do the tables have to be synchronized? What are the
consequences of them getting out of synch?

Those are the kinds of questions I would be thinking about.

Many thanks for your response.

I think that you are right I don't need something that sophisticated.
The tables only need to be synchronised overnight. The reason for the
synchronisation is that two teams one in each country are each
interpreting the source data and need to keep reasonably up to date
with what the other team is doing. Occasionally it might be necessary
to synchronise during the day. Getting out of sync for a short period
of time a day or two would not be a problem.

Thanks again.

hpuxrac wrote:
flyingleon wrote:
We are considering synchronisation of records within oracle databases
at two site in different countries. Currently, access to the remote
site is via ftp or telnet. These oracle instances are similar but not
identical and probably never will be. Now we have a project where we
wish to synchronise selected records within selected tables of one of
the oracle database instances (site A) to the other instance (site B).
Conversely we also wish to synchronise a different set of records
within selected tables of the site B oracle database instance to site
A. The two sets would be clearly identify records to be synchronised
in a give direction by a naming convention.

We do have a project to achieve this goal with some bespoke software.
However, we see that there is a product on the market called Snapmirror
which might be able to do what we require. Does anybody know whether
Snapmirror can synchronise at the oracle record level or have I
completely missed the point of what Snapmirror is supposed to do.

I think you are probably on the wrong track. The snapshotting and
mirror technology is dasd (disk) based and usually ( well at least the
EMC stuff ) works by keeping track of which "disk" blocks have changed
( or tracks that have blocks that have changed ).

So usually you have "one" database that is mirrored somewhere else.

Sounds like you are potentially looking at an application using oracle
replication and perhaps multi master replication. Oracle has renamed
this capability as streams recently.

But maybe you don't need something that sophisticated. Maybe you have
use tables in one site and periodically copy ( add in ) records from
the other site, and vice versa.

How quickly do the tables have to be synchronized? What are the
consequences of them getting out of synch?

Those are the kinds of questions I would be thinking about.

I would suggest you to look at oracle streams for replication between
the two sites.

If you want to keep it simple take a look if a pl/sql using MERGE
statements over dblinks solves your issue.


pedro.



flyingleon wrote:
Many thanks for your response.

I think that you are right I don't need something that sophisticated.
The tables only need to be synchronised overnight. The reason for the
synchronisation is that two teams one in each country are each
interpreting the source data and need to keep reasonably up to date
with what the other team is doing. Occasionally it might be necessary
to synchronise during the day. Getting out of sync for a short period
of time a day or two would not be a problem.

Thanks again.

hpuxrac wrote:
flyingleon wrote:
We are considering synchronisation of records within oracle databases
at two site in different countries. Currently, access to the remote
site is via ftp or telnet. These oracle instances are similar but not
identical and probably never will be. Now we have a project where we
wish to synchronise selected records within selected tables of one of
the oracle database instances (site A) to the other instance (site B).
Conversely we also wish to synchronise a different set of records
within selected tables of the site B oracle database instance to site
A. The two sets would be clearly identify records to be synchronised
in a give direction by a naming convention.

We do have a project to achieve this goal with some bespoke software.
However, we see that there is a product on the market called Snapmirror
which might be able to do what we require. Does anybody know whether
Snapmirror can synchronise at the oracle record level or have I
completely missed the point of what Snapmirror is supposed to do.
I think you are probably on the wrong track. The snapshotting and
mirror technology is dasd (disk) based and usually ( well at least the
EMC stuff ) works by keeping track of which "disk" blocks have changed
( or tracks that have blocks that have changed ).

So usually you have "one" database that is mirrored somewhere else.

Sounds like you are potentially looking at an application using oracle
replication and perhaps multi master replication. Oracle has renamed
this capability as streams recently.

But maybe you don't need something that sophisticated. Maybe you have
use tables in one site and periodically copy ( add in ) records from
the other site, and vice versa.

How quickly do the tables have to be synchronized? What are the
consequences of them getting out of synch?

Those are the kinds of questions I would be thinking about.