SharePlex 10.1.1 - Installation and Setup for Oracle Source

Configure, activate, and verify replication

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.

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.

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.

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

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

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.

Demonstration of vertically partitioned replication

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

In this demonstration you will :

1. Specify a column partition in the configuration file. A column partition replicates only the data changes that are made to the specified columns.

2. Activate the configuration.
3. Load data into the source table and verify replication of the specified columns 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.

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 column partition

Perform these steps on the source system.

1. In sp_ctrl, deactivate the od_config configuration.

   sp_ctrl(source)>deactivate config od_config

2. Confirm that the configuration deactivated successfully. The name od_config should appear under File Name, and the State column should show that it is inactive.

   sp_ctrl(source)>list config

3. In sp_ctrl, open the od_config configuration file for editing.

   sp_ctrl(source)>edit config od_config

4. In the text editor, edit the configuration file to change the entry for the od_employee table so that it uses a column partition.

   # od_config configuration file datasource:o.source_SID
   demo.od_department	demo.od_department	target_system@o.target_SID
   demo.od_salary !(SAL_VALUE)	demo.od_salary	target_system@o.target_SID
   demo.od_timesheet	demo.od_timesheet	target_system@o.target_SID
   demo.od_employee (EMP_NO, EMP_FIRST_NAME, EMP_LAST_NAME	demo.od_employee	target_system@o.target_SID

   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.
   • !(SAL_VALUE) is the syntax for an excluded column partition. All columns except the one listed are replicated.

   • (EMP_NO, EMP_FIRST_NAME, EMP_LAST_NAME) is the syntax for a column partition. Only the listed columns are replicated.

     NoteS: 

     • This configuration file template is set up in table form to show the source, target, and routing elements clearly. In a real configuration file, the source (including the column partition), target, and routing map should be in that order, all on one line.
     • Any columns that are defined as NOT NULL must be included in the column partition, because SharePlex replicates a NULL into columns that are not in the column partition.
5. Save the file, then exit the editor. SharePlex automatically saves the file in the config sub-directory.

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

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;

   There should be values in all of the columns of the table.

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

   SQL> select * from od_employee;

   There should only be values in the EMP_NO, EMP_FIRST_NAME, and EMP_LAST_NAME columns. The other columns should contain null values.

4. On the target, select all rows from the target od_salary table.

   SQL> select * from od_salary;

   There should only be values in the SALE_EMP_NO and SAL_CHANGED columns. The SAL_VALUE column should contain only nulls.

Demonstration of transformation

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

In this demonstration, you will use a supplied transformation procedure to have SharePlex replicate data from two separate source tables and apply it to one target table.

Prepare the objects

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

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

3. On the target, grant the user who owns the demonstration objects the system privilege to execute the sp_cr package, which was installed in the SharePlex schema when SharePlex was first installed.

   SQL> grant execute on sp_cr to user_name

4. On the target, log into SQL*Plus as the user who owns the SharePlex demonstration objects, then run the transform.sql script from the util sub-directory of the SharePlex product directory. This installs transformation routines named od_transform_employee_insert and od_transform_employee_update. You are prompted for:

   • a schema and tablespace
   • the name of the SharePlex database user

Configure SharePlex

1. On the target, open the transformation.SID file (where SID is the ORACLE_SID of the target database) in a text editor. This file is located in the data sub-directory in the SharePlex variable-data directory.

   Note: Post checks this file to determine if there is a transformation procedure that it must call instead of posting the operation to the database.

2. Create the following entries in the transformation.SID file.

   Separate each column with at least a few spaces or a tab character.

   demo.od_employee	I	demo.od_transform_employee_insert
   demo.od_employee	U	demo.od_transform_employee_update
   demo.od_salary	I	demo.od_transform_employee_insert
   demo.od_salary	U	demo.od_transform_employee_update

   Note: The components of each entry are as follows, in the order they must appear:

   • The target table to which a transformation procedure is assigned.
   • The operation type for which the specified transformation procedure will be called.
   • The name of the assigned transformation procedure to use. Multiple entries can be used to assign different procedures to different operation types for the same table.

3. On the target, enable the following parameter.

   sp_ctrl(target)> set param SP_OPO_XFORM_EXCLUDE_ROWID 1

4. On the source, create a configuration file named od.transform that replicates the od_salary and od_employee tables.

   sp_ctrl(source)> create config od_transform

5. In the text editor, build your configuration file based on the following template.

   datasource:o.source_SID
   demo.od_salary	demo.od_salary	target_system@o.target_SID
   demo.od_employee	demo.od_employee	target_system@o.target_SID

6. Save the file, then exit the editor. SharePlex automatically saves the file in the config sub-directory.

Activate and start replication

1. On the source, activate the configuration.

   sp_ctrl(source)> activate config od_transform

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

   sp_ctrl(source)>list config

3. On the source, log in as the demo schema owner, then execute the od_add_emps procedure to populate the od_employee and od_salary tables. Use an IN parameter of 10 to create 50 new employees in the od_sales_emp_data table.

   SQL> exec od_add_emps(10);

View the transformed data

1. On the target, run SQL*Plus.

2. In SQL*Plus, select all rows from od_sales_emp_data.

3. View the transformed data. You should see the following results:

   • The EMPLOYEE_NAME column contains the first and last name of the employee. Compare this to the source od_employee table, where the first and last names are in separate columns.
   • The DEPARTMENT column contains the department name. Compare this to the source od_employee table, where the EMP_DEPT_NO column contains a number. The transformation procedure transformed the replicated department number into the department name by referencing the od_department table.
   • The SALARY column contains the salary from the od_salary table.
4. [OPTIONAL] To see how transformation works for UPDATEs, you can update the od_employee table manually. The od_transform_employee_update procedure will make the transformation. To further this demonstration, you may construct a transformation procedure for DELETEs.