Chat now with support
Chat with Support

SharePlex 9.0 - Installation Guide

About this Guide Conventions used in this guide System Requirements SharePlex pre-installation checklist Installation and setup for Oracle cluster Installation and setup for cloud-hosted databases Download SharePlex Install SharePlex on Linux and UNIX Install SharePlex on Windows Assign SharePlex users to security groups Set up an Oracle environment for replication Set up replication between SQL Server databases Set up replication between different database types Basic SharePlex demonstrations Advanced SharePlex demonstrations Solve Installation Problems Uninstall SharePlex SharePlex Utilities Advanced installer options Install SharePlex as root Run the installer in unattended mode SharePlex installed items

Demo1: Initial Demonstration

This demonstration highlights the compare command, and provides a simple demonstration of performance.

Start SharePlex

The following are instructions for starting SharePlex on Unix, Linux, and Windows systems. Start SharePlex on the source and target systems.

Unix and Linux systems

1 Log onto the system as a SharePlex Administrator (a member of the SharePlex Admin group).

From the bin sub-directory of the SharePlex product directory (the one containing the binaries, represented by the productdir variable in the following syntax), run sp_cop and sp_ctrl.

$ cd /productdir/bin
 
$ ./sp_cop &
 
$ . /sp_ctrl

Windows systems

  1. Log onto the system as a SharePlex Administrator (a member of the SharePlex Admin group).
  2. On the Windows desktop, double-click the SpUtils shortcut to open the SharePlex Utilities dialog box.
  3. Click the SharePlex Services tab to display the SharePlex Services dialog box.
  4. In the Port list box of the SharePlex Services dialog box, select the SharePlex port number, then click Start.
  5. When the Current State text box shows that the service has started, click Close to close the dialog box.
  6. On the Windows desktop, double-click the sp_ctrl shortcut to open the sp_ctrl command prompt

Create the configuration

In sp_ctrl, create a replication configuration named od.config that replicates the od_department, od_salary, od_timesheet, and od_employee tables on the source system to the same tables on the target system.

To create the configuration, use the create config command in sp_ctrl. This command opens a new file in the default text editor for SharePlex, which is either vi (Unix and Linux) or WordPad (Windows).

sp_ctrl(sysA)> create config od.config

Using the editing commands in the text editor, follow Template 1 below to create the configuration, making the following substitutions.

  • For source_SID on line 1, use the ORACLE_SID of the source database. The SID is case-sensitive.
  • For owner, substitute the owner of the object. The object in column 1 is the source object, and the object in column 2 is the target object.
  • For target_host, substitute the target machine’s name.
  • For target_SID, substitute the ORACLE_SID of the target database. The SID is casesensitive.

Do not allow any spaces between characters within a column. Separate columns with two or more spaces or a tab.

 

Template 1: Demonstration configuration “od.config”

datasource:o.source_SID  

 

owner.od_department owner.od_department target_host@o.target_SID
owner.od_salary owner.od_salary target_host@o.target_SID
owner.od_timesheet owner.od_timesheet target_host@o.target_SID
owner.od_employee owner.od_employee target_host@o.target_SID

 

When you are finished making the configuration entries, save the file using either the :wq command (in vi) or File>Save (WordPad). SharePlex automatically saves the file in the config sub-directory.

Important! Retain this configuration file on your system, since it will be needed for another demonstration.

Activate the configuration

