SharePlex 11.2 - Reference Guide

Use the compare and compare using commands (collectively known as the comparison commands) to verify that a target table is in synchronization with its source table.

The compare command compares individual source and target tables or any number of tables in a given schema through the use of wildcards. When comparing individual tables, you can make use of column-based filtering to control the rows that are selected for comparison.
The compare using command compares all of the tables listed in the active configuration or in another file that contains a subset of the tables in the active configuration.

After running a comparison, you can run the repair or repair using command to repair any out-of-sync rows.

Note: A running comparison or repair does not affect the source tables in any way. SharePlex logs into the database only to query for read consistency, and the locks on the source tables are brief. SharePlex briefly locks the target tables during the processing, but users can continue accessing them with little or no awareness of the lock.

Supported operations

SharePlex can detect and repair out-of-sync rows in a target table that are caused by DML operations: INSERT, UPDATE, DELETE.

Not supported

SharePlex does not support (and will skip) the comparison and repair of the following:
- UDT inside VARRARY. Other UDTs are supported.
- Sequences.
- Tables for which transformation was used.
- Network configurations in which a pass-through server is used to pass data between the source and target servers.
- XMLtype with different storage on the source and target (CLOB on source, BINARY on target, or the other way around). Like to like compare/repair is supported.
- VARRAY types not inside SDO_GEOMETRY or ST_GEOMETRY.
Comparisons and repairs are not supported in a cascading replication environment.
Do not perform DDL on a table that is being compared or repaired. A comparison does not detect out-of-sync conditions caused by DDL operations, including those that SharePlex supports. If the DDL changes the table definition, it invalidates the SELECT statement that is built by the comparison process to get the rows that need to be compared. The following error indicates that DDL occurred:

Oracle Error: ORA-01466: unable to read data - table definition has changed

Once you correct an out-of-sync condition caused by DDL, you can use the repair command to resynchronize the data in the rows.
Comparison and repair command strings longer than 255 characters are not supported. This is an operating system limitation. To work around this limitation, use the edit command on the source system. You can type the command string within a text file, and then the command automatically executes the file.

See the SharePlex Release Notes for additional information about data types that are supported by compare and compare using

Other conditions

The tables that you want to compare or repair must be part of an active configuration file.
Uncommitted transactions on a source table prevent the comparison and repair processes from obtaining the brief locks they need to obtain read consistency. Make certain that all transactions are committed before you run a comparison or repair.
Replication latency reduces the performance of compare and repair processing. The message from the source that spawns the comparison and repair processes on the target is sent through the queues with the replicated data. Delays caused by a data backlog also delay the spawn message and can cause the source process to lose its read consistency. If possible, perform comparisons and repairs during off-peak hours.
Do not truncate a table that is being compared or repaired. The comparison commands take a snapshot of each source table when they start. If a table is truncated, the table view in the snapshot is truncated, and this can cause the command to return invalid out-of-sync conditions.
To repair a view, the following must be true:
- The view table must contain all of the constraint columns of the underlying table.
- The names of the constraint columns in the view must be identical to the names of the constraint columns in the underlying table.

Special use cases

The following scenarios require special handling when running a comparison.

Use case	Compare support
Consolidated replication	Consolidated replication is supported if the target database and Post processes are configured to add the ID of the source host to each row. To compare or repair the correct rows in the central target table, use the targetwhere option and base the where clause on the source ID value. For example, to compare a table in the database at the Eastern headquarters of a company to the correct rows in the central corporate database, you could use a source ID of "East" for the Eastern database and then base the targetwhere clause on that value. Use the same targetwhere clause in the repair command. The comparison and repair processes can use the source ID value to select only the rows that are valid for the Eastern database. The use the comparison or repair commands for any implementation of consolidated replication, other than one that identifies a source ID, may result in the unwanted deletion of target rows. For more information about this configuration, see the SharePlex Administration Guide. You may need to combine the targetwhere option with the standard where option to ensure that the target rows are selected accurately.
Peer-to-peer replication	In a peer-to-peer configuration, you must decide which system is the trusted source system and which is the secondary, or target, system. The secondary system is the one where any repairs will be performed. Before you run a comparison or repair in a peer-to-peer environment, follow these steps: Stop user access on the secondary system and wait for replicated operations from that system to post to the trusted source database. Users can continue to access the source database. Issue the qstatus command on the source and secondary systems. When there are 10 or fewer messages in the queues, run the comparison from the source system. During a comparison, you can permit user access to the source and secondary databases after the sp_desvr and sp_declt start. Use the repair command with the where option to repair selected rows in a target table without locking users out of the table. For more information about this configuration, see the SharePlex Administration Guide.
Tables without keys	The comparison and repair commands issue a SELECT statement with an ORDER BY clause on the source and target systems. The ordering is faster if large tables have a primary key or a unique, non-null key and an index (preferably a unique index). Otherwise, all of the columns are used as a key. If a table has no unique row identifier, but does have one or more columns that can identify a row as unique, you can use the compare command with the orderby option. When this option is used, SharePlex prints a notice to the sp_desvr log on the source system that the command used those columns as a key.
Tables with extra source or target columns	Use the compare command with the sourcewhere or targetwhere clause if the source or target table contains extra columns and those columns contain unique values on which to base the sorting. See Control which rows are compared.
Compare operation on the XML data	SharePlex displays the “ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT” error while performing the Compare operation on the XML data. Workaround: Upgrade Oracle to version 19.19 DBRU Set an appropriate PGA_AGGREGATE_LIMITusing the Oracle suggested script provided below : WITH MAX_PGA as (select round(value/1024/1024,1) max_pga from v$pgastat where name='maximum PGA allocated'), MGA_CURR as (select round(value/1024/1024,1) mga_curr from v$pgastat where name='MGA allocated (under PGA)'), MAX_UTIL as (select max_utilization as max_util from v$resource_limit where resource_name='processes') SELECT a.max_pga "Max PGA (MB)", b.mga_curr "Current MGA (MB)", c.max_util "Max # of processes", round(((a.max_pga - b.mga_curr) + (c.max_util * 5)) * 1.1, 1) "New PGA_AGGREGATE_LIMIT (MB)" FROM MAX_PGA a, MGA_CURR b, MAX_UTIL c WHERE 1 = 1; Upgrade to or install the SharePlex version 11.1.

How the comparison works

A comparison detects out-of-sync conditions in a target table that are caused by DML operations:

extra or missing rows
rows whose values do not match

When you run the compare or compare using command on the source system, SharePlex initiates the following events:

