Materialized view replication test

Materialized view replication test between oracle 11gr2 Enterprize edition  and 11g express edition.

 We have two databases domdb( 11gr2 enterprise edition) and xe(express edition).

Objective:  We want to test data replication between domdb and xe databases.

Database username is rep and table name is to_be_replicated in both databases

I have created link between xe and domdb from both sides.

 SQL> CONN rep/rep@domdb
SQL>  CREATE public DATABASE LINK REP CONNECT TO REP IDENTIFIED BY REP USING 'XE';
Database link created.
 SQL> CONN rep/rep@xe
Connected.
SQL>  CREATE public DATABASE LINK REP CONNECT TO REP IDENTIFIED BY REP USING 'domdb';
Database link created.

 

Create table which data to be replicated

 SQL> conn rep/rep@domdb
Connected.
SQL> create table to_be_replicated (
  a    number        primary key,
  b    varchar2(20)
);  2    3    4 

Table created.

SQL> conn rep/rep@xe
Connected.
SQL> ;
  1  create table to_be_replicated (
  2    a    number   primary key,
  3    b    varchar2(20)
  4* )
SQL> /

Table created.

 

Same way I have created Materialized view in both databases.

 SQL> conn rep/rep@domdb
Connected.
SQL> create materialized view replicated_table
for update
as
select * from rep.to_be_replicated@xe;
  2    3    4 
Materialized view created.

SQL> conn rep/rep@xe
Connected.
SQL> ;
  1  create materialized view replicated_table
  2  for update
  3  as
  4* select * from rep.to_be_replicated@xe
SQL> create materialized view replicated_table
for update
as
select * from rep.to_be_replicated@domdb;  2    3    4 

Materialized view created.

 

Insert Records in domdb

SQL> conn rep/rep@domdb
Connected.
SQL> insert into to_be_replicated values (5, 'intodomdb');
1 row created.
SQL> insert into to_be_replicated values (6, 'intodomdb');
1 row created.
SQL> commit;
Commit complete.

SQL> conn rep/rep@xe

Connected.

SQL> select * from replicated_table;
no rows selected

It is not showing any data because Materialized view needs to be refreshed.

 SQL> begin
  dbms_mview.refresh('REPLICATED_TABLE');
end;
/  2    3    4 
 PL/SQL procedure successfully completed.

 

SQL> select * from replicated_table;

                A B
---------- --------------------------------------------------
                5 intodomdb
                6 intodomdb

 

 

Now i am inserting records in xe database

 SQL> conn rep/rep@xe
Connected.
 SQL> insert into to_be_replicated values (7, 'intoxedb');
 1 row created.
 SQL> insert into to_be_replicated values (8, 'intoxedb');
 1 row created.
 SQL> commit;
 Commit complete.
 SQL> conn rep/rep@domdb
Connected.

Refresh Materialized view

 

SQL>  begin
  dbms_mview.refresh('REPLICATED_TABLE');
end;
/   2    3    4 

PL/SQL procedure successfully completed.

SQL> select * from replicated_table;
                 A B
---------- --------------------
                7 intoxedb
                8 intoxedb

It means data is replicating from both sides

Advertisements

About Sher khan

Senior Oracle DBA, Oracle 10g , Oracle 11g OCE ,Oracle 12C and Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g , 11g and 12C. I am working in Oracle technology since 2004. Currently working in U.A.E Email: sher487@hotmail.com
This entry was posted in Materialized view. Bookmark the permalink.

2 Responses to Materialized view replication test

  1. Hi there, I discovered your blog by the use of Google while searching for a related subject, your
    web site got here up, it seems to be great.
    I have bookmarked it in my google bookmarks.

    Hi there, just became alert to your blog thru Google, and located that
    it is really informative. I’m going to be careful for brussels.

    I’ll appreciate should you proceed this in future.
    A lot of people might be benefited out of your writing. Cheers!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s