SharePlex 11.0 - SharePlex Installation and Setup Guide

Advanced SharePlex Demonstrations for PostgreSQL

Contents

This chapter demonstrates selected features of SharePlex. These exercises can be run on Unix and Linux systems to demonstrate:

• How to build and verify a replication configuration
• How to use generic conflict resolution in peer-to-peer replication

Demonstration of Conflict Resolution - PostgreSQL to PostgreSQL

Demonstration of Conflict Resolution - PostgreSQL to PostgreSQL

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

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 strategy is demonstrated:

• 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.
• Timestamp priority – This demonstration is based on UPDATEs. When there is a conflict, the row that was updated LAST prevails.

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 the (missing or bad snippet).

Pre-work for the Demonstrations

Install the demonstration objects as described below before you start the demonstrations.

Note: To run the scripts, set Replica Identity to Full for tables that are a part of replication.

Install the demonstration objects

The pg_p2p.sql and pg_od.sql scripts install the demonstration objects that you will use during these demonstrations. These scripts are stored in the util sub-directory of the SharePlex product directory.

To run the scripts:

• Run pg_od.sql first, then run pg_p2p.sql.
• Run the scripts on the source and target systems that you will be using for the demonstrations.
• Run them in PSQL as an existing user with the DBA role.

Note: The scripts prompt for the schema where you want the demonstration objects need to be installed. Additionally, pg_p2p.sql prompts for the name of the system that will be the trusted source of accurate data.

Description of the demonstration objects

Note: Apart from these tables, other demonstration objects are also created those are not part of this demonstration.

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 pg_cleansp utility. This removes the queues from the previous demonstrations and deactivates the previous configuration.
4. TRUNCATE  the od_employee tables.
5. Log into PSQL as the user who owns the SharePlex demonstration objects.

6. Run the pg_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 for the procedure
   • The name of the system that will be the trusted source of accurate data (pri_system). 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.
7. Set the Replica Identity to Full for the od_employee tables.

Configure SharePlex

Perform these steps to configure SharePlex:

1. On each system, open the conflict_resolution.database file 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.database 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@r.database

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@r.database

Activate and start replication

Perform these 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 Export process.
2. On both systems, log in to PSQL as demo (the owner of the demonstration objects).

3. On peer2, insert a row into od_employee.

   insert into od_employee values(1, 'John', 'Doe', '1949-04-01', 1, '2022-01-01');

4. On peer1, insert the same row (same values).

   insert into od_employee values(1, 'John', 'Doe', '1949-04-01', 1, '2022-01-01');

5. On both systems, restart the Export processes.
6. This operation should generate a conflict, which Post resolves automatically based on instructions in the conflict_resolution.database file.
7. 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, whenever there is a conflict for an UPDATE statement, the row with the most current value of the emp timestamp column gets precedence.

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 Export process.
2. On both systems, log in to PSQL 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:
   update od_employee set emp_first_name = 'James', emp_timestamp = '2022-07-01' where emp_no = 1;
4. On peer2, update the same columns using a different update value but the same key value.
   update od_employee set emp_first_name = 'Harry', emp_timestamp = '2022-08-02' where emp_no = 1;
5. On both systems, restart the Export processes.
6. On both systems, select the row that you updated to verify that it contains the most recent emp_ timestamp value.
7. On both systems, view the demo.exc_table to verify that the conflict was resolved. See View the conflict resolution results.

Demonstrate no conflict resolution

In this demonstration, if a DELETE statement generates a conflict, it is ignored.

1. On both systems, stop the Export process.
2. On both systems, log in to log in to PSQL as demo (the owner of the demonstration objects).
3. On peer1, delete a row as follows.
   delete from od_employee where emp_no = 1;
4. On peer2, delete the same row.
   delete from od_employee where emp_no = 1;
5. On both systems, restart the Export processes.
6. On both systems, view the demo.exc_table to verify that the conflict is not resolved. See View the conflict resolution results.

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 PSQL to view information about each conflict. The following is the table description.

Demonstration of Conflict Resolution - PostgreSQL to Oracle

