Chat now with support
Chat with Support

SharePlex 11.3 - Reference Guide

About this guide Conventions used in this guide Revision History SharePlex Commands for Oracle SharePlex Commands for PostgreSQL SharePlex parameters General SharePlex utilities Oracle Cloud Infrastructure SharePlex environment variables

License utilities

SharePlex License Utilities

Description

Use the SharePlex license utilities to view, add, and remove license keys to hosts in the SharePlex replication environment. Each installation of SharePlex requires a valid license key.

There are three types of SharePlex license keys for each supported platforms:

  • Trial license keys

  • Perpetual License keys (Permanent)
  • Term license keys

Following is the list of supported platforms for which licenses are available:

  • Oracle

  • File

  • JMS

  • Kafka

  • SQL Server

  • Postgres

  • MySQL

  • Snowflake

  • Event Hubs

  • All Platforms

SharePlex licensing information can be found in the Quest Software Product Guide. Please contact your account manager if you have questions.

If you do not have a valid license key, you may obtain one from Quest Technical Support or your Quest sales representative for the required platform from the above list of supported platforms. Use the appropriate procedure in this documentation to obtain the necessary information to support your license request.

NOTE: To install a trial version of SharePlex, users need to select the All Platforms option when prompted during installation of SharePlex or while running the splex_add_key utility.

License utilities on Unix and Linux

On Unix and Linux systems, separate utilities are used to:

Add a license key

Use the splex_add_key utility to add a license key to a machine during the installation of SharePlex or afterward to convert from one type of license to another.

You can use the splex_add_key utility as follows:

  • You can use splex_add_key on the primary node of a cluster to install licenses for all secondary nodes in the cluster, because they all share one variable-data directory.
  • You cannot use splex_add_key to add licenses for non-clustered machines from one machine. It must be run on each non-clustered replication system so that the license information is stored in the variable-data directory on each system.

To run splex_add_key:

  1. Log on to the system as the SharePlex Administrator.
  2. Run sp_ctrl on the machine where you want to install a license key.
  3. If SharePlex is running, shut it down.

    sp_ctrl> shutdown

  4. Run splex_add_key from the install sub-directory of the SharePlex product directory.

    $ /proddir/install/splex_add_key

  5. Choose a platform to add/update license key:

    SharePlex License Utility
    1) Oracle
    2) File
    
    3) JMS
    4) Kafka
    5) SQL Server
    6) Postgres
    7) MySQL
    8) Snowflake
    9) Event Hubs
    10) All Platforms
    q) Quit License Utility
    Enter option:

    NOTE: To install a trial version of SharePlex, users need to select the All Platforms option.

  6. Enter the appropriate number from the above list to choose the platform.

  7. Enter the key manually as received from Quest. Press Enter when finished entering the key.

  8. Enter q to exit the utility.
  9. Start SharePlex when you are ready for replication to resume.

View a license key

Use the splex_get_key utility to view the SharePlex license key related details. Run this utility from the install sub-directory of the SharePlex product directory.

$ /proddir/install/splex_get_key

The information is similar to the following example:

$ /splex/proddir/install/splex_get_key 
Platform           = All
Product Name       = SharePlex
Product Version    = 11
License Number     = 123-456-789
License Key Type   = Trial
License Expiry     = Midnight of Jan 01, 2050
License Key        = lxxjLny9CqMCqdPZKZGRXIjnz7vpbTPQANliJi7PXJ7+Q8=

Remove a license key

Use the splex_remove_key utility to remove already installed SharePlex license key for a particular platform. If no license key is installed on the machine, it will display the "No license installed" message.

To run splex_remove_key:

  1. Log on to the system as the SharePlex Administrator.
  2. Run splex_remove_key from the install sub-directory of the SharePlex product directory to remove a license key. Remove key will display a list of previously added licence keys.

    $ /proddir/install/splex_remove_key

  3. Choose the appropriate platform to remove a license key:

    SharePlex License Utility

    1) Oracle

    2) Postgres

    q) Quit License Utility

    Enter option: 1

