EnterpriseDB: Configure Oracle Administrator
Oracle Tips by
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).
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
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).
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.
steps in creating the user are:
Create Any Trigger
Select Access on Replicated Tables
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;
SQL> grant connect, resource to rrepadmin;
SQL> grant create any trigger to rrepadmin;
SQL> grant select on hr.employees to rrepadmin;
SQL> grant select on hr.departments to rrepadmin;
SQL> grant select on hr.countries to rrepadmin;
SQL> grant select on hr.jobs to rrepadmin;
SQL> grant select on hr.regions to rrepadmin;
SQL> grant select on hr.locations to rrepadmin;
SQL> select count(*) from hr.employees;
Configure Replication Management Server
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.
7.4: Register Replication Management Server
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:
– Publisher information such as database, publisher/subscriber links
and log records
- Subscription information
– Replication schedule information
tables are automatically created by the replication console.
Starting the Services
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
7.5: Start Replication Services
Configure the Publisher (Oracle)
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).
7.6: Configure Publisher (Source, Oracle)
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).
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.
7.7: Configure Publisher Preferences
Configure the Subscription
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 the Subscription Service – Add Database dialog (Figure 7.8), right
click on the running Subscription Service node in the navigator and
choose Add Database.
7.8: Configure Subscriber (Target, EnterpriseDB)
the required information and choose save. You can choose test to
verify that your connection information is correct.
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.