The sp_cop process spawns a sp_desvr (server) process on the source system.
The sp_desvr process returns control and use of the sp_ctrl interface to the user, and replication continues while the comparison proceeds.
The sp_desvr process sends a message to the Post process to initiate a sp_declt (client) process on the target system.
The server and client processes establish direct communication with each other.
The processes perform a sanity check by comparing the number of rows in the source and target tables. If the difference in the number of rows between a source and target table is larger than 20 percent, the command terminates. The sanity check prevents wasting time to compare or repair tables that could be fixed more quickly by reloading the target data. You can use the copy or append command to reload the target data or use another method of your choosing. To override the sanity check, reissue the compare command with the override option.
To override the sanity check, reissue the Compare command with Override option.
If the row count passes the sanity check, the tables are compared as follows:
- If the compare command is being used, sp_desvr selects the source rows, and sp_declt selects the target rows, then the rows are sorted and compared.
- If the compare using command is being used, the sp_desvr process creates a number of processing threads on the target system. The value set by the SP_DEQ_THREADS parameter controls the number of threads created. Each thread spawns an sp_declt (client) process. The server and client processes establish direct communication with each other. The processing load is divided among the client processes. The rows from each source and target table are selected, sorted and compared.
When finished, the processes write their log files, and you can view the results with the show compare command.

Manage the SQL log file

The compare and repair commands write the SQL that is needed to repair any out-of-sync rows to a SQL file in the same location as the log files. If only a compare command is issued, SharePlex does not execute these SQL statements. If a repair command is issued, the command works identically to the compare commands except that it executes the SQL statements to repair the out-of-sync rows.

You can suppress the output of the SQL log file. Some reasons to suppress this file are:

The data contains sensitive information. The SQL log file is written in clear text. By not producing a SQL log file, the sensitive data is not persisted to disk, which may satisfy security requirements for data at rest, such as those required to meet PCI compliance standards.
The compared or repaired tables have a very large number of out-of-sync rows. A log file of this size can consume a large amount of disk space.

To suppress the SQL log file, use the nosqllog option with the compare or repair command.

To suppress the output of the SQL log file for all compare and repair runs while the current instance of SharePlex is running, set the SP_SYS_SECURE_MODE environment variable to 1. This variable must be set before starting SharePlex, so if the sp_cop process is running it must be restarted after setting this variable. When sp_cop is run with this environment variable, the compare and repair commands will not put data into SQL files and the Post process will not put data into the SharePlex error log.

Run multiple processes

All of the compare and repair commands enable you to run multiple processes concurrently.

Multiple compare and repair commands can operate concurrently, each processing a pair of source and target tables, or you can use one command with Oracle wildcards to specify multiple sets of tables. See the SharePlex Administration Guide for more information about how SharePlex supports wildcards.
The compare using and repair using commands operate on an entire file. For example, you can compare or repair the tables in an entire configuration file, or you can create one or more compare files or repair files to affect a subset of the target tables, and then run one or more of them concurrently. See the command syntax for instructions.

A maximum of 20 SharePlex processes can use the post queue at the same time, including the replication processes and the comparison and repair processes. It is recommended that you allow a maximum of five comparison and repair processes to run at any given time. By using the compare using and repair using commands, you can work around the 20-process limit by comparing more tables per process.

If a comparison or repair fails because the limit is reached, SharePlex logs a message to the Event Log.

Note: You can run multiple commands more easily by using the edit command to edit a previous command to create a new one.

Compare a subset of a configuration

You can compare subsets of an active configuration file in the following ways.

To compare all of the tables in replication that belong to one schema, use the compare command with a wildcard:

sp_ctrl> compare scott.%
To compare all of the tables in a configuration file, use the compare using command:

sp_ctrl> compare using myconfig
To compare all of the tables in replication to one target route, use the compare using command with the at option:

sp_ctrl> compare using config.active at prodsys@o.ora112
To compare a custom subset of the tables in a configuration, specify them in a compare file. This is a plain text file that lists only the source tables that you want to compare. The target tables are taken from the configuration file at the time of comparison. You can create a compare file by using the create config or copy config command. Make certain to give this file a name that makes it clear it is not a configuration file. See the command syntax for more information.

Control which rows are compared

The compare and repair commands have where options that enable you to filter the rows that are selected for processing. By default, these commands affect all rows of a table and ignore columns in the target table that are not contained in the source table.

Use the where option to filter rows based on identically named columns in the source and target tables.
Use the where option for a table that uses vertically partitioned replication. The source and target columns can have different names. Base the where selection on the source columns. SharePlex reads the column mappings from the configuration file to build the correct WHERE clause for the target table.
Use the sourcewhere and targetwhere options if one or more extra columns exists in either the source or target table and those rows contain values that determine row uniqueness.
- Use sourcewhere if the source table contains the extra columns.
- Use targetwhere if the target table contains the extra columns.
To use this option correctly:
- Use a sourcewhere or targetwhere option only for the extra columns.
- Use the standard where option for the other columns that have the same name on both source and target.
- SharePlex combines the where option with the sourcewhere or targetwhere option to create the complete WHERE clause.
Important! If you plan to run both a comparison and repair for a target table that has extra rows, only use targetwhere to compare for UPDATEs and DELETEs. The repair command cannot determine the correct values for INSERTs. To work around this issue, set a default value for the extra columns or manually update the inserted rows.

Identify processes

Every time that a comparison or repair command is issued, the job ID is shown in the sp_ctrl display. If the sp_ctrl display is not available, you can view the job ID by running the compare status command.

View status and results in sp_ctrl

To view the status or results of a comparison, use the compare status command in sp_ctrl.

The basic command displays the processing status of the most recently started comparison job, as well as other comparison jobs that are still running.
Additional options can be used to display a status summary for comparison jobs for which there is history, or to display detailed information about a comparison job.

For more information, see Compare status.

View warnings and errors

The sp_desvr and sp_declt processes write a log file on the system where they run. The logs are stored in the log sub-directory of the SharePlex variable-data directory.

The name of the log written by the sp_desvr process is desvr_JobID_SID_pProcessID.log, where:

JobID is the SharePlex-assigned job ID.
SID is the ORACLE_SID of the Oracle instance where the source table resides.
ProcessID is the process ID of the sp_desvr process.

The names of the files written by the sp_declt process are declt_JobIDTableID_SID_SourceHost_pProcessID appended with either .log or .sql, where:

JobID is the SharePlex-assigned job ID for the job.
TableID is the SharePlex-assigned table ID for the table in the job.
SID is the ORACLE_SID of the Oracle instance where the source table resides.
SourceHost is the name or IP address of the source host.
ProcessID is the process ID of the sp_declt process.

Example log file names:

desvr_606_ora112_p14610.log

declt_606-1_ora112_prodsys_p6528.log

declt_606-1_ora112_prodsys_p6528.sql

To control disk usage, the logs are aged in a circular fashion. SharePlex generates a new log file when the current log reaches the size limit. New logs are created up to a maximum number of logs, and then SharePlex starts overwriting the oldest log.

Note: For the compare using command, there rarely is more than one log file.

Cancel a compare job

Use the cancel command to stop a running comparison or repair job.

sp_ctrl(sysA)>cancel JOBID

