 |
|
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: ******
Connected.
SQL>
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 hr.jobs 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;
COUNT(*)
----------
107
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.
|