A successful removal of the license terminates with a message similar to the following:

The SharePlex for Oracle license has been successfully removed.

ots

Description

Use the OTS utility (ODBC Test Suite) to test the ability of SharePlex to connect to an Open Target target database through a selected ODBC driver, and then post test data to different column types.

Notes:
  • The OTS utility uses a generic ODBC connection and data type handling method to provide a preliminary test of ODBC databases that are not yet SharePlex Certified (see How a database is considered for support by SharePlex ). The OTS utility is not needed for databases that are certified. For a list of certified databases, see the SharePlexRelease Notes.
  • This utility should not be considered as definitive proof of the suitability of an ODBC datastore as a SharePlex target. Comprehensive testing of actual data in a replica of the proposed production environment must always be part of the certification process.

Supported databases

Open Target databases being considered for use with SharePlex.

Requirements

  1. Install the ODBC driver that you want to test.

  2. (Linux) Set the following environment variables:

    • Set LD_LIBRARY_PATH to both of the following places:

      • SharePlex lib directory, which contains the ODBC libraries.
      • The location of the ODBC driver.
    • If you configured a data source name (DSN) for the database, set ODBCINI and ODBCSYSINI to point to the directory where OTS is installed. If a DSN does not exist and you will connect with a connection string, these variables do not have to be set. (See How to run OTS.)
  3. OTS creates a table to test INSERT, UPDATE, and DELETE operations. The table is created and owned by the user that connects to the database (see How to run OTS). This user must have sufficient permissions to create and own this table.

How to install a standalone OTS

OTS is available in the SharePlex build as well as a standalone version.

To install the standalone version:

Linux: Download the OTS package SPOodbc_test_suite-version-Beta-db_version-rh-40-amd64-m64.tpm. You are prompted for an installation directory.

How to run OTS

Perform the follwing steps to run OTS:

  1. Run OTS. If you are running it from within an installation of SharePlex or the standalone OTS package on Linux, run it from the util subdirectory.

  2. Type one of the following:
    • A data source name (DSN) for the target database. The DSN must already exist. You are then prompted for the name and password.

    • A connection string. The string must have all the correct parameters to establish a connection to the target database.
    OTS requires connection information for the tested database.
    Enter data source name (DSN) or connection string: mydsn
    

How it works

The OTS utility performs the following tests:

  1. Connect to the Open Target target with a name and password or a connection string provided by the person that is running the utility.
  2. Query the target database for a list of supported operations and data types, including allowed storage values and whether a column is nullable.
  3. Issue a full set of SharePlex-supported DML and DDL operations for each data type.
  4. Summarize the results on screen (see How to interpret the OTS results).
  5. Create a report, dump files, and logs to support debugging and support cases.

    Note: The report file is only generated when OTS can connect to the database. If the connection fails, the file is empty.

How to interpret the OTS results

The OTS screen summary provides the following:

  • A summary of the connection information that was used
  • A summary list of data types that were tested
  • Details of the test

The detail results are organized into tables, one per data type category, for example character data types or integer data types, as shown in the example.

Figure 1: Example detail table for character data types

Test: Character data types
--------------------------------------------------------------------------------
Name     Size Nullable Insert Insert Update Delete Add Drop Trunc Pass/Fail
                       Null                        Col Col
--------------------------------------------------------------------------------
char     4096 Y        P      P      P      P      P   P    P     P
nchar    4096 Y        P      P      P      P      P   P    P     P
varchar  4096 Y        P      P      P      P      P   P    P     P
sysname    30 Y        P      P      P      P      P   P    P     P
nvarchar 4096 Y        P      P      P      P      P   P    P     P

The first three columns of each table (Name, Size, Nullable) are taken from a query to the ODBC driver. If the data type is allowed to contain NULL, the Nullable column shows a Y (yes). Otherwise, it shows an N (no).

