Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

 

 

   
  Oracle Replication Tips by Burleson

Create the Oracle master replication group REP_GP1

With the master site now tracking changes to the PUBS base tables, we can create the updatable materialized views on the mydb (remote) database.

Create the Oracle Updatable Materialized View at the Remote Database

IMPORTANT: Log onto the UMV site as REPADMIN, not as PUBS.

Step 8.1 - Create a Refresh Group

We first need to create a refresh group on the remote database so that all three UMVs will refresh together.  Notice that we set push_deferred and refresh_after_errors  to TRUE.  Setting push_deferred_rpc tells the refresh group that updates will be pushed back to the master table.  Setting refresh_after_errors to true will allow the UMVs to continue to refresh, even if there are errors in the DEFERROR queue.

BEGIN
  DBMS_REFRESH.MAKE(
    name => '"REPADMIN"."GROUPA"',
    list => '',
    next_date => SYSDATE,
    interval => '/*1:Mins*/ sysdate +
      1/(60*24)',
    implicit_destroy => FALSE,
    lax => FALSE,
    job => 0,
SEE CODE DEPOT FOR FULL SCRIPT
    purge_option => NULL,
    parallelism => NULL,
    heap_size => NULL);
END;
/

Step 8.2 – Create the Materialized View Group

Now, create the materialized view group in the MYDB database.  In order for UMVs to push changes back to

Create the Oracle Updatable Materialized View at the Remote Database

the master tables, the UMVs must belong to a materialized view group.

BEGIN
   DBMS_REPCAT.CREATE_MATERIALIZED VIEW_REPGROUP(
     gname => '"REP_GP1"',
     master => 'NAVDB.WORLD',
     propagation_mode => 'ASYNCHRONOUS');
END;
/
 

This creates a replication group called REP_GP1 on the UMV database that uses the mydb.world database link. It will propagate data asynchronously.

Step 8.3 - Create the BOOK Updatable Materialized View

The next step is to create the actual materialized view on the remote site. Here, we create the BOOK materialized view:

CREATE MATERIALIZED VIEW "PUBS"."BOOK" 
REFRESH FAST FOR UPDATE
AS SELECT * FROM "PUBS"."BOOK"@MYDB.WORLD c

In this CREATE MATERIALIZED VIEW statement, we want to fully qualify the table names, since we are creating a UMV in the PUBS schema as the REPADMIN user.  We could have created and used private or public synonyms, instead of fully qualifying, if that is what we preferred to do.

Create the Oracle Updatable Materialized View at the Remote Database

We could also have placed restrictions on the columns in the UMV by specifying which columns to include, or restricting the rows with a WHERE clause. Here is an example of that type of materialized view:

CREATE MATERIALIZED VIEW "PUBS"."BOOK" 
REFRESH FAST FOR UPDATE
AS SELECT isbm_number, name, publisher
FROM "PUBS"."BOOK"@MYDB.WORLD c

Step 8.4 – Add the Book UMV To the Refresh Group

Since we want the UMVs to replicate together, we add the BOOK updatable materialized view to the refresh group GROUPA.

BEGIN
   DBMS_REFRESH.ADD(
     name => '"REPADMIN"."GROUPA"',
     list => '"PUBS"."BOOK"',
     lax => TRUE);
END;
/

Step 8.5 -  Update The UMV Replication Group

Now, we need to add the BOOK updatable materialized view to the materialized view replication group REP_GP1 that we created earlier.  This creates the replication support needed for two-way replication with the master site.  The BOOK updatable materialized view is now replicating with the book base table on the master database.

BEGIN
   DBMS_REPCAT.CREATE_
MATERIALIZED VIEW_REPOBJECT(
      gname => '"REP_GP1"',

Create the Oracle Updatable Materialized View at the Remote Database

sname => '"PUBS"',
      oname => '"BOOK"',
SEE CODE DEPOT FOR FULL SCRIPT
      min_communication => TRUE);
END;
/

Step 8.6 -  Add Other UMVs

Now follow steps 8.3 through 8.5 to add the other two updatable materialized views (Sales and Store).

CREATE MATERIALIZED VIEW "PUBS"."SALES" 
REFRESH FAST FOR UPDATE
AS SELECT * FROM
"PUBS"."SALES"@NAVDB.WORLD;

CREATE MATERIALIZED VIEW "PUBS"."STORE" 
REFRESH FAST FOR UPDATE
AS SELECT * FROM
"PUBS"."STORE"@NAVDB.WORLD; 

BEGIN
   DBMS_REFRESH.ADD(
     name => '"REPADMIN"."GROUPA"',
     list => '"PUBS"."SALES"',
     lax => TRUE);
END;
/

BEGIN
   DBMS_REFRESH.ADD(
     name => '"REPADMIN"."GROUPA"',
     list => '"PUBS"."STORE"',
     lax => TRUE);
END;

 


This is an excerpt from Oracle Replication By Rampant TechPress (only $19.95).  You can click here to order a copy and get instant access to the code depot:

http://www.rampant-books.com/book_2003_2_replication.htm


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks