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: Configure Oracle Administrator
Oracle Tips by Burleson

Now that I have verified that my databases are ready, I must perform some administrative set up in the Oracle database.  EnterpriseDB replication is accomplished by creating data structures and triggers in the source database (which I will cover in detail below).

To allow this, I must configure a replication administrator account in the Oracle database.  This administrative user requires specific grants to successfully create the tables and triggers that it needs to function.

You can run the commands below to create the replication administrator user.  You can call the user anything that meets your naming standards.  I chose to use rrepadmin (I got that from the EnterpriseDB example).

While not required, you may want to assign a default tablespace to the replication administrator account so that any created objects will be in that location. 

The steps in creating the user are:

* Create the user

* Grant Create Any Trigger

* Grant Select Access on Replicated Tables

Those are the critical steps on the Oracle side of the equation.  Below is an example of the code I executed to set up this example.

SQL> conn sys@ora92 as sysdba
Enter password: ******
SQL> create user rrepadmin identified by repl; 

User created.

SQL> grant connect, resource to rrepadmin; 

Grant succeeded.

SQL> grant create any trigger to rrepadmin;

Grant succeeded.

SQL> grant select on hr.employees to rrepadmin;

Grant succeeded.

SQL> grant select on hr.departments to rrepadmin;

Grant succeeded.

SQL> grant select on hr.countries to rrepadmin;

Grant succeeded.

SQL> grant select on to rrepadmin;

Grant succeeded.

SQL> grant select on hr.regions to rrepadmin;

Grant succeeded.

SQL> grant select on hr.locations to rrepadmin;

Grant succeeded.

SQL> select count(*) from hr.employees;


Configure Replication Management Server

The first step within the console is to configure the management server (Figure 7.4).  This defines the database that will act as the repository for all of your replication jobs.  In the Replication Console, right click on the Replication Servers node and choose Register Management Server.

Figure 7.4: Register Replication Management Server

Enter the information and press the Register button.  After registering the replication management server, you should have an additional node in the navigator, DBA Management Server. 

Registering the Replication Management Server creates three schemas in the identified database.  The schemas are:

* _edb_replicator_pub – Publisher information such as database, publisher/subscriber links and log records

* _edb_replicator_sub -  Subscription information

* _edb_scheduler – Replication schedule information

These tables are automatically created by the replication console.

Starting the Services

Click on the plus (+) next to the DBA Management Server node and you will see the Publication Service and the Subscription Service in the Navigator (Figure 7.5).  Right-click on both of those and choose Start Service.

Figure 7.5: Start Replication Services

Configure the Publisher (Oracle)

Once you have the publication and subscription services running, right click on the Publication Service and choose Add Database.  This will launch the Publication Service – Add Database dialog (Figure 7.6).

Figure 7.6: Configure Publisher (Source, Oracle)

The publication service is the replication server's connection to the Oracle database.  The user field is the schema that will contain the tables and triggers required for replication (the Oracle replication administration user). 

In addition to configuring the connection, you may also configure a cleanup job (Figure 7.7).  The cleanup job will remove successfully replicated data on a scheduled basis.  The frequency of the cleanup job will be dependant on the load on your Oracle database and the volume of transactions being replicated.

Figure 7.7: Configure Publisher Preferences

Configure the Subscription

The Subscription is the target database.  This database may or may not be the same database as the DBA Management Server.  In this example, it is the same but I would recommend a stand-alone database in a production environment.

To get to the Subscription Service – Add Database dialog (Figure 7.8), right click on the running Subscription Service node in the navigator and choose Add Database.

Figure 7.8: Configure Subscriber (Target, EnterpriseDB)

Enter the required information and choose save.  You can choose test to verify that your connection information is correct.


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