Upgrade Oracle Clinical Databases

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:

1. 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:

     sqlplus sys/password
     

     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;
     

2. Reset the OWNING_LOCATION and LOCATION_CODE so that they match. For instructions, see Cloning Oracle Clinical and TMS Databases (Doc ID 883213.1) on My Oracle Support.

For more information, see:

• Gather Required Information
  
• Run the Installer
  
• Review the Log Files for Upgrade Results and Errors
  
• Compile Invalid Objects
  
• Perform Post-Upgrade Database Tasks
  

Gather Required Information

Make sure you have the information below before you start the Installer. The information is not in quite the same order as displayed in the Installer for upgrading databases but it is the same information.

You have the option to skip the upgrade but rerun all recreatable object scripts (views, packages, functions, and so on) against the database.

1. Home Details: The OPA_HOME location where you installed Oracle Database 12c Release R2 (12.1.0.2) or ORACLE_HOME location where you installed Oracle Database 19c (19.3.0.0).

   For example:

   • For Oracle Database 12c: root:app/oracle/product/12.1.0.2/dbhome
   • For Oracle Database 19c: /app/oracle/product/19.0.0/dbhome_1/
2. OPA Home: the directory where Oracle Clinical will be installed; Oracle recommends:

   Drive:\opapps

3. SAS View: the directory where Oracle Clinical will generate SAS views; Oracle recommends:
   • UNIX: $OPA_HOME/sas_view
   • Windows: %OPA_HOME%\sas_view

   Note:

   The upgrade Installer does not prompt for this value.
4. Enter the service name for the database to be installed
5. Enter the database server name and database port.
6. Know if you plan to use either Automatic Storage Management (ASM) or Real Application Clusters (RAC). This affects the Installer behavior for validating tablespaces.
7. Location for tablespace datafiles. You can change the default sizes by editing the script before running the Installer.
   If you had selected Yes in the previous step, prefix the location for tablespace datafiles with "<ASM disk group name>/". For example, <ASM disk group name>/<location for tablespace datafiles>.

   To get the location for tablespace datafiles, execute the following command:

   show parameter db_create_file_dest
   

8. Provide the database configuration parameter.
9. Location and password for the Wallet created during Oracle Clinical database server installation to store credentials for OCPSUB and RXC_DISC_REP.
10. You will need to enter passwords for the following:
    • SYS
    • SYSTEM
    • RXC_MAA
    • RXC_PD
    • RXC_REP
    • RXC_DISC_REP
    • OPA
    • RXC
    • TMS
    • RXA_DES
    • RXA_LR
    • OCPSUB
    • RXA_WS
    • RDC_MIDTIER_PROXY

      Note:

      When you upgrade a database, the Installer does not prompt for the following passwords.
    • RXA_READ
    • RXA_RAND
    • RXA_ACCESS
    • OPS$OPAPPS

      Note:

      For information on changing the passwords for these accounts on a regular basis to avoid expiration, see the Oracle Clinical Administrator's Guide.
11. 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.
12. Database host name and port number
13. 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.
14. Location of the secret store folder you created in Create a Secret Store Directory.

Run the Installer

To start the upgrade of an Oracle Clinical database on a UNIX database server:

1. Log in to the server computer as the opapps user.
2. Change the primary group of the opapps account to the group that owns the Oracle Inventory:
   newgrp inst_group

   where inst_group is the name of the group that owns the Oracle Inventory. You specified the name during the Oracle Database 12c Release 2 (12.1.0.2) installation. Typically, this user group is oinstall.

   This temporary change is necessary so that the Installer can update the Oracle Inventory.

3. Set the X Window display output to the IP address of your local computer. Use the standard format for IP addresses, for example:
   setenv DISPLAY 123.45.67.89
4. Navigate to this location in the folder where you extracted the server code; see Download the Software:
   server_code_platform\Disk1\install
5. Change protections on files to 755.
   chmod 755 *
6. Start the Installer:
   • For a non-RAC installation:

     ./runInstaller
     

     Or, if the database server has multiple Oracle Homes, then enter:

     ./runInstaller -invPtrLoc ORACLE_HOME/oraInst.loc
     

     For example:

     ./runInstaller -invPtrLoc /u01/app/oracle/product/121010_qa/oraInst.loc
     

   • For a RAC installation:

     ./runInstaller
     

     Or, if the database server has multiple Oracle Homes, then enter:

     ./runInstaller -local -invPtrLoc ORACLE_HOME/oraInst.loc
     

     For example:

     ./runInstaller -invPtrLoc /u01/app/oracle/product/121010_qa/oraInst.loc
     

     This ensures that the installation is performed only on the local node.

   The Installer opens to the Welcome screen. Click Next.

7. In the Select a Product to Install screen, select OC Database Upgrade 5.2.2.0.x. Follow instructions on screen, entering the information indicated in Gather Required Information.
8. Reset the privileges for the opapps account:

   newgrp group

   where group is the name of your original primary group for the opapps account.

