SharePlex 9.4 - Installation and Setup for Oracle Source

How to resynchronize source and target tables

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

• For Oracle tables, if only a few tables are out of synchronization, and they are not large, you can use the compare command in sp_ctrl to see how many rows are out-of-sync in each one. If the number of out-of-sync rows is small, you can run the repair command to resynchronize them. If the number of out-of-sync tables is large, it might take less time to re-synchronize the databases than it will to use the compare and repair commands to repair them. For more information, see Overview of Compare and Repair.
• For both Oracle and Open Target databases, you can use the SQL statement(s) in the ID_errlog.sql file to patch the tables manually after you correct the problem. For more information, see Manually patch out-of-sync tables .

• You can resynchronize the data in a number of ways. See the following topics:

  • Manually patch out-of-sync tables
  • Resynchronize by copying the source tables
  • Resynchronize with Oracle transportable tablespace
  • Resynchronize with an Oracle hot backup on an active database

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:

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 the SharePlex 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 the SharePlex 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 the SharePlex 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 the SharePlex 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 the SharePlex 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