Chat now with support
Chat with Support

SharePlex 11.0 - SharePlex Administration Guide

About this Guide Conventions used in this guide Revision History Overview of SharePlex Run SharePlex Run multiple instances of SharePlex Execute commands in sp_ctrl Set SharePlex parameters Configure data replication Configure replication to and from a container database Configure named queues Configure partitioned replication Configure replication to a change history target Configure a replication strategy Configure DDL replication Configure error handling Configure data transformation Configure security features Assign SharePlex users to security groups Start replication on your production systems Monitor SharePlex Prevent and solve replication problems Repair out-of-sync data Tune the Capture process Tune the Post process Recover replication after Oracle failover Make changes to an active replication environment Apply an Oracle application patch or upgrade Back up Oracle data on the source or target Troubleshooting Tips Appendix A: Peer-To-Peer Diagram Appendix B: SharePlex environment variables

How to resynchronize source and target objects

How to Resynchronize Source and Target Tables

The following instructions help you decide how to resynchronize out-of-sync tables.

Manually patch out-of-sync tables

Valid for: All database types

If the number of synchronization errors is small, you can try to repair out-of-sync tables manually. When the Post process detects an out-of-sync condition, it ignores the error and continues to apply the next operations in the post queue. However, Post logs source SQL statements that cause out-of-sync errors to an error file calleID_errlog.sql. (ID is the identifier that SharePlex uses for the target instance, such as the ORACLE_SID or the database name.) You can apply those SQL statements to a target table through the native SQL interface of the database. Because this procedure bypasses the comparison made by Post, the operations should succeed assuming the structure of the target table did not change.

SharePlex stores ID_errlog.sql in the data sub-directory of the variable-data directory on the target system. The entries in the file are similar to the following example:

-- Host (irvlabua) Sid (al920u64)

-- session 2, 1 error --

--

-- [1] Tue Dec 11 13:31:32 2007

-- redolog seq#/offset 26622/26980368

-- redolog timestamp 641050290 (12/11/15 13:31:30)

-- original rowid AAE0m8AAWAAAAFEAAA

-- -- NOT FOUND

delete from “SP_5”.”QA_LOB_DISABLE_INROW” t where rownum = 1 and “KEY”='01';

To apply the SQL manually:

  1. Stop user access to the affected source table.
  2. On the target system, open the ID_errlog.sql file.
  3. Apply the SQL statement(s) to the target table.

  4. Reactivate the configuration if you had to make any changes to it.

    sp_ctrl> activate config filename

  5. Allow user access to the source table.

Resynchronize by copying the source tables

Valid for: All database types

This procedure restores synchronization to out-of-sync target tables by applying a copy of the source tables. You only need to resynchronize the tables that are out of synchronization, so users can continue accessing all other tables.

Important! Before you start, review this procedure and see theSharePlex Reference Guide for more information about the commands that are used.

  1. On the source and target systems, make sure sp_cop is running.
  2. On the target system, run sp_ctrl.

  3. [If necessary] On the target system, issue the show sync command to identify the tables that are out of synchronization.

    sp_ctrl> show sync

  4. On the source system, stop activity for the out-of-sync tables.
  5. On the source system, run sp_ctrl.

  6. On the source system, issue the flush command.

    Note: This command has additional options for use with named queues or multiple targets. See theSharePlex Reference Guide for more information about this command.

    sp_ctrl> flush datasource

  7. On the source system, copy the tables.

  8. On the source system, reactivate the configuration file if you had to make any changes.

    sp_ctrl> activate config filename

  9. On the source system, allow users back onto the source tables.

  10. On the target system, issue the status command until it shows that Post stopped.

    sp_ctrl> status

  11. On the target system, restore the tables.

  12. On the target system, disable or modify triggers, referential integrity constraints and check constraints according to the requirements of your replication strategy.

  13. On the target system, determine the status ID of each message by viewing the Status Database.

    sp_ctrl> show statusdb detail

  14. On the target system, clear each message with the following command.

    sp_ctrl> clear status statusID

  15. On the target system, start the Post process.

    sp_ctrl> start post

Resynchronize with Oracle transportable tablespace

Valid for: Oracle database

