This chapter describes how to upgrade the Oracle Clinical database server on a UNIX computer.
This chapter includes:
Note:
See "Use the Silent Installer (Optional)" for instructions for running the Installer as a file with pre-entered parameter values.This section describes the tasks that you complete to upgrade Oracle Clinical 5.2 on UNIX database servers.
Grant write access for the opapps account to the ORACLE_HOME directory and its contents.
After you install Oracle Database and before you install the Oracle Clinical component, you must:
Note:
You might have to perform these steps whenever you apply an HSGBU-approved Critical Patch Update, or any Oracle software that uses the Oracle Database ORACLE_HOME directory.With the Bourne shell, you use the Oracle environment-setting script (oraenv) when granting write access to the ORACLE_HOME directory.
However, the oraenv script gives an error if run by a non-Oracle user. To avoid this error:
Set the following directory and file permissions:
drwxrwxr-x 18 oracle oinstall 1024 Apr 11 19:11 inventory drwxrwxr-x 3 oracle oinstall 1024 Apr 11 18:29 ContentsXML -rwxrwxrwx 1 oracle oinstall 492 Apr 11 13:15 oraclehomeproperties.xml
Because the Oracle Clinical Installer checks if the ORACLE_HOME directory exists and if it has write access, you must change the access settings for this directory before you install Oracle Clinical.
To grant write access to the ORACLE_HOME directory and its contents:
Log in to the server as the oracle user.
Find the Oracle environment-setting script to define ORACLE_HOME:
For C shell, use coraenv.
For Bourne shell, use oraenv.
These shells are located under your Oracle Database 12c Release 2 (12.1.0.2) installation.
Note that the oraenv script gives an error if run by a non-Oracle user. To avoid this error, see Section 4.1.2.1, "Change Permissions for Running oraenv Script".
Grant group users modification access to all files in the ORACLE_HOME directory:
chmod -R g+rw $ORACLE_HOME
If you receive any warning messages, you can ignore them.
To set the permissions for the Oracle Inventory (oraInventory) directory:
Log in to the server as the oracle user.
Locate the path for the oraInventory directory. The location is defined in the inventory_loc parameter in the /var/opt/oracle/oraInst.loc file.
For example, suppose you enter:
more /var/opt/oracle/oraInst.loc
The system might return the oraInventory location as:
inventory_loc=/u01/app/oraInventory
Give recursive read and write permission for the oraInventory directory to the group:
chmod -R g+rw /oraInventory_location
For example:
chmod -R g+rw /u01/app/oraInventory
If you receive any warning messages, you can ignore them.
Modify protections on the oraInventory directory to ensure that the group you set up as the oinstall group has write access:
chmod -R g+w oraInventory
Use oinstall instead of dba because the dba group membership gives you access to databases, which is a security issue. The oinstall group gives you access to the Oracle Inventory.
Log in to the UNIX server computer as the opapps user.
Set the UNIX environment:
opa_setup database_name code_environment
Connect to SQL*Plus as the SYS user:
sqlplus sys/password
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 4.1.4, "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:
start update_javavm_db.sql
Have the following information ready to enter in the Installer:
Home Details: The ORACLE_HOME location, which is where you installed Oracle Database 12c Release 2 (12.1.0.2); for example:
root:app/oracle/product/12102/dbhome
If you are installing in a RAC installation, the names of all RAC nodes where the same product(s) should be installed.
OPA Home: the directory where Oracle Clinical will be installed; Oracle recommends:
Drive:\opapps
Owner of Oracle Clinical Server Code must be opapps
Location for files oratab, tnsnames; for example:
/etc
$ORACLE_HOME/network/admin
RXC_USER: Choose directory for RXC_USER; Oracle recommends:
Drive:\opapps
Oracle Wallet location and password. The Installer uses these to create the database Wallet in the location you specify. If you are upgrading, you can enter the existing location and password.
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. Its credentials are created in this Wallet by the Installer.
The RXC_DISC_REP account. If you use disconnected replication you must insert credentials for this account manually.
Log in to the database server as the opapps user.
Change the primary group of the opapps account to the group that owns the Oracle Inventory:
Note:
Make a note of the current group so that you can set it back after the installation.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 installation. Typically, this user group is oinstall.
This temporary change is necessary so that the Installer can update the Oracle Home.
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
Locate the Oracle Clinical software in the directory in the staging area on the database server where you downloaded it; see Section 1.7, "Download the Software".
Open the ldflags file at root: app/oracle/product/12.1.0.2/lib and add the following flag:
-lnnz12
Log in to the server computer using the opapps account.
In the staging area, locate the directory where you downloaded Oracle Clinical and extract the .zip file if you have not already done so.
Navigate to this location:
server_code_platform\Disk1\install
Change protections on all files to 755:
chmod 755 *
Run one of the following commands, depending on whether or nor you are using Oracle Real Application Cluster (RAC):
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 -local
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.
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 UNIX 5.2.0.0.x.
Follow the instructions on the installation screens, providing the information you assembled in Section 4.1.4, "Gather Required Information".
You can review the progress of the installation:
Open another terminal session as the opapps user.
To review the relinking progress, run the following:
tail -f $OPA_HOME/oc/52/relink_rxc.log
Open the installActionstimestamp.log in the oraInventory/logs directory.
This section describes the following tasks that you perform to complete the upgrade of Oracle Clinical database server on a UNIX computer:
Create the log directory for opapps in the following location:
OPA_HOME/log
Define the environment variables for the opapps user:
Open the .cshrc file. This file is located in the home directory after you log in as the opapps user. You can use the following command to view the hidden .cshrc file:
ls -arlt
Add the following lines to the .cshrc file:
set path=( $path ORACLE_HOME/bin ORACLE_HOME/lib )
setenv RXC_LOG OPA_HOME/log
source OPA_HOME/bin/copa_setup_alias
where:
ORACLE_HOME is the directory where you installed Oracle Database 12c Release 2 (12.1.0.2).
OPA_HOME is the directory where you installed Oracle Clinical database server.
Source the .cshrc file when you finish editing it:
source .cshrc
To reduce security risks, you should limit permissions on the XMLTEMP folder for all database server installations.
Log in as opapps.
Go to the OPA_HOME directory.
Enter:
chmod 777 <XMLTEMP>
The Installer creates the opa_setting file and enters all necessary entries and default values for the Oracle Clinical environment.
Review the opa_settings file in the following directory:
OPA_HOME/bin
Adjust the default values for the Oracle Clinical environment., if necessary.
See the Oracle Clinical Administrator's Guide for a list of the environment variables and for information about changing, adding, and verifying values.
Note:
The db_env_setting records in opa_settings 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.
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.
As part of the implementation of the PDR hyperlinks on superscripts, a new script post-processes DCI Form Version templates to insert hyperlink placeholders. This process can fail with error ORA-27369, when a database post-install step has not been completed. For example, for a database on HP-UX, refer to https://docs.oracle.com/cd/B28359_01/install.111/b32072/post_inst_task.htm#BJFEHEGG. You can also refer to My Oracle Support Article ID 391820.1, Scheduled Job Running Shell.
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:
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;
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.
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.
The ORACLE_HOME location, which is where you installed Oracle Database 12.1.0.2; for example:
root:app/oracle/product/12.1.0.2/dbhome
OPA Home: the directory where Oracle Clinical will be installed; Oracle recommends:
/pharm/home/opapps
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.Enter the service name for the database to be installed
Enter the database server name and database port.
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.
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
Provide the database configuration parameter.
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:
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.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 start the upgrade of an Oracle Clinical database on a UNIX database server:
Log in to the server computer as the opapps user.
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.
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
Navigate to this location in the folder where you extracted the server code; see Section 1.7, "Download the Software":
server_code_platform\Disk1\install
Change protections on files to 755.
chmod 755 *
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.
In the Select a Product to Install screen, select OC Database Upgrade 5.2.0.0.x. Follow instructions on screen, entering the information indicated in Section 4.2.1, "Gather Required Information".
Reset the privileges for the opapps account:
newgrp group
where group is the name of your original primary group for the opapps account.
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.
To simplify reviewing upgrade results:
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
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:
$RXC_INSTALL\compile_all_invalid_database.log
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:
compile_schema_invalid.sql X
Then run:
compile_schema_invalid.sql RXC_PD
Log in to the UNIX database server computer as the opapps user.
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, the default value is 52.
Change to the RXC_INSTALL directory:
cd $RXC_INSTALL
Start an SQL*Plus session, and connect to the database as sys:
sqlplus sys/sys_password as sysdba
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 |
|
|
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:
$RXC_INSTALL\compile_schema_invalid_database.log
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='tzname_value';
For example:
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 UNIX server:
Log in to the UNIX server computer as the opapps user.
Set the UNIX environment:
opa_setup database_name code_environment
Change to the RXC_INSTALL directory:
cd $RXC_INSTALL
Connect to SQL*Plus as the rxc user:
sqlplus rxc/password
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 runsrxcdbinit.sql automatically.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 (12.1.0.2) Optimizer.For more information on gathering statistics and using dynamic sampling to improve performance, see the Oracle Clinical Administrator's Guide.
Since the 12.1.0.2 database server code will be in a different directory from the 11g code, you do not have to copy customized files to save your changes.
When you have finished your database installation:
Copy the customized portions of the files into the Release 5.2 version of the file.
All customizable scripts are located in the same directories in Release 5.2 as in Release 4.6.2. For information about customizing them, see the Oracle Clinical Administrator's Guide.
Table 4-2 Customizable Database Scripts and Packages
| File Name | Location | Purpose of Customization | Changed? |
|---|---|---|---|
|
ocl_add_user.sql |
$RXC_TOOLS |
Create new user accounts. |
Changed in 5.0 and 5.1 |
|
ocstats.sql |
$RXC_INSTALL |
Gather statistics; see "Run Scripts to Gather Schema Statistics for the Oracle Database Optimizer". |
New in 5.0, unchanged in 5.1 or 5.2 |
|
opastats.sql |
$RXC_INSTALL |
Gather statistics; see "Run Scripts to Gather Schema Statistics for the Oracle Database Optimizer". |
New in 5.0, unchanged in 5.1 or 5.2 |
|
rxcptdxvb.sql |
$RXC_INSTALL |
Customize data extract views. |
Not changed |
|
pop_vb_static_views.sql |
$RXC_INSTALL |
Customize data extract views. |
Not changed |
|
ocl_client_pb.sql |
$RXC_INSTALL |
Customize to add PLSQL code to be invoked from various parts of the application. |
Not changed |
|
rxasravw.sql/rxasravw_custom.sql |
$RXC_INSTALL |
Customize Study Design replication. |
Not changed |
|
rdcpb_client.sql |
$RXC_INSTALL |
Customize flex fields for DCI Forms. |
Not changed |
|
OCL_UTILS |
$RXC_INSTALL |
See Oracle Clinical Creating a Study for information on customizing this package for making patients eligible for a PSDV plan and the Oracle Clinical Remote Data Capture Onsite Administrator's Guide for other uses. |
Changed in 5.1, not changed in 5.2 |
If you use replication and have upgraded all databases:
Enable the type(s) of replication you use.
See the Oracle Clinical Administrator's Guide for instructions.