Install the Data Model with Pre-created User Schemas

This section describes how to install the Data Model by creating Oracle Healthcare Foundation user schemas and tablespaces outside the installer, then using the installer to create database objects in the respective user schemas. Please complete the following:

• Check Prerequisites
  
• Oracle Healthcare Foundation Tablespaces
  
• Temporary Tablespaces
  
• Prepare the Installation Files
  
• Create Database Roles
  
• Create User Schemas
  
• Run the Installation Scripts
  
• Start the Installer
  
• Run the Installer
  
• Check the Installation
  
• Revoke Privileges
  

Check Prerequisites

-	Prerequisites
	Make sure Oracle Database Enterprise Edition 19c (19.3 or above) with the latest patches is installed.
	Make sure that the database compatible parameter is set to 19.3.0.0.0 by connecting to the DBA user and running the query below: select * from v$parameter where name = 'compatible'; If the parameter is not set to 19.3.0.0.0, ask your database administrator to set it.
	If you are installing Oracle Healthcare Foundation on an Exadata environment, apply the Oracle database patch 19562381 (Doc ID 19562381.8) and patch 31182793.
	Make sure the database initialization parameter MAX_STRING_SIZE is set to STANDARD (default value). This parameter cannot be changed to STANDARD if the database was created with MAX_STRING_SIZE set to EXTENDED.
	Set the NLS_LENGTH_SEMANTICS parameter to either CHAR or BYTE, based on your requirements. For Oracle Healthcare Foundation Globalization Support information, see Oracle Database Globalization Support Guide and set your database character set accordingly.
	Make sure that the password expiry notification message does not display for the sys and system schemas.
	To create an encrypted tablespace during the installation, set up your environment for tablespace encryption. For more information, see Oracle Database Advanced Security Guide 12.2. The installer only creates the tablespace and does not handle prerequisites like setting up the keystore, opening it, and so forth. The installer only checks whether the keystore is open if the sys and system passwords are provided during the installation.
	Verify if you have a database license for Advanced Compression.
	Set the GLOBAL_NAMES database initialization parameter to false.
	If you are installing Oracle Healthcare Foundation on an Exadata database machine, see the Exadata Machine and Exadata Implementation sections in the Oracle Healthcare Foundation Programmer's Guide.
	For remote installations, make sure the following are available on the client machine: Oracle Linux 6.7 (64-bit) OS or above Oracle Database Enterprise Edition 19c (19.3) with the latest patches
	Enable a database connection through SERVICE_NAME. Make sure that you are connecting to the pluggable database (PDB) if you are installing in a container-based environment. Verify the database connectivity using the following commands: SQL>connect username@hostname:port number/service name or sqlplus username@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost)(PORT=dbport))(CONNECT_DATA=(SERVICE_NAME=<dbservicename>)))'
	Make sure that the Sqlplus utility is available in the installation server.

Oracle Healthcare Foundation Tablespaces

Create the following default tablespaces. You can use different tablespace names than the ones listed below.

-	Tablespace Name	Big File Tablespace	Description
	hdi_ts	Yes	Default tablespace for the Interface Tables (hdi) schema
	hdm_ts	Yes	Default tablespace for the Data Warehouse (hdm) schema
	hcd_ts	Yes	Default tablespace for the Common Data Mart (hcd) schema
	odb_data_ts	Yes	Default tablespace for the Omics Data Bank (odb) schema
	odb_index_ts	Yes	Used for indexes of the Omics Data Bank (odb) schema
	odb_lob_ts	Yes	Used for LOB columns in the Omics Data Bank (odb) schema
	cdm_data_ts	Yes	Default tablespace for the Cohort Data Mart (cdm) schema
	cdm_index_ts	Yes	Used for indexes of the Cohort Data Mart (cdm) schema
	ent_ts	No	Default tablespace for the Enterprise (ent) schema
	job_data_ts	No	Default tablespace for the Job Engine (job) schema
	job_index_ts	No	Used for indexes of the Job Engine (job) schema
	job_store_ts	No	Database File System (DBFS) store created as part of the Job Engine installation
	job_lob_ts	No	Name of the tablespace to store LOB data in the DBFS store created for the JOB ENGINE schema user
	job_tbs_ts	No	Name of the tablespace to be used for the DBFS store created for the JOB_ENGINE schema
	svc_ts	No	Default tablespace used for the Services (svc) schema
	api_ts	No	Default tablespace for the Clinical Genomics (cga) API schema.

Temporary Tablespaces

Oracle Healthcare Foundation loaders and ETLs require a large temporary tablespace for sorting. A dedicated bigfile temporary tablespace or a single temporary tablespace group should be assigned to all Oracle Healthcare Foundation schemas.

Prepare the Installation Files

-	Preparations
	Extract the contents of the Oracle Healthcare Foundation media pack to your system.
	Open the media_pack_location folder.
	Unzip the OHF_V821_Linux-x64.zip file where you want to launch the installer using the following command: unzip -a OHF_V821_Linux-x64.zip

Create Database Roles

-	Instructions
	Navigate to the directory <media_pack_location>/Disk1/stage/Components/oracle.hsgbu.hc.datamodel/8.2.2.0.0/1/DataFiles/Expanded/filegroup1 .
	Unzip the master_install.zip file.
	Connect to Oracle SYS from the extracted content and create database roles by executing the roles.sql script.

Create User Schemas

Create the following user schemas and assign the appropriate default and temporary tablespaces. Refer to Oracle Healthcare Foundation Tablespaces and Temporary Tablespaces for details. If you created tablespaces with different names, use those names instead.

-	Schema	Default Tablespace	Temporary Tablespace
	Interface Tables (hdi)	hdi_ts	temp
	Data Warehouse (hdm)	hdm_ts	temp
	Common Data Mart (hcd)	hcd_ts	temp
	Omics Data Bank (odb)	odb_data_ts	temp
	Cohort Data Mart (cdm)	cdm_data_ts	temp
	Enterprise (ent)	ent_ts	temp
	Job Engine (job)	job_data_ts	temp
	Services (svc)	svc_ts	temp
	Clinical Genomics (cga)	api_ts	temp

Run the Installation Scripts

Execute the following scripts/commands as a DBA user. When prompted, enter the respective schema names.

When prompted for the application role, enter OHF_APPLICATION_ROLE.

-	Scripts
	@hdi_install/grant_schema_priv.sql
	@hdm_install/grant_schema_priv.sql
	@hcd_install/grant_schema_priv.sql
	@odb_install/grant_schema_priv.sql Execute the following commands: > ALTER USER odb QUOTA UNLIMITED on odb_index_ts > ALTER USER odb QUOTA UNLIMITED on odb_lob_ts
	@cdm_install/grant_schema_priv.sql Execute the following command: > ALTER USER cdm QUOTA UNLIMITED on cdm_index_ts
	@enterprise_install/grant_schema_priv.sql
	@job_install/grant_schema_priv.sql Execute the following commands: > ALTER USER job QUOTA UNLIMITED on job_index_ts > ALTER USER job QUOTA UNLIMITED on job_store_ts > ALTER USER job QUOTA UNLIMITED on job_lob_ts > ALTER USER job QUOTA UNLIMITED on job_tbs_ts
	@svc_install/grant_schema_priv.sql

Start the Installer

-	Steps
	Make sure that all the Oracle Healthcare Foundation schemas are disconnected from the database by querying gv_$session from the sys user: select * from sys.gv_$session where status <> 'KILLED' and username in ('<OHF Schemas>');
	Navigate to the media_pack_location/Disk1/install folder.
	Change the protection on the files as follows: chmod 755 *
	Start the Oracle Universal Installer (OUI) using the following command: If the database server is on the machine where the installer is running and is an Exadata instance: sh runInstaller.sh -local dba_tasks=false db_platform=exadata If the database server is on the machine where the installer is running and is a non-Exadata instance: sh runInstaller.sh -local dba_tasks=false db_platform=default If the database server is on a different machine and is an Exadata instance: sh runInstaller.sh -local remote_installation=true dba_tasks=false db_platform=exadata If the database server is on a different machine and is a non-Exadata instance: sh runInstaller.sh -local dba_tasks=false db_platform=default remote_installation=true The -local option tells the installer to install on the local node, irrespective of the cluster nodes specified on the installer machine. The dba_tasks=false option tells the installer not to create user schemas and tablespaces.

Run the Installer

