Infrastructure at your Service

Nowadays, replicating data is very important. Many tools exist in the market (Goldengate, Dbvisit …). In this article we talk about a Dell Product tool that can be used for data replication: SharePlex. The goal is to show how it is easy to setup a SharePlex environment for replicating data.
After installing the software we will replicate scott schema to a target database into a new schema named scott_str1

stream1

We present below the environment we use

Server Database Oracle Version
atlas SPLEXDB 12.1.0.2 Source
atlasrep SPLEXSTR1 11.2.0.4 Target

1- Installation on both source and target

The first step is to install the SharePlex software, the downlod page is  https://www.quest.com/products/shareplex/.
For the intallation we need two directories on both source and target servers:

Product directory: stores programs and libraries
Variable-data directory: stores queues, log files and other components files

Note: For some steps we will only show outputs for the source

[[email protected] app]$ mkdir -p /u01/app/shareplex/splex_864_12_bin
[[email protected] app]$ mkdir -p /u01/app/shareplex/splex_864_12_var

Now Let’s unzip the downloded file and after execute the .tpm resulting file on both source and target

[[email protected] shareplex]$ tar xvf shareplex-8.6.4-b66-oracle120-rh-40-amd64-m64.tar
SharePlex-8.6.4-b66-oracle120-rh-40-amd64-m64.tpm
[[email protected] shareplex]$ ./SharePlex-8.6.4-b66-oracle120-rh-40-amd64-m64.tpm
Unpacking ..................................................................
  ............................
SharePlex installation program:
    SharePlex Version: 8.6.4
    Build platform: rh-40-amd64
    Target platform: lin-amd64

Please enter the product directory location?/u01/app/shareplex/splex_864_12_bin
Please enter the variable data directory location?/u01/app/shareplex/splex_864_12_var
Please specify the SharePlex Admin group (select a number):
1. [oinstall]
2. osdba
3. osasm
4. dba
5. osoper
6. vboxsf
7. oracle
?  1
Please wait while the installer obtains Oracle information ..
Please enter the ORACLE_SID that corresponds to this installation? [SPLEXDB]
Please enter the ORACLE_HOME directory that corresponds to this ORACLE_SID? [/u01/app/oracle/product/12.1.0.2/dbhome_1]
Please enter the TCP/IP port number for SharePlex communications? [2100]

Preparing to install SharePlex for Oracle v.8.6.4:
    User:                     oracle
    Admin Group:              oinstall
    Product Directory:        /u01/app/shareplex/splex_864_12_bin
    Variable Data Directory:  /u01/app/shareplex/splex_864_12_var
    ORACLE_SID:               SPLEXDB
    ORACLE_HOME:              /u01/app/oracle/product/12.1.0.2/dbhome_1

Proceed with installation? [yes]
Please enter the License key? ENGMFYD3X0P2SV88Y6PLHF0DVJXJAB5SY4
Please enter the customer name associated with this license key? DBI

SharePlex v. 8.6.4 license validation successful:
    Customer Name:      XXXXX
    License Key:        XXXXXXXXXXXXXXXXXXXXXXXXX
    Product Name:       SharePlex for Oracle - RAC
    License Key Type:   "Trial Key"
    Expires:            at Midnight of Jul 04, 2016

NOTE: You can upgrade this license key or add license keys for additional machines
      by executing utility /u01/app/shareplex/splex_864_12_bin/install/splex_add_key.

Installation log saved to: /home/oracle/.shareplex/INSTALL-SharePlex-8.6.4-1606091325.log
SharePlex for Oracle v.8.6.4 installation successful.
[[email protected] shareplex]$

Wow SharePlex installation is finished.

 2- Database preparation

The initial load on the target must me done. Datapump may help for this. Just export data from the source and import it on the target.

Minimal supplemental logging must be set before you activate the SharePlex replication configuration. In addition, SharePlex recommends that both primary key and unique key supplemental logging be set. In this article we will only activate minimal supplemental logging.

SQL> select name,supplemental_log_data_min from v$database;

NAME      SUPPLEME
--------- --------
SPLEXDB   NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> select name,supplemental_log_data_min from v$database;

NAME      SUPPLEME
--------- --------
SPLEXDB   YES

SharePlex will create a user  in the database so it is recommended to create dedicated tablespaces for this user

3-  Define a new variable-data directory on both source and target

When configuring a replication configuration, it is recommended to use different variable-data directory other than the one mentioned during the installation. The main reason is that by doing that we will have one working directory for each replication. What we need is just to create a new directory and to copy the contents of the default variable-data directory in the new one. Remark the naming for the new var directory  (dbname_shareplexverion_oracleversion_port). Of course feel free for your naming. But it is useful to adopt a naming that can help for identifying the replication as we can have many configurations in the same server

