In this article, we are sharing the steps required to setup the SMR replication between Oracle and EPAS databases which are running on different hosts. Below are the high level steps to be performed for setting up SMR replication using CLI (CommandLine Interface).
- Install, configure and start the xDB publication service on xDB host
- Password Encryption
- Add Publication DB node
- Create Publication
- Add Subscription DB node
- Create Subscription
- Take Snapshot
- Synchronize
- Schedule synchronization
Where,
To set up the xDB MMR/SMR replication, we use the edb-repcli.jar (located at /usr/edb/xdb/bin after successful installation of EPRS) with command line arguments.
SMR Setup Details
| Node Type | Host | DB Port | DB Name | User Name |
| Publication | 172.22.14.39 | 5445 | xdb_demo | enterprisedb |
| Subscription | 172.22.14.40 | 5445 | xdb_Demo1 | enterprisedb |
| xDB Host | 172.22.14.44 | - | - | - |
Publication Name : ora_pub
Publication table list : kishor.test1,kishor.test2
The EPRS/xDB server is installed on separate host (172.22.14.44) so all the given below steps are performed on xDB host (172.22.14.44).
- Port no. 9051 and 9052 should be open on xDB host.
- The xDB Publication service runs on 9051 and xDB subscription service runs on 9052 port.
- The pg_hba.conf entries should be configured on all databases node to be participated in the xDB replication setup. This is to allow the database connection from xDB host.
- Publication tables must have the primary key.
- Publication database user should be granted with CREATE ANY TRIGGER, RESOURCE and CONNECT privileges
Refer EPRS/xDB installation steps.
Note: The edb-repcli.jar (which is inside the binaries location of the EPRS i.e. /usr/edb/xdb/bin) to be used to execute the xDB/EPRS commands for setting up the SMR replication.
Step 1: Start the publication and Subscription services
Before starting the SMR setup, we have to start the publication service on xDB host (172.22.14.44)
For SMR replication Publication and Subscription both service needs to be running.
Steps to start Publication service
Step 1.1: Copy service file edb-xdbpubserver.service and edb-xdbsubserver.service from /usr/lib/systemd/system to /etc/systemd/system
[root@localhost bin]# cp /usr/lib/systemd/system/edb-xdbpubserver.service /etc/systemd/system/
[root@localhost bin]# cp /usr/lib/systemd/system/edb-xdbsubserver.service /etc/systemd/system/
Step 1.2: Start the publication service
[root@localhost bin]# systemctl start edb-xdbpubserver.service
[root@localhost bin]# systemctl start edb-xdbsubserver.service
Step 1.3: Check the status of the publication service
[root@localhost bin]# systemctl status edb-xdbpubserver.service
[root@localhost bin]# systemctl status edb-xdbsubserver.service
Please refer service status output for service status command output
xDB Service Status
● edb-xdbpubserver.service - Publication Server Service script for Linux
Loaded: loaded (/etc/systemd/system/edb-xdbpubserver.service; disabled; vendor preset: disabled)
Active: active (running) since Mon 2019-08-26 16:43:45 IST; 43min ago
Process: 9398 ExecStartPost=/bin/bash -c touch /var/lock/edb-xdbpubserver/edb-xdbpubserver (code=exited, status=0/SUCCESS)
Process: 9387 ExecStart=/bin/bash -c cd /usr/ppas-xdb-6.2/bin; ./runPubServer.sh >> /var/log/edb-xdbpubserver/edb-xdbpubserver.log 2>&1 & (code=exited, status=0/SUCCESS)
Main PID: 9388 (bash)
CGroup: /system.slice/edb-xdbpubserver.service
├─9388 /bin/bash -c cd /usr/ppas-xdb-6.2/bin; ./runPubServer.sh >> /var/log/edb-xdbpubserver/edb-xdbpubserver.log 2>&1 &
└─9425 /usr/bin/java -XX:-UsePerfData -Xms256m -Xmx1536m -XX:ErrorFile=/var/log/xdb-6.2/pubserver_pid_%p.log -Djava.library.path=/usr/ppas-xdb-6.2/bin -Djava.awt.headless=true -jar /usr/ppas-xdb-6.2/bin/edb-repserver.jar pub...
Aug 26 16:43:45 localhost.localdomain systemd[1]: Starting Publication Server Service script for Linux...
Aug 26 16:43:45 localhost.localdomain systemd[1]: Started Publication Server Service script for Linux.
Step 2: Password encryption
need to encrypt the Publication db ,Subscription db,admin user passwords while setting up the SMR replication in xDB.
encrypt the Publication\Subscription database user passwords using -encrypt subcommand.
Syntax
java -jar edb-repcli.jar
-encrypt ## Subcommand to encrypt the password
-input <path_of_input_file> ## file path which stores the plaintext password to be encrypted
-output <path_for_output_file> ## file path which will hold the encrypted password
Example
[root@localhost bin]# java -jar edb-repcli.jar -encrypt -input ../etc/infile.txt -output ../etc/outfile.txt
[root@localhost bin]# cat ../etc/infile.txt
kishor1234
[root@localhost bin]# cat ../etc/outfile.txt
hV66jbp9L8bvLxZ0Bea/tw==
[root@localhost bin]#
Default publication server admin user credentials file
[root@localhost bin]# cat /etc/edb-repl.conf
admin_user=admin
admin_password=SJ70z8Gk0zY= ## Plain text password is edb
Create publication and subscription server configuration file
Create the publication and subscription server configuration file by specifying publication and subscription server details (like publication/subscription server host, port, user and password)
cat /usr/edb/xdb/etc/pubsrv.conf
host=localhost
port=9051
user=admin
password=SJ70z8Gk0zY=
cat /usr/edb/xdb/etc/subsrv.conf
host=localhost
port=9052
user=admin
password=SJ70z8Gk0zY=
Step 3: Add Publication database node
Syntax
java -jar edb-repcli.jar
-addpubdb ## Sub command to add publication database
-repsvrfile <publ_server_conf_file> ## Path of Publication server configuration file
-dbtype oracle ## Type of database enterprisedb or postgresql (in case of opensource)
-dbhost <oracle_host_ip> ## Host ip of the database server which is going to be publication database
-dbport <oracle_db_port> ## Port no. of the database server which is going to be publication database
-dbuser <oracle_user> ## DB user (superuser) of the database server which is going to be publication database
-dbpassword hV66jbp9L8bvLxZ0Bea/tw== ## encrypted password of the db user of the database server which is going to be publication database
-database <oracle_db_name> ## database name of the database server which is going to be publication database
-repgrouptype <replication_group_type> ## replication type i.e. s . s for smr
Example
[root@kishor bin]# java -jar edb-repcli.jar -addpubdb -repsvrfile /usr/edb/xdb/etc/pubsrv.conf -dbtype oracle -dbhost 172.16.139.127 -dbport 1522 -dbuser kishor -dbpassword C42FICcoopM= -database mydb -repgrouptype s
Adding publication database...
Publication database added successfully. Publication database id:1
Step 4: Create publication(Set of tables to be published)
Syntax
java -jar edb-repcli.jar
-createpub <publication_name> ## Sub command to create publication e
-pubid <id_of_the_pub_db_added> ## ID of publication database
-repsvrfile <publ_server_conf_file> ## Path of Publication server configuration file
-reptype <type_of_replication> ## Type of replication t for synchronization and s for snapshot only
-tables <space_seperated_tables_list> ## List of schema qualified tables to be published
-repgrouptype <replication_group_type> ## replication group type i.e. s . s for smr
Example
java -jar edb-repcli.jar -createpub ora-pub -pubdbid 1 -reptype t -tables KISHOR.TEST1 KISHOR.TEST2 -repsvrfile /usr/edb/xdb/etc/pubsrv.conf -repgrouptype s
Creating publication...
Tables:[[KISHOR.TEST1, TABLE], [KISHOR.TEST2, TABLE]]
Filter clause:[]
Publication created.
Step 5: Add Subscription DB node
Note : Before adding a Subscription node make sure that Publication db node and publication is created.
Syntax
java -jar edb-repcli.jar
-addsubdb ## Sub command to add publication database
-repsvrfile <sub_server_conf_file> ## Path of Subscription server configuration file
-dbtype <epas_db_user> ## Type of database server
-dbhost <epas_db_host> ## hostname/IP of database server
-dbport 5444 -dbuser <epas_db_user> ## Username of database server
-dbpassword <encrypted_password> ## encrypted password of database user
-database xdb_demo ## Database name from database server to be added as publication database
Example
java -jar edb-repcli.jar -addsubdb -repsvrfile /usr/edb/xdb/etc/subsrv.conf -dbtype enterprisedb -dbhost 127.0.0.1 -dbport 5444 -dbuser enterprisedb -dbpassfile /tmp/output.txt -database xdb_demo
Adding Subscription Database...
Subscription database added successfully. Subscription Database id:19
Step 6: Create subscription
Syntax
java -jar edb-repcli.jar
-createsub ## name of the subscription
-subsvrfile <sub_svr_conf_file> ## Path of Subscription server configuration file
-pubsvrfile <sub_svr_conf_file> ## Path of Publication server configuration file
-subdbid <subscription_db_id> ## ID of subscription database
-pubname <pub_name> ## name of the publication to be subscribed
Example
java -jar edb-repcli.jar -createsub sub1 -subsvrfile /usr/edb/xdb/etc/subsrv.conf -subdbid 19 -pubsvrfile /usr/edb/xdb/etc/pubsrv.conf -pubname ora-pub
Creating subscription...
Subscription created successfully
Step 7: Snapshot operation
Using this command, take the initial snapshot from MDN database node to all non-MDN database node for specific publication.
Syntax
java -jar edb-repcli.jar
-dosnapshot <sub_name> ## Subcommand for snapshot operation in SMR with subscription name
-repsvrfile <sub_server_conf_file> ## Path of Subscription server configuration file
Example
java -jar edb-repcli.jar -dosnapshot sub1 -repsvrfile /usr/edb/xdb/etc/subsrv.conf -verboseSnapshotOutput true
Please refer snapshot command output for dosnapshot command output
Snapshot command output
Performing snapshot...
Running EnterpriseDB Migration Toolkit (Build 52.0.1) ...
Source database connectivity info...
conn =jdbc:oracle:thin:@172.16.139.127:1522:mydb
user =kishor
password=xxxxx
Target database connectivity info...
conn =jdbc:edb://127.0.0.1:5444/xdb_demo?loginTimeout=60&connectTimeout=30&socketTimeout=900
user =enterprisedb
password=xxxxxx
Connecting with source Oracle database server...
Connected to Oracle, version 'Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options'
Connecting with target EDB Postgres database server...
Connected to EnterpriseDB, version '11.1.7'
Warning! Character set on source Oracle is US7ASCII
Warning! Character set on target EDB Postgres is UTF8
Importing redwood schema KISHOR...
Table List: 'TEST1','TEST2'
Loading Table Data in 8 MB batches...
Disabling FK constraints & triggers on kishor.test1 before truncate...
Truncating table TEST1 before data load...
Disabling indexes on kishor.test1 before data load...
Loading Table: TEST1 ...
[TEST1] Migrated 100000 rows.
[TEST1] Table Data Load Summary: Total Time(s): 1.448 Total Rows: 100000 Total Size(MB): 1.7900390625
Disabling FK constraints & triggers on kishor.test2 before truncate...
Truncating table TEST2 before data load...
Disabling indexes on kishor.test2 before data load...
Loading Table: TEST2 ...
[TEST2] Migrated 100000 rows.
[TEST2] Table Data Load Summary: Total Time(s): 1.473 Total Rows: 100000 Total Size(MB): 1.7900390625
Enabling FK constraints & triggers on kishor.test1...
Enabling indexes on kishor.test1 after data load...
Enabling FK constraints & triggers on kishor.test2...
Enabling indexes on kishor.test2 after data load...
Performing ANALYZE on EnterpriseDB database...
Data Load Summary: Total Time (sec): 3.509 Total Rows: 200000 Total Size(MB): 3.666
Schema KISHOR imported successfully.
Migration process completed successfully.
Migration logs have been saved to /var/log/xdb-6.2
Migration Summary
Tables: 2 out of 2
Total objects: 2
Successful count: 2
Failed count: 0
Invalid count: 0
Performing post-Snapshot implicit Synchronize operation for target database 127.0.0.1:5444/xdb_demo...
The post-Snapshot implicit Synchronize operation for target database 127.0.0.1:5444/xdb_demo completed successfully.
Snapshot taken successfully.
Step 8: Synchronization operation
Note: Before run the synchronize operation on any subscription node make sure that, initial snapshot has been taken prior to synchronize operation.
Syntax
java -jar edb-repcli.jar
-dosynchronize <sub_name> ## Subcommand for synchoronization operation with subscription name
-repsvrfile <sub_server_conf_file> ## Path of subscription server configuration file
Example
java -jar edb-repcli.jar -dosynchronize sub1 -repsvrfile /usr/edb/xdb/etc/subsrv.conf
Performing synchronize...
Synchronize done successfully.
Step 9: Synchronization Schedule
Automate the data synchronization replication by defining the schedule for data synchronize operation. In below example, defined the schedule of 5 seconds interval between two synchronize replication operation.
Syntax
java -jar edb-repcli.jar
-confschedulemmr 1 ## subcommand to configure MMR schedule
-pubname pub_db ## name of Publication created on MDN node
-repsvrfile /usr/edb/xdb/etc/publication_server.conf ## Path of Publication server configuration file
-realtime 5 ## time interval for next synchronisation operation(here 5 seconds)
Example
java -jar edb-repcli.jar -confschedule sub1 -repsvrfile /usr/edb/xdb/etc/subsrv.conf -jobtype t -realtime 3
Configuring scheduler ...
Job is successfully scheduled
Further, you can verify the synchronization of data changes from Oracle to EPAS by executing the transactions like INSERT/UPDATE/DELETE/TRUNCATE to modify the data in publictaion tables in Oracle database and verifying the effects of the transactions in subscription database.
Related to