To Migrate from HDR 7.x

(Optional) Set up the HDR8 Database on a new Database machine

If you want to setup HDR 8.1 on a new Database machine, perform the following:

Make a backup of the existing HDR 7.x schemas for CTB and HCT, and export the CTB and HCT schemas to a dump. For example:
expdp system/<password>@<SID> schemas=CTB,HCT exclude=GRANT,USER,STATISTICS,TABLESPACE_QUOTA,DEFAULT_ROLE directory=hdr_dmp_dir dumpfile=<dump file name>.dmp logfile=hdr_dump.log
Copy the dump files to the new database machine.
Create the following tablespaces on the HDR8 target database by connecting as the sys user:
- Tablespace for the CTB user (CTB_TBS)
- Tablespace for the HCT user (HCT_TBS)
- Tablespace for Context (CTX_TBS)
- Tablespace for Indexes (IDX_TBS)
For example:

Create TABLESPACE <tablespace name> LOGGING DATAFILE '<data file path>/hdr_hcttb.dbf' SIZE <100M> AUTOEXTEND ON NEXT <100M> MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;
Create the HCT and CTB schema users on the HDR8 target database by connecting as the sys user:
- Create user HCT identified by hct default tablespace HCT_TBS quota unlimited on HCT_TBS:
  ALTER USER HCT QUOTA unlimited ON IDX_TBS; ALTER USER HCT QUOTA unlimited ON CTX_TBS; GRANT UNLIMITED TABLESPACE TO HCT; GRANT CREATE INDEXTYPE to HCT; GRANT CREATE PROCEDURE to HCT; GRANT CREATE SEQUENCE to HCT; GRANT CREATE SESSION to HCT; GRANT CREATE TABLE to HCT; GRANT CREATE TYPE to HCT; GRANT CREATE VIEW to HCT; GRANT ALTER SESSION to HCT; GRANT ANALYZE ANY to HCT; GRANT EXECUTE on CTXSYS.ctx_ddl to HCT; GRANT Create Any Job to HCT; GRANT Create External Job to HCT; GRANT Create Job to HCT; GRANT Execute Any Class to HCT; GRANT Execute Any Program to HCT; GRANT Manage Scheduler to HCT; GRANT CREATE ANY DIRECTORY TO HCT;
- Create user CTB identified by ctb default tablespace CTB_TBS quota unlimited on CTB_TBS:
  ALTER USER CTB QUOTA unlimited ON IDX_TBS; GRANT UNLIMITED TABLESPACE TO CTB; GRANT CREATE INDEXTYPE to CTB; GRANT CREATE PROCEDURE to CTB; GRANT CREATE SEQUENCE to CTB; GRANT CREATE SESSION to CTB; GRANT CREATE TABLE to CTB; GRANT CREATE TYPE to CTB; GRANT CREATE VIEW to CTB; GRANT CREATE SYNONYM to CTB; GRANT ALTER SESSION to CTB; GRANT ANALYZE ANY to CTB; GRANT EXECUTE ON SYS.DBMS_AQIN TO CTB; GRANT EXECUTE ON SYS.DBMS_AQADM TO CTB; GRANT Create Any Job to CTB; GRANT Create External Job to CTB; GRANT Create Job to CTB; GRANT Execute Any Class to CTB; GRANT Execute Any Program to CTB; GRANT Manage Scheduler to CTB; GRANT create any directory to CTB; GRANT CREATE ANY DIRECTORY TO CTB;
Execute the following script (by connecting as sys user) to create the HTB_DUMP_DIR directory on the target HDR8.1 Oracle database where the HDR 7.x dump file is located:
CREATE OR REPLACE DIRECTORY HDR_DMP_DIR as '<path>';
Import the HCT objects from HDR 7.x to the target HCT schema. For example, execute the following script to import the HCT objects:
impdp system/<password> exclude=GRANT,USER,STATISTICS,TABLESPACE_QUOTA,DEFAULT_ROLE schemas=hct directory=HDR_DMP_DIR dumpfile=< HDR7.x dump file name >.dmp logfile=hdr_hct_import.log
Import the CTB objects from HDR7.x to the target CTB schema. For example, execute the following script to import the CTB objects:
impdp system/<password> exclude=GRANT,USER,STATISTICS,TABLESPACE_QUOTA,DEFAULT_ROLE schemas=ctb directory=HDR_DMP_DIR dumpfile=< HDR7.x dump file name >.dmp logfile=hdr_ctb_import.log

