Chat now with support
Chat with Support

SharePlex 8.6.6 - Installation Guide

About this Guide Conventions used in this guide Introduction to SharePlex SharePlex pre-installation checklist Set up SharePlex in an Oracle cluster Set up SharePlex on Amazon Cloud Download SharePlex UNIX Installation and Setup Windows Installation and Setup Assign SharePlex users to security groups Basic SharePlex demonstrations Advanced SharePlex Demonstrations Solve Installation Problems Uninstall SharePlex SharePlex Utilities Appendix A: Advanced installer options Appendix B: Install SharePlex as root Appendix C: SharePlex installed items

Part 3: Test Replication

Basic SharePlex demonstrations > Part 3: Test Replication

This section demonstrates the speed and accuracy of SharePlex replication using the configuration you built and activated. Before you run the tests, run SQL*Plus on each system, connect to your database, and select all rows from both of the demonstration tables.

On sysA:
SQL> select * from splex.demo_src;
On sysB:
SQL> select * from splex.demo_dest;

These tables were installed empty. If they contain data, TRUNCATE them so that you can start the demonstration from a fresh state.

Test 1: Verify replication startup

This test verifies that replication is proceeding.

  1. Insert and commit a record into the source table (splex.demo_src) by typing:

    SQL> insert into splex.demo_src values (‘JIM’, ‘8001 Irvine Center Drive’, ‘949-754- 8000’);
    SQL> commit;

    SharePlex replicates the changes and posts them to the target table.

  2. Verify that the new record is now in the splex.demo_dest table on the target system by typing the following query:

    SQL> select * from splex.demo_dest;

    You can see that the row you inserted now exists in the target table as well as the source table.

Test 2: Verify speed and replication of large volumes

This test verifies that SharePlex replicates large volumes of data quickly and accurately.

  1. Create a SQL script to insert 500 rows into the splex.demo_src table on the source system, following it with a COMMIT. You can refer to the description on page 93 for the table's description when creating the script.
  2. Run the perf_mon.sh script. This script monitors the speed of the Post process. When it runs, it polls the Post process to determine the number of operations that were processed. It repeats this poll a specified number of times at specified intervals, and then it computes the number of INSERTs, UPDATEs, DELETEs, and COMMITs per second that SharePlex posted during that time. The results are printed to screen. Following are instructions for Unix, Linux, and Windows systems.

    Unix and Linux systems

    Run perf_mon from the util sub-directory of the SharePlex product directory using the following syntax. You can control the frequency and interval of the poll.

    Syntax:

    perf_mon.sh #_polls poll_interval

    where #_polls is the number of times to poll the Post process

    and poll_interval is the time interval between polls

    Example:

    perf_mon.sh 10 60

    In this example, the Post process is polled 10 times at 60-second intervals.

    Windows systems

    Run sp_perf_mon in the Command Prompt from the bin sub-directory of the Share- Plex product directory. Use the following syntax. On Windows systems, the poll occurs every second and continues until you kill it with the control+C command.

    sp_perf_mon -rport_number

    where port_number is the SharePlex port number

    Example:

    sp_perf_mon -r2100

    Note: This script does not work with multiple post queues using the same port.

  3. As you did in Test 1, verify that all rows have been sent to the target system. By the time you run SQL*Plus and generate the query on the target system, the data should already have been posted.

Test 3: Verify queuing when the target is unavailable

This test shows you how SharePlex queues the replicated data on the source system when the target system is unavailable.

  1. Shut down SharePlex on the target system to simulate that this system is unavailable.

    sp_ctrl(sysB)> shutdown
  2. On the source system, INSERT and COMMIT records into splex.demo_src using the script you created for Test 2.
  3. Since the target system was made “unavailable” by shutting down SharePlex, meaning that there is no Import service available on that system to receive data, the records will be queued in the export queue on the source system. You can verify this by issuing the qstatus command, which displays the status of all SharePlex queues on a system.

    sp_ctrl(sysA)> qstatus

    Figure 1 (following) is similar to what you will see when you issue the qstatus command on the source system. Notice that there is no post queue on the source system. This is because the system is only being used as a source system. Normally, the post queue for this configuration would be on a different system, the target system. However, this system also could be a target system if a configuration is active on another system.

    Figure 2: Sample qstatus command issued on the source system.

    Note: Since SharePlex is not running on the target system, you cannot view that system's post queue. Under normal circumstances, when SharePlex is running on both systems, you can use the qstatus command with the [on host] option on the source system to view the post queue instead of running sp_ctrl on the target. (Substitute the target system's name for “host.”)

    sp_ctrl (sysA) > qstatus on host

