2 Installing the Database Tier

2.1 Prerequisites

Following are the prerequisites for installing or upgrading the database tier:

1. Download the TRC installation package from the Media Pack and extract the files into a directory.

   The Database folder contains all the scripts required for the TRC database tier installation. The master install script install_TRC.sh is in the master_install directory.

2. If an Oracle Exadata Appliance is used for the database tier, see Oracle Health Sciences Translational Research Center Suite Exadata Configuration Guide (Doc ID 1557221.1).

3. TRC requires several roles to exist, which are used for controlling access to different database features. Create the following roles. The installation of the database tier will check their existence:

   • create role CohortDatamartUser;

     This is a read-only role to access the CDM objects. This role should be assigned to a user that needs to use SQL to query the CDM data.

   • create role CohortDatamartAdmin;

     This is a role that can be used for ETL operations if the CDM schema account cannot be used directly to run the ETL code.

   • create role OmicsDatamartUser;

     This is a read-only role to access the ODB objects. This role should be assigned to a user that needs to use SQL to query the ODB data.

   • create role OmicsDatamartAdmin;

     This is a privileged role to let a user run the reference loaders that insert data into the reference tables. This role lets a schema user load reference data.

   • create role OmicsDatamartContributor;

     This is a privileged role to let a user run the result loaders that load data for patients or subjects. This role lets a schema user load result data.

   • create role TRC_APPLICATION_ROLE;

     This role is used by the application to access various objects in other schemas. This role is not intended to be assigned to any user.

   • create role JOBENGINE_ADMIN;

     All JOB_ENGINE roles are reserved for future use to access the JOB schema code. There is no direct access at this time to use the job engine schema and these roles should not be assigned to a schema user.

   • create role JOBENGINE_MANAGER;

   • create role JOBENGINE_PROGRAMER;

   • create role JOBENGINE_USER;

   • create role JOBENGINE_VIEWER;

4. The following five database schemas are required to store TRC objects. It is recommended that each schema have a separate default tablespace and that each schema have quota granted on the tablespaces required:

   • CDM - Cohort Data Mart (CDM) schema stores the clinical information that is refreshed by ETLs.

   • ODB - Omics Data Bank (ODB) schema stores the omics data linked to patients as well as genetic reference data to link.

   • APPS - Application (APP) schema stores information used by the User Interface.

   • ENT - The Enterprise (ENT) schema stores system-wide configuration information like product version.

   • JOB_ENGINE - Job Engine (JOB_ENGINE) schema tracks all jobs executed and provides templates for jobs.

5. Create TRC tablespaces to store schema objects. Each schema should be granted quota on each of these tablespaces. Each tablespace should be created with options to match the anticipated size and use. For details on creating tablespaces, see the Oracle® Database Storage Administrator's Guide 11g Release 1 (11.1).

   Database Schema	Tablespaces
   ENT	ENT_DATA (default)
   CDM	CDM_DATA (default) CDM_INDEX
   ODB	ODB_DATA (default) ODB_INDEX ODB_LOB
   APPS	APPS_DATA (default)
   JOB_ENGINE	JOB_DATA (default) JOB_INDEX DBFS_TBS DBFS_ LOB

   
   
   
   

   Note: The CDM schema now stores HIPAA identifiable attributes. Make sure that tablespaces are encrypted to protect the at-rest data. Contact your database administrator for encrypting the tablespaces. Using encrypted tablespaces requires a license for the Advanced Security Option. For details, see the Oracle Database Administrator's Guide 11g Release 2 here https://docs.oracle.com/cd/E11882_01/server.112/e25494/toc.htm.

   
   

6. Run the following SQL script to grant all required system privileges and roles to the schema users. This script can be executed as SYS database account and is available in the master_install directory (replacing each schema name with the correct schema name used):

   @grant_schema_priv.sql cdm_schema odb_schema app_schema job_schema ent_schema

