This demonstration highlights the compare command, and provides a simple demonstration of performance.
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.
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).
Using the editing commands in the text editor, follow Template 1 below to create the configuration, making the following substitutions.
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”
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.
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.
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:
To disable the constraints through SQL*Plus:
SQL> alter table od_salary disable constraint od_sal_emp_no_fk;
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;
In sp_ctrl, issue the activate config command for the od.config configuration.
sp_ctrl(sysA)> activate config od.config
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.
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.
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.
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.
Issue the compare status command on the source system to view the results of the comparisons.
The results should show no out-of-sync rows.
Delete some rows from od_employee on the target system. This causes that table to go out of synchronization with its source table.
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
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
This demonstration allows you to view performance statistics for SharePlex replication.
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.
where #_polls is the number of times to poll the Post process
and poll_interval is the time interval between polls
perf_mon.sh 10 5
In this example, the Post process is polled 10 times at 5-second intervals.
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.
where port_number is the SharePlex port number
Note: This script does not work with multiple post queues using the same port.
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.
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.
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.
To implement vertically partitioned replication, create a new configuration named od.partition on the source system.
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 “! firstname.lastname@example.org_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”
|owner.od_employee (emp_no, emp_first_name, emp_last_name, emp_dept_no)||owner.od_employee !email@example.com_SID|
Activate the configuration on the source system.
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.
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.
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
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).
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
On the target system, enable the parameter:
Follow Template 4 below to create a configuration named od.transform on the source system that replicates the od_salary and od_employee tables.
Template 4: Demonstration configuration “od.transform”
Activate the configuration.
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:
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.
Generic conflict resolution allows you to use one PL/SQL procedure to resolve conflicts for multiple tables. The following conflict-resolution strategies are demonstrated:
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.
Shut down SharePlex on both systems.
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.
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
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.
Template 6: Demonstration configuration “od.cr_trusted_src”
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”
Activate both configurations.
sp_ctrl(sysA)> activate config od.cr_trusted_src
sp_ctrl(sysB)> activate config od.cr_secondary
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.
To demonstrate timestamp priority
In this demonstration, whichever row was updated LAST takes priority when there is a conflict.
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.
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.
This column is the exception number of the conflict.
This column is the type of SQL statement, whether INSERT, UPDATE or DELETE.
This column is the table on which the conflict occurred.
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.
This column describes what the conflict resolution routine found.
This column shows the time that the conflict occurred on this machine.
This concludes the Advanced SharePlex demonstration.