SharePlex 9.0 - Installation Guide

Part 2: Create and activate a configuration

SharePlex gets its replication instructions from “configurations,” which are user-defined specifications that tell SharePlex what to do. For each group of objects that you want to replicate, you create a configuration file. Configurations reside on the source system and define:

• The datasource (source database) — the ORACLE_SID of the Oracle database on the source system that contains the data to be replicated.
• The source objects — the names of the objects within the source database that contain the data to be replicated. You can replicate some or all of the tables and sequences within a database.
• The target objects — the names of the objects in the database on the target system that will receive the replicated data.
• The routing map — the route for transporting the data. This includes the target system( s), any intermediary systems, and the target database’s ORACLE_SID. (An intermediary system is not used in this demonstration.)

To create the demonstration configuration

1. Create a replication configuration named sample_config by issuing the create config command in sp_ctrl on the source system. This opens the default text editor, which is vi for Unix and Linux systems and WordPad for Windows systems.

   sp_ctrl(sysA)> create config sample_config

   Refer to Template 1 below as you construct your configuration.

   Template 1: Basic demonstration configuration sample_config

   datasource:o.source_SID
   splex.demo_src	splex.demo_dest	targetsys@o.target_SID

2. On the first non-commented line of the file, type the following, leaving no space between any of the items.

   datasource:o.source_SID

   (Substitute the ORACLE_SID of the source instance for source_SID.) This tells SharePlex where to find the table whose data will be replicated. The o. tells Share- Plex that Oracle data is being replicated.

3. On the next line, enter the owner name (splex) and table name (demo_src) of the source table, separating the two items with a dot (.) but no spaces. Using the owner’s name with a table name ensures that SharePlex replicates the correct table, since different tables in different schemas in a database could have the same name.

   splex.demo_src
4. Type at least a few spaces or a tab to create a second column. Do not press Enter.

5. In the second column, enter the owner name (splex) and table name (demo_dest) of the target table, separating the two items with a dot (.) but no spaces.

   splex.demo_dest

6. Type a few spaces or a tab to create a third column. Do not press Enter.

7. In the third column, type the following items with no space between them. This creates the routing map for your configuration, telling SharePlex where to put the replicated data.

   • the name of the target system
   • the @ symbol
   • the letter o
   • a dot (.)
   • the target instance SID

   Example:

   sysB@o.oraB

8. Save the file and exit the editor. This returns you to the sp_ctrl prompt.

9. [OPTIONAL] To view the configuration, issue the view config command in sp_ctrl on the source system:

   sp_ctrl(sysA)> view config sample_config

10. Activate the configuration in sp_ctrl on the source system. Configuration names are case-sensitive.

    sp_ctrl(sysA)> activate config sample_config

11. To confirm that your configuration is active, type the following sp_ctrl command on the source system to display a list of all configurations. The sample_config configuration should appear under “File Name,” and the word “Active” should appear under “State.”

    sp_ctrl(sysA)> list config

Tip: If your configuration activation fails, use the view config sample_config command in sp_ctrl to view the file. Compare it to Template 1 on page 95 and make sure all of the information you entered is correct. For example, check for extra spaces that are not supposed to be there, or for missing components, such as the o. before the SID. For other configuration troubleshooting tips, refer to Chapter 3 of the SharePlex Reference Manual.

To correct mistakes in the configuration file:

Use the edit config sample_config command in sp_ctrl to correct mistakes in the configuration file before you activate it (or if the activation failed). This command opens the file in the text editor, and you can make the changes by editing the file. Save the changes, and re-try the activation. To change an active configuration, you must copy it to a new file first with the copy config command, and then edit and activate the copy. For more information about the copy config command, see the SharePlex Reference Manual.

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.

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 1: 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 2: 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

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

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