Oracle® Clinical Installation Guide Release 4.6 Part Number A83779-08 |
|
|
View PDF |
Oracle Clinical 4.6 includes enhancements for tracking changes to audit data and verifications that were introduced in the following companion patches: Oracle Clinical patch OC_4.5.3.11 and Oracle Clinical patch OC_4.5.3.12.
Oracle Clinical 4.6 runs the repairs from these patches, however, you must manually migrate your data from that repair.
This section describes modifying existing data to enable the Approvals and Verifications enhancements introduced in Oracle Clinical patch OC_4.5.3.11 and Oracle Clinical patch OC_4.5.3.12.
WARNING:
If you applied patch OC_4.5.3.11 or any of its successors, skip the tasks in this section. Rerunning the Data Migration script can corrupt your data. If you installed patch OC_4.5.3.11 or any of its successors, you already prepared and migrated your data. Do not migrate data again. Skip this section.
This appendix includes the following topics:
The way in which Oracle Clinical internally stores approvals and verifications changed beginning with patch OC_4.5.3.11. You must apply patch OC_4.5.3.11 (or one of its successors) and then migrate your existing received DCIs to the new format for representing approvals and verifications.
Note:
If you installed patch OC_4.5.3.11 (or one of its successors), you already prepared and migrated your data. You do not need to migrate data again when you install any patch that obsoletes patch OC_4.5.3.11.The data migration may be a lengthy process. To minimize downtime and give users the maximum possible access during the data migration process, divide the process into two stages:
Data preparation, which can be done while users are active on the system
Data migration, which along with the application of the patch itself, must be done while the system is inaccessible to users
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 ScriptFoot 1 | Data Migration ScriptFoot 2 |
---|---|---|---|
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) |
Footnote 1 The system can be accessible to users while you run the Preparation script.
Footnote 2 The system MUST NOT be accessible to users while you run the Data Migration script.
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.
Oracle Clinical 4.5 included two new tablespaces:
TEST_DATA
TEST_INDEX
Note that:
If you installed Oracle Clinical 4.5 as a fresh install, Oracle Clinical automatically created these tablespaces for you.
If you applied patch OC_4.5.1.30, you already addressed the tablespace issue during the installation of that patch.
If you created your database before Oracle Clinical 4.5, performed upgrades only, and did not install patch OC_4.5.1.30, you must create tablespaces for test indexes. You can either create the TEST_DATA and TEST_INDEX tablespaces, or edit the oclupg4.5.3.11drvr.sql script and change the tablespace definition in the received_dcm_site_nfk_idxt index creation to an existing tablespace such as RXC_LI_IDX_TSPA.
If you installed patch OC_4.5.3.11, you already prepared and migrated your data. Do not migrate data again.
You can run the Preparation script while users have access to the system.
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.
You can use these counts to estimate the amount of time required to run the Data Migration script. See Section A.1.1, "Benchmarks for Estimating the Downtime Required" for more information.
To set up UNIX systems for the Preparation script:
Set the environment variables for the database you are patching:
opa_setup
database_name
45
where database_name
is the name of your database.
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 the database you are patching:
set p1=
database_name
set p2=45
opa_setup
where database_name
is the name of your database.
Change to the RXC_INSTALL directory:
cd /d %RXC_INSTALL%
To run the Preparation script for data 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. 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 script displays the database session ID on screen.
The preparation script creates the following output file:
oclupg45311prepare-
dbname-yyyymmddhh24miss
.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.
You can use these counts to estimate how long the Data Migration script will run. See Section A.1.1, "Benchmarks for Estimating the Downtime Required" for more information.
Note:
CRFs created while the Preparation script is running may not be included in the count. However, they will be processed by the Data Migration script.You can run the Preparation script while users have access to the system. However, you must ensure that no data entry is performed, and no jobs that update data (such as batch validation) run during the rest of the installation and migration process, including:
Run these diagnostic scripts and migrate data on each database.
On each database, back up the database. This step is very strongly recommended.
Install patch OC_4.5.3.11 on each database.
On each database, run the Data Migration script.
Upgrade the application and report servers.
Install patch OC_4.5.3.11 on each Oracle AS10gR2 server. See the Oracle Clinical Patch OC_4.5.3.11 Release Notes for instructions.
Install patch OC_4.5.3.12 on the Oracle9i Application Server. See the Oracle Clinical Patch OC_4.5.3.12 Release Notes for instructions.
Install patch OC_4.5.3.12 on the AS10gR1 Reports Server and restart the Report Server services. See the Oracle Clinical Patch OC_4.5.3.12 Release Notes for instructions.
To prevent users from accessing the data during migration and upgrade (applying the patch), place the database in restricted mode. Provide restricted session access to the following accounts used by the Data Migration script:
OPA
RXC
RXA_DES
RXC_SERVLETST
SYSTEM
After the database has been upgraded and data migration has finished, remove the restricted access from the databases and user accounts.
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.
Oracle strongly recommends that you have a complete database backup available for each database before applying the patch. If any users make changes to data while you are applying the patch or running the Data Migration script, data will be corrupted. The only way to correct the data is to restore the database from the backup and reenter the data updated or added during the installation and data migration process.
Follow these instructions to patch each Oracle Clinical database.
To patch each Oracle Clinical 4.5.3 database on a UNIX server:
Set the environment variables for the database you are patching:
opa_setup
database_name 45
where database_name is the name of your database
Change to the RXC_INSTALL directory:
cd $RXC_INSTALL
Open an SQL*Plus session, connecting as opa
.
Confirm that you are connected to the correct database. The following SQL statement returns the name of the current database:
select * from global_name;
Run the patch driver script:
start oclupg4.5.3.11drvr.sql
Exit from SQL*Plus.
Review the generated log files for errors:
oc4.5.3.11_database_datetime.log
xmlp_clob_seeddata_database_datetime.log
compile_all_invalid.log
Work with Oracle Support, if necessary, to resolve any errors.
Repeat these instructions for each Oracle Clinical database on this Windows server.
When you have applied the patch to all appropriate databases, you have completed the application of the patch on this UNIX server.
To patch each Oracle Clinical 4.5.3 database on a Windows server:
Log in to the server as an administrator.
Open an MS-DOS command window.
Set the environment variables for the database you are patching:
set p1=
database_name
set p2=45
opa_setup
where database_name
is the name of your database.
Determine the location of the ORACLE_HOME directory on this server:
Open the Windows Registry Editor. (Click Start, click Run, enter regedit
, and then click OK.)
Navigate to the following key:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_oraclehomename
Double-click ORACLE_HOME, and note its location. This value is commonly drive:\oracle\ora92080. The remaining instructions refer to this location as the ORACLE_HOME_VALUE.
Exit from the Windows Registry Editor.
Define the ORACLE_HOME environment variable to ensure that the driver script can locate the import utility:
set oracle_home=
ORACLE_HOME_VALUE
Change the working directory to the INSTALL directory:
cd /d
OPA_HOME_VALUE
\oc\45\install
Open an SQL*Plus session, connecting as opa
.
Confirm that you are connected to the correct database. The following SQL statement returns the name of the current database:
select * from global_name;
Run the patch driver script:
start oclupg4.5.3.11drvr.sql
Exit from SQL*Plus.
Review the generated log files for errors:
ocl4.5.3.11_database_datetime.log
xmlp_clob_seeddata_database_datetime.log
compile_all_invalid.log
Work with Oracle Support, if necessary, to resolve any errors.
Repeat these instructions for each Oracle Clinical database on this Windows server.
When you have applied the patch to all appropriate databases, you have completed the application of the patch on this Windows server.
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).
Does not migrate data for frozen studies that do not have approvals and verifications. CRFs in Oracle Clinical frozen studies are displayed as not approved and not verified both before and after the application of the patch, when viewed through RDC. 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 Data Migration script processes all studies that the Preparation script determined should be migrated (3 out of 4 categories). In addition, the Data Migration script identifies and compensates for updates that were made during and after the Preparation script was executed, so normal data entry activities can continue while the Preparation script is running. However, you should ensure that users do not make the following study-level change during this time:
Do not unfreeze a frozen Oracle Clinical study that never used RDC (specifically, has no approvals or verifications).
If you do, only newly created CRFs are migrated. The existing data continues to be represented as not approved and not verified. If you anticipate that you will start using RDC to perform approvals and verifications for existing CRFs in a frozen non-RDC study, contact Oracle.
To minimize the downtime required, run the Data Migration script as soon as possible after running the Preparation script.
Set the environment variables for the database you are patching:
opa_setup
database_name
45
where database_name
is the name of your database.
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 the database you are patching:
set p1=
database_name
set p2=45
opa_setup
where database_name
is the name of your database.
Change to the RXC_INSTALL directory:
cd /d %RXC_INSTALL%
Note: If you installed patch OC_4.5.3.11, you already ran the Data Migration script to migrate your data. You do not need to run the script again.
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 9.1.6, "Review Tablespace Sizes" for details.
Run the oclupg45311migrate.sql script. 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 script displays the database session ID on screen.
The Data Migration script creates the following output file:
oclupg45311migrate-
dbname-yyyymmddhh24miss
.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, for which the approval and verification statuses are recomputed
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