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 Updatable Materialized View at the Remote Database

/

BEGIN
   DBMS_REPCAT.CREATE_
MATERIALIZED VIEW_REPOBJECT(
      gname => '"REP_GP1"',
SEE CODE DEPOT FOR FULL SCRIPT
      min_communication => TRUE);
END;

BEGIN
   DBMS_REPCAT.CREATE_
MATERIALIZED VIEW_REPOBJECT(
      gname => '"REP_GP1"',
      sname => '"PUBS"',
      oname => '"STORE"',
      type => 'SNAPSHOT',
      min_communication => TRUE);
END;
/

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:

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