12 Setting Up Replication

12.1.1 Locations in a Distributed Study Environment Installation

Replication occurs among multiple database instances. Each database instance in your distributed installation must use the same release of Oracle Clinical.

Each included database instance is referred to as a location. The locations in an installation share Oracle Clinical data or dictionaries with other databases via Oracle Clinical replication capabilities. A location can refer to a separate database on the same computer or to a database in a different physical location.

This set of locations, which shares the same Global Library and can replicate study designs, definitions, and data, is referred to as an installation.

Within a distributed Oracle Clinical installation, you must identify the following locations:

• Global Library-Owning Location — Only one per installation.

• Study-Owning Location — One per study. Each study has one owning location, which can be different from the Global Library-owning location. An installation can have multiple study-owning locations, each for a different study.

• Lab-Owning Location — One or more per installation.

The following sections describe the role of each location.

12.1.2 What Can You Replicate?

You can replicate the following Oracle Clinical objects:

• Global Library

• Study design

• Study definition

• Data

• Global Lab information

• Local labs and ranges

• Study randomization

12.1.3 What Methods of Replication Does Oracle Clinical Support?

Oracle Clinical supports the following methods of replication:

• Standard replication

• Disconnected replication

• Symmetric replication

In your distributed study environment, you can use one method or a combination of methods to replicate data across all the locations in your installation.

12.2.1 Oracle Accounts for Replication

When you install Oracle Clinical, the system creates the following accounts for managing and conducting studies in a distributed environment:

• RXA_READ Account

• RXC_REP Account

• RXC_DISC_REP Account

See "Changing the Password for a Schema or Role Using the SET_PWD Utility."

12.2.2 Tables that Store Replication Information

The following table lists the tables that Oracle Clinical uses to record information about the execution of each type of replication. The information recorded, which varies from table to table, may include the date and time the replication started, the current status of the replication, and the date and time the replication completed.

12.2.3 Seed Numbers for Databases Involved in Replication

Each database within an installation requires a unique start with value that seeds the internal numbers assigned to generated study objects (DCMs, Validation Procedures, and so on) so they are uniquely identified across all locations.

When you install Oracle Clinical, the Oracle Universal Installer prompts you for a unique starting number (from 1 to 99) for each database to be involved in replication. For information on the installation process, see the Oracle Clinical Installation Guide.

12.4.1 Configuring the DB_LINKS Codelist for Standard Replication

Each database location in the installation maintains the DB_LINKS local reference codelist, which has an entry in the Short Value field for each of the other database locations in the installation. The Long Value contains the name of the private database link to that database, owned by the RXC_REP or RXA_DES user.

To configure the DB_LINKS codelist for standard replication:

1. Navigate to Admin, Reference Codelists, and then select Local Codelists.

2. Query for the DB_LINKS local codelist.

   1. In the Short Value field, enter a name for each of the other locations (instances) in the installation. This name should be descriptive, such as "CRO-LONDON" or "HEADQUARTERS" or "PHILA-SITE."

   2. In the Long Value field, enter the database link name of the private link to that database.

Note the name of the private link you specify in the DB_LINKS codelist. You will need this information when you create the private database links for the RXC_REP and RXA_DES accounts later in this section.

12.4.2 Configuring the PUBLIC_DB_LINKS Codelist for Standard Replication

Each database in the installation also maintains the PUBLIC_DB_LINKS local reference codelist, which also has an entry in the Short Value field for each of the other database locations in the installation. The Long Value contains the name of the public database link to that database.

To configure the PUBLIC_DB_LINKS codelist for standard replication:

1. Navigate to Admin, Reference Codelists, and then select Local Codelists.

2. Query for the PUBLIC_DB_LINKS local reference codelist:

   1. In the Short Value field, enter the name for each of the other locations in the installation.

   2. In the Long Value field, enter the database link name of the public link to that database.

Note the name of the public link you specify in the PUBLIC_DB_LINKS codelist. You will need this information when you create the public database link for the RXA_READ account later in this section.

12.4.3 Creating the Private Database Links for the RXC_REP and RXA_DES Accounts

After you set up the DB_LINKS and the PUBLIC_DB_LINKS local reference codelists, you need to create the database links to the other locations in your installation

To create the private database links required for standard replication:

1. Set environment variables for the database name and code environment; see "Setting Environment Variables on the Command Line."

2. Create the private database link for the RXC_REP account:

   1. Connect to SQL*Plus as RXC_REP.

   2. Create a private database link to each of the other database locations:

      create database link linkname connect to rxc_rep identified by password using 'connectstring' ;

      where:

      • linkname is the name specified for your private database link in the DB_LINKS reference codelist.

      • password is the password of the RXC_REP account.

      • connectstring is the appropriate SQL*Net connect string.

        Make sure the connectstring has single quotes around it. Oracle recommends that the connectstring be the same as the linkname although it is possible for them to be different.

   3. Verify that you created the link correctly. The following command should return RXC_REP as the user:

      SELECT username FROM user_users@linkname ;

