Migrate HDR 7.0.2.x CTB Schema to HDR 8.2 CTB Schema
The migration will happen within the HDR 7.0.2.x CTB schema itself. Once the migration is completed successfully, the migrated CTB schema can be used for HDR 8.2 CTB schema.
Execute migrate_ctb_db_objects.sh script with MIGRATE_OPTION (1 to 16) to migrate HDR7.0.2.x CTB user database objects to HDR8.2 CTB user database objects.
Table 5-1 Migrate Options
| Migrate Option | Action |
|---|---|
| 1 | Update CTB Sequences |
| 2 | Create CTB temp Tables |
| 3 | Insert into CTB temp Tables from CTB tables |
| 4 | Compare Count Validation |
| 5 | Drop CTB Tables |
| 6 | Rename CTB temp Tables to CTB main tables |
| 7 | Create CTB Types |
| 8 | Create CTB synonyms |
| 9 | Grant All synonyms from HCT |
| 10 | Create Packages for CTB |
| 11 | Create Views for CTB |
| 12 | Create Indexes for CTB |
| 13 | Create OMP default queue, i.e. CTB_OMP_DEFAULT_QUEUE |
| 14 | Persist HDR 8.2 Release Information |
| 15 | Compile CTB schema |
| 16 | Gather stats CTB Schema |
Execute the migrate_ctb_db_objects.sh script in the following order to migrate HDR7.0.2.x CTB schema to HDR8.2 CTB schema.
Navigate to the $HDR_HOME/database/migration_scripts/HDR7.x_HDR82 directory.
Update CTB Sequences
Update the CTB sequences by running $HDR_HOME/database/migration_scripts/HDR7.x_HDR82/migrate_ctb_db_objects.sh script with MIGRATE_OPTION 1.
Example:
sh migrate_ctb_db_objects.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT> <CTB_INDEX_TABLESPACE_NAME> <MIGRATE_OPTION>
sh migrate_ctb_db_objects.sh /scratch/app/oracle/product/19c sys localhost servicename 1521 IDX_TBS 1The script prompts you to enter SYS and CTB user passwords, provide valid values and hit enter. The script updates the sequences for CTB user and initialized to next values.
Create CTB Temp Tables
Once the CTB sequences are updated successfully, create the CTB temp tables by running $HDR_HOME/database/migration_scripts/HDR7.x_HDR82/migrate_ctb_db_objects.sh script with MIGRATE_OPTION 2.
Example:
sh migrate_ctb_db_objects.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT> <CTB_INDEX_TABLESPACE_NAME> <MIGRATE_OPTION>
sh migrate_ctb_db_objects.sh /scratch/app/oracle/product/19c sys localhost servicename 1521 IDX_TBS 2The script prompts you to enter SYS and CTB user passwords, provide valid values and hit enter. The script creates the temp tables for CTB user.
Insert into CTB Temp Tables from CTB Tables
Once the CTB temp tables are created successfully, insert into CTB temp tables by running $HDR_HOME/database/migration_scripts/HDR7.x_HDR82/migrate_ctb_db_objects.sh script with MIGRATE_OPTION 3.
Example:
sh migrate_ctb_db_objects.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT> <CTB_INDEX_TABLESPACE_NAME> <MIGRATE_OPTION>
sh migrate_ctb_db_objects.sh /scratch/app/oracle/product/19c sys localhost servicename 1521 IDX_TBS 3The script prompts you to enter SYS and CTB user passwords, provide valid values and hit enter. The script inserts the data into the temp tables from the CTB tables.
Count Validation
Once the data is migrated to CTB temp tables successfully, verify the count validation by running $HDR_HOME/database/migration_scripts/HDR7.x_HDR82/migrate_ctb_db_objects.sh script with MIGRATE_OPTION 4.
Example:
sh migrate_ctb_db_objects.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT> <CTB_INDEX_TABLESPACE_NAME> <MIGRATE_OPTION>
sh migrate_ctb_db_objects.sh /scratch/app/oracle/product/19c sys localhost servicename 1521 IDX_TBS 4The script prompts you to enter SYS and CTB user passwords, provide valid values and hit enter. The script checks the count validation between CTB temp tables and CTB main tables and record those results into CTB. HDR_CTB_COUNT_VALIDATION_LOG.
Drop CTB Existing Tables
Once the data is migrated to CTB temp tables successfully, drop the existing CTB tables by running $HDR_HOME/database/migration_scripts/HDR7.x_HDR82/migrate_ctb_db_objects.sh script with MIGRATE_OPTION 5.
Example:
sh migrate_ctb_db_objects.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT> <CTB_INDEX_TABLESPACE_NAME> <MIGRATE_OPTION>
sh migrate_ctb_db_objects.sh /scratch/app/oracle/product/19c sys localhost servicename 1521 IDX_TBS 5The script prompts you to enter SYS and CTB user passwords, provide valid values and hit enter. The script drops the CTB existing tables.
Rename CTB Temp Tables to CTB Main Tables
Once the CTB existing tables are dropped successfully, rename the temp CTB tables to CTB main tables by running $HDR_HOME/database/migration_scripts/HDR7.x_HDR82/migrate_ctb_db_objects.sh script with MIGRATE_OPTION 6.
Example:
sh migrate_ctb_db_objects.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT> <CTB_INDEX_TABLESPACE_NAME> <MIGRATE_OPTION>
sh migrate_ctb_db_objects.sh /scratch/app/oracle/product/19c sys localhost servicename 1521 IDX_TBS 6The script prompts you to enter SYS and CTB user passwords, provide valid values and hit enter. The script renames the CTB temp tables to main tables.
Create CTB Types
Once the CTB temp tables are renamed successfully, create CTB types by running $HDR_HOME/database/migration_scripts/HDR7.x_HDR82/migrate_ctb_db_objects.sh script with MIGRATE_OPTION 7.
Example:
sh migrate_ctb_db_objects.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT> <CTB_INDEX_TABLESPACE_NAME> <MIGRATE_OPTION>
sh migrate_ctb_db_objects.sh /scratch/app/oracle/product/19c sys localhost servicename 1521 IDX_TBS 7The script prompts you to enter SYS and CTB user passwords, provide valid values and hit enter. The script creates the CTB types.
Create CTB Synonyms
Once the CTB types are created successfully, create CTB synonyms by running $HDR_HOME/database/migration_scripts/HDR7.x_HDR82/migrate_ctb_db_objects.sh script with MIGRATE_OPTION 8.
Example:
sh migrate_ctb_db_objects.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT> <CTB_INDEX_TABLESPACE_NAME> <MIGRATE_OPTION>
sh migrate_ctb_db_objects.sh /scratch/app/oracle/product/19c sys localhost servicename 1521 IDX_TBS 8The script prompts you to enter SYS and CTB user passwords, provide valid values and hit enter. The script creates the CTB synonyms.
Grant Permission to all Synonyms from HCT
Once the CTB synonyms are created successfully, provide grant permission to all CTB synonyms by running $HDR_HOME/database/migration_scripts/HDR7.x_HDR82/migrate_ctb_db_objects.sh script with MIGRATE_OPTION 9.
Example:
sh migrate_ctb_db_objects.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT> <CTB_INDEX_TABLESPACE_NAME> <MIGRATE_OPTION>
sh migrate_ctb_db_objects.sh /scratch/app/oracle/product/19c sys localhost servicename 1521 IDX_TBS 9The script prompts you to enter SYS and CTB user passwords, provide valid values and hit enter. The script grants permission to all CTB synonyms from HCT.
Create CTB Packages
Once the CTB grant permissions created successfully, create CTB packages by running $HDR_HOME/database/migration_scripts/HDR7.x_HDR82/migrate_ctb_db_objects.sh script with MIGRATE_OPTION 10.
Example:
sh migrate_ctb_db_objects.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT> <CTB_INDEX_TABLESPACE_NAME> <MIGRATE_OPTION>
sh migrate_ctb_db_objects.sh /scratch/app/oracle/product/19c sys localhost servicename 1521 IDX_TBS 10The script prompts you to enter SYS and CTB user passwords, provide valid values and hit enter. The script creates the CTB packages.
Create CTB Views
Once the CTB packages created successfully, create CTB views by running $HDR_HOME/database/migration_scripts/HDR7.x_HDR82/migrate_ctb_db_objects.sh script with MIGRATE_OPTION 11.
Example:
sh migrate_ctb_db_objects.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT> <CTB_INDEX_TABLESPACE_NAME> <MIGRATE_OPTION>
sh migrate_ctb_db_objects.sh /scratch/app/oracle/product/19c sys localhost servicename 1521 IDX_TBS 11The script prompts you to enter SYS and CTB user passwords, provide valid values and hit enter. The script creates the CTB views.
Create CTB Indexes
Once the CTB views created successfully, create CTB indexes by running $HDR_HOME/database/migration_scripts/HDR7.x_HDR82/migrate_ctb_db_objects.sh script with MIGRATE_OPTION 12.
Example:
sh migrate_ctb_db_objects.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT> <CTB_INDEX_TABLESPACE_NAME> <MIGRATE_OPTION>
sh migrate_ctb_db_objects.sh /scratch/app/oracle/product/19c sys localhost servicename 1521 IDX_TBS 12The script prompts you to enter SYS and CTB user passwords, provide valid values and hit enter. The script creates the CTB indexes.
Create OMP Default Queue CTB_OMP_DEFAULT_QUEUE
Once the CTB indexes created successfully, create CTB OMP default queue by running $HDR_HOME/database/migration_scripts/HDR7.x_HDR82/migrate_ctb_db_objects.sh script with MIGRATE_OPTION 13.
Example:
sh migrate_ctb_db_objects.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT> <CTB_INDEX_TABLESPACE_NAME> <MIGRATE_OPTION>
sh migrate_ctb_db_objects.sh /scratch/app/oracle/product/19c sys localhost servicename 1521 IDX_TBS 13The script prompts you to enter SYS and CTB user passwords, provide valid values and hit enter. The script creates the CTB OMP default queue i.e. AQ$CTB_OMP_DEFAULT_QUEUE.
Persist HDR 8.2 Release Information
Persist HDR 8.2 patch release information into HDR_RELEASE_INFO table by running $HDR_HOME/database/migration_scripts/HDR7.x_HDR82/migrate_ctb_db_objects.sh script with MIGRATE_OPTION 14.
Example:
sh migrate_ctb_db_objects.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT> <CTB_INDEX_TABLESPACE_NAME> <MIGRATE_OPTION>
sh migrate_ctb_db_objects.sh /scratch/app/oracle/product/19c sys localhost servicename 1521 IDX_TBS 14The script prompts you to enter SYS and CTB user passwords, provide valid values and hit enter. The script records the HDR 8.2 patch release information into HDR_RELEASE_INFO table for database tier. You can see those details in CTB.HDR_RELEASE_INFO table.
Compile CTB Schema
You can compile CTB schema by running $HDR_HOME/database/migration_scripts/HDR7.x_HDR82/migrate_ctb_db_objects.sh script with MIGRATE_OPTION 15.
Example:
sh migrate_ctb_db_objects.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT> <CTB_INDEX_TABLESPACE_NAME> <MIGRATE_OPTION>
sh migrate_ctb_db_objects.sh /scratch/app/oracle/product/19c sys localhost servicename 1521 IDX_TBS 15The script prompts you to enter SYS and CTB user passwords, provide valid values and hit enter. The script compiles the CTB schema using dbms_utility.compile_schema procedure.
Gather Stats CTB Schema
This is optional. If you want to do gather stats to CTB schema then execute $HDR_HOME/database/migration_scripts/HDR7.x_HDR82/migrate_ctb_db_objects.sh script with MIGRATE_OPTION 16.
Example:
sh migrate_ctb_db_objects.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT> <CTB_INDEX_TABLESPACE_NAME> <MIGRATE_OPTION>
sh migrate_ctb_db_objects.sh /scratch/app/oracle/product/19c sys localhost servicename 1521 IDX_TBS 16The script prompts you to enter SYS and CTB user passwords, provide valid values and hit enter. The script gather stats the CTB schema.
Note:
If you want to migrate HDR 7.0.2.x CTB schema to HDR 8.2 CTB schema with all migrate options in a single execution, run database/migration_scripts/HDR7.x_HDR82/migrate_ctb_db_objects.sh with MIGRATE_OPTION ALL.sh migrate_ctb_db_objects.sh <ORACLE_HOME> <SYS_USER_NAME> <HOST_NAME> <SERVICE_NAME> <DB_PORT> <CTB_INDEX_TABLESPACE_NAME> ALLThe script prompts you to enter SYS database user password and CTB database user password, provide valid values and hit enter. The script migrates the HDR 7.0.2.x CTB schema to HDR 8.2 CTB schema in a single execution. You can check the count validation in HDR_CTB_COUNT_VALIDATION_LOG table.
Parent topic: Migration