1. Installation Guide
  2. Upgrade
  3. Data Model Upgrade
  4. Check Prerequisites

Check Prerequisites

- Steps

Checkbox

Make sure the Oracle Business Intelligence Enterprise Edition service is shut down.

Checkbox

Make sure Oracle Database 12.2.0.1 Enterprise Edition is installed.

Checkbox

Make sure that the database compatible parameter is set to 12.2.0.1.0 or higher 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 12.2.0.1.0 or higher, ask your database administrator to set it.

Checkbox

If you are upgrading Oracle Healthcare Foundation on an Exadata environment, make sure the Oracle database patch 19562381 (Doc ID 19562381.8) is applied.

Checkbox

Create a container database with a single pluggable database instance with the help of a Database Administrator.

Checkbox

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 is created with MAX_STRING_SIZE set to EXTENDED.

Checkbox

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.

Checkbox

Verify if you have a database license for Advanced Compression.

Checkbox

Make sure the password expiry notification message does not display for the Sys, system and all Oracle Healthcare Foundation schemas.

Checkbox

If you have Oracle Healthcare Translational Research (OHTR) 3.1.x installed, make backups of the CDM, Oracle Database, Enterprise, Job Engine and Apps schemas. Import CDM, ODB, Enterprise, Job Engine, and Apps schemas in the database instance created for Oracle Healthcare Foundation (PDB or non-container database).

The CDM, Oracle Database, Enterprise, and Job Engine schemas are updated as part of the Oracle Healthcare Foundation 8.1 upgrade. The Apps schema is upgraded during the Oracle Healthcare Translational Research 4.0 upgrade.

If you are importing existing Oracle Healthcare Translational Research CDM schema into different named CDM schema, update the table below after importing the dump. Connect to the Oracle Database schema and run the following commands:

  1. select * from W_EHA_DATASOURCE

    In this table, update the CDM schema name to the new CDM schema name.

Checkbox

Before you upgrade to Oracle Healthcare Foundation 8.1, back up the data model schemas of the existing Oracle Healthcare Data Warehouse Foundation (HDWF) environment and the Oracle Healthcare Translational Research schemas (if you have installed older versions of Oracle Healthcare Translational Research).

Checkbox

Identify the manually updated seed data and inconsistent terminology records using the below query and revert the Oracle Healthcare Foundation seed data. 

select crct.cd_id,cr.CD_ID,
                  CR.INTEGRATION_ID,
                  CRCT.INTEGRATION_ID
            from
                  HDM_CD_REPOSITORY_CD_TYP CRCT,
                  HDM_CD_REPOSITORY CR
            where
                  SUBSTR(CRCT.INTEGRATION_ID,1,INSTR(CRCT.INTEGRATION_ID,'~',- 1)- 1)=
                  CR.INTEGRATION_ID
            and CRCT.CD_ID <> CR.CD_ID
            and CRCT.CD_REPOSITORY_CD_TYP_ID <= 50000;

Checkbox

During the upgrade, certain objects are dropped irrespective of prefixes or naming conventions. This includes objects such as packages, stored procedures, views, materialized views, scheduler jobs, and synonyms.

Make a backup of the custom scripts before the upgrade and re-execute the custom scripts for all custom objects after the upgrade.

Checkbox

During the upgrade, HCD materialized views will be dropped to replace the underlying sql logic , this requires all the dependent objects to be dropped. Any custom/peer objects referencing the Oracle Healthcare Foundation HCD materialized views will not be dropped by the installer.

Make sure to drop the custom referential integrity constraints before starting the upgrade. Identify the custom objects using the below query: 

select uc.table_name,
               uc.constraint_name,
               o.object_type
                             from user_constraints uc,
                                           user_objects o
                             where uc.table_name=o.object_name
                             and o.object_type in ('TABLE','VIEW')
                             and uc.constraint_type in ('R') -- R = Referential integrity; U = Unique key; P = Primary key; C = Check constraint
                             and r_constraint_name in
(select constraint_name from user_constraints
where table_name in (select object_name
    from user_objects
    where object_type in ('MATERIALIZED VIEW')
      and object_name like 'W\_HCD\_%' ESCAPE '\'
                and not REGEXP_LIKE(object_name,'_X+')));

Checkbox

Set the GLOBAL_NAMES database initialization parameter to false.

Checkbox

For remote upgrades, make sure the following are available on the client machine:

  • Oracle Linux 6.7 (64-bit) OS or above
  • Oracle Database 12.2.0.1.0 client with the sqlplus utility

Checkbox

Before you run the Oracle Healthcare Foundation installer to upgrade the existing schema, execute the following query as a SYSTEM user to validate if the data model user has OHF_APPLICATION_ROLE as the default role.

select grantee, DEFAULT_ROLE from dba_role_privs where granted_role='OHF_APPLICATION_ROLE' AND DEFAULT_ROLE='NO';

Data model users are schema users that are used during the installation of the Oracle Healthcare Foundation Data Model. If this is not a default role, execute the following command as a SYSTEM user to set this as a default role:

alter user data model user default role all;

Where data model user should be assigned to each of the following users:

  • Data warehouse schema name (HDM)
  • Interface tables schema name (HDI)
  • Common data mart schema name (HCD)
  • Cohort data mart schema name (CDM)
  • Job engine schema name (JOB)
  • Services schema name (SVC)
  • Omics data bank schema name (ODB)

Note: If you don't have Oracle Healthcare Translational Research products installed, assign data_model_user only to the HDM, HDI, and HCD schemas.

Checkbox

Make sure that the SELECT privilege on the ALL_TAB_COLS view is granted to the PUBLIC role.

Log in as a SYS user and execute the following query:

select * from dba_tab_privs where table_name = 'ALL_TAB_COLS';

If the privilege is not found, execute the below query as SYS user to grant the privileges to the PUBIC role:

grant select on SYS.ALL_TAB_COLS to PUBLIC with grant option;

Checkbox

The installer does not validate the tablespace data files location. If the database server is on the remote server, make sure the location physically exists or the installer will fail.

Parent topic: Data Model Upgrade