The transportable tablespace feature enables you to resynchronize numerous out-of-sync tables quickly and with minimal downtime. To use the transportable tablespace feature, follow the instructions in the Oracle documentation for generating a tablespace set, moving the tablespace set to the target database, and plugging the set into the database. The following instructions contain steps only for using this feature to resynchronize data. It assumes familiarity with using the transportable tablespace feature.

Important! Before you start, review this procedure and see theSharePlex Reference Guide for more information about the commands that are used.

  1. On the source system, set the source tablespace to READ ONLY.

    SQL> ALTER TABLESPACE name READ ONLY;

  2. On the source system, run sp_ctrl.

  3. On the source system, issue the flush command in sp_ctrl.

    Note: This command has additional options for use with named queues or multiple targets. See theSharePlex Reference Guide for more information.

    sp_ctrl> flush datasource

  4. Export the metadata to an export file according to the Oracle documentation.
  5. When the export is finished, copy the datafiles to a secondary location on the source system. This minimizes the impact on the source database of copying the files to the target system.

  6. On the source system, set the source tablespace(s) to READ WRITE mode.

    SQL> ALTER TABLESPACE name READ WRITE;

  7. On the target system, drop the existing datafiles and tablespaces from the target database so that the copied files can be applied.
  8. Copy the files from the secondary location on the source system to the target system.
  9. On the target system, use the Oracle import utility to import the metadata and the tablespace definitions.

  10. On the target system, set the tablespace(s) to READ WRITE mode.

    SQL> ALTER TABLESPACE name READ WRITE;

    Note: SharePlex must be the only user permitted to have write access to the target tables, unless you are using peer-to-peer replication.

  11. On the source system, reactivate the configuration file if you had to make any changes to it.

    sp_ctrl> activate config filename

  12. On the target system, run sp_ctrl.

  13. On the target system, start the Post process.

    sp_ctrl> start post

Resynchronize with an Oracle hot backup on an active database

Valid for: Oracle database

When you use an Oracle hot backup and the reconcile command to resynchronize a target instance, users can continue to access the production data while the backup is made and applied.

Important:
  • To resynchronize centralized reporting, such as a data warehouse, you cannot use a hot backup from all source systems. One backup would override the data from the previous one. You can use a hot backup of one of the source instances to establish the target instance, and then use another method such as export/import or transportable tablespaces to copy the tables from the other instances.
  • To resynchronize peer-to-peer replication, you must quiet all of the secondary source systems for the duration of this procedure. Move all users to the primary system, and then follow the procedure. After the procedure has been performed on all of the secondary systems, users can resume activity on them.
  • Before you start, review this procedure and see theSharePlex Reference Guide for more information about the commands that are used.

To resynchronize with a hot backup:

  1. On the source and target systems, run sp_ctrl.

  2. On the target system, stop the Post process. This allows the replicated data to accumulate in the post queue until the target instance has been recovered and reconciled.

    sp_ctrl> stop post

  3. On the source system, run the Oracle hot backup.

  4. On the source and target systems, verify that sp_cop, sp_ctrl and all SharePlex processes (Capture, Read, Export, Import, Post) are running.

    sp_ctrl> status

  5. Switch log files on the source system.

    • To recover the database to a sequence number, make a note of the highest archive-log sequence number.

    • To recover the database to an Oracle System Change Number (SCN), pick an SCN to recover to on the target database.

  6. Recover the target database from the hot backup:

    • If recovering to a sequence number, recover the database from the hot backup using the UNTIL CANCEL option in the RECOVER clause, and cancel the recovery after Oracle has fully applied the log from the previous step.
    • If recovering to a SCN, recover the database from the hot backup using the UNTIL CHANGE SCN option in the RECOVER clause, and cancel the recovery after Oracle has applied the logs matching the SCN from the previous step.

  7. Open the database with the RESETLOGS option.

  8. On the target system, issue the reconcile command. If you are using named post queues, issue the command for each one. Issue the qstatus command if you are unsure of the queue name.

    • If recovering to a sequence number, substitute the sequence number of the log that you noted in step 5.

      sp_ctrl> reconcile queue queuename for datasource-datadest seq sequence_number

      Example: reconcile queue SysA for o.oraA-o.oraA seq 1234

    • If recovering to a SCN, substitute the SCN that you noted in step 5.

      sp_ctrl> reconcile queue queuename for datasource-datadest scn scn_number

      Example: reconcile queue SysA for o.oraA-o.oraA scn 0123456789

    The reconcile process retains control of sp_ctrl until it is finished, and then the sp_ctrl prompt returns.

  9. On the target system, log onto SQL*Plus as the Oracle user for SharePlex, and run the cleanup.sql script located in the bin sub-directory of the SharePlex product directory. This script truncates and updates the SharePlex tables, which are owned by the SharePlex user. If you are running multiple instances of sp_cop with multiple variable-data directories, there is a SharePlex Oracle user for each one. Make sure you run this script as the SharePlex user that owns the tables you want to restore. The script prompts you for the SharePlex user name and password.

    SQL> @/productdir/bin/cleanup.sql

  10. On the target system, disable or modify the following according to your replication strategy:

    • triggers
    • foreign key constraints
    • cascading delete constraints (or configure SharePlex to ignore them)
    • check constraints
    • scheduled jobs that perform DML

  11. On the source system, reactivate the configuration file if you had to make any changes to it.

    sp_ctrl> activate config filename

  12. On the target system, start the Post process. The two instances are now in synchronization, and SharePlex will continue replicating.

    sp_ctrl> start post

