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
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
- Run sp_ctrl
-
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
| activate config filename |
[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
| 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.
| 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
Syntax
|
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
| 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
| analyze config filename |
n {minutes | hours | days} |
[ on host |
on host:portnumber |
on login/password@host |
on login/password@host:portnumber ] |
Syntax 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.
| 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
| 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
| 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.
| 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 |
