What is SP_SYS_HOST_NAME?
SP_SYS_HOST_NAME is a useful SharePlex environment variable and also a parameter. Properly set and used, it can be used to enhance performance and improve reliability. if it's not set correctly, it can be a source of issues and frustration.
Why is this so important?
A typical SharePlex installation involves moving data between databases on two or more servers, or hosts, connected by a TCP/IP network. Each host will have at least one IP address that uniquely identifies that specific host. In addition to a string of numbers (the IP address), we can give each host a more "recognizable" name, like "OracleSource1" or "HRDBServer". To further complicate things, each host can be part of a "domain" or collection of hosts. A domain name might be something like "XYZCorp.com". We can reference the server running SharePlex by any of these; for example, IP Address 10.1.1.1 might be called "HRDBServer" and be in Domain "XZYCorp.com"; so the "hostname" would be "HRDBServer" and the "Fully Qualified Domain Name", or FQDN, would be HRDBServer.XYZCorp.com. SharePlex relies on DNS (Domain Name Services) to resolve the names into IP addresses.
SharePlex uses these names to make the network connection from the source to the target, and for conflict resolution in Active-Active replication. It's also used in default queue names. When SharePlex is used in a cluster environment, either OS or RAC; it's important that the hostname used by SharePlex point to a "virtual" IP address that will in turn point to the specific server on which SharePlex is actually running. Finally, if a system has multiple network interfaces, unless the name used by SharePlex is specifically "bound", by DNS and the use SP_SYS_HOST_NAME, SharePlex will pick the "first" network interface card, which could be incorrect.
It's worth noting that as long as SP_SYS_HOST_NAME is set, the host name used by SharePlex does not have to be the same name as the hostname of the server running SharePlex.
Who does SharePlex think I am?
If you don't set SP_SYS_HOST_NAME, either as an environment variable or a parameter (the parameter has the same name SP_SYS_HOST_NAME) SharePlex will obtain the hostname from the server. SharePlex then stores that hostname. You can see what hostname SharePlex is using when you run sp_ctrl, the hostname is part of the prompt, for example:
In this case, I'm running sp_ctrl on oratgt.exampe.com, using port 2100. You can also use the "hostinfo" command to obtain more information:
sp_ctrl (oratgt.example.com:2100)> hostinfo
Linux oratgt.example.com 4.14.35-1902.0.18.el7uek.x86_64 #2 SMP Thu May 23 16:44:07 PDT 2019 x86_64
How do I change the name used by SharePlex?
As we've seen above, the name used by SharePlex is important in a number of cases. It's also critically important that this name remain consistent once SharePlex is installed. Simply changing the name of the host in DNS, or setting SP_SYS_HOST_NAME can result in errors, as below:
shareplex@orasrc bin]$ export SP_SYS_HOST_NAME=orasrcenv
[shareplex@orasrc bin]$ ./sp_cop &
Parameter database initialization failed - exiting
There's a SharePlex utility, provision, that can be used to safely and effectively change the hostname or IP address used by SharePlex. This utility is found in the "util" directory., under the SharePlex "proddir". It's fully documented in the SharePlex Administration Guide. Here are some highlights:
This command can only be run with SharePlex shut down. Since provision does NOT change any active configuration; if SharePlex is actively replicating, it's best to use the DEACTIVATE CONFIG command to stop replication. To shut down SharePlex, use the "shutdown" command in sp_ctrl and be sure to wait for it to complete.
Once SharePlex is down and all of the queues have been flushed, you can run the provision command, set the SP_SYS_HOST_NAME environment variable appropriately and restart sp_cop. If you have also set the SP_SYS_HOST_NAME parameter from sp_ctrl, you'll need to reset that and then shutdown and restart sp_cop. Once the hostname is changed, you can edit your config files to reflect the new names and reactivate replication.
I hope this blog has help demystify the SP_SYS_HOST_NAME and it's use. For more information about SharePlex, visit our website here