You will use the activate command in sp_ctrl to activate the configuration. Prior to activating the configuration this command validates the database ORACLE_SID, and object names using the source system.

  1. Make certain the SharePlex demonstration objects are installed. See Prework for the demonstrations.
  2. The target system contains an ON DELETE CASCADE constraint on the od_salary table. The constraint’s name is od_sal_emp_no_fk. You can either disable the constraint on the target table or you can configure SharePlex to handle the cascaded deletes (Post detects the ON DELETE CASCADE dependencies and suppresses the posting of any replicated cascaded deletes to the child tables).

    To configure SharePlex to handle the constraints:

    Set the following parameters:

    • SP_OPO_DEPENDENCY_CHECK parameter to 2
    • SP_OCT_REDUCED_KEY parameter to 0
    • SP_OPO_REDUCED_KEY parameter to 0 or 1

    To disable the constraints through SQL*Plus:

    SQL> alter table od_salary disable constraint od_sal_emp_no_fk;

  3. The target system also contains a trigger on the od_timesheet table. The trigger’s name is od_timesheet_mod. Since triggers on target tables cause SharePlex to return out-of-sync errors, disable this trigger on the target table only.

    SQL> alter trigger od_timesheet_mod disable;

  4. In sp_ctrl, issue the activate config command for the od.config configuration.

    sp_ctrl(sysA)> activate config od.config

Verify synchronization

This demonstrates the compare command. This command compares the source and target tables to ensure their synchronization. The repair command can then be used to resynchronize out-of-sync rows that are found.

  1. Make certain the SharePlex demonstration objects are installed. See Prework for the demonstrations.
  2. Populate the od_employee and od_salary tables on the source system by executing the od_add_emps procedure that was installed in the schema where you installed the demonstration objects. This procedure has one IN parameter that specifies the number of employees to insert per department. The default number of departments is Use an IN parameter of 100 to create 500 new employees in the od_employee table and 500 entries in the od_salary table.

    SQL> exec od_add_emps(100);
  3. In sp_ctrl on the source system, issue the compare command once for the od_employee table and once for the od_salary table. Substitute the tables’ owners, the target system’s name, and the target ORACLE_SID for the appropriate variables in the following syntax.

    sp_ctrl(sysA)> compare source_owner.od_employee
    sp_ctrl(sysA)> compare source_owner.od_salary
  4. In sp_ctrl on the source system, issue the compare status command. This displays the progress of the comparisons. Continue issuing this command until both compare processes have completed.

    sp_ctrl> compare status
  5. Issue the compare status command on the source system to view the results of the comparisons.

    sp_ctrl(sysA)> compare status

    The results should show no out-of-sync rows.

  6. Delete some rows from od_employee on the target system. This causes that table to go out of synchronization with its source table.

  7. Issue the repair command on the source system again for the od_employee table. This repairs the out-of-sync condition.

    sp_ctrl(sysA)> repair source_owner.od_employee

  8. Issue the repair status command on the source system. This should show no out-ofsync rows, since the repair command added the missing rows to the target od_employee table.

    sp_ctrl(sysA)> repair status

View performance statistics

This demonstration allows you to view performance statistics for SharePlex replication.

  1. Make certain the SharePlex demonstration objects are installed. See Prework for the demonstrations.
  2. Run the od_add_emps procedure on the source system using an IN value of 2000. This adds 10,000 rows assuming you made no changes to the od_department table.
  3. Run the perf_mon.sh script. This script monitors the speed of the Post process. When it runs, it polls the Post process to determine the number of operations that were processed. It repeats this poll a specified number of times at specified intervals, and then it computes the number of INSERTs, UPDATEs, DELETEs, and COMMITs per second that SharePlex posted during that time. The results are printed to screen. Following are instructions for Unix, Linux, and Windows systems.

    Unix and Linux systems

    Run perf_mon from the util sub-directory of the SharePlex product directory using the following syntax. You can control the frequency and interval of the poll.

    Syntax:

    perf_mon.sh #_polls poll_interval

    where #_polls is the number of times to poll the Post process

    and poll_interval is the time interval between polls

    Example:

    perf_mon.sh 10 5

    In this example, the Post process is polled 10 times at 5-second intervals.

    Windows systems

    Run sp_perf_mon in the Command Prompt from the bin sub-directory of the Share- Plex product directory. Use the following syntax. On Windows systems, the poll occurs every second and continues until you kill it with the control+C command.

    sp_ perf_mon -rport_number

    where port_number is the SharePlex port number

    Example:

    sp_perf_mon -r2100