How to restore Oracle archive logs

How to Restore Oracle Archive Logs

If you decide to restore the archive logs to enable SharePlex to resume capture and replication, use the following procedure to determine the required archive logs.

Perform the following steps to determine the required archive logs:

  1. Determine the sequence number that Capture needs to resume processing from. Capture stops when it encounters a log wrap and prints a message to the Event Log (event_log) containing the redo log sequence number it needs. You also can find out this number by querying the SHAREPLEX_ACTID table and looking at the SEQNO column, as shown in the following example:

    SQL> select * from splex.shareplex_actid;

    ACTID SEQNO OFFSET AB_FLAG QUE_SEQ_NO_1 QUE_SEQ_NO_2 COMMAND
    ----- ------ -------- -------- ------------- -------------- ------------
    14 114 9757200 0 672101000 0  

  2. Query the Oracle V$LOG_HISTORY table to find out when that sequence number was archived, then copy the logs from that point forward to the source system.

    SQL> select * from V$LOG_HISTORY;

    RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIM NEXT_CHANGE#
    ----- ------ -------- -------- ------------- --------------
    111 402941650 1 111 2729501 14-JUL-00 2729548
    112 402941737 1 112 2729548 14-JUL-00 2729633
    113 402941930 1 113 2729633 14-JUL-00 2781791
    114 402942019 1 114 2781791 14-JUL-00 2836155
    115 402942106 1 115 2836155 14-JUL-00 2890539

How to release semaphores after process failure

If database corruption or other system problem forced you to shut down SharePlex, verify that SharePlex released the semaphores and shared memory that it was using.

To verify and release semaphores:

  1. Look for any SharePlex processes that did not shut down, and kill them.

    $ ps -ef | grep sp_

    $ kill -9 PID

  2. Change directories to the rim sub-directory of the SharePlex variable-data directory, then issue the od -x command for the shmaddr.loc and the shstinfo.ipc files.

    # od -x shmaddr.loc

    0000000 0000 00e1 ed40 0000 4400 9328 0080 0000

    0000020 0002 0021

    0000024

    # od -x shstinfo.ipc

    0000000 0000 00e0 ee90 0000 4100 9328 0010 0000

    0000020 0002 0020

    0000024

  3. Make a note of the following values:

    • The first 32-bit word of each of the files above reveals the hexadecimal equivalent of the ID of the shared memory segment. Convert this value to decimal. For example, in the shmaddr.loc file shown in step 2, the first word is 0000 00e1, which equates to a decimal value of 225. In the shstinfo.ipc file, the first word is 0000 00e0, which equates to a decimal value of 224.
    • The third word of the shmaddr.loc and the shstinfo.ipc files reveals the hexadecimal equivalent of the KEY of the shared memory segment and the semaphore. (Each set has the same key value.) Do not convert this value to decimal. For example, in the shmaddr.loc file, the third word is 4400 9328. In the shstinfo.ipc file, the third word is 4100 9328.
    • The fifth word of each file is the SEMAPHORE ID. Convert this value to decimal. The semaphore IDs in the examples are hex 0002 0021 and 0020 0020, which in decimal are 131105 and 131104, respectively.

  4. Issue the ipcs -smaa command to view all of the shared memory segments and semaphores. (Shared memory segments are listed first and are denoted with an “m.” Semaphores are denoted with an “s.”) The display looks similar to the following, but will be more extensive.

  5. Verify that the shared memory IDs from the shmaddr.loc and shstinfo.ipc are in the list and that the keys match.

  6. For each shared memory segment, verify that the value in the NATTCH column is 0. This ensures that the SharePlex processes that you killed released their memory segments.

  7. For the semaphores, verify that the semaphore IDs and keys match the file values.

  8. As root, issue the ipcrm -m command for the ID values (224 and 225 in the examples) to remove the memory segments.

    # ipcrm -m 224

    # ipcrm -m 225

  9. As root, issue the ipcrm -s command for the key values (131104 and 131105 in the examples) to remove the semaphores.

    # ipcrm -s 131104

    # ipcrm -s 131105