Test 4: Verify continuation of replication

This test demonstrates how SharePlex resumes processing from its stopping point when the target system becomes available again.

  1. Start sp_cop and sp_ctrl (if they are not already running) on the target system.
  2. To verify that all SharePlex processes are started on the target system, issue the status command.

    sp_ctrl(sysB)> status

    The status command summarizes the SharePlex Status Database, showing you processes that are running and any errors that occurred. The following screen shows a typical status window.

    Figure 3: sample status command issued on the target system

    The status command is one of several commands in sp_ctrl that can display replication status. To get detailed status information, including explanations of errors, you can use the lstatus command.

    sp_ctrl(sysB)> lstatus
  3. When the SharePlex processes start, replication resumes. Run SQL*Plus on the target system and verify that the records inserted by the script on the source system now exist in the target database.

    SQL> select * from splex.demo_dest;

Test 5: Verify SharePlex recovery after interruption to the primary instance

This test shows how SharePlex recovers after an interruption to the primary instance.

  1. Stop the Capture process on the source system to simulate the interruption.

    sp_ctrl(sysA)> stop capture
  2. Use the script you created for Test 2 to INSERT and COMMIT records in splex.demo_src.

  3. Re-start the Capture process on the source system.

    sp_ctrl(sysA)> start capture
  4. Check the status of the SharePlex processes on the source system to verify that Capture has started.

    sp_ctrl(sysA)> status
  5. Run SQL*Plus on the target system and verify that all records inserted by the script on the source system now exist in the target database.

    SQL> select * from splex.demo_dest;

Test 6: Verify replication of the TRUNCATE TABLE command

This test demonstrates replication of the TRUNCATE TABLE command, allowing you to remove data from the source and target tables with one command.

  1. In SQL*Plus, issue the TRUNCATE TABLE command for splex.demo_src on the source system.

    SQL> TRUNCATE TABLE splex.demo_src;
  2. Immediately issue the qstatus command on the source system, then issue it again. You can see that the transactions are moving through the queues on their way to the target system.

    sp_ctrl(sysA)> qstatus
  3. Run SQL*Plus on the target system, and verify that the target table splex.demo_dest is empty.

    SQL> select * from splex.demo_dest;

Test 7: Compare and repair out-of-sync rows

This is the final test in this demo. It demonstrates how you can use the compare command in sp_ctrl to compare source and target tables to verify synchronization . You can then use the repair command to repair rows that are out of synchronization.

  1. On the source system, use the script you created for Test 2 to insert several rows into splex.demo_src.
  2. Verify that all of the data has posted to splex.demo_dest by issuing the qstatus command in sp_ctrl on the target system until the queues in the display are at “0.

    sp_ctrl(sysB)> qstatus
  3. Issue the compare command in sp_ctrl on the source system. sp_ctrl(sysA)> compare splex.demo_src

  4. View the compare results using the compare status command on the source system. This should show that there are no rows out of synchronization.

    sp_ctrl(sysA)> compare status
  5. On the target system, issue the UPDATE command for several rows to change the values in the NAME column in splex.demo_dest. This causes the source and target tables to be out of synchronization.

  6. Issue the compare command on the source system again. This compares the rows in splex.demo_dest against those in splex.demo_src

    sp_ctrl(sysA)> compare splex.demo_src
  7. To repair the rows that are out of synchronization, issue the repair command.

    sp_ctrl(sysA)> repair splex.demo_src
  8. To verify the repair, issue the repair status command on the source system. The status should show that the table has been repaired.

    sp_ctrl(sysA)> repair status
  9. To verify that the repair was accurate, you can manually compare the source and target tables using a SELECT statement to view all rows in both tables.

    SQL> select * from splex.demo_src; (on the source system)
    SQL> select * from splex.demo_dest; (on the target system)

Tip: SharePlex also provides a compare using command to compare all source tables in a configuration to their target tables.

This concludes the Basic SharePlex demonstration.

Advanced SharePlex Demonstrations