Note: This script does not work with multiple post queues using the same port.

Demo 2: Demonstration of Partitioned Replication

This demonstration illustrates a combination of horizontally partitioned replication and vertically partitioned replication, also known as selective row replication and selective column replication. You can use vertically partitioned replication to distribute selected information, such as employee names and locations, while protecting other data, such as personal information or salaries, without separating the data sets into different objects. You can use horizontally partitioned replication to distribute different segments of data to different target systems, such as sales and customer data to the individual stores responsible for it. More information about partitioned replication can be found in Chapter 5 of the SharePlex Administrator’s Guide.

  1. Make certain the SharePlex demonstration objects are installed. See Prework for the demonstrations.
  2. Delete (not TRUNCATE) all rows from the od_employee table on the source system. If the od.config configuration is still active from a previous demo, the cascading delete constraint on the od_salary table activates as a result of the deletes on od_employee. SharePlex will replicate all of those deletes and remove all rows from both target tables. If the od.config configuration is not active, delete all rows from the source and target od_employee and od_salary tables.

  3. To implement horizontally partitioned replication, create a column condition, which is conditional syntax that directs SharePlex to replicate only certain rows from the source od_employee table, in this case only the rows for the Sales department. To create the column condition, run SQL*Plus on the source system and insert the following into the SHAREPLEX_PARTITION table, substituting for the variables the SHAREPLEX_PARTITION table’s owner name, the target system name, and the target database ORACLE_SID.

    SQL> insert into owner.shareplex_partition (partition_scheme, description, route, target_table_name, ordering, col_conditions) values (‘sales_partition’, ‘Replicate only sales employees’, ‘targetsystem@o.target_SID’, ‘OD_EMPLOYEE’, 1, ‘EMP_DEPT_NO=1’);
     
    SQL> COMMIT;
  4. To implement vertically partitioned replication, create a new configuration named od.partition on the source system.

    sp_ctrl(sysA)> create config od.partition
  5. Follow Template 2 below to construct the configuration. Vertically partitioned replication is specified in the configuration file using a column partition, which appears in parentheses after the source object. For this demonstration, you are specifying a column partition that directs SharePlex to replicate all columns in od_employee except for the EMP_DOB and EMP_TIMESTAMP columns.

    It is permissible for a configuration line to wrap to the next line, as long as you do not press Enter.

    Notice that, in addition to the column partition, you also are listing a “partition scheme” instead of a regular routing map, as indicated by the “!sales_partition” component. The partition scheme is a logical “container” for one or more column conditions. Normally, you would design your own partition schemes based on your business rules, but for the sake of this demonstration, just one partition scheme named “sales_partition” is used.

    You will notice that a line with “! target_host@o.target_SID” also is needed. Use the target system name and the target database ORACLE_SID in the syntax. This line is necessary because SharePlex must be able to see the target system name and ORACLE_SID within the configuration file itself. Normally, there are configuration entries for other, non-partitioned tables replicating to the target system, which satisfies this requirement, but in this case there is only one.

    Template 2: Demonstration configuration “od.partition”

    datasource:o.srce_SID    
    owner.od_employee (emp_no, emp_first_name, emp_last_name, emp_dept_no) owner.od_employee !sales_partition !target_host@o.target_SID
  6. Activate the configuration on the source system.

    sp_ctrl(sysA)> activate config od.partition
  7. Run od_add_emps to populate the od_employee table on the source system. Use a small value for the IN parameter, since you will be selecting rows for viewing after they are replicated.
  8. Select all rows from the target od_employee table. The value for the EMP_DEPT_NO column should be “1” for all rows. Rows where the value for this column is a value other than “1” were not replicated.

Demo 3: Demonstration of Transformation

