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

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy





Oracle Replication Tips by Burleson

Create the Oracle Updatable Materialized View at the Remote Database


      gname => '"REP_GP1"',
      min_communication => TRUE);

      gname => '"REP_GP1"',
      sname => '"PUBS"',
      oname => '"STORE"',
      type => 'SNAPSHOT',
      min_communication => TRUE);

At this point, all three updatable materialized views are replicating with the base tables on the master site.  The updates on the master table will be replicated to the UMV, and updates to the UMV will be replicated back to the master table.

There is a bug in Oracle9i that fails to update dba_Materialized View with the correct status.  The compiled_state will show ‘INVALID’ and the refresh fields will be incorrect. 

Create the Oracle Updatable Materialized View at the Remote Database

The last two areas to cover are monitoring UMV replication and establishing methods to resolve primary key collisions. These subjects will be discussed in Chapters 5 and 7. 

Now that we’ve seen how to manually create the updatable materialized views, let’s bring in Oracle Enterprise Manager to simplify these operations.

Creating Oracle Replication Using OEM

Once you have UMV replication running, it is easy to update and monitor it using Oracle’s Enterprise Manager.   OEM is a powerful tool that gets better with each release.  However, one problem with using OEM in building replication is that it performs a number of steps at once, without fully insuring that each step completes successfully. 

This can sometimes result in a report of a successful execution while, in fact, some part did not complete successfully.  In practice, the object will not replicate successfully, but you will not know why it failed. For this reason, use the method above to manually create replication on at least one table, and then use OEM to add additional objects to the replication. 

Let’s start over now and use OEM to build our UMV replication.

It is still necessary to load the replication schemas into dbms_repcat_admin, so we still execute these tasks using the scripts already discussed.  We will begin using OEM at step 5, creating the database links. Since OEM does multiple steps, these steps will not be exactly identical to the previous group of steps.

The master site is still called NAVDB and the UMV site is still MYDB.  Both are Oracle9iR2 databases, so the replication catalog is already installed.

Step 5 - Create the Database Links

After logging onto MYDB as the SYSTEM user, create a public database link by selecting the “Distributed” tab, right clicking on the “Database Link” folder, and



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:

Download your Oracle scripts now:

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 -2017 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