Chat now with support
Chat with Support

SharePlex 11.0 - SharePlex Installation and Setup Guide

About this Guide Conventions used in this guide Revision History SharePlex pre-installation checklist Download the SharePlex installer Installation and setup for Oracle cluster Installation and setup for remote capture Install SharePlex on Linux and UNIX Assign SharePlex users to security groups Set up an Oracle environment for replication Set up replication from Oracle to a different target type Generic SharePlex demonstration-all platforms Advanced SharePlex demonstrations for Oracle Advanced SharePlex Demonstrations for PostgreSQL Solve Installation Problems Database Setup Utilities General SharePlex Utilities Uninstall SharePlex Advanced installer options Install SharePlex as root Run the installer in unattended mode SharePlex installed items

Oracle Setup (ora_setup)

Database Setup for Oracle

Overview

Use the Database Setup utility for Oracle (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 other objects 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 source or target on supported platforms

When to run Oracle setup

Whether or not to run this utility at the time of SharePlex installation depends on whether the database is a source, intermediary, or target database, 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 the Database Setup utility 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 the Database Setup utility during SharePlex installation.
  • If you intend to use a hot backup to establish the target data, do not run the Database Setup utility. You will run it when you perform the initial synchronization procedure.

Note: If you run the Database Setup utility 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

The setup utility 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.

    Amazon RDS

    TNS alias

    Required run privileges

    The user who runs the Database Setup utility must have the following privileges:

    Amazon RDS source or target

    The user who runs the setup utility must be the primary user that was created when the Oracle RDS instance was created. You are prompted for this user during the setup.

    Non-multitenant (standard) database:

    The user who runs the setup utility must have DBA 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;

    Privileges granted to SharePlex

  • The Database Setup utility grants to the SharePlex database user the following privileges.

    Privilege granted

    Description

    DBA role

    The Database Setup utility grants 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.

    Grants

    The following grants are issued to SharePlex:

    • To access the data dictionary (outside the DBA roles) if O7_DICTIONARY_ACCESSIBILITY is set to FALSE:

      grant select any dictionary toSharePlexUser;

    • To replicate DDL:

      grant select any table toSharePlexUserwith admin option;

      grant create any view toSharePlexUserwith admin option;

    Privileges Required by SharePlex to Perform Replication

    The following is a list of privileges required by SharePlex to perform replication. If you revoke the DBA role for the SharePlex database user, ensure these privileges are granted.

    Privilege Details
    CREATE SESSION  
    ALTER SESSION  
    ALTER SYSTEM  
    RESOURCE role

    RESOURCE role privileges required by SharePlex:

    • CREATE TRIGGER
    • CREATE SEQUENCE
    • CREATE TYPE
    • CREATE PROCEDURE
    • CREATE CLUSTER
    • CREATE OPERATOR
    • CREATE INDEXTYPE
    • CREATE TABLE
    SELECT ANY DICTIONARY  
    SELECT ANY TABLE  
    INSERT ANY TABLE  
    UPDATE ANY TABLE  
    DELETE ANY TABLE  
    UNLIMITED TABLESPACE  
    CREATE ANY TABLE  
    DROP ANY TABLE  
    LOCK ANY TABLE  
    EXECUTE ON DBMS_FLASHBACK  
    SELECT ANY TRANSACTION  
    CREATE ANY INDEX  
    DROP ANY INDEX  
    ALTER ANY TABLE  
    ALTER ANY INDEX  
    CREATE ANY VIEW  
    ALTER ANY SEQUENCE  
    EXP_FULL_DATABASE role  
    IMP_FULL_DATABASE role  
    SELECT ON SYS.ENC$  
    SELECT ON SYS.USER$  

    Requirements

    • 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 the Database Setup utility for all source and target Oracle instances in the SharePlex replication configuration.
    • For a consolidated replication topography, or other topology with multiple variable-data directories, run the Database Setup utility 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. If using TNS, the tnsnames.ora file must be configured prior to running setup.

    • If the Oracle database is a multitenant container database, run the Database Setup utility for each pluggable database involved in a replication scenario. A SharePlex user and schema objects must exist in each PDB.
    • If you run the Database Setup utility when there is an active configuration, the DDL that the 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 the utility, then return the parameter to its previous setting after the setup is complete. This parameter takes effect immediately.

    SharePlex schema storage requirements

    The Database Setup utility for Oracle 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 Database 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.

    Preferred action: If you enable supplemental logging for primary and unique keys, you can set the SP_OCT_ENABLE_LOBMAP parameter to 0, and nothing will be stored in the SHAREPLEX_LOBMAP table. In this case, you do not have to consider its size growth. It is recommended that you enable supplemental logging for primary and unique keys to maximize the performance of the Read process.

    Alternate action: 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 Database 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 Database 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.

    Run database setup for Oracle

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

    Perform the following steps to run database setup for Oracle:

    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 Database Setup program from the command prompt of the operating system, using the full path from the SharePlex bin subdirectory.

    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.

     

    Table 9: Setup prompts and response

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

    Press Y to use a local BEQUEATH connection, or press N to use a TNS alias connection.

    Note: Press N to use a TNS alias if:
    • the database is a multitenant database
    • SharePlex is capturing from, or posting to, a remote database, such as one on Amazon RDS.
    • the database is in a cluster (such as Oracle RAC)

    Are you configuring SharePlex for an AWS RDS database?

    Press N if you are not configuring SharePlex for an Oracle database on RDS.

    Press Y if you are configuring SharePlex for an Amazon AWS RDS database.

     

    One of the following prompts is shown:

    • If you selected BEQUEATH= Y:

      Enter the Oracle SID for which SharePlex should be installed:

    • If you selected 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 a global alias.

    Multitenant database: Type the TNS alias of the PDB.

    Amazon RDS: Type the TNS alias of the RDS database.

     

    One of the following prompts is shown:

    • If the database is not on RDS:

      Enter a DBA user for SID:

    • If the database is on RDS:

      In order to create the SharePlex tables and user account, we must connect to the RDS database using the RDS primary user.

       

    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.

    Amazon RDS database: Type the name of the RDS primary user.

     

    One of the following prompts is shown:

    • If the database is not on RDS:

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

    • If the database is on RDS:

      Enter the password for the RDS primary user, 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.

    Amazon RDS database: Type the password of the RDS primary user.

     

    Current SharePlex user is user.

    Would you like to create a new SharePlex user?

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

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

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

    Do you want to enable replication of tables with TDE?

    Press N since TDE is not supported for RDS.

     

    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 primary-primary setups)?

    Press Y if this is a source system or press N if this is a target system.

    Important: All systems in a primary-primary configuration (peer-to-peer) and in a high-availability configuration are considered to be source systems due to the bidirectional nature of the replication.

     

    Notes:
    • The following prompts are only shown if the database is a source on ASM.
    • If this is an Oracle target, the setup is now complete.

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

     

    Press Y 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.

    The following prompt is displayed if you did not select a BEQUEATH connection:

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

     

    Press Y to use the same user and password as the login user, or press N to be prompted for a different user and password.

    Normally the user running SharePlex must be a member of the OSASM group. This does not apply if SP_OCT_OLOG_USE_OCI is set to the non-default value of 1.

    Also if you are using a BEQUEATH connection, the user running SharePlex must be a member of the OSDBA group.

    Note: If you selected to use a BEQUEATH connection to connect to ASM, the database setup is complete. Continue to Database Setup for Oracle.

    If you selected N, you need to supply a TNS alias, and 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.

    SharePlex installs internal objects that include a package to support the SDO_GEOMETRY data type of the Oracle Spatial and Graph option. If this option is not installed in the database, the following prompt is shown:

    The SharePlex object that supports replication of SDO_GEOMETRY cannot be installed because the Oracle Spatial and Graph feature is not installed. Do you want to continue with the setup without support for SDO_GEOMETRY? [n]:

    Press Y to continue the database setup without support for SDO_GEOMETRY, or press N to terminate ora_setup.

    1. After completion of the Oracle setup, the ora_setup.config file will be generated inside <Installation_directory>/var/data.
    Note about the tnsnames 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 tnsnames.ora 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 tnsnames.ora file on that node.

    Run database setup for Oracle remote capture

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

    Perform the following steps to run database setup for Oracle remote capture:

    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. Set the SP_OCT_OLOG_USE_OCI parameter to 1 and restart the Capture process.
    3. Shut down any SharePlex processes that are running, including sp_cop.
    4. Run the Database Setup program from the command prompt of the operating system, using the full path from the SharePlex bin subdirectory.

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

    6. For connection type, select Oracle.
    7. Refer to the following table for the prompts and responses to configure SharePlex correctly for the desired connection type.

    Table 10: Setup prompts and response

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

    Press N to use a TNS alias connection.

    Are you configuring SharePlex for an AWS RDS database?

    Press N if you are not configuring SharePlex for an Oracle database on RDS.

    One of the following prompts is shown:

    • If you selected BEQUEATH= Y:

      Enter the Oracle SID for which SharePlex should be installed:

    • If you selected 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 a global alias.

    Multitenant database: Type the TNS alias of the PDB.

    Amazon RDS: Type the TNS alias of the RDS database.

    One of the following prompts is shown:

    • If the database is not on RDS:

      Enter a DBA user for SID:

    • If the database is on RDS:

      In order to create the SharePlex tables and user account, we must connect to the RDS database using the RDS primary user.

    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.

    Amazon RDS database: Type the name of the RDS primary user.

     

    One of the following prompts is shown:

    • If the database is not on RDS:

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

    • If the database is on RDS:

      Enter the password for the RDS primary user, 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.

    Amazon RDS database: Type the password of the RDS primary user.

     

    Enter the Local Oracle Client Library directory:

    Enter the Oracle Client Home directory path.

    Example:

    /u01/app/oracle/product/19.0.0/clienthome_1

    Enter the TNS alias for which SharePlex should be installed (Blank to abort) : Enter the name of the TNS alias

    Current SharePlex user is user.

    Would you like to create a new SharePlex user?

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

    You are allowed five attempts to type 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?

    Press N since TDE is not supported for RDS.

    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 primary-primary setups)?

     

    Press Y if this is a source system or press N if this is a target system.

    Important: All systems in a primary-primary configuration (peer-to-peer) and in a high-availability configuration are considered to be source systems due to the bidirectional nature of the replication.

    The following prompt is displayed if you did not select a BEQUEATH connection:

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

     

    Press Y to use the same user and password as the login user, or press N to be prompted for a different user and password.

    Normally the user running SharePlex must be a member of the OSASM group. This does not apply if SP_OCT_OLOG_USE_OCI is set to the non-default value of 1.

    Also if you are using a BEQUEATH connection, the user running SharePlex must be a member of the OSDBA group.

    SharePlex installs internal objects that include a package to support the SDO_GEOMETRY data type of the Oracle Spatial and Graph option. If this option is not installed in the database, the following prompt is shown:

    The SharePlex object that supports replication of SDO_GEOMETRY cannot be installed because the Oracle Spatial and Graph feature is not installed. Do you want to continue with the setup without support for SDO_GEOMETRY? [n]:

    Press Y to continue the database setup without support for SDO_GEOMETRY, or press N to terminate ora_setup.

    Note: While running the ora_cleansp utility, enter the TNS alias provided while performing ora_setup.

    Run database setup for Oracle in silent mode

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

    Perform the following steps to run database setup for Oracle in silent mode:

    1. (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.
    1. Use the following command to run Ora_setup in the background in silent mode:-
      nohup ./ora_setup -f <Full_file_path of ora_setup.config> > SilentOra.out 2>&1
    2. For more details on how to configure the Ora_setup.config file, refer to the ora_setup.config file at the below location:
      /<product directory>/install/ora_setup.config

    Note: After the setup is completed successfully, all of the password fields will be automatically removed from the config file for security reasons.

  • Database Setup for PostgreSQL as a Source

    PostgreSQL configuration prerequisites:

    1. In the PostgreSQL configuration file, set the wal_level parameter to logical.
      For example:wal_level = logical # minimal, replica, or logical
    2. Add an entry of SharePlex user in the pg_hba.conf file so that SharePlex Capture can establish a connection with the walsender process of PostgreSQL
      For example: # TYPE DATABASE USER ADDRESS METHOD
      host replication all 10.10.10.10/32 trust
    3. For all the tables which are going to be part of replication, set the 'replica identity' to 'full' before activation.
      For example: alter table "DEMO_SRC" replica identity full;
    4. Navigate to the /var/lib/pgsql/13/data directory and edit the PostgreSQL file.
      Remove the comment and increase the below two parameters' value as below:
      wal_sender_timeout = 6000s
      wal_receiver_timeout = 6000s
    5. Navigate to the bin directory and restart database: cd /usr/pgsql-13/bin/./pg_ctl -D /var/lib/pgsql/13/data/ restart
    Note:SharePlex PostgreSQL as Source does not replicate data from Archive WAL files.
    Overview

    Run the PG Setup utility for PostgreSQL (pg_setup) to establish a user account, schema, and tables for use by SharePlex.

    Guidelines for Use

    • Run the PG Setup utility on all source PostgreSQL instances in the SharePlex replication configuration.
    • Within a server cluster, run the PG Setup utility on the node to which the shared disk that contains the SharePlex variable-data directory is mounted.
    • For consolidated replication, run the PG Setup utility for each variable-data directory.
    • You can provide a connection string or a DSN (data source name). Note the following when deciding on the connection method on Linux:

      Connection Type on Linux What to do
      Connection string You do not need to provide a user, password, or default database in a connection string. The connection string must have the port, server and driver defined. This is an example from EDB PostgreSQL Plus Advanced Server; your connection string will probably be different based on your own PG setup: 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.

    For an example of the sample odbc.ini and odbcinst.ini files, see the example below:

    Required Privilege

    The PG_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.

    Run PG Setup for PostgreSQL

    Follow the below instructions to run the PG setup for PostgreSQL:

    1. Shut down any running SharePlex processes and sp_cop on the source system.
    2. Run the pg_setup program 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 11: Setup prompts and responses

    Prompt Response

    (Linux)

    Enter the PostgreSQL DSN name or connection string[]:

    Enter a DSN or a connection string.

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

    NOTE: Enter the user name in lowercase letters only.

    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.
    Will this database be used as a source? [n] : Y
    Enter the replication slot name? Enter the replication slot name. Example: Slot1, Slot2. etc
    Enter the default tablespace for use by SharePlex [pg_default] : Enter the default tablespace by SharePlex
    Enter the index tablespace for use by SharePlex [pg_default] : Enter the index tablespace by SharePlex

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

    Completed SharePlex for PostgreSQL database configuration

    SharePlex User name: splexab

    Database name: testdb

    -- The datasource identifier in the SharePlex configuration is 'r.testdb' --

    Important!SharePlex holds the WAL logs when configuration is not active. When pg_setup is executed, it creates a replication slot. This replication slot maintains the latest LSN value of the WAL records that Capture has received and processed. Also, it holds all the WAL files from getting removed or archived, starting from the stored LSN in this slot.

    Following are the steps that need to be performed when no config is active and you don’t want to hold WAL files in the pg_wal directory:

    1. Get the replication slot name from the var/data/connections.yaml file.

    2. Check if the replication slot exists in the database using the below query:

      select count(*) FROM pg_replication_slots where slot_name = <slot_name>;

    3. If a replication slot exists, then delete that replication slot from the database using the below query:

      select pg_drop_replication_slot(<slot_name>);

    4. If you want to activate the config file, then first execute the pg_setup utility, which will create the replication slot again, and then activate the config file.

    PostgreSQL Setup (pg_setup)

    Database Setup for PostgreSQL as a Target

    Overview

    Run the Database Setup utility for PostgreSQL (pg_setup) 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

    Fujitsu Enterprise PostgreSQL Prerequisite

    Fujitsu PostgreSQL contains a customized ODBC driver designed to run with the shared libraries supplied by Fujitsu PostgreSQL. In order to ensure the proper configuration, you must create a symbolic link to ensure that the correct libraries are found at runtime.

    Issue Description:

    If you attempt to run the pg_setup program prior to creating the symbolic link, pg_setup will fail with the following error message:

    Error: [unixODBC]invalid connection option "target_server"

    To create a symbolic link

    To ensure that the proper library gets loaded, first locate the path to the directory where the Fujitsu PostgreSQL libraries are installed (for example /opt/fsepv95server64/lib). Then create a symbolic link in the SharePlex OPTDIR/lib/hooks directory pointing to the libpq.so.5 library in the Fujitsu PostgreSQL library directory. For example:

    cd /opt/splex/lib/hooks

    ln –s /opt/fsepv95server64/lib/libpq.so.5

    Guidelines for use

    • Run the Database Setup utility on all target PostgreSQL instances in the SharePlex replication configuration.
    • Within a server cluster, run the Database Setup utility on the node to which the shared disk that contains the SharePlex variable-data directory is mounted.
    • For consolidated replication, run the Database Setup utility for each variable-data directory.
    • On Linux, you can provide a connection string or a DSN (data source name). Note the following when deciding on the connection method on Linux:

      Connection Type on Linux What to do
      Connection string You do not need to provide a user, password, or default database in a connection string. The connection string must have the port, server and driver defined. This is an example from EDB PostgreSQL Plus Advanced Server; your connection string will probably be different based on your own database setup: 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

    Review the following requirements to ensure that the setup succeeds.

    • The Database 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 utility is not a Fujitsu Enterprise PostgreSQL owner, set the environment variable LD_LIBRARY_PATH to include the path to the lib subdirectory in the Fujitsu Enterprise PostgreSQL installation directory. The LD_LIBRARY_PATH is set in the .bash_profile file of the Fujitsu Enterprise PostgreSQL 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, only use the setup utility for the following purposes: To do a first-time database setup with a new SharePlex user, or, to modify an existing SharePlex user that either owns the database or has access to it.
      • On Amazon RDS, you might need to grant usage/privileges on the target schema and tables to the SharePlex user, as shown in the following example:

        Log in as the schema owner, then issue the following commands:

        grant usage on schema schema_name to user_name;

        grant all privileges on all tables in schema schema_name to user_name;

    Run Database Setup for PostgreSQL

    1. Shut down any running SharePlex processes and sp_cop on the target system.
    2. Run the pg_setup program 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 12: Setup prompts and responses

    Prompt Response

    (Linux)

    Enter the PostgreSQL DSN name or connection string [] :

    Enter a DSN or a connection string if the system is Linux.

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

    NOTE: Enter the user name in lowercase letters only.

     

    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

    Configure SharePlex to support PostgreSQL database on Azure platform

    You can configure SharePlex to support the PostgreSQL database on the Azure platform using the below steps:

    1. Add the DSN entry in the Odbc.ini file at /etc/odbc.ini and the …/var/odbc directory.
    2. Copy the /etc/odbcinst.ini file to the …/var/odbc directory.
    3. Run the PostgreSQL setup utility.
    This process creates the SharePlex tables, login and user accounts needed to run SharePlex replication.

    Note: While performing DML operations in bulk for PostgreSQL PaaS (Azure), a performance slowness issue is observed.

     

    Table 13: Setup Prompts and Responses

    Prompt Response

    Enter the PostgreSQL DSN name or connection string [] :

    Enter the PostgreSQL DSN name or a connection string that connects to the PostgreSQL database system.

    Example: pslazpgdb02

    Is DB hosted over Azure ? :

    Y

    Enter the Database hostname :

    Enter the Azure database host name.

    Note: PGsetup Administrator name should be in the <username> format (Don't mention hostname)

    Example: pslazpgdb02.postgres.database.azure.com

    If a user already exists for SharePlex then it should be entered in the <username@hostname> format.

    If a new user needs to be created for SharePlex then is should be entered in the <username> format.

    Enter the PostgreSQL Administrator name :

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

    Example: PostgreSQL

    Enter the password for the Administrator account:

    Enter the password of the Administrator.

    Note: User 'postgres@pslazpgdb02.postgres.database.azure.com' got the superuser role.

    Enter the replication target database name :

    Enter the target database name on which replication needs to be performed.

    Example:

    sp_mk

    Would you like to create a new SharePlex user?

    Enter the name of the existing SharePlex user:

    N (If you want to create a user, enter “y”)

     

    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: postgres@pslazpgdb02.postgres.database.azure.com

    Database name: sp_mk

    The data source identifier in the SharePlex configuration is 'r.sp_mk' –

     

     

    SQL Server Setup (mss_setup)

    Database Setup for SQL Server

    Overview

    Run the Database Setup utility for SQL Server (mss_setup) on a Microsoft SQL Server system to establish SharePlex as a SQL Server database user.

    This utility creates the following:

    • A SharePlex user account with the db_owner role
    • Tables and indexes for use by SharePlex and owned by the SharePlex user in a database of your choosing
    • A default database connection.

    Supported databases

    For supported SQL Server platforms and versions, see the SharePlex Release Notes.

    Guidelines for use

    • A system DSN (data source name) must exist for the SQL Server database. SharePlex Post uses the DSN to connect to the database through ODBC.
    • Run the Database Setup utility on all SQL Server instances in the SharePlex replication configuration.
    • Within a cluster, run the Database Setup utility on the node to which the shared disk that contains the variable-data directory is mounted.
    • For consolidated replication, run the Database Setup utility for each variable-data directory.

    Required privileges

    Review the following requirements to ensure that the setup succeeds.

    • The Database Setup utility must be run as a SQL Server System Administrator in order to grant SharePlex the required privileges to operate on the database and to create the SharePlex database account and objects.

    • The sysadmin role is required to run mss_setup, but it is not required by the SharePlex database account used to run SharePlex replication (the SharePlex User). The db_owner role is required by the SharePlex User account and it is granted by mss_setup (for source and target).

    • (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, only use the setup utility for the following purposes: To do a first-time database setup with a new SharePlex user, or, to modify an existing SharePlex user that either owns the database or has access to it. (Cloud installation is not supported while replicating data from PostgreSQL to SQL Server)

    Run database setup for SQL server

    Perform the following steps to run database setup for SQL server:

    1. Shut down any running SharePlex processes and sp_cop on the SQL Server system.
    2. Run the mss_setup program 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.

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

    Table 14: Setup prompts and responses

    Prompt Response

    Enter the Microsoft SQL Server DSN name [] :

    Enter the data source name (DSN) that connects to SQL Server. Make certain the DSN is a system DSN, not a user DSN.

    Enter the Microsoft SQL Server Administrator name :

    Enter the name of the SQL Server Administrator. This user will perform the setup work on the SharePlex account and schema.

    Enter the password for the Administrator account :

    Enter the password of the Administrator.

    Enter the database name:

    Enter the name of the database where you want to install the SharePlex objects.

    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 utility create it for you.

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

    Press Enter to accept the default to create a new SharePlex database user account, or enter n to use an existing account as the SharePlex database user.

    Enter the name of the existing SharePlex login:

    Enter the name of the new SharePlex login:

    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 for login:

    Enter the password of the SharePlex user account.

    Re-enter the password for login :

    Enter the SharePlex password again.

    Will this database be used as a source?

    Accept the default of n if the database will only be a target. Enter y if this database will be a source database for SharePlex. A response of y prompts the setup to prepare the database for data capture and installs the SharePlex account and objects.

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

    Completed SharePlex for Microsoft SQL Server database configuration

    SharePlex User name: splex

    Database name: db1

    Target specification in SharePlex configuration: r.db1

    Configure Remote Posting from a Linux to a SQL Server Database

    To replicate data from a Linux server to a SQL server database, follow the steps below:

    1. Install the latest SQL Server ODBC drivers on Linux.

      By default, the ODBC drivers will be installed at /opt/microsoft/msodbcsql18/lib64.

    2. Modify the odbcinst.ini and odbc.ini files in the <var_dir>/odbc directory.

    3. Run the mss_setup program from the bin subdirectory of the SharePlex product directory.

    4. Provide other details when it prompts you, like the database name, username, password, etc. For more information, see Run Database Setup for SQL Server.

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

    Completed SharePlex for Microsoft SQL Server database configuration

    SharePlex User name: splex

    Database name: db1

    Target specification in SharePlex configuration: r.db1

     

    Related Documents

    The document was helpful.

    Select Rating

    I easily found the information I needed.

    Select Rating