SharePlex provides the ability to have the Post process call a PL/SQL transformation procedure (routine) instead of applying a SQL operation to the target database. This gives you the ability to manipulate the replicated data first.

For example, if the source and target tables are dissimilar in construction — as when a person’s first and last name are in one column in the source table but in separate columns in the target table — you can write a procedure to make the conversion. You can use transformation for other business requirements, as well, such as to convert datatypes, units of measurement, character sets, etc. Transformation routines also can be used instead of database triggers to reduce I/O overhead.

When you specify transformation for a table, Post takes no action on the replicated data; it simply passes data values to your procedure. This lets you control the destination of the data after it has been transformed. You can post to the target table and/or you can post to an alternate location. When writing your routine, it is your responsibility to include in your procedure the necessary SQL operations to either post the data to the target database or redirect it to an alternate location (or both).

In this demonstration, transformation procedures are provided that combine data replicated from two separate source tables, od_employee and od_salary into one target table named od_sales_emp_data.

  1. Make certain the SharePlex demonstration objects are installed. See Prework for the demonstrations.
  2. If you ran previous demonstrations, run ora_cleansp (OraCleanSp on Windows systems) on both systems according to the instructions in “Running ora_cleansp” on page 139. This removes the queues from the previous demonstrations and deactivates the previous configuration.

    Note: Prior to running ora_cleansp you must shutdown SharePlex. You can do this using the shutdown command in sp_ctrl.

  3. DELETE (do not TRUNCATE) all rows from the source and target od_employee tables. This table has a cascading DELETE constraint that deletes all rows from the dependent od_salary tables. DO NOT delete any rows from the od_department table. This is a look-up table.
  4. Grant the demonstration user on the target 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

  5. Log into SQL*Plus on the target system as the user who owns the SharePlex demonstration objects, and run the transform.sql script from the util sub-directory of the SharePlex product directory. This installs the od_transform_employee_insert and od_transform_employee_update demonstration transformation routines. You are prompted for a schema and tablespace for this procedure and the name of the Share- Plex Oracle user.
  6. To direct SharePlex to call transformation routines instead of posting the SQL operations, you use the transformation.SID file (where SID is the ORACLE_SID of the target database). Post checks this file to determine if there is a transformation procedure that it must call. This file was installed with SharePlex in the data sub-directory in the SharePlex variable-data directory. Open this file on the target system in either the vi text editor (Unix and Linux) or WordPad (Windows).

  7. Follow Template 3 below to create entries in the file, using the target tables and owners. Separate each column with at least a few spaces or a tab character. Substitute the correct owner names for the owner variables.

    Template 3: Demonstration transformation.SID file

    owner.od_employee I owner.od_transform_employee_insert
    owner.od_employee U owner.od_transform_employee_update
    owner.od_salary I owner.od_transform_employee_insert
    owner.od_salary U owner.od_transform_employee_update
  8. On the target system, enable the parameter:

    SP_OPO_XFORM_EXCLUDE_ROWID

    sp_ctrl(sysB)> set param SP_OPO_XFORM_EXCLUDE_ROWID 1
  9. Follow Template 4 below to create a configuration named od.transform on the source system that replicates the od_salary and od_employee tables.

    sp_ctrl(sysA)> create config od.transform

    Template 4: Demonstration configuration “od.transform”

    datasource:o.source_SID
    owner.od_salary owner.od_salary target_host@o.target_SID
    owner.od_employee owner.od_employee target_host@o.target_SID
  10. Activate the configuration.

    sp_ctrl(sysA)> activate config od.transform
  11. Populate the od_employee and od_salary tables on the source system using the od_add_emps procedure. Use an IN parameter of 10 to create 50 new employees in the od_sales_emp_data table.
  12. In SQL*Plus, select all rows from od_sales_emp_data on the target system and view the transformed data. You will see the following differences due to transformation:

    • The EMPLOYEE_NAME column contains the first and last name of the employee. Compare this to the source od_employee table, where the first and last names are in separate columns.
    • The DEPARTMENT column contains the department name. Compare this to the source od_employee table, where the EMP_DEPT_NO column contains a number. The transformation procedure transformed the replicated department number into the department name by referencing the od_department table.
    • The SALARY column contains the salary from the OD_SALARY table.
  13. [OPTIONAL] To see how transformation works for UPDATEs, you can update the od_employee table manually. The od_transform_employee_update procedure will make the transformation. To further this demonstration, you may construct a transformation procedure for DELETEs.

