Chat now with support
Chat with Support

SharePlex 11.0 - SharePlex Installation and Setup Guide

About this Guide Conventions used in this guide Revision History SharePlex pre-installation checklist Download the SharePlex installer Installation and setup for Oracle cluster Installation and setup for remote capture Install SharePlex on Linux and UNIX Assign SharePlex users to security groups Set up an Oracle environment for replication Set up replication from Oracle to a different target type Generic SharePlex demonstration-all platforms Advanced SharePlex demonstrations for Oracle Advanced SharePlex Demonstrations for PostgreSQL Solve Installation Problems Database Setup Utilities General SharePlex Utilities Uninstall SharePlex Advanced installer options Install SharePlex as root Run the installer in unattended mode SharePlex installed items

Prework for the demonstrations

Prework for the Demonstrations

Perform these tasks before you start the demonstrations.

Verify that DDL replication is enabled

On the source, verify that the SP_OCT_REPLICATE_DDL parameter is set to the default of 3. This parameter controls basic DDL replication, including the TRUNCATE command.

sp_ctrl(source)>list param modified capture

The SP_OCT_REPLICATE_DDL parameter should not be listed in the output. If it is, issue the following command:

sp_ctrl(source)>reset param SP_OCT_REPLICATE_DDL

Install the demonstration objects

The p2p.sql and od.sql scripts install the demonstration objects that you will use during these demonstrations. These scripts are stored in the util sub-directory of the SharePlex product directory.

To run the scripts:

  • Run od.sql first, then run p2p.sql.
  • Run the scripts on the source and target systems that you will be using for the demonstrations.
  • Run them in SQL*Plus as an existing user with the DBA role and SELECT ANY TABLE privileges.

The scripts prompt for the following items:

  • The schema where you want the demonstration objects to be installed.
  • The tablespace for the demonstration objects.
  • Whether or not you want old demonstration objects from a previous version of SharePlex to be removed. To remove the old objects, supply the name of their schema.
Description of the demonstration objects
od_employee    

Name

----------------------------

Null?

---------------

Type

-------------

EMP_NO NOT NULL NUMBER
EMP_FIRST_NAME   VARCHAR2
EMP_LAST_NAME   VARCHAR2
EMP_DOB   DATE
EMP_DEPT_NO   NUMBER
EMP_TIMESTAMP   DATE

 

od_timesheet    

Name

----------------------------

Null?

---------------

Type

-------------

TS_EMP_NO   NUMBER
TS_IN_TIME   DATE
TS_OUT_TIME   DATE
TS_MOD_TIME   DATE

 

od_department    

Name

---------------------------

Null?

---------------

Type

-------------

DEPT_NO NOT NULL NUMBER
DEPT_NAME   VARCHAR2
DEPT_CODE   VARCHAR2

 

od_salary    

Name

---------------------------

Null?

---------------

Type

-------------

SALE_EMP_NO   NUMBER
SAL_VALUE   NUMBER
SAL_CHANGED   DATE

 

od_sales_emp_data    

Name

------------------------------

Null?

---------------

Type

-------------

EMP_NO_KEY NOT NULL NUMBER
EMPLOYEE_NAME   VARCHAR2 (70)
SALARY   NUMBER
DEPARTMENT   VARCHAR2 (50)

 

oxc_table    

Name

-----------------------------

Null?

--------------

Type

-------------

EXC_NO NOT NULL NUMBER
EXC_TYPE   VARCHAR2 (6)
EXC_TARGET_TABLE   VARCHAR2 (66)
EXC_FIXED   VARCHAR2 (3)
EXC_INFO   VARCHAR2 (500)
EXC_TIMESTAMP   DATE

Start SharePlex

Start SharePlex

The following are instructions for starting SharePlex and the sp_ctrl command-line interface on Unix and Linux. Start SharePlex on the source and target systems.

To start SharePlex on Unix and Linux systems:

  1. Log onto the system as a SharePlex Administrator (a member of the SharePlex Admin group).
  2. From the bin sub-directory of the SharePlex product directory ( productdir in the following syntax), run sp_cop and sp_ctrl.

    $cd /productdir/bin

    $./sp_cop &

    $. /sp_ctrl

