Setting Up Symmetric Replication

With standard replication, study design replication copies the supporting design information (not study-specific information) as well as the study-specific design details. You must select a menu option to replicate data for a specific study. Only the data related to that study is replicated.

Symmetric replication copies only the supporting study design information (not study-specific information). In other words, symmetric replication replicates data related to study design, but not data specific to a study. However, with symmetric replication, all data potentially or actually related to study design is replicated automatically. You do not need to select any menu option.

Symmetric replication offers the following advantages:

• Design elements are defined in one location then automatically replicated to other database locations, reducing input time and risk of error.

• After initial replication, changes to design elements are automatically replicated to the other locations.

• The database system checks for changes at regularly scheduled intervals of your choosing.

• Self-referencing information is replicated: information about regions within regions, what strata are part of what combined strata, and what single treatment regimens are part of what combined treatment regimen.

To replicate study design elements between databases, Oracle Clinical requires that the databases share the same Global Library. That is, you must perform a full Global Library replication before you perform the first design replication at a sharing location.

For more information , see:

• Setting Up Symmetric Replication
  
• Enabling Symmetric Replication
  
• Reconciling Data
  
• Troubleshooting Symmetric Replication
  

Setting Up Symmetric Replication

To successfully complete symmetric replication, you should have a firm understanding of managing multi-master replicated databases. A complete description of this topic is beyond the scope of this documentation. If you choose to perform symmetric replication, see the following Oracle database manual for details about advanced replication and for references to related documentation:

Oracle Database Advanced Replication 11g Release 2 (11.2) Part Number: E10706-05

Note:

If you are using the Oracle Thesaurus Management System (TMS) with Oracle Clinical in a distributed environment, you must set up Oracle Clinical to run symmetric replication. Oracle Clinical handles symmetric replication for both itself and TMS.

For more information , see:

• Installing Symmetric Replication
  
• Running the Symmetric Replication Scripts Required for Oracle Clinical
  
• Verifying Data Is Replicating
  

Installing Symmetric Replication

This section describes the tasks that you must complete before you perform the Oracle Clinical-specific tasks described in the following sections. For detailed information about symmetric replication, see your Oracle documentation.

1. Install the database image with symmetric replication.

2. Check that the initdbname.ora file contains the following specifications:

   • JOB_QUEUE_PROCESSES — At least 10.

   • SHARED_POOL_SIZE — See Oracle Database Reference 11g Release 2 (11.2) for details on setting this parameter.

   • GLOBAL_NAMES — TRUE.

   • DISTRIBUTED_LOCK_TIMEOUT — At least 30.

   • OPEN_LINKS — Number of symmetrically replicated sites.

   See Oracle Database Reference 11g Release 2 (11.2), Part Number E17110-05, for more information about these parameters.

Running the Symmetric Replication Scripts Required for Oracle Clinical

The following table lists the scripts that you run to set up Oracle Clinical to use symmetric replication. These scripts complete the appropriate setup activities for each of the locations.

Before you run these scripts, you must have installed symmetric replication. For details, see Installing Symmetric Replication.

Execute the following prepared scripts in the order listed after you have performed the general tasks described in the previous section.

Table 12-5 Scripts to Run to Set Up Symmetric Replication