Following the query columns are columns that represent the tests that were performed. There is a row for each data type that was tested.

The SharePlex-supported operations that OTS tests are as follows:

  • INSERT with NULL value
  • INSERT
  • UPDATE
  • DELETE
  • ALTER TABLE to ADD COLUMN
  • ALTER TABLE to DROP COLUMN
  • TRUNCATE TABLE

The results also show the maximum and minimum (where applicable) storage values that were used for CHAR and VARCHAR data types in each test:

  • For a data type that has only a maximum value (VARCHAR and CHAR) OTS will insert a data length that is greater than the maximum size to determine whether the ODBC database driver returns an error or automatically truncates the data with no error.
  • For a data type that has both minimum and maximum values (numerical data types) a series of operation tests are run for each value. Numbers less than the minimum value and greater than the maximum value are tested to see if the ODBC database driver returns an overflow error or automatically rounds off and truncates the value.

For each test of a data type and operation, a P (pass) or F (fail) result is given, indicating whether or not SharePlex was able to perform that specific DML or DDL operation with that specific data type.

The final column, Pass/Fail, shows at a glance whether the test passed or failed as a whole for a specific data type. An F for any given operation type across the row triggers an F in the Pass/Fail column, meaning an overall failure of testing for that data type.

How to use the results

The OTS utility is an initial screening tool for Open Target databases that are not yet Quest SharePlex Certified. Its purpose is to determine whether the types of operations that you want to replicate to a given target, with a given set of metadata, are likely to succeed, without requiring the installation or setup of SharePlex itself. For example, you could assume that SharePlex probably can post to a target if the outcome of the test is the following:

  • All of the DML tests except one passed, but the failed data type is not defined in your data.
  • All of the DDL failed, but you do not need to replicate DDL.

The OTS utility is only a first step. It is not a substitute for testing a full installation of SharePlex with your actual data, using actual operations that your source Oracle applications generate, and posting those replicated operations to your actual target tables in a test environment. Additionally, a successful test with OTS does not necessarily mean the database will be fully supported by SharePlex. See How to run OTS

How a database is considered for support by SharePlex

To determine whether SharePlex will support a given Open Target database, the following are considered:

  1. Can SharePlex, through OTS, run a full set of transactions through a given ODBC driver for the database?
  2. Does SharePlex Product Management approve the technical and market feasibility of supporting the database?

  3. Have the SharePlex QA labs verified in testing that SharePlex can support the database?

If the answer to questions 1 and 2 are yes, then the database is ODBC-approved. This means the database is then eligible for testing in the QA labs to answer question 3.

It is not realistic to test every possible edition, version, and platform of a given database. Based on market and technical factors, Product Management will determine which variants of a database will be tested. If those tests are successful, Product Management can then consider assigning the database a designation of SharePlex Certified.

provision

Description

Use the provision utility to change a host name or IP address in the SharePlex configuration.

The SharePlex processes rely on the host names or IP addresses of the source and target machines to route data properly. The provision utility enables you to change host names or IP addresses within an active SharePlex instance, without reactivating a new configuration.

Note: The provision utility does not change anything in the database. It only affects SharePlex internal objects.

Supported databases

All databases supported by SharePlex on all supported platforms

Guidelines for using provision

  • If running SharePlex on an AIX machine, set EXTSHM before running provision.

    export EXTSHM=ON

  • Run provision on all of the machines in the SharePlex configuration. Each machine can reference the IP addresses of all the other machines.

Run provision

  1. Stop sp_cop. If sp_cop is running, provision will fail.