Demo 4: Demonstration of Generic Conflict Resolution

Conflict resolution is employed in peer-to-peer replication, a replication scenario where users work concurrently on the same data in replica databases, usually on different systems, while SharePlex replicates their changes to keep all databases synchronized. Peerto- peer replication presents challenges that are not found in single-direction replication situations like replicating from a production server to a secondary system for reporting purposes.

In those configurations, there is never DML or DDL activity on the database on the secondary system. In peer-to-peer replication, however, all databases are accessed and changed. If the same record in matching (or “shadow”) tables is changed on different systems at or about the same time, conflicts can occur, and conflict resolution is required.

Conflict resolution relies on PL/SQL procedures (called conflict resolution routines) that tell the SharePlex Post process what to do when a row it needs to change (insert, update or delete) was already changed by another user. For example, SharePlex can be directed to give priority to a specific source system or the most recent timestamp, or it can give specific users priority. In an actual production deployment of peer-to-peer replication, you would develop custom conflict resolution routines based on your company’s business rules and replication environment.

 

About this demonstration

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.
  • Source-system 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, which is referred to as the “secondary” system.

There is no conflict-resolution logic in the demonstration procedure for DELETEs. Instead, SharePlex will write failed DELETE statements to the SID_errlog.sql log and report an error to the Event Log. In addition, information about the statement will be written to the source.exc_table table. To extend this demonstration, you can add conflict- resolution logic for DELETEs to conform to your company’s business rules.

Warning! This demonstration is intended to introduce you to the concept of peer-to-peer replication and conflict resolution. Do not use this demonstration as the basis for establishing peer-to-peer replication, and do not use the provided conflict resolution routines as your own. Peer-to-peer replication is not necessarily compatible with all business applications. It requires a thorough understanding of your data, your applications, your business rules, and how conflicts could occur. When suitable for an environment, it requires careful execution, including the creation of custom conflict resolution procedures that can be quite complex. Before you consider deployment of peer-to-peer replication, please read the documentation for establishing this replication strategy in the SharePlex Administrator’s Guide.

 

Prepare for the demonstration

  1. Make certain the SharePlex demonstration objects are installed. See Prework for the demonstrations.
  2. Shut down SharePlex on both systems.

    sp_ctrl(sysA)> shutdown
     
    sp_ctrl(sysB)> shutdown
  3. Run ora_cleansp (OraCleanSp on Windows systems) on both systems according to the instructions in “Running ora_cleansp” on page 139. This removes the queues from the previous demonstrations and deactivates the previous configuration.
  4. Delete all rows in the od_employee tables on both systems. DO NOT delete any rows from the od_department table. This is a look-up table for the conflict resolution procedure.
  5. Grant the demonstration user on both systems privilege to execute the sp_cr package, which was installed in the SharePlex schema when SharePlexwas 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, and run the p2p.sql script on both systems from the util sub-directory of the SharePlex product directory. This installs the od_employee_gen demonstration conflict resolution routine. You are prompted for a schema and tablespace for this procedure and the name of the SharePlex Oracle user. You also are prompted for the name of the machine that will be the “trusted” source machine, which will take priority when conflicts are generated. It does not matter which system you use.

 

