Thursday, November 25, 2010

xDB Replication from Oracle to PPAS

EnterpriseDB Replication tool has been now modified and add new functionality in it.

Previous Replication Console, which had been made, was using the DBA Management server and Publication & Subscriptions were dependent.

Now, xDB Replication has been divided into two components:
1. Publication Server : (Master Server)
2. Subscription Server: (Slave Server)

Some Background of Publication Server. Publication Server has following components:
1. JAVA Based Daemon.
2. MetaDatabase of Publication Server.

Publication Server keeps its information (Information about the Primary Database, Tables Details etc.) in the MetaDatabase under schema _edb_replicator_pub

Publication server is independent and has no dependency on Subscriptions Server. By default publication server uses the 9011 port. However, if user wants, [S]he can use different ports too.

For running Publication Server on different port, use the following command:
$EDBHOME/jre/bin/java -Djava.awt.headless=true -jar $EDBHOME/bin/edb-repserver.jar pubserver 

Similarly, Subscription Server has two components:
1. JAVA Based Daemon
2. MetaDatabase of Publication. (which Subscription server uses for Keeping the information of Subscriptions)

In Metadatabase, xDB Subscription server keeps the information in _edb_replicator_sub.

By Default xDB Replication runs on port 9012. However if user wants [S]he can run the daemon on different port too, by using following command:
$EDBHOME/jre/bin/java -Djava.awt.headless=true -jar $EDBHOME/bin/edb-repserver.jar subserver 
Now, Questions comes, How to use the daemon for Publication and Subscriptions?

EnterpriseDB provides a jar file edb-repcli.jar in $EDBHOME/bin which can be use.

edb-repcli.jar has been exhausted with lots of options using which user can Create a Publication/Subcriptions, can add the database etc.

To find more of its options, user can use following command:
$EDBHOME/jre/bin/java -jar $EDBHOME/bin/edb-repcli.jar -help

With above, user has to create a two configuration files:
1. Publication Configuration file
2. Subscription Configuration file.

Format of Configuration files of Publication and Subcription are same.
Following is a snapshot and description of configuration file:
user=enterprisedb # User using which xDB Publication/Subscription can connect with MetaDAtabase
port=9011 # Port on  which Publication/Subscription Daemon is running.
type=enterprisedb # There are two of database which xDB Replication Daemon can use one EnterpriseDB databases (Postgres Plus Advanced Or postgres (PostgreSQL)
host=localhost # Host on which Publication Daemon is running

With above configuration file of Publication/Subscription configuration files, there is one more xDB Server configuration file. This file by default exists in /etc/edb-repl.conf. This file contains the information of xDB Metadabase.
Following is a snapshot of this file:
port=5445 (port on which xDB Replication is running)

Few Handy Commands of edb-repcli.jar is given below:

1. To view list the publications, user can use following command:
$EDBHOME/jre/bin/java -jar $EDBHOME/bin/edb-repcli.jar -printpublist -repsvrfile 
2. To view the published tables of a publication, following command:
$EDBHOME/jre/bin/java -jar $EDBHOME/bin/edb-repcli.jar -printpublishedtables  -repsvrfile 

3. To list the subscriptions of a particular subscription database, following command can be use:
$EDBHOME/jre/bin/java -jar $EDBHOME/bin/edb-repcli.jar -printsublist -repsvrfile  -subdbid 

4. To print the subscribed database ids following command can be use:
$EDBHOME/jre/bin/java -jar $EDBHOME/bin/edb-repcli.jar -printsubdbidsdetails -repsvrfile 

5. To add the Databases [Oracle|EnterpriseDB] for publication, following command can be use:
$EDBHOME/jre/bin/java -jar $EDBHOME/bin/edb-repcli.jar -addpubdb -repsvrfile  -dbtype {oracle | enterprisedb | postgresql} -dbhost  -dbport  -dbuser  -dbpassword  -database { | } [-oraconnectiontype {sid | servicename}]

6. To print the published Databases in Publication Server, following command can be use:
$EDBHOME/jre/bin/java -jar $EDBHOME/bin/edb-repcli.jar -printpubdbidsdetails -repsvrfile 

To control the behavior/performance of Publication/Subscription daemon, EnterpriseDB provides a two important files.
1. xdb_pubserver.conf
2. xdb_subserver.con

Location of above two files is $EDBHOME/etc.

Information of the contents of these two files can be find in following link:

Details of Permitted Source and Target databases can be find in following link:

1 comment:

  1. Thanks very much for this post. I don't find many posts regarding EnterpriseDB and this one was very helpful. Their documentation for the command line client is sparse and does not indicate that you require 2 authentication files to use the CLI.