-	Screen	Action
	Welcome	Click Next.
	Select a Product to Install	Select the Oracle Healthcare Foundation Data Model 8.2.2.0.0 option.
	Specify Home Details	Enter the installation home name and location.
	Verify Installation Prerequisites	Verify if all the prerequisites are met before proceeding.
	Oracle Client Home Configuration	Specify the Oracle (version 19.3.0.0.0) client home path. The installer validates this path.
	Database Configuration	Enter values for the following fields: Hostname - By default, the system host name appears. For remote installations, set this value to the host name of the remote machine. Port - By default, the port number is 1521. You can edit this field if required. Service name The installer will not validate the database connectivity. Make sure that you are able to connect to the database from SQLPlus.
	Table Compression	On an Exadata setup, use the following compression options: Interface Tables schema Hybrid columnar compression (default) No Compression Data Warehouse schema No Compression (default) Advanced Compression: Preferred if updates are high. If you don't have a license for Advanced Compression, select Hybrid Columnar Compression. Hybrid Columnar Compression Common Data Mart schema No Compression (default) Advanced Compression Cohort Data Mart schema No Compression (default) Advanced Compression Omics Data Bank schema Hybrid columnar compression (default) On a non-Exadata setup, for each of the above schemas, choose either No Compression (default) or Advanced Compression.
	Data Model Configuration	Enter values for the pre-created schemas: Interface Tables schema name Interface Tables schema password Data Warehouse schema name Data Warehouse schema password Common Data Mart schema name Common Data Mart schema password Omics Data Bank schema name Omics Data Bank schema password
	Data Model Configuration	Enter values for the pre-created schemas: Cohort Data Mart schema name Cohort Data Mart password Enterprise schema name Enterprise schema password Job Engine schema name Job Engine schema password Services schema name Services schema password
	Data Model Configuration	Enter values for the pre-created schemas: Clinical Genomics (cga) schema name Clinical Genomics (cga) password
	Data Model Configuration Verification	Click Next.
	Tablespace Details	Click Next.
	Temporary Tablespace Details	Click Next.
	Omics Data Bank and Cohort Data Mart Parameters	Enter values for the following fields: Result Partition - Used to partition result tables in the ODB schema. The available options are: GENE (Default) Promoter Offset - Numerical value to specify a portion of the gene used as a promoter in the ODB schema. The default value is 200. Flanking Offset - Numerical value to specify the region before and after a gene to link results to a gene in the ODB schema. The default value is 200. Max Parallel Degree - An option to specify the maximum degree of parallelism to be set on tables or used in the SQL statements for the CDM or ODB schema. It is dependent on the machine configuration of the database server. The default value is 2. Job Store Name - Use the job schema name. Execute the following query to find all store names in the database (created by all previous installations) by connecting to the SYS user: SELECT DISTINCT owner,object_type,object_name,created FROM all_objects WHERE object_name LIKE '%JOB_STORE%'; Note: If you have store names in the database that do not match the %JOB_STORE% pattern, run the following query to find the correct names: SELECT DISTINCT owner,object_type,object_name,created FROM all_objects WHERE owner =Job Username/schemaname AND object_type IN ('TABLE','VIEW');
	Tablespace Parameters	Enter values for the fields below from the existing or pre-created CDM, ODB, and Job engine schemas. Make sure that these tablespaces exist or were created before the installation. Refer to Oracle Healthcare Foundation Tablespaces for the tablespace names to be used. cdm_index_ts_name odb_index_ts_name odb_lob_ts_name job_index_ts_name job_lob_ts_name job_tbs_ts_name
	Summary	Click Install.
	End of Installation	Click Exit after reviewing the installation information. At the confirmation prompt, click Yes to exit the installer.

Check the Installation

-	Steps
	Review the generated installation log files for errors. For details, see Installation Log Files.
	Contact Oracle support, if necessary, to resolve any errors.

Revoke Privileges

After the Cohort Data Mart (CDM) and Omics Data Bank (ODB) data model installation, connect to the database user with the DBA privilege, and execute the following scripts to revoke privileges:

REVOKE CREATE ANY DIRECTORY FROM odb user

REVOKE CREATE ANY CONTEXT FROM cdm user

where,

odb_user is the ODB schema name.

cdm_user is the CDM schema name.