Re: Replication

Hi there,

First of all;

Get some documentation, and read,read,read!

Replication is not a piece of cake.

At least not for me it is ;o)

Now;

Do you need it to be an Updatable snapshot? Or just the same as the rest, only the other way around?

If it's the second option, just do what you did with all the other MV's, only the other way around :)

If its the first option;

(I'm assuming you're using Oracle 9ir2, as you neglected to tell us which version you are using, which OS, etc.etc..)

--> Please keep in mind that I am NOT a Senior-DBA with a lot of experience! Below is the way I do it (except some steps for the configuration of sequences, triggers, etc.). I could be wrong on some/all points, or the order I do things! But I'm sure (and I hope) I will be corrected by some people here if I am :)

-Create schema's for the replication-owner, the propagator, etc..

  see the documentation for the details.

• Create database links on both the master and the snapshot-db.

-Connect to the Master-db as the replication-owner.

-Create a replication-group:

begin

  DBMS_REPCAT.CREATE_MASTER_REPGROUP(         gname => '"REPGROUP"',

        qualifier => '',

        group_comment => '');

end;

/

-Add the table to the Replication group:

begin

  DBMS_REPCAT.CREATE_MASTER_REPOBJECT(         gname => 'REPGROUP',

        type => 'TABLE',

        oname => 'TABLE_NAME',

        sname => 'SCHEMA_NAME',

        copy_rows => TRUE,

        use_existing_object => TRUE);

end;

/

-Generate replication support for the table:

begin

  DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(         sname => 'SCHEMA_NAME',

        oname => 'TABLE_NAME',

        type => 'TABLE',

        package_prefix => '',

        procedure_prefix => '',

        distributed => TRUE,

        gen_objs_owner => '',

        min_communication => TRUE);

end;

/

-Initiate Master activity:

begin

  DBMS_REPCAT.RESUME_MASTER_ACTIVITY(         gname => 'REPGROUP',

        override => FALSE);

end;

/

• Create a snapshot log:

CREATE SNAPSHOT LOG     ON SCHEMA_NAME.TABLE_NAME TABLESPACE "TS" WITH PRIMARY KEY EXCLUDING NEW VALUES /

-Connect to the Snapshot-db as the replication-owner.

-Create a snapshot:

Create snapshot SCHEMA_NAME.TABLE_NAME

TABLESPACE "TS" BUILD IMMEDIATE USING INDEX TABLESPACE "TS_IDX" refresh fast

with primary key

for update

as select * from SCHEMA_NAME.TABLE_NAME_at_DB_LINK_TO_MASTER;

-Create a snapshot replication group:

EXECUTE DBMS_REPCAT.CREATE_SNAPSHOT_REPGROUP( - gname => 'REPGROUP', -

master => 'MASTER-DB', -

comment => '', -

propagation_mode => 'ASYNCHRONOUS');

• Add the snapshot to the replication-group:

execute dbms_repcat.create_snapshot_repobject( -

sname => 'SCHEMA_NAME', -

oname => 'TABLE_NAME', -

type => 'SNAPSHOT', -

gname => 'REPGROUP');

-Create a Refreshgroup:

BEGIN    DBMS_REFRESH.MAKE(name => '"REFRESH_GROUP"'

,list => ''

,next_date => SYSDATE

,interval => '/*1:15 min*/ sysdate + 15/(60*24)'

,implicit_destroy => FALSE

,lax => FALSE

,job => 0

,rollback_seg => NULL

,push_deferred_rpc => TRUE

,refresh_after_errors => TRUE

,purge_option => NULL

,parallelism => NULL

,heap_size => NULL);

END; /

-Add snapshot to the refreshgroup:

BEGIN    DBMS_REFRESH.ADD(name => '"REFRESH_GROUP"',

                    list => '"SCHEMA_NAME"."TABLE_NAME"',

                    lax  => TRUE);

END; /

Okay..

That's it I guess..

Ofcourse, If you use sequences, triggers, etc. there's a lot more to think about, but that's what documentation is for :)

Good luck,

Victor.

Originally posted by Kalle

> Hi all,

>

> I am currently using materialized views for replication from Master to

> two sites, and it is working perfectly.

>

> Now there is one table I need to replicate from these sites to > a Master.

>

> How do I setup this and do you have any instructions for ...

>

> thank you in advance...

>

> rgds

Kalle

--
Posted via http://dbforums.com