Review the Log Files for Upgrade Results and Errors

The Installer generates numerous log files and saves the files to the following location:

$ORACLE_BASE/oraInventory/logs

For example:

/u01/app/oraInventory/logs

The rest of this section describes finding errors in the log files (as logfile), and descriptions of known errors.

For more information, see:

• Find Errors
  
• Known Error Messages
  
• Reencrypt Account Passwords
  

Find Errors

To simplify reviewing upgrade results:

1. Run these commands for each of the database upgrade log files:
   • Oracle Linux: From the command line, enter:

     opa_setup database 52
     cd $RXC_INSTALL
     /bin/grep -n -E '^ORA-|^PLS-|^SP2-' logfile | more
     

   • Oracle Solaris SPARC: From the command line, enter:

     opa_setup database 52
     cd $RXC_INSTALL
     /usr/xpg4/bin/grep -E '^ORA-|^PLS-|^SP2-' logfile | more
     

   • HP-UX Itanium: From the command line, enter:

     opa_setup database 52
     cd $RXC_INSTALL
     /usr/bin/grep -n -E '^ORA-|^PLS-|^SP2-' logfile | more
     

Known Error Messages

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

Reencrypt Account Passwords

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

1. Check if the "Passwords for the following schema accounts were not converted" section exists and if it lists any accounts.
2. 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.

Compile Invalid Objects

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:

1. Open the following log file:

   $RXC_INSTALL\compile_all_invalid_database.log

To compile the remaining invalid objects, use instructions in the following sections:

• Compile PL/SQL Code Before Running the Script
  
• Run the compile_schema_invalid.sql Script
  

Compile PL/SQL Code Before Running the Script

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:

compile_schema_invalid.sql X

Then run:

compile_schema_invalid.sql RXC_PD

Run the compile_schema_invalid.sql Script

1. Log in to the UNIX database server computer as the opapps user.
2. Set the UNIX environment:

   opa_setup database_name code_environment

   where:

   • database is the name of this database instance.
   • code_environment is the value in the opa_settings file for this code environment. For Oracle Clinical 5.2.2, the default value is 52.
3. Change to the RXC_INSTALL directory:

   cd $RXC_INSTALL

4. Start an SQL*Plus session, and connect to the database as sys:
   sqlplus sys/sys_password as sysdba
5. Run the script as shown in Table 4-1.

   Table 4-1 SQL Commands for Compiling Specific Types of Invalid Objects

   To…	Enter this SQL Command…
   Compile any invalid objects in RXC_PD	start compile_schema_invalid RXC_PD
   Compile any invalid objects for the Data Extract views that belong to a study	start compile_schema_invalid study_name$%
   Compile any invalid objects in OPS$ accounts	start compile_schema_invalid OPS$%
   Compile any invalid objects in any account that has the dollar symbol ($) in the account name	start compile_schema_invalid %$%
   Compile all invalid objects in all schemas	start compile_schema_invalid % 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.

6. Check the log file to verify that the script compiled the invalid objects successfully:

   $RXC_INSTALL\compile_schema_invalid_database.log

Perform Post-Upgrade Database Tasks

Do each of the following tasks:

• Set the Database Time Zone
  
• Pin Database Packages
  
• Migrate Users
  
• Run Scripts to Gather Schema Statistics for the Oracle Database Optimizer
  
• Regenerate DCI Forms to Access from Microsoft Edge Browser
  

Set the Database Time Zone

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:

1. Connect to the database as any user that has ALTER DATABASE privileges.
2. Enter the following command:

   alter database set time_zone='tzname_value';

   For example:

   alter database set time_zone='US/Eastern';

Pin Database Packages

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 UNIX server:

1. Log in to the UNIX server computer as the opapps user.
2. Set the UNIX environment:
   opa_setup database_name code_environment
3. Change to the RXC_INSTALL directory:
   cd $RXC_INSTALL
4. Connect to SQL*Plus as the rxc user:
   sqlplus rxc/password
5. Run the rxcdbinit.sql script:
   start rxcdbinit.sql

   The script pins the database packages and exits upon completion.

   Note:

   You must rerun this script each time you restart the database. Consider creating an entry in the database startup script that runs rxcdbinit.sql automatically.

Migrate Users

The requirements for users who need to run PSUB jobs changed in Release 5.0.

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

Run Scripts to Gather Schema Statistics for the Oracle Database Optimizer

After upgrading to Oracle Clinical 5.2.2 and setting initialization parameter optimizer_features_enable (see Set Initialization Parameters):

1. 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.2 is certified on the 12c (12.1.0.2)/19c (19.1.0) Optimizer.

For more information on gathering statistics and using dynamic sampling to improve performance, see the Oracle Clinical Administrator's Guide.

Regenerate DCI Forms to Access from Microsoft Edge Browser

For information, see the Oracle Clinical Remote Data Capture Onsite Administrator's Guide.