HCT/CTB to HDR_CONFIG migration (inter-schema)

Preliminary setup:

Set the CONFIG_HOME environment variable (for example: <hdr-package-path>/hdr-8.1.0-SNAPSHOT/db).
Make sure that at least the tablespaces for HDR_CONFIG user are created. You can create these by running $CONFIG_HOME/config/create_config_tablespace.sh. For example:
sh $CONFIG_HOME/config/create_config_tablespace.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT> <TABLE_SPACE_LOCATION>

The script creates the HDR_CONFIG_TBS and HDR_CONFIG_IDX_TBS table spaces for the HDR_CONFIG user.
(Optional) Create the HDR_CONFIG user by running $CONFIG_HOME/config/create_config_user.sh. For example:
sh $CONFIG_HOME/config/create_config_user.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT>

The user creation is also handled by the migrate_config.sh script as described below.

Run the scripts by following the steps below (note: if mentioned anywhere, old refers to HDR 7.x and new refers to HDR 8.1):

cd $CONFIG_HOME/config/HDR7.x_to_HDR8.1
Execute migrate_config.sh
At the "Have you already created schema/user HDR_CONFIG ? [Y/N] (default=N): " prompt in the terminal window:
- If a user is already created for the HDR_CONFIG schema, enter Y and continue.
- If a user is not created for the HDR_CONFIG schema, enter N and continue.
- In either case, the password for the HDR_CONFIG user will be asked.
The console displays the default options set in the script (the password will always be asked by prompt). You can manually customize these parameters by editing migrate_config.sh.
At the "Use Default Migrate Config Database Options[Y/N] (default=Y): " prompt in the terminal window:
- If you choose 'Y', the console will ask for the password of sysdba user. The default parameters will be used for running the script.
- If you choose 'N', you need to enter the values by following the prompts on the terminal window.

Once the script runs successfully, the following changes will be made to support the HDR 8.1 code:

User: If the option to create HDR_CONFIG user was selected, a new HDR_CONFIG user is created with the provided password.
Sequences: New sequences will be created in the HDR_CONFIG schema and initialized to the last value of the old sequences (from both HCT and CTB schemas, as applicable).
Tables: New tables will be created in the HDR_CONFIG schema by copying the data from old tables (from both HCT and CTB schemas, as applicable).
Indexes: New indexes will be created in HDR_CONFIG schema.
The HDR_CONFIG schema will be compiled to verify if the migration was successful.

All the logs will be found in the log directory

HCT to ETS migration (inter-schema)

Preliminary setup:

The hdr-ets-8.1.0-SNAPSHOT.zip file is in <hdr-package-path>/hdr-8.1.0-SNAPSHOT/ets directory.
Unzip hdr-ets-8.1.0-SNAPSHOT.zip to your work folder <hdr-package-path>/hdr-8.1.0-SNAPSHOT/ets/hdr-ets-8.1.0-SNAPSHOT.
Set the ETS_HOME environment variable. For example:
ETS_HOME = <hdr-package-path>/hdr-8.1.0-SNAPSHOT/ets/hdr-ets-8.1.0-SNAPSHOT
Make sure that the tablespaces for the ETS user are created. You can create these by running $ETS_HOME/db/create_tablespace.sh. For example:
$ETS_HOME/db/create_tablespace.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT> <TABLE_SPACE_LOCATION> <ETS_DEFAULT_TABLESPACE_NAME> <ETS_CONTEXT_TABLESPACE_NAME> <ETS_INDEX_TABLESPACE_NAME>

$ETS_HOME/db/create_tablespace.sh /scratch/u01/app/oracle/product/12201 sys localhost servicename 1521 /scratch/u01/app/oracle/oradata OHF_ETS_TBS OHF_ETS_CTX_TBS OHF_ETS_IDX_TBS

In the above example, the script creates the OHF_ETS_TBS, OHF_ETS_CTX_TBS, and OHF_ETS_IDX_TBS table spaces for the ETS user.
Create the ETS user by running $ETS_HOME/db/create_ets_user.sh. For example:
sh $ETS_HOME/db/create_ets_user.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT> <ETS_DEFAULT_TABLESPACE_NAME> <ETS_CONTEXT_TABLESPACE_NAME> <ETS_INDEX_TABLESPACE_NAME>

$ETS_HOME/db/create_ets_user.sh /scratch/u01/app/oracle/product/12201 sys localhost servicename 1521 OHF_ETS_TBS OHF_ETS_CTX_TBS OHF_ETS_IDX_TBS

