SharePlex provides the following scripts to automate the building of a configuration file to specify Oracle source objects.
Oracle
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.
Source and target table names must be the same.
To run config.sql
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
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.
Prompt | What to enter |
---|---|
Target machine | The name of the target machine, for example SystemB. |
Source database SID | The ORACLE_SID of the source (primary) Oracle instance, for example oraA. Do not include the o. keyword. The ORACLE_SID is case-sensitive. |
Target database SID | The ORACLE_SID of the target (destination) Oracle instance, for example oraB. Do not include the o. keyword. The ORACLE_SID is case-sensitive. |
Replicate sequences | Enter y to replicate sequences or n not to replicate sequences. |
Shareplex oracle username | The name of the SharePlex user in the source database. This entry prevents the SharePlex schema from being replicated, which would cause replication problems. If a valid name is not provided, the script fails. |
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
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.
To run build_config.sql
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
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.
Prompt | What to enter |
---|---|
Target machine | The name of the target machine, for example SystemB. |
Source database SID | The ORACLE_SID of the source (primary) Oracle instance, for example oraA. Do not include the o. keyword. The ORACLE_SID is case-sensitive. |
Target database SID | The ORACLE_SID of the target (destination) Oracle instance, for example oraB. Do not include the o. keyword. The ORACLE_SID is case-sensitive. |
Owner of the source database tables | The owner of the source tables. |
Owner of the target database tables | The owner of the target tables. |
Table name to include (blank for all) |
Do one of the following:
|
Name of the output file to create | A name for the configuration. The script gives the file a .lst suffix, for example Scott_config.lst. |
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
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.
Configure replication to a Microsoft SQL Server target
Configure replication to a Postgres (PPAS) target
Configure replication to an SAP ASE target
Configure replication to a SAP HANA target
Configure replication to a Teradata target
Configure replication to other Open Target databases
Configure replication to a JMS target
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 and the database on the source system as follows.
To replicate from an Oracle source to an Open Target target, you must make key information available to SharePlex.
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 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.
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:
Datasource: datasource_spec | ||
src_owner.table |
tgt_owner.table |
host@r.database_name |
where:
* Important! If target owner or table name is defined in the database as anything other than UPPERCASE, be certain to:
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.
Datasource:o.ora112
HR.EMP "Region1"."Emp" sysprod@r.mydb
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.
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
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.)
If you did not do so when you installed SharePlex, run the
See the SharePlex Reference Guide for more information about this utility.
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:
Configure SharePlex and the database on the source system as follows.
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.
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.
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:
Datasource:o.SID | ||
src_owner.table |
tgt_owner.table |
host@r.database_name |
where:
* Important! If target owner or table name is defined in the database as anything other than UPPERCASE, be certain to:
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.
Datasource:o.ora112
HR.EMP "Region1"."Emp" sysprod@r.mydb
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.
If you did not do so when you installed SharePlex, run the
© 2021 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy