Chat now with support
Chat with Support

SharePlex 8.6.6 - Reference Guide

About this guide Conventions used in this guide SharePlex commands SharePlex parameters SharePlex utilities Appendix B: SharePlex environment variables

td_cleansp

SharePlex utilities > td_cleansp

Overview

Use the td_cleansp utility to remove the current replication state on a system where SharePlex is replicating to a Teradata target.

Caution:

  • The effects of td_cleansp are not reversible. To stop replication without restoring it to an initial state, you might be able to use the abort config or purge config command.
  • td_cleansp must be run on all Teradata target systems in the replication configuration.
  • To fully remove the replication environment, run the ora_cleansp utility on all Oracle source systems in the replication configuration. To verify if and when td_cleansp or ora_cleansp was run on a system, view the SharePlex event log on that system.

td_cleansp does the following on the target system:

  • Truncates the SHAREPLEX_OPEN_TRANS internal table in the SharePlex schema and any other SharePlex-installed tables as applicable.
  • Removes the following from the variable-data directory: the queue files, the process log files, the contents of the statusdb (but not the file), the contents of the dump and state directories, and all but one entry in the event log (the status entry for td_cleansp)

td_cleansp preserves the following:

  • The SharePlex database, account, and password, and only cleans the data in the SharePlex tables.
  • User-created files such as the paramdb and the target configuration settings

Supported databases

Teradata on supported platforms

Run td_cleansp

  1. Stop all SharePlex processes on the Teradata system.
  2. Shut down sp_cop on that system.
  3. Run the td_cleansp utility from the bin sub-directory of the SharePlex product directory with the following syntax:

    td_cleansp [-p port_number] database_name/user_name/password

    where:

    • port_number is the port number of the SharePlex instance that you want to clean up.
    • database_name is the name of the Teradata database.
    • user_name is the name of the SharePlex user account.
    • password is the password of that user account.

    Example: td_cleansp -p 8347 mytera/splex/splex

  4. Enter Y to confirm the cleanup.

    Are you sure you want to perform the clean for '/home/splex/var' and port 8347? [N] : y

    Clean port 8347

    Cleaning /home/splex/var subdirectories

    shareplex_open_trans table truncated

HANA Setup (hana_setup)

SharePlex utilities > HANA Setup (hana_setup)

Overview

Run the HANA Setup program (hana_setup) on a target HANA system to establish a user account, schema, and tables for use by SharePlex.

Supported databases

HANA on supported platforms

Guidelines for using HANA Setup

  • Run HANA Setup on all target HANA instances in the SharePlex replication configuration.
  • Within a server cluster, run HANA Setup on the node that has the mount point to the shared disk that contains the SharePlex variable-data directory.
  • For consolidated replication, run HANA Setup for each variable-data directory.
  • The only supported connection method to HANA is by connect string. Connection through a DSN is not yet supported.

  • Make certain that you assign the required permissions that are shown in this setup process.

Required privileges to run HANA Setup

HANA Setup must be run as a HANA Administrator in order to grant SharePlex the required privileges to operate on the database and to create the SharePlex database account.

Run HANA Setup

  1. Shut down any running SharePlex processes and sp_cop on the target system.
  2. Run the hana_setup utility from the bin subdirectory of the SharePlex product directory.

    Important! If you installed the SharePlex instance on any port other than the default of 2100, use the -p option to specify the port number. For example, in the following command the port number is 9400.

    $ /users/splex/bin> hana_setup -p9400

Table 1: HANA Setup Prompts and Responses

Prompt Response

Enter the connection string [] :

Enter a connection string that connects to the HANA database system. The required components of a connection string for SharePlex to connect to HANA are:

  • SERVERNODE: The name of the target HANA server, then a colon, then the HANA port number.
  • DRIVER: The path to the HANA ODBC driver.
  • CHAR_AS_UTF8: This must be passed as CHAR_AS_UTF8=1.

Example:

