SharePlex 8.6.6 - Administration Guide

Build a configuration file using a script

SharePlex provides the following scripts to automate the building of a configuration file to specify Oracle source objects.

• config.sql: configure all tables and optionally all sequences in the database.
• build_config.sql: configure multiple or all tables in a schema

Supported databases

Oracle

Use config.sql

The config.sql script enables you to build a configuration that lists all of the tables, and optionally all of the sequences, in all of the schemas of a database. This script saves time when establishing a high-availability replication strategy or other scenario where you want the entire database to be replicated to an identical secondary database.

Conditions for using config.sql

• Source and target table names must be the same.

• The script does not configure objects in the SYS, SYSTEM, and SharePlex schemas. These schemas cannot be replicated since they are system and/or instance-specific.
• The script does not support partitioned replication. You can use the copy config command to copy the configuration file that the script builds, then use the edit config command to add entries for tables that use partitioned replication. Activate the new configuration file, not the original one.
• You can use the edit config command to make any other changes as needed after the configuration is built.

To run config.sql

1. Change directories to the config sub-directory of the SharePlex variable-data directory. The config.sql script puts configurations in the current working directory, and SharePlex configurations must reside in the config sub-directory.

   cd /vardir/config

2. Log onto SQL*Plus as SYSTEM.

3. Run config.sql using the full path from the util sub-directory of the SharePlex product directory.

   @ /proddir/util/config.sql

Refer to the following table when following the prompts.

Note: The name assigned by SharePlex to the configuration is config.file. If you run the script again to create another configuration file, it overwrites the first file. To preserve the original file, rename it before you create the second one.

Next steps:

• If any tables or owners are case-sensitive, open the configuration file with the edit config command in sp_ctrl, then use the text editor to enclose case-sensitive table and owner names within double-quote marks, for example “scott”.“emp”. The script does not add the quote marks required by Oracle to enforce case-sensitivity.

  sp_ctrl> edit config filename

• To ensure that the configuration is in the correct location, issue the list config command. If the name of the configuration is not shown, it was created in the wrong directory. Find the file and move it to the config sub-directory of the variable-data directory.

  sp_ctrl> list config

Use build_config.sql

The build_config.sql script enables you to build a configuration that contains multiple (or all) tables in a schema. It is an interactive script that prompts for each component of the configuration step by step. Instead of entering the information for each object and the routing individually, you can use a wildcard to select certain tables at once, or you can select all of the tables in the schema.

Conditions for using build_config.sql

• Source and target table names must be the same.
• The script does not support sequences. Before you activate the configuration that the script builds, you can use the edit config command in sp_ctrl to add entries for sequences.
• The script does not support partitioned replication. You can use the copy config command to copy the configuration that the script builds, then use the edit config command to add entries for the tables that use partitioned replication. Activate the new configuration, not the original.
• The script does not configure objects in the SYS, SYSTEM, and SharePlex schemas. These schemas cannot be replicated since they are system and/or instance-specific.
• You can run build_config.sql for different schemas, then combine those configurations into one configuration by using a text editor. Make certain to eliminate all but one Datasource:o.SID line, which is the first non-commented line of the file. Do not move the file out of the config sub-directory.
• You can use the edit config command to make any other changes as needed after the configuration is built.

To run build_config.sql

1. Change directories to the config sub-directory of the SharePlex variable-data directory. The build_config.sql script puts configurations in the current working directory, and SharePlex configurations must reside in the config sub-directory.

   cd /vardir/config

2. Log onto SQL*Plus as SYSTEM.

3. Run build_config.sql using the full path from the util sub-directory of the SharePlex product directory.

   @ /proddir/util/build_config.sql

Refer to the following table when following the prompts.

Next steps:

• If any tables or owners are case-sensitive, open the configuration with the edit config command in sp_ctrl, then use the text editor to enclose case-sensitive table and owner names within double-quote marks, for example “scott”.“emp”. The script does not add the quote marks required by Oracle to enforce case-sensitivity.

  sp_ctrl> edit config filename

• To ensure that the configuration is in the correct location, issue the list config command. If the name of the configuration is not shown, it was created in the wrong directory. Find the file and move it to the config sub-directory of the variable-data directory.

  sp_ctrl> list config

Configure replication to Open Target targets

This chapter contains instructions for configuring SharePlex to replicate data from Oracle Database to Open Target (non-Oracle) targets. The Post process must be configured to process data to the intended target in the correct manner and format. This chapter guides you through the Post configuration requirements for each supported target.

Note: The SharePlex-supported Open Target targets are listed in the SharePlex Release Notes. Other targets may be in beta testing. For more information about the SharePlex beta program, see the SharePlex Release Notes.

Contents

Configure replication to a Microsoft SQL Server target

Overview

SharePlex can post replicated source data to a Microsoft SQL Server target database through an Open Database Connectivity (ODBC) interface. These instructions guide you through the configuration processes that are required to support this target.

Note: For the datatypes and operations that are supported when using SharePlex to replicate to a SQL Server target, see the SharePlex Release Notes.

Configure SharePlex on the source

Configure SharePlex and the database on the source system as follows.

Enable Oracle keys

To replicate from an Oracle source to an Open Target target, you must make key information available to SharePlex.

Enable Oracle supplemental logging

Enable PK/UK supplemental logging in the Oracle source database. SharePlex must have the Oracle key information to build an appropriate key on the target.

Set SP_OCT_USE_SUPP_KEYS parameter

Set the SP_OCT_USE_SUPP_KEYS parameter to a value of 1. This parameter directs SharePlex to use the columns set by Oracle's supplemental logging as the key columns when a row is updated or deleted. When both supplemental logging and this parameter are set, it ensures that SharePlex can always build a key and that the SharePlex key will match the Oracle key.

See the SharePlex Reference Guide for more information about this parameter.

Configure replication

On the source, create a SharePlex configuration file that specifies capture and routing information. The components that are required in a configuration file vary, depending on your replication strategy. However, the most important part of the configuration file as it relates to replication to an Open Target target is the portion of the routing map after the @ symbol.

Note: See Create configuration files for additional information about creating a configuration file.

To configure replication to an Open Target target, use the following syntax in the configuration file:

where:

* Important! If target owner or table name is defined in the database as anything other than UPPERCASE, be certain to:

• Type the name in the correct case.
• Enclose the name in quotation marks, for example "MySchema"."MyTable".

Source configuration example

The following configuration file replicates table HR.EMP from Oracle instance ora112 to target table Region1.Emp in database mydb on target system sysprod. The target table is case-sensitive.

Configure SharePlex on the target

Configure the target database and SharePlex as follows.

Note: The first two steps might have been performed already if the procedures in the SharePlex Preinstallation Checklist were followed before SharePlex was installed.

1. Install the Microsoft SQL Server ODBC Driver. It must be that driver, not the Microsoft SQL Server Native Client, or SharePlex will return an error when you run mss_setup to configure Post to connect to the database. To tell the difference between the two drivers:

   • The Microsoft SQL Server ODBC Driver has versions such as 06.02.9200.
   • The Microsoft SQL Server Native Client has versions such as 11.00.3513.

2. Create a System (not User) Data Source Name (DSN) for the SQL Server database on the Windows system. The DSN can use either Windows NT authentication or SQL Server authentication. If you configure the DSN to use NT authentication and are using SQL Server 2012 or later, grant the NTAuthority\SYSTEM user the sysadmin fixed server role. ( For earlier versions of SQL Server, sysadmin is granted to the NT Authority\SYSTEM user by default.)

3. Test the DSN and make certain the ODBC connection works before proceeding to the next step.

4. If you did not do so when you installed SharePlex, run the mss_setup utility, which performs the following target setup:

   • Establish a SharePlex database account with privileges to post to the target SQL Server database
   • Establish a target connection configuration
   • Install tables and indexes for use by SharePlex and owned by the SharePlex account

   See the SharePlex Reference Guide for more information about this utility.

Configure replication to a Postgres (PPAS) target

Overview

SharePlex can post replicated Oracle data to a PostgreSQL target database through an Open Database Connectivity (ODBC) interface. These instructions guide you through the configuration processes that are required to support this target.

NoteS:

• SharePlex supports all implementations of the PostgreSQL open-source database.
• For the datatypes and operations that are supported when using SharePlex to replicate to PostgreSQL-based databases, see the SharePlex Release Notes.

Configure SharePlex on the source

Configure SharePlex and the database on the source system as follows.

Enable supplemental logging

To replicate to an Open Target target, enable PK/UK supplemental logging in the Oracle source database. SharePlex must have the Oracle key information to build an appropriate key on the target.

Set SP_OCT_USE_SUPP_KEYS parameter

To replicate to an Open Target database target, set the SP_OCT_USE_SUPP_KEYS parameter to a value of 1. This parameter directs SharePlex to use the columns set by Oracle's supplemental logging as the key columns when a row is updated or deleted. When both supplemental logging and this parameter are set, it ensures that SharePlex can always build a key and that the SharePlex key will match the Oracle key.

See the SharePlex Reference Guide for more information about this parameter.

Configure replication

On the source, create a SharePlex configuration file that specifies capture and routing information. The components that are required in a configuration file vary, depending on your replication strategy. However, the most important part of the configuration file as it relates to replication to an Open Target target is the portion of the routing map after the @ symbol.

Note: See Create configuration files for additional information about creating a configuration file.

To configure replication to an Open Target target, use the following syntax in the configuration file:

where:

* Important! If target owner or table name is defined in the database as anything other than UPPERCASE, be certain to:

• Type the name in the correct case.
• Enclose the name in quotation marks, for example "MySchema"."MyTable".

Source configuration example

The following configuration file replicates table HR.EMP from Oracle instance ora112 to target table Region1.Emp in database mydb on target system sysprod. The target table is case-sensitive.

Configure SharePlex on the target

1. If you have not done so already, install the appropriate ODBC driver for the target database.

   Note: For EDB Postgres Advanced Server, see the StackBuilder documentation for instructions on how to get the most current Connector for your environment.

2. If you did not do so when you installed SharePlex, run the pg_setup utility, which establishes a SharePlex database account and connection information. See the SharePlex Reference Guide for more information about this utility.