Demonstration of Conflict Resolution - PostgreSQL to Oracle

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

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 strategy is demonstrated:

• 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(i.e. PostgreSQL) and the other system is peer2(i.e. Oracle).
• Timestamp priority – This demonstration is based on UPDATEs. When there is a conflict, the row that was updated LAST prevails.

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 the (missing or bad snippet).

Pre-work for the Demonstrations

Install the demonstration objects as described below before you start the demonstrations.

Note: The below pre-work is applicable only for the Oracle peer. For the information on the pre-work of PostgreSQL peer, see Pre-work for the Demonstrations - PostgreSQL.

Install the demonstration objects

The p2p.sql and od.sql scripts install the demonstration objects that you will use during these demonstrations. These scripts are stored in the util sub-directory of the SharePlex product directory.

To run the scripts:

• Run od.sql first, then run p2p.sql.
• Run the scripts on the source and target systems that you will be using for the demonstrations.
• Run them in SQL*Plus as an existing user with the DBA role and SELECT ANY TABLE privileges.

The scripts prompt for the following items:

• The schema where you want the demonstration objects to be installed.
• The tablespace for the demonstration objects.
• Whether or not you want old demonstration objects from a previous version of SharePlex to be removed. To remove the old objects, supply the name of their schema.

Description of the demonstration objects

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.

The following steps provide information on how to prepare the demonstration objects for Oracle peer. For information on how to prepare the demonstration objects of PostgreSQL peer, see Prepare the objects.

Perform these steps on Oracle peer:

1. Run sp_ctrl.

2. Shut down SharePlex.

   sp_ctrl(peer1)>shutdown

3. Run the ora_cleansp utility. 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 these steps to configure SharePlex:

Note: In this configuration, Peer 1 is Postgresql database and Peer2 is Oracle database. The name of the conflict_resolution file for Oracle is conflict_resolution.sid, and for PostgreSQL it is conflict_resolution.database.

1. On each system, open the conflict_resolution file 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 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

   datasource:r.dbname
   "demo"."od_employee" ("emp_no", "emp_first_name", "emp_last_name", "emp_dob", "emp_dept_no", "emp_timestamp")	"DEMO"."OD_EMPLOYEE" ("EMP_NO", "EMP_FIRST_NAME", "EMP_LAST_NAME", "EMP_DOB", "EMP_DEPT_NO", "EMP_TIMESTAMP")	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

   datasource:o.sid
   "DEMO"."OD_EMPLOYEE" ("EMP_NO", "EMP_FIRST_NAME", "EMP_LAST_NAME", "EMP_DOB", "EMP_DEPT_NO", "EMP_TIMESTAMP")	"demo"."od_employee" ("emp_no", "emp_first_name", "emp_last_name", "emp_dob", "emp_dept_no", "emp_timestamp")	peer2@r.dbname

Note: In the above example, column mapping feature is used since both (source and target) tables have different case sensitivity.

7. Set the SP_OPO_SUPPRESSED_OOS parameter to 0 on peer 2.

Activate and start replication

Perform these 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 Export process.
2. On both systems, log in to database as demo (the owner of the demonstration objects).

3. On peer1, insert a row into od_employee.

   insert into od_employee values(1, 'John', 'Doe', '1949-04-01', 1, '2022-01-01');

4. On peer2, insert the same row (same values).

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

5. On both systems, restart the Export processes.
6. This operation should generate a conflict, which Post resolves automatically based on instructions in the conflict_resolution file.
7. 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, whenever there is a conflict for an UPDATE statement, the row with the most current value of the emp_timestamp column gets precedence.

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 Export process.
2. On both systems, log in to database as demo (the owner of the demonstration objects).
3. On peer1, update the emp_first_name and emp_timestamp column of the od_employee table as follows.
   update od_employee set emp_first_name = 'James', emp_timestamp = '2022-07-01' where emp_no = 1;
4. On peer2, update the same column using a different update value but the same key value.
   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, restart the Export processes.
6. On both systems, select the row that you updated to verify that it contains the most recent emp_ timestamp value.
7. On both systems, view the demo.exc_table to verify that the conflict was resolved. See View the conflict resolution results.