How to resolve disk space shortage

This topic helps you resolve disk space issues that can occur when something interferes with replication. See Solve Replication Problems for possible causes.

How to conserve disk space on the target

SharePlex captures and processes data much faster than it posts it with SQL statements on the target system, so the target is where most disk problems can occur, assuming the network is operational and data is being sent from the source. If you think the post queue may exceed its disk space, there may be enough free space on the source system to store the data temporarily until the Post queue clears out.

To conserve the disk space on the target:

  1. Stop the Import process.
  2. Let the data accumulate on the source system until Post processes enough messages to clear the post queue.
  3. Start Import.
  4. Continue to stop and start Import until the amount of data accumulating in the post queue levels out.

When you implement this method, monitor the replication services and disk usage on the source system. On Unix and Linux systems, you can use the sp_ps script to monitor processes and the sp_qstatmon monitoring script to monitor the queues.

How to restore disk space

If a queue disk runs out of disk space, you may see messages similar to this in the Event Log:

11/22/07 14:14 System call error: No space left on device bu_wt.write [sp_mport(que)/1937472]

11/22/07 14:14 System call error: No space left on device bu_rls.bu_wt [sp_mport(que)/1937472]

11/22/07 14:14 Error: que_BUFWRTERR: Error writing buffer to file que_writecommit(irvspxuz+P+o.a920a64z-o.a102a64z) [sp_mport(rim)/1937472] 11/22/07 14:14 Error: sp_mport: rim_writecommit failed 30 - exiting [sp_mport/ 1937472]

11/22/07 14:14 Process exited sp_mport (from irvspxuz.domain.com queue irvspxuz) [pid = 1937472] - exit(1)

If a queue disk is almost out of free space, you might be able to add disk space without the need to resynchronize the data.

To restore disk space:

  1. Stop SharePlex on the affected system.
  2. Add more disk space.
  3. Start SharePlex.

  4. View the Event Log and look for the messages "queue recovery started" and "queue recovery complete."

    • If both messages are there, SharePlex resumes processing where it stopped and the recovery succeeded. If your applications generate high volumes of transactions, there may be numerous backlogged messages in the queues. Depending on the nature of the transactions, how well the target database and the Post process are tuned, and your tolerance for latency, it might be more practical to resynchronize the data instead of waiting for replication to regain parity with transactional activity.
    • If one or more queues is corrupted, the Event Log records a message like this: Bad header magic... or peekahead failure. Or, you will see the message queue recovery started, but you will not see the queue recovery complete message that signifies successful queue recovery. In this case, you must restore replication an initial state.

To restore replication to an initial state:

  1. Run db_cleansp to restore the variable-data directory and SharePlex tables. It must be run on all systems in the affected replicationconfiguration. See the utilities documentation in theSharePlex Reference Guide.
  2. Synchronize the data using your method of choice, then reactivate the configuration. For more information, see Start Replication on your Production Systems.
  3. You can prevent this problem from occurring again by using the SharePlex monitoring utilities to start unattended monitoring of key replication events, including queue volume alerts. For more information, see Monitor SharePlex.
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating