User defined conflict resolution routines for PostgreSQL to Oracle
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.
Before you get started, understand that any table to be accessed through PL/SQL for conflict resolution requires implicitly granted privileges from the owner of the object to SharePlex.
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
For the procedure interface information of Oracle, see Procedure interface for Oracle.
For the procedure interface information of PostgreSQL, see Procedure interface for PostgreSQL.
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.oraA, where oraA is a sid of OracleDB, 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.oraA, where oraA is a sid of OracleDB.
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.
| SchemaName.tableName |
IUD |
schema.procedure |
where:
- IUD is the type of operation that creates the conflict that is resolved with the specified procedure.
- schema.procedure is the schema 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.
- 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.
Example conflict resolution file
| scott.sal |
IUD |
scott.sal_cr |
| scott.cust |
IUD |
scott.cust_cr |
| !DEFAULT |
IUD |
scott.sal_cr5 |
How it works:
- The scott.sal_cr routine is used for the scott.sal table before the scott.sal_cr5 procedure is used for that table.
- For scott.cust, a procedure scott.cust_cr is called for IUD before the default 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 schema 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.
| !DEFAULT |
IUD |
proc0 |
| schema.table1 |
IUD |
proc1 |
| james.table1 |
IUD |
proc2 |
| james.table1 |
IUD |
proc3 |
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.
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.
Supported source and target database combinations
-
Oracle to Oracle
-
PostgreSQL to PostgreSQL
-
PostgreSQL to Oracle
Considerations
Review the following considerations before implementing SharePlex prepared routines.
!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 (Oracle source) or SP_OPX_TRUSTED_SOURCE (PostgreSQL source) parameter to the name of the source system.
Resolution logic
|
INSERT |
If the source is the one specified with SP_OPO_TRUSTED_SOURCE (Oracle source) or SP_OPX_TRUSTED_SOURCE (PostgreSQL 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 |
If the source is the one specified with SP_OPO_TRUSTED_SOURCE (Oracle source) or SP_OPX_TRUSTED_SOURCE (PostgreSQL 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
!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.
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 for Oracle and SP_CAP_REDUCED_KEY parameter for PostgreSQL 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 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.
NOTE: The recommended data type for col_name in a PostgresSQL database is timestamp, as DATE data type does not store time value. As a result, if a date data type is used, a conflict will not be resolved if the same date is updated at both peers at the same time.
!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.
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 for Oracle and SP_CAP_REDUCED_KEY parameter for PostgreSQL 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.
NOTE: The recommended data type for col_name in a PostgresSQL database is timestamp, as DATE data type does not store time value. As a result, if a date data type is used, a conflict will not be resolved if the same date is updated at both peers at the same time.
!UpdateUsingKeyOnly
This routine works for UPDATE operations. It provides conflict resolution that relies solely on the key value of the changed row.
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 INSERT and DELETE operations, custom logic must be used.
Configure Replication through an intermediary system
These instructions show you how to set up cascading replication, also known as multi-tiered replication. This strategy replicates data from a source system to an intermediary system, and then from the intermediary system to one or more remote target systems.
Cascading replication can be used to support various replication objectives as a workaround in such conditions as the following:
- Your replication strategy exceeds the 1024 routes that are allowed directly from a given source system: You can send data to the intermediary system and then broadcast to the additional targets from there.
- The source has no direct connection to the ultimate target, because of firewall restrictions or other factors. You can cascade to a system that does allow remote connection from the source system.
To use a cascading strategy, the source machine must be able to resolve the final target machine name(s), although the ability to make a direct connection is not required.
Supported sources
Oracle
Supported targets
Oracle
Oracle and Open Target (final target)
Capabilities
This replication strategy supports the following:
- Replication to one or more target systems
- Identical or different source and target names
- Use of vertically partitioned replication
- Use of horizontally partitioned replication
- Use of named export and post queues
Requirements
-
Prepare the system, install SharePlex, and configure database accounts according to the instructions in the SharePlex Installation Guide.
Important! Create the same SharePlex user on all systems if you will be using SharePlex to post to a database on the intermediary system.
-
Disable triggers that perform DML on the target objects.
- No DML or DDL should be performed on the target tables except by SharePlex. Tables on the target system that are outside the replication configuration can have DML and DDL operations without affecting replication.
-
If sequences are unnecessary on the target system, do not replicate them. It can slow down replication. Even if a sequence is used to generate keys in a source table, the sequence values are part of the key columns when the replicated rows are inserted on the target system. The sequence itself does not have to be replicated.
DDL Replication Support
DDL replication from source to target through an intermediary system is supported in accordance with the information found in the DDL that SharePlex Supports chapter of the Administration Guide, with the following exceptions:
- DDL initiated on the intermediary system, as opposed to the source, will cause inconsistencies leading to Post errors and should be avoided unless the DDL is synchronized across all systems.
- All systems must be monitored to ensure that latency or errors on the intermediary system do not cause inconsistencies.
Important! These instructions assume you have a full understanding of SharePlex configuration files. They use abbreviated representations of important syntax elements. For more information, see Configure Data Replication.
Conventions used in the syntax
In the configuration syntax in this topic, the placeholders represent the following:
- source_specification[n] is the fully qualified name of a source object (owner.object) or a wildcarded specification.
- target_specification[n] is the fully qualified name of a target object or a wildcarded specification.
- host is the name of a system where SharePlex runs. Different systems are identified by appending a letter to the names, like hostB.
- db is a database specification. The database specification consists of either o. or r. prepended to the Oracle SID, TNS alias, or database name, as appropriate for the connection type. A database identifier is not required if the target is JMS, Kafka, or a file.
Important! Configure Data Replication.
Deployment options
To cascade data, you have the following options:
-
If there is a database on the intermediary system, you can configure SharePlex to post to that database and then capture the data again to replicate it to one or more remote targets.
-
If there is not a database on the intermediary system, you can configure SharePlex to import, queue, and then export the data to one or more remote targets. There is no Capture process on the system. This is known as a pass-through configuration. It passes the data directly from the source system to the target(s).
Cascade with posting on intermediate system
To use this configuration:
- SharePlex database accounts must exist on all systems and must be the same name on all systems. This account is usually created when SharePlex is installed. See the SharePlex Installation Guide for more information.
- Triggers must be disabled in the intermediary database, as well as on the target system.
-
Oracle DDL replication is not supported from an Oracle database on the intermediary system to the target systems. It is supported only from the source system to the intermediary system.
- You create two configuration files: one on the source system, and one on the intermediary system.
-
Enable archive logging on the source and intermediary systems in case the redo logs wrap before Capture is finished with them.
Configuration options on source system
This configuration replicates from the source system to the database on the intermediary system.
Note: In this template, hostB is the intermediary system.
|
datasource_specification |
|
|
| source_specification1 |
target_specification1 |
hostB@o.SID |
| source_specification2 |
target_specification2 |
hostB@o.SID |
Example on source system
| Datasource:o.oraA |
|
|
| hr.emp |
hr.emp2 |
hostB@o.oraB |
| hr.sal |
hr.sal2 |
hostB@o.oraB |
| cust.% |
cust.% |
hostB@o.oraB |
Note: In this same configuration, you could route data from other source objects directly to other targets, without cascading through the intermediary system. Just specify the appropriate routing on a separate line.
Configuration options on intermediary system
This configuration captures the data from the database on the intermediary system, then replicates it to the target system(s). The tables that were the target tables in the source configuration are the source tables in this configuration. The target can be any supported SharePlex target.
|
datasource_specification |
|
|
| source_specification1 |
target_specification1 |
hostC[@db][+...] |
| source_specification2 |
target_specification2 |
hostD[@db][+...] |
Example on intermediary system
| Datasource:o.oraB |
|
|
| hr.emp |
hr.emp2 |
hostC@o.oraC |
| hr.sal |
hr.sal2 |
hostD@o.oraD+hostE@r.mssE |
| cust.% |
cust.% |
!cust_partitions |
Note: The last entry in this example shows the use of horizontally partitioned replication to distribute different data from the sales.accounts table to different regional databases. For more information, see Configure Horizontally Partitioned Replication.
Required parameter setting on intermediary system
(Oracle intermediary database) Set the SP_OCT_REPLICATE_POSTER parameter to 1 if the intermediary database is Oracle. This instructs the Capture process on the intermediary system to capture the changes posted by SharePlex and replicate them to the target system. (The default is 0, meaning that Capture ignores Post activity on the same system.)
In sp_ctrl, issue the following command. The change takes effect the next time Capture starts.
set param SP_OCT_REPLICATE_POSTER 1
Cascade with pass-through on intermediary system
To use this configuration:
Configuration options on source system
Note: In this template, hostB is the intermediary system.
|
datasource_specification |
| source_specification1 |
target_specification1 |
hostB*hostC[@db] |
| source_specification2 |
target_specification2 |
hostB*hostD[@db][+hostB*hostE[@db][+...] |
| source_specification3 |
target_specification3 |
hostB*hostX[@db]+hostY[@db] |
- The hostB*host syntax configures the pass-through behavior.
- If using a compound routing map where all data passes through the intermediary system first, make certain to use the hostB* component in each target route.
- You can also use a compound routing map where data from a source object is replicated directly to one target, and also through the intermediary system to another target, as in the third line of this configuration file.
Example
| Datasource:o.oraA |
|
|
| hr.emp |
hr.emp2 |
hostB*hostC@o.oraC |
| hr.emp |
hr."Emp_3" |
hostB*hostC@r.mssB |
| cust.% |
cust.% |
hostB*hostD@o.oraD+hostE@o.oraE |
Configure Replication to maintain high availability
These instructions show you how to set up SharePlex for the purpose of high availability: replicating to a secondary Oracle database that is a mirror of the source database. This strategy uses bi-directional replication with two SharePlex configurations that are the reverse of each other. The configuration on the secondary (standby) machine remains in an activated state with the Export process on that system stopped in readiness for failover if the primary machine fails.
This strategy supports business requirements such as the following:
- Disaster recovery
- Continuous operation of business applications throughout maintenance cycles or mechanical failures
In this strategy, SharePlex operates as follows:
- Under normal conditions, SharePlex replicates changes from the primary database to the secondary database.
- When the primary system or database is offline and users are transferred to the secondary system, SharePlex captures their changes and queues the data on that system until the primary system is restored.
- When the primary system is restored, SharePlex updates it with those changes and then resumes capture and replication from the primary database.
Supported sources
Oracle
Supported targets
Oracle
Capabilities
This replication strategy supports the use of named export and post queues.
Note: Column mapping and partitioned replication is not appropriate in a high availability configuration. Source and target objects can have different names but this makes the management of a high-availability structure more complicated.
Requirements
For failover purposes, the following are required:
- Make the applications used on the primary system available on the secondary system.
- Copy non-replicated database objects and critical files outside the instance to the secondary system.
- Create a script that grants INSERT, UPDATE and DELETE privileges to all users, which can be run during a failover procedure.
- Create a script that enables constraints on the secondary system to be used during a failover procedure.
- Develop a failover procedure for relocating users to the secondary system.
Note: If you use an Oracle hot backup to create the secondary instance, keep the script. It can be modified to re-create the primary instance.
Conventions used in the syntax
In the configuration syntax in this topic, the placeholders represent the following:
Important! See Configure Data Replication for more information about the components of a configuration file.
Configuration
A high availability configuration uses two configurations that are the reverse of each other. To replicate all objects in the database, you can use the config.sql script to simplify the configuration process. For more information, see Configuration Scripts.
Configuration on the source system (primary system)
|
Datasource:o.oraA |
|
|
| ownerA.object |
ownerB.object |
hostB@o.oraB |
Configuration on the target system (secondary system)
|
Datasource:o.oraB |
|
|
| ownerB.object |
ownerA.object |
hostA@o.oraA |
Make the system ready for failover
- On the secondary system (the one that will initially be the passive system) run sp_ctrl and then issue the following command to stop the Export process on the secondary system so that nothing accidentally happening on the secondary system (such as a scheduled job changing data) gets replicated back to the primary system. This is the required state of SharePlex on that system until there is a need for a role switch between systems.
- Perform initial synchronization and startup. You will activate the source configuration during this procedure. For more information, see Start Replication on your Production Systems.
- Making sure the Export process is stopped on the secondary system, activate the configuration on that system. The configuration on the secondary machine remains in an activated state, but the stopped Export process and lack of user activity ensure that the system remains static in readiness for failover.
- Monitor the SharePlex instance that is linked to the secondary Oracle instance to make sure no non-SharePlex DDL or DML changes were performed. You can do this as follows: View the status of the export queue on the secondary system using the qstatus command in sp_ctrl. The queue should be empty, because the Capture process on a system ignores the Post process on that system. If there are any messages in the export queue, it means those transactions originated on the secondary system or the SP_OCT_REPLICATE_POSTER parameter was mistakenly enabled. See the SharePlex Reference Guide for more information about SharePlex commands and parameters.
- Maintain backups of replication files.
Perform recovery procedures
If a system fails in your high-availability environment, you can move replication to a secondary system and then move it back to the primary system when it is restored. For more information, see Recover Replication after Oracle Failover .
