SharePlex replication between two instances on the same host

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.

Overcoming the SharePlex Single-Port Constraint

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.

Preparing the Network and Host Environment

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:

[root@sandbox ~]$ cat /etc/hosts | grep splex 10.177.130.58 splexhost 172.16.128.10 splexstor 

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:

[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 

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.

Installing Source and Target SharePlex Instances

Here is how I started installation for the source:

[oracle@sandbox ~]$ export SP_SYS_HOST_NAME=splexhost [oracle@sandbox ~]$ . oraenv ORACLE_SID = [test] ?  [oracle@sandbox ~]$ cd /u01/distr/SharePlex [oracle@sandbox SharePlex]$ ./SharePlex-8.6.3-b171-oracle100-rh-40-amd64-m64.tpm Unpacking ..................................................................  

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.

Launching the Shared Host Instances

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.

[oracle@sandbox ~]$ export SP_SYS_HOST_NAME=splexhost [oracle@sandbox ~]$ . oraenv [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 & [oracle@sandbox bin]$ telnet splexhost 2100 Trying 10.177.130.58... Connected to splexhost. 

For target you have to adjust your environment variables accordingly and do the same.

[oracle@sandbox ~]$ export SP_SYS_HOST_NAME=splexstor [oracle@sandbox ~]$ . oraenv ORACLE_SID = [test] ? orcl [oracle@sandbox ~]$cd /u01/sp12/bin [oracle@sandbox bin]$ nohup /u01/sp12/bin/sp_cop -usp12 & 

As result we have two SharePlex instances running on the same host and talking to each other.

Configuring and Testing Replication

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:

sp_ctrl (splexhost:2100)> create config sample 

The command opens default editor and you can write your configuration. Here is what I put to my sample config:

datasource:o.test #source tables    target tables    routing map splex.demo_src    splex.demo_dest  splexstor@o.orcl 

Now we can activate config:

sp_ctrl (splexhost:2100)> activate config sample 

Now we can see all the processes running on the source and target. Let's insert a row on the source:

test> insert into splex.demo_src values ('JIM', '8001 Irvine Center Drive', '949-754-8000'); test> commit; 

And we can see the row was successfully replicated to target:

orcl> select * from splex.demo_dest; NAME   ADDRESS                   PHONE# ----   -------                   ------ JIM    8001 Irvine Center Drive  949-754-8000 

Final Observations on Hostname Parameters

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.

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?