The internal HDR data model is changed significantly in this release. To migrate your data, run scripts in the order given below. If you want to use any of the class subtypes supported in HDR 8.0, edit one of the migration scripts; see Section 7.3, "Categorize Observations, Medications, Supplies, and Control Acts (Optional)".
Oracle WebLogic Server 12c (12.2.1.2.0)
Oracle Database 12c (12.2.0.1.0)
JDK 8u121 and later
Oracle Linux 7(x64)
Windows 10/7/2008 for Dev and Testing
Install JDK.
Install Oracle WebLogic Server.
Install Oracle Database.
Set the JAVA_HOME environment variable.
Install Cygwin 10.0 or above open source tool if you work on Windows.
Download the hdr-8.0.0-SNAPSHOT.zip file and unzip it to the <hdr-package-path> directory. The topmost folder of this directory will be hdr-8.0.0-SNAPSHOT.
Launch the Cygwin Terminal if you work on Windows.
Copy hdr-8.0.0-SNAPSHOT.zip from the HDR software you downloaded to a working directory on the database server.
Unzip the file.
Migration scripts will promt you to enter your credentials.
HDR 8.0 supports grouping data in certain general classes into subtypes, or categories. These categories follow the HL7 FHIR standard described here: https://www.hl7.org/fhir/codesystem-observation-category.html. You may need all, some, or none of these categories.
HDR 8.0 has a separate table to store data for each category. For more information about the tables, see the HDR Technical Reference Manual (eTRM) on the Oracle Help Center at https://docs.oracle.com/health-sciences/health-hdr-80/hdr-docs.htm.
To use categories, it must be possible to identify them in your HDR data. For example, to identify lab observations you may use ETS classifications or an attribute on the observation itself, like title.
You must edit the hdr8_acts_migration.sql migration script with appropriate filter conditions to separate the data into category tables.
In the tables below, find the table name that stores data for a category you want to use. See Observation categories, Medication (Substance Administration) categories, Supply categories, or Control Act categories.
Open hdr8_acts_migration.sql in a text editor.
Search for: AND 1 = 0 --modify the logic.
Replace 1 = 0 --modify the logic with the filter condition and logic to write data to the appropriate table.
For example, for extracting only laboratory observations into OHF_HDR_LAB_OBSEVN_RES table, customers who currently use ETS Classifications to main laboratory terminology codes can replace the condition 1=0 with the below condition with appropriate changes.
AND ACT_CODE_ETS_ID IN (
SELECT CONCEPT_ID FROM HCT_ET_CLSSFCTN_DCLRNS
WHERE CLASSIFICATION_ID = (
SELECT CONCEPT_ID FROM HCT_ET_CONCEPTS_V
WHERE CODINGSCHEME_NAME = 'ETSClassifications'
AND CONCEPT_CODE = 'LAB_RESULT'
)
);
Any data that is not categorized will be classified under the general category. Once the data is migrated to the category-specific tables, the RIM APIs support retrieval and persistence of data into the same tables using a special category attribute.
There are Observation categories for mood codes EVN, RQO, and DEF.
Table 7-1 Categories for mood code EVN
| Category (Subtype) | Table name |
|---|---|
|
General Observation Event |
OHF_HDR_OBS_EVN_RES |
|
Social History |
OHF_HDR_SOCHIST_OBSEVN_RES |
|
Vital Sign |
OHF_HDR_VITAL_OBSEVN_RES |
|
Laboratory |
OHF_HDR_LAB_OBSEVN_RES |
|
Procedure |
OHF_HDR_PROC_OBSEVN_RES |
|
Survey |
OHF_HDR_SURVEY_OBSEVN_RES |
|
Exam |
OHF_HDR_EXAM_OBSEVN_RES |
|
Therapy |
OHF_HDR_THERAPY_OBSEVN_RES |
|
Allergy |
OHF_HDR_ALRGY_OBSEVN_RES |
|
Diagnostic Report |
OHF_HDR_DIAGRPT_OBSEVN_RES |
|
Risk Assessment |
OHF_HDR_RISK_OBSEVN_RES |
If you want to setup HDR8.0 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.0 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
Preliminary setup:
Set the CONFIG_HOME environment variable (for example: <hdr-package-path>/hdr-8.0.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.0):
cd $CONFIG_HOME/config/HDR7.x_to_HDR8.0
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.0 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
Preliminary setup:
The hdr-ets-8.0.0-SNAPSHOT.zip file is in <hdr-package-path>/hdr-8.0.0-SNAPSHOT/ets directory.
Unzip hdr-ets-8.0.0-SNAPSHOT.zip to your work folder <hdr-package-path>/hdr-8.0.0-SNAPSHOT/ets/hdr-ets-8.0.0-SNAPSHOT.
Set the ETS_HOME environment variable. For example:
ETS_HOME = <hdr-package-path>/hdr-8.0.0-SNAPSHOT/ets/hdr-ets-8.0.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.0):
cd $ETS_HOME/db/HDR7.x_to_HDR8.0
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.0 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.
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.0.0-SNAPSHOT/db
cd $HDR_HOME/hdr-core/HDR7.x_to_HDR8.0
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.
Preliminary setup
Set environment variable HDR_HOME. For example:
<hdr-package-path>/hdr-8.0.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.
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
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.0
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.0 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.0.0-SNAPSHOT/db
cd $HDR_HOME/hdr-core/HDR7.x_to_HDR8.0
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.
To cleanup the HCT schema objects:
cd $ETS_HOME/db/HDR7.x_to_HDR8.0
Execute clean_hct.sh.
To drop the old CTB schema:
cd $HDR_HOME/hdr-core/HDR7.x_to_HDR8.0
Execute drop_ctb.sh.
Make a backup of the existing HTB 6.x schemas for CTB and HCT. Export the following tables from the APPLSYS schema to a dump.
Create the following tablespaces on the HDR8.0 target database by connecting as the system user:
Tablespace for CTB user(CTB_TBS).
Tablespace for HCT user(HCT_TBS).
Tablespace for Context(CTX_TBS).
Tablespace for Indexes(IDX_TBS).
Create the HCT and CTB schemas users by executing the following script:
Example 7-3 Create HCT user 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;
Example 7-4 Create CTB user 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;
While still connected as a system user, execute the CREATE OR REPLACE DIRECTORY HTB_DMP_DIR as '<path>' script to create the HTB_DUMP_DIR directory on the Oracle database where the HTB 6.x dump file is located.
Import the HCT objects from HTB 6.x to the target HCT schema.
Example 7-5 Import HCT 6.x objects to 8.0 schema
impdp system/<password> schemas=hct remap_schema=apps:hct remap_ schema=applsys:hct remap_tablespace=APPS_TS_SEED:hct_tbs remap_tablespace=APPS_TS_TX_DATA:<hct_tbs> remap_tablespace=APPS_TS_TX_IDX:<idx_tbs> remap_tablespace=APPS_TS_NOLOGGING:<hct_tbs> remap_tablespace=APPS_TS_MEDIA:<hct_tbs> remap_tablespace=APPS_TS_INTERFACE:<hct_tbs> remap_tablespace=APPS_TS_QUEUES:<hct_tbs> remap_tablespace=APPS_TS_ARCHIVE:<hct_tbs> remap_tablespace=APPS_TS_SUMMARY:<hct_tbs> remap_tablespace=APPS_TS_TOOLS:<hct_tbs> directory=HTB_DMP_DIR dumpfile=< HTB 6.X dump file name >.dmp logfile=htb_hct_import.log
Note:
Make sure that the appropriate tablespace names, dump path, and logfile path are mentioned in the script.Import the CTB objects from HTB 6.X to the target CTB schema. Refer to the HCT migration script from above, and replace HCT with CTB where this applies.
Import the tables from APPLSYS from HTB 6.X to the HCT schema on the target database. Execute the following script to import the tables.
Example 7-6 Import tables from APPLSYS from HTB 6.X on the target database
impdp system/<password> tables=APPLSYS.FND_LANGUAGES,APPLSYS.FND_USER,APPLSYS.FND_ APPLICATION,APPLSYS.FND_PROFILE_OPTIONS,APPLSYS.FND_PROFILE_OPTIONS_ TL,APPLSYS.FND_PROFILE_OPTION_VALUES remap_schema=apps:hct remap_schema=applsys:hct remap_tablespace=APPS_TS_SEED:<hct_tbs> remap_tablespace=APPS_TS_TX_DATA:<hct_tbs> remap_tablespace=APPS_TS_TX_IDX:<idx_tbs> remap_tablespace=APPS_TS_NOLOGGING:<hct_tbs> remap_tablespace=APPS_TS_MEDIA:<hct_tbs> remap_tablespace=APPS_TS_INTERFACE:<hct_tbs> remap_tablespace=APPS_TS_QUEUES:<hct_tbs> remap_tablespace=APPS_TS_ARCHIVE:<hct_tbs> remap_tablespace=APPS_TS_SUMMARY:<hct_tbs> remap_tablespace=APPS_TS_TOOLS:<hct_tbs> directory=HTB_DMP_DIR dumpfile=<HTB 6.X dump file name>.dmp logfile=hct_fnd_import.log
Note:
Ignore the following errors while importing the dump:
User or role <role/user> does not exist. Object type OBJECT_GRANT failed to create with error. The APPLSYS schema does not exist.
Preliminary setup:
Set the CONFIG_HOME environment variable (example: <hdr-package-path>/hdr-8.0.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 6.x and new refers to HDR 8.0):
cd $CONFIG_HOME/config/HDR6.x_to_HDR8.0
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.0 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
Preliminary setup:
The hdr-ets-8.0.0-SNAPSHOT.zip file is in <hdr-package-path>/hdr-8.0.0-SNAPSHOT/ets directory.
Unzip hdr-ets-8.0.0-SNAPSHOT.zip to your work folder <hdr-package-path>/hdr-8.0.0-SNAPSHOT/ets/hdr-ets-8.0.0-SNAPSHOT.
Set the ETS_HOME environment variable. For example:
ETS_HOME = <hdr-package-path>/hdr-8.0.0-SNAPSHOT/ets/hdr-ets-8.0.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 6.x and new refers to HDR 8.0):
cd $ETS_HOME/db/HDR6.x_to_HDR8.0
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.0 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.
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 the HDR_HOME environment variable. For example:
<hdr-package-path>/hdr-8.0.0-SNAPSHOT/db
cd $HDR_HOME/hdr-core/HDR6.x_to_HDR8.0
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.
Preliminary setup
Set environment variable HDR_HOME. For example:
<hdr-package-path>/hdr-8.0.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.
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
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 HDR6 (CTB) schema to the HDR8 (HDR) schema:
cd $HDR_HOME/hdr-core/HDR6.x_to_HDR8.0
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.0 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.0.0-SNAPSHOT/db
cd $HDR_HOME/hdr-core/HDR6.x_to_HDR8.0
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.
To cleanup the HCT schema objects:
cd $ETS_HOME/db/HDR6.x_to_HDR8.0
Execute clean_hct.sh.
To drop the old CTB schema:
cd $HDR_HOME/hdr-core/HDR6.x_to_HDR8.0
Execute drop_ctb.sh.