Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




EnterpriseDB: Verify EDB Schema
Oracle Tips by Burleson

The last few steps in this example are to demonstrate how to verify a replication set up.  The first step in verification is to ensure that the new HR schema exists in EnterpriseDB (our snapshot should have created that).

We can check this by opening Developer Studio and check that a new HR schema exists (Figure 7.21)

Figure 7.21: Verify Schema Created in EnterpriseDB

I can see in the figure above that the tables I selected for replication: countries, departments, employees, jobs, locations and regions, do exist in the edb database.

Verify Oracle Configuration

The next step is to verify that the expected triggers were created for the HR objects (Figure 7.22).

Figure 7.22: Verify Triggers in Oracle

I will also verify that the shadow tables were created in the rrepadmin schema (Figure 7.23) in Oracle.

Figure 7.23: Verify Replication Administrator Tables

Insert Source Data

Now that I am sure that all of the expected objects exist in both Oracle and EnterpriseDB, I will insert a record in Oracle and then monitor EnterpriseDB to see it arrive.

Figure 7.24 shows an Oracle SQL*Plus session connecting to the HR account and inserting a record into the HR.locations table.  Before inserting the row, I did a describe to see which columns were not null (so I could successfully insert a row) and then selected any rows with a location ID of 9999 to make sure I was inserting a unique row.

Figure 7.24: Create a Test Record

Select Target Data

After successfully inserting a row in Oracle, I check the EnterpriseDB instance to see if the new row arrives.  The schedule was configured as continuous (execute every 10 seconds).

It will take at least 10 seconds before I see the transactions.  In Figure 7.25, I run the command several times before the transaction appears.

Figure 7.25: Verify Data Synchronization

The error in the above screenshot was a typo on my part.  Not an error in replication.


I feel that this chapter is one of the most important in this book.  To me, the success of EnterpriseDB Advanced Server is not in how it replaces Oracle, but in how it works with Oracle.  The EnterpriseDB Replication Sever allows Oracle and EnterpriseDB to work together in your data center.

The EnterpriseDB Replication Server is integrated with the DBA Management Server instance and is optionally available to premium subscribers.  The replication console is the GUI configuration and monitoring tool.

The replication works via a repository based, publish/subscribe model.  Publications and subscriptions are maintained in a central repository and the replication server maintains the appropriate connections and refresh schedules.

Figure 7.26 displays a simple diagram outlining the architecture of the replication server and its relationship to the Oracle source databases and the EnterpriseDB target databases.

Figure 7.26: Simplified Replication Architecture

Implementation of Oracle to EnterpriseDB replication via the replication console can be reduced to a checklist:

* Configure Oracle Replication Administrator

* Create any trigger

* Select on replicated tables

* Configure Replication Management Server

* Start Replication Services (Publish and Subscribe)

* Configure the Publisher (Oracle)

* Configure the Subscriber (EnterpriseDB)

* Create the Publication (List of replicated objects)

* Create the Subscriptions (Who gets what)

* Take a snapshot (Create replicated objects in target)

* Synchronize (Play catch-up)

* Schedule

* Verify

* Test

If you follow the checklist in order, any errors will prevent you from continuing through the process until they are fixed.  That will save you from having to redo steps later.

One thing to watch out for is performance impacts due to the triggers being added to the source database.  Testing replication does not just mean testing data propagation; you must test all of the pieces to ensure a quality implementation.

This chapter ends the discussion of EnterpriseDB tools.  The next and last chapters discuss compatibility with Oracle and migrating a sample application.


This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



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

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter