Chat now with support
Chat with Support

SharePlex 8.6.6 - Administration Guide

About this Guide Conventions used in this guide Overview of SharePlex Run SharePlex Run multiple instances of SharePlex Execute commands in sp_ctrl SharePlex parameters Prepare an Oracle environment for replication Create a configuration file Configure replication to Open Target targets Configure a replication strategy Configure partitioned replication Configure named queues Configure SharePlex to maintain a change history target Replicate Oracle DDL Set up error handling Transform data Configure SharePlex security features Activate replication in your production environment Monitor SharePlex Prevent and solve replication problems Repair out-of-sync Data Procedures to maintain Oracle high availability Make changes to an active replication environment Apply an Oracle application patch or upgrade Back up Oracle data on the source or target Tune the Capture process Tune the Post process Appendix A: Peer-To-Peer Diagram Appendix B: SharePlex environment variables

Deploy transformation

Transform data > Deploy transformation

Deployment of transformation involves the following steps.

Create transformation routines

Write your transformation routines with dynamic PL/SQL procedural language. Use parameters and record and table structures defined in the public package named sp_cr. This package was installed in the SharePlex schema in the database. The package uses the following parameters.

Procedure interface

Follow this template to create your procedure.

(table_info in outsplex.sp_cr.row_typ, col_values insplex.sp_cr.col_def_tabtyp)

where:

  • splex is the SharePlex schema.
  • sp_cr is the name of the package that contains the PL/SQL record and table structures.
  • row_typ is the name of the PL/SQL record that passes in/out variables (see Package definition).
  • col_def_type is the name of the PL/SQL table that stores column information (see col_def_type table).

Package definition

SharePlex defines PL/SQL record and table structures in a public package named sp_cr in the SharePlex database schema. The package uses the following parameters.

"CREATE or REPLACE PACKAGE %s.sp_cr AS 
        "TYPE row_typ IS RECORD  
        "(src_host VARCHAR2(32), 
        "src_ora_sid VARCHAR2(32), 
        "src_ora_time VARCHAR2(20), 
        "source_rowid VARCHAR2(20), 
        "target_rowid VARCHAR2(20), 
        "statement_type VARCHAR2(6), 
        "source_table VARCHAR2(78), 
        "target_table VARCHAR2(78), \n\t"
        "oracle_err NUMBER, 
        "status NUMBER, 
        "action NUMBER, 
        "reporting NUMBER 
        "); 
IN variables

For each row operation that causes a conflict, SharePlex passes this metadata information to your procedure.

Variable Description
src_host The name of the source system (where the operation occurred). It is case-sensitive and is passed using the same case as on the source system, for example SysA. If there are named post queues in use on the target system, this variable consists of the name of the post queue, for example postq1.
src_ora_sid The ORACLE_SID of the source database. It is case-sensitive and is passed in the same case as in the oratab file, Windows Registry or V$PARAMETER table.
src_ora_time The timestamp of the change record in the source redo log.
source_rowid The row ID of the source row. It is passed as a literal within single quotes, for example ‘123456’.
target_rowid The row ID of the corresponding row in the target database. SharePlex obtains the row ID by querying the target database. It is passed as a literal within single quotes, for example ‘123456’. If the row cannot be found using the PRIMARY key, the value is NULL.
statement_type A letter, either I, U or D, indicating whether the operation is an INSERT, UPDATE or DELETE statement.
source_table The owner and name of the source table, expressed as owner.table. This value is case-sensitive and matches the way the table is named in the database. It is passed within double quotes, for example "scott"."emp."
target_table The owner and name of the target table, expressed as owner.table. This value is case-sensitive and matches the way the table is named in the database. It is passed within double quotes, for example "scott"."emp."
oracle_err

This is different, depending on whether the procedure is being used for conflict resolution or transformation.

Transformation: SharePlex passes a value of 0 for this variable. This variable is only used for conflict resolution.

Conflict resolution: The Oracle error number that caused the conflict.

OUT variables

These variables direct the action of SharePlex based on whether the procedure succeeded or failed).

Variable Description
status