For more information, see Cancel.

Manage compare history and logs

SharePlex retains a history of each finished job in the database on the source system. The SP_SYS_JOB_HISTORY_RETENTION parameter controls how long history is retained.

To clear this history on demand, use the clear history command. When SharePlex removes the history of a job, it also removes the log file that was the source of the history.

To remove the log files from the source system without clearing the job history from the database, use the remove log command. You can also use this command to remove old log files from the target system.

To control the size of the log files, set the SP_DEQ_LOG_FILESIZE parameter.

To produce separate SQL files for INSERT, UPDATE, and DELETE operations, rather than use just one file for all operation types, use the log split option.

Control the batch size

You can control the size of the block of rows that is fetched when the process makes its SELECT query. The block size is calculated based on the value set with the SP_DEQ_MALLOC parameter. The value is divided equally by the number of comparison threads to be used, and then it is recalculated based on the size of all of the columns added together.

Repair out-of-sync rows

To repair out-of-sync rows found by the comparison, use the repair or repair using command. See Repair / repair using.

Usage

Supported sources:	Oracle
Supported targets:	Oracle
Authorization level:	Operator (2)
Issues on:	source system
Related commands:	Repair / repair using

Syntax

Command	Command options	Remote options
compare owner.source_table[.partition]	[ at target_host@o.target_sid ] \| [ for o.source_sid ] \| [ hint “hint” ] \| [ {include \| exclude} "column_list" ] \| [ key ] \| [ log rowdata ] \| [ log split ] \| [ {nolocksource \| nolocktarget} ] \| [ nosqllog ] \| [ not "exception_list" ] \| [ orderby "column_list” ] \| [Override parameter to the compare and repair command] \| [ parallelism degree ] \| [ port port_number ] \| [ quickcheck ] \| [ sourcewhere “clause” ] \| [ threads thread_count ] \| [ targetwhere “clause” ] \| [ to target_owner.target_table[.partition] ] \| [ where “clause” ]	[ on host \| on host:portnumber \| on login/password@host \| on login/password@host:portnumber ]
compare using filename	[key] \| [log rowdata] \| [log split] \| [parallelism degree] \| [port port_number] \| [quickcheck] \| [threads threads_count]	[ on host \| on host:portnumber \| on login/password@host \| on login/password@host:portnumber ]

Command

Command options

Remote options

compare owner.source_table[.partition]

[ at target_host@o.target_sid ] |

[ for o.source_sid ] |

[ hint “hint” ] |

[ {include | exclude} "column_list" ] |

[ key ] |

[ log rowdata ] |

[ log split ] |

[ {nolocksource | nolocktarget} ] |

[ nosqllog ] |

[ not "exception_list" ] |

[ orderby "column_list” ] |

[Override parameter to the compare and repair command] |

[ parallelism degree ] |

[ port port_number ] |

[ quickcheck ] |

[ sourcewhere “clause” ] |

[ threads thread_count ] |

[ targetwhere “clause” ] |

[ to target_owner.target_table[.partition] ] |

[ where “clause” ]

[ on host |

on host:portnumber |

on login/password@host |

on login/password@host:portnumber ]

compare using filename

[key] |

[log rowdata] |

[log split] |

[parallelism degree] |

[port port_number] |

[quickcheck] |

[threads threads_count]

[ on host |

on host:portnumber |

on login/password@host |

on login/password@host:portnumber ]

Syntax description

Required command components

Component	Description
compare owner.source_table[.partition]	The basic command compares all of the source rows with all of the target rows. owner.source_table is the owner and name of the source table. Use double quotes to enforce case-sensitivity or spaces within a name, for example “HR”.emp. Wildcarded table names (but not owner names) are supported. To be compared, tables that satisfy a wildcard in this command must be listed (explicitly or by wildcard) in the active replication configuration. For more information about how SharePlex handles wildcards, see the SharePlex Administration Guide. Example sp_ctrl(sysA)>compare scott.emp sp_ctrl(sysA)>compare scott.emp.west
compare using filename	The basic command compares all of the source rows with all of the target rows in the tables listed in filename. filename is the name of the file that contains the names of the source tables that you want to compare. Example sp_ctrl(sysA)>compare using sales

Component

Description

compare owner.source_table[.partition]

The basic command compares all of the source rows with all of the target rows.

owner.source_table is the owner and name of the source table. Use double quotes to enforce case-sensitivity or spaces within a name, for example “HR”.emp.

Wildcarded table names (but not owner names) are supported. To be compared, tables that satisfy a wildcard in this command must be listed (explicitly or by wildcard) in the active replication configuration. For more information about how SharePlex handles wildcards, see the SharePlex Administration Guide.

Example

sp_ctrl(sysA)>compare scott.emp

sp_ctrl(sysA)>compare scott.emp.west

compare using filename

The basic command compares all of the source rows with all of the target rows in the tables listed in filename.

filename is the name of the file that contains the names of the source tables that you want to compare.

Example

sp_ctrl(sysA)>compare using sales

Optional command components

Component	Description
at target_host@o.target_sid	Valid for compare Compares the source table to only one of its targets. Use when the source table replicates to multiple target systems. target_host is the name of the target system. target_sid is the ORACLE_SID of the target Oracle instance. Example sp_ctrl(SysA)>compare scott.emp at prod@o.prodsid
for o.SID	Valid for compare Specifies the Oracle instance that contains the source table. Use when the same source table is in multiple Oracle instances on a system. SID is the ORACLE_SID of the source instance. It is case-sensitive and must be typed as it appears in the oratab file or V$PARAMETER table. When used, this option must appear after the required command arguments, but it can appear in any order with other options. Example sp_ctrl (SysA)>compare scott.emp for o.oraA
hint "hint"	Valid for compare Includes an Oracle hint in the SELECT statement. The hint is used on the source and target systems. “hint” is a standard Oracle hint no longer than 2000 characters. Enclose the entire hint within double quotes. Omit the leading /+ and trailing / in the hint string. They are added by SharePlex. When used, this option must appear after the required command arguments, but it can appear in any order with other options. Example sp_ctrl (SysA)>compare scott.emp where “file >001005” hint “emp(salary)” When running a comparison from the command line of the operating system, quoted strings must have an extra set of escaped double quotes as in this example: /productdir/bin/sp_ctrl compare scott.emp hint “\“emp(salary)\””
{include \| exclude} "(column_list"	Valid for compare Filters the columns to be compared. Use include to specify columns that you want to compare. No other columns are compared. You must include all of the key columns in an include clause. Use exclude to compare all columns except those specified with exclude. Do not exclude any key columns. (column_list) is the list of columns to include or exclude. Separate each name with a comma. No spaces are permitted in the list unless the name of a column contains spaces. Enclose the column list within double quote marks. List columns in any order. The sort is performed in ascending order. Column names are not case-sensitive. When used, this option must appear after the required arguments of the command, but it can appear in any order with other options. Note: There could still be rows that are out-of-sync in the columns that were not compared. Example sp_ctrl (SysA)>compare scott.emp exclude "color, weight"
key	Valid for compare and compare using Performs a fast comparison of large tables. This command does not compare all of the data values. It compares one of the following: Only the PRIMARY key or non-null UNIQUE key columns. Or... The columns specified with the orderby option. Use this option if the tables have no keys. Important: Even if the keys or the orderby values match, the tables remain out of synchronization if values in other columns do not match. When used, this option must appear after the required command arguments. It can appear in any order with other options. Do not use this option to base a comparison on a SharePlex key definition. For more information about SharePlex key definitions, see the SharePlex Administration Guide. Example sp_ctrl (SysA)>compare scott.emp key sp_ctrl(sysA)>compare using sales key
log rowdata	Valid for compare and compare using Directs the client process to produce a SQL file that logs the actual row data rather than hash values. The file is produced on the target system. Do not use log rowdata if the tables being compared have LONGs, LOBs, and VARRAYs. If the value of any of these data types is very large, SharePlex may not be able to log the actual data. IMPORTANT! The purpose of this SQL file is to provide a view of the data that was compared. Do not use it to repair target tables. The data values that were captured when the command was run may be different from those currently in the database. Use the repair command to repair any out-of-sync rows. Example sp_ctrl(sysA)>compare scott.emp log rowdata sp_ctrl(sysA)>compare using sales log rowdata
log split	Valid for compare and compare using Directs the client process to split its SQL file into three different files based on the operation type: one for INSERTs, one for UPDATEs, and one for DELETEs. Example sp_ctrl(sysA)>compare scott.emp log split sp_ctrl(sysA)>compare using sales log split
{nolocktarget \| nolocksource}	Valid for compare Prevents the comparison process from locking tables during the comparison phase of a run that includes a repair. Normally, SharePlex locks the tables momentarily during a comparison to get a read-consistent view, and then unlocks them immediately, but SharePlex always locks target tables during a repair. SharePlex locks source tables momentarily during a repair to get a read-consistent view.
nosqllog	Suppresses output of the SQL log file. This file contains the SQL that is needed to repair out-of-sync rows. Some reasons not to output this file include: The data contains sensitive information. The SQL log file is written in clear text. By not producing a SQL log file, the sensitive data is not persisted to disk, which may satisfy security requirements for data at rest, such as those required to meet PCI compliance standards. The compared or repaired tables have a very large number of out-of-sync rows. A log file of this size can consume a large amount of disk space.
not “exception_list”	Valid for compare Specifies an exception list of tables not to compare when the table specification includes wildcards. “exception_list” is a list of names of the tables not to compare. Use the owner.tablename format. Separate each name with a comma. No spaces are permitted in the list. Enclose the list within double quote marks. List the tables in any order. When used, this option must appear after the required arguments of the command, but it can appear in any order with other options. Example sp_ctrl(SysA)>compare scott.% not (%temp%)
orderby “column_list”	Valid for compare Specifies columns for the comparison process to use in its ORDERBY clause when it sorts rows to be compared. This option enables comparisons to be performed on tables that have no primary or unique key. “column_list” is the names of the columns to use in the ORDERBY clause. Separate each name with a comma. No spaces are permitted in the list unless the name of a column contains spaces. Enclose the column list within double quote marks. List columns in any order. The sort is performed in ascending order. Column names are not case-sensitive. When used, this option must appear after the required arguments of the command, but it can appear in any order with other options. Example sp_ctrl(SysA)>compare scott.emp where “file >001005” orderby “Last Name,Division” When running a comparison from the command line of the operating system, quoted strings must have an extra set of escaped double quotes: /productdir/bin/sp_ctrl compare scott.emp orderby “\“Last Name,Division\””
override	Valid for compare and compare using Overrides the sanity check and allows the comparison of tables that are out-of-sync beyond the sanity check threshold. The alternative to comparing the out-of-sync tables is to reload the target table by using the copy command (see Copy / append). For more information about sanity check, see how the comparison works. Example: SP_ctrl (sysA) > compare scott.emp override SP_ctrl (sysA) > compare using sales override
port port_number	Valid for compare and compare using Available for backward compatibility if the version of SharePlex is earlier than 8.0 on the source or target system. Specifies a port on the source system for the client process to use for communication with the server process. In earlier versions of SharePlex, the communication is two-way, and a random port number is selected by default for client-to-server communication. This option overrides the random port selection with a specific port number, such as that required by a firewall. Example sp_ctrl(sysA)>compare scott.emp port 1234
quickcheck	Valid for compare and compare using Performs an initial check to determine if any rows are out of synchronization. As soon as one out-of-sync row is detected, the process stops. No further information is returned. No out-of-sync row information is logged to the compare SQL file. Do not use this option with the key option or any of the where options. This option does not support LONG columns. Example sp_ctrl(sysA)>compare scott.emp quickcheck sp_ctrl(sysA)>compare using sales quickcheck
parallelism degree	Valid for compare and compare using Adds a parallel hint to the SELECT statement. For degree, set the degree of parallelism. Example sp_ctrl(sysA)>compare scott.emp parallelism 4 sp_ctrl(sysA)>compare using sales parallelism 4
sourcewhere “clause”	Valid for compare Bases the comparison on one or more columns in the source table when those columns do not exist in the target table. Enclose clause within double quote marks and refer to tables by their fully qualified names, for example scott.emp. Use double quote marks to enforce case sensitivity or spaces within a table name. Dates must be in the format of 'YYYYSMMDDHH24MISS'. To convert a date to that format, use the Oracle TO_DATE function. For example if c1 is a DATE column, the WHERE clause "c1 > '10-SEP-2001'" will not work, but "c1 > to_date('10- SEP-2001', 'DD-MON-YYYY')" will work. When running a comparison from the command line of the operating system, quoted strings must have an extra set of escaped double quotes, like the following example: sp_ctrl> compare scott.emp sourcewhere “\“file >001005\”” When used, this option must appear after the required command arguments, but it can appear in any order with other options. Example #1: sp_ctrl(sysA)>compare scott.emp sourcewhere “file >001005” Example #2: The following example shows how the sourcewhere and where options are combined to get the desired result. Only the source comparison process will use the sourcewhere clause, but both the source and target comparison processes will use the where clause. sp_ctrl(SysA)>compare scott.emp sourcewhere “deptno = 200” where “mgr = ‘SMITH’”
targetwhere "clause"	Valid for compare Bases the comparison on one or more columns in the target table when those columns do not exist in the source table. Enclose the clause within double quote marks and refer to tables by their fully qualified names, for example scott.emp. Use double quote marks to enforce case sensitivity or spaces within a table name. Dates must be in the format of 'YYYYSMMDDHH24MISS'. To convert a date to that format, use the Oracle TO_DATE function. For example if c1 is a DATE column, the WHERE clause "c1 > '10-SEP-2001'" will not work, but "c1 > to_date('10- SEP-2001', 'DD-MON-YYYY')" will work. When running a comparison from the command line of the operating system, quoted strings must have an extra set of escaped double quotes: /productdir/bin/sp_ctrl compare scott.emp targetwhere “\“file >001005\”” When used, this option must appear after the required command arguments, but it can appear in any order with other options. Example #1: sp_ctrl(SysA)> compare scott.emp targetwhere “file >001005” Example #2: The following example shows how the targetwhere and where options are combined to get the desired result. Only the target comparison process will use the targetwhere clause, but both the source and target comparison processes will use the where clause. sp_ctrl(SysA)>compare scott.emp where “deptno = 200” targetwhere “mgr = ‘SMITH’” repair
threads thread_count	Valid for compare and compare using Sets the number of processing threads that are used by the comparison process. Example sp_ctrl(sysA)>compare scott.emp threads 4 sp_ctrl(sysA)>compare using sales threads 4
to target_owner.target_table[.partition]	Valid for compare Compares the source table to only one of its targets. Use when the source table replicates to multiple target systems and the target tables have different names. This option can also be used to specify a target partition. Example (Compares a partition) sp_ctrl(SysA)>compare scott.emp to scott.allemp.east
where “clause”	Valid for compare Include a WHERE clause in the SELECT statement on both the source and target systems. The WHERE clause acts as a filter to compare specific rows. For “clause” specify a standard WHERE clause that does not include subqueries. Enclose the clause within double quote marks and refer to tables by their fully qualified names, for example scott.emp. Use double quote marks to enforce case sensitivity or spaces within a table name. Dates must be in the format of 'YYYYSMMDDHH24MISS'. To convert a date to that format, use the Oracle TO_DATE function. For example if c1 is a DATE column, the WHERE clause "c1 > '10-SEP-2001'" will not work, but "c1 > to_date('10- SEP-2001', 'DD-MON-YYYY')" will work. When used, this option must appear after the required command arguments, but it can appear in any order with other options. Example sp_ctrl (SysA)>compare scott.emp where “region=4”

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

compare status

connection

Basic command	Command options	Remote options
job 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 ]

Component	Description
job status	Shows status of all compare and repair jobs for which SharePlex has history.
job_id	Displays status history for the job with the specified SharePlex-assigned job ID. Example: sp_ctrl(sysA)>job status 2828.2
job_id.table_id	Displays status history for the job with the specified SharePlex-assigned job ID and table. Example: sp_ctrl(sysA)>job status 2828.HR.SRC_TEST3
all	Displays a summary line for every job with history in the database. Example: sp_ctrl(sysA)>job 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)>job 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)>job status detail
status	Displays status history for previous jobs with the specified status. Example: sp_ctrl(sysA)>job status "Error"