Advanced SharePlex demonstrations
Contents

 

This chapter demonstrates selected features of SharePlex. These exercises can be run on Unix and Linux and Windows systems to demonstrate:

  • How to build and verify a replication configuration
  • How to use the compare command to verify synchronization
  • How to use partitioned replication to replicate subsets of data
  • How to use transformation to manipulate replicated data
  • How to use generic conflict resolution in peer-to-peer replication

Important! These demonstrations were created specifically for demonstration purposes and are intended to introduce you to SharePlex. Do not deploy these in a production environment or use them as the basis for establishing replication. To properly implement replication, use the SharePlex Administrator’s Guide. It is recommended that you run these demonstrations in the order that they appear in this manual.

Tip: The commands used in these demonstrations are explained in the SharePlex Reference Manual.

Install the Demonstration Objects

Advanced SharePlex Demonstrations > Install the Demonstration Objects

SharePlex provides several SQL scripts, including p2p.sql and od.sql that install all of the objects that you need for these demonstrations. These scripts are located in the util sub-directory of the SharePlex product directory. Run them on the source and target systems that you will be using for the demonstrations. Run the scripts in SQL*Plus as an existing user with the DBA role and SELECT ANY TABLE privileges.

You will be prompted 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. This requires entering the schema name for those objects, and the script will remove them.
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

Demo1: Initial Demonstration

Advanced SharePlex Demonstrations > Demo1: Initial Demonstration

This demonstration highlights the compare command, and provides a simple demonstration of performance.

Start SharePlex

The following are instructions for starting SharePlex on Unix, Linux, and Windows systems. Start SharePlex on the source and target systems.

Unix and Linux systems

1 Log onto the system as a SharePlex Administrator (a member of the SharePlex Admin group).

From the bin sub-directory of the SharePlex product directory (the one containing the binaries, represented by the productdir variable in the following syntax), run sp_cop and sp_ctrl.

$ cd /productdir/bin
 
$ ./sp_cop &
 
$ . /sp_ctrl

Windows systems

  1. Log onto the system as a SharePlex Administrator (a member of the SharePlex Admin group).
  2. On the Windows desktop, double-click the SpUtils shortcut to open the SharePlex Utilities dialog box.
  3. Click the SharePlex Services tab to display the SharePlex Services dialog box.
  4. In the Port list box of the SharePlex Services dialog box, select the SharePlex port number, then click Start.
  5. When the Current State text box shows that the service has started, click Close to close the dialog box.
  6. On the Windows desktop, double-click the sp_ctrl shortcut to open the sp_ctrl command prompt

Create the configuration

In sp_ctrl, create a replication configuration named od.config that replicates the od_department, od_salary, od_timesheet, and od_employee tables on the source system to the same tables on the target system.

To create the configuration, use the create config command in sp_ctrl. This command opens a new file in the default text editor for SharePlex, which is either vi (Unix and Linux) or WordPad (Windows).

sp_ctrl(sysA)> create config od.config

Using the editing commands in the text editor, follow Template 1 below to create the configuration, making the following substitutions.

  • For source_SID on line 1, use the ORACLE_SID of the source database. The SID is case-sensitive.
  • For owner, substitute the owner of the object. The object in column 1 is the source object, and the object in column 2 is the target object.
  • For target_host, substitute the target machine’s name.
  • For target_SID, substitute the ORACLE_SID of the target database. The SID is casesensitive.

Do not allow any spaces between characters within a column. Separate columns with two or more spaces or a tab.

 

Template 1: Demonstration configuration “od.config”

datasource:o.source_SID  

 

owner.od_department owner.od_department target_host@o.target_SID
owner.od_salary owner.od_salary target_host@o.target_SID
owner.od_timesheet owner.od_timesheet target_host@o.target_SID
owner.od_employee owner.od_employee target_host@o.target_SID

 

When you are finished making the configuration entries, save the file using either the :wq command (in vi) or File>Save (WordPad). SharePlex automatically saves the file in the config sub-directory.

Important! Retain this configuration file on your system, since it will be needed for another demonstration.

Activate the configuration