7. Edit the 3 partitioning script files to match the expected user requirements. These scripts should be modified to match the expected amount of data to maximize the efficiency of each partition. Queries using these tables use the partition keys to limit the amount of data that is used in any query. Extreme care should be used to set the correct values needed. Following is a description of each script that should be modified:

   
   

   Note: For ODB, the partitioning is for either GENE or STUDY so you can update that particular script. The specimen partitioning is mandatory and has to be updated.

   
   

   • cdm_install/cdm_define_partitions.sql: This script defines the number of hash partitions on the bridge tables in the CDM schema. The bridge tables have the most amount of data since they store patient specific information for various dimensions. The default number of partitions is set to 1024. Any number you use must be a power of 2.

     
     

     Note: Each definition can be set to an empty string if no partitions are used, and the definition for the partition index clause should also be set to an empty string.

     
     

   • odb_install/define_partition_hash_gene.sql: This script defines the number of hash partitions for each result table when gene partitioning is used to create the schema. The gene partitioning mode uses a hash sub partition for all result records linked to specific GENE records. The value used should be a power of 2 and reflect the anticipated amount of data. There is also a hash value for the number of partitions used for the W_EHA_VARIANT_BROWSER_AGG table that collects information for each patient.

   • odb_install/define_partition_hash_study.sql: This script defines the number of hash partitions used for each result table when study partitioning is used to create the schema. The study partitioning mode uses an interval partition on the study foreign key and then a hash sub partition for all result records using the foreign key to the GENE field. The values used restrict the number of studies that can be supported. Each Oracle table has a limitation of 1048575 (1024K - 1) total physical partitions. This implies that the maximum value for STUDY key cannot exceed the maximum number of partitions divided by the hash value. For the default value of 128, the maximum STUDY value is 8191. If a large number of studies are anticipated, then the hash value should be adjusted to allow for the total number of studies. The value used should be a power of 2. There is also a hash value for the number of partitions used for the W_EHA_VARIANT_BROWSER_AGG table that collects information for each patient.

   • odb_install/define_partition_hash_spec.sql: This script defines the number of hash partitions for the specimen partitioning used for all result tables. The specimen partitioning will create a chromosome based hash main partition (if chromosome exists in the data) and then a specimen based hash subpartition. The values used should reflect the anticipated amount of data to keep the amount of data in each partition manageable. For details on the specimen partitioning, see the Oracle® Health Sciences Translational Research Center Programmer's Guide.

2.2 Database Physical Layout Considerations

Oracle recommends creating each TRC schema with a dedicated default tablespace. The TRC installer places schema tables into these default tablespaces. Oracle also recommends creating separate tablespaces for each TRC schema to store index and LOB segments. These tablespace names are supplied as installer parameters during installation.

The ODB loaders in TRC utilize large Global Temporary tables, which require adequately sized temporary tablespace. Some of the ODB loaders use very large transactions that require adequately sized UNDO tablespace and Redo logs. For more information on allocating and managing these objects, see the Oracle Database documentation.

2.3 Installing the Database Tier

2.4 Upgrade Considerations

The upgrade portion of the installer supports upgrading the ODB schema from any of the existing versions to the current version. The CDM schema must use the following upgrade matrix for TRC 3.1.

Following is the upgrade matrix for TRC 3.1:

2.5 Post-Installation Database Tier Steps

ODB schema loaders require an Oracle directory object to be passed to each loader. The database administrator should create the required Oracle directory objects and ensure that appropriate permissions are set to let ODB schema user load result files. For more information, see the Oracle Health Sciences Translational Research Center Programmer's Guide.

2.6 Creating Synonyms for TRC Objects (Optional)

If desired, a set of synonyms for TRC objects can be created in any additional database schema. Firstly, appropriate TRC role(s) should be granted to this database user. Then, connect as this database user and execute the following script from the master_install directory:

@create_synonym_for_user.sql &&cdm_schema_name &&odb_schema_name &&apps_schema_name &&jobengine_schema_name

2.7 Recommended Patches for Known RDBMS Issues

Oracle recommends that you apply the following RDBMS patches to prevent known RDBMS issues. At the time of this guide's release, these patches have not been incorporated into any available critical patch update.

ORA-07445

ORA-07445: exception encountered: core dump [kkexGetUStat()+68] after statistics is collected using GLOBAL_STATS option. Apply patch 13537901 available on My Oracle Support. For details on downloading patches from My Oracle Support, see Section *, "Finding Information and Patches on My Oracle Support".

ORA-01008

ORA-01008: not all variables bound in parallel queries while running a Cohort Report.

• For Exadata systems, apply patch 17258090 available on My Oracle Support. For details on downloading patches from My Oracle Support, see Section *, "Finding Information and Patches on My Oracle Support".

• For Linux 64-bit and Linux x86-64 systems, apply patch 18332139 available on My Oracle Support. For details on downloading patches from My Oracle Support, see Section *, "Finding Information and Patches on My Oracle Support".

2.8 Login Query Performance Fix

The following SQL was observed to be a high consumer of CPU resources in Performance Benchmarking runs.

Resolution

To tune the performance of the Login Query, use Oracle's DBMS_SQLTUNE feature. See the Oracle documentation available at the following location to create and manage plan baselines.

https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_sqltun.htm#CHDFECGH

Verification

To verify that the Login Query performance issue has been fixed, generate AWRs after running a representative workload on the system. The Login Query should not appear as a top resource consumer anymore.