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
-
PostgreSQL Database as a Service to PostgreSQL Database as a Service
-
PostgreSQL Database as a Service to Oracle
-
PostgreSQL Database as a Service to PostgreSQL
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 to the 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.
|
NOTEs:
-
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
-
The case sensitivity of the col_name for the Oracle peer depends on the source database.
-
If the data is being replicated from Oracle to Oracle, the col_name should be in uppercase.
-
If the data is being replicated from PostgreSQL to Oracle, the col_name should be in lowercase.
-
If the data is being replicated from both Oracle and PostgreSQL sources to Oracle, there should be two different entries of conflict resolution routines for col_name – one in uppercase and the other in lowercase. |
!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.
|
NOTEs:
-
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
-
The case sensitivity of the col_name for the Oracle peer depends on the source database.
-
If the data is being replicated from Oracle to Oracle, the col_name should be in uppercase.
-
If the data is being replicated from PostgreSQL to Oracle, the col_name should be in lowercase.
-
If the data is being replicated from both Oracle and PostgreSQL sources to Oracle, there should be two different entries of conflict resolution routines for col_name – one in uppercase and the other in lowercase. |
!UpdateUsingKeyOnly
This routine works for UPDATE operations. It provides conflict resolution that relies solely on the key value of the changed row. 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.
Log information about resolved conflicts for Oracle database
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.
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. |
Log information about resolved conflicts for PostgreSQL database
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.
To enable the logging of conflict resolution:
- Run sp_ctrl on the target system.
-
Issue the following command:
sp_ctrl> set param SP_OPX_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 column existing_timestamp (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.
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 |
bigserial primary key |
The unique identifier of the resolved conflict. This value is generated from the shareplex_conf_log_conflict_no_seq sequence. |
| conflict_time |
timestamp |
The timestamp of the conflict resolution. |
| src_host |
varchar(64) |
The host name of source host. |
| curr_host |
varchar(64) |
The host name of current host. |
| trusted_host |
varchar(64) |
The host name of trusted host. It will be used in case of !HostPriority prepared routine. |
| src_db |
varchar(150) |
The source database name. |
| source_rowid |
varchar(20) |
The source table rowid. For PostgreSQL source, this column is not applicable. Its value will be N/A. |
| target_rowid |
varchar(20) |
The target table rowid. For PostgreSQL target, this column is not applicable. Its value will be N/A. |
| conflict_table |
varchar(300) |
The name of the target table that was involved in the conflict. |
| conflict_type |
char(1) |
The type of conflict, either I for insert, U for update, or D for delete. |
| conflict_resolved |
char(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. |
| odbc_error |
varchar(20) |
Indicates odbc error which caused the conflict. Its format is <native error>:<error sqlstate> |
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 and PostgreSQL
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 SharePlex to Replicate Data.
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 SharePlex to Replicate Data.
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.
Note: For CrunchyData High Availability cluster environment setup information, see the Install SharePlex on PostgreSQL High Availability Cluster section in the SharePlex Installation and Setup Guide.
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 and PostgreSQL
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 SharePlex to Replicate Data 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 the Configuration Scripts section in the SharePlex Reference Guide.
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 .