You will use the activate command in sp_ctrl to activate the configuration. Prior to activating the configuration this command validates the database ORACLE_SID, and object names using the source system.

  1. Make certain the SharePlex demonstration objects are installed. See Install the demonstration objects.
  2. The target system contains an ON DELETE CASCADE constraint on the od_salary table. The constraint’s name is od_sal_emp_no_fk. You can either disable the constraint on the target table or you can configure SharePlex to handle the cascaded deletes (Post detects the ON DELETE CASCADE dependencies and suppresses the posting of any replicated cascaded deletes to the child tables).

    To configure SharePlex to handle the constraints:

    Set the following parameters:

    • SP_OPO_DEPENDENCY_CHECK parameter to 2
    • SP_OCT_REDUCED_KEY parameter to 0
    • SP_OPO_REDUCED_KEY parameter to 0 or 1

    To disable the constraints through SQL*Plus:

    SQL> alter table od_salary disable constraint od_sal_emp_no_fk;

  3. The target system also contains a trigger on the od_timesheet table. The trigger’s name is od_timesheet_mod. Since triggers on target tables cause SharePlex to return out-of-sync errors, disable this trigger on the target table only.

    SQL> alter trigger od_timesheet_mod disable;

  4. In sp_ctrl, issue the activate config command for the od.config configuration.

    sp_ctrl(sysA)> activate config od.config

Verify synchronization

This demonstrates the compare command. This command compares the source and target tables to ensure their synchronization. The repair command can then be used to resynchronize out-of-sync rows that are found.

  1. Make certain the SharePlex demonstration objects are installed. See Install the demonstration objects.
  2. Populate the od_employee and od_salary tables on the source system by executing the od_add_emps procedure that was installed in the schema where you installed the demonstration objects. This procedure has one IN parameter that specifies the number of employees to insert per department. The default number of departments is 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);
  3. In sp_ctrl on the source system, issue the compare command once for the od_employee table and once for the od_salary table. Substitute the tables’ owners, the target system’s name, and the target ORACLE_SID for the appropriate variables in the following syntax.

    sp_ctrl(sysA)> compare source_owner.od_employee
    sp_ctrl(sysA)> compare source_owner.od_salary
  4. In sp_ctrl on the source system, issue the compare status command. This displays the progress of the comparisons. Continue issuing this command until both compare processes have completed.

    sp_ctrl> compare status
  5. Issue the compare status command on the source system to view the results of the comparisons.

    sp_ctrl(sysA)> compare status

    The results should show no out-of-sync rows.

  6. Delete some rows from od_employee on the target system. This causes that table to go out of synchronization with its source table.

  7. Issue the repair command on the source system again for the od_employee table. This repairs the out-of-sync condition.

    sp_ctrl(sysA)> repair source_owner.od_employee

  8. Issue the repair status command on the source system. This should show no out-ofsync rows, since the repair command added the missing rows to the target od_employee table.

    sp_ctrl(sysA)> repair status

View performance statistics

This demonstration allows you to view performance statistics for SharePlex replication.

  1. Make certain the SharePlex demonstration objects are installed. See Install the demonstration objects.
  2. Run the od_add_emps procedure on the source system using an IN value of 2000. This adds 10,000 rows assuming you made no changes to the od_department table.
  3. Run the perf_mon.sh script. This script monitors the speed of the Post process. When it runs, it polls the Post process to determine the number of operations that were processed. It repeats this poll a specified number of times at specified intervals, and then it computes the number of INSERTs, UPDATEs, DELETEs, and COMMITs per second that SharePlex posted during that time. The results are printed to screen. Following are instructions for Unix, Linux, and Windows systems.

    Unix and Linux systems

    Run perf_mon from the util sub-directory of the SharePlex product directory using the following syntax. You can control the frequency and interval of the poll.

    Syntax:

    perf_mon.sh #_polls poll_interval

    where #_polls is the number of times to poll the Post process

    and poll_interval is the time interval between polls

    Example:

    perf_mon.sh 10 5

    In this example, the Post process is polled 10 times at 5-second intervals.

    Windows systems

    Run sp_perf_mon in the Command Prompt from the bin sub-directory of the Share- Plex product directory. Use the following syntax. On Windows systems, the poll occurs every second and continues until you kill it with the control+C command.

    sp_ perf_mon -rport_number

    where port_number is the SharePlex port number

    Example:

    sp_perf_mon -r2100

Note: This script does not work with multiple post queues using the same port.

Related Documents