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.
This chapter reviews some common problems that you could experience when installing or running SharePlexfor the first time after installation.
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.