This chapter includes:
This section describes how to install and set up the Oracle Clinical database server.
The Oracle Universal Installer performs the following operations:
Creates the Oracle Clinical directory structure
Installs the Oracle Clinical database server
Sets permissions on the directories
Creates the environment setup files
Modifies the environment setup files
Creates the Oracle Wallet to store credentials for OCPSUB and RXC_DISC_REP
Creates the directory for storing the SAS files
You must have the following information ready to enter in the Installer screens:
Home Details: The ORACLE_HOME location, which is where you installed Oracle Database 184.108.40.206; for example:
OPA Home: The directory where Oracle Clinical will be installed; Oracle recommends:
SAS View: The directory where Oracle Clinical will generate SAS views; Oracle recommends:
Oracle Wallet location and password. The Installer uses these to create the database Wallet in the location you specify. The database Wallet stores the password for two accounts:
The OCPSUB proxy account, which is used for database access for parameterized submission (PSUB) jobs. If your installation includes multiple databases using either replication or RAC, each database needs an OCPSUB account and its credentials are created in this Wallet on the database server during Oracle Clinical database installation.
The RXC_DISC_REP account. If you use disconnected replication you must insert credentials for this account manually.
Note:If you have already created a Wallet through a previous installation of Oracle Clinical 5.0 or higher, you can continue using it instead of creating a new one by entering the location and password for the existing Wallet.
Log in to the server computer using the opapps account.
In the staging area, locate the directory where you downloaded and extracted Oracle Clinical (see Section 1.7, "Download the Software").
Run the following file as an administrator:
The Installer opens to the Welcome screen.
Note:See Section 1.8, "Use the Silent Installer (Optional)" for instructions for running the Installer as a file with pre-entered parameter values.
Note:Although there is a button for deinstalling products on the Welcome screen, Oracle does not support using the Installer to deinstall Oracle Clinical or Oracle Clinical Remote Data Capture (RDC) Onsite.
In the Select a Product to Install page, select OC Server for Windows 220.127.116.11.x.
Follow the instructions on the installation screens, providing the information you assembled in Section 5.1.1, "Gather Required Information".
Review the generated log files located at:
The Installer creates the following directory structure:
OPA_HOME \bin \xmltemp \oc \52 \bin (Executables) \dcd (Data Collection Definition) \des (Design) \dm (Data Management) \dx (Data Extract) \glib (Global Library) \install (Install and upgrade scripts) \log (PSUB log files) \lr (Lab Ranges) \patch (Patches to Oracle Clinical) \pd (Procedure Definition) \psub (Parameterized Submission process) \release (Server code release marker) \tools (Miscellaneous tools)
Note that OPA_HOME refers to the root installation directory of the Oracle Health Sciences products, which were formerly known as Oracle Pharmaceutical Applications (OPA). You specify the root installation directory when you install the Oracle Clinical database server. Typically, you specify the path to the opapps login directory, for example,
This section describes the tasks you perform to complete the upgrade of Oracle Clinical database server on a Windows computer.
By default, a Windows installation grants read and write privileges to the XMLTEMP database folder to everyone. To reduce security risks, limit permissions on the XMLTEMP folder for all Windows database server installations.
Use Windows Explorer to locate the XMLTEMP folder in the OPA_HOME directory.
Right-click on the XMLTEMP folder, and then select Properties from the menu.
Click the Sharing tab, then click Advanced Sharing.
Select the Share this folder check box to enable sharing with other users on your network.
Give Read and Write (Change) permissions to user
Click OK to save your changes and close the Permissions dialog box.
Click OK to save your changes and close the Advanced Sharing dialog box.
Click Close to close the Properties dialog box.
On Windows systems, configurations are defined in the opa_settings.bat file. This file contains the commands to set environment variables at startup and execution of the PSUB service.
Review the opa_settings.bat file in the following directory:
Adjust the default values, if necessary.
See the Oracle Clinical Administrator's Guide for a list of the settings and for information about changing, adding, and verifying values.
db_env_setting records in opa_settings.bat define a default value for particular environment variables that are set when the application calls opa_setup. You can override the default values for all databases or for a particular database.
See Section 1.9, "Choose a Character Set" for important information.
NLS_DATE_FORMAT must be set to DD-MON-RRRR. It is possible to override this setting for display in RDC Onsite, the Patient Data Report, and Oracle Clinical data entry, but the value in opa_settings must be DD-MON-RRRR.
This section describes how to run Installer to upgrade each database.
Note:If you are upgrading to a RAC environment, you must import your database(s) to a single RAC node before running the Installer to upgrade it.
If you are upgrading your database using export and import, do the following:
Perform one of the following:
If you are upgrading your database through export and import:
a. Make sure GLOBAL_NAME does not include the domain name. To do so, leave DB_DOMAIN null when you create the target database instance.
If you are upgrading an existing database:
You can successfully install Oracle Clinical in a pluggable database only if the database is configured to receive connections as SERVICE rather than the SID. The DB_NAME in OCL_STATE reference codelist must match the service name. Further, the OWNING_LOCATION and LOCATION_CODE in several tables must match the DB_NAME. The OWNING_LOCATION and LOCATION_CODE have a limit of maximum 15 characters. Therefore, the SERVICE name and DB_NAME also have a limit of maximum 15 characters. To satisfy this limitation, remove the domain name from the GLOBAL_NAME and DB_NAME. To do so:
a. Back up your database.
b. Connect to SQL*Plus as the SYS user:
c. Check the current database global name:
select * from global_name;
d. Update the props$ table to update the global_name:
update props$ set value$='New name without domain' where name = 'GLOBAL_DB_NAME'; commit;
e. Verify that the global database name does not include the domain name:
select * from global_name;
Reset the OWNING_LOCATION and LOCATION_CODE so that they match. For instructions, see Cloning Oracle Clinical and TMS 4.6.x, 5.0.x, and 5.1.x Databases, available under Article ID 883213.1 on My Oracle Support.
Log in to the server computer.
Set environment variables:
set p1=database_name set p2=52 opa_setup
Connect to SQL*Plus as the SYS user:
Run the following command to check for the ORA-29548 error:
select dbms_java.get_jdk_version() from dual;
If no error appears and the command retrieves the JDK version, skip to Section 5.2.2, "Gather Required Information".
If you see the following error, continue with the next step to fix it:
ERROR at line 1: ORA-29548: Java system class reported: release of classes.bin in the database does not match that of the oracle executable
To correct the ORA-29548 error, run the following script:
Make sure you have the information below, which is arranged in the order it is prompted for by the Installer during a fresh installation. The order for upgrades is somewhat different.
You have the option to skip the upgrade but rerun all recreatable object scripts (views, packages, functions, and so on) against the database.
Note:You must install the Oracle Clinical database server before you install or upgrade the Oracle Clinical database.
The ORACLE_HOME location, which is where you installed Oracle Database 18.104.22.168; for example:
OPA Home: the directory where Oracle Clinical will be installed; Oracle recommends:
SAS View: the directory where Oracle Clinical will generate SAS views; Oracle recommends:
Note:The upgrade Installer does not prompt for this value.
Service name for the database to be installed
Know if you plan to use either Automatic Storage Management (ASM) or Real Application Clusters (RAC). This affects the Installer behavior for validating tablespaces.
Location for tablespace datafiles. You can change the default sizes by editing the script before running the Installer.
Location and password for the Wallet created during Oracle Clinical database server installation to store credentials for OCPSUB and RXC_DISC_REP.
You will need to enter passwords for the following:
Note:When you upgrade a database, the Installer does not prompt for the following passwords. For new installations, it does prompt for the following passwords.
Note:For information on changing the passwords for these accounts on a regular basis to avoid expiration, see the Oracle Clinical Administrator's Guide.
A database seed number between 1 and 99. Each database in an Oracle Clinical installation (or group of databases that are replicating with each other) must have a unique seed starting number.
Database host name and port number
Global library code. There can be only one Global Library location. If you have only one database, this value should be the same as the database host name. If you are using Oracle Clinical replication and have multiple databases, enter the host name for the database designated as the Global Library location.
Location of the secret store folder you created in Section 3.5, "Create a Secret Store Directory".
To begin the installation:
Log in using an account with Windows system administrator privileges.
Navigate to this location in the folder where you extracted the server code; see Section 1.7, "Download the Software".
Execute the following file as an administrator:
The Installer opens to the Welcome screen. Click Next.
In the Select a Product to Install screen, select OC Database Upgrade 22.214.171.124.x. Follow instructions on screen, entering the information indicated in Section 5.1.1, "Gather Required Information".
Review the generated log files located at:
The rest of this section describes finding errors in the log files (as
logfile), and descriptions of known errors.
To simplify reviewing upgrade results, run these commands for each of the database upgrade log files:
Open a DOS window as an administrator and enter:
set p1=database set p2=52 opa_setup cd %RXC_INSTALL% find /i "error" logfile | find /v "No error"
See My Oracle Support Article ID 386941.1, OLSA 4.6.x and 4.7.x Known Install and Configuration Issues, for a description of any error messages.
If the installation fails to reencrypt any password, it does not list them as errors. Instead, it lists them in the log files in a section titled, "Passwords for the following schema accounts were not converted."
Check if the "Passwords for the following schema accounts were not converted" section exists and if it lists any accounts.
If there are any accounts, reencrypt them using the set_pwd command.
For instructions, see the Oracle Clinical Administrator's Guide.
Normally, if you are upgrading from 5.0 or higher and have not changed the location of the Secret Store directory, you do not need to rerun set_pwd.
When upgrading the Oracle Clinical database, the Installer calls and runs compile_all_invalid.sql to compile invalid objects. However, to reduce the time required to run the script and to ensure that the installation completes in a timely manner, the compile_all_invalid.sql script does not compile these invalid objects:
Packages owned by RXC_PD (that is, the validation and derivation procedures that you have created). The package name starts with RXC_PD.
Data Extract views that belong to a study. In the database, these views are owned by an internal user whose name starts with study_name$.
Objects owned by any ops$ account. The compile_all_invalid.sql script ignores objects if the owner has a dollar symbol ($) in the name.
To view the list of invalid objects:
Open the following log file:
To compile the remaining invalid objects use instructions in the following sections.
If you have any PL/SQL code referenced from your generated procedures, ensure that these objects are valid before running the compile_schema_invalid.sql script.
For example, if you created a schema named X that contains all the PL/SQL code referenced from your generated procedures, first run:
Open a DOS window as an administrator and enter:
set p1=database set p2=52 opa_setup cd %RXC_INSTALL%
Start an SQL*Plus session, and connect to the database as
Run the script as shown in Table 5-1.
|To…||Enter this SQL Command…|
Compile any invalid objects in RXC_PD
Compile any invalid objects for the Data Extract views that belong to a study
Compile any invalid objects in OPS$ accounts
Compile any invalid objects in any account that has the dollar symbol ($) in the account name
Compile all invalid objects in all schemas
Note that this command compiles all invalid objects, including those in other schemas such as RXC and RXA_DES. However, the compile_all_invalid.sql script that the Installer automatically runs after upgrading the Oracle Clinical database already compiles the invalid objects for those schemas.
Check the log file to verify that the script compiled the invalid objects successfully:
Do each of the following tasks.
The Oracle Clinical Remote Data Capture Onsite (RDC Onsite) application uses the dbtimezone value for internal calculations when the Display timestamps in local timezone preference is set.
Oracle recommends setting time zone to a named location rather than a numeric offset so that standard and daylight time adjustments are made automatically.
You can find valid named location strings in the V$TIMEZONE_NAMES view. For example, to find a time zone in the United States, enter the following query:
SELECT distinct tzname FROM V$TIMEZONE_NAMES WHERE tzname like 'US/%'
To set the time zone in the database:
Connect to the database as any user that has ALTER DATABASE privileges.
Enter the following command:
alter database set time_zone='
alter database set time_zone='US/Eastern';
To improve performance, some of Oracle Clinical's packages are pin-able packages; Pinning allocates a stable memory location so that a package cannot be subjected to being swapped out of memory. Oracle Clinical provides the rxcdbinit.sql script in the rxc_install directory to pin the database packages.
To pin the database packages located on a Windows server:
Log in to the Windows server computer as an administrator.
Set the Windows environment:
database is the name of this database instance, and
52 is the alias for the version of Oracle Clinical.
Change to the drive where Oracle Clincal is installed. For example:
Start an SQL*Plus session, and connect to the database in the RXC account:
Run the rxcdbinit.sql script to pin the database packages:
If you are continuing the upgrade, note that you perform the next task in this environment.
The requirements for users who need to run PSUB jobs changed in Release 5.0.
Run a script to migrate user accounts to the new model, if you have not already done so.
See the Oracle Clinical Administrator's Guide for information.
After upgrading to Oracle Clinical 5.2 and setting initialization parameter optimizer_features_enable (see Section 3.4, "Set Initialization Parameters"):
Run scripts ocstats.sql and opastats.sql to gather statistics required for the Oracle Database Optimizer to be effective for internally used accounts.
Failure to execute these scripts can negatively impact performance.
Note:Oracle Clinical 5.2 is certified on the 12c (126.96.36.199) Optimizer.
For more information on gathering statistics and using dynamic sampling to improve performance, see the Oracle Clinical Administrator's Guide.