Custom Search . . .

Friday, November 2, 2007

Adding Objects in Replication

Alter Replication:

If you want add objects in replication site. (You must be already done Advanced Replication “Click here “)

That objects must be contains primary key constraints.

In scott@US.edkal

SQL> CREATE TABLE U_MASTER

(UNO INT CONSTRAINT U_MASTER_PK PRIMARY KEY,

USERNAME VARCHAR2 (22),

U_PASSWORD VARCHAR2 (22));

Table created.

In repadmin@US.edkal

Here you need temporary stop master site activity and add those objects in master site.

SQL> BEGIN

2 DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (

3 gname => 'us_rep'

4 );

5 END;

6 /

PL/SQL procedure successfully completed.

SQL> BEGIN

2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (

3 gname => 'us_rep',

4 sname => 'SCOTT',

5 oname => 'U_MASTER',

6 type => 'TABLE',

7 copy_rows => TRUE,

8 use_existing_object => TRUE );

9 END;

10 /

PL/SQL procedure successfully completed.

SQL> ED

Wrote file afiedt.buf

1 BEGIN

2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (

3 gname => 'us_rep',

4 sname => 'SCOTT',

5 oname => 'U_MASTER_PK',

6 type => 'INDEX',

7 copy_rows => TRUE,

8 use_existing_object => TRUE );

9* END;

SQL> /

PL/SQL procedure successfully completed.

SQL> CREATE MATERIALIZED VIEW LOG ON SCOTT.U_MASTER;

Materialized view log created.

SQL> BEGIN

2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (

3 sname => 'SCOTT',

4 oname => 'U_MASTER',

5 type => 'TABLE',

6 min_communication => true,

7 generate_80_compatible => FALSE);

8* END;

SQL> /

PL/SQL procedure successfully completed.

SQL> BEGIN

2 DBMS_REPCAT.RESUME_MASTER_ACTIVITY (

3 gname => 'us_rep'

4 );

5 END;

6 /

PL/SQL procedure successfully completed.

Once complete start your master activity.

Create materialized view’s in your replication site.

In scott@mv01.edkal

SQL> CREATE MATERIALIZED VIEW U_MASTER REFRESH FAST WITH PRIMARY KEY UPDATABLE AS SELECT * FROM SCOTT.U_MASTER@US.EDKAL;

Materialized view created.

In mviewadmin@mv01.edkal

SQL> BEGIN

DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(

gname => '"US_REP"',

sname => '"SCOTT"',

oname => '"U_MASTER"',

type => 'SNAPSHOT',

min_communication => TRUE);

END;

PL/SQL procedure successfully completed.

SQL> BEGIN

DBMS_REFRESH.ADD(

name => '"MVIEWADMIN"."US_REP"',

list => '"SCOTT"."U_MASTER"',

lax => TRUE);

END;

PL/SQL procedure successfully completed.

2 comments:

Mohammed Taj said...

Keep it up Babu,

Keep Explore Replication.


Good Luck.

Muhammad Shakeel said...

Dear Babu

It was an excellent doc but i have a query regarding how to add another materialized view site
i think we should have to follow the following Steps

Create materialized view’s in your replication site.


In scott@mv02 (Another MV Site)

SQL> CREATE MATERIALIZED VIEW U_MASTER REFRESH FAST WITH PRIMARY KEY UPDATABLE AS SELECT * FROM SCOTT.U_MASTER@US.EDKAL;


Materialized view created.

We should have first create Mviewadmin at new site and then

In mviewadmin@mv01.edkal

SQL> BEGIN

DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(gname => '"US_REP"',
sname => '"SCOTT"',
oname => '"U_MASTER"',
type => 'SNAPSHOT',
min_communication => TRUE);
END;
PL/SQL procedure successfully completed.

and we also have to create a refresh job and then add
SQL> BEGIN
DBMS_REFRESH.ADD(
name => '"MVIEWADMIN"."US_REP"',
list => '"SCOTT"."U_MASTER"',
lax => TRUE);
END;
/

hence we can accomplish the task (adding Mv Site) ????????

Can u also please guide me how to add a site in Multimaster Replication
please Reply at
s_aazeem@yahoo.com
M.Shakeel