Use the connection command to configure SharePlex to connect to a database.

How to use the connection command

Use the connection command to establish connection properties for SharePlex to use when connecting to a database. Use this command only if there is no database setup utility available for the database. To determine whether a setup utility exists for the database, see Database Setup Utilities .

Use the connection command in the following ways:

With the set option to set connection attributes. Issue one connection command per option used. The command does not support the stringing of multiple keyword/value pairs with one command.
With the reset option to clear connection settings.
With the show option to view the current connection settings.

Notes:

The SharePlex-supported Open Target targets are listed in the SharePlex Release Notes.
When a DSN exists for an ODBC database, the dsn, user and password keywords are the only required connection settings. If a DSN does not exist, the user, password, server, driver, and port keywords are all required.
The connect_string keyword supports a user-defined ODBC connection string where preferred or in cases where the supplied ODBC keywords are not sufficient.

Important: Make certain to stop and then restart the Post process after using this command.

Usage

Supported sources:	Oracle
Supported targets:	All
Authorization level:	Operator (2)
Issues on:	source or target system
Related commands:	target

Syntax

Basic command	Keyword=value options	Remote options
connection {o. \| r.}database { set keyword=value \| show [all] \| reset [keyword] }	[user=username] [password=password] [tns_alias=alias] [asm_sid=SID] [asm_user=username] [asm_password=password] [asm_tns_alias=alias] [wallet_location = path] [wallet_automode = Y/N] [dsn=DSN] [server=servername] [driver=path] [port=portnumber] [connect_string=string] [plugin = pluginname] [plugin_version = versionnumber] [plugin_direction = source/target/both] [sp_host_port = Hostname:port] [admin_user = username] [database = SID] [dstype = Datastorename] [version = versionnumber] [hostaddr = IPaddress] [host = servername] [dbname = databasename] [db_host = IP] [db_port = portnumber] [oracle_home = path] [service_name = servicename]	Not available