SERVERNODE=server1.dept.abc.corp:30015;DRIVER=/usr/sap/hdbclient/libodbcHDB.so;CHAR_AS_UTF8=1

You do not have to supply a user, password, and default database, because hana_setup prompts for those.

Enter the HANA Administrator name :

Enter the name of the HANA Administrator. This user will perform the work on the SharePlex account.

Enter the password for the Administrator account :

Enter the password of the Administrator.

Enter the name of the database :

Enter the name of the database that you want to contain the tables and other objects for use by SharePlex. You can enter the name of a new or existing database.

Database name database does not exist. Would you like to create it? [y] :

If this prompt is displayed, the specified database does not exist. Press Enter to have hana_setup create it for you.

Would you like to create a new SharePlex user [y]:

Press Enter to accept the default to create a new SharePlex database user account and schema of the same name in the specified database, or enter n to use an existing SharePlex account.

Enter the name of the new SharePlex user:

Enter the name of the existing SharePlex user:

One of these prompts is displayed depending on whether you elected to create a new user or use an existing user. Enter the name of the SharePlex user.

 

Enter the password of the SharePlex user :

Enter the password of the SharePlex user account.

Re-enter the password for the SharePlex user :

This prompt is only shown if you created a new user. Enter the SharePlex password again.

A successful setup terminates with a message similar to the following:

Completed SharePlex for HANA database configuration

SharePlex User name: splex

Database name: ndb5

Target specification in SharePlex configuration: r.ndb5

Grant the required privileges to SharePlex

Before you start SharePlex replication to a HANA target, grant the following privileges to the SharePlex database user in that target (where SP_USER is the name of the SharePlex database user):

  • GRANT USER ADMIN TO sp_user;
  • GRANT TABLE ADMIN TO sp_user;
  • GRANT CATALOG READ TO sp_user;
  • GRANT DATA ADMIN TO sp_user WITH ADMIN OPTION;
  • GRANT ROLE ADMIN TO sp_user WITH ADMIN OPTION;

Additionally, log in as the owner of each schema that contains objects that you want to replicate, then make the following grants on the schema:

  • GRANT CREATE ANY ON SCHEMA schema_name TO sp_user;
  • GRANT DEBUG ON SCHEMA schema_name TO sp_user;
  • GRANT DELETE, DROP, EXECUTE, INDEX, INSERT, SELECT, UPDATE ON SCHEMA schema_name TO sp_user;

Oracle Setup (ora_setup)

SharePlex utilities > Oracle Setup (ora_setup)

Overview

Use the Oracle Setup utility (ora_setup) to establish SharePlex as an Oracle user and create the required SharePlex database objects. This setup utility creates the following:

  • A SharePlex account
  • Tables and indexes for use by SharePlex and owned by the SharePlex account
  • Default connection for the SharePlex user

It is recommended that you review all of the content in this topic before running the setup utility.

Supported databases

Oracle on supported platforms

When to run Oracle Setup

Whether or not to run Oracle Setup at the time of SharePlex installation depends on whether this is a source, intermediary, or target system and on how you intend to synchronize the data. To view the initial synchronization procedures, see the SharePlex Administration Guide.

System Type When to run Oracle Setup

Source system

During installation of SharePlex

Intermediary system An intermediary system is used in a cascading configuration, where SharePlex replicates data to a remote system (or systems) and then sends that data from the intermediary system to the final target. If you intend to configure SharePlex to post data to a database on an intermediary system, and you intend to use a hot backup to establish the data on that system and the target, do not run Oracle Setup on the intermediary or target systems. You will run it when you perform the initial synchronization procedure.

Target system

Depends on the method that you will use to synchronize the source and target data when you are ready to activate replication:

  • If you intend to use transportable tablespaces or a cold copy (such as export/import, store/restore from tape, FTP), run Oracle Setup during SharePlex installation.
  • If you intend to use a hot backup to establish the target data, do not run Oracle Setup. You will run it when you perform the initial synchronization procedure.

    Note: If you run Oracle Setup before the backup and recovery, the setup gets overwritten, and you will need to re-run it again after the backup and recovery.

