Chat now with support
Chat with Support

SharePlex 11.4 - Reference Guide

About this guide Conventions used in this guide Revision History SharePlex Commands for Oracle SharePlex parameters SharePlex Commands for PostgreSQL SharePlex Parameters for PostgreSQL General SharePlex utilities Oracle Cloud Infrastructure SharePlex environment variables

Activate Config for PostgreSQL

Use the activate config command to activate a configuration. Replication begins immediately as soon as the activation process is complete.

The activation process reads the configuration file, from which it gets all of the information needed for SharePlex to:

  • Identify the objects that are in replication
  • Route the replicated changes to the appropriate source and target database
  • Generate the SQL that Post uses to apply the changes to the target
  • Activate all of the tables that have been added to replication

The process that sp_cop calls to activate a configuration is sp_tconf.

Notes:

  • While activating the configuration using logical replication, if any uncommitted transactions are being executed on the PostgreSQL instance, you may experience lag in the config activation.

  • For partitioned tables, users must set the replica identity to full for all its sub-partitions. If not set, verify config will display a message as "Object may not be replicated because replica identity is not full for its partitions." Activation will not add the partitioned table in replication. It will log all the sub-partition table names that do not have replica identity set to full in the activation log.

Guidelines for activation

  • To activate a configuration, the database containing the objects to be replicated must be mounted and open. The length of time that activation takes varies, depending on the size, number and structure of the configured objects.
  • You can activate one configuration per PostgreSQL server on each system. For example, if there are ConfigA, ConfigB and ConfigC for instance dbname1, you can activate only one of them at a time. Activating another configuration for the same datasource automatically deactivates the first one.
  • Before you activate a configuration, use the verify config command to confirm that basic requirements for successful activation and replication have been satisfied. The command alerts you to potential problems that can cause the activation to fail.

View activation status and results

SharePlex activates objects according to their object ID, not their order in the configuration file, so there is no way to predict the order of activation.

To view the results of activation, issue the show config command

What to do if activation fails

Many things can cause the activation of a table or the entire configuration to fail. For example, if one or more components in the configuration file were entered incorrectly, activation of the affected objects fails.

If you did not issue the verify config command before you activated, run it now, and correct any problems that it finds. Then, try activating again.

Usage

Supported sources: PostgreSQL (pglsn option only applicable for physical replication.) , Amazon RDS for PostgreSQL, Amazon Aurora for PostgreSQL, Azure Database for PostgreSQL Flexible Server, and Google Cloud SQL for PostgreSQL
Supported targets: PostgreSQL, Oracle, SQL Server, Kafka, Amazon RDS for PostgreSQL, Amazon Aurora for PostgreSQL, Azure Database for PostgreSQL Flexible Server, and Google Cloud SQL for PostgreSQL
Authorization level: Administrator (1)
Issues for: source system
Related commands: abort config, copy config, create config, deactivate config, edit config, list config, purge config, remove config, rename config, show config, verify config , view config

Syntax

Basic command Command option
activate config filename pglsn=lsn_value
Syntax description
Component Description
filename 

Required. The name of the configuration that you want to activate. Configuration names are case-sensitive.

Example: sp_ctrl(sysA)>activate config sales

pglsn=lsn_number

Use this option to activate the configuration to start replication at a specific LSN in the WAL files. Before activating the configuration, do the following:

If there was a previously active configuration, run the pg_cleansp utility on the source and all targets to restore the environment to a clean state. For more information, see pg_cleansp.

Use the show last_posted command to get the LSNs of the last transactions that were posted from all the Post processes (if using named queues). Use the lowest of those LSN values for activate config.

Example: sp_ctrl> activate config myconfig pglsn=6/555FAE0

Note: Activate with LSN is not supported for logical replication. If database goes out of sync, user need to sync the data manually using external utilities.

Note: After successful activation physical and logical slots will get created in the database.

 

Important!

Steps to follow when performing the alter table add/drop column operation which is part of ongoing replication.

Cautions:

  • Do not stop the Capture process while altering the table.

  • No DML operations should be performed on the table for which column is being added or dropped.

Follow the below steps:

  1. Execute the query for alter table add/drop column without performing any DML on the table.

  2. Reactivate the config file again so that the latest details are fetched and stored in the object cache of all the processes.

  3. Once reactivation is done, then DML operations can be performed on the table.

Steps to follow when performing the alter table add/drop partition operation which is part of ongoing replication.