Configure, activate, and verify replication

Configure, Activate, and Verify Replication on the Oracle Source System

Note: Before proceeding, make certain the SharePlex demonstration objects are installed. See Prework for the Demonstrations .

In this demonstration, you will create and activate a replication configuration, load data to the source table, and then confirm that the data was replicated successfully to the target table. You will also repair a table that is out of synchronization.

Create a configuration file

Note: In this demonstration, the demonstration objects are assumed to be in the schema named demo. Substitute the actual schema, if different.

Perform these steps on the source system:

  1. Run sp_ctrl .

  2. Issue the following command to create a configuration file named od_config that replicates the od_department, od_salary, od_timesheet, and od_employee tables to target tables of the same names on the target system.

    sp_ctrl(source)>create config od_config

  3. In the text editor, build your configuration file based on the following template. Allow no spaces between the characters in a component (source specification, target specification, routing map), and place at least one space between each component.

    # od_config configuration file

    datasource:o.source_SID

    demo.od_department

    demo.od_department

    target_system@o.target_SID

    demo.od_salary demo.od_salary

    target_system@o.target_SID

    demo.od_timesheet demo.od_timesheet target_system@o.target_SID
    demo.od_employee demo.od_employee target_system@o.target_SID

    where:

    • # denotes a comment.
    • source_SID is the ORACLE_SID of the source database.
    • target_system is the name or IP address of the target system.
    • target_SID is the ORACLE_SID of the target database.
  4. Save the file, then exit the editor. SharePlex automatically saves the file in the config sub-directory.
  5. In sp_ctrl, verify that the configuration file will activate successfully.

    sp_ctrl(source)>verify config sample_config

Activate the configuration

Perform these steps on the source system. When you activate a configuration, SharePlex is ready to capture transactional changes that are made to the specified source data.

Perform the following steps to activate the configuration:

  1. Activate the configuration.

    sp_ctrl(source)>activate config od_config

    Note: Configuration names are case-sensitive.

  2. Confirm that the configuration activated successfully. The name config od_config should appear under File Name, and the word Active should appear under State.

    sp_ctrl(source)>list config

Note: If you ran the basic SharePlex demonstration using the same datasource , this activation deactivates the configuration file that you used for that demonstration.

Generate transactions to the source table

On the source, log in as the demo schema owner and execute the od_add_emps procedure to populate the od_employee and od_salary tables.

This procedure has one IN parameter that specifies the number of employees to insert per department:

  • The default number of departments is 5.
  • Use an IN parameter of 100 to create 500 new employees in the od_employee table and 500 entries in the od_salary table.

SQL> exec od_add_emps(100);

Compare source and target tables

In this section, you will compare the source and target tables to verify that the data is synchronized.

  1. On the source, run sp_ctrl.

  2. On the source, compare the od_employee and od_salary tables to their targets.

    sp_ctrl(source)> compare demo.od_employee

    sp_ctrl(source)> compare demo.od_salary

    Note: The command determines the correct target table from the configuration file.

  3. On the source, view the results of the comparisons.

    sp_ctrl(source)> compare status

    Continue issuing this command until both compare processes have completed their work. The command output should show no out-of-sync rows.

Repair an out-of-sync condition

In this section, you will repair an out-of-sync condition to restore data synchronization.

Perform the following steps to repair an out-of-sync condition:

  1. On the target, run SQL*Plus and delete some rows from od_employee. This causes that table to go out of synchronization with its source table.

  2. On the source, compare the od_employee tables.

    sp_ctrl(source)> compare demo.od_employee

  3. On the source, view the results of the comparisons.

    sp_ctrl(source)>compare status

    The command output should show out-of-sync rows, the number of which should equal the number of rows you deleted from od_employee on the target.

  4. On the source, repair the od_employee table.

    sp_ctrl(source)>repair demo.od_employee

  5. On the source, view the results of the repair.

    sp_ctrl(source)>repair status

    The command output should show no out-of-sync rows, because the repair command inserted the rows that that you deleted, because they were still present in the source table.

Demonstration of horizontally partitioned replication