Script Order	Run from Account	Script Name	Locations to Run	Script Purpose, Parameters, and Notes
1	SYSTEM	CATREP.SQL	Run at each location.	NOTE: This script is automatically executed when the Oracle database is created or upgraded. If it was run previously, you do not need to run the script again.
2	SYSTEM	OPASRC01.SQL	Run at each location.	The OPASRC01.SQL script: Creates REPSYS accounts and grants privileges required for replication. Registers REPSYS as the replication propagator and receiver. Schedules a job to purge pushed trans­actions from the deferred transaction queue. This script prompts you to enter the: Name of the local database (that is, the current location). Name of the remote database. You can press Enter for this prompt. The OPASRC01.SQL script does not require the name of the remote database instance. Password for local SYSTEM account. Password for local REPSYS account. Verify your progress at this point by entering the following SQL command: SELECT * FROM user_users@dbname .domain The REPSYS account should be open.
3	SYSTEM	OPASRC02.SQL	Run at each location, for each remote location.	The OPASRC02.SQL script: Creates public and private database links to the remote location. Connects to remote location and schedules a job to push the deferred transaction queue to the master location. This script prompts you to enter the: Name of the local database (that is, the current location). Name of the remote database (that is, the complete connect string for the remote database). Password for local SYSTEM account. Password for local REPSYS account. Password for REPSYS account at remote location. NOTE: When the script finishes processing, you will receive the error message: ERROR at line 1: ORA-02011: duplicate database link name Ignore this message. The public database link was already created when you set up standard replication.
4	RXA_DES	RXASRCPK.SQL	Run at each location.	Creates primary keys in each table. CAUTION: If you have existing data, you must reconcile data in all tables symmetrically replicated at all locations before you go any farther. See Reconciling Data for details.
5	SYSTEM	COMPILE_ALL_INVALID.SQL	Run at each location.	Compiles objects rendered invalid by the RXASRCPK.SQL script.
6	REPSYS	RXASRC03.SQL	Run at the master definition location.	Creates empty, quiesced master replication group RXA_DES.
7	REPSYS	RXASRC03A.SQL	Run at the master definition location.	Indicates that each object in the Design sub­system is a replicated object in the RXA_DES schema. Makes column groups for conflict resolution.
8	REPSYS	RXASRC04.SQL	Run at the master definition location.	Sets update resolution to latest time-stamp method.
9	REPSYS	RXASRC04A.SQL	Run at the master definition location, for each remote location.	Adds each remote location to the replication environment as another master group. This scripts prompts for the fully qualified database name of the remote location.
10	REPSYS	RXASRC05.SQL	Run at the master definition location.	Generates triggers and packages needed to support replication, at all locations. Wait while the packages generate at all locations. To monitor progress, enter the following SQL command from each location: SELECT * FROM dba_repcatlog WHERE gname = 'RXA_DES'; When the generation process is done, the DBA_REPCATLOG has no records. Once the count is zero, enter the following SQL command from each location: SELECT COUNT(*) FROM dba_repobject WHERE sname = 'RXA_DES'; The count should be 138.
11	REPSYS	RXASRC06.SQL	Run at the master definition location.	Resumes normal replication activity on RXA_DES (quiesced by the RXASRC03.SQL script).

Verifying Data Is Replicating

To check whether data is replicating correctly:

1. Modify the description of a region at each location.

2. Verify that the change replicates to the other locations.

Caution:

Do not change the description of the same record at two different locations unless the first change is replicated. Otherwise, you may cause a data conflict. See Problems During Installation of Symmetric Replication for more information.

Enabling Symmetric Replication

To activate symmetric replication in each location:

1. Log in at the Global Library-owning location.

2. Navigate to Admin, Reference Codelists, and then select Installation Codelists.

3. Query for the OCL_OPTIONS_TYPE_CODE installation codelist.

4. Set the Long Value of the SR_INSTALLED parameter to Y.

5. Save your changes.

6. Perform Global Library replication.

You cannot change this codelist setting manually at other sites.

Reconciling Data

This section outlines one approach for reconciling data at different locations. A description of Oracle Clinical's management of conflict resolution follows.

Execute all scripts from the RXA_DES account at the master definition location, with the current or default directory being the install directory.

Note:

Use the OCL_MENU_ACCESS local reference codelist to specify which types of data can be maintained at each location. You can set this reference codelist at any time.

To reconcile data:

1. Make sure that the database is active, but that no study design entries are being made.

2. Enter the following command from the SYS account of each database:

   GRANT EXECUTE ON DBMS_RECTIFIER_DIFF TO RXA_DES;

3. Pick a reconciliation master location.

   Once the reconciliation process is completed, all data will be copied from the master location to the remote location.

4. Set the SQL*Plus environment before running the scripts:

   SET ARRAYSIZE 1

5. Make sure the RXA_DES account is assigned a default tablespace in which it has quota.