Basic command

Keyword=value options

Remote options

connection {o. | r.}database

{

set keyword=value |

show [all] |

reset [keyword]

}

[user=username]

[password=password]

[tns_alias=alias]

[asm_sid=SID]

[asm_user=username]

[asm_password=password]

[asm_tns_alias=alias]

[wallet_location = path]

[wallet_automode = Y/N]

[dsn=DSN]

[server=servername]

[driver=path]

[port=portnumber]

[connect_string=string]

[plugin = pluginname]

[plugin_version = versionnumber]

[plugin_direction = source/target/both]

[sp_host_port = Hostname:port]

[admin_user = username]

[database = SID]

[dstype = Datastorename]

[version = versionnumber]

[hostaddr = IPaddress]

[host = servername]

[dbname = databasename]

[db_host = IP]

[db_port = portnumber]

[oracle_home = path]

[service_name = servicename]

Not available

Syntax description

Component	Description
{o. \| r.}database	The database for which you are configuring the connection. Use the following format: Oracle: o.SID where: SID is the ORACLE_SID of the database. Open Target (ODBC): r.database where: database is the name (not the DSN) of the database. Note: The database specification must match the database specification on the datasource line or in the routing map of the configuration file. For example, if the routing map is myhost@r.mydb then a connection command would include the same database specification, like this: connection r.mydb set user=myuser
set keyword=value	Sets a connection property. See Keywords.
show [all]	show displays the current connection settings for the specified database. show all displays connection settings for all local databases. Do not use the database specification with show all, as in this example: connection show all
reset [keyword]	reset clears all of the connection settings for the specified database. reset keyword clears the connection setting only for a specific connection property. See Keywords

Keywords

