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.
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.
Run sp_ctrl .
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
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:
In sp_ctrl, verify that the configuration file will activate successfully.
sp_ctrl(source)>verify config sample_config
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.
Activate the configuration.
sp_ctrl(source)>activate config od_config
Note: Configuration names are case-sensitive.
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.
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:
SQL> exec od_add_emps(100);
In this section, you will compare the source and target tables to verify that the data is synchronized.
On the source, run sp_ctrl.
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.
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.
In this section, you will repair an out-of-sync condition to restore data synchronization.
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.
On the source, compare the od_employee tables.
sp_ctrl(source)> compare demo.od_employee
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.
On the source, repair the od_employee table.
sp_ctrl(source)>repair demo.od_employee
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.
Note: Before proceeding, make certain the SharePlex demonstration objects are installed. See Prework for the demonstrations.
In this demonstration you will:
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.
Note: In this demonstration, the demonstration objects are assumed to be in the schema named demo. Substitute the actual schema, if different.
If you ran previous demonstrations, do the following:
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
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;
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:
Perform these steps on the source system.
In sp_ctrl, open the od_configconfiguration file for editing.
sp_ctrl(source)>edit config od_config
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:
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.
Activate the configuration.
sp_ctrl(source)>activate config od_config
Note: Configuration names are case-sensitive.
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
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:
SQL>exec od_add_emps(100);
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.
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.
Note: Before proceeding, make certain the SharePlex demonstration objects are installed. See Prework for the demonstrations.
In this demonstration you will :
Specify a column partition in the configuration file. A column partition replicates only the data changes that are made to the specified columns.
Note: In this demonstration, the demonstration objects are assumed to be in the schema named demo. Substitute the actual schema, if different.
If you ran previous demonstrations, do the following:
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
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;
Perform these steps on the source system.
In sp_ctrl, deactivate the od_config configuration.
sp_ctrl(source)>deactivate config od_config
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
In sp_ctrl, open the od_config configuration file for editing.
sp_ctrl(source)>edit config od_config
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:
(EMP_NO, EMP_FIRST_NAME, EMP_LAST_NAME) is the syntax for a column partition. Only the listed columns are replicated.
NoteS:
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.
Activate the configuration.
sp_ctrl(source)>activate config od_config
Note: Configuration names are case-sensitive.
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
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:
SQL>exec od_add_emps(100);
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.
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.
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.
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.
Note: In this demonstration, the demonstration objects are assumed to be in the schema named demo. Substitute the actual schema, if different.
If you ran previous demonstrations, do the following:
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
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;
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
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:
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.
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:
On the target, enable the following parameter.
sp_ctrl(target)> set param SP_OPO_XFORM_EXCLUDE_ROWID 1
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
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 |
On the source, activate the configuration.
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
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);
In SQL*Plus, select all rows from od_sales_emp_data.
View the transformed data. You should see the following results:
© ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
