SharePlex 10.1.2 - Installation and Setup for Oracle Source

Set up replication from Oracle to SQL Server

Overview

SharePlex can post replicated source data to a Microsoft SQL Server target database through an Open Database Connectivity (ODBC) interface.

These instructions contain setup instructions that are specific to this target. Install SharePlex on the source and target according to the appropriate directions in this manual before performing these setup steps.

For the versions, data types and operations that are supported when using SharePlex to replicate to this target, see the SharePlex Release Notes.

Review column names

To support replication between a source of one database type and a target of another type, the letter case of the names of the source and target columns must be the same, for example the column names on both sides in lower case or both sides in upper case. If the case differs between the source and target column names, use the column mapping feature to map the column names in the configuration file.

See the SharePlex Administration Guide for more information about column mapping with SharePlex.

Configure SharePlex on the Source

Set up SharePlex and the database on the Oracle source system as follows.

Make keys available to SharePlex

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.

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

• To support replication between a source of one database type and a target of another type, the letter case of the names of the source and target columns must be the same, for example the column names on both sides in lower case or both sides in upper case. If the case differs between the source and target column names, use the column mapping feature to map the column names in the configuration file.

Note: This is a basic one-source, one-target configuration using no additional SharePlex configuration features. See "Configure SharePlex to replicate data" in the SharePlex Administration Guide for important information about creating a configuration file and for additional setup instructions for more complex replication scenarios.

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 source table is case-sensitive.

Configure SharePlex on the target

1. Make certain that the database setup meets all of the requirements in Open Target checklist .

2. Run Database Setup for SQL Server (mss_setup) to establish a database account and connection information for SharePlex. For more information, see Database Setup for SQL Server.

Set up replication from Oracle to PostgreSQL

Note: These instructions apply to all implementations of PostgreSQL unless otherwise noted.

Overview

SharePlex can post replicated Oracle data to a PostgreSQL target database through an Open Database Connectivity (ODBC) interface. SharePlex supports all implementations of the PostgreSQL open-source database.

These instructions contain setup instructions that are specific to this target. Install SharePlex on the source and target according to the appropriate directions in this manual before performing these setup steps.

For the versions, data types and operations that are supported when using SharePlex to replicate to this target, see the SharePlex Release Notes.

Review column names

To support replication between a source of one database type and a target of another type, the letter case of the names of the source and target columns must be the same, for example the column names on both sides in lower case or both sides in upper case. If the case differs between the source and target column names, use the column mapping feature to map the column names in the configuration file.

See the SharePlex Administration Guide for more information about column mapping with SharePlex.

Configure SharePlex on the Source

Set up SharePlex and the database on the Oracle source system as follows.

Make keys available to SharePlex

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.

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

• To support replication between a source of one database type and a target of another type, the letter case of the names of the source and target columns must be the same, for example the column names on both sides in lower case or both sides in upper case. If the case differs between the source and target column names, use the column mapping feature to map the column names in the configuration file.

Note: This is a basic one-source, one-target configuration using no additional SharePlex configuration features. See "Configure SharePlex to replicate data" in the SharePlex Administration Guide for important information about creating a configuration file and for additional setup instructions for more complex replication scenarios.

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 source table is case-sensitive.

Configure SharePlex on the target

1. Make certain that the database setup meets all of the requirements in Open Target checklist .

2. Run Database Setup for PostgreSQL (pg_setup) to establish a database account and connection information for SharePlex. For more information, see Database Setup for PostgreSQL.

Set up replication from Oracle to SAP HANA

Overview

SharePlex can post replicated Oracle data to a HANA target database through an Open Database Connectivity (ODBC) interface.

These instructions contain setup instructions that are specific to this target. Install SharePlex on the source and target according to the appropriate directions in this manual before performing these setup steps.

For the versions, data types and operations that are supported when using SharePlex to replicate to this target, see the SharePlex Release Notes.

Review column names

To support replication between a source of one database type and a target of another type, the letter case of the names of the source and target columns must be the same, for example the column names on both sides in lower case or both sides in upper case. If the case differs between the source and target column names, use the column mapping feature to map the column names in the configuration file.

See the SharePlex Administration Guide for more information about column mapping with SharePlex.

Configure SharePlex on the Source

Set up SharePlex and the database on the Oracle source system as follows.

Make keys available to SharePlex

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.

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

• To support replication between a source of one database type and a target of another type, the letter case of the names of the source and target columns must be the same, for example the column names on both sides in lower case or both sides in upper case. If the case differs between the source and target column names, use the column mapping feature to map the column names in the configuration file.

Note: This is a basic one-source, one-target configuration using no additional SharePlex configuration features. See "Configure SharePlex to replicate data" in the SharePlex Administration Guide for important information about creating a configuration file and for additional setup instructions for more complex replication scenarios.

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 source table is case-sensitive.

Configure SharePlex on the target

1. Make certain that the database setup meets all of the requirements in Open Target checklist .