Keyword	Database type	Description of input value
user=username	All	The name of the database user that SharePlex will use to connect to the database specified with database.
password=password	All	The password for the user specified with username.
tns_alias=alias	Oracle	The TNS alias to which SharePlex will connect. Required if connections to the database are managed with a tnsnames.ora file.
asm_sid=SID	Oracle	The Oracle SID of the ASM instance. Required if Oracle Automatic Storage Management is being used by the database.
asm_user=username	Oracle	The name of the ASM user that SharePlex will use to connect to the ASM instance.
asm_password=password	Oracle	The password of the ASM user.
asm_tns_alias=alias	Oracle	The TNS alias of the ASM instance. Required if connections to the ASM instance are managed with a tnsnames.ora file.
wallet_location = path	Oracle	If set, path to Oracle wallet file
wallet_automode = Y/N	Oracle	Set to Y if setup for SharePlex auto open
dsn=DSN	Open Target (ODBC)	The data source name (DSN) that is associated with the database. If a DSN exists for the database, this keyword plus the user and password keywords are the only required connection settings.
server=servername	Open Target (ODBC)	The name or IP address of the server that hosts the database.
driver=path	Open Target (ODBC)	The full path to the ODBC driver that supports ODBC connectivity to the database.
port=portnumber	Open Target (ODBC)	The port number of the database.
connect_string=string	Open Target (ODBC)	A user-defined connection string. When using your own connection string, make certain it includes all of the required elements to make a successful ODBC connection, but omit the user name and password. Use the connection command with the user and password options to supply user information.
plugin = pluginname		Name of plugin
plugin_version = versionnumber		Plugin version
plugin_direction = source/target/both		The plugin_direction value can be set as source, target or both.
sp_host_port = Hostname:port	Database (Non-Oracle)	SharePlex host: port that ran setup
admin_user = username		Administrator user SQL Server
database = SID		Database name
dstype = Datastorename		Datastore name
version = versionnumber
hostaddr = IPaddress	Database (Non-Oracle)	The server IP address to which ODBC will connect
host = servername	Database (Non-Oracle)	The server name to which ODBC will connect
dbname = databasename	Database (Non-Oracle)	The database name to which ODBC will connect
db_host = IP	Database (Non-Oracle)	The host of the database through DSN
db_port = portnumber	Database (Non-Oracle)	The port used by the database through DSN
oracle_home = path	Oracle	The path to Oracle Home
service_name = servicename	Oracle	Oracle service name

Examples

Oracle examples

connection o.myora12 set user=myuser

connection o.myora12 set password=mypassword

connection o.myora12 set tns_alias=myora12

Open Target examples

DSN exists

connection r.mydb set user=myuser

connection r.mydb set password=mypassword

connection r.mydb set dsn=mydsn

DSN does not exist

connection r.mydb set user=myuser

connection r.mydb set password=mypassword

connection r.mydb set port=1234

connection r.mydb set server=server1

connection r.mydb set driver=/database/ODBC/lib/databasedriver.so

DSN does not exist, use connection string

connection r.mydb set user=myuser

connection r.mydb set password=mypassword

connection r.mydb set connect_string=”driver=/database/ODBC/lib/databasedriver.so;server=server1;port=1234;uid=myuser;pwd=mypassword”

Remove a connection value

connection r.mydb reset port

connection r.mydb reset

View connection settings

connection r.mydb show

connection show all

copy/append

Copy / append

The copy/append command uses the Oracle Export/Import utility to allow users to set up replication and/or (re)synchronize individual tables already in replication.

Users may copy/append individual tables to a single target, specify multiple targets, or all targets in the replication configuration.

Important:

This command invokes Export with CONSISTENT=Y. This requires an adequately sized RBS to avoid an ORA-01555 error.
This command cannot be used in a bi-directional or high availability environment without additional steps. Refer to the Knowledge Base solution 48020.
Before you use this command, review all of this documentation. Improper use could result in lost data and out-of-sync conditions.

The copy command

Use the copy command to synchronize or instantiate a table (or tables) from one host to any other host when you want the target table truncated prior to synchronizing data.

The append command

Use the append command to synchronize or instantiate a table (or tables) from one host to any other host when you do not want the target table truncated prior to synchronizing data.

When to use the copy/append command

Use the copy/append command in the following ways:

To migrate a database.
To sync a table that the Compare/Repair process is unable to repair.
Place a table into replication while instantiating it.

What the copy/append command supports

The copy/append command supports the following:

Objects supported by the copy/append command are those objects supported by Oracle’s Import/Export utility.
Copying from a lower version of Oracle to a higher version of Oracle, or between the same versions of Oracle.
The copy/append of objects in a synchronization routing file that are included as a result of Oracle wildcard specification.
The use of Oracle wildcard specification in command line syntax.

What the copy/append command does not support

The copy/append command does not support:

Copying from a higher version of Oracle to a lower version of Oracle
Copying from a table to a view
UDTs or VARRAYs if the UDT or VARRAY type was not created on the target database using Oracle's Import/Export utility or Oracle's hot backup. This is a limitation in Oracle's import/export facility
Tables in replication that are horizontally or vertically partitioned
Sequences
Tables for which transformation is being used, unless the transformation is being applied on the target, as well
Network configurations in which a pass-through server is used to pass data between the source and target servers
Column mapping
Subset of columns
Specifying a subset of rows within a table
Indexes, constraints, triggers, and grants options
Direct load
Transparent Data Encryption (TDE)
The append command does not support Oracle partitions. (Only the copy command supports Oracle partitions.)
The BINARY DOUBLE and the BINARY FLOAT data types are not supported for the copy/append command.
If a configuration file has different table names on the source and target, and the target contains another table with the same name as the source, using the copy/append command may compromise the consistency of the data already present in the target table. It is important to note that in such cases, the copy/append command will not rename the table on the target. The copy status detail command will display a message in sp_ctrl that "Cannot rename table on target; source name exists".

Important:

The copy/append command supports Oracle wildcards; however, if the set of objects indicated by the wildcard specification includes objects that are not presently in replication, the copy/append command will only act on those objects currently in replication.
If the force option is employed and all the target information is specified, the tables in replication will be copied statically, as is the current behavior.

The launcher process

The launcher process must be running on the target system(s) prior to executing the copy/append command. To start the launcher process the user must execute the following command on each of the target systems:

sp_ctrl(sysB)> start launcher

While the launcher process is running the show launcher command may be used to view process details.

When the user has completed synchronization the launcher may be stopped on the target system(s). To stop the launcher process the user must execute the following command on each of the target systems:

sp_ctrl(sysB)>stop launcher

Overview of copy/append process

The copy/append command initiates a process to synchronize individual tables between individual hosts.

The following is the sequence of events that illustrates simple synchronization process activity:

Log onto the target system and issue the start launcher command from sp_ctrl.
When the copy/append command is issued from a sp_ctrl session the sp_cnc spawns sp_sync_svr on the source system.
The sp_sync_svr connects to the target system and starts the sp_sync_clnt on the target system.
The sp_sync_svr sends the sp_syn_clnt a table list to verify information on the target table. a) If the object is being copied (versus an append) the target table is truncated.
For each object under copy the sp_sync_svr starts up an Oracle export process.
If the object under copy is in replication sp_sync_svr must wait for sp_sync_clnt to indicate that the Post process is ready.
The sp_sync_clnt process starts an Oracle import process for each of the objects that the sp_sync_svr process opened an Oracle export process for.
The sp_sync_svr process transfers data to the sp_sync_clnt process until the copy is complete.
If the object under copy is in replication the Post process re-initiates and resumes it’s posting activities.
Log onto the target system and issue the stop launcher command from sp_ctrl.