Configure conflict resolution

  1. To direct SharePlex to call conflict resolution routines when there is a conflict, you use the conflict_resolution.SID file (where SID is the ORACLE_SID of the local database). Post checks this file to determine if there is a conflict resolution procedure that it must call. This file was installed with SharePlex in the data sub-directory of the SharePlex variable-data directory on each system. Open this file in either the vi text editor (Unix and Linux) or WordPad (Windows).
  2. Create an entry as shown in Template 5 below in the conflict_resolution.SID file on both systems, using the od_employee table, the correct owner for that table, and the od_employee_gen procedure. Separate each column with a few spaces or a tab.

    Template 5: Demonstration conflict_resolution.SID file

    owner.od_employee IUD owner.od_employee_gen
  3. Start SharePlex and sp_ctrl on both systems.
  4. Referring to Template 6 below, create a configuration named od.cr_trusted_src on the trusted source system that replicates the od_employee table on that system to the od_employee table on the secondary system.

    sp_ctrl(sysA)> create config od.cr_trusted_src

    Template 6: Demonstration configuration “od.cr_trusted_src”

    owner.od_employee owner.od_employee secondary_host@o.secondary_SID
  5. Referring to Template 7 below, create another configuration named od.cr_secondary on the secondary system that is the opposite of the preceding configuration. It replicates the secondary od_employee table to the od_employee table on the trusted source system. Use the secondary database’s ORACLE_SID for the datasource. Use the trusted source system’s name for trusted_source_host. Use the trusted source database’s ORACLE_SID for trusted_source_SID.

    Template 7: Demonstration configuration “od.cr_secondary”

    datasource:o.secondary_SID
    owner.od_employee owner.od_employee trusted_source_host@o.trusted_source_SID
  6. Activate both configurations.

    sp_ctrl(sysA)> activate config od.cr_trusted_src

    sp_ctrl(sysB)> activate config od.cr_secondary

 

Create conflicts

Use the od_add_emps procedure to initially populate the od_employee table on the trusted source system. SharePlex replicates those changes to the od_employee table on the secondary system, and the two are now synchronized. Now you can create conflicts.

To demonstrate source-system priority

In this demonstration, an INSERT that originates on the trusted source will override an INSERT from the other system.

  1. Stop the Post processes on both systems.
  2. Insert a row into od_employee on the secondary system, but do not issue the COMMIT.
  3. Insert the same row on the trusted source, but do not issue the COMMIT.
  4. Issue the COMMIT on the secondary system.
  5. Issue the COMMIT on the trusted source. This should generate a conflict.
  6. Restart the Post processes on both systems.

 

To demonstrate timestamp priority

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

  1. Stop the Post processes on both systems.
  2. Update the EMP_TIMESTAMP column to a different value on each system using the Primary Key, EMP_NO, to find the row.
  3. Wait for the data from each system to replicate to the other system. You will see messages in the queues when you issue the qstatus command from sp_ctrl. The message count will remain there until the COMMIT is sent.

    sp_ctrl(sysA)> qstatus
     
    sp_ctrl(sysB)> qstatus
  4. Issue COMMITs on both systems simultaneously.
  5. Start the Post processes on both systems.
  6. Verify the results by selecting the rows that were updated from both tables.

 

View the results of the Conflict Resolution Demonstration

A table named exc_table was installed in the schema that you specified when you ran the installation script. This table has the following columns, which provide information about the conflicts.

  • EXC_NO

    This column is the exception number of the conflict.

  • EXC_TYPE

    This column is the type of SQL statement, whether INSERT, UPDATE or DELETE.

  • EXC_TARGET_TABLE

    This column is the table on which the conflict occurred.

  • EXC_FIXED

    This column describes the results of the conflict resolution routine. YES means that the routine was successful. NO means that the routine failed and the row needs to be manually changed to the correct value.

  • EXC_INFO

    This column describes what the conflict resolution routine found.

  • EXC_TIMESTAMP

    This column shows the time that the conflict occurred on this machine.

 

This concludes the Advanced SharePlex demonstration.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating