Chat now with support
Chat with Support

SharePlex 11.0 - SharePlex Reference Guide

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

activate config

Use the activate config command to activate a configuration. Replication begins immediately as soon as the activation process is complete. For more information about what happens when you activate a configuration, see the SharePlex Administration Guide.

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 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.

Activation creates asynchronous, parallel processing threads to activate multiple tables simultaneously. Each table is locked for a very short time, just long enough to activate the table. Replication of each table begins as soon as its activation is complete.

Should one or more table fail to activate, SharePlex continues with the activation of the other tables. If an application uses NOWAIT locking on tables in the replication configuration, it could fail if it attempts to obtain a lock on an object being activated.

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 datasource (Oracle instance) on each system. For example, if there are ConfigA, ConfigB and ConfigC for instance ora10, you can activate only one of them at a time. Activating another configuration for the same datasource automatically deactivates the first one.
  • Do not perform DDL, including TRUNCATE, during activation. DML changes are the only permissible changes during activation.
  • Activation requires that the applications have retry logic. NOWAIT locking on tables in the replication configuration may cause the application to fail if it attempts to obtain a lock on an object that is being activated.
  • The activation process retains control of the sp_ctrl interface until the activation is finished. To activate multiple configurations for different datasources on the same system, activate the first one, then open another session of sp_ctrl to activate the second one. Open as many sessions of sp_ctrl as you have configurations to activate.
  • 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.

Set the Number of Activation Threads

You can set the number of activation threads globally (for all activations) and you can override this setting for any activation.

To set the number of threads globally:

  1. Run sp_ctrl
  2. Issue the following command. You may use a value of up to 32 threads.

    sp_ctrl> set param SP_OCF_THREAD_COUNT number_of_threads

To set the number of threads for the current activation:

Use the [threads=n] option when you issue the activate config command.

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.

Because SharePlex continues with activation whether or not individual tables fail to activate, it alerts you when tables fail to activate by displaying the following error message at the sp_ctrl prompt: “WARNING, not all objects activated successfully. Check activation log.”

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. For more troubleshooting advice, see the SharePlex Administration Guide.

Usage

Supported sources: Oracle (all options)
Supported targets: All
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 options Remote options
activate configfilename

[threads=n]

[nolock]

scn=scn_value

seqno=log_sequence_number

[ on host |

on host:portnumber |

on login/password@host |

on login/password@host:portnumber ]

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

threads=n

(Valid for Oracle) Use this option to set the number of analysis threads that the activation process generates. This option overrides the default value set by the SP_OCF_THREAD_COUNT parameter.

The range of valid values for n is 1 to 32, but it is recommended that you use no more than 5 threads because the benefits of using threads generally diminish beyond that point. SharePlex will not start more threads than the number of tables to be analyzed.

When used, this option must appear after the required command arguments.

Example:

sp_ctrl(sysA)> activate config sales threads=3

 

nolock

(Valid for Oracle) Use this option to activate without locking the tables being added to replication.

scn=scn_value

(Valid for Oracle) Use this option to activate the configuration to start replication at a specific SCN in the redo logs. Before activating the configuration, do the following:

  • If there was a previously active configuration, run the ora_cleansp utility on the source and all targets to restore the environment to a clean state. For more information, see Ora_cleansp.
  • Use the show scn command to get the SCNs of the last transactions that were posted from all the Post processes (if using named queues). Use the lowest of those SCN values for activate config.

Do not use this option with the nolock option.

Example:

sp_ctrl> activate config myconfig scn=123456

seqno=log_sequence_number

(Valid for Oracle) Use this option to activate the configuration to start replication at a specific redo log sequence number.

