Oracle® Clinical Installation Guide Release 4.6.2 E18817-04 |
|
|
PDF · Mobi · ePub |
Patch OC_4.5.3.11 and patch OC_4.5.3.12 introduced enhancements that expect approval and verification data to be stored internally in a new data model. Oracle Clinical 4.6.2 includes all these enhancements.
In addition, Patch OC_4.5.3.11 and patch OC_4.5.3.12 included a Preparation script and a Data Migration script that migrate existing data from the old format to the new format. Oracle Clinical 4.6.2 includes these scripts.
It is very important that you migrate your data ONCE and ONLY ONCE. Both failing to migrate your data and running the scripts to prepare and migrate data more than once will cause problems maintaining and reporting the correct approval and verification status for patient CRFs.
Oracle strongly recommends applying the latest patch set—4.6.4 or later—immediately after upgrading to Oracle Clinical 4.6.2 to ensure proper data migration because:
The Installer for the patch set automatically checks the data migration status of each study on each database being upgraded and prevents the database upgrade from completing until you have successfully upgraded all nonfrozen studies to the enhanced data model. Failing to migrate causes problems maintaining and reporting the correct approval and verification status for patient CRFs.
The versions of the preparation and data migration scripts shipped with the patch set include a mechanism that prevents running these scripts more than once, which can also cause problems with CRF approval and verification status.
You can also perform several manual checks to determine if the preparation and data migration scripts have already been run on your database(s); see Section 12.1.1, "Methods to Identify If Scripts Were Already Run".
Caution:
If you are not sure if the Preparation and Data Migration scripts have been run against the database, contact Oracle Support for assistance. DO NOT CONTINUE with the upgrade.This appendix includes the following topics:
Section A.1, "Determining Requirements Before Preparing and Migrating Data"
Section A.5, "Migrating a Single Study Before Unfreezing It"
This section describes the following requirements:
If you determined that you need to run the Preparation and Data Migration scripts during the upgrade, note that:
Preparing and migrating data is a lengthy process.
Users cannot access the system. You must perform all data preparation and data migration while the system is inaccessible to users.
You should calculate the required downtime BEFORE you start the upgrade process. Once you start the upgrade, you cannot start up the database until after you run the Preparation and Data Migration scripts.
The Preparation and Data Migration scripts were tested on two different database servers with different hardware configurations. Table A-1 shows the results of the tests. Neither the total load on the system nor the database configuration in either environment was captured. All other factors being equal, the execution time of both scripts is directly related to the number of received DCIs (RDCIs) being processed.
Table A-1 Time Estimates for Running the Preparation and Data Migration Scripts
Hardware Configuration | Number of RDCIs | Preparation Script | Data Migration Script |
---|---|---|---|
HP-UX Itanium RP 4440: 4x 1000 MHz CPUs (2 dual core processors) with 16 GB RAM |
90,000 |
40 min (2250 RDCIs/minute) |
6 minutes (15,000 RDCIs/minute) |
Oracle Sun e6800: 4x 900 MHz CPUs (4 single core processors) with 8 GB RAM |
12,000,000 |
18 hours (11,111 RDCIs/minute) |
2 hours (100,000 RDCIs/minute) |
The Preparation and Data Migration scripts create temporary tables and indexes, and recreate both existing indexes on the RDCI_HISTORY table.
All the temporary tables are created in the tablespace represented by the script variable tbl_tbs. The default value is RXC_DEF_TSPA
. To determine your space requirements for this tablespace, use the following formula as a guideline and add additional space because the script performs direct inserts, and direct inserts insert the data after the HWM.
<number of received DCIs> * 3 records * 3 tables * 350 bytes = number of bytes required without allowance for direct inserts
The indexes are all created in the tablespace represented by the script variable idx_tbs. The default value is RXC_DEF_IDX_TSPA
. To determine your space requirements for this tablespace, use the following formula:
(<number of received DCIs> * 10) + (<number of received DCIs> * 70) + (<number of received DCIs> * 70)
The formulas above are based on the shipped default tablespace usage. If you changed the default tablespace names or usage, you need to recompute accordingly.
Before you prepare and migrate any data, confirm that you completed the following steps:
Have you determined that you need to run the Preparation and Data Migration scripts? See Section 12.1, "Determining Whether You Need to Prepare and Migrate Data" for details.
Have you upgraded the database to Oracle Clinical 4.6.2? See Section 12.6.2 through Section 12.6.6 for details.
Have you backed up your newly upgraded database? See Section A.2.1 for details.
Have you prevented access to Oracle Clinical databases? Section A.2.2 for details.
Back up your newly upgraded database. You should have a complete database backup available for each database before you run the Preparation and Data Migration scripts.
You must ensure that no data entry is performed, and no jobs that update data (such as batch validation) run during the data preparation and migration process.
To prevent users from accessing the data, place the database in restricted mode. Provide restricted session access to the following accounts:
OPA
RXC
RXA_DES
RXC_SERVLETST
SYSTEM
After the data preparation and migration process has finished, remove the restricted access from the databases and user accounts.
Caution:
If users have access to RDC at any point in this process, approval and verification statuses will be misrepresented in all RDC user interfaces and RDC-entered approvals and verifications will not be correctly captured. These changes are not recoverable and must be reentered.Caution:
If you already ran the Preparation script on your data, do not run the script again; see Section 12.1, "Determining Whether You Need to Prepare and Migrate Data."The Preparation script takes a long time to run. See Section A.1.1, "Benchmarks for Estimating Downtime Required to Prepare and Migrate Data".
The Preparation script:
Analyzes which studies to process. For the purpose of this analysis, RDC studies means studies in which approvals and verifications have been entered through RDC. Oracle Clinical studies means studies that do not include any RDC-entered approvals and verifications. The Preparation script labels each study as being in one of four categories for processing as follows:
RDC non-frozen studies. All non-frozen RDC studies are marked for migration.
RDC frozen studies. All frozen RDC studies are marked for migration.
Oracle Clinical non-frozen studies. All non-frozen Oracle Clinical studies are marked for migration.
Oracle Clinical frozen studies. Frozen Oracle Clinical studies will not be migrated. If you anticipate that you will unfreeze an Oracle Clinical study in the future and use RDC to perform approvals and verifications, contact Oracle.
Identifies received DCIs, and their current approval and verification statuses, in a temporary table.
Counts the number of received DCIs to be migrated.
Produces an output file that tells you how many studies need to be migrated and how many received DCIs they contain.
The setup steps vary depending on the operating system your database uses.
To set up UNIX systems for the Preparation script:
Set the environment variables for your database:
opa_setup
database_name
code_environment
For example: opasetup db0001 462
where your database is named db0001 and you have Patch Set 4.6.3 installed.
Change to the RXC_INSTALL directory:
cd $RXC_INSTALL
To set up Windows systems for the Preparation script:
Log in to the server as an administrator.
Open an MS-DOS command window.
Set the environment variables for your database:
set p1=
database_name
set p2=
code_environment
opa_setup
For example:
set p1=db0001
set p2=462
opa_setup
where your database is named db0001 and you have Patch Set 4.6.3 installed.
Change to the RXC_INSTALL directory:
cd /d %RXC_INSTALL%
To run the Preparation script to prepare your data for migration:
Open an SQL*Plus session, connecting as opa
.
Edit the tablespace variable definitions at the top of the script as appropriate for your environment. For example:
define tbl_tbs='TABLESPACE RXC_DEF_TSPA'
define idx_tbs='TABLESPACE RXC_DEF_IDX_TSPA'
Ensure that there is enough space in each of the tablespaces you define for the amount of data you have. See Section A.1.2, "Tablespace Size Requirements" for more information.
Run the oclupg45311prepare.sql script:
start oclupg45311prepare.sql
The script prompts you for the following passwords:
OPA password RXC password
Once you enter your passwords, the script displays the new database session ID on screen.
To view the progress of the script, connect as opa
in a different session and enter the following command:
select * from opa_debug where sessionid =
your_session_id
The preparation script creates the following output file:
oclupg45311prepare-
database_name
-
timestamp
.lis
The output file, which is created in the same location as the script, includes the following information:
A list of all studies in the database, with their categories, where RDC study means a study with RDC-entered approvals and verifications, and Oracle Clinical study indicates a study without such approvals or verifications:
RDC non-frozen studies. These studies are marked for migration.
RDC frozen studies. These studies are marked for migration.
Oracle Clinical non-frozen studies. These studies are marked for migration.
Oracle Clinical frozen studies. These studies are not marked for migration.
A count of the received DCIs for each study to be migrated.
Caution:
If you already ran the Data Migration script, do not run the script again; see Section 12.1, "Determining Whether You Need to Prepare and Migrate Data".The Data Migration script:
Upgrades all live (unfrozen) studies, whether they use RDC or not.
Upgrades all frozen studies that used RDC (specifically, that have approvals and verifications).
Migrates the data for all non-frozen Oracle Clinical studies.
Does not migrate any data for frozen Oracle Clinical studies.
While the Data Migration script is running, users must not be allowed to perform any data entry or updates. Additionally, you must not unfreeze a frozen Oracle Clinical study that never used RDC (specifically, has no approvals or verifications). If you anticipate that you will unfreeze an Oracle Clinical study in the future and use RDC to perform approvals and verifications, contact Oracle.
The setup steps vary depending on the operating system your database uses.
Set the environment variables for your database:
opa_setup
database_name code_environment
For example: opasetup db0001 462
where your database is named db0001 and you have Patch Set 4.6.3 installed.
Change to the RXC_INSTALL directory:
cd $RXC_INSTALL
Log in to the server as an administrator.
Open an MS-DOS command window.
Set the environment variables for your database:
set p1=
database_name
set p2=
code_environment
opa_setup
For example:
set p1=db0001
set p2=462
opa_setup
where your database is named db0001 and you have Patch Set 4.6.3 installed.
Change to the RXC_INSTALL directory:
cd /d %RXC_INSTALL%
To run the Data Migration script:
Open an SQL*Plus session, connecting as opa.
Edit the tablespace variable definitions at the top of the script as appropriate for your environment. For example:
define tbl_tbs='TABLESPACE RXC_DEF_TSPA'
define idx_tbs='TABLESPACE RXC_DEF_IDX_TSPA'
Ensure that there is enough space in each of the tablespaces you define for the amount of data you have. See Section 12.5.2, "Review Tablespace Sizes" for details.
Run the oclupg45311migrate.sql script.
start oclupg45311migrate.sql
The script prompts you for the following passwords:
OPA password RXC password
Once you enter your passwords, the script displays the new database session ID.
To view the progress of the script, connect as opa
in a different session and enter the following command:
select * from opa_debug where sessionid =
your_session_id
The Data Migration script creates the following output file:
oclupg45311migrate-
database_name
-
timestamp
.lis
This output file, which is created in the same location as the script, tracks the progress of the job and provides the following information:
If any entries have been inserted into the OPA_DEBUG table in the current database session, an indication that they are being deleted.
The database session ID for the current script execution.
The number of CRFs that were created and modified between the time the Preparation script started running and the time the Data Migration script started. This number should be zero (0). However, if the number is not zero, note that all approval and verification statuses were recomputed as long as data access was prevented when running the Data Migration script.
An indication that entries are being written to the OPA_DEBUG table.
Information about enabling and disabling logging, triggers, and indexes.
An indication that rows are being deleted from the OPA_DEBUG table, which means that those rows have successfully finished processing.
Note:
You can skip this section if you plan to apply Patch Set 4.6.4 or its successor immediately after upgrading to Oracle Clinical 4.6.2.The oclupg45311replicate.sql script replicates the approval and verification system tracking records in the RDCI_HISTORY table from the source location to the target location for the data replicated studies.
You must run the oclupg45311replicate.sql script on the slave database only if the following conditions are true:
You are upgrading a replicated environment.
Replicated data has approval and verification records at the source location.
This data existed and was replicated before you ran the following scripts:
Preparation script (oclupg45311prepare.sql)
Data Migration script (oclupg45311migrate.sql)
Note:
The Preparation and Data Migration scripts must have been run (once and only once) in all of the databases within the replication installation before running the oclupg45311replicate.sql script.For information about whether you ran these scripts, see Section 12.1, "Determining Whether You Need to Prepare and Migrate Data."
You run the oclupg45311replicate.sql script at the target location so the system can populate the RDCI_HISTORY table for all data replicated studies. The script uses the list of studies in the STUDY_REPLICATION_JOBS table in the database at the target location. Running the script synchronizes the data in the RDCI_HISTORY table between the source location and the target location.
UNIX To run the oclupg45311replicate.sql script on UNIX:
Log in to the database server as the opapps
user.
Set up the environment variables:
opa_setup
database_name code_environment
where:
database_name is the name of the target database
code_environment = 462
Change to the installation directory:
cd $RXC_INSTALL
Connect to SQL*Plus. You can connect as any user; the script prompts for the correct user.
sqlplus
user_name/password
Run the script:
start oclupg45311replicate.sql
The script prompts for the opa and rxc_rep passwords.
Enter the passwords.
Verify that the script ran successfully.
Review the following generated log file after the script completes processing:
oclupg45311replicate-
timestamp
.lis
where timestamp is the date and time (yyyymmddhhmiss).
Windows To run the oclupg45311replicate.sql script on Windows:
From the command line, enter:
set p1=database
set p2=462
opa_setup
cd %RXC_INSTALL%
Start an SQL*Plus session, and connect to the database as sys:
sqlplus sys/
sys_password
as sysdba
Run the script:
start oclupg45311replicate.sql
The script prompts for the opa and rxc_rep passwords.
Verify that the script ran successfully.
Review the following generated log file after the script completes processing:
oclupg45311replicate-
timestamp
.lis
where timestamp is the date and time (yyyymmddhhmiss).
After you install Oracle Clinical Patch Set 4.6.3 or later, the only studies that remain unmigrated are frozen studies that have never had any approvals or verifications performed. All other studies are migrated.
If you later try to unfreeze an unmigrated study, the system automatically checks if the study has been migrated to the enhanced approve/verify data model and prevents you from unfreezing the study until you have migrated its data. This prevents any problems from occurring if you later use RDC to perform approvals and verifications.
If necessary, you can migrate the study's data using the script ocl_appver_single_migrate.sql that is shipped with the latest patch set.
Set the environment variables for your database:
opa_setup
database_name code_environment
For example: opasetup db0001 462
where your database is named db0001 and you have Patch Set 4.6.3 installed.
Change to the RXC_INSTALL directory:
cd $RXC_INSTALL
Log in to the server as an administrator.
Open an MS-DOS command window.
Set the environment variables for your database:
set p1=
database_name
set p2=
code_environment
opa_setup
For example:
set p1=db0001
set p2=462
opa_setup
where your database is named db0001 and you have Patch Set 4.6.3 installed.
Change to the RXC_INSTALL directory:
cd /d %RXC_INSTALL%
To set up and run the ocl_appver_single_migrate.sql script:
Log in to SQL*Plus as the user RXC.
Run the single-study migration script.
start ocl_appver_single_migrate.sql
The script prompts you for the following passwords:
OPA password
RXC password
The script prompts you for the name of the study you want to migrate.
The system migrates the study to the enhanced data model and generates log file ocl_appver_single_migrate-database-timestamp.lis, with the timestamp in format YYYYMMDDHH24MISS.
The log file contains the following information:
If any entries have been inserted into the OPA_DEBUG table in the current database session, an indication that they are being deleted
The database session ID for the current script execution
An indication that entries are being written to the OPA_DEBUG table
Information about enabling and disabling logging, triggers, and indexes
An indication that rows are being deleted from the OPA_DEBUG table, which means that those rows have successfully finished processing