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

Creating Oracle Replication Using OEM

selecting create.  The master database SID is NAVDB, which is also the Net Service Name in the tnsnames.ora file (Figure 4.5).

Figure 4.5 Creating a public database link

Be sure to select the public checkbox.  Selecting the “SHOW SQL” button will cause OEM to display the SQL that will be used to execute the command.  Select OK to execute the command and create the database link.  OEM displays a dialog box to confirm that the link was created (Figure 4.6).

ure 4.6 Database Link Creation Dialog Box

Next, log on as REPADMIN to create the private database link.  To log onto OEM as REPADMIN, you will need to grant REPADMIN the system privilege “SELECT ANY DICTIONARY”.  Navigate back to the Database Link folder, right-click, and select create.  Create the private database link from REPADMIN to REPPROXY (Figure 4.7) using the same link name as the private link created above.

Figure 4.7 Create a Private Database Link

Again, OEM displays a dialog box confirming the creation of the link.  Next, test the link by expanding the Database Link folder, selecting the database link under the REPADMIN user.  Select the “Test” button and OEM will verify that the link is active.

Creating Oracle Replication Using OEM

Step 6 - Create the Master Replication Group

Now log onto the Master database (navdb) as REPADMIN.  Again, you are required to grant “SELECT ANY DICTIONARY” to REPADMIN in order to log on with OEM.  Expand the Distributed tab and the Advanced Replication tab. 

This is the area of OEM that you will use to perform most of your replication maintenance.  Expand the Multi-master Replication folder and you will find the Master Groups folder.  Right-click on the folder and select Create.

In the Name field enter the name of this replication group, REP_GP1.  Select the Objects tab and select ADD.  Here, we are going to add the book table from the PUBS schema.  Use the schema dropdown menu to select the PUBS schema.  Check the “Tables” check box and a list of tables is provided.  Select the book table and press the “ADD” button.  The book table is now listed in the “Selected Objects” text area (Figure 4.8).

Figure 4.8  Selecting Replication Objects in OEM

Selecting OK takes you back to the Objects tab, which now displays the book table.  Selecting the “Show SQL” button shows you the series of commands that this step will execute.  This one step will generate the master replication group, add the book table to the group, generate the replication support, and finally resume replication activity (Figure 4.9).

Creating Oracle Replication Using OEM

ure 4.9  OEM Create Master Group

Selecting “Create” will cause OEM to execute the commands and create the master replication group.  Once created, you can expand the master group folder and the REP_GP1 tab to see the PUBS.BOOK table.  Selecting the REP_GP1 tab will display the status page. 

As you can see, Oracle has used the graphic capabilities of OEM to combine multiple steps for simultaneous execution.  You can highlight and copy the commands out of the “Show SQL” text field to save as a file, which can be very helpful when problems arise.

At this point, we are going to deviate from the steps introduced in the first part of this chapter and add the sales and store tables in the PUBS schema to the master group.  Before we add additional tables to the replication group, we need to stop replication activity.

From the REP_GP1 status page (Figure 4.10), select the “Submit Stop Request” button in the Replication Activity section.  Notice that the status changed from Running (Normal), to Stopped (Quiesced).  Select the Objects tab and then select the Add button at the bottom.  If OEM asks you to set an “alternate key column” on the sales table, then you forgot to set the primary key.  By selecting a column or columns, you are defining a key for use in replication that cannot be used to create a UMV. 

Select the Apply button to add the new tables.  Next, highlight the new tables and press the “Generate Replication Support” button.

Return to the General page and restart replication activity.  The status should return to Running (Normal) (Figure 4.10).

Figure 4.10  Restarting Replication Support

Now, all the tables to be replicated in the PUBS schema are generating replication support and we can create the Updatable Materialized Views in the mydb database.

Step 7 - Create the Updatable Materialized Views

Now that we have the master replication running normally, we are ready to create the updateable materialized views. 

Log onto the UMV site as REPADMIN.  In our case, that is the mydb database.  Again, expand the Distributed tab, the Advanced Replication tab, and in this case, the Materialized View Replication tab (Figure 4.11).

Figure 4.11  OEM Materialized View Replication

As with creating UMVs manually, the first step using OEM is to create a materialized view group.  Right-click on the Materialized View Groups folder and select “Create using Wizard”.  The first thing the wizard asks for is the database link. Select the “All” checkbox to see the available database links.   Select the one you created at the start of this section, in this case (Figure 4.12).

Figure 4.12  Materialized View Group Wizard Link to Master

Selecting the “Next” button will take us to the Master Groups page.  Notice that the master replication group we created on the master database, navdb, is listed.  The wizard polled the dba_repcat data dictionary view across the link to locate the available master replication groups. 

If you received an error and the text area does not display the name of your master group, you may need to grant REPPROXY on the master database the “SELECT ANY DICTIONARY” system grant.  Stop the wizard and restart it to display the list of master groups (Figure 4.13).

Figure 4.13  Materialized View Group Wizard Master Groups

Leave the Asynchronous radio button set and select the master group, in our case REP_GP1.   Once you select the “Next” button, the wizard will use the database link to get a list of replication objects contained in the selected group.  In this case, it is the three tables that are replicated from the PUBS schema (Figure 4.14). 

Figure 4.14  Materialized View Wizard Select Master Objects

Since we are replicating all three tables to the mydb database, use the right-arrow to move the three tables to the Materialized View Group Objects side. 

There is no requirement that the UMV site replicate all the objects in the master group.  It is not uncommon for some sites to replicate a subset of the objects.  As with all parts of replication, more is not better, if you don’t need it, don’t replicate it.

As each table is moved, the wizard gathers more information across the database link.  Selecting Next will display the Materialized View Defaults page. 


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