Copy/Append - Oracle to PostgreSQL

The copy/append command allows users to set up replication and/or (re)synchronize individual tables. It requires an ODBC configuration for the Oracle database.

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

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

What the copy/append command supports

The copy/append command supports the following:

• Table objects supported by the copy/append command.
• The copy/append of objects in a synchronization routing file that are included as a result of wildcard specification.
• The use of wildcard specification in command line syntax.
• Tables in replication that are horizontally or vertically partitioned
• Transparent Data Encryption (TDE)

• Specifying a subset of rows within a table

What the copy/append command does not support

The copy/append command does not support:

• Data types that are not supported by SharePlex replication between the source and target

• Tables using transformation, unless the transformation is also being applied on the target

• Network configurations where a pass-through server is used to transfer data between the source and target servers

• Sequences, indexes, constraints, triggers, and grants options

Data synchronization architecture

This diagram illustrates a multi-threaded architecture for synchronizing data between a source and a target system.

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 an sp_ctrl session, sp_cnc spawns sp_xsync_svr processes on the source system. The number of processes spawned equals the value of the SP_XSY_PROCESSES parameter.

3. Each sp_xsync_svr process handles one table at a time.

4. The sp_xsync_svr connects to the target sp_cop system and starts the sp_xsync_clt on the target system.
5. The sp_xsync_svr sends parameter information and table metadata to sp_xsync_clt.

6. If the object being copied is under replication, sp_xsync_clt must wait until it receives the sync marker. The Post process thread should wait until the table copy is complete.

7. The sp_xsync_svr process transfers data to the sp_xsync_clt process until the copy is complete.

8. If the object being copied is under replication, the Post process thread re-initiates and resumes its posting activities.

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 for the tables being copied/appended.

Identifying synchronization processes

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

• sp_xsync_svr

• sp_xsync_clt

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_XSY_COMPRESSION: This parameter adjusts the data compression from off (0) to full (22). The default integer value is set to three (3).
• SP_XSY_BUFFER_SIZE: This parameter adjusts the amount of data that is buffered before being sent to the target system. The default integer value is 500 MB.
• SP_XSY_POST_TIMEOUT: This parameter sets the number of seconds that the synchronization process will wait for the Poster process to be ready and the synchronization to begin. The default integer value is set to 1800 seconds (30 minutes).
• SP_XSY_READ_BUFFER_SIZE: This parameter adjusts the amount of data that is buffered before being sent to the target system for advanced data types. The default integer value is 1 MB.
• SP_XSY_SVR_THREADS: This parameter sets the number of threads in the sp_xsync_svr process. The default integer value is 2 threads.

• SP_XSY_CLT_THREADS: This parameter sets the number of threads in the sp_xsync_clt process. The default integer value is 2 threads.

• SP_XSY_PROCESSES: This parameter sets the number of sp_xsync_svr processes to be created. The default integer value is 2 processes.

• SP_XSY_LOG_FILESIZE: This parameter controls the maximum log file size. The default integer value is 50 MB.

• SP_XSY_LOG_NUMFILES: This parameter controls the number of log files before rolling over. The default integer value is 3 files.

• SP_XSY_SYNC_DEBUG: This parameter is used to adjust the debug levels for logging. The default value is 0x00000000.

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.

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:

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 wildacrds in synchronization routing files is supported. The application of 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

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

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.
• Specifying a partition as a target but not a source is not supported.
• The copy/append command does not support vertically or horizontally partitioned tables currently in replication.

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_XSY_SVR_THREADS parameter. The default value of this parameter is set to two (2) threads, and the range of valid values is from 1 to 32 threads.

You can also set the number of copy/append processing threads on the target system using the SP_XSY_CLT_THREADS parameter. The default value of this parameter is set to two (2) threads, and the range of valid values is from 1 to 32 threads.

Using wildcards in command line syntax

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.

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 for tables being copied/appended while the table is being copied. The Post process will resume for tables being copied/appended 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 operate on multiple tables based on the value of the SP_XSY_PROCESSES parameter.
• 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.

• When objects are in replication, the copy/append command briefly locks the source table, while the target table is not locked. The source tables can undergo changes while they are being copied or appended; however, it is assumed that the target table will not undergo any changes by any process or application other than SharePlex.

• 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:	PostgreSQL
Authorization level:	Operator (2)
Issues on:	source system
Related commands:	None

Syntax

Basic command	Command options	Remote options
{copy | append} source_schema.source_table.[partition]	[not (list of exceptions)] [totarget_schema.target_table[.partition]] [at target_host[@target_SID] [for o.source_SID] [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_schema.source_table	Specifies an individual source schema 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_schema.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_schema.target_table [.partition]	Specifies the target table for synchronization. This option only valid with the source_schema.source_table option. If the table is not in replication, use this option in conjunction with the attarget_host@ [target_dbid] 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_DBID]	Specifies the location of the target table for synchronization. Use in conjunction with the totarget_schema.target_table [.partition] option. Examples: sp_ctrl(SysA)>copy s_user1.sync_single_tbl to d_user1.sync_single_tbl at d_host1@r.d_sid1 sp_ctrl(SysA)>append s_user1.sync_single_tbl to d_user1.sync_single_tbl at d_host1@r.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
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@r.target_sid force sp_ctrl(SysA)>append s_user1.sync_single_tbl at target_host@r.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