SharePlex 12.1 - Installation and Setup Guide

Demonstration of conflict resolution

Demonstration of conflict resolution for Oracle

Note: Before proceeding, make certain the SharePlex demonstration objects are installed. See Prework for the demonstrations .

In this demonstration, you will configure SharePlex to use generic conflict resolution procedures to resolve a replication conflict. Generic conflict resolution allows you to use one PL/SQL procedure to resolve conflicts for multiple tables.

The following conflict-resolution strategies are demonstrated:

• Timestamp priority – This demonstration is based on UPDATEs. Whichever row was updated LAST takes priority when there is a conflict.
• Trusted-source priority – In the following steps, you will define one system as the “trusted” source that takes priority in the event of a conflict. This demonstration is based on INSERTs. All INSERTs that originate on the trusted source will override INSERTs from the other system. In this demonstration, the trusted source is peer1 and the other system is peer2.

IMPORTANT! Peer-to-peer replication is not compatible with all business applications. When suitable for an environment, it requires careful analysis and execution, including the creation of custom conflict resolution procedures that are typically more complex than those in this demonstration. Do not use this demonstration as the foundation of a production peer-to-peer deployment. For more information about peer-to-peer replication, see SharePlex Administration Guide.

Prepare the objects

Note: In this demonstration, the demonstration objects are assumed to be in the schema named demo. Substitute the actual schema, if different.

Perform these steps on both systems (for both databases).

1. Run sp_ctrl.

2. Shut down SharePlex.

   sp_ctrl(peer1)> shutdown

3. Run the ora_cleansp utility according to the instructions in SharePlex Reference Guide. This removes the queues from the previous demonstrations and deactivates the previous configuration.
4. TRUNCATE  the od_employee tables.

5. Grant the user who owns the demonstration objects the system privilege to execute the sp_cr package, which was installed in the SharePlex schema when SharePlex was first installed.

   SQL>grant execute on sp_cr to user_name

6. Log into SQL*Plus as the user who owns the SharePlex demonstration objects.

7. Run the p2p.sql script from the util sub-directory of the SharePlex product directory. This installs the od_employee_gen demonstration conflict resolution routine.

   You are prompted for the following:

   • A schema and tablespace for the procedure
   • The name of the SharePlex database user.
   • The name of the system that will be the trusted source of accurate data. As in a production deployment, operations the trusted source take priority during a conflict. This system is known as peer1 in this demonstration. The other system is known as peer2 in this demonstration.

Configure SharePlex

Perform the following steps to configure SharePlex:

1. On each system, open the conflict_resolution.SID file (where SID is the ORACLE_SID of the local database) in a text editor. This file is located in the data sub-directory of the SharePlex variable-data directory.

   Note: Post checks this file when there is a replication conflict to determine if there is a resolution procedure to call.

2. On each system, create the following entries in the conflict_resolution.SID file. Separate each column with at least a few spaces or a tab character.

   demo.od_employee

   IUD

   demo.od_employee_gen

   Note: The first component is a table, the second specifies the operation types for which a resolution routine will be called if there is a conflict on that table, and the third is the name of the resolution routine that will be used.

3. On each system, start sp_cop.
4. On each system, start sp_ctrl.

5. On peer1 (the trusted source), create a configuration file named od_peer1 that replicates the od_employee table to the od_employee table on peer2.

   sp_ctrl(peer1)> create config od_peer1

   demo.od_employee

   demo.od_employee

   peer2@o.SID

6. On peer2 (the secondary source), create a configuration file named od.peer2 that replicates the od_employee table to the od_employee table on peer1.

   sp_ctrl(peer2)> create config od_peer2

   demo.od_employee

   demo.od_employee

   peer1@o.SID

NOTE: In order for post to detect out-of-sync inserts where all columns are identical, set SP_OPO_SUPPRESSED_OOS to 0. Issue this command from sp_ctrl: set param SP_OPO_SUPPRESSED_OOS 0 and verify the parameter is set by using the list param modified command.

Activate and start replication

Perform the following steps to activate and start replication:

1. On peer1, activate the od_peer1 configuration.

   sp_ctrl(peer1)> activate od_peer1

2. On peer2, activate the od_peer2 configuration.

   sp_ctrl(peer2)> activate od_peer2

3. On each system, confirm that the configuration activated successfully. The name od_peer1 or od_peer2 (depending on the system) should appear under File Name, and the word Active should appear under State.

   sp_ctrl(source)>list config

Demonstrate trusted-source priority

In this demonstration, an INSERT that originates on peer1 will override a conflicting INSERT that is replicated from peer2.

1. On both systems, stop the Post process.
2. On both systems, log in to SQL*Plus as demo (the owner of the demonstration objects).

3. On peer2, insert a row into od_employee but do not issue a COMMIT.

   SQL (peer2) > INSERT INTO OD_EMPLOYEE VALUES (1,'John','Doe',to_date('04/01/1949','MM/DD/RRRR'),1,to_date('01/01/2017','MM/DD/RRRR'));

4. On peer1, insert the same row (same values) but do not issue a COMMIT.

   SQL (peer1) > INSERT INTO OD_EMPLOYEE VALUES (1,'John','Doe',to_date('04/01/1949','MM/DD/RRRR'),1,to_date('01/01/2017','MM/DD/RRRR'));

5. On both systems, restart the Post processes.
6. On peer2, issue the COMMIT.
7. On peer1, issue the COMMIT. This operation should generate a conflict, which Post resolves automatically based on instructions in the conflict_resolution.SID file.
8. On both systems, view the demo.exc_table to verify that the conflict was resolved. See View the conflict resolution results.

Demonstrate timestamp priority