Running concurrent copy processes

You can issue multiple copy/append commands at a time. Users should note that while the copy process is in progress the Post process stops all replication.

Identifying synchronization processes

The synchronization processes are not listed in the sp_ctrl console as they are not started by sp_cop process. To identify the process use your operating system to locate the executables.

sp_sync_svr
sp_sync_clnt
sp_sync_lstnr
sp_sync_launcher

Tuning parameters for the synchronization

The following is a list of parameters associated with synchronization using the copy/ append command. These parameters are all live and take effect the next time the command is issued.

SP_OSY_COMPRESSION: This parameter adjusts the data compression from off (0) to full (9). The default integer value is set to six (6).
SP_OSY_EXP_BUFFER_SIZE: This parameter adjusts the amount of data that is buffered before being sent to the target system. The default integer value is 1024 Kilobytes.
SP_OSY_IMP_BUFFER_SIZE: This parameter adjusts the amount of data that is buffered before being applied on the target system. The default integer value is 1024 Kilobytes.
SP_OSY_LOCK_TIMEOUT: This parameter set the number of seconds that the synchronization process will wait to obtain a table lock. The default integer value is 2 seconds.
SP_OSY_PORT: This parameter sets the port number for the synchronization process. The default integer value is set to port number 2501.
SP_OSY_POST_TIMEOUT: This parameter sets the number of seconds that the synchronization process will wait for the Post process to be ready and the synchronization to begin. The default integer value is set to 1800 seconds (30 minutes).
SP_OSY_TCP_TIMEOUT: This parameter sets the number of seconds for the IPC time-out. The default integer value is set to 60 seconds.
SP_OSY_THREADS: This parameter sets the number of synchronization processing threads. This thread parameter only affects a partitioned table. The default integer value is set to 5 threads. It has a maximum value of 32 threads.

Viewing copy status

The best way to view the status of one or more copy/append commands is to use the copy status command or the append status command. The basic command displays the results of all copy/append processes on a system.

The basic command displays the status of all copy/append jobs currently running on the system, and the most recently executed copy/append if it isn’t included in the currently running list. This list can be filtered to show only a specified status.

The best way to view the results of one or more copy/append commands is to use the report command with the copy option in sp_ctrl.

In the example text that follows, the copy/append command was issued against a table not in replication. The copy status that follows displays basic information about the table and the status of the procedure.

sp_ctrl (alvlabl17:8708)> copy sp_iot.SYNC_iot_BASIC to sp_iot.SYNC_iot_BASIC for o.w920a32f at irvqasu15@o.a102u64f force 
copy started; job id 111 
sp_ctrl (alvlabl17:8708)> copy status 111 for o.w920a32f 
Job ID : 111 
Host : alvlabl17.quest.com 
Started : 17-MAR-08 13:59:28 
Job Type : Copy 
Status : Done

ID	Tablename	Total rows	%Comp	Status	Status time	Total time
---	-------------------------	-----------	--------	--------	-----------	----------
1	"SP_IOT"."SYNC_IOT_BASIC1"	3720	100	Done	N/A	0:08
2	"SP_IOT"."SYNC_IOT_BASIC2"	3720	100	Error	N/A	0:08
3	"SP_IOT"."SYNC_IOT_BASIC3"	3720	100	Done	N/A	0:08

To filter this list to only show the tables with a status of error, execute the following command:

sp_ctrl (alvlabl17:8708)> copy status 111 for o.w920a32f “Error”

Using a synchronization routing file

The synchronization routing file is very similar in structure to a configuration file used for activation, in that the file contents have the same formatting requirements. The difference between the two files is that some tables or objects in a configuration file may not be supported by the synchronization process and therefore should not be included in a synchronization routing file. For example, horizontally or vertically partitioned tables are not supported by the synchronization process and should not be included in a synchronization routing file.

Use of Oracle wildacrds in synchronization routing files is supported. The application of Oracle wildcards in a synchronization routing file is the same as in a config file. For information about how SharePlex supports wildcards, see the SharePlex Administration Guide.

Example routing file

Datasource:o.s_sid1
s_user0.sync_multi_trgt	d_user0.sync_multi_trgt	d_host1@o.d_sid1
s_user0.sync_multi_trgt	d_user0.sync_multi_trgt	d_host2@o.d_sid1
s_user0.sync_multi_trgt	d_user0.sync_multi_trgt_bu	d_host1@o.d_sid2

Note:

When using the copy/append command for a group of tables all the tables must be in replication or all the tables must not be in replication. If you have a combination of tables in replication and tables not in replication the user must create a separate synchronization routing file for each group and a separate synchronization process be started with each file.
The copy/append command copies tables in random order and does not take into account referential integrity. Therefore, we recommend that users copy/append only one table at a time, or limit the copy/append to a very small number of tables.

Copying dissimilar source and target tables

The structure of the source and target table(s) should be identical, with the exception that the source tables may contain less columns than the target tables.

The copy/append command should not be used for source tables that contain more columns than their corresponding target tables.

Using copy with partitioned replication

When tables in a configuration use partitioned replication:

The copy/append command will support the copying of individual tables and individual partitioned tables, but not individual partitions.
Data is never copied to an individual partition, even when the target object specified is a partition.
Specifying a partition as a target is not supported.
The synchronization of data, for both, copy and append, is always done on the base table and never the partition.
The copy/append command does not support vertically or horizontally partitioned tables currently in replication.
Using the option, [singlethread], a partitioned table will be copied as one object. The default behavior is to copy each partition of a partitioned table separately so that the copy can be done in parallel.

Controlling the number of processing threads

To take advantage of machines with multiple processors, you can set the number of copy/append processing threads on the source system using the SP_OSY_THREADS parameter. This thread parameter only affects a partitioned table. The default value of this parameter is set to five (5) threads, and the range of valid values is from 1 to 32 threads.

Please note that synchronization process is single threaded when the tables are in replication. Multi-threaded processing only comes into play with tables that are not in replication.

Using Oracle wildcards in command line syntax

Oracle wildcards may be employed when specifying the source_table portion of the copy/append command. Additionally, you may use not notation to exclude specific objects.

You may specify all the tables in a specific schema using the following:

sp_ctrl (irvspxu14:8567)> copy scott.%

In the above example, all objects under the scott schema will be copied, provided the objects are currently in replication.

You can also set exclusions using the following:

sp_ctrl (irvspxu14:8567)> copy scott.% not (%"foo"%)

In the above example, all objects under the scott schema, except objects whose name contain “foo”, will be copied, provided the objects are currently in replication.

For more information about how SharePlex supports wildcards, see the SharePlex Administration Guide.

Other considerations for using copy/append

When using the copy/append command to sync a table that is out-of-sync or when using the copy/append command to place a table into replication while instantiating it the Post process will pause while the table is being copied. The Post process will resume when the copy/append command is complete. This does not apply to a table that is being added to replication through a new route.
The copy/append command can only operate on one table at a time if the tables are currently in replication.
When using the copy/append command for a table not in a known replication route the user must employ the force option. To employ copy/append on objects not in replication the user must employ the force option.
When using the copy/append command for a table in replication, all SharePlex processes (Capture, Read, Export, Import, Post) must be running when you issue the copy/append command, and they must remain running throughout the processes.
Do not use the copy/append command in a cascading replication environment.
Tables with foreign keys should have those keys disabled and then re-enabled.
Table indexes, constraints, triggers, and grants must be (re)enabled after the synchronization completes, as these options are not supported.
When objects are in replication copy/append does not require locks or synchronization between the source table and target table since it is assumed that the source and target table will not undergo changes while the copy/append command is being executed.
When a config file is specified, which contains multiple objects, the source and target schema name can only be different if all of the target objects have the same schema name.
The copy/append command will only be able to copy or append data between two objects on the same database if the objects reside in different schemas.
Activating a new config while the copy/append process is running may cause the synchronization process to fail.

Note: The syntax for the copy and append commands are exactly the same. The examples that appear below utilize the copy command, but the append command can be invoked by simply substituting append for copy.

Usage

Supported sources:	Oracle
Supported targets:	Oracle
Authorization level:	Operator (2)
Issues on:	source system
Related commands:	None

Syntax

Basic command	Command options	Remote options
{copy \| append} source_owner.source_table	[not (list of exceptions)] [totarget_owner.target_table[.partition]] [at target_host[@target_SID] [for o.source_SID] [singlethread] [force] [status]	[ on host \| on host:portnumber \| on login/password@host \| on login/password@host:portnumber ]
{copy \| append} using filename	[for o.source_SID] [force]	[ on host \| on host:portnumber \| on login/password@host \| on login/password@host:portnumber ]

Basic command

Command options

Remote options

{copy | append} source_owner.source_table

[not (list of exceptions)]

[totarget_owner.target_table[.partition]]

[at target_host[@target_SID]

[for o.source_SID]

[singlethread]

[force]

[status]

[ on host |

on host:portnumber |

on login/password@host |

on login/password@host:portnumber ]

{copy | append} using filename

[for o.source_SID]

[force]

[ on host |

on host:portnumber |

on login/password@host |

on login/password@host:portnumber ]

Syntax Description

Component	Description
source_owner.source_table	Specifies an individual source owner and source table name or a group of objects using wildcards. Not valid if using {copy \| append} with the usingfilename syntax. When used without the totarget_owner.target_table option, this syntax assumes that the synchronization target is the same as the replication target. Examples: sp_ctrl(SysA)>copy s_user1.sync_single_tbl sp_ctrl(sysA)>append s_user1.sync_single_tbl
filename	The name of a synchronization routing file containing the tables to be synchronized. File names are case-sensitive. Use this option when using the {copy \| append} using command. Example: sp_ctrl(SysA)>copy using sync_file_2.txt sp_ctrl(sysA)>append using sync_file_2.txt
[not (list of exceptions)]	Specifies an exception list of tables not to be copied/appended when the table specification includes wildcards. The exception_list is a list of table names that should not be copied/appended. Consider three tables: copytest1, copytest2, and copytest3. In the example below, only the data from the copytest3 table is copied to the target. sp_ctrl > copy s_user1.% not (copytest1,copytest2)
totarget_owner.target_table [.partition]	Specifies the target table for synchronization. This option only valid with the source_owner.source_table option. If the table is not in replication, use this option in conjunction with the attarget_host@ [target_sid] option. Examples: sp_ctrl(SysA)>copy s_user1.sync_single_tbl to d_user1.sync_single_tbl sp_ctrl(SysA)>append s_user1.sync_single_tbl to d_user1.sync_single_tbl
at target_host@ [target_SID]	Specifies the location of the target table for synchronization. Use in conjunction with the totarget_owner.target_table [.partition] option. Examples: sp_ctrl(SysA)>copy s_user1.sync_single_tbl to d_user1.sync_single_tbl at d_host1@o.d_sid1 sp_ctrl(SysA)>append s_user1.sync_single_tbl to d_user1.sync_single_tbl at d_host1@o.d_sid1
for o.source_SID	Use to specify the datasource that contains the objects to be synchronized, if there is no active configuration, or if there are more than one active configurations. Examples: sp_ctrl(SysA)>copy s_user1.sync_single_tbl for o.source_sid at target_host@o.target_sid force sp_ctrl(SysA)>append s_user1.sync_single_tbl for o.source_sid at target_host@o.target_sid force
singlethread	Use this option to copy a partitioned table as one object. The default behavior is to copy each partition of a partitioned table separately so that the copy can be done in parallel.
force	Use this option when tables intended for synchronization are not in replication or when the table route does not match an existing or known route. Examples: sp_ctrl(SysA)>copy s_user1.sync_single_tbl at target_host@o.target_sid force sp_ctrl(SysA)>append s_user1.sync_single_tbl at target_host@o.target_sid force
status	Displays status history for previous copy/append commands. sp_ctrl(SysA)>copy status sp_ctrl(SysA)>append status

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

Selecione seu produto:

Para podermos atendê-lo melhor, informe o Propósito de seu chat:

Soluções recomendadas para seu problema

SharePlex 11.2 - Reference Guide

compare / compare using

Supported operations

Not supported

Other conditions

Special use cases

How the comparison works

Manage the SQL log file

Run multiple processes

Compare a subset of a configuration

Control which rows are compared

Identify processes

View status and results in sp_ctrl

View warnings and errors

Cancel a compare job

Manage compare history and logs

Control the batch size

Repair out-of-sync rows

Usage

Syntax

Syntax description

Remote options

compare status

Usage

Syntax

Syntax description

Remote options

connection

How to use the connection command

Usage

Syntax

Syntax description

Keywords

Examples

Oracle examples

Open Target examples

DSN exists

DSN does not exist

DSN does not exist, use connection string

Remove a connection value

View connection settings

copy/append

Copy / append

The copy command

The append command

When to use the copy/append command

What the copy/append command supports

What the copy/append command does not support

The launcher process

Overview of copy/append process

Running concurrent copy processes

Identifying synchronization processes

Tuning parameters for the synchronization

Viewing copy status

Using a synchronization routing file

Example routing file

Copying dissimilar source and target tables

Using copy with partitioned replication

Controlling the number of processing threads

Using Oracle wildcards in command line syntax

Other considerations for using copy/append

Usage

Syntax

Syntax Description

Remote options