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.
Perform the following steps to prepare the tables:
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 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.
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
Perform the following steps to replicate data:
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.
Perform the following steps to prepare the objects:
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:
Perform the following steps to configure SharePlex:
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 |
Perform the following steps to activate and start replication:
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);
Perform the following steps to view the transformed data:
In SQL*Plus, select all rows from od_sales_emp_data.
View the transformed data. You should see the following results:
Note: Before proceeding, make certain the SharePlex demonstration objects are installed. See Prework for the Demonstrations .
In this demonstration, you will configure SharePlex to use generic conflict resolution procedures to resolve a replication conflict. Generic conflict resolution allows you to use one PL/SQL procedure to resolve conflicts for multiple tables.
The following conflict-resolution strategies are demonstrated:
IMPORTANT! Peer-to-peer replication is not compatible with all business applications. When suitable for an environment, it requires careful analysis and execution, including the creation of custom conflict resolution procedures that are typically more complex than those in this demonstration. Do not use this demonstration as the foundation of a production peer-to-peer deployment. For more information about peer-to-peer replication, see SharePlex Administration Guide.
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 both systems (for both databases).
Shut down SharePlex.
sp_ctrl(peer1)> shutdown
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
Run the p2p.sql script from the util sub-directory of the SharePlex product directory. This installs the od_employee_gen demonstration conflict resolution routine.
You are prompted for the following:
Perform the following steps to configure SharePlex:
On each system, open the conflict_resolution.SID file (where SID is the ORACLE_SID of the local database) in a text editor. This file is located in the data sub-directory of the SharePlex variable-data directory.
Note: Post checks this file when there is a replication conflict to determine if there is a resolution procedure to call.
On each system, create the following entries in the conflict_resolution.SID file. Separate each column with at least a few spaces or a tab character.
demo.od_employee | IUD | demo.od_employee_gen |
Note: The first component is a table, the second specifies the operation types for which a resolution routine will be called if there is a conflict on that table, and the third is the name of the resolution routine that will be used.
On peer1 (the trusted source), create a configuration file named od_peer1 that replicates the od_employee table to the od_employee table on peer2.
sp_ctrl(peer1)> create config od_peer1
demo.od_employee | demo.od_employee | peer2@o.SID |
On peer2 (the secondary source), create a configuration file named od.peer2 that replicates the od_employee table to the od_employee table on peer1.
sp_ctrl(peer2)> create config od_peer2
demo.od_employee | demo.od_employee | peer1@o.SID |
NOTE: In order for post to detect out-of-sync inserts where all columns are identical, set SP_OPO_SUPPRESSED_OOS to 0. Issue this command from sp_ctrl: set param SP_OPO_SUPPRESSED_OOS 0 and verify the parameter is set by using the list param modified command.
Perform the following steps to activate and start replication:
On peer1, activate the od_peer1 configuration.
On peer2, activate the od_peer2 configuration.
On each system, confirm that the configuration activated successfully. The name od_peer1 or od_peer2 (depending on the system) should appear under File Name, and the word Active should appear under State.
sp_ctrl(source)>list config
In this demonstration, an INSERT that originates on peer1 will override a conflicting INSERT that is replicated from peer2.
On peer2, insert a row into od_employee but do not issue a COMMIT.
SQL (peer2) > INSERT INTO OD_EMPLOYEE VALUES (1,'John','Doe',to_date('04/01/1949','MM/DD/RRRR'),1,to_date('01/01/2017','MM/DD/RRRR'));
On peer1, insert the same row (same values) but do not issue a COMMIT.
SQL (peer1) > INSERT INTO OD_EMPLOYEE VALUES (1,'John','Doe',to_date('04/01/1949','MM/DD/RRRR'),1,to_date('01/01/2017','MM/DD/RRRR'));
In this demonstration, whichever row was updated LAST takes priority when there is a conflict.
Note: The EMP_TIMESTAMP field, along with the other columns, must be modified to a non-null value in order for timestamp priority to function. The stored procedure won't be able to resolve the conflict if the timestamp column in the UPDATE statement is not being updated or is being modified to a null value. Out-of-sync problems will then be seen.
On peer1, UPDATE the EMP_FIRST_NAME and EMP_TIMESTAMP columns of the od_employee table as follows.
SQL (peer1) > UPDATE OD_EMPLOYEE SET EMP_FIRST_NAME = 'James', EMP_TIMESTAMP = to_date('01/01/2017','MM/DD/RRRR') WHERE EMP_NO = 1;
On peer2, UPDATE the same column using a different update values. but the same key value.
SQL (peer2) > UPDATE OD_EMPLOYEE SET EMP_FIRST_NAME = 'Harry', EMP_TIMESTAMP = to_date('02/02/2017','MM/DD/RRRR') WHERE EMP_NO = 1;
On both systems, issue COMMITs at the same time.
On both systems, view the post queue to make sure the update operation is in the queue. You will see a message in each queue.
sp_ctrl(peer1)>qstatus
sp_ctrl(peer2)>qstatus
A table named exc_table was installed in the schema that you specified when you installed the demonstration objects. You can view it through SQL*Plus to view information about each conflict. The following is the table description.
Column | Description |
---|---|
EXC_NO | The exception number of the conflict. |
EXC_TYPE | The type of SQL statement, whether INSERT, UPDATE or DELETE. |
EXC_TARGET_TABLE | The table on which the conflict occurred. |
EXC_FIXED |
The results of the conflict resolution routine. YES means that the routine was successful. NO means that the routine failed and the row needs to be manually changed to the correct value. |
EXC_INFO | The cause of the conflict. |
EXC_TIMESTAMP | The time that the conflict occurred on this machine. |
This demonstration shows the default DDL replication support that is enabled when SharePlex is installed. You can enable other DDL replication with parameters, as needed.
Note: This demonstration supports Oracle source and targets only.
On the source, verify that the SP_OCT_REPLICATE_DDL parameter is set to the default of 3. This parameter controls basic DDL replication.
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
Perform the following steps to test DDL replication:
On the source, TRUNCATE splex.demo_src to make certain it is empty.
SQL> truncate table splex.demo_src;
SharePlex replicates the TRUNCATE command to the target.
On the source, add a column to splex.demo_src.
SQL> alter table splex.demo_src add (department varchar2(30) not null default 'unknown');
On the target, describe the splex.demo_dest table.
SQL> describe splex.demo_src;
The table should now contain four columns, including the new department column.
On the source, insert a row into splex.demo_src.
SQL> insert into splex.demo_src values (‘Jane’, ‘1 Oak Street’, ‘123-123-1234’, sales);
SQL> commit;
On the target, select the new row from splex.demo_dest.
SQL> select * from splex.demo_dest where department = 'sales';
The query should return one record, the one you inserted where the department name is sales.
On the source, drop the department column.
SQL> alter table splex.demo_src drop column department;
On the target, describe the splex.demo_dest table.
SQL> describe splex.demo_src;
The table should now contain only the original three columns.
On the source, insert an invalid row into splex.demo_src.
SQL> insert into splex.demo_src values (‘Tom’, ‘2 State Street’, ‘555-444-3333’, accounting);
SQL> commit;
The target should return "ORA-00913: too many values" indicating that the department column was dropped.
On the source, a valid row into splex.demo_src.
SQL> insert into splex.demo_src values (‘Mary’, ‘3 Elm Street’, ‘555-555-5555’);
SQL> commit;
On the target, select the new row from splex.demo_dest.
SQL> select * from splex.demo_dest where name = 'Mary';
The query should return the record, which was replicated successfully.
On the source, TRUNCATE Tsplex.demo_src.
SQL> TRUNCATE TABLE splex.demo_src;
On the target, verify that splex.demo_dest is empty.
SQL> select * from splex.demo_dest;
© ALL RIGHTS RESERVED. 利用規約 プライバシー Cookie Preference Center