Defines whether or not the procedure succeeded. You must specify a value for this parameter.

  • A value of 0 implies successful execution. It acts differently, depending on whether the procedure is used for conflict resolution or transformation.

    Transformation: Post does not write any SQL. SharePlex does not write any error messages to the Event Log when transformation succeeds. It continues its processing by reading the next replicated operation in the post queue.

  • Conflict resolution: A value of 0 directs SharePlex to proceed with the SQL statement. SharePlex does not write any log entries to the Event Log when conflict resolution succeeds.
  • A value of 1 implies that the procedure was unsuccessful. In this case, the action SharePlex takes depends on what you specified as the action variable.
  • (Transformation only) A value of 7 implies unsuccessful execution and instructs the Post process to stop.
action

Defines the action that you want SharePlex to take. This is different, depending on whether the procedure is used for transformation or conflict resolution.

Transformation: You must specify a value of 0 for this parameter, which directs SharePlex NOT to post the SQL statement. Your transformation routine is responsible for posting the results of the transformation either to the target table or another table. The outcome of this action depends on what you specify for the reporting variable

Conflict resolution: Specifies the action to take as a result of an unsuccessful conflict resolution procedure. You must specify a value for this parameter.

  • A value of 0 directs SharePlex NOT to post the SQL statement. The outcome of this action depends on what you specify for the reporting variable.
  • The value of 1 is reserved for internal SharePlex use. Do not use it.
  • A value of 2 directs SharePlex to try the next conflict resolution procedure that you listed in the conflict resolution file, if one exists.
reporting

Determines how SharePlex reports unsuccessful procedural results. You must specify a value for this parameter.

  • A value of 0 directs SharePlex NOT to report an error or write the failed SQL statement to the SID_errlog.sql log.
  • Values 1 and 2 are reserved for internal SharePlex use. Do not use them.
  • A value of 3 directs SharePlex to write the failed SQL statement to the SID_errlog.sql log and report an error to the Event Log.

col_def_type table

SharePlex creates a col_def_tabtyp PL/SQL table for each replicated operation. This table stores column information. It is different depending on whether the procedure is used for transformation or conflict resolution.

  • Transformation: For each row operation, SharePlex writes column information to col_def_type.
  • Conflict resolution: For each row operation that causes a conflict, SharePlex writes column information to col_def_tabtyp.

All fields are passed by SharePlex to your routine, although not all will have values if SharePlex cannot locate the row.

Following is the datatype that is used to populate the col_def_tabtyp table.

type col_def_typ is record
	(column_name	user_tab_columns.column_name%type
	,datatype	user_tab_columns.datatype%type
	,is_key	boolean
	,is_changed	boolean
	,old_value	varchar2(32764)
	,new_value	varchar2(32764)
	,current_value	varchar2(32764)
	);
type col_def_tabtyp is table of col_def_typ
Description of col_def_tabtyp
Column Description
column_name Tells your procedure the name of the column that was replicated from the source table, for example emp_last_name. This value is not case-sensitive.
datatype Tells your procedure the datatype of the data in the replicated column, for example VARCHAR2. This value is always in capital letters.
is_key Tells your procedure whether or not the column is a key column. If it is a key column, SharePlex passes a value of TRUE. If the column is not part of a key, SharePlex passes a value of FALSE.
is_changed

Tells your procedure whether or not the column value has changed. If it is changed, SharePlex passes a value of TRUE. If the column is not changed, SharePlex passes a value of FALSE.

  • For INSERTs, is_changed is TRUE for non-NULL values, because none of the columns existed in the database. If a NULL value is inserted, is_changed is FALSE.
  • For UPDATEs, is_changed is TRUE for non-key columns. For key columns, is_changed normally is FALSE, but SharePlex will pass a value for a changed key column.

    Conflict resolution only: If a key value also was changed on the target system, SharePlex will not be able to locate the correct row, and conflict resolution could fail.

  • For DELETEs, is_changed is always FALSE, because SharePlex replicates only the key values for a DELETE statement.
old_value

Tells your procedure the old value of the replicated column, before it was changed on the source system. This column is NULL for INSERTs, because the row did not exist in the target database before the INSERT.

Conflict resolution only: This is the pre-image against which SharePlex compared the source and target columns as part of its synchronization check for UPDATEs and DELETEs. If the old value passed by SharePlex does not match the current_value value obtained from the target row, then there is a conflict.

new_value Tells your procedure the new value of the replicated column, as changed on the source system.
current_value Tells your procedure the current value of the column in the target table. If SharePlex cannot locate the target row, the value is NULL.
Example entries in col_def_tabtyp table per operation type

The following tables illustrate the possible outcomes of each type of operation.

