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> |
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 |
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 .
Configure DDL Replication
