SharePlex replication between two instances on the same host

6 min read

May 25, 2016 12:00:00 AM

Several days ago I was asked a question about SharePlex and should verify behaviour before providing the answer. I had one linux VM with two databases and needed to setup replication between them. One of them (orcl) was a target 12c EE database while the second one was a source 10g SE. Accordingly, I should use two different versions of SharePlex to mimic the questionable behaviour, so I should have two different SharePlex instances talking to each other on the same host machine. It worked pretty well for, as an example, GoldenGate, where you just setup different ports for manager processes. However, in SharePlex all the instances participating in a configuration should use the same port. The SharePlex documentation states: "Important! The SharePlex port number must be the same one on all machines in the replication configuration so that they can communicate through TCP/IP connections." Of course you cannot use the same port on the same network interface for two independent SharePlex processes working from different homes. In addition, SharePlex is not asking you about a hostname either during installation, or when you start it. In my case I had 2 interfaces in different subnets on my VM, and I could use them for my replication. But how should I tell to the SharePlex to use one or the other? The answer was simple. You should use "SP_SYS_HOST_NAME" parameter as the environment variable for your shell. Here is how I've done that. I added two new hostnames for those interfaces to my /etc/hosts file to be used for my SharePlex instances: [code lang="text"] [root@sandbox ~]$ cat /etc/hosts | grep splex 10.177.130.58 splexhost 172.16.128.10 splexstor [root@sandbox ~]$ [/code] I unzipped SharePlex 8.6.3 for Oracle 10 and prepared installation. By default the installer will pick up the hostname automatically and use it during installation. Here is my hostname and it is not what I want to use for my first SharePlex instance: [code lang="bash"] [oracle@sandbox ~]$ hostname sandbox.localdomain [oracle@sandbox ~]$ ping sandbox.localdomain PING sandbox.localdomain (127.0.0.1) 56(84) bytes of data. 64 bytes from sandbox.localdomain (127.0.0.1): icmp_seq=1 ttl=64 time=0.058 ms 64 bytes from sandbox.localdomain (127.0.0.1): icmp_seq=2 ttl=64 time=0.061 ms ^C --- sandbox.localdomain ping statistics --- 2 packets transmitted, 2 received, 0% packet loss, time 1468ms rtt min/avg/max/mdev = 0.058/0.059/0.061/0.007 ms [oracle@sandbox ~]$ [/code] What you need to do is to setup an environment variable SP_SYS_HOST_NAME. I wanted the first SharePlex listening and working on hostname splexhost using default port 2100. I should also define proper Oracle home and Oracle database SID during installation. I used standard Oracle utility oraenv to have Oracle variables setup. Here is how I started installation for the source: [code lang="text"] [oracle@sandbox ~]$ export SP_SYS_HOST_NAME=splexhost [oracle@sandbox ~]$ . oraenv ORACLE_SID = [test] ? The /u01/app/oracle/product/10.2.0/sehome_1/bin/orabase binary does not exist You can set ORACLE_BASE manually if it is required. [oracle@sandbox ~]$ cd /u01/distr/SharePlex [oracle@sandbox SharePlex]$ ll total 96736 -rwxr-xr-x. 1 oracle oinstall 99056391 Jan 11 21:56 SharePlex-8.6.3-b171-oracle100-rh-40-amd64-m64.tpm [oracle@sandbox SharePlex]$ ./SharePlex-8.6.3-b171-oracle100-rh-40-amd64-m64.tpm Unpacking .................................................................. .......................................................................... [/code] During the installation I provided directories for program and variable files, port number and information about ORACLE_SID and ORACLE_HOME. Everything went smoothly. I finished the installation by running the "ora_setup" utility creating necessary schema and objects in the source database. For target I used the same strategy setting up Oracle variables by "oraenv" and exporting explicitly SP_SYS_HOST_NAME=splexstor. The installation worked out without any problems and I got two SharePlex instances installed to different directories. To start a SharePlex home for a certain IP address and interface you need to explicitly setup SP_SYS_HOST_NAME to an appropriate value. Let's see how it's been done for source. [code lang="bash"] [oracle@sandbox ~]$ export SP_SYS_HOST_NAME=splexhost [oracle@sandbox ~]$ . oraenv ORACLE_SID = [test] ? The /u01/app/oracle/product/10.2.0/sehome_1/bin/orabase binary does not exist You can set ORACLE_BASE manually if it is required. [oracle@sandbox ~]$ cd /u01/sp10/bin [oracle@sandbox bin]$ telnet splexhost 2100 Trying 10.177.130.58... telnet: connect to address 10.177.130.58: Connection refused [oracle@sandbox bin]$ nohup /u01/sp10/bin/sp_cop -usp10 & [1] 2023 [oracle@sandbox bin]$ nohup: ignoring input and appending output to `nohup.out' [oracle@sandbox bin]$ telnet splexhost 2100 Trying 10.177.130.58... Connected to splexhost. Escape character is '^]'. ^] telnet> q Connection closed. [oracle@sandbox bin]$ [/code] For target you have to adjust your environment variables accordingly and do the same. [code lang="bash"] [oracle@sandbox ~]$ export SP_SYS_HOST_NAME=splexstor [oracle@sandbox ~]$ . oraenv ORACLE_SID = [test] ? orcl The Oracle base has been set to /u01/app/oracle [oracle@sandbox ~]$cd /u01/sp12/bin [oracle@sandbox bin]$ nohup /u01/sp12/bin/sp_cop -usp12 & [1] 2066 [oracle@sandbox bin]$ nohup: ignoring input and appending output to `nohup.out' [oracle@sandbox bin]$ [/code] As result we have two SharePlex instances running on the same host and talking to each other. Now we can create a sample replication. In the database schema "splex" created by "ora_setup" utility we have DEMO_SRC and DEMO_DST tables. SharePlex is using those tables for a demo replication. We can use them too and see how it works in our case. We can either modify a default pre-created sample configuration "ORA_config" or we can create a new one. I've created a new config "sample" on my source SharePlex and activated it: [code lang="text"] sp_ctrl (splexhost:2100)> list config File Name State Datasource -------------------------------------------------- ---------- --------------- ORA_config Inactive o.SOURCE_SID Last Modified At: 17-May-16 11:17 Size: 151 sp_ctrl (splexhost:2100)> create config sample [/code] The command opens default editor and you can write your configuration. Here is what I put to my "sample" config. [code lang="text"] datasource:o.test #source tables target tables routing map splex.demo_src splex.demo_dest splexstor@o.orcl [/code] Now we can activate config. [code lang="text"] sp_ctrl (splexhost:2100)> activate config sample sp_ctrl (splexhost:2100)> list config File Name State Datasource -------------------------------------------------- ---------- --------------- ORA_config Inactive o.SOURCE_SID Last Modified At: 17-May-16 11:17 Size: 151 sample Active o.test Last Modified At: 17-May-16 11:30 Size: 134 Internal Name: .conf.1 sp_ctrl (splexhost:2100)> [/code] Now we can see all the processes running : [code lang="text"] On the source: sp_ctrl (splexhost:2100)> lstatus Detailed Status for splexhost Process State PID Running Since --------------- ------------------------------ -------- -------------------- Cop Running 2023 17-May-16 11:24:39 Capture Running 2250 17-May-16 11:30:53 Data/Host: o.test Read Running 2279 17-May-16 11:30:53 Data/Host: o.test Export Running 2304 17-May-16 11:30:56 Data/Host: splexstor Queue Name: splexhost Cmd & Ctrl Running 2581 17-May-16 11:40:39 Data/Host: splexhost [/code] On the target: [code lang="text"] sp_ctrl (splexstor:2100)> lstatus Detailed Status for splexstor Process State PID Running Since --------------- ------------------------------ -------- -------------------- Cop Running 2066 17-May-16 11:26:23 Import Running 2305 17-May-16 11:30:56 Data/Host: splexhost Queue Name: splexhost Post Running 2306 17-May-16 11:30:56 Data/Host: o.test-o.orcl Queue Name: splexhost Cmd & Ctrl Running 2533 17-May-16 11:38:18 Data/Host: splexstor [/code] Let's insert a row on the source : [code lang="sql"] test> insert into splex.demo_src values ('JIM', '8001 Irvine Center Drive', '949-754-8000'); 1 row created. test> commit; Commit complete. test> [/code] And we can see the row was successfully replicated to target: [code lang="sql"] orcl> select * from splex.demo_dest; NAME ADDRESS PHONE# ------------------------------------------------------------ ------------------------------------------------------------ ------------ JIM 8001 Irvine Center Drive 949-754-8000 orcl> [/code] As you can see, we were able to use one box to replicate data between two different databases, using two different SharePlex installations. The idea was simple and clear from the start, but I couldn't find enough information in the installation guide for the SP_SYS_HOST_NAME parameter except documentation about configuring it for cluster installation. The parameter was documented in the reference section of documentation, though in reality it was not. It could be even better if we could place the parameter inside, and not think about setting variables, but unfortunately setting the parameter in the "paramdb" didn't work for me. Even having the parameter, you still need to setup your environment variable SP_SYS_HOST_NAME=your_host_name for non default hostname. I hope the article may help somebody save a bit of time.

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

SharePlex replication between two instances on the same host

Share this

Share this

More resources

AWS's Top 5 Commands: ALTER SYSTEM and Managing SYS Objects in RDS

Let DataGuard Broker Do ALL The Work

Room for Improvement - Using DBMS_REDEFINITION with Spatial Data

Ready to unlock value from your data?