Cautions:

  • Do not stop the Capture process while altering the table. (applicable only if reactivation is being done)

  • No DML operations should be performed on the table for which partition is being added or dropped.

Follow the below steps:

  1. Execute the query for alter table add/drop partition without performing any DML on the table.

  2. Reactivate the config file again or restart the Capture process so that the latest partition details are fetched and used by the Capture process.

  3. Once reactivation or restart Capture process is done, then DML operations can be performed on the table.

Add Partition for PostgreSQL

Use the add partition command to add a row partition to a partition scheme when configuring horizontally partitioned replication. Issue an add partition command for each row partition that you want to create.

This command captures all of the information required to create the partition, including the following required components:

  • The partition scheme name. To create a new partition scheme, specify the name in the add partition command that creates the first row partition for that scheme. SharePlex automatically creates the partition scheme. Then, specify that name when adding additional row partitions to that partition scheme.
  • The routing for the rows that are specified in the row partition.

Reactivate the configuration file if the command affects a table that is already being replicated. SharePlex will only lock tables for which there are configuration changes.

For more information about how to configure horizontally partitioned replication, see the SharePlex Administration Guide.

Usage

Supported source:

PostgreSQL (on-prem), Amazon RDS for PostgreSQL, Amazon Aurora for PostgreSQL, Azure Database for PostgreSQL Flexible Server, and Google Cloud SQL for PostgreSQL

Supported targets: PostgreSQL, Oracle, SQL Server, Kafka, Amazon RDS for PostgreSQL, Amazon Aurora for PostgreSQL, Azure Database for PostgreSQL Flexible Server, and Google Cloud SQL for PostgreSQL
Issues on: source system
Related commands: modify partition, drop partition, drop partition scheme, view partitions

Syntax

Basic command Command options

add partition toscheme_name

set