Do not use this option with the nolock option.

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

    Example:

    activate config myconfig seqno=98765

  • 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

    add partition

    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 hash value or the column condition specification that creates the row partition.
    • 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 sources: Oracle
    Supported targets: All
    Authorization level: Operator (2)
    Issues on: source system
    Related commands: Modify partition, Drop partition, Drop partition scheme, View Partitions

    Syntax

    Basic command Command options Remote options

    add partition to scheme_name

    set

    {condition = column_condition |

    hash = hash_value}

    and

    route = routing_map

    [and name = partition_name]

    [and tablename = target_table]

    [and description = description]

    Not available
    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).

    The condition and hash components are mutually exclusive.

    hash = value

    Creates a row partition based on a hash value. The specified value determines the number of row partitions in the partition scheme.

    The condition and hash components are mutually exclusive.

    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@o.myora or sysB:q1@o.myora or sysB@o.myora+sysC@o.myora (compound routing map).

    If the target is JMS, Kafka, or a file, then the target should be specified as x.jms, x.kafka, or x.file, for example: sysA:hpq1@x.kafka.

    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.

    Partition based on a hash:

    Use the following format to direct SharePlex to create a named post queue for each partition:

    host:basename|#{o.SID | r.database}

    where:

    • host is the name of the target system.
    • basename is the base name that is assigned to all queues.
    • |# directs SharePlex to number the queues sequentially by appending the base name with an integer, starting with 1 to the value set with hash.
    • o.SID for an Oracle target or r.database for an Open Target target.
    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@o.orasid 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@o.orasid

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

    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@o.orasid and tablename = ora1.targ

    sp_ctrl> add partition to scheme1 set name = west and condition = "area = west" and route = sys2w@o.orasid and tablename = ora2.targ

    Row partitions based on a hash value

    Divide rows into four partitions, each processing through a different post queue:

    sp_ctrl> add partition to scheme1 set hash = 4 and route = sysb:hash|#@o.ora112

    analyze config

    Use the analyze config command to run an analysis of the tables in a configuration file. This command gathers information about the activity of the tables.

    Important! Do not activate the configuration before you run the analysis, and make certain there are no other active configurations when you run it. The use of this command is similar to an actual activation.

    The analyze process writes out its results based upon the data gathered at the time that was specified in the command, and then the replication stream cleans itself up.

    The analysis is written to a file in the log subdirectory of the variable-data directory. The name of the file is:

    o.datasource-analysis.actid

    The analyze process maintains information about the activity of each object in replication, as well as transaction information. The transaction information can be used to identify groups of tables that are interrelated in such a way that they should be replicated in the same replication stream (same set of queues and processes).

    The analysis lists each group of related tables, the total number of operations per table , and the total number of operations for the group.

    For example:

    >cat o.w111a64f-analysis.1575

    Activity Analysis

     

    Group 1 of related tables: 1000 total operations in group

    "TEST"."SS2_TEST1" 346

    "TEST"."SS2_TEST2" 348

    "TEST"."SS2_TEST3" 306

     

    Group 2 of related tables: 1124 total operations in group

    "TEST"."SRC_TEST1" 232

    "TEST"."SRC_TEST2" 177

    "TEST"."SRC_TEST3" 178

    "TEST"."SRC_TEST4" 175

    "TEST"."SRC_TEST5" 188

    "TEST"."SRC_TEST6" 174

     

    Tablename Inserts Updates Deletes Rollbacks Total
    "TEST"."SS2_TEST2" 146 169 33 0 348
    "TEST"."SS2_TEST1" 140 176 30 0 346
    "TEST"."SS2_TEST3" 116 158 32 0 306
    "TEST"."SS2_TEST1" 75 114 29 14 232
    "TEST"."SS2_TEST5" 61 94 22 11 188
    "TEST"."SS2_TEST3" 69 73 28 8 178
    "TEST"."SS2_TEST2" 69 77 21 10 177
    "TEST"."SS2_TEST4" 54 89 19 13 175
    "TEST"."SS2_TEST6" 61 79 25 9 174

    To view the current state of analysis:

    Use the show analyze command to view the state of the analysis:

    sp_ctrl (alvspxl11:8567)> show analyze detail

     

    Host: alvspxl11.quest.com

    Operations  
    Source Status Processed Since Total Backlog
    ------ ------------ ----------- ------- ------ -------
    o.w111a64f Running 1497 17-Mar-12 10:41:54 1496 0
               

    Last operation processed:

    Redo log: 295 Log offset: 32327800

    UPDATE of "TEST"."SRC_TEST3" at 03/17/12 0:59:17

     

    Activation id : 1573
    Operations processed : 1497
    Transactions processed : 398
    Analysis complete : 20-Mar-12 10:41:54

    To terminate the analysis before completion:

    To terminate the analysis before it is complete, use the abort config or deactivate config command, or modify the SP_ANL_RUN_TIME parameter.

    Usage

    Supported sources: Oracle
    Supported targets: All
    Authorization level: Administrator (1)
    Issued 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 options Remote options
    analyze config filename n {minutes | hours | days}

    [ on host |

    on host:portnumber |

    on login/password@host |

    on login/password@host:portnumber ]

    Syntax description
    Component Description
    filename 

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

    Example:

    sp_ctrl(sysA)>analyze config sales

    n {minutes | hours | days) The number of minutes, hours, or days worth of activity to analyze.
    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

    Example

    analyze config testconf 5 days

     

    sp_ctrl (alvspxl11:8567)> show analyze

    Process Source Target State PID
    ------ ------------ ----------- ------- ----
    Capture o.w111a64f   Running 2968
    Analyze o.w111a64f   Running 2976

     

    append status

    Use the append status command to view the status of the last copy or append command job run. The append status command can be used to view detailed status on a copy or append job or a portion of a copy or append job, or to view status on all copy and append jobs for which SharePlex has history.

    For details about using the append status command, refer to the below example:

    Usage

    Supported sources: Oracle
    Supported targets: Oracle
    Authorization level: Viewer
    Issued for: source or target
    Related commands: copystatus

    Syntax

    Basic command Command options Remote options
    append status

    [job_id]

    [Job_id.table_id]

    [all]

    [full]

    [detail]

    [status]

    [ on host |

    on host:portnumber |

    on login/password@host |

    on login/password@host:portnumber ]

    Syntax description
    Component Description
    job_id

    Displays status history for the job with the specified SharePlex-assigned job ID.

    Example: sp_ctrl(sysA)>append status 28282

    job_id.table_id

    Displays status history for the job with the specified SharePlex-assigned job ID and table.

    Example: sp_ctrl(sysA)>append status 2828.HR.SRC_TEST3

    all

    Displays a summary line for every job with history in the database.

    Example: sp_ctrl(sysA)>append status all

    full

    Displays the status of every object in the job. By default, the job status command displays the status of those objects not completed, or completed with an exceptional status.

    Example: sp_ctrl(sysA)>append status 2828 full

    detail

    Displays detail information for every object reported upon. By default, the job status command displays a summary line for every object reported upon. Note that the detail information is the same as is displayed for the job_id.table_id option.

    Example: sp_ctrl(sysA)>append status detail

    [status]

    Displays status history for previous jobs with the specified status.

    Example: sp_ctrl(sysA)>append status "Error"

    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