2. Run Database Setup for HANA (hana_setup) to establish a database account and connection information for SharePlex. See Database Setup for HANA
3. See the target and typemap commands for additional configuration options.

Set up replication from Oracle to other Open Targets

Overview

SharePlex can connect to a database that supports Open Database Connectivity (ODBC).

These instructions contain setup instructions that are specific to this target. Install SharePlex on the source and target according to the appropriate directions in this manual before performing these setup steps.

For the versions, data types and operations that are supported when using SharePlex to replicate to this target, see the SharePlex Release Notes.

Review column names

To support replication between a source of one database type and a target of another type, the letter case of the names of the source and target columns must be the same, for example the column names on both sides in lower case or both sides in upper case. If the case differs between the source and target column names, use the column mapping feature to map the column names in the configuration file.

See the SharePlex Administration Guide for more information about column mapping with SharePlex.

Configure SharePlex on the Source

Set up SharePlex and the database on the Oracle source system as follows.

Make keys available to SharePlex

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.

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

• To support replication between a source of one database type and a target of another type, the letter case of the names of the source and target columns must be the same, for example the column names on both sides in lower case or both sides in upper case. If the case differs between the source and target column names, use the column mapping feature to map the column names in the configuration file.

Note: This is a basic one-source, one-target configuration using no additional SharePlex configuration features. See "Configure SharePlex to replicate data" in the SharePlex Administration Guide for important information about creating a configuration file and for additional setup instructions for more complex replication scenarios.

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 source table is case-sensitive.

Configure SharePlex on the target

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

Note: Make certain that the database setup meets all of the requirements in Open Target checklist .

Install the ODBC driver

Make certain to download the correct ODBC driver for your database.

Set up ODBC

1. Install and test the ODBC driver that is appropriate for your target database. SharePlex provides the OTS utility for testing the ability of SharePlex to connect to and post through ODBC. For more information, see the OTS documentation in the SharePlex Reference Guide.

2. On the target system, configure ODBC connection information for use by Post to connect to the target database. You have the following options for configuring this connection information:

   • On Windows, create a user or system DSN (Data Source Name) by using Data Sources (ODBC) in the Administrative Tools section of the Windows control panel. See the Windows documentation or your system administrator. If using a DSN, you must set the Post user name and password for the target database with the connection comand. See Set connection information with the connection command.

   • On Unix and Linux, you can do either of the following:

     • Configure a user or system DSN on the target system according to the instructions provided with the database. Test the DSN by using it to connect to the target database. If the connection is successful, copy the ODBC configuration files to the odbc subdirectory of the SharePlex variable-data directory. Set the LD_LIBRARY_PATH environment variable to the location of the database ODBC driver.

       or...

     • Set the ODBC connection information in the Post configuration. See Set connection information with the connection command.

Set connection information with the connection command

Use the connection command to:

• Set the Post user name and password if you created a DSN.
• Set all of theODBC connection information if a DSN does not exist.

To set connection information

1. Create a user account for SharePlex in the target database. This account must be granted the privileges to connect, query the metadata structures of the database, create and update tables in the SharePlex database or schema, and perform full DML and supported DDL operations. Make certain that this user can connect successfully to the database through ODBC outside SharePlex.
2. Run sp_ctrl.

3. Execute the connection command with the set option, once for each keyword.

   connection r.database_name set keyword=value

Option1: Input when a DSN exists

Option 2: Input when a DSN does not exist (Unix and Linux)

Option 3: Connect string when a DSN does not exist (Unix and Linux)

Connection command examples

DSN exists

connection r.mydb set user=myuser

connection r.mydb set password=mypassword

connection r.mydb set dsn=mydsn

DSN does not exist

connection r.mydb set user=myuser

connection r.mydb set password=mypassword

connection r.mydb set port=1234

connection r.mydb set server=server1

connection r.mydb set driver=/database/ODBC/lib/databasedriver.so

DSN does not exist, use connection string

connection r.mydb set user=myuser

connection r.mydb set password=mypassword

connection r.mydb set connect_string=”driver=/database/ODBC/lib/databasedriver.so;server=server1;port=1234;uid=myuser;pwd=mypassword”

Remove a connection value

Use connection with the reset option to remove SharePlex connection settings.

To remove a specific connection value

connection r.database_name reset keyword

To remove all connection values

connection r.database_name reset

Examples

connection r.mydb reset port

connection r.mydb reset

View connection values

Use connection with the show option to view SharePlex connection settings.

To view connection values for a database

connection r.database_name show

To view connection settings for all local databases

connection show all

Map data types

To support the accurate posting of replicated Oracle ALTER TABLE commands to the correct data types in an Open Target target, SharePlex maps Oracle data types to default data types for the target database. To view and change this mapping, use the typemap command.

Note: For DML operations, SharePlex queries the target database to determine the appropriate data type mapping for replicated data.

For more information about the typemap command, see the SharePlex Reference Guide.