Run the scripts by following the steps below (note: if mentioned anywhere, old refers to HDR 7.x and new refers to HDR 8.1):

cd $ETS_HOME/db/HDR7.x_to_HDR8.1
Execute migrate_ets.sh.
The console will display the default options set in the script (the password will always be asked by prompt). You can manually customize these parameters by editing migrate_ets.sh.
At the "Use Default HCT To ETS Migration Options[Y/N] (default=Y): " prompt in terminal window:
- If you choose 'Y', the console will ask for the password of the sysdba user and ets schema user. Default parameters will be used for running the script.
- If you choose 'N', you must enter the values by following the prompts on the terminal window.

Once the script runs successfully, the following changes will be made to support the HDR 8.1 code:

Sequences: New sequences will be created and initialized to the last values of the old sequences.
Tables: New tables will be created.
Types: New types will be created.
Packages/Package Bodies: New packages/package bodies will be created.
Views: New views will be created.
Indexes: New indexes will be created.
Synonyms: Synonyms of the required objects from the HDR_CONFIG schema will be created in the schema.
Jobs/Programs: Old jobs/programs will be dropped and new jobs/programs will be created.
The ETS schema will be compiled to verify if the migration was successful.

All the logs will be found in the log directory.

CTB to HDR migration (inter-schema)

Pre-migration scripts

Before running the migration scripts, it is advisable to run the pre-migration scripts to check any data inconsistency or incorrectness. Such incorrect data shall not be handled by the migration scripts. You can correct the data based on the guidelines below.

Set environment variable HDR_HOME. For example:
<hdr-package-path>/hdr-8.1.0-SNAPSHOT/db
cd $HDR_HOME/hdr-core/HDR7.x_to_HDR8.1
execute pre_migrate_hdr.sh
The console will display the default options set in the script (the password will always be asked by prompt). You can manually customize these parameters by editing pre_migrate_hdr.sh.
At the "Use Default Options[Y/N] (default=Y): " prompt in the terminal window:
- If you choose 'Y', the console will ask for the password of the CTB schema user. Default parameters will be used for running the script.
- If you choose 'N', you need to enter the values by following the prompts on the terminal window.