Supported Oracle Connections

Oracle Setup can configure any of the following connections for the SharePlex user to use when connecting to the database.

Database type Connection
Database with or without ASM Bequeath

Database with or without ASM

TNS alias

  • (A TNS login is specified for both the database and the ASM instance.)
  • PDB with ASM

    TNS alias for the PDB and either TNS or bequeath for the ASM instance.

    Requirements for using Oracle Setup

    • Install the database client on the system where you are running Oracle Setup. Consult the Oracle documentation for the appropriate client version to use with the database.
    • Run Oracle Setup for all source and target Oracle instances in the SharePlex replication configuration.
    • Within a cluster, run Oracle Setup on all nodes of the cluster. This ensures that the SharePlex settings in the Windows Registry include the correct ORACLE_SID.
    • For a consolidated replication topography, or other topology with multiple variable-data directories, run Oracle Setup for each variable-data directory.
    • SharePlex supports local BEQUEATH connections or remote connections using a TNS alias. Be prepared to supply Oracle Setup the needed connection values for whichever connection you want to use. Review Run Oracle Setup to familiarize yourself with the prompts before you actually run Oracle Setup.

    • If the Oracle database is a multitenant container database, run Oracle Setup for each pluggable database involved in a replication scenario. A SharePlex user and schema objects must exist in each PDB.
    • If you run Oracle Setup when there is an active configuration, the DDL that Oracle Setup performs to install or update the SharePlex internal tables will be replicated to the target. To work around this issue, set the SP_OCT_REPLICATE_ALL_DDL parameter to 0 before running Oracle Setup, then return it to its previous setting after Oracle Setup is complete. This parameter takes effect immediately.

    SharePlex schema storage requirements

    Oracle Setup installs some database objects for use by SharePlex. The storage requirements for these objects should be satisfied before running Oracle Setup. See the following table.

    Storage Description
    SharePlex objects tablespace

    The setup utility installs some tables into a tablespace of your choosing. All but the SHAREPLEX_LOBMAP table use the default storage settings of the tablespace.

    The SHAREPLEX_LOBMAP table contains entries for LOBs stored out-of-row. It is created with a 1 MB INITIAL extent, 1 MB NEXT extent, and PCTINCREASE of 10. The MAXEXTENTS is 120, allowing the table to grow to 120 MB.

    The default storage usually is sufficient for SHAREPLEX_LOBMAP, permitting more than 4 million LOB entries. If the Oracle tables to be replicated have numerous LOB columns that are inserted or updated frequently, consider increasing the size the SharePlex tablespace accordingly. Take into account that this table shares the tablespace with other SharePlex tables.

    If the database uses the cost-based optimizer (CBO) and the tables that SharePlex processes include numerous LOBs, incorporate the SHAREPLEX_LOBMAP table into the analysis schedule.

    Note: A new installation of SharePlex does not change storage parameters from a previous installation.

    SharePlex temporary tablespace

    The setup utility prompts for a temporary tablespace for SharePlex to use for sorts and other operations, including sorts performed by the compare commands. The default temporary tablespace is the one where the SharePlex objects are installed. If you plan to use the compare commands to compare large tables, especially those without a primary or unique key, specify a dedicated temporary tablespace for SharePlex.

    SharePlex index tablespace

    The setup utility prompts for a tablespace to store the indexes for the SharePlex tables. The default index tablespace is the one where the SharePlex objects are installed. To minimize I/O contention, specify a different index tablespace from the one where the tables are installed.

    Note: If indexes from a previous version of SharePlex are installed in the SharePlex objects tablespace, you can move them to a different tablespace and then specify that tablespace when you run the setup utility.

    Privileges granted to the SharePlex database user

  • Oracle Setup grants to the SharePlex database user the following:
    • DBA role and unlimited resource privileges, tablespace privileges, and read privileges to the redo logs.
    • Default Oracle profile. By default this profile has the unlimited resource privileges originally assigned by Oracle. If the default has been changed, assign SharePlex a DBA profile with unlimited resource privileges.
    • The following grants:
      • To access the data dictionary (outside the DBA roles) if O7_DICTIONARY_ACCESSIBILITY is set to FALSE:

        grant select any dictionary to SharePlexUser;

      • To replicate DDL:

        grant select any table to SharePlexUser with admin option;

        grant create any view to SharePlexUser with admin option;

    Required privileges to run Oracle Setup

    The user who runs Oracle Setup must have the following privileges:

    Non-multitenant (standard) database

    The user who runs the setup utility must have DBA privileges, but if support for TDE is required, then this user must have SYSDBA privileges.

    Multitenant database

    The user who runs the setup utility should have SYSDBA privileges (recommended), but at minimum the user should be a DBA user with privileges for sys.users$ and sys.enc$. The minimum following grants are required for the SharePlex user:

    create user c##sp_admin identified by sp_admin;

    grant dba to c##sp_admin container=ALL;

    grant select on sys.user$ to c##sp_admin with grant option container=ALL;

    If TDE support is required for the CDB, then the following additional priviledge is required:

    grant select on sys.enc$ to c##sp_admin with grant option container=ALL;

    Run Oracle Setup

    Important! The Oracle instance must be open before this procedure is performed.

    1. (Unix and Linux only) If you are using multiple variable-data directories, export the environment variable that points to the variable-data directory for the SharePlex instance for which you are running Database Setup.

      ksh shell:

      export SP_SYS_VARDIR=/full_path_of_variable-data_directory

      csh shell:

      setenv SP_SYS_VARDIR /full_path_of_variable-data_directory

    2. Shut down any SharePlex processes that are running, including sp_cop.
    3. Run the ora_setup program from the command prompt of the operating system, using the full path from the SharePlex bin subdirectory.

      Important! On Windows, if you installed SharePlex on any port other than the default of 2100, use the -p option to specify the port number. For example, in the following command the port number is 9400:

      C:\users\splex\bin>ora_setup -p9400

    4. Specify whether the system is a source system, a target system, or both a source and target system in the SharePlex configuration.

      Note: This prompt only appears the first time that you run setup for this database.

    5. For connection type, select Oracle.
    6. Refer to the following table for the prompts and responses to configure SharePlex correctly for the desired connection type, either local using BEQUEATH or remote using a TNS alias.

    Table 2: Database setup prompts and response

    Prompt Response
    Will SharePlex install be using a BEQUEATH connection? (Entering 'n' implies a SQL*net connection):

    Press Enter to use a local BEQUEATH connection, or type N to use a TNS alias connection.

    Note: You must type N to use a tns_alias if the database is a multitenant database or if using SharePlex in a cluster (such as Oracle RAC).

    (If BEQUEATH= Y)

    Enter the Oracle SID for which SharePlex should be installed:

     

    (If BEQUEATH = N)

    Enter the TNS alias for which SharePlex should be installed:

    Non-multitenant database: Accept the default or type the correct SID or TNS alias. On RAC, the tns_alias must be the global alias that you created in Configure SharePlex in an Oracle cluster. For more information, see Configure SharePlex in an Oracle cluster in the SharePlex Installation Guide.

    Multitenant database: Specify the tns_alias of the PDB.

    Enter a DBA user for SID

    Non-multitenant database: Type the name of a database user that has DBA privileges.

    Multitenant database: Type the name of a common user who has the required privileges to install the account and objects.

    Enter password for the DBA account, which will not echo:

    Non-multitenant database: Type the password of the DBA user.

    Multitenant database: Type the password of the common user. Omit the @ and the rest of the connect string. SharePlex constructs the connect string in the proper format.

    Current SharePlex user is user.

    Would you like to create a new SharePlex user?

    Enter N to update an existing SharePlex account or Y to create a new SharePlex account. Enter the credentials when prompted.

    You are allowed five attempts to enter a valid password for an existing SharePlex user. Passwords are obfuscated.

    Important!If there is an active configuration and you changed the SharePlex schema, copy the SharePlex objects from the old schema to the new one to preserve the replication environment.

    Do you want to enable replication of tables with TDE?

    Note: If this is an upgrade and you already have TDE enabled, the following prompt appears before this prompt:

    Formerly, SharePlex required a Shared Secret key. Now, the pathname of the TDE wallet is required.

    Enter Y to be prompted for the path name of the TDE wallet file. Supply the fully qualified path for the TDE wallet file, including the wallet file name.

    Or...

    Press Enter if not replicating TDE tables.

    Enter the default tablespace for use by SharePlex:

    Press Enter to accept the default or type the name of a different tablespace.

    Enter the temporary tablespace for use by Shareplex:

    Press Enter to accept the default or type the name of a different tablespace.

    Enter the index tablespace for use by SharePlex:

    Press Enter to accept the default or type the name of a different tablespace.

    Will the current setup for sid: SID be used as a source (including cases as source for failover or master-master setups)?

    Press Enter if this is a source system or type N if this is a target system. Important: All systems in a master-master configuration (peer-to-peer) and in a high-availability configuration are considered to be source systems due to the bidirectional nature of the replication.

    Note: The rest of the prompts configure an ASM connection. If ASM is not detected, the database setup is complete at this point.

     

    ASM detected. Do you wish to connect to ASM using BEQUEATH connection?

    Press Enter for SharePlex to use a BEQUEATH connection to connect to the ASM instance, or press N to use a TNS alias.

    Important! If the database uses ASM and the database tns_alias is configured with a SCAN IP, then you must specify connection through an ASM tns_alias in order for SharePlex to connect to the ASM instance.

    Do you wish to keep connecting using the same user/password?

    If you selected BEQUEATH, this prompt is displayed. Enter Y to use the same user and password as the login user, or enter N to be prompted for a different user and password.

    Note: If you selected to use a BEQUEATH connection, the database setup is complete. See Note about the tns_alias file:

    If you selected N, the prompts continue.

     

    Enter the ASM tns alias to be used by SharePlex:

    Type the name of the TNS alias.

    Enter an ASM admin (has both sysdba and sysasm privilege) username for alias:

    Type the name of a user with sysasm and sysdba privileges to the ASM instance.

    Enter user password for user:

    Type the password of the user.

    Note: If SharePlex will be reading online redo logs on a remote system, make certain to set the SP_OCT_ASM_USE_OCI parameter to a value of 1:

    After you start sp_cop, and before activating a configuration file, run this command in sp_ctrl:

    sp_ctrl>set param SP_OCT_ASM_USE_OCI 1

    If this is an upgrade and you disabled DDL replication, you can enable it again with the following command in sp_ctrl:

    sp_ctrl> set param SP_OCT_REPLICATE_ALL_DDL 1

    Note about the tns_alias file:

    When you set up SharePlex for database connection through a tns_alias and ASM connection locally through a BEQUEATH connection (through OS authentication), it is important to set up the tns_alias file correctly on each node. Assuming a SharePlex database account exists on the primary node, SharePlex will always connect to the primary ASM_SID automatically because it was provided when SharePlex was installed. However, upon failover, SharePlex must query the local v$asm_client view to get the correct ASM_SID of the failover instance. Therefore, ensure that the IP address of a given node is always listed first in the local tns_names file on that node.

    PostgreSQL Setup (pg_setup)

    SharePlex utilities > PostgreSQL Setup (pg_setup)

    Overview

    Run the pg_setup program on a PostgreSQL system to establish a user account, schema, and tables for use by SharePlex.

    Supported databases

    All implementations of the PostgreSQL open-source database on supported platforms

    Guidelines for using pg_setup

    • Run pg_setup on all target PostgreSQL instances in the SharePlex replication configuration.
    • Within a server cluster, run pg_setup on the node to which the shared disk that contains the SharePlex variable-data directory is mounted.
    • For consolidated replication, run pg_setup for each variable-data directory.
    • You can provide a connection string or a DSN. Note the following:

      Connection Type What to do
      Connection string You do not need to provide a user, password, or default database in a connection string. These will be added when you run the setup program. The connection string must have the port, server and driver defined. This is an example: Port=5444;server=localhost;driver=/u01/PostgresPlus/connectors/odbc/lib/edb-odbc.so;database=edb;
      DSN

      If you have a DSN defined, and you want to use it for the SharePlex connection, copy or link the ODBC files in which that DSN is defined (odbc.ini and odbcinst.ini) to the odbc subdirectory of the SharePlex variable-data directory. This prevents connection errors when the SharePlex processes connect to the database.

      If you do not have a DSN defined but want to use one, you can create it in the template files provided in the odbc subdirectory.

    Required privileges to run pg_setup

    Review the following requirements to ensure that the setup succeeds.

    • The setup utility must be run as a PostgreSQL Administrator in order to grant SharePlex the required privileges to operate on the database and to create the SharePlex database account.
    • (Symfoware only) If the person who is running the setup is not a Fujitsu Enterprise Postgres owner, set the environment variable LD_LIBRARY_PATH to include the path to the lib subdirectory in the Fujitsu Enterprise Postgres installation directory. The LD_LIBRARY_PATH is set in the .bash_profile file of the Fujitsu Enterprise Postgres owner.

      Example:

      export LD_LIBRARY_PATH= /opt/symfoserver64/lib:$LD_LIBRARY_PATH

      If you do not set this path, the following error occurs:

      symbol lookup error: /opt/fsepv95client64/odbc/lib/psqlodbca.so: undefined symbol: PQconnectdbParams

    • Cloud installations:
      • Common restrictions on privileges in cloud-hosted database services make it difficult for the setup utility to succeed in every possible type of scenario. To ensure that the database setup succeeds, use the setup utility only to set up replication to the following: A new database, to be created by the setup utility, where the SharePlex user is also created by the setup utility.An existing database, where the SharePlex user is an existing user that is either the owner of the database or has access privileges to the database.

    Run pg_setup

    1. Shut down any running SharePlex processes and sp_cop on the target system.
    2. Run the pg_setup utility from the bin subdirectory of the SharePlex product directory.

      Important! If you installed the SharePlex instance on any port other than the default of 2100, use the -p option to specify the port number. For example, in the following command the port number is 9400.

      $ /users/splex/bin> pg_setup -p9400

    Table 3: Setup prompts and responses

    Prompt Response

    Enter the PostgreSQL DSN name or connection string [] :

    Enter a connection string or a data source name (DSN) that connects to the PostgreSQL target.

    Enter the PostgreSQL Administrator name :

    Enter the name of the PostgreSQL Administrator. This user will perform the work on the SharePlex account.

    Enter the password for the Administrator account :

    Enter the password of the Administrator.

    Enter the replication target database name:

    Enter the name of the database that you want to contain the SharePlex tables and other objects for use by SharePlex. You can enter the name of a new or existing database.

    Database name database does not exist. Would you like to create it? [y] :

    If this prompt is displayed, the specified database does not exist. Press Enter to have the setup program create it for you.

    Would you like to create a new SharePlex user [y]:

    Press Enter to accept the default to create a new SharePlex database user account and schema of the same name in the specified database, or enter n to use an existing SharePlex account.

    Enter the name of the new SharePlex user:

    Enter the name of the existing SharePlex user:

    One of these prompts is displayed depending on whether you elected to create a new user or use an existing user. Enter the name of the SharePlex user.

     

    Enter the password of the SharePlex user :

    Enter the password of the SharePlex user account.

    Re-enter the password for the SharePlex user :

    This prompt is only shown if you created a new user. Enter the SharePlex password again.

    A successful setup terminates with a message similar to the following:

    Completed SharePlex for PostgreSQL database configuration

    SharePlex User name: splex

    Database name: ndb5

    Target specification in SharePlex configuration: r.ndb5

    Related Documents