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:
     • 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:

     1. Back up your database.
     2. Connect to SQL*Plus as the SYS user:

        sqlplus sys/password
        

2. Check the current database global name: select * from global_name;
3. Update the props$ table to update the global_name:

   update props$ set value$ = 'New name without domain'
   where name = 'GLOBAL_DB_NAME';
   commit;

4. Verify that the global database name does not include the domain name: select * from global_name;
5. 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:

• Check for an ORA-29548 Error
  
• Gather Required Information for Oracle Clinical Database
  
• Run the Installer
  
• Review the Log Files for Upgrade Results and Errors
  
• Compile Invalid Objects
  
• Perform Post-Upgrade Database Tasks
  

Check for an ORA-29548 Error

1. Log in to the server computer.
2. Set environment variables:

   set p1=database_name
   set p2=54
   opa_setup
   

3. Connect to SQL*Plus as the SYS user:

   sqlplus sys/password
   

4. 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 Gather Required Information for Oracle Clinical Database.

   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
   

5. To correct the ORA-29548 error, run the following script:

   start update_javavm_db.sql

Gather Required Information for Oracle Clinical Database

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.

1. Home Details: The ORACLE_HOME location where you installed Oracle Database 19c (19.3.0.0.0).

   For example: Drive:\app\oracle\product\19.3.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:
   Windows: %OPA_HOME%\sas_view

   Note:

   The upgrade Installer does not prompt for this value.
4. Service name for the database to be installed
5. Know if you plan to use either Automatic Storage Management (ASM) or Real Application Clusters (RAC). This affects the Installer behavior for validating tablespaces.
6. Location for tablespace datafiles. You can change the default sizes by editing the script before running the Installer.
7. Location and password for the Wallet created during Oracle Clinical database server installation to store credentials for OCPSUB and RXC_DISC_REP.
8. 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. For new installations, it does 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.
9. 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.
10. Database host name and port number
11. 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 have multiple databases, enter the host name for the database designated as the Global Library location.
12. Location of the secret store folder you created in Create a Secret Store Directory.

Run the Installer

To begin the installation:

1. Log in using an account with Windows system administrator privileges.
2. Navigate to this location in the folder where you extracted the server code; see Download the Software.
3. Execute the following file as an administrator:

   Disk1\install\setup.exe

   The Installer opens to the Welcome screen. Click Next.

4. In the Select a Product to Install screen, select OC Database Upgrade 5.4.0.0.x. Follow instructions on screen, entering the information indicated in Gather Required Information on Oracle Clinical Database Server for Windows.

Review the Log Files for Upgrade Results and Errors

1. Review the generated log files located at:
   OPA_HOME\oc\54\install

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
  
• Reencrypt Account Passwords
  

Find Errors

To simplify reviewing upgrade results, run these commands for each of the database upgrade log files:

1. Open a DOS window as an administrator and enter:

   set p1=database
   set p2=54
   opa_setup
   cd %RXC_INSTALL%
   find /i "error" logfile | find /v "No error"
   

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.

For more information, see:

• 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. Open a DOS window as an administrator and enter:

   set p1=database
   set p2=54
   opa_setup
   cd %RXC_INSTALL%
   

2. Start an SQL*Plus session, and connect to the database as sys:

   sqlplus sys/sys_password as sysdba

3. Run the script as shown in Table 5-1.

   Table 5-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.

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

Note:

Make sure that you have installed the latest CPU certified for release 5.4 before performing any of the instructions below. For information on the latest certified CPU, see Oracle Health Sciences Applications Supported Technology Stack (Document ID 180430.1) on My Oracle Support > section Oracle Health Sciences Critical Patch Updates (CPU) Notes in the PDF file.

Perform 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 Windows server:

1. Log in to the Windows server computer as an administrator.
2. Set the Windows environment:

   set p1=database

   set p2=54

   opa_setup

   where database is the name of this database instance, and 54 is the alias for the version of Oracle Clinical.

3. Change to the drive where Oracle Clincal is installed. For example:
   cd %RXC_INSTALL%
4. Start an SQL*Plus session, and connect to the database in the RXC account:
   sqlplus rxc/password
5. Run the rxcdbinit.sql script to pin the database packages:
   start rxcdbinit.sql

If you are continuing the upgrade, note that you perform the next task in this environment.

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

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.