SharePlex 11.0 - SharePlex Installation and Setup Guide

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.

Supported Oracle connections

The setup utility can configure any of the following connections for the SharePlex user to use when connecting to the database.

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

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

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.

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

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' --

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

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.

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

Table 13: Setup Prompts and Responses

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

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