To Migrate from HDR 6.x

Upgrade the HTB 6.x Database Schema to HDR 8.1

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.1 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:
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.

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.
Note:
- Before importing the dumps to the target database, ensure that there is enough tablespace available on the target database.
- 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.
```

Example 7-1 Execute a dump

expdp system/<password>@<SID> schemas=CTB,HCT,APPLSYS exclude=GRANT,USER,STATISTICS,TABLESPACE_QUOTA,DEFAULT_ROLE directory=htb_dmp_dir dumpfile=<dump file name>.dmp logfile=htb_dump.log.

Example 7-2 Script to create migration tablespaces

CREATE TABLESPACE <tablespace name>
 
        LOGGING
 
        DATAFILE '<data file path>/hdr_hcttb.dbf'
 
        SIZE <100M>
 
        AUTOEXTEND ON
 
        NEXT <100M> MAXSIZE UNLIMITED
 
        EXTENT MANAGEMENT LOCAL;

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;

Example 7-5 Import HCT 6.x objects to 8.1 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

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

HCT/CTB to HDR_CONFIG migration (inter-schema)

Preliminary setup:

Set the CONFIG_HOME environment variable (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 6.x and new refers to HDR 8.1):

cd $CONFIG_HOME/config/HDR6.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 6.x and new refers to HDR 8.1):

cd $ETS_HOME/db/HDR6.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 the HDR_HOME environment variable. For example:
<hdr-package-path>/hdr-8.1.0-SNAPSHOT/db
cd $HDR_HOME/hdr-core/HDR6.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 HDR6 (CTB) schema to the HDR8 (HDR) schema:

cd $HDR_HOME/hdr-core/HDR6.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/HDR6.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/HDR6.x_to_HDR8.1
Execute clean_hct.sh.

To drop the old CTB schema:

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