In this demonstration, whichever row was updated LAST takes priority when there is a conflict.

Note: The EMP_TIMESTAMP field, along with the other columns, must be modified to a non-null value in order for timestamp priority to function. The stored procedure won't be able to resolve the conflict if the timestamp column in the UPDATE statement is not being updated or is being modified to a null value. Out-of-sync problems will then be seen.

1. On both systems, stop the Post process.
2. On both systems, log in to SQL*Plus as demo (the owner of the demonstration objects).

3. On peer1, UPDATE the EMP_FIRST_NAME and EMP_TIMESTAMP columns of the od_employee table as follows.

   SQL (peer1) > UPDATE OD_EMPLOYEE SET EMP_FIRST_NAME = 'James', EMP_TIMESTAMP = to_date('01/01/2017','MM/DD/RRRR') WHERE EMP_NO = 1;

4. On peer2, UPDATE the same column using a different update values. but the same key value.

   SQL (peer2) > UPDATE OD_EMPLOYEE SET EMP_FIRST_NAME = 'Harry', EMP_TIMESTAMP = to_date('02/02/2017','MM/DD/RRRR') WHERE EMP_NO = 1;

5. On both systems, issue COMMITs at the same time.

6. On both systems, view the post queue to make sure the update operation is in the queue. You will see a message in each queue.

   sp_ctrl(peer1)>qstatus

   sp_ctrl(peer2)>qstatus

7. On both systems, start the Post process.
8. On both systems, select the row that you updated to verify that it contains the more recent EMP_TIMESTAMP value.

View the conflict resolution results

A table named exc_table was installed in the schema that you specified when you installed the demonstration objects. You can view it through SQL*Plus to view information about each conflict. The following is the table description.

Demonstration of Oracle DDL replication

This demonstration shows the default DDL replication support that is enabled when SharePlex is installed. You can enable other DDL replication with parameters, as needed.

Note: This demonstration supports Oracle source and targets only.

Verify that DDL replication is enabled

On the source, verify that the SP_OCT_REPLICATE_DDL parameter is set to the default of 3. This parameter controls basic DDL replication.

sp_ctrl(source)>list param modified capture

The SP_OCT_REPLICATE_DDL parameter should not be listed in the output. If it is, issue the following command:

sp_ctrl(source)>reset param SP_OCT_REPLICATE_DDL

Test DDL replication

Perform the following steps to test DDL replication:

1. On the source, TRUNCATE splex.demo_src to make certain it is empty.

   SQL> truncate table splex.demo_src;

   SharePlex replicates the TRUNCATE command to the target.

2. On the source, add a column to splex.demo_src.

   SQL> alter table splex.demo_src add (department varchar2(30) not null default 'unknown');

3. On the target, describe the splex.demo_dest table.

   SQL> describe splex.demo_src;

   The table should now contain four columns, including the new department column.

4. On the source, drop the department column.

   SQL> alter table splex.demo_src drop column department;

5. On the target, describe the splex.demo_dest table.

   SQL> describe splex.demo_src;

   The table should now contain only the original three columns.

Database Setup Utilities

The Database Setup utilities automatically configure a source or target database to allow SharePlex connections and to establish required database components that support replication.

Contents

Database Setup for MariaDB

Overview

Run the Database Setup utility for MariaDB (mariadb_setup) on a MariaDB system to establish SharePlex as a MariaDB database user.

This utility creates the following:

• A SharePlex user account with full DBA privileges
• Tables and indexes for use by SharePlex and owned by the SharePlex user in a database of your choosing
• A default database connection.

Supported databases

MariaDB on Linux. For supported Linux platforms and versions, see the SharePlex Release Notes.

Guidelines for use

• Run the setup utility on all MariaDB instances in the SharePlex replication configuration.
• Within a cluster, run the setup utility on the node to which the shared disk that contains the SharePlex variable-data directory is mounted.
• For consolidated replication, run the setup utility for each variable-data directory.

ODBC configuration

Use the following odbc.ini entry to connect via ODBC:

[sp_mariadb]

Driver = /usr/lib64/libmaodbc.so

Database = sp_md

Servername = <ip-address or hostname>

Port = 3306

Connection Timeout=30

SOCKET = /var/lib/mysql/mysql.sock

Required privileges

Review the following requirements to ensure that the setup succeeds:

• The setup utility must be run as a MariaDB Administrator that retains all of that user's default privileges. Both local and cloud MariaDB Administrators should have the required privileges by default. The Administrator user is able to grant SharePlex the required privileges to operate on the database and to create the SharePlex database account and objects.
• (Cloud installations) Common restrictions on privileges in cloud-hosted database services make it difficult for the setup utility to succeed in every possible type of scenario. To ensure that the database setup succeeds, only use the setup utility for the following purposes: To do a first-time database setup with a new SharePlex user, or, to modify an existing SharePlex user that either owns the database or has access to it.

Run database setup for MariaDB

Perform the following steps to run database setup for MariaDB:

1. Shut down any running SharePlex processes and sp_cop on the MariaDB system.

2. Add the DSN entry along with Socket path (SOCKET=) in the odbc.ini file at /etc/odbc.ini and the …/var/odbc directory.

3. Run the mariadb_setup program from the bin sub-directory of the SharePlex product directory.

   Important! If you installed the SharePlex instance on any port other than the default of 2100, use the -p option to specify the port number. For example, in the following command the port number is 9400.

   \usr\splex\bin\mariadb_setup-p9400

Table 5: Setup prompts and responses

A successful setup terminates with a message similar to the following:

Completed SharePlex for MariaDB database configuration

SharePlex User name: mariadb29

Database name: mariadb29

Target specification in SharePlex configuration: r.mariadb29