[[email protected] splex_864_12_var]$ pwd
/u01/app/shareplex/splex_864_12_var 
[[email protected] splex_864_12_var]$ cp -R * /u01/app/shareplex/vardir/splexdb_864_12_2103

4- Configure SharePlex on both source and target

To configure SharePlex we have to run the ora_setup file. Before launching the ora_setup file, we  set some variables. Just note that we can use a different port than the one we specified in the installation process The only requirement is that ports should have same value on both source and target

[[email protected] bin]$ env | grep  SP_
SP_SYS_HOST_NAME=atlas.localdomain
SP_COP_UPORT=2103
SP_COP_TPORT=2103
SP_SYS_VARDIR=/u01/app/shareplex/vardir/splexdb_864_12_2103
SP_HOME=/u01/app/shareplex/splex_864_12_bin/
[[email protected] bin]$
[[email protected] ~]$ cd $SP_HOME
[[email protected] splex_864_12_bin]$ pwd
/u01/app/shareplex/splex_864_12_bin
[[email protected] splex_864_12_bin]$


[[email protected] bin]$ ./ora_setup

Welcome to the Oracle SharePlex setup process for port 2103.
This process creates tables and user accounts needed to run
Oracle SharePlex replication.
....
....
Enter the Oracle SID for which SharePlex should be installed [SPLEXDB] :

In order to create the SharePlex tables and user account, we must
connect to the database as a DBA user

Enter a DBA user for SPLEXDB  : system

Enter password for the DBA account, which will not echo :

connecting--This may take a few seconds.

validating user name and password. . . This may take a few seconds.
SharePlex objects will need to be created under a special
account.  You can pick an existing user or create a new one.
Would you like to create a new SharePlex user ? [y] :
Enter username for new user [splex/splex] : splex_864_12_2103

Enter password for new user :

Re-enter password for new user :

Warning:  This user is now being granted unlimited tablespace.
This privilege will remain in effect until it is explicitly changed.
Granting select on sys.user$ to splex_864_12_2103

Do you want to enable replication of tables with TDE? [n] :

To enable replication of tables with TDE in the future, please rerun ora_setup.

Setup will now install SharePlex objects.

These are the existing tablespaces.

SYSTEM SYSAUX UNDOTBS1 TEMP USERS EXAMPLE SHAREPLEX_DATA SHAREPLEX_INDX
SHAREPLEX_TEMP

Enter the default tablespace for use by SharePlex [USERS] : SHAREPLEX_DATA

Enter the temporary tablespace for use by SharePlex [TEMP] : SHAREPLEX_TEMP
Enter the index tablespace for use by SharePlex [ ] : SHAREPLEX_INDX

Creating SharePlex objects [Installation type: Fresh]. . .

Creating SharePlex Oracle-timezone-region map . . . Done.

Creating Conflict Resolution Package . . . Done.

Creating SharePlex Dataequator package . . .

Loading Compare Package from "/u01/app/shareplex/splex_864_12_bin/util/sp_deq_pkg.plb"...Done.

Loading Compare Varray Package from "/u01/app/shareplex/splex_864_12_bin/util/sp_deq_v_pkg.plb"...Done.

Will the current setup for sid: [SPLEXDB] be used as source (including cases as source for failover or master-master setups)?  [y] :

Setup of SharePlex objects successful . . .

Changing SharePlex connection database . . .

Setup completed successfully

[[email protected] bin]$

Run also ora_setup on the target. We only show variables on target

[[email protected] ~]$ env | grep SP_
SP_SYS_HOST_NAME=atlasrep.localdomain
SP_COP_UPORT=2103
SP_COP_TPORT=2103
SP_SYS_VARDIR=/u01/app/shareplex/vardir/splexstr1_864_11_2103
SP_HOME=/u01/app/shareplex/splex_864_11_bin/

5- Starting SharePlex processes on both source and target

Now we are ready to start SharePlex processes. The $SP_HOME/bin/sp_cop executable is used. Just remark the option –u followed by a naming variable. Not mandatory but can help for alerting setup or for locating our processes.

Starting on the source side

[[email protected] ~]$ echo $NAME_FOR_ALERTING
ATLAS_SPLEXDB_TO_ATLASREP_2103
[[email protected] ~]$  $SP_HOME/bin/sp_cop -u $NAME_FOR_ALERTING &
[1] 22174
[[email protected] ~]$

*******************************************************
* SharePlex for Oracle Startup
* Copyright 2016 Dell, Inc.
* ALL RIGHTS RESERVED.
* Protected by U.S. Patents: 7,461,103 and 7,065,538
* Version: 8.6.4.66-m64-oracle120
* VarDir : /u01/app/shareplex/vardir/splexdb_864_12_2103
* Port   : 2103
*******************************************************
[[email protected] ~]$ ps -ef | grep sp_cop