INSERT operation
column_name is_changed old_value new_value current_value1 is_key
C1 TRUE NULL bind NULL FALSE
C2 TRUE NULL bind NULL TRUE
C3 FALSE NULL NULL NULL TRUE | FALSE

1 When an INSERT fails, it is because a row with the same PRIMARY key already exists in the target database. SharePlex does not return the current value for INSERTs.

UPDATE operation
column_name is_changed old_value new_value current_value1, 2 is_key
C1 TRUE bind bind NULL | target_value FALSE
C2 FALSE bind NULL NULL | target_value TRUE
C3 TRUE bind bind NULL | target_value TRUE

1 (Conflict resolution) When an UPDATE fails, it is because SharePlex cannot find the row by using the PRIMARY key and the pre-image. If the row cannot be found, SharePlex searches for the row by using only the PRIMARY key. If SharePlex finds the row, it returns the current value for the key column as well as the changed columns. If SharePlex cannot find the row by using just the PRIMARY key, then SharePlex returns a NULL.

2 (Transformation) For an UPDATE, SharePlex cannot locate a row using the PRIMARY key and the pre-images, because the pre-images are different due to transformation. As an alternative, it searches for the row using just the PRIMARY key. If it finds it, SharePlex returns the current value for the key column as well as the changed columns. If it cannot locate the row using just the PRIMARY key, then current_value is NULL

DELETE operation
column_name is_changed old_value new_value current_value1 is_key
C1 FALSE bind NULL NULL TRUE

1 When a DELETE fails, it is because SharePlex could not find the row by using the PRIMARY key. Therefore, SharePlex returns a NULL.

Complete the transformation file

To direct SharePlex to call transformation routines instead of posting SQL operations, use the transformation.SID file, where SID is the ORACLE_SID of the target database. Before executing a SQL operation, Post reads this file to determine if there is a transformation routine that it must call.

Where to find this file

A blank transformation.SID file, where SID is the ORACLE_SID of the target instance, was included in the data sub-directory of the SharePlex variable-data directory when SharePlex was installed. Use the file on the target system.

If this file does not exist, you can create one in ASCII format in an ASCII text editor. It must be named transformation.SID, where SID is the ORACLE_SID of the target instance. Note: the SID is case-sensitive.

Important! There can be only one transformation.SID file per active configuration.

How to make entries in the file

Use the following template to link a procedure to one or more objects and operation types.

owner.object {i | u | d | iud} owner.procedure

where:

  • owner.object is the owner and name of a target object, or a wildcarded entry. (See Syntax rules)
  • i| u | d is the type of operation to be transformed by the specified procedure. You can specify any or all operation types, for example id or iud. Upper or lower case are both valid.
  • owner.procedure is the owner and name of the procedure that will handle the specified object and operation type.