6. Log in as RXA_DES. Run the diffwsetup.sql script. This script creates the tables that will be populated when you run the diffwgo.sql script.

7. Run the command:

   @diffremote.sql remote_db_link

   where remote_db_link is the database link to the remote database. For example:

   @diffremote hp73x1.world
   

8. Run the diffwgo.sql script. For each table, this script:

   • Empties DIFFtablename and DIFFRtablename.

   • Uses the Oracle DBMS_RECTIFIER_DIFF.DIFFERENCES function to compare the tablename table with the like table in the remote database and populates the DIFFtablename and DIFFRtablename tables based on the comparison.

     These tables are used for later steps.

   • Inserts any records from the remote location that are missing at the master location into the master location.

   • Creates the COMPtablename comparison table.

     This table contains two records for each record at the master location that matches a record in the remote location based on primary keys, but differs from the remote location on one or more field values.

   • Creates the COMP2 tablename comparison table similar to the COMP tablename table, but eliminates duplicate master location records when that same record is compared to multiple remote locations.

9. Look at the COMP2tablename tables created when you ran the diffwgo.sql script. The following tables will probably show differences:

   • COMP2OCL_INVESTIGATORST
   • COMP2OCL_SITEST
   • COMP2TREATMENT_REGIMENS

   COMP2OCL_INVESTIGATORST and COMP2OCL_SITEST will show differences for the XDUMMY1 investigator and site because of differences in the times these dummy records were created. These records are created at installation.

   COMP2TREATMENT_REGIMENS will show differences for previously replicated treatment regimens because of differences in the PM_ID field. Before V3.1, the PM_ID field was not correctly replicated.

   For values that are different, decide whether the value from the remote location or the master location is more accurate. If the value from the remote location is more accurate, update the table corresponding to that comparison table to have the appropriate value. If the master location is more accurate, no action is necessary. Delete all records from COMP2tablename (or only those that have reconciled, if you do not look at all of them).

   Caution:

   Before executing the diffwpropagate.sql script in the next step, back up your data. The script copies all tables involved in symmetric replication to the remote location. Set the SQL*Plus environment again (Step 4), if necessary.

10. Run the diffwpropagate.sql script.

    This script propagates data involved in symmetric replication from the master location to the remote location. The script automatically uses the remote database link that you specified with the diffremote.sql command (Step 7).

11. Repeat Step 6 through Step 10 for each remote location.

12. Run diffwdrop.sql to drop all tables used for comparison.

For more information , see:

• Conflict Resolution
  
• Non-study-specific Tables Replicated
  

Conflict Resolution

Updates to the same record at more than one location before the updates are replicated creates a conflict. For example, if two locations tried to change the code associated with a particular investigator this could cause a conflict when replication is invoked. Handling this type of conflict is one of the management tasks Oracle Clinical performs as part of the replication process. This section describes how Oracle Clinical manages this conflict resolution.

Oracle advanced replication offers several methods for resolving this conflict, while keeping the data synchronized and creating no additional entries in the error queue. Oracle Clinical implements the latest time-stamp method, that is, the update with the most recent time stamp overrides updates at other conflicting locations.

For example, an update to a record in New York has a time stamp of 10:00, Boston updates the same record with a time stamp of 10:02, and the updates are not propagated between the two updates. In this case, the Boston update overrides the New York update.

This does mean that in the case of update conflicts locations in a later (more eastern) time zone will usually prevail, as the latest time-stamp method is not sensitive to differences in time zones. However, these conflicts should occur rarely in Oracle Clinical because the data chosen to be symmetrically replicated is not updated frequently and the replication is scheduled to occur every 2 to 3 minutes.

Without an update conflict resolution method, when update conflicts occur an entry is added to the error queue and neither update is propagated. The error queue can be difficult to manage, so keeping it as clean as possible is important. Additionally, further updates to the same record would automatically create conflicts because symmetric replication requires the pre-update value of all fields exactly match.