3. Create the private database link for the RXA_DES account:

   1. Connect to SQL*Plus as RXA_DES.

   2. Create a private database link between the RXA_DES accounts in each instance:

      create database link linkname connect to rxa_des identified by password using 'connectstring' ;

      where:

      • linkname is the name specified for your private database link in the DB_LINKS reference codelist (and also matches the linkname value used for the same database in the previous step).

      • password is the password of the RXA_DES account.

      • connectstring is the SQL*Net alias of the database to which the link connects.

        Make sure the connectstring has single quotes around it.

   3. Verify that you created the link correctly. The following command should return RXA_DES as the user:

      SELECT username FROM user_users@linkname ;

4. Exit from SQL*Plus.

12.4.4 Creating the Public Database Link for RXA_READ

To set up the public database link for standard replication:

1. Connect to SQL*Plus as SYS.

2. Create a public database link to each of the other database locations for the RXA_READ account:

   create public database link linkname connect to rxa_read identified by password using 'connectstring' ;

   where:

   • linkname is the name specified for your database link in the PUBLIC_DB_LINKS reference codelist.

   • password is the password of the RXA_READ account.

   • connectstring is the SQL*Net alias of the database to which the link connects.

     Make sure the connectstring has single quotes around it. Oracle recommends that the connectstring be the same as the linkname, although it is possible for them to be different, if Global Naming is not enabled for the database.

3. Verify that you created the link correctly. The following command should return RXA_READ as the user:

   SELECT username FROM user_users@linkname ;

4. Exit from SQL*Plus.

12.5.1 Using the rxasrall.sql Script

The rxasrall.sql script is the driver script that automatically calls the following three scripts to create the study design replication packages:

• rxaoclrp.sql

• rxarelrp.sql

• rxastmrp.sql

The rxasrall.sql script accepts the name of the remote (that is, shared) database and the name of the database link to that database, and then passes the information to the other three scripts. The study design replication packages replicate all the study-specific design details for the selected study as well as study design supporting information.

12.5.2 Creating the Views and Synonyms Required to Replicate Study Design

The rxasravw_custom.sql script creates the ALL_name views on the design tables, and the AVAILABLE_CLINICAL_STUDIES view. The rxasvsra.sql script creates synonyms to all the views.

You need to run these scripts if you are using:

• Standard replication to replicate study-specific designs

• Symmetric replication to replicate data related to study design, but not specific to a study

To create the views and synonyms required for study design replication:

1. Copy the rxasravw_custom.sql script to the rxasravw.sql script, overwriting the default version. (The default version is for non-replicated installations only.) The custom version creates the views required for design replication.

   The rxasravw.sql script assumes there are two locations remote from any given installation (total of three locations in your replication installation).

   • If you have three or fewer locations, you can use the script as is. Copy it to each of the other one or two locations and proceed to Step 2.

   • If you have more than three locations edit rxasravw.sql, adding a clause for each remote location. If your installation has n locations, your script should contain n-1 clauses. Copy your edited script to each location.

   Tip:

   If you have more than one environment in the same code area, save more than one copy of rxasravw.sql with a name that indicates the environment to which it applies. For example, rxasravw_prod for your production database.

2. Connect to SQL*Plus as RXA_DES.

3. Run the rxasravw.sql script at each location. For example:

   Name of the Current Oracle Clinical Database: example_mexico
   Name of Remote Oracle Clinical Database 1: example1_jersey
   Name of db Link to Remote Oracle Clinical Database 1: to_example1
   Name of Remote Oracle Clinical Database 2: example2_york
   Name of db Link to Remote Oracle Clinical Database 2: to_example2
   

   You do not need to replace spaces in the location codes with underscores.

4. Run the rxasvsra.sql script at each location. This script creates synonyms to all the views created by the rxasravw.sql script.

12.5.3 Creating the Package for Replicating Investigators and Sites

The dyna_rxapkirp.sql script creates the REPLINVSITE_remote_location package, which replicates the investigators and sites used in a study from the specified source location.

Only standard replication uses the REPLINVSITE_remote_location package. Therefore, you run the dyna_rxapkirp.sql script only if you are using standard replication.

As RXC_REP, you need to run the dyna_rxapkirp.sql script at each location, for each remote location from which data will be retrieved.

To run the dyna_rxapkirp.sql script:

1. Change to the RXC_INSTALL directory.

2. Connect to SQL*Plus as RXC_REP.

3. Run the dyna_rxapkirp.sql script at each location, once for each of the source locations in the installation:

   start dyna_rxapkirp.sql

   The script prompts for the following information:

   Enter value for source_location: location_code
Enter value for link: database_link

   where:

   • location_code is the code for the remote location from which data will be retrieved to the current database. The value you specify should match the identifying code stored for this database in the SOURCE LOCATION CODE reference codelist. Be sure to replace any spaces in the location_code with underscores.

   • database_link is the name of the database link to the source database. The value you specify should match a record in the DB_LINKS reference codelist.

4. Exit from SQL*Plus.

12.6.1 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

SELECT * FROM dba_repcatlog WHERE gname = 'RXA_DES';

SELECT COUNT(*) FROM dba_repobject WHERE sname = 'RXA_DES';

12.6.2 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.

12.6.3 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.

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.

12.6.4 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.