Syntax rules
  • There must be at least one space between the object specification, the operation type specification, and the procedure specification.
  • You can use the LIKE operator and a SQL wildcard (%) to specify multiple objects by using a search string. (See the Example.)
  • You can use an underscore (_) to denote a single-character wildcard. For table names that contain an underscore character (for example emp_sal), SharePlex recognizes the backslash (\) as an escape character to denote the underscore as a literal and not a wildcard, for example: like:scott.%\_corp\_emp. If you are not using the LIKE operator, the backslash escape character is not required if an object name contains an underscore.
  • You a comment line anywhere in the file. Start a comment line with the pound symbol (#).
Example transformation file
scott.sal IUD scott.sal_tr
like:scott.%\_corp\_emp IUD scott.emp_tr1
like:scott% IUD scott.emp_tr2
scott.cust U scott.sal_tr

How to change the file during replication

You can change the transformation file any time during replication to add and remove tables and procedures. After you change the file, stop and re-start the Post process.

Configure SharePlex security features

Configure SharePlex security features

SharePlex provides a number of security features that help protect replicated data on the local system and during transfer across a network. This chapter provides guidelines for the configuration and use of these features.

Contents

Secure connections to SharePlex

Configure SharePlex security features > Secure connections to SharePlex

SharePlex provides host authorization security that verifies whether or not SharePlex processes on specific remote systems are authorized to connect to the local system for service and command requests. To implement host authorization, you create an ASCII text file named auth_hosts in the data sub-directory of the SharePlex variable-data directory and then populate it with the names of systems being granted connection permission.

Requirements

  • If used, the auth_hosts file must contain valid entries. If this file exists but is empty or contains invalid entries, SharePlex sends an error message similar to the following example to the Event Log: unauthorized connection attempt.
  • If an auth_hosts file does not exist on a system, SharePlex accepts all requests from all systems that attempt to connect to sp_cop.
  • The name of the local system must be the first non-commented line of this file, or host authorization will not function.
  • All entries, including comments, must end with a return.

To configure the auth_hosts file

Note: Begin comment lines with a pound character (#).

  1. Run an ASCII text editor such as vi (Unix and Linux), NotePad (Windows), or WordPad (Windows) to open a blank file. If you are using a Unix and Linux text editor, change directories to the data sub-directory of the SharePlex variable-data directory before you run the editor.
  2. On the first non-commented line, enter the full machine name of the local system, for example:Localhost.mycorp.com.
  3. On the next non-commented line, enter one of the following:

    Value Description
    all Grants connection authorization to processes on all remote systems.
    hostname Grants connection authorization to the specified host. Enter the fully qualified machine name, for example remotehost.mycorp.com. Specify as many host names as needed, each on its own line.
  4. Save the file as auth_hosts in the data sub-directory of the SharePlex variable-data directory. If running multiple instances of sp_cop, make certain to save the file to the correct variable-data directory.

Example

Note the name of the local host is on the first non-commented line.

#Comment: first line is local host name.

Localhost.mycorp.com

#Comment: remaining lines are remote hosts.

remotehost.mycorp.com

remotehost2.mycorp.com

remotehost3.mycorp.com

Secure data with SSH

SharePlex uses the SSH® Secure ShellTM utility to provide encryption for network services such as secure remote login and other services over an insecure network.

Requirements

  • Purchase and install the SSH software. SSH is not included with Shareplex.
  • Using SSH with SharePlex requires the use of local port forwarding (also known as tunneling) within the SSH configuration. Port forwarding allows you to establish a secure SSH session and then tunnel TCP connections through it.
  • SharePlex can be configured to work with SSH software between a source system and one target system. If a source replicates to multiple targets, only one of the routes can be configured with SSH.
  • This feature is supported on Unix and Linux.

To set up SharePlex to use SSH

  1. On the source and target systems, choose an available local port to be used as the tunnel port. For peer-to-peer and high availability replication, the port must be the same number on both systems. For other replication strategies, choose a different port on each system.
  2. On the source system, issue the following command from the command prompt. This command connects to the target system to set up the tunnel.

    $ ssh -L source_port:target_host:target_port userid@target_host -N -f

    where:

    • -L specifies that the specified port on the local host (acting as the client) is to be forwarded to the remote host and port.
    • source_port is the port number on the source system.
    • target_host is the name of the target system.
    • target_port is the port on the target system.
    • userid is your Unix and Linux user ID. You will be prompted for the password.
    • -N specifies not to execute a remote command. This is used just to forward a port (protocol version 2 only).
    • -f forces the SSH shell to work in the background just before command execution. If this argument is omitted, the terminal window you are using must be kept open. SSH cannot be started with nohup.

    Refer to your SSH documentation for more information about these commands.

  3. (If using multiple SharePlex instances) On the source system, export the correct variable-data directory for the instance of sp_cop for which you are setting up SSH.

    ksh shell:

    export SP_SYS_VARDIR=/full_path_of_variable-data_directory

    csh shell:

    setenv SP_SYS_VARDIR /full_path_of_variable-data_directory

  4. On the source system, start sp_cop. For more information about starting SharePlex, see Run SharePlex.
  5. On the source system, run sp_ctrl from the bin subdirectory of the product directory.
  6. In sp_ctrl, set the SP_XPT_USE_LOCALHOST parameter in one of the following ways.

    • If there is only one target system, set the parameter with the following syntax:

      sp_ctrl> set param SP_XPT_USE_LOCALHOST 1

    • If there are multiple targets, use the following command to set up a tunnel to the target that will use SSH. Replication to the other target systems will connect directly in the normal fashion.

      sp_ctrl> set param SP_XPT_USE_LOCALHOST to host 1

    where: host is the name of the target system that will use the tunnel.

  7. In sp_ctrl, use the list param command with the modified option to verify the parameter setting. If the setting is correct, you can activate a configuration at this point.

    sp_ctrl> list param modified

  8. If there is an active configuration, stop and then start sp_cop to make the new parameter setting active.

    To stop sp_cop:

    sp_ctrl> shutdown /productdir/bin/sp_cop &

    To start sp_cop:

    $ /productdir/bin/sp_cop &

Related Documents