The script logs and cleans up "possibly incorrect data". Such incorrect data will be logged in the log/*incorrect_data.log files.

The the reasons for incorrect data are:

entities_incorrect_data.log: NAME_PART_TYPE_CODE of the CTB_CORE_ENTY_NAME_PARTS is NULL
roles_incorrect_data.log: NAME_PART_TYPE_CODE of the CTB_CORE_ROLE_NAME_PARTS is NULL

All the logs will be found in the log directory.

Migration scripts

Preliminary setup

Set environment variable HDR_HOME. For example:
<hdr-package-path>/hdr-8.1.0-SNAPSHOT/db
Make sure that the tablespaces for the HDR user are created. You can create these by running HDR_HOME/hdr-core/create_tablespace.sh.
1. If you want to create tablespaces per domain, run the script in the form:
  sh $HDR_HOME/hdr-core/create_tablespace.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT> <TABLE_SPACE_LOCATION> <NEED_TABLESPACE_PER_DOMAIN> <DEFINE_20_TABLESPACES_NAMES>.....
  
  For example:
  
  $HDR_HOME/hdr-core/create_tablespace.sh /scratch/u01/app/oracle/product/12201 sys localhost servicename 1521 /scratch/u01/app/oracle/oradata Y OHF_HDR_CLIN_CUR_TBS OHF_HDR_CLIN_HIS_TBS OHF_HDR_FIN_CUR_TBS OHF_HDR_FIN_HIS_TBS OHF_HDR_ADM_CUR_TBS OHF_HDR_ADM_HIS_TBS OHF_HDR_INF_CUR_TBS OHF_HDR_INF_HIS_TBS OHF_HDR_IDN_CUR_TBS OHF_HDR_IDN_HIS_TBS OHF_HDR_CLIN_CUR_IDX_TBS OHF_HDR_CLIN_HIS_IDX_TBS OHF_HDR_FIN_CUR_IDX_TBS OHF_HDR_FIN_HIS_IDX_TBS OHF_HDR_ADM_CUR_IDX_TBS OHF_HDR_ADM_HIS_IDX_TBS OHF_HDR_INF_CUR_IDX_TBS OHF_HDR_INF_HIS_IDX_TBS OHF_HDR_IDN_CUR_IDX_TBS OHF_HDR_IDN_HIS_IDX_TBS
2. If you don't want to create tablespaces per domain, run the script in the form:
  sh $HDR_HOME/hdr-core/create_tablespace.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT> <TABLE_SPACE_LOCATION> <NEED_TABLESPACE_PER_DOMAIN> <CURRENT_DATA_TABLESPACE_NAME> <HISTORY_DATA_TABLESPACE_NAME> <CURRENT_INDEX_TABLESPACE_NAME> <HISTORY_INDEX_TABLESPACE_NAME>
  
  For example:
  
  $HDR_HOME/hdr-core/create_tablespace.sh /scratch/u01/app/oracle/product/12201 sys localhost servicename 1521 /scratch/u01/app/oracle/oradata N OHF_HDR_CUR_TBS OHF_HDR_HIS_TBS OHF_HDR_CUR_IDX_TBS OHF_HDR_HIS_IDX_TBS
Create the HDR user by running $HDR_HOME/hdr-core/create_hdr_user.sh:
sh $HDR_HOME/hdr-core/create_hdr_user.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT>

For example:

sh $HDR_HOME/hdr-core/create_hdr_user.sh /scratch/u01/app/oracle/product/12201 sys localhost servicename 1521

Follow the steps below to migrate the data from the HDR7 (CTB) schema to the HDR8 (HDR) schema:

cd $HDR_HOME/hdr-core/HDR7.x_to_HDR8.1
Execute migrate_hdr.sh.
At the "Are the tablespaces created per domain?[Y/N] (default=N):" console prompt:
- Enter 'Y' if the tablespaces are available domain wise. Otherwise enter 'N'.
- Based the response, the appropriate number of tablespace names will be prompted.
The console will display the default options set in the script (password will always be asked by prompt). You also can manually customize these parameters by editing migrate_hdr.sh.
At the "Use Default CTB To HDR Migration Options[Y/N] (default=Y): " prompt in the terminal window:
- If you choose 'Y', the console will ask for the password of the sysdba user and HDR schema user. Default parameters will be used for running the script.
- If you choose 'N', you need to enter the values by following the prompts on the terminal window.

Once the script runs successfully, the following changes will be made to support the HDR 8.1 code:

Synonyms: Synonyms will be created in the HDR schema for the objects in the HCT/ETS schema and HDR_CONFIG schema.
Sequences: New sequences will be created in the HDR schema.
Types: New types will be created in the HDR schema.
Tables and Indexes: Tables and indexes will be created in the HDR schema by pulling the data from the CTB schema. The tables will be created for acts, roles, entities, act relationships, and participation objects.
Views: New views will be created in the HDR schema.
The HDR schema will be compiled.

All the logs will be saved in the log directory. Any error data will be output in the log files, so that users can check and take appropriate action.

Validate the migration scripts

Once the migration script is run, you can verify if the migration was successful. This can be done by following below steps:

Set the HDR_HOME environment variable, for example:
<hdr-package-path>/hdr-8.1.0-SNAPSHOT/db
cd $HDR_HOME/hdr-core/HDR7.x_to_HDR8.1
Execute validate_migrate_hdr.sh.
The console will display the default options set in the script (the password will always be asked by prompt). You can manually customize these parameters by editing validate_migrate_hdr.sh.
At the "Use Default Options[Y/N] (default=Y): " prompt in terminal window:
- If you choose 'Y', the console will ask for the password of the HDR schema user. Default parameters will be used for running the script.
- If you choose 'N', you need to enter the values by following the prompts on the terminal window.

After the script is run, the following will be validated:

Count: The count of the total rows for each table in the HDR schema will be validated against the count of rows of corresponding table in CTB. Any issue will be logged to the log/count_validation.log file.
Length: The length of some records in the HDR schema tables will be validated against the count of rows of corresponding table in CTB. Any issue will be logged to the log/*_row_validation.log files (where * can be act, entity, role, etc).

The consolidated log of above will be saved in validate_migrate_to_hdr.sh.log.

Clean up old objects in the HCT and CTB schemas

To cleanup the HCT schema objects:

cd $ETS_HOME/db/HDR7.x_to_HDR8.1
Execute clean_hct.sh.

To drop the old CTB schema:

cd $HDR_HOME/hdr-core/HDR7.x_to_HDR8.1
Execute drop_ctb.sh.