Other types of conflicts can occur, such as two locations inserting exactly the same record or a record with the same key values. If some data that is symmetrically replicated is part of clinical study replication (which could occur if the data had not yet been symmetrically replicated), then when symmetric replication occurred, a unique key (insert) conflict would occur. However, having SR_INSTALLED set to Y in the OCL_OPTIONS_TYPE_CODE Installation Codelist avoids this type of conflict. When this option is enabled, symmetrically replicated data is not inserted when a user replicates a clinical study from the menu between symmetric replications.

Non-study-specific Tables Replicated

The following table lists the set of tables containing non-study-specific information that is replicated between all locations when you run the RXASRALL.SQL script.

The OCL_MENU_ACCESS local reference codelist determines which of the non-study-specific tables can be updated at your site. When you set up this access, coordinate the sites. You probably do not want one site responsible for all updating, but if too many sites have update privileges, you risk creating conflicts. Other considerations include how frequently these tables are replicated and time zone differences.

The following is the list of non-study-specific tables that are replicated between all locations when you run the rxasrall.sql script:

• CLINICAL_SUBJECTS
• OCL_PRODUCT_MASTERS
• PATTERNS
• COMBINED_TREATMENT_COMPONENTS
• OCL_PROGRAM_PRODUCT_MASTERS
• REGION_COMPONENTS
• DAILY_DOSES
• OCL_PROGRAMS
• REGIONS
• FACTORS
• OCL_PROJECTS
• STRATA
• OCL_DOSAGE_FORMS
• OCL_SITES
• STRATUM_COMPONENTS
• OCL_INVESTIGATORS
• OCL_SITEST
• TITRATION_STEPS
• OCL_INVESTIGATORST
• OCL_STUDIES
• TREATMENT_REGIMENS
• OCL_ORGANIZATION_UNITS
• OCL_UOM_CONVERSIONS
• TREATMENT_REGIMEN_BY_RANGES

Troubleshooting Symmetric Replication

Symmetric replication provides much desirable capability to sites using it, but it requires monitoring that takes some regular time and education. This section provides some guidelines to stay abreast of issues that affect the monitoring of Oracle Clinical on systems with symmetric replication enabled.

You should be familiar with Oracle Server advanced replication concepts. The most relevant items in the documentation set are the Replication and Replication API Reference manuals. Significant chapters include:

• Using Multimaster Replication

• Administering a Replicated Environment

• Using Deferred Transactions

This section is not intended to be comprehensive, but offers some guidance in diagnosing symmetric replication problems. The first two sections cover the two stages when most problems arise: while installing Oracle Clinical with symmetric replication, and during routine use. The last section provides some ideas for disaster recovery.

In this section:

• Problems During Installation of Symmetric Replication
  
• Problems During Routine Use of Symmetric Replication
  
• Problems after a Failure when Using Symmetric Replication
  

Problems During Installation of Symmetric Replication

If problems arise in replication during the installation process, check the parameters listed in this section.

Parameters in the init.ora File

The following table lists the parameters in the init.ora file that:

• Are used in testing Oracle Clinical and required at installation
• Are known to have an impact on performance

Table 12-6 Parameters to Check in the init.ora File for Symmetric Replication

Parameter	Value	There parameters are...
JOB_QUEUE_PROCESSES	1	used in testing Oracle Clinical and required at installation.
JOB_QUEUE_INTERVAL	600	used in testing Oracle Clinical and required at installation.
SHARED_POOL_SIZE	At least 60 MB	used in testing Oracle Clinical and required at installation.
GLOBAL_NAMES	TRUE	used in testing Oracle Clinical and required at installation.
DISTRIBUTED_LOCK_TIMEOUT	30 or greater	known to have an impact on performance.
OPEN_LINKS	Number of symmetrically replicated sites	known to have an impact on performance.

Advanced Replication Option Is Installed

SELECT * FROM v$option;

The value of the Advanced replication parameter should be TRUE.

Invalid Replication Objects

SELECT sname,oname FROM dba_repobject WHERE status != 'VALID'

DBA_REPCATLOG Is Empty

SELECT timestamp, sname, oname, message FROM dba_repcatlog;

Problems During Routine Use of Symmetric Replication

If you encounter problems after you have successfully initiated symmetric replication, checking for the following situations may provide clues or answers.

