SharePlex 10.0 - Installation and Setup for Oracle Source

HANA Setup (hana_setup)

Overview

Run the Database Setup utility for HANA (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 use

• Run the setup utility on all target HANA instances in the SharePlex replication configuration.
• Within a server cluster, run the setup utility on the node that has the mount point to the shared disk that contains the SharePlex variable-data directory.
• For consolidated replication, run the setup utility for each variable-data directory.

• The only supported connection method to HANA is by connect string. Connection through a DSN is not supported.

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

Required privileges

The setup utility 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 Database Setup for HANA

1. Shut down any running SharePlex processes and sp_cop on the target system.

2. Run the hana_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> hana_setup -p9400

Table 5: Setup Prompts and Responses

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

MySQL Setup (mysql_setup)

Overview

Run the Database Setup utility for MySQL (mysql_setup) on a MySQL system to establish SharePlex as a MySQL database user. This utility creates the following:

• A SharePlex user account with full DBA privileges
• 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

MySQL on Linux. For supported Linux platforms and versions, see the SharePlex Release Notes.

Guidelines for use

• Run the setup utility on all MySQL instances in the SharePlex replication configuration.
• Within a cluster, run the setup utility on the node to which the shared disk that contains the SharePlex variable-data directory is mounted.
• For consolidated replication, run the setup utility for each variable-data directory.

Required privileges

Review the following requirements to ensure that the setup succeeds.

• The setup utility must be run as a MySQL Administrator that retains all of that user's default privileges. Both local and cloud MySQL Administrators should have the required privileges by default. The Administrator user is able to grant SharePlex the required privileges to operate on the database and to create the SharePlex database account and objects.
• (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.

Run Database Setup for MySQL

1. Shut down any running SharePlex processes and sp_cop on the MySQL system.

2. Run the mysql_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> mysql_setup -p9400

Table 6: Setup prompts and responses

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

Completed SharePlex for MySQL database configuration

SharePlex User name: mysql29

Database name: mysql29

Target specification in SharePlex configuration: r.mysql29

Oracle Setup (ora_setup)

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

PostgreSQL Setup (pg_setup)

Overview

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

Supported databases

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

Fujitsu Enterprise Postgres Prerequisite

Fujitsu Postgres contains a customized ODBC driver designed to run with the shared libraries supplied by Fujitsu Postgres. 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 Postgres 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 Postgres 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 Postgres 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 Windows, you must supply a DSN (data source name) as the connection method.

• 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 Postgres 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 Postgres 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 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, 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 8: 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