What is the usage of snapshots?

Can someone clarify me in using snapshots in a distributed environment we are
using Oracle 8.0.3 on NT We Have production database as well as development
database and we are thinking of implementing snaphots mechanism in production
database to refelect the data for few tables from development database.
My doubdt is will the snapshot will be replacing actual table in
destination or is there any other way through which we can have a linking
facility of snapshot to the base table. Will it be possible to do the entire
process without writing any procedures. Or can we implement in such a way by
creating snapshot log and by reading this we can reflect changes to the table
in a procedural way. Please suggest me in implementing snapshots for a
existed distributed database and how to maintain referential integrity.

Can someone give me details about usage of replication manager

Ans:
In snapshot based replication mechanism there are single master/multi master
scheme. As per your description your case is single master situation (one
master database - your development database and one slave - your production
database) hence your problem is a simple one.

First you must create a dblink under the schema of slave site which will have
replicated data from tables from master site. These dblink is your logical
transport for replication.

Create snapshot logs on tables in your master database. For example if there
is an emp table in your master database and you want it to be replicated in
your slave site create snapshot log on emp in master site. These snapshot
logs are change vectors in your master table which needs to be replicated.

Then create snapshots in your slave site as "select * from emp@dblink". First
snapshot creation actualy copies all data from the table as we have used * in
snapshot creation query. So you need to allocate large enough rollback segment
on both sites and create snapshots so that they use that segment (see oracle
documentation).

The first time you create a snapshot log and corresponding snapshot there is
no refresh interval unless you specify. Then there are some design headaches.
Normally there are parent tables and child tables in every database in
hierarchy. Child tables must be refreshed first for integrity constraints
(snapshot mechanism keeps child/parent reference).

Use DBMS_REFRESH.MAKE procedure to create refresh groups. Group snapshots so
that tables at same hierarchical level are refreshed in a group. Timing
refreshment appropritely. For tables which are very frequent in transaction
use as minimum as possible a value as your network and system and data volume
allows (refresh process need an amount of time, make the gap larger). For
tables which you want to refresh manually do no specify interval. You have to
refresh manualy.

A sample refresh group script

execute DBMS_REFRESH.MAKE( name => 'programmer.refgrp1', list =>
'programmer.t_exchangeInfo, programmer.t_billInfo', next_date => SYSDATE,
interval => 'SYSDATE+1/24');
COMMIT;

In this example two snapshots t_exchangeInfo and t_billInfo are under refresh
group refgrp1 under programmer schema. Refreshing starts from sysdate and
interval is one hour.

If you are using Schema Manager then the matter is a simple point and shoot
mechanism.

Note : Full table snapsots need exact amount of space as snapshoot table.
This is just a copy with referential integrity checking. In fact when you
create a snapshot log in your master schema yoy see a corresponding
mlog$_xxxx table in your schema which is the table supporting logs or change
vectors. And when you create a snapsot in slave site you have a corresponding
snap$_ table which is just the table in master site. You also have a view
with the same name as your snapshot in your schema. So you gain no space
rather lose some. The benefit is you get a distributed environment which in
your case is required I believe.

There is no way you can have an existing table in slave and use it as or link
it to a snapshot to base table. The original table is just in only one place.
However you can update snapshots and propagate changes to master before
refreshing (updatable snapshots).

If you really need updating an existing table through replication I suggest
using a snapshot in seperate name and have an after trigger for update,
delete, insert on snap$_xxx table for each row so that the data is reflected
in your existing table.

For replication manager the oracle provided guide is enough to operate on I
think

Asif Hasan

Hosted by www.Geocities.ws