Broken Jobs

SELECT job,broken,interval,what FROM user_jobs;

The PSUB job that executes symmetric replication is set to run every few minutes. After 16 consecutive failed attempts to connect to a location, its status becomes BROKEN. Broken job can occur, for example, if a database went down halfway through a replication.

You must stop and restart the broken jobs.

Scheduled Jobs Executing

SELECT job,dblink,last_date FROM defschedule;

The last date should have a value for the job in question.

Unavailable Queues

If you get an error message that a queue is not available for enqueueing, enter the following statement from the SYSTEM account in the master database:

EXECUTE dbms_aqadm.start_queue (    queue_name => 'queue_name_from_message');

followed by a commit.

Errors in DEFERROR

SELECT * FROM deferror;

DEFTRAN queue emptying after sufficient time has elapsed

SELECT COUNT(*) FROM deftran;

Pending Calls

SELECT * FROM defcall;

Invalid Objects in RXA_DES, SYS, SYSTEM, and REPSYS

SELECT owner,object_name,object_type FROM dba_objects WHERE owner IN ('RXA_DES','SYS','SYSTEM');

Incorrect Links

SELECT username,global_name FROM user_users@link, global_name@link;

• From SYS, the user name should be REPSYS.

• From RXA_DES, the user name should be RXA_DES.

• From RXC_REP, the user name should be RXC_REP.

• From anyuser, the user name should be RXA_READ.

Error and Transaction Queues Not Processing Correctly

First, force the transactions to occur.

• From location 1:

  EXECUTE DBMS_DEFER_SYS.EXECUTE('location 2 link');

• From location 2:

  EXECUTE DMBS_JOB.EXECUTE('location 1 link');

Then, check the error queue and transaction queue. If these queues are working, the problem is probably the job scheduling.

Deadlocks or Other Database Errors

Examine the Oracle trace files.

Differences in Data at Different Locations

See Reconciling Data and follow the procedure to see if there are differences in the data at disparate locations. You may want to eliminate the last step that propagates data from one location to another.

Problems after a Failure when Using Symmetric Replication

Oracle Clinical uses several different techniques to replicate data and the underlying database model is very complex. This section is not intended to be the definitive guide to data recovery; it provides some idea of the issues specific to Oracle Clinical replication.

Note:

This section assumes you have already established a backup and recovery plan as described in the Oracle Backup and Recovery Guide.

With most failures, standard Oracle recovery methods are sufficient. For example, if there were an instance failure during replication, Oracle Clinical would recover cleanly and resume from where it left off (with the possible exception of "broken" jobs, see above). In the event of a disaster, the primary concern is the Global Library.

Following are some scenarios of possible disasters and how to recover from them. They assume that generic database recovery has been completed but was unable to fully restore the database. Recovering from a real disaster will probably involve several different scenarios.

Damage to the Global Library at a Non-Global Library Location

Once Oracle Clinical is available for normal use, you can recover the Global Library at a non-Global Library location by performing a full replication from the Global Library-owning location.

Note:

The global library is a logical collection of data within Oracle Clinical. The actual tables also contain study definitions. Depending upon the cause of the disaster, you may also need to recover study definitions.

Damage to the Global Library at the Global Library-owning Location

To repair damage to the Global Library at the Global Library-owning location:

1. Restore the Global Library-owning location.

   1. Identify the most recent copy of the global library.

   2. Open the OCL_INSTALLATION installation reference codelist.

   3. Set the Long Value of the GLIB_LOCATION parameter to that site.

   4. Perform a full replication at the Global Library-owning location.

   5. Return to the OCL_INSTALLATION codelist and set GLIB_LOCATION back to its original Long Value.

2. Recover the data entered into the Global Library between the time of the original replication and the actual disaster. This orphaned data can exist only at the Global Library-owning location.

   1. Test for Study Questions, Question Groups, DCMs, DCIs, and DVGs where the corresponding library object is missing.

   2. Either delete the orphaned study records or reconstruct the Global Library records. This process requires altering internal IDs from SQL*Plus.