Demonstrate no conflict resolution

In this demonstration, if a DELETE statement generates a conflict, it is ignored.

1. On both systems, stop the Export process.
2. On both systems, log in to log in to database as demo (the owner of the demonstration objects).
3. On peer1, delete a row as follows.
   delete from od_employee where emp_no = 1;
4. On peer2, delete the same row.
   DELETE FROM OD_EMPLOYEE WHERE EMP_NO = 1;
5. On both systems, restart the Export processes.
6. On both systems, view the demo.exc_table to verify that the conflict is not resolved. See View the conflict resolution results.

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 PSQL for PostgreSQL and SQLPLUS for Oracle to view information about each conflict. The following is the table description.

Sample Configuration for PostgreSQL to PostgreSQL Replication

SharePlex gets its replication instructions from “configurations,” which are user-defined specifications that tell SharePlex what to do. For each group of objects that you want to replicate, you create a configuration file.

Configurations reside on the source system and define:

• The datasource (source database) — the identifier of the source database.
• The source objects — the names of the objects within the source database that contain the data to be replicated. You can replicate some or all of the tables within a database.
• The target objects — the names of the objects in the database on the target system that will receive the replicated data.
• The routing map — the name of the target system and, if the target is a database, its identifier.

To create the demonstration configuration:

1. Create a replication configuration named sample_config by issuing the create config command in sp_ctrl on the source system. This opens the default text editor, which is vi for Linux systems.

   sp_ctrl(sysA)> create config sample_config

   Refer to Template 1 below as you construct your configuration.

   Template 1: Basic demonstration configuration sample_config

   datasource:r.source_DB
   "qarun"."basic_c127"	"splex"."basic_c127"	10.250.14.105@r.sp_ad

2. On the first non-commented line of the file, type the following, leaving no space between any of the items.

   datasource:r.source_databasename

   (Substitute the PostgreSQL database name of the source instance for source_databasename.) This tells SharePlex where to find the table whose data will be replicated. The r. tells Share- Plex that PostgreSQL data is being replicated.

3. On the next line, enter the owner name (splex) and table name (demo_src) of the source table, separating the two items with a dot (.) but no spaces. Using the owner’s name with a table name ensures that SharePlex replicates the correct table, since different tables in different schemas in a database could have the same name.

   splex.demo_src
4. Type at least a few spaces or a tab to create a second column. Do not press Enter.

5. In the second column, enter the owner name (splex) and table name (demo_dest) of the target table, separating the two items with a dot (.) but no spaces.

   splex.demo_dest

6. Type a few spaces or a tab to create a third column. Do not press Enter.

7. In the third column, type the following items with no space between them. This creates the routing map for your configuration, telling SharePlex where to put the replicated data.

   • the name of the target system
   • the @ symbol
   • the letter r
   • a dot (.)
   • the target instance

   Example:

   sysB@r.databasename

8. Save the file and exit the editor. This returns you to the sp_ctrl prompt.

9. [OPTIONAL] To view the configuration, issue the view config command in sp_ctrl on the source system:

   sp_ctrl(sysA)>view config sample_config

10. Activate the configuration in sp_ctrl on the source system. Configuration names are case-sensitive.

    sp_ctrl(sysA)>activate config sample_config

11. To confirm that your configuration is active, type the following sp_ctrl command on the source system to display a list of all configurations. The sample_config configuration should appear under “File Name,” and the word “Active” should appear under “State.”

    sp_ctrl(sysA)>list config

Tip: If your configuration activation fails, use the view config sample_config command in sp_ctrl to view the file. Compare it to Template 1 and make sure all of the information you entered is correct. For example, check for extra spaces that are not supposed to be there, or for missing components, such as the r. before the database name.

To correct mistakes in the configuration file:

1. Run the edit config sample_config command in sp_ctrlbefore you activate the configuration file (or if the activation failed).
   This command opens the file in the text editor.
2. Make the changes by editing the file.
3. Save the changes, and re-try the activation.

Note: To change an active configuration, you must copy it to a new file first with the copy config command, and then edit and activate the copy.