oracle 22174 3983 0 15:37 pts/2 00:00:00 /u01/app/shareplex/splex_864_12_bin/.app-modules/sp_cop -u ATLAS_SPLEXDB_TO_ATLASREP_2103

Starting on the target side

[[email protected] ~]$ echo $NAME_FOR_ALERTING
FROM_ATLAS_SPLEXDB_2103
[[email protected] ~]$  $SP_HOME/bin/sp_cop -u $NAME_FOR_ALERTING &
[1] 14933
[[email protected] ~]$

*******************************************************
* SharePlex for Oracle Startup
* Copyright 2016 Dell, Inc.
* ALL RIGHTS RESERVED.
* Protected by U.S. Patents: 7,461,103 and 7,065,538
* Version: 8.6.4.66-m64-oracle110
* VarDir : /u01/app/shareplex/vardir/splexstr1_864_11_2103
* Port   : 2103
*******************************************************
[[email protected] ~]$ ps -ef | grep sp_cop
oracle   14933  2439  3 15:52 pts/0    00:00:00 /u01/app/shareplex/splex_864_11_bin/.app-modules/sp_cop -u FROM_ATLAS_SPLEXDB_2103

6- Configuring Replication

To configure data replication, we need to create a configuration file on the source. There is already a template (ORA_config)  for the configuration in the $SP_SYS_VARDIR/config directory.

[[email protected] config]$ cat ORA_config

datasource:o.SOURCE_SID

#source tables      target tables           routing map

splex.demo_src      splex.demo_dest        [email protected]_sid

We just have to create a configuration file based on this template. Below our config file

[[email protected] config]$ cat str1_config_atlasrep.cnf
datasource:o.SPLEXDB

#source tables      target tables            routing map
expand scott.%       scott_str1.%               [email protected]

[[email protected] config]$

When using wildcarded names in the configuration file, the keyword expand is mandatory. Without this required keyword, the wildcard characters are assumed to be part of an explicit object name, and no wildcard expansion is performed.
Once the configuration created we verify it and then activate it. Activating the configuration file will start the replication. The sp_ctrl utility is used to manage SharePlex

$SP_HOME/bin/sp_ctrl

We can display the list of our configuration files

sp_ctrl (atlasrep:2103)> list config

File   Name                                         State       Datasource
--------------------------------------------------  ----------  ---------------
str_config_atlas.cnf                                Inactive    o.SPLEXSTR1
Last Modified At: 13-Jun-16 16:52    Size: 159

ORA_config                                          Inactive    o.SOURCE_SID
Last Modified At: 09-Jun-16 13:33    Size: 151

Let’s verify the configuration file syntax

sp_ctrl (atlasrep:2103)> verify config str_config_atlas.cnf

  Config str_config_atlas.cnf is valid
  View detailed config in /u01/app/shareplex/vardir/splexstr1_864_11_2103/temp/str_config_atlas.cnf

The last step is to activate the config file

sp_ctrl (atlas:2103)> activate config str1_config_atlasrep.cnf

We can validate the replication by checking the running processes with the command show.
From the Source: Capture, Read and Export processes should be running

sp_ctrl (atlas:2103)> show

Process    Source                               Target                 State                   PID
---------- ------------------------------------ ---------------------- -------------------- ------
Capture    o.SPLEXDB                                                   Running               24650
Read       o.SPLEXDB                                                   Running               24656
Export     atlas                                atlasrep               Running               24671

From the Target : Import and Post processes should be running

sp_ctrl (atlasrep:2103)> show

Process    Source                               Target                 State                   PID
---------- ------------------------------------ ---------------------- -------------------- ------
Import     atlas                                atlasrep               Running               16143
Post       o.SPLEXDB-atlas                      o.SPLEXSTR1            Running               16144

 

Now we can see that if we insert new rows in the source, they are replicated in the target

SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      SPLEXDB
SQL> conn scott/tiger
Connected.
SQL> insert into dept values (60,'IT','GENEVA');

1 row created.

SQL> insert into dept values (70,'SECURITY','DAKAR');

1 row created.

SQL> commit;

Commit complete.

SQL>

 

SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      SPLEXSTR1
SQL> conn scott_str1/tiger
Connected.
SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        60 IT             GENEVA
        70 SECURITY       DAKAR

6 rows selected.

 

Conclusion :

In this article we shown how easy it is to setup data replication with SharePlex.

Ref: Dell Official documentation

One Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Mouhamadou Diaw
Mouhamadou Diaw

Consultant