Re: Usage of snapshots

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_at_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
spectrum_at_bangla.net
Spectrum Engineering Consortium
Dhaka, Bangladesh

In article <7dis04$1nl$1_at_nnrp1.dejanews.com>,   mkrao_at_my-dejanews.com wrote:
> Hi All
>
> 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
>
> Thanks & regards
> Madhukar Rao
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Mar 29 1999 - 06:08:30 CST