{condition = column_condition |

route = routing_map

[and name =partition_name]

[and tablename =target_table]

[and description =description]

Syntax description
Component Description
to scheme_name

to is a required keyword indicating the row partition is being added to scheme_name.

scheme_name is the name of the partition scheme. The partition scheme is created by the first add partition command that you issue, which will also specify the first set of rows to partition.

If you are making heavy use of horizontal partitioning, it may help to establish naming conventions for your partition schemes.

set

Required keyword that starts the definition of the row partition.

condition = column_condition

Creates a row partition based on a column condition. The condition must be in quotes. Use standard WHERE conditional syntax such as ((region_id = West) and region_id is not null).

route = routing_map

The route for this partition. This can be one of the following:

Partition based on a column condition:

Specify any standard SharePlex routing map, for example: sysB@r.dbname or sysB:q1@r.dbname or sysB@r.testdb+sysC@r.testdb (compound routing map).

To route a partition to multiple target tables that have different names, do the following:

  • Issue a separate add partition command for each different target name. Use the tablename option to specify the name.
  • In the configuration file, specify any of these target tables as the target table in the entry that uses this partition scheme. SharePlex will detect the other names when the configuration is activated.
  • Set the SP_ORD_FIRST_FIND parameter to 0 so that SharePlex checks all of the column conditions in the partition scheme. By default SharePlex assumes that any given row change will satisfy only one column condition in the partition scheme.
name = name

(Recommended) A short name for this partition. This option is only useful for partitions based on column conditions. A name eliminates the need to type out long column conditions in the event that you need to modify or drop the partition in the future.

tablename = owner.table

(Optional) Use this option when there are multiple target tables and one or more have different names. Issue a separate add partition command for each name.

The table name must be fully qualified. If case-sensitive, the name must be specified in quotes.

Example:

add partition to scheme1 set name = p1 and condition = "C1 > 200" and route = sysb:p1@r.dbname and tablename = myschema.mytable

description = description (Optional) Description of this partition.
Examples
Row partitions based on column conditions

Route different sets of rows through different post queues:

sp_ctrl> add partition to scheme1 set name = q1 and condition = "C1 >= 200" and route = sysb:q1@r.dbname

sp_ctrl> add partition to scheme1 set name = q2 and condition = "C1 < 200" and route = sysb:q2@r.dbname

Route different sets of rows to different target systems and different table names from the source:

sp_ctrl> add partition to scheme1 set name = east and condition = "area = east" and route = sys1e@r.dbname and tablename = schema1.targ

sp_ctrl> add partition to scheme1 set name = west and condition = "area = west" and route = sys2w@r.dbname and tablename = schema2.targ

Cancel command for PostgreSQL

Use the cancel command to cancel a running compare and repair command job.

To cancel a job, you must supply its job ID. The job ID is reported back from the compare and repair job when you issue the command that starts it:

sp_ctrl>repair using 1elliot

repairing 7 of 7 objects

repair started; job id 408

Alternatively, you can get the job ID of the most recently streamed job by issuing the job status command:

sp_ctrl>job status

Job ID: 408

PID: 11527

Host: prodsys

Started: 22-FEB-15 18:08:09

Job Type: Repair

Status: Processing - 0 objects completed

Usage

Supported sources: PostgreSQL
Supported targets: PostgreSQL
Authorization level: Operator (2)
Issues on: source system
Related commands: compare, repair

Syntax

Basic command Remote options
cancel job_id

[ on host |

on host:portnumber |

on login/password@host |

on login/password@host:portnumber ]

Syntax description
Component Description
jobID

The ID of the job to be canceled.

Example:

sp_ctrl>cancel 407

Remote options

These options enable you to issue the command on a remote machine and to script commands that include a login name, password, port number, or combination of those items.

Option Description
on host

Execute the command on a remote system (one other than the one where the current sp_ctrl session is running). You are prompted for login credentials for the remote system. If used, must be the last component of the command syntax.

Example: sp_ctrl(sysB)>status on SysA

on host:portnumber

Execute the command on a remote system when a remote login and port number must be provided. If used, must be the last component of the command syntax.

Example: sp_ctrl(sysB)>status on SysA:8304

on login/password@host

Execute the command on a remote system when a remote login, password, and host name must be provided. If used, must be the last component of the command syntax.

Example:sp_ctrl(sysB)>status on john/spot5489@SysA

on login/password@host:portnumber

Execute the command on a remote system when a remote login, password, host name, and port number must be provided. If used, must be the last component of the command syntax.

Example: sp_ctrl(sysB)>status on john/spot5489@SysA:8304

Clear History command for PostgreSQL

Use the clean history command to remove the information and logs from old compare, and repair command jobs.

These jobs generate log files on the source and target systems. By default, the job information and log files are cleaned when the job is older than the value set with the SP_SYS_JOB_HISTORY_RETENTION parameter. The clear history command can be used to clear the job information and logs on demand for a specific job or table, or for all jobs that are of a specific age.

Usage

Supported sources: PostgreSQL
Supported targets: PostgreSQL
Authorization level: Operator (2)
Issues on: source system
Related commands: compare, repair

Syntax

Basic command Command options Remote options

clear history

{ all |

source_owner.source_table |

age days |

jobID }

[for r.dbid]

[ on host |

on host:portnumber |

on login/password@host |

on login/password@host:portnumber ]

Syntax description
Component Description
all

Causes all jobs and log history to be removed.

Example:

sp_ctrl(sysA)> clear history all

source_owner.source_table

Causes history for a particular table to be removed. History gets deleted as per SP_SYS_JOB_HISTORY_RETENTION parameter.

Example:

sp_ctrl(sysA)> clear history clear history user2.employee

age days

Causes the job history older than the specified number of days to be removed.

Example:

sp_ctrl(sysA)> clear history age 10

jobID

Causes the jobs for the specified job id (obtained using the job status command) to be removed. History gets deleted as per SP_SYS_JOB_HISTORY_RETENTION parameter.

Example:

sp_ctrl(sysA)> clear history jobID

for r.dbid

Optional. Can be used to employ the clear history command when there is no active configuration, or if there are more than one active configurations. In either case, the source SID must be specified using the for option.

Example:

sp_ctrl(sysA)>clear history all for r.dbid

Remote options

These options enable you to issue the command on a remote machine and to script commands that include a login name, password, port number, or combination of those items.

Option Description
on host

Execute the command on a remote system (one other than the one where the current sp_ctrl session is running). You are prompted for login credentials for the remote system. If used, must be the last component of the command syntax.

Example: sp_ctrl(sysB)>status on SysA

on host:portnumber

Execute the command on a remote system when a remote login and port number must be provided. If used, must be the last component of the command syntax.

Example: sp_ctrl(sysB)>status on SysA:8304

on login/password@host

Execute the command on a remote system when a remote login, password, and host name must be provided. If used, must be the last component of the command syntax.

Example:sp_ctrl(sysB)>status on john/spot5489@SysA

on login/password@host:portnumber

Execute the command on a remote system when a remote login, password, host name, and port number must be provided. If used, must be the last component of the command syntax.

Example: sp_ctrl(sysB)>status on john/spot5489@SysA:8304

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating