SharePlex 8.6.6 - Administration Guide

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

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:

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:

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:

1. Log onto the target system and issue the start launcher command from sp_ctrl.
2. When the copy/append command is issued from a sp_ctrl session the sp_cnc spawns sp_sync_svr on the source system.
3. The sp_sync_svr connects to the target system and starts the sp_sync_clnt on the target system.
4. 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.
5. For each object under copy the sp_sync_svr starts up an Oracle export process.
6. 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.
7. 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.
8. The sp_sync_svr process transfers data to the sp_sync_clnt process until the copy is complete.
9. If the object under copy is in replication the Post process re-initiates and resumes it’s posting activities.
10. 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 10 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.

Note: For more information on each of the parameters associated with synchronization using copy/append please refer to each individual parameter in the “SharePlex Parameters” section of this guide.

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.

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

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 on using Oracle wildcards in this context, please refer to the “Specifying source tables using wildcards” section of the SharePlex Administrator’s Guide.

Example routing file

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 one (1) to ten (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.

For more information on the SP_OSY_THREADS parameter please refer this parameter in the “SharePlex Parameters” section of this guide.

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:

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:

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.

To gain a better understanding of how to employ Oracle wildcards please refer to the “Specifying source tables using wildcards” section of the SharePlex Administrator’s 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

Syntax

Syntax Description

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.