Note: Before proceeding, make certain the SharePlex demonstration objects are installed. See Prework for the Demonstrations .

In this demonstration you will:

  1. Define a partition scheme and one or more row partitions for the partition scheme.

    • A row partition is a defined subset of the rows in a source table that you want to replicate to the target table.

    • A partition scheme is a logical container for row partitions.

  2. Specify the name of the partition scheme in the SharePlex configuration file to include the partitions in replication.
  3. Activate the configuration.
  4. Load data into the source table and verify replication of the specified rows to the target.

Prepare the tables

Note: In this demonstration, the demonstration objects are assumed to be in the schema named demo. Substitute the actual schema, if different.

Perform the following steps to prepare the table:

  1. If you ran previous demonstrations, do the following:

    1. On the source and target, run sp_ctrl and issue the following command to shut down sp_cop.

      sp_ctrl (source) shutdown

      sp_ctrl (target) shutdown

    2. On the source and target, run the ora_cleansp utility according to the instructions in "ora_cleansp" in the SharePlex Reference Guide. This removes the queues from the previous demonstrations and deactivates the previous configuration.
  2. On the source and target, TRUNCATE the od_employee and od_salary tables.

    SQL> truncate table demo.od_employee;

    SQL> truncate table demo.od_salary;

Configure the row partition

Perform the following steps to configure the row partition:

  1. On the source, run sp_ctrl.
  2. On the source, create a row partition that specifies the rows to replicate from the source od_employee table, in this case only the rows where the employee department is 1.

    Note: The partition only specifies rows. The names of the source and target tables are specified in the configuration file.

    sp_ctrl> add partition to scheme1 set condition = 'EMP_DEPT_NO=1' and route = target_sys@o.ora_SID

    where:

    • scheme1 is the name of the partition scheme.
    • condition = 'EMP_DEPT_NO=1' is the column condition
    • route = target_sys@o.ora_SID is the routing that consists of the name of your target system and the ORACLE_SID of the target database.

Specify the partition scheme in the configuration file

Perform these steps on the source system.

  1. In sp_ctrl, open the od_configconfiguration file for editing.

    sp_ctrl(source)>edit config od_config

  2. In the text editor, edit the configuration file to change the entry for the od_employee table so that it uses the partition scheme that you created.

    # od_config configuration file

    datasource:o.source_SID

    demo.od_department

    demo.od_department

    target_system@o.target_SID

    demo.od_salary demo.od_salary

    target_system@o.target_SID

    demo.od_timesheet demo.od_timesheet target_system@o.target_SID
    demo.od_employee demo.od_employee !scheme1

    Where:

    • source_SID is the ORACLE_SID of the source database.
    • target_system is the name or IP address of the target system.
    • target_SID is the ORACLE_SID of the target database.
    • !scheme1 is the syntax for listing the partition scheme.
  3. Save the file, then exit the editor. SharePlex automatically saves the file in the config sub-directory.

Activate the configuration

Perform the following steps to activate the configuration:

Perform these steps on the source system. When you activate a configuration, SharePlex is ready to capture transactional changes that are made to the specified source data.

  1. Activate the configuration.

    sp_ctrl(source)>activate config od_config

    Note: Configuration names are case-sensitive.

  2. Confirm that the configuration activated successfully. The name config od_config should appear under File Name, and the word Active should appear under State.

    sp_ctrl(source)>list config

Replicate data

Perform the following steps to replicate data:

  1. On the source, log in as the demo schema owner and execute the od_add_emps procedure to populate the od_employee and od_salary tables. This procedure has one IN parameter that specifies the number of employees to insert per department:

    • The default number of departments is 5.
    • Use an IN parameter of 100 to create 500 new employees in the od_employee table and 500 entries in the od_salary table.

    SQL>exec od_add_emps(100);

  2. On the source, select all rows from the source od_employee table.

    SQL> select * from od_employee;

    The value for the EMP_DEPT_NO column should range from '1' to '5' across the rows.

  3. On the target, select all rows from the target od_employee table.

    SQL> select * from od_employee;

    The value for the EMP_DEPT_NO column should be '1' for all rows. Rows where the value for this column is a value other than “1” were not replicated.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating