Skip Headers
Oracle® Clinical Installation Guide
Release 5.0.1

E36499-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

4 Creating an Oracle Clinical Database

This chapter describes how to create a new Oracle database for use with Oracle Clinical. You must install Oracle Database first; see Chapter 2, "Installing Oracle Clinical Database Server on UNIX" or Chapter 3, "Installing the Oracle Clinical Database Server on Windows."

This chapter includes the following topics:

4.1 Reviewing Database Requirements and Recommendations

Before you install the Oracle Clinical database component, review the requirements and recommendations — such as SID names, tablespace sizes, memory management, and initialization parameters — listed in this section.

Note:

Review these requirements for each new database you create.

4.1.1 Start with a New Database Instance

Oracle recommends that you set up a new database instance so that neither Oracle Clinical nor its installation process interferes with other applications. However, you can install Oracle Clinical on an existing database instance.

4.1.2 Decide on Lowercase or Uppercase SID Name (UNIX)

On UNIX systems, when you define the Oracle Clinical SID (database System Identifier) name, consider these options:

  • If you define the Oracle Clinical SID name using lowercase letters only, there are no conflicts in the Oracle Clinical Data Extract module. Oracle recommends that you use all lowercase letters for the SID name.

  • If you define the Oracle Clinical SID name using uppercase letters, you must create symbolic links. These links are required so that the path to the SAS_VIEW directory is recognized. For information about creating these links, see the Oracle Clinical Administrator's Guide.

4.1.3 Check Required Tablespaces

Table 4-1 lists the tablespaces, along with their minimum size, required for Oracle Clinical. Make sure the database contains these tablespaces. The best practice is to create them with the Autoextend On option, to avoid running out of space.

In addition, you may need to increase the minimum sizes for your installation.

Table 4-1 Required Tablespaces and Sizes

Tablespace Minimum Size

SYSTEM

900 MB

TEMP

100 MB

UNDOTBS1

700 MB

USERS

500 MB

SYSAUX

600 MB


4.1.4 Use the Database Configuration Assistant

To create a new database, use the Database Configuration Assistant (DBCA). For instructions about the Database Configuration Assistant, see the Oracle Database 11g Release 2 documentation, including online help and the Oracle Database 11g Release 2 Installation Guide for the appropriate operating system.

Note:

  • Use the Custom Database installation option.

  • The following parameters are not listed under All Initialization Parameters. Click Show Advanced Parameters to set the following parameters as described in Section 4.1.7, "Set Initialization Parameters":

    • Db_cache_size

    • Db_files

    • Java_pool_size

    • Job_queue_process

    • Large_pool_size

  • The Custom installation option allows access to the Database Storage screen, where you can expand the Tablespaces folder in the tree on the left side of the screen and edit the following tablespaces as required; see Section 4.1.3, "Check Required Tablespaces"

4.1.5 Select Required Components

When you create an Oracle Clinical database, select the following mandatory components:

  • Oracle Text

  • Oracle JVM

  • Oracle XML DB

4.1.6 Use Automatic Memory Management

Oracle recommends that you use Oracle Database 11g Release 2 (11.2.0.4) 's Automatic Memory Management feature for a new or an upgraded Oracle Clinical database.

4.1.7 Set Initialization Parameters

Table 4-2 lists the required and recommended initialization parameters in the init.ora file for Oracle Clinical. For those parameters that accept a value from within a range, the values in the table are the minimum values.

Tip:

Table 4-2 arranges the parameters in alphabetical order. In the Database Configuration Assistant, you can select the Parameter column to sequence the parameters in the same order.

Note:

If you make any changes to the initialization parameters, be sure to stop and restart the database for the new settings to take effect.

Table 4-2 Required and Recommended Initialization Values in the init.ora File

Parameter Value Comments

COMPATIBLE

11.2.0.4

Specifies the release with which the Oracle server must maintain compatibility.

DB_BLOCK_SIZE

16384 bytes

You cannot change this value after you create the database.

DB_CACHE_SIZE

150 MB

Recommended value for 50 to 60 concurrent users. Adjust this value according to your organization's needs.

DB_DOMAIN

company.com

Make this value the same as your company domain name.

DB_FILES

200

Oracle adds needed space to the control files up to the number specified in the DB_FILES parameter.

EVENT

31151 trace name context forever, level 0x100

Required for HTML generation.

NOTE: Do not include the EVENT parameter when you create the database. Once the database is created, you can add the EVENT parameter to the init.ora file.

JAVA_POOL_SIZE

50 MB

Recommended value for 50 to 60 concurrent users. You can change the value of this parameter after installation. (Set greater than 150 MB with Oracle AERS, minimum.)

JOB_QUEUE_PROCESSES

10

Developer-specific parameter. You can change the value of this parameter after installation.

LARGE_POOL_SIZE

50 MB

Recommended value for 50 to 60 concurrent users.

MEMORY_MAX_TARGET

1000 MB (minimum)

Adjust this value according to your organization's needs.

MEMORY_TARGET

1000 MB (minimum)

Adjust this value according to your organization's needs.

 
 

NLS_DATE_FORMAT

DD-MON-RRRR (default value)

Determines the format in which client applications running on the Windows server transfer date information to and from the database. The format must specify the year as RRRR.

NLS_LENGTH_SEMANTICS

BYTE

The CHAR value for this parameter is not supported.

 

OPEN_CURSORS

800 or greater

You can change the value of this parameter after installation.

OPTIMIZER_FEATURES_ENABLE

11.2.0.4

Acts as an umbrella for enabling a series of optimizer features based on an Oracle release number. Oracle Clinical uses the optimizing features of Oracle 11g.

OPTIMIZER_MODE

CHOOSE

If you run Oracle Clinical's statistics gathering scripts, the CHOOSE value sets Oracle Optimizer to apply the execution plan that best minimizes response time. See the Oracle Concepts Guide and the Oracle Tuning Guide for more information. (CHOOSE is the default value when you specify 11.2.0.4 as the value of OPTIMIZER_FEATURES_ENABLE.)

PGA_AGGREGATE_TARGET

200 MB

Recommended value for 50 to 60 concurrent users. You can change the value of this parameter after installation.

REMOTE_LOGIN_PASSWORDFILE

EXCLUSIVE

The database must be set up to use password file authentication.

SEC_CASE_SENSITIVE_LOGON

FALSE

Lets you enter passwords without case sensitivity.

SESSIONS

500 or greater

You can change the value of this parameter after installation.

SGA_MAX_SIZE

600 MB (minimum)

Recommended value for 50 to 60 concurrent users. Adjust this value according to your organization's needs.

SGA_TARGET

600 MB (minimum)

Recommended value for 50 to 60 concurrent users. Adjust this value according to your organization's needs.

SHARED_POOL_SIZE

150 MB (minimum)

Recommended value for 50 to 60 concurrent users. You can change the value of this parameter after installation.

UNDO_MANAGEMENT

AUTO

Specifies which undo space management mode the system uses. When set to AUTO, the instance starts in Automatic Undo Management (AUM) mode.

UTL_FILE_DIR

opa_home\xmltemp

Specifies each directory you access.

Required to support Oracle Clinical PDF layout generation and Oracle AERS.

For Windows environments, samples of the valid syntax are as follows:

UTL_FILE_DIR=c:\e2b\import

UTL_FILE_DIR=c:\opapps\xmltemp

In a UNIX environment, UTL_FILE_DIR requires an entry with two specified paths: one with and one without a trailing slash. Add these lines before any other UTL_FILE_DIR entries:

UTL_FILE_DIR=/usr/opapps/oc/xmltemp/

UTL_FILE_DIR=/usr/opapps/oc/xmltemp


4.1.8 Modify the tnsnames.ora File on the Database Server

The tnsnames.ora file must have an entry for each database that matches the database's Oracle SID. There is a tnsnames.ora file in at least two locations in the installation. Oracle recommends maintaining a master file and copying it to each location whenever you add a database. See Section 5.8, "Modify the tnsnames.ora File on the Application Server" for the application server locations.

The tnsnames.ora file is on each database server at:

oracle_home\network\admin.

4.2 Installing Oracle Clinical Database Objects

Follow the instructions in this section to add Oracle Clinical database objects to this database.

4.2.1 Customize the Installation

Before you install database objects, you may want to modify some of the default SQL scripts used by the Installer; see Section 4.2.1, "Customize the Installation".

4.2.1.1 Edit the Tablespace Size Scripts

The Installer creates several new tablespaces with default sizes. To create larger databases, you can edit two scripts. You can either increase the size of the data files or remove the autoextend clause from the data files. The default value is an autoextend of 1MB and an unlimited maximum size.

UNIX To create larger databases in UNIX, edit the following scripts:

OPA_HOME/oc/50/install/opadba2.sql

OPA_HOME/oc/50/install/rxcdba2.sql

Windows To create larger databases in Windows, edit the following scripts:

OPA_HOME\oc\50\install\opadba2.sql

OPA_HOME\oc\50\install\rxcdba2.sql

4.2.1.2 Edit the User Account Creation Script

The Installer prompts to create accounts in this database. If you select Yes, the Installer runs a script that creates default guest accounts. Before running the Installer, edit the rxcdba4.sql script to customize the accounts that get created and their default settings:

UNIX OPA_HOME/oc/50/install/rxcdba4.sql

Windows OPA_HOME\oc\50\install\rxcdba4.sql

See the Oracle Clinical Administrator's Guide for more information about enrolling users.

4.2.2 Gather Required Information

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.

Note:

You must install the Oracle Clinical Server before you install or upgrade the Oracle Clinical database.
  1. The ORACLE_HOME location, which is where you installed Oracle Database 11.2.0.4; for example:

    root:app/oracle/product/11.2.0/dbhome

  2. OPA Home: the directory where Oracle Clinical will be installed; Oracle recommends:

    /pharm/home/opapps

  3. SAS View: the directory where Oracle Clinical will generate SAS views; Oracle recommends:

    drive: opapps/sas_view

    Note:

    The upgrade Installer does not prompt for this value.
  4. Connect string 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; see Section 4.2.1, "Customize the Installation".

  7. Get the information for wallet location.

  8. You will need to enter passwords for the following accounts. These passwords are encrypted.

    • Oracle Wallet

    • SYS

    • SYSTEM

    • RXC_MAA

    • RXC_PD

    • RXC_REP

    • RXC_DISC_REP

    • BC4J_INTERNAL

    • OPA

    • RXC

    • TMS

    • RXA_DES

    • RXC_LR

    • OCPSUB

    • rdc_midtier_proxy

      Note:

      You will need to reenter the rdc_midtier_proxy_user when you install the Oracle Clinical front end.
    • RXC_WS

    • RXA_READ

    • RXA_RAND

    • RXA_ACCESS

    • OPS$OPAPPS

  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 are using Oracle Clinical replication and have multiple databases, enter the host name for the database designated as the Global Library location.

4.2.3 Start Installing the Oracle Clinical Database

Follow the instructions appropriate for your operating system:

4.2.3.1 Start the Installer on a UNIX Server

To start installing the Oracle Clinical database:

  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:

    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 11g Release 2 (11.2.0.4) 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, and add ":0" to the end of the address. For example:

    setenv DISPLAY 123.45.67.89:0

  4. In the staging area, locate the directory where you downloaded Oracle Clinical (see Section 1.5, "Downloading and Extracting the Software") and extract the .zip file if you have not already done so.

  5. Navigate to this location:

    server_code_platform\Disk1\install

  6. Change protections on files to 755.

    chmod 755 *

  7. Start the Installer:

    ./runInstaller

  8. In the Select a Product to Install page, select OC Database Install.

  9. Follow the instructions on the installation screens, providing the information you assembled in Section 4.2.2, "Gather Required Information".

4.2.3.2 Start the Installer on a Windows Server

To start installing the Oracle Clinical database:

  1. Log in to the server computer using the opapps account.

  2. In the staging area, locate the directory where you downloaded and extracted Oracle Clinical (see Section 1.5, "Downloading and Extracting the Software").

  3. Run the following file:

    Disk1\install\setup.exe

    The Installer opens to the Welcome screen.

    Note:

    See "Using the Silent Installer" 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.
  4. In the Select a Product to Install page, select OC Database Install.

  5. Follow the instructions on the installation screens, providing the information you assembled in Section 4.2.2, "Gather Required Information".

4.2.4 Remove Group Privileges from this Session (UNIX Only)

Recall that before you started this installation on UNIX, you changed the primary group of the opapps account to the group that owns the Oracle Inventory (see Section 4.2.3, "Start Installing the Oracle Clinical Database"). This temporary change was necessary so that the Installer could update the Oracle Inventory.

To reset the privileges for the opapps account, enter the following command:

newgrp group

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

4.3 Reviewing the Log Files for Installation Errors

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

UNIX OPA_HOME/oc/50/install

Windows OPA_HOME\oc\50\install

Check the following log files (where database is the database SID) for error messages and invalid objects:

  • oclinst_database.log

  • reall_database.log

  • oclconfig_database.log

  • flt_seeddata_database_timestamp.log

  • html_dialg_templ_database_timestamp.log

  • html_blob_seeddata_database_timestamp.log

  • xmlp_clob_seeddata_database_timestamp.log

  • xml_clob_seeddata_database_timestamp.log

  • compile_all_invalid_database.log

  • load_olsardcstatemachine_jar_database.log

  • install_database_timestamp.log

  • opaconnectcheck_system_database.log

4.4 Performing Post-Installation Database Tasks

This section describes the following tasks for completing the installation of your Oracle Clinical database:

See also Section 8.1, "Setting Up PSUB" for additional required tasks.

4.4.1 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';

4.4.2 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 to pin the database packages.

4.4.2.1 Pin UNIX 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.

4.4.2.2 Pin Windows Database Packages

To pin the database packages located on a Windows server:

  1. Set the Windows environment:

    set p1=database

    set p2=50

    opa_setup

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

  2. Change to the drive where Oracle Clincal is installed. For example:

    X:

  3. Start an SQL*Plus session, and connect to the database in the RXC account:

    sqlplus rxc/password

  4. Run the rxcdbinit.sql script to pin the database packages:

    start %RXC_INSTALL%\rxcdbinit.sql

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

4.4.3 Set Up Disconnected Replication

Do the following only if you plan to use disconnected replication:

4.4.3.1 Check Tablespace Size

If you use disconnected replication, you might need to increase the size of the DISC_REP_DATA tablespace to fit the amount of replicated data. Installing Oracle Clinical creates the RXC_DISC_REP user account to manage disconnected replication. DISC_REP_DATA is the default tablespace for RXC_DISC_REP. For more information about disconnected replication, see the Oracle Clinical Administrator's Guide.

4.4.3.2 Create a Wallet Credential for the RXC_DISC_REP Database User

  1. Log in as opapps.

  2. Create an entry for the wallet alias in tnsnames.ora; see Section 4.1.8, "Modify the tnsnames.ora File on the Database Server.".

    Enter tnsping wallet_alias to test that it returns success.

  3. Create the credentials for the disconnected replication job. This wallet alias entered here is different from the OCPSUB wallet alias that is created by the Installer.

    In UNIX:

    mkstore -wrl wallet_path -createCredential wallet_alias rxc_disc_rep rxc_disc_rep_password
    

    Note:

    Verify that .cshrc is there and add if it is not.

    In Windows:

    mkstore -wrl %walletpath% -createCredential %walletalias%  rxc_disc_rep rxc_disc_rep_password /NOCONSOLE 1
    
  4. Set tns_admin:

    In UNIX:

    setenv  TNS_ADMIN path_of_sqlnet.ora
    

    In Windows:

    set TNS_ADMIN=%walletpath%
    
  5. Run sqlplus /@ wallet_alias

    The connection should be a success.

  6. Run showuser

    This should display the rxc_disc_rep user.

4.4.3.3 Add Reference Codelist Value

In the OCL_STATE reference codelist:

  1. Add a value called DISC_REP_WALLET. See the Oracle Clinical Administrator's Guide for more information.

  2. Enter the wallet alias as the long value.

4.4.4 Consider Implementing Partitioning

Oracle Clinical supports partitioning of the responses data entry table. Before implementing partitioning to responses data for your Oracle Clinical installation, consider your requirements and options in migrating from a non-partitioned, pre-Oracle Clinical 4.0 responses table to Oracle Clinical 5.0.

Before you migrate your data from a non-partitioned responses table into a partitioned one, back up your existing responses table, indexes, and data.