Chat now with support
Chat with Support

SharePlex 8.6.6 - Installation Guide

About this Guide Conventions used in this guide Introduction to SharePlex SharePlex pre-installation checklist Set up SharePlex in an Oracle cluster Set up SharePlex on Amazon Cloud Download SharePlex UNIX Installation and Setup Windows Installation and Setup Assign SharePlex users to security groups Basic SharePlex demonstrations Advanced SharePlex Demonstrations Solve Installation Problems Uninstall SharePlex SharePlex Utilities Appendix A: Advanced installer options Appendix B: Install SharePlex as root Appendix C: SharePlex installed items

Demo 2: Demonstration of Partitioned Replication

Advanced SharePlex Demonstrations > 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 Install the demonstration objects.
  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

Advanced SharePlex Demonstrations > 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 Install the demonstration objects.
  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

Advanced SharePlex Demonstrations > 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 Install the demonstration objects.
  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.

Solve Installation Problems

Solve installation problems
Contents

 

This chapter reviews some common problems that you could experience when installing or running SharePlexfor the first time after installation.

Did you review the Release Notes for this version?

Sometimes there are special installation instructions that supersede or supplement certain instructions in this manual. In addition, there can be known issues for this version that you should be aware of during or after installation. Please read the Release Notes for the version of SharePlex that you are installing before you begin the installation process.

 

Related Documents