Note: provision prevents sp_cop from being started while it is running.

  1. Using the command line of the operating system, run provision from the SharePlex util sub-directory of productdir with the following syntax:

    provision -f old_name[:old_ipaddress] -t new_name[:new_ipaddress] [-p port] [-n]

    provision -h <new hostid> [-p <port>] -n
    provision -i [-p <port>]
    Argument Input
    -f old_hostname[:old_ipaddress]
    • -f is required and represents "from."
    • old_hostname is the old (current) host name.
    • old_IPaddress is the old IP address. Use if the IP address cannot be obtained from the network.
    -t new_hostname[:new_ipaddress]
    • -t is required and represents "to."
    • new_hostname is the new host name.
    • new_IPaddress is the new IP address. Use if the IP address cannot be obtained from the network.
    -p port

    For Windows systems, specifies the port of the SharePlex instance for which provision is being run.

    (You can run the "-p"port provision only on the Windows system.)

    -n

    Runs provision without actually making any changes. Generates a report on the changes that provision will make.

    Important! The best practice is to run provision with -n first, to make certain you agree with the potential changes, then run it without -n to make the changes.

    -hnew_hostID
    • -h is required and represents changing host ID or replacing host ID
    • new_hostid is the new IP address
    -i -i is required and represents host information

Example:

provision -h newid -n
provision -i
provision -f oldname -t newname -n
  1. View the event log to view every change that was made. If the provision run fails or you do not agree with the changes that were made, you can undo them by running the undo_provision script. See Undo changes made by provision .

Undo changes made by provision

The provision utility creates an undo_provision script that can be used to restore the host names and IP addresses to their previous state. Run the undo_provision script from the util subdirectory of the SharePlex product directory. There are no input arguments to this script.

Known issues

The following may occur but do not affect the integrity of the replication environment:

  • The provision utility does not change the active configuration file. This means that the configuration file no longer represents the current state of replication after provision is run. If you need to run the compare config command, or if you decide to reactivate the configuration, update the host name or IP address in the configuration file first.
  • If an Export or Import error occurred when SharePlex connected to a machine before the name or address was changed, the error status persists and cannot be cleared.
  • If the new or changed machine is a source machine, provision generates new routing information, but the Read process may still have the old routing in its cache. When you start sp_cop, Read might generate a warning that the stored IP address does not match the one for the machine. You can ignore this error.
  • After provision is run for a source host, it might not update the "hostname" column in SHAREPLEX_ACTID table with the new host name details. If that column is not correctly updated, you must update the SHAREPLEX_ACTID table manually to specify the new host name. This is only required if the name change affected a source machine.

qview

Description

Through the qview utility, you can view queue names and remove old queue files. The qview tools described here do not deactivate the configuration.

IMPORTANT!Do not use qview for the first time without the assistance of Quest Technical Support. If this utility is not used properly, it can damage the replication environment and require resynchronization and reactivation.

Supported databases

All SharePlex-supported databases on all supported platforms

Run qview

Log on to the system as a SharePlex Administrator, and use the command line of the operating system to run qview from the bin sub-directory of the SharePlex product directory. The utility is an interactive command session.

Overview of qview commands

The qview utility provides the following commands:

Command Description
list Lists all queues for all active configurations on a system.
trim Clean up obsolete subqueue files.
fullrb Create a full rollback message.
otrans Scans for a specified number of messages in the Capture queue.

List queues

Use the list command to list all queues for all active configurations on a system.

Description

The qview list command lists each queue, the replication process that writes to it, and the replication process that reads it. For example, for the capture queue, it lists the Capture process and the Read process. The queues are designated as follows:

  • A capture queue is designated with a +C.
  • An export queue is designated with a +X.
  • A post queue is designated with a +P.
Example output:

In this example, the writer to the capture queue o.ora11+C is the Capture process, as indicated by the sp_ocap in its name string. The reader is the Read process, as indicated by the sp_ord in its name string. The same naming logic applies to the other queues shown in the output (export queue expdsg+X and post queue expdsg+P).

The following queues exist:
o.ora11+C
	WRITER +PA+o.ora11+sp_ocap+o.ora11
	READER +PR+o.ora11+sp_ordr+o.ora11
elliot+X
	WRITER +PR+o.ora11+sp_ordr+o.ora11
	READER +PX+elliot+sp_xport+0x0a01014e (11.1.1.78)
