These instructions show you how to set up SharePlex for the purpose of maintaining multiple databases, where applications on each system can make changes to the same data, while SharePlex keeps all of the data synchronized through replication. This is known as peer-to-peer, or active-active, replication. In this strategy, the databases are usually mirror images of each other, with all objects existing in their entirety on all systems. Although similar in benefit to a high-availability strategy, the difference between the two is that peer-to-peer allows concurrent changes to the same data, while high availability permits changes to the secondary database only in the event that the primary database goes offline.
This strategy supports the following business requirements:
- Maintain the availability of mission-critical data by operating multiple instances in different locations.
- Distribute heavy online transaction processing application (OLTP) loads among multiple points of access.
- Limit direct access to an important database, while still enabling users outside a firewall to make updates to their own copies of the data.
An example of peer-to-peer replication is an e-commerce company with three identical databases. When users access the application from a web browser, the web server connects to any of those databases sequentially in a round-robin configuration. If one of the databases is unavailable, the server connects to a different available database server. Thus the configuration serves not only as a failover resource, but also as a means of distributing the load evenly among all the peers. Should the company need to produce business reports, user access to one of the databases can be stopped temporarily, and that database can be used to run the reports.
Note: Data changes made in peer-to-peer replication are prevented from looping back from one machine to another because Capture ignores transactions performed on the local system by the Post process.
Peer-to-peer replication is not appropriate for all replication environments. It requires a major commitment to database design that might not be practical when packaged applications are in use. It also requires the development of conflict resolution routines to prioritize which transaction SharePlex posts to any given database if there are multiple changes to the same data at or near the same time.
Supported source-target combinations
Oracle to Oracle
Capabilities
This replication strategy supports the following:
- Use of named export and post queues
This replication strategy does not support the following:
- Replication of LOBs. If tables with LOBs are included in replication the LOBs will be bypassed by conflict resolution, causing the potential for data to be out of synchronization.
- Column mapping and partitioned replication is not appropriate in a peer-to-peer configuration.
Requirements
Overview
In peer-to-peer replication, DML changes are allowed on copies of the same tables in different databases, usually on different systems, while SharePlex keeps them all current through replication. If a record is changed in more than one database at (or near) the same time, conflicts can occur, and conflict-resolution logic must be applied to resolve the discrepancy.
What is a conflict?
A conflict is defined as an out-of-sync condition — source and target tables are not identical. You can predict that out-of-sync (conflict) situations will occur when a DML statement constructed by SharePlex fails to execute on a row in the target table because of the following reasons:
-
Post applies a replicated INSERT but a row with the same key already exists in the target. Post applies the following logic:
- If all of the current values in the target row are the same as the INSERT values, Post considers the rows to be in-sync and discards the operation.
- If any of the values are different from those of the INSERT, Post considers this an out-of-sync condition.
Note: You can configure Post so that it does not consider non-key values when posting an INSERT. See the SP_OPO_SUPPRESSED_OOS parameter in the SharePlex Reference Guide.
-
Post applies a replicated UPDATE but either cannot find a row in the target with the same key value as the one in the UPDATE or Post finds the correct row but the row values do not match the before values in the UPDATE. Post applies the following logic:
- If the current values in the target row match the after values of the UPDATE, Post considers the rows to be in-sync and discards the operation.
- If the values in the target row do not match the before or after values of the UPDATE, Post considers this an out-of-sync condition.
Note: You can configure Post so that it returns an out-of-sync message if the current values in the target row match the after values of the UPDATE. See the SP_OPO_SUPPRESSED_OOS parameter in the SharePlex Reference Guide.
- A DELETE is performed on the source data, but Post cannot locate the target row by using the key. When Post constructs its DELETE statement, it includes only the key value in its WHERE clause. If the row does not exist in the target, Post discards the operation.
What causes a conflict in peer-to-peer replication?
To understand how SharePlex determines a conflict, refer to the following examples of normal and conflict situations. In the examples, three systems (SysA, SysB and SysC) are used.
The following tables are used in the example:
Scott.employee_source
jane.employee_backup
The column names and definitions are identical:
| EmpNo |
number(4) not null, |
| SocSec |
number(11) not null, |
| EmpName |
char(30), |
| Job |
char(10), |
| Salary |
number(7,2), |
| Dept |
number(2) |
The values for both tables in a synchronized state are:
| 1 |
111-22-3333 |
Mary Smith |
Manager |
50000 |
1 |
| 2 |
111-33-4444 |
John Doe |
Data Entry |
20000 |
2 |
| 3 |
000-11-2222 |
Mike Jones |
Assistant |
30000 |
3 |
| 4 |
000-44-7777 |
Dave Brown |
Manager |
45000 |
3 |
Example of peer-to-peer replication without a conflict
- At 9:00 in the morning, UserA on SysA changes the value of the Dept column to 2, where EmpNo is 1. SharePlex replicates that change to SysB and SysC, and both databases remain synchronized.
- At 9:30 that same morning, UserB on SysB changes the value of Dept to 3, where EmpNo is 1. SharePlex replicates that change to SysA and SysC, and the databases are still synchronized.
Now the row looks like this:
| EmpNo (key) |
SocSec |
EmpName |
Job |
Salary |
Dept |
| 1 |
111-22-3333 |
Mary Smith |
Manager |
50000 |
3 |
Example of peer-to-peer replication with an UPDATE conflict
- At 11:00 in the morning, UserA on SysA updates the value of Dept to 1, where EmpNo is 1. At 11:02 that morning, the network fails. Captured changes rest in the export queues on all systems.
- At 11:05 that morning, before the network is restored, UserB on SysB updates the value of Dept to 2, where EmpNo is 1. The network is restored at 11:10 that morning. Replication data transmission resumes.
- When SharePlex attempts to post the change from UserA to the database on SysB, it expects the value in the Dept column to be 3 (the pre-image), but the value is 2 because of the change made by UserB. Because the pre-images do not match, SharePlex generates an out-of-sync error.
- When SharePlex attempts to post the change from UserB to SysA, it expects the value of the column to be 3, but the value is 1 because of the change made by UserA. SharePlex generates an out-of-sync error.
- When SharePlex attempts to post the changes made by UserA and User B to the database on SysC, both of those statements fail because the pre-images do not match. SharePlex generates an out-of-sync error.
Note: For more information, see Appendix A: Peer-To-Peer Diagram.
Deployment
To deploy peer-to-peer replication, perform the following tasks:
- Evaluate the data for suitability to a peer-to-peer environment. Make any recommended alterations. For more information, see Evaluate the data.
- Configure SharePlex so that data from each system replicates to all other systems in the peer-to-peer environment. For more information, see Configure replication.
- Develop conflict resolution routines that provide rules for how Post handles conflicts. For more information, see Develop conflict resolution routines.
- Create a conflict resolution file. SharePlex refers to this file to determine the correct procedure to use when a conflict occurs. For more information, see List the routines in conflict_resolution.SID.
Evaluate the data
To successfully deploy SharePlex in a peer-to-peer configuration, you must be able to:
- isolate keys
- prevent changes to keys
- control sequence generation
- control trigger usage
- eliminate cascading deletes
- designate a trusted host
- define priorities
These requirements must be considered during the architectural phase of the project, because they demand cooperation with the application. Consequently, many packaged applications are not suitable for a peer-to-peer deployment because they were not created within those guidelines.
Following are more detailed explanations of each of the requirements.
Keys
The only acceptable key in peer-to-peer replication is a primary key. If a table has no primary key but has a unique, not-NULL key, you can convert that key to a primary key. LONG columns cannot be part of the key.
If you cannot assign a primary key, and you know all rows are unique, you can create a unique index on all tables.
The primary key must be unique among all of the databases in the peer-to-peer replication network, meaning:
- it must use the same column(s) in each corresponding table in all databases.
- key columns for corresponding rows must have the same values.
The primary key must be created to contain enough information about a row so there can be no question about the uniqueness of that row, and so that there will be a conflict if a replicated operation would violate uniqueness.
The primary key value cannot be changed.
Supplemental logging of primary and unique keys must be enabled in the database.
Using only a sequence as the primary key probably will not suffice for peer-to-peer replication. For example, suppose the sample table uses sequences to generate values for key column EmpNo. Suppose UserA gets the next sequence value on SysA and inserts a row for “Jane Wilson.” UserB gets the next sequence value on SysB and also inserts a row for “Jane Wilson.” Even if the sequence numbers are different on each system, so there are no unique key violations on the replicated INSERTs, data integrity is compromised because there are now two entries for “Jane Wilson” in the databases, each with a different key. Subsequent UPDATEs will fail. The solution is to include other unique columns in the key, so that there is enough information to ensure uniqueness and ensure a conflict that can then be resolved through resolution logic.
Sequences
SharePlex does not support peer-to-peer replication of sequences. If the application uses sequences to generate all or part of a key, there must be no chance for the same range of values to be generated on any other system in the peer-to-peer configuration. You can use a sequence server or you can maintain sequences separately on each server and make sure you partition a unique range to each one. Quest recommends using n+1 sequence generation (where n = the number of systems in replication). Depending on the type of application, you can add a location identifier such as the system name to the sequence value in the primary key to enforce uniqueness.
Triggers
DML changes resulting from triggers firing on a source system enter the redo log and are replicated to the target system by SharePlex. If the same triggers fire on the target system, they return out-of-sync errors.
To handle triggers in a peer-to-peer configuration, you can do either of the following:
- Disable the triggers.
- Keep them enabled, but alter them to ignore the SharePlex user on all instances in the peer-to-peer configuration. SharePlex provides the sp_add_trigger.sql script for this purpose. This script puts a WHEN clause into the procedural statement of the trigger that tells it to ignore the Post process. For more information, see Set up Oracle database objects for replication.
ON DELETE CASCADE constraints
ON DELETE CASCADE constraints can remain enabled on all instances in the peer-to-peer replication configuration, but you must set the following parameters to direct Post to ignore those constraints:
- SP_OPO_DEPENDENCY_CHECK parameter to 2
- SP_OCT_REDUCED_KEY parameter to 0
- SP_OPO_REDUCED_KEY parameter to 0 (although in other replication scenarios this parameter can be set to different levels, it must be set to 0 in a peer-to-peer configuration)
Balance values maintained by using UPDATEs
Applications that use UPDATE statements to record changes in quantity, such as inventory or account balances, pose a challenge for peer-to-peer replication. The following example of an online bookseller explains the reason why.
The bookseller’s Inventory table contains the following columns.
Book_ID (primary key)
Quantity
Suppose the following sequence of events takes place:
- A customer buys a book through the database on one server. The quantity on hand reduces from 100 books to 99. SharePlex replicates that UPDATE statement to the other server. (UPDATE inventory SET quantity = 99 WHERE book_ID = 51295).
- Before the original UPDATE arrives, another customer buys two copies of the same book on another server (UPDATE inventory SET quantity = 98 WHERE book_ID = 51295), and the quantity on that server reduces from 100 books to 98.
- When the Post process attempts to post the first transaction, it determines that the pre-image (100 books) on the first system does not match the expected value on the second system (it is now 98 as a result of the second transaction). Post returns an out-of-sync error.
A conflict resolution procedure could be written, but how would the correct value be determined? The correct value in both databases after the two transactions should be 97 books, but no matter which of the two UPDATE statements is accepted, the result is incorrect.
For this reason, peer-to-peer replication is not recommended for applications maintaining account or inventory balances using UPDATEs. If you can use a debit/credit method of maintaining balances, you can use INSERT statements (INSERT into inventory values “n”,...) instead of UPDATE statements. INSERT statements do not require a before-and-after comparison with a WHERE clause, as do UPDATE statements.
If your application must use UPDATE statements, you can write a conflict resolution procedure to determine the absolute (or net) change resulting from different UPDATE statements on different systems. For example, in the case of the preceding online bookseller example, when the first customer’s purchase is replicated to the second system, the following conflict resolution procedure fires:
if existing_row.quantity <> old.quantity then old.quantity - new.quantity = quantity_change; update existing_row set quantity = existing_row.quantity - quantity_change;
The conflict resolution logic tells SharePlex that, if the quantity value of the existing row in the target database (98) does not equal the old value (pre-image of 100), then subtract the new value (the replicated value of 99) from the pre-image to get the net change (1). Then, issue an UPDATE statement that sets the Quantity column to 98-1, which equals 97.
When the second user’s change is replicated to the first system, the same conflict resolution procedure fires. In this case, the net change (pre-image of 100 minus the new value of 98) is 2. The UPDATE statement on this system also results in a value of 97, which is 99 (the existing row value after the first customer’s purchase) minus the net change of 2. The result of this procedure’s logic is that the Quantity columns on each system are updated to 97 books, the net effect of selling three books.
The following example illustrates this concept using an account balance within a financial record:
account_number (primary key)
balance
- Suppose a row (an account) in the example table has a balance of $1500 on SysA. CustomerA makes a deposit of $500 on that system. The application uses an UPDATE statement to change the balance to $2000. The change is replicated to SysB as an UPDATE statement (such as UPDATE...SET balance=$2000 WHERE account_number=51295).
- Before the change arrives, CustomerA’s spouse makes a withdrawal of $250 on SysB, and the application updates the database on that system to $1250. When CustomerA’s transaction arrives from SysA and Post attempts to post it to SysB, there is a conflict, since the pre-image from the source system is $1500, but the pre-image on the target is $1250 because of the spouse’s transaction — not a match.
You can write a conflict resolution routine to accommodate this kind of transaction by calculating the absolute (or net) change in the account, then using that value to resolve the conflict. For example:
if existing_row.balance <> old.balance then old.balance - new.balance = balance_change; update existing_row set balance = existing_row.balance - balance_change;
The result of this procedure would be to update the account balance to $1750, the net effect of depositing $500 and withdrawing $250. On SysB, the routine directs SharePlex to subtract the new (replicated) balance of 2000 from the old balance of 1500 for a net change of -500. The UPDATE statement sets the balance value to 1250 - (-500) = 1750, the correct value.
On SysA, the replicated value of 1250 is subtracted from the old balance of 1500 to get the net change of 250. The UPDATE statement subtracts that value from the existing balance of 2000 to get the correct value of 1750.
Priority
When the environment is established to avoid or resolve conflict when SharePlex searches for the correct row to change, the only remaining conflict potential is on fact data — which change to accept when the values for the same column in the same row differ on two or more systems. For this, your application must be able to accept the addition of timestamp and source columns, with source being the name of the local system for the table.
The following explains how those columns play a vital role when using a conflict resolution routine to establish priority.
Trusted source
You must assign a particular database or server to be the prevailing, or trusted, source for two reasons:
- The conflict resolution routine has the potential to get quite large and complex the more systems you have. There are bound to be failures that require resynchronization at some point. One of the systems in the configuration must be considered the true source from which all other systems will be resynchronized if necessary.
- You can write your conflict resolution routines so that operations from the trusted source system take priority over conflicting operations from other systems. For example, changes on the server at corporate headquarters could take priority over the same changes made by a branch office.
Timestamp
It is recommended that you include a timestamp column in the tables and assign priority in the conflict resolution routine to the earliest or latest timestamp. However, the timestamp must not be part of a key, or it will cause conflicts. SharePlex cannot locate rows if a key value changes — and the key value will change if one of the columns is a timestamp.
For timestamp priority to work, you must make sure all of the servers involved agree on the date and time. Tables on servers in different time zones can use Greenwich Mean Time (GMT).
To handle the situation where servers involved are in different time zones, you can specify a 'TIMESTAMP WITH LOCAL TIME ZONE' column in tables to be used by the routine, and make sure that the 'DBTIMEZONE' of databases in peer to peer replication is the same.
The default date format for SharePlex conflict resolution is MMDDYYYY HH24MISS. Tables with default dates must use that format, or conflict resolution will return errors. Before creating a table with a default date, use the following command to change the date format in SQL*Plus.
ALTER SESSION SET nls_date_format = 'MMDDYYYYHH24MISS'
Configure replication
The configuration files on the systems in a peer-to-peer configuration are identical with the exception of the datasource specification and the routing.
Conventions used in the syntax
In the configuration syntax in this topic, the placeholders represent the following items in the environment. This documentation assumes three systems, but there can be more.
Important!
Configuration on hostA
|
Datasource:o.oraA |
| ownerA.object |
ownerB.object |
hostB@o.oraB |
| ownerA.object |
ownerB.object |
hostB@o.oraB |
| ownerA.object |
ownerC.object |
hostC@o.oraC |
| ownerA.object |
ownerC.object |
hostC@o.oraC |
Note: If all owner names and table names are the same on all systems, you can use a compound routing map for each of these configuration files. For example, the compound routing for replication from hostA is as follows:
| Datasource:o.oraA |
| owner.object |
owner.object |
hostB@o.oraB+hostC@o.oraC |
Configuration on hostB
|
Datasource:o.oraB |
| ownerB.object |
ownerA.object |
hostA@o.oraA |
| ownerB.object |
ownerA.object |
hostA@o.oraA |
| ownerB.object |
ownerC.object |
hostC@o.oraC |
| ownerB.object |
ownerC.object |
hostC@o.oraC |
Configuration on hostC
|
Datasource:o.oraC |
| ownerC.object |
ownerA.object |
hostA@o.oraA |
| ownerC.object |
ownerA.object |
hostA@o.oraA |
| ownerC.object |
ownerB.object |
hostB@o.oraB |
| ownerC.object |
ownerB.object |
hostB@o.oraB |
Example
| Datasource:o.oraA |
| hr.emp |
hr.emp |
hostB@o.oraB |
| hr.sal |
hr.sal |
hostB@o.oraB |
| cust.% |
cust.% |
hostB@o.oraB |
Develop conflict resolution routines
To create conflict resolution routines, you write PL/SQL procedures that direct the action of SharePlex when a conflict occurs. Business rules vary widely from company to company, so it is impossible to create a standard set of conflict resolution rules and syntax that apply in every situation. You will probably need to write your own routines. It is good practice to write more than one procedure, such as making site or system priority the primary routine and timestamp a secondary routine. SharePlex invokes one routine after another until one succeeds or there are no more procedures available.
SharePlex provides the following tools that can be used as a basis for your routines:
Important!
- This documentation provides guidelines, examples and templates to assist you, but do not use them as your own routines.
- Test your conflict resolution routines before you put them into production to make sure they work as intended, and to make sure that one routine does not counteract another one.
- By default, SharePlex does not stop for out-of-sync conditions. If failed attempts at conflict resolution are not resolved, the databases can become more and more out of synchronization. Check the Event Log frequently to monitor for out-of-sync warnings by using the show log command in sp_ctrl. See the SharePlex Reference Guide for more information about show log and other SharePlex commands.
- Updates are occasionally made to the conflict resolution logic, so refer to the Release Notes and documentation for your version of SharePlex for any additional information that augments or supersedes these instructions.
How to write a routine using the SharePlex generic interface
SharePlex provides a generic conflict resolution PL/SQL package that can be used to pass information to and from the procedural routines that you write.
Note: Custom routines are supported for Oracle to Oracle source-target combinations only.
Before you get started, understand the following guidelines:
- The same PL/SQL package is used for both generic conflict resolution and transformation (its name is sp_cr). Use either generic conflict resolution or transformation for a table, but not both. Transformed tables cannot be compared by SharePlex and conflict resolution cannot succeed. If both are used, SharePlex only calls the transformation routine. If appropriate, you can use generic conflict resolution and transformation for different tables in the same configuration. For more information, see Configure data transformation.
- Conflict resolution cannot be used for DDL changes.
- Any table to be accessed through PL/SQL for conflict resolution requires implicitly granted privileges from the owner of the object to SharePlex.
- Conflict resolution does not support changes to LONG or LOB columns.
Note: If you ran the SharePlex conflict resolution demonstration in the SharePlex Installation and Setup Guide, you can view a sample generic conflict resolution routine by viewing the od_employee_gen routine that was installed in the database used for the demonstration.
Procedure interface
Follow this template to create your procedure.
| (table_info in outsplex.sp_cr.row_typ, col_values insplex.sp_cr.col_def_tabtyp) |
where:
- splex is the SharePlex schema.
- sp_cr is the name of the package that contains the PL/SQL record and table structures.
- row_typ is the name of the PL/SQL record that passes in/out variables (see Package definition).
- col_def_type is the name of the PL/SQL table that stores column information (see col_def_type table).
Package definition
SharePlex defines PL/SQL record and table structures in a public package named sp_cr in the SharePlex database schema. The package uses the following parameters.
CREATE SCHEMA IF NOT EXISTS sp_cr;
CREATE TYPE sp_cr.row_typ AS
(src_host VARCHAR(32),
src_db VARCHAR(32),
src_time VARCHAR(20),
statement_type VARCHAR(6),
source_table VARCHAR(128),
target_table VARCHAR(128),
native_error INTEGER,
sql_state VARCHAR(10)
);
CREATE TYPE sp_cr.col_def_typ AS
(column_name VARCHAR,
datatype VARCHAR,
is_key BOOLEAN,
is_changed BOOLEAN,
old_value VARCHAR ,
new_value VARCHAR
);
CREATE SEQUENCE EXC_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 1 CACHE 20 NO CYCLE ;
IN variables
For each row operation that causes a conflict, SharePlex passes this metadata information to your procedure.
| src_host |
The name of the source system (where the operation occurred). It is case-sensitive and is passed using the same case as on the source system, for example SysA. If there are named post queues in use on the target system, this variable consists of the name of the post queue, for example postq1. Note: Maximum length is 32 characters. A host name longer than 32 will be truncated to 32 characters. |
| src_ora_sid |
The ORACLE_SID of the source database. It is case-sensitive and is passed in the same case as in the oratab file, Windows Registry or V$PARAMETER table. |
| src_ora_time |
The timestamp of the change record in the source redo log. |
| source_rowid |
The row ID of the source row. It is passed as a literal within single quotes, for example ‘123456’. |
| target_rowid |
The row ID of the corresponding row in the target database. SharePlex obtains the row ID by querying the target database. It is passed as a literal within single quotes, for example ‘123456’. If the row cannot be found using the PRIMARY key, the value is NULL. |
| statement_type |
A letter, either I, U or D, indicating whether the operation is an INSERT, UPDATE or DELETE statement. |
| source_table |
The owner and name of the source table, expressed as owner.table. This value is case-sensitive and matches the way the table is named in the database. It is passed within double quotes, for example "scott"."emp." |
| target_table |
The owner and name of the target table, expressed as owner.table. This value is case-sensitive and matches the way the table is named in the database. It is passed within double quotes, for example "scott"."emp." |
| oracle_err |
This is different, depending on whether the procedure is being used for conflict resolution or transformation.
Transformation: SharePlex passes a value of 0 for this variable. This variable is only used for conflict resolution.
Conflict resolution: The Oracle error number that caused the conflict. |
OUT variables
These variables direct the action of SharePlex based on whether the procedure succeeded or failed).
| status |
Defines whether or not the procedure succeeded. You must specify a value for this parameter.
-
A value of 0 implies successful execution. It acts differently, depending on whether the procedure is used for conflict resolution or transformation.
Transformation: Post does not write any SQL. SharePlex does not write any error messages to the Event Log when transformation succeeds. It continues its processing by reading the next replicated operation in the post queue.
- Conflict resolution: A value of 0 directs SharePlex to proceed with the SQL statement. SharePlex does not write any log entries to the Event Log when conflict resolution succeeds.
- A value of 1 implies that the procedure was unsuccessful. In this case, the action SharePlex takes depends on what you specified as the action variable.
- (Transformation only) A value of 7 implies unsuccessful execution and instructs the Post process to stop.
|
| action |
Defines the action that you want SharePlex to take. This is different, depending on whether the procedure is used for transformation or conflict resolution.
Transformation: You must specify a value of 0 for this parameter, which directs SharePlex NOT to post the SQL statement. Your transformation routine is responsible for posting the results of the transformation either to the target table or another table. The outcome of this action depends on what you specify for the reporting variable
Conflict resolution: Specifies the action to take as a result of an unsuccessful conflict resolution procedure. You must specify a value for this parameter.
-
A value of 0 directs SharePlex NOT to post the SQL statement. The outcome of this action depends on what you specify for the reporting variable.
In addition, it directs SharePlex to try the next conflict resolution procedure that you listed in the conflict resolution file, if one exists.
- The value of 1 is reserved for internal SharePlex use. Do not use it.
- A value of 2 directs SharePlex to try the next conflict resolution procedure that you listed in the conflict resolution file, if one exists.
|
| reporting |
Determines how SharePlex reports unsuccessful procedural results. You must specify a value for this parameter.
|
col_def_type table
SharePlex creates a col_def_tabtyp PL/SQL table for each replicated operation. This table stores column information. It is different depending on whether the procedure is used for transformation or conflict resolution.
- Transformation: For each row operation, SharePlex writes column information to col_def_type.
- Conflict resolution: For each row operation that causes a conflict, SharePlex writes column information to col_def_tabtyp.
All fields are passed by SharePlex to your routine, although not all will have values if SharePlex cannot locate the row.
Following is the data type that is used to populate the col_def_tabtyp table.
CREATE SCHEMA IF NOT EXISTS sp_cr;
CREATE TYPE sp_cr.row_typ AS
(src_host VARCHAR(32),
src_db VARCHAR(32),
src_time VARCHAR(20),
statement_type VARCHAR(6),
source_table VARCHAR(128),
target_table VARCHAR(128),
native_error INTEGER,
sql_state VARCHAR(10)
);
CREATE TYPE sp_cr.col_def_typ AS
(column_name VARCHAR,
datatype VARCHAR,
is_key BOOLEAN,
is_changed BOOLEAN,
old_value VARCHAR ,
new_value VARCHAR
);
CREATE SEQUENCE EXC_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 1 CACHE 20 NO CYCLE ;
Description of col_def_tabtyp
| column_name |
Tells your procedure the name of the column that was replicated from the source table, for example emp_last_name. This value is not case-sensitive. |
| data type |
Tells your procedure the data type of the data in the replicated column, for example VARCHAR2. This value is always in capital letters. |
| is_key |
Tells your procedure whether or not the column is a key column. If it is a key column, SharePlex passes a value of TRUE. If the column is not part of a key, SharePlex passes a value of FALSE. |
| is_changed |
Tells your procedure whether or not the column value has changed. If it is changed, SharePlex passes a value of TRUE. If the column is not changed, SharePlex passes a value of FALSE.
- For INSERTs, is_changed is TRUE for non-NULL values, because none of the columns existed in the database. If a NULL value is inserted, is_changed is FALSE.
-
For UPDATEs, is_changed is TRUE for non-key columns. For key columns, is_changed normally is FALSE, but SharePlex will pass a value for a changed key column.
Conflict resolution only: If a key value also was changed on the target system, SharePlex will not be able to locate the correct row, and conflict resolution could fail.
- For DELETEs, is_changed is always FALSE, because SharePlex replicates only the key values for a DELETE statement.
|
| old_value |
Tells your procedure the old value of the replicated column, before it was changed on the source system. This column is NULL for INSERTs, because the row did not exist in the target database before the INSERT.
Conflict resolution only: This is the pre-image against which SharePlex compared the source and target columns as part of its synchronization check for UPDATEs and DELETEs. If the old value passed by SharePlex does not match the current_value value obtained from the target row, then there is a conflict. |
| new_value |
Tells your procedure the new value of the replicated column, as changed on the source system. |
| current_value |
Tells your procedure the current value of the column in the target table. If SharePlex cannot locate the target row, the value is NULL. |
Example entries in col_def_tabtyp table per operation type
The following tables illustrate the possible outcomes of each type of operation.
INSERT operation
| C1 |
TRUE |
NULL |
bind |
NULL |
FALSE |
| C2 |
TRUE |
NULL |
bind |
NULL |
TRUE |
| C3 |
FALSE |
NULL |
NULL |
NULL |
TRUE | FALSE |
1 When an INSERT fails, it is because a row with the same PRIMARY key already exists in the target database. SharePlex does not return the current value for INSERTs.
UPDATE operation
| C1 |
TRUE |
bind |
bind |
NULL | target_value |
FALSE |
| C2 |
FALSE |
bind |
NULL |
NULL | target_value |
TRUE |
| C3 |
TRUE |
bind |
bind |
NULL | target_value |
TRUE |
1 (Conflict resolution) When an UPDATE fails, it is because SharePlex cannot find the row by using the PRIMARY key and the pre-image. If the row cannot be found, SharePlex searches for the row by using only the PRIMARY key. If SharePlex finds the row, it returns the current value for the key column as well as the changed columns. If SharePlex cannot find the row by using just the PRIMARY key, then SharePlex returns a NULL.
2 (Transformation) For an UPDATE, SharePlex cannot locate a row using the PRIMARY key and the pre-images, because the pre-images are different due to transformation. As an alternative, it searches for the row using just the PRIMARY key. If it finds it, SharePlex returns the current value for the key column as well as the changed columns. If it cannot locate the row using just the PRIMARY key, then current_value is NULL
DELETE operation
| C1 |
FALSE |
bind |
NULL |
NULL |
TRUE |
1 When a DELETE fails, it is because SharePlex could not find the row by using the PRIMARY key. Therefore, SharePlex returns a NULL.
How to use the SharePlex prepared routines
SharePlex provides optional prepared routines for use in conjunction with custom routines. These options can be used with basic and generic conflict resolution formats. There are no limitations on column types.
Supplemental logging of primary and unique keys must be enabled in the database.
Considerations
Review the following considerations before implementing SharePlex prepared routines.
!UpdateUsingKeyOnly
This routine works for UPDATE operations. It provides conflict resolution that relies solely on the key value of the changed row.
Note: This routine can be used only with an Oracle to Oracle source-target combination.
Supported source-target: This routine can be used only with Oracle to Oracle source-target combinations.
Normally, when SharePlex builds a SQL statement to post data, the WHERE clause uses both the key and the pre-image of the columns that changed to ensure synchronization. The !UpdateUsingKeyOnly routine directs SharePlex to post the data even though the pre-image values do not match, assuming the keys match.
If appropriate, this routine can be used as the sole routine for UPDATEs, but with the understanding that it does not include logic that assigns priority, such as system or time priority, in case of multiple concurrent UPDATEs. To avoid out-of-sync errors, Quest recommends using !UpdateUsingKeyOnly in conjunction with other, more specific routines, relying on !UpdateUsingKeyOnly as a final option if the custom routines fail.
Important: !UpdateUsingKeyOnly must be the last entry in the list of routines, thus assigning it last priority.
In the following example, when there is a conflict for owner.table1 during an UPDATE, SharePlex calls the two custom routines first (in order of priority) and then calls the !UpdateUsingKeyOnly routine.
| owner.table1 |
u |
owner.procedure_up_A |
| owner.table1 |
u |
owner.procedure_up_B |
| owner.table1 |
u |
!UpdateUsingKeyOnly |
The !UpdateUsingKeyOnly name is case sensitive. It must be typed exactly as shown in these instructions, with no spaces between words. Do not list an owner name with this routine in the configuration file. For more information, see List the routines in conflict_resolution.SID.
For INSERT and DELETE operations, custom logic must be used.
!HostPriority
This prepared conflict resolution routine works for INSERT, UPDATE, and DELETE operations. It provides host-based conflict resolution by assigning priority to the row change that originated on the trusted source system. To define the trusted source, set the SP_OPO_TRUSTED_SOURCE or SP_OPX_TRUSTED_SOURCE parameter to the name of the source system.
Resolution logic
|
INSERT |
If the source is the one specified with SP_OPO_TRUSTED_SOURCE or SP_OPX_TRUSTED_SOURCE, convert the INSERT to an UPDATE and overwrite the existing row.
Otherwise, discard the change record and do nothing to the target row. |
| UPDATE |
(Oracle only) If the source is the one specified with SP_OPO_TRUSTED_SOURCE, overwrite the existing row using an UPDATE and use only the key columns in the WHERE clause. Otherwise, discard the change record and do nothing to the target row. |
| DELETE |
Ignore the out-of-sync error and do nothing to the target row. |
Syntax in conflict resolution file
owner.table {I | U | D} !HostPriority
For more information, see List the routines in conflict_resolution.SID.
!MostRecentRecord
This prepared routine works for INSERT, UPDATE, and DELETE operations. It provides time-based conflict resolution by assigning priority to the most recent row change, as determined by a timestamp.
Note: This routine can be used only with an Oracle to Oracle source-target combination.
To capture the timestamp, tables using this routine must have a non-NULL timestamp column that is updated with every INSERT and UPDATE on the table. If the timestamp column in the DML, or in the existing row, is NULL, this routine cannot resolve the conflict.
This routine requires the SP_OCT_REDUCED_KEY parameter to be set to 0 on the source system, so that all of the pre-image values of UPDATES are available to the Post process.
Resolution logic
|
INSERT
and
UPDATE |
- If the timestamp of the replicated operation is greater than the timestamp of the row in the target, overwrite the existing row using an UPDATE and use only the key columns in the WHERE clause.
- If the timestamp of the replicated operation is less than or equal to the timestamp of the row in the target, discard the change record and do nothing to the target row.
|
| DELETE |
Ignore the conflict (out-of-sync message). |
Syntax in conflict resolution file
owner.table {I | U | D} !MostRecentRecord(col_name)
Where col_name is the timestamp column to be used by the routine.
See List the routines in conflict_resolution.SID.
!LeastRecentRecord
This prepared routine works for INSERT, UPDATE, and DELETE operations. It provides time-based conflict resolution by assigning priority to the least recent row change, as determined by a timestamp.
Note: This routine can be used only with an Oracle to Oracle source-target combination.
To capture the timestamp, tables using this routine must have a non-NULL timestamp column that is updated with every INSERT and UPDATE on the table. If the timestamp column in the DML, or in the existing row, is NULL, this routine cannot resolve the conflict.
Resolution logic
|
INSERT
and
UPDATE |
- If the value of the timestamp column of the replicated operation is greater than or equal tothe timestamp column of the row in the target, discard the replicated operation and do nothing to the target row.
- If the timestamp column of the replicated operation is less than the timestamp column of the row in the target, overwrite the existing row using an UPDATE and use only the key columns in the WHERE clause.
|
| DELETE |
Ignore the conflict (out-of-sync message). |
Syntax in conflict resolution file
owner.table {I | U | D} !LeastRecentRecord(col_name)
Where col_name is the timestamp column to be used by the routine.
See List the routines in conflict_resolution.SID.
List the routines in conflict_resolution.SID
After you create the conflict resolution procedure(s), construct the conflict resolution file. This file tells SharePlex which procedures to use for which objects and operation types, and in which order.
Where to find the conflict resolution file
A blank conflict_resolution.SID file, where SID is the ORACLE_SID of the target instance, was included in the data sub-directory of the SharePlex variable-data directory when SharePlex was installed. Use the file on the target system.
If this file does not exist, you can create one in ASCII format in an ASCII text editor. It must be named conflict_resolution.SID, where SID is the ORACLE_SID of the target instance. Note: the SID is case-sensitive.
Important! There can be only one conflict_resolution.SID file per active configuration.
How to make entries in the conflict resolution file
Use the following template to link a procedure to one or more objects and operation types.
| owner.object |
{i | u | d | iud} |
owner.procedure |
where:
- owner.object is the owner and name of a target object, or a wildcarded entry. (See Syntax rules)
- i| u | d is the type of operation that creates the conflict that is resolved with the specified procedure. You can specify any or all operation types, for example id or iud. Upper or lower case are both valid.
- owner.procedure is the owner and name of the conflict resolution procedure that will handle the specified object and operation type.
Syntax rules
- There must be at least one space between the object specification, the operation type specification, and the procedure specification.
- You can use the LIKE operator and a SQL wildcard (%) to specify multiple objects by using a search string. (See the Example.)
- You can use an underscore (_) to denote a single-character wildcard. For table names that contain an underscore character (for example emp_sal), SharePlex recognizes the backslash (\) as an escape character to denote the underscore as a literal and not a wildcard, for example: like:scott.%\_corp\_emp. If you are not using the LIKE operator, the backslash escape character is not required if an object name contains an underscore.
- The order in which you list the procedures in the conflict resolution file determines their priority of use (in descending order). If you list a table-specific procedure, SharePlex uses it before procedures that are specified with a wildcarded object name.
- You a comment line anywhere in the file. Start a comment line with the pound symbol (#).
Example conflict resolution file
| scott.sal |
IUD |
scott.sal_cr |
| like:scott.%\_corp\_emp |
IUD |
scott.emp_cr1 |
| like:scott.%\_corp\_emp |
IUD |
scott.emp_cr2 |
| like:scott% |
IUD |
scott.emp_cr3 |
| scott.cust |
U |
scott.sal_cr |
How it works:
- The scott.sal_cr routine is used for the scott.sal table before the scott.emp_cr1 procedure is used for that table.
- The scott.emp_cr1 procedure is used before the scott.emp_cr2 procedure for all tables meeting the search criteria, and so forth.
- For scott.cust, a procedure is called for UPDATEs before the other routines are used for all operations.
How to specify SharePlex prepared routines in the conflict resolution file
To use the SharePlex prepared routines for all tables in the replication configuration, use the !DEFAULT parameter instead of specifying an owner and object name.
A custom routine takes priority over a SharePlex prepared routine. A prepared routine is used only if the custom routine fails. This is true regardless of the order in which the !DEFAULT-associated routine appears in the file.
The !DEFAULT parameter is case-sensitive. It must be typed in all capital letters.
In the following example, the !UpdateUsingKeyOnly procedure is used for UPDATEs and DELETEs for all tables, including james.table1, although the user-defined procedures listed for james.table1 take precedence.
| !DEFAULT |
U |
!UpdateUsingKeyOnly |
| !DEFAULT |
D |
!UpdateUsingKeyOnly |
| james.table1 |
U |
james.procedure_upd |
| james.table1 |
I |
james.procedure_ins |
| james.table1 |
D |
james.procedure_del |
How to change the conflict resolution file while replication is active
You can change the conflict resolution file any time during replication to add and remove tables and procedures. After you change the conflict resolution file, stop and re-start the Post process.
Log information about resolved conflicts
You can configure the Post process to log information about successful conflict resolution operations if you are using the SharePlex prepared routines. This feature is disabled by default.
Note: This feature can be used only with Oracle to Oracle source-target combinations.
To enable the logging of conflict resolution
- Run sp_ctrl on the target system.
-
Issue the following command:
sp_ctrl> set param SP_OPO_LOG_CONFLICT {1 | 2}
-
A setting of 1 enables the logging of conflict resolution to the SHAREPLEX_CONF_LOG table.
Note: A setting of 1 will not update the columns EXISTING_TIMESTAMP and TARGET_ROWID (when existing data is not replaced) in the SHAREPLEX_CONF_LOG table.
-
A setting of 2 enables the logging of conflict resolution to the SHAREPLEX_CONF_LOG table with Post query for additional meta data.
Using LeastRecentRecord or MostRecentRecord prepared routines Post will query the target database for the timestamp column of the existing record. The query result is logged into the EXISTING_TIMESTAMP column of the SHAREPLEX_CONF_LOG table.
For any prepared routines, on rows that aren't replaced by the incoming record, Post will query the TARGET_ROWID of the existing row that could have been replaced. Otherwise the ROWID of the existing row will not be logged.
Note: A setting of 2 may affect the performance of Post as a result of making the query.
- Restart Post.
Post logs the information to a table named SHAREPLEX_CONF_LOG. The following describes this table.
| CONFLICT_NO |
NUMBER NOT NULL |
The unique identifier of the resolved conflict. This value is generated from the shareplex_conf_log_seq sequence. |
| CONFLICT_TIME |
TIMESTAMP DEFAULT SYSTIMESTAMP |
The timestamp of the conflict resolution |
| CONFLICT_TABLE |
VARCHAR2(100) |
The name of the target table that was involved in the conflict |
| CONFLICT_TYPE |
VARCHAR2(1) |
The type of conflict, either I for insert, U for update, or D for delete |
| CONFLICT_RESOLVED |
VARCHAR2(1) NOT NULL |
Indicator of whether the conflict was resolved or not.
Y = yes, the conflict was resolved
N = no, the conflict was not resolved. Unresolved conflicts are logged to the ID_errlog.sql file, where ID is the source database identifier. |
| TIMESTAMP_COLUMN |
VARCHAR2(50) |
The name of the column that contains the timestamp that Post compared to determine which record was most recent. |
| INCOMING_TIMESTAMP |
DATE |
The timestamp that the row was inserted, updated, or deleted on the source system |
| EXISTING_TIMESTAMP |
DATE |
The current timestamp of the row in the target database. This applies only if the SP_OPO_LOG_CONFLICT parameter is set to 2, which directs Post to query the target database to get this value. |
| PRIMARY_KEYS |
VARCHAR2(4000) |
The names of the primary key columns |
| MESSAGE |
VARCHAR2(400) |
A message that states which row won in the conflict. The row that wins depends on which conflict resolution routine was used. For example, the following message is returned when the !MostRecentRecord routine is used and the most recent record is the source record:
Incoming timestamp > existing timestamp. Incoming wins, overwrite existing.
If the target record was the most recent one or has the same timestamp as the source record, then the message would be:
Incoming timestamp <= existing timestamp. Existing wins, discard incoming. |
| SQL_STATEMENT |
LONG |
The final SQL statement that got executed as a result of the conflict resolution |
| CONFLICT_CHECKED |
VARCHAR2(1) |
Indicates whether or not someone reviewed the conflict. The default is N for No. The person who reviews the conflict can change this value to Y. |