elliot+P+o.ora11-o.ora11
subqueues range from 2 to 6
	WRITER +PI+elliot+sp_mport+0x0a01014e (11.1.1.78)
	READER +PP+elliot+sp_opst_mt+o.ora11-o.ora11

Syntax

list

Trim obsolete subqueues

Use the trim command to clean up obsolete subqueue files on the source system.

Description

The SharePlex post queue actually consists of a number of subqueues, each approximately corresponding to a user session on the source system. The Post process uses the subqueues to establish Oracle sessions for the target instance. The number of subqueues that exist at a given time on a target system reflects the peak activity on the source system since replication started.

SharePlex routinely writes replicated data from the subqueues to associated datafiles on disk as part of its checkpoint recovery system. Each subqueue can have one or more datafiles associated with it, each with a default size of 8 MB. If the entire 8 MB file size is not consumed, a datafile remains on the system even though the data was posted and read/released. Consequently, the higher the activity level on the source system, the more datafiles on disk. The size in megabytes (MB) for the post queue in a qstatus display is the actual disk space that the datafiles occupy.

For example, suppose there were 100 concurrent sessions on the source system, creating 100 subqueues in the post queue on the target system. And, suppose the datafiles were only partially full when the activity level dropped—half full, for example, or 4 MB of 8 MB used—and thus were not deleted. The post queue on that system would consist of 100 datafiles at 4 MB each, totalling 400 MB of disk space.

Using the trim command in qview, you can routinely eliminate obsolete subqueue files that were read-released, while preserving the ones containing data not yet committed to the target database. The trim command does not eliminate queue files for subqueues 0 or 1, because those are the most heavily used subqueues.

How to run this command

Run this command on the target system only.

Stop Import and Post before running qview to issue this command. You can leave sp_cop running.

Note: If one or both of those processes is not stopped, qview returns this error message: que_INUSE: Que is already open.

You can only trim one queue at a time. If there are more than one post queue, you are prompted to select the one you want the command to affect:

Queue zac+P+o.ora920-o.ora920 (y/n) <n>? n
Queue elliot+P+o.ora920-o.ora920 (y/n) <n>? y

Note: If you do not select a queue, qview returns this error message: que_NOEXIST: Queue does not exist.

Syntax

trim

Execute a full rollback

Use the otrans and fullrb commands to create a full rollback message.

Description

Use the otrans command to scan a specified number of messages in the Capture queue, starting at the read release point. The qview utility then prints the transaction id, the number of operations (records), the DML type operation (if there is only one) and the object id modified (if there is only one).

Use the transaction id obtained from otrans to execute fullrb. The qview utility opens the Capture queue, writes an out-of-band full rollback message to the Capture queue, and then writes a commit.

How to run this command

Perform the follwing steps to to run Qview:

  1. Stop Capture.
  2. Run qview.
  3. Issue the otrans command.

    vqiew> otrans 500000

    The output is similar to the following:

    Full rollback 8(7).752562-3(139) --- 99999 Update operations on object id 466857
    Open transaction 8(23).752700-2(14162) --- 2001 Update operations (1000 backward operations) 
    on object id 466857
  4. Issue the fullrb command using the transaction ID from the otrans output.

    vqiew> fullrb 8(7).752562-3(139)

    The output is similar to the following:

    Current queue o.ora920+C        user +PA+o.ora920+sp_ocap+o.ora920
    Full rollback record written to capture queue at 378744, id 1102
    odr_magic        0x4f445235
    odr_op           ODR_FULL_ROLLBACK (50)
    odr_trans        8(7).752562-3(139)
    odr_time         01/01/88 00:00:00 (0)
  5. Start Capture.

    sp_ctrl> start capture

Syntax

otrans number

where: number is the number of messages to scan in the queue.

fullrb transaction_ID

where: transaction_ID is the transaction ID that was returned from otrans.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating