The Oracle E-Business Suite Tablespace Model (formerly called the Oracle Applications Tablespace Model, or OATM) uses twelve consolidated tablespaces (including three system tablespaces: temporary, system and undo segments) and provides support for locally managed tablespaces. OATM was introduced in Release 11i.10. In prior 11i releases of Oracle E-Business Suite, each product was allocated two tablespaces, one for data and one for indexes. The Migration Utility is a menu-based PERL program and a series of sizing estimate reports that enables conversion of Oracle E-Business Suite applications schemas either in a single comprehensive migration or a phased, schema-by-schema migration. In general Oracle recommends performing a single comprehensive migration, however this requires a sufficient amount of down time and disk space. Oracle does not support partial migration of tablespaces. You must still migrate all schemas when performing a phased schema-by-schema migration.
With OATM, each database object is mapped to a tablespace based on its Input/Output characteristics, which include object size, life span, access methods and locking granularity. This model allows for easier maintenance, and reduced space usage for Oracle E-Business Suite.
Another configuration supported with Locally Managed Tablespaces, User Extent Management, is only relevant when Dictionary Tablespaces have been migrated to Locally Managed Tablespaces. Because migration to OATM is performed at the object level and not at the tablespace level, User Extent Management is not relevant. The default recommended configuration in OATM is Uniform Extent Management.
Migrating database objects to OATM provides the following benefits:
Fewer and more consolidated tablespaces
Locally Managed Tablespaces
Accounts for the I/O characteristics of an object
Reclaims space after migration
Real Application Cluster (RAC) Support
OATM contains twelve locally managed tablespaces for all products, including temporary tablespace, system tablespace, and undo segments. The previous tablespace model contained two tablespaces for each Oracle product resulting in hundreds of tablespaces.
This model provides support for either Uniform or Auto-allocate extent management, available with Locally Managed Tablespaces. Locally Managed Tablespaces have benefits over Dictionary Tablespaces in the previous model and allow for the sizes of extents to be determined automatically by the system (Auto-allocate). Alternatively, all extents can have the same size (Uniform) in and override object storage options.
OATM implements Automatic segment-space management, a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace. Automatic segment-space management delivers better space utilization than manual segment-space management, and is self tuning because it scales by increasing the number of users, as well as instances. For a Real Application Clusters environment, automatic segment-space management enables dynamic affinity of space to instances, which avoids the hard partitioning of space inherent with using free list groups.
The value for uniform extent size should be carefully selected based on system requirements. For production environments and large tablespaces like transaction tables, transaction indexes, interfaces, summaries, archives, and media, a uniform extent size of 1MB or 10MB (with caution) should be considered. Choosing an extent size that is too small can result in frequent extensions and performance degradation of the system.
The Release 12 Rapid Install production database is delivered out-of-the-box with locally managed tablespaces with uniform extent sizes of 128Kb. If this size is not the best match for the characteristics of your system, you can follow subsequent re-migration steps to create new tablespaces with the desired uniform extent size and migrate objects to those new tablespaces. The OATM Migration Utility supports all possible configurations of locally managed tablespaces. You have the flexibility to override the default recommendation of uniform extent size with Auto-Allocate extent management as per your requirements. Uniform extent size is also configurable.
Note: The Oracle database server does not as yet support altering the extent management type or uniform extent size after the locally managed tablespaces have been created. Therefore, if the originally used extent management type or uniform extent size needs to be modified, re-creation of the tablespaces and re-migration of objects is the only available option.
Use the customization option to change names of existing OATM tablespaces as listed in the following table:
| Tablespace Type | Old Tablespace Name | New Tablespace Name |
|---|---|---|
| Transaction Tables | APPS_TS_TX_DATA | APPS_TS_TX_DATA_1MB |
| Transaction Indexes | APPS_TS_TX_IDX | APPS_TS_TX_IDX_1MB |
| Reference | APPS_TS_SEED | |
| Interface | APPS_TS_INTERFACE | APPS_TS_INTERFACE_1MB |
| Summary | APPS_TS_SUMMARY | APPS_TS_SUMMARY_1MB |
| Nologging | APPS_TS_NOLOGGING | |
| Advanced Queuing/AQ | APPS_TS_QUEUES | |
| Media | APPS_TS_MEDIA | APPS_TS_MEDIA_1MB |
| Archive | APPS_TS_ARCHIVE | APPS_TS_ARCHIVE_1MB |
Please note that for the types of tablespaces for which you do not wish to modify the uniform extent size, you should not change the tablespace name and objects will remain in the respective tablespace with the originally selected extent size and no attempt will be made to migrate them. Sizes of those tablespaces should be ignored in the sizing report and 'alter tablespace' statements removed from the 'create tablespace' script before the script is run.
From the main menu, select option 7. Run Customization Steps:
Customization
Register new tablespace - tablespace type
Change name of the existing tablespace
Register object classification
Change object classification
Please select "2. Change name of existing tablespace".
Enter tablespace type: TRANSACTION_TABLES Enter new tablespace name: APPS_TS_TX_DATA_1MB Tablespace name for TRANSACTION_TABLES changed to APPS_TS_TX_DATA_1MB. Do you want to continue changing tablespace names?[Y]: Enter tablespace type: TRANSACTION_INDEXES Enter new tablespace name: APPS_TS_TX_IDX_1MB ...
The same steps should be performed for all tablespace types for which you want to change the uniform extent size. When the customizations are complete, please run the following steps as described in the chapter Oracle E-Business Suite Tablespace Migration Utility.
Run the sizing process and create new tablespaces.
Run migration command generation.
Complete post-migration steps and drop old tablespaces that have no remaining segments.
OATM takes into account the following object I/O characteristics of an object:
size
life span
access methods
locking granularity
Automatic segment-space management is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace. Automatic segment-space management delivers better space utilization than manual segment-space management, and is self tuning because it scales with the increasing number of users and instances. For a Real Application Cluster (RAC) environment, automatic segment-space management enables dynamic affinity of space to instances, which avoids the hard partitioning of space inherent with using free list groups.
The tablespace migration utility migrates objects from the existing dictionary-managed tablespaces to locally managed tablespaces with automatic segment management and either uniform or Auto-allocate extent management. As a result, space is better utilized and less wasted. Migration of table and index segments from one tablespace to another also reclaims unused space, especially for indexes that are fragmented when the index columns are inserted, updated or deleted frequently.
OATM facilitates Real Application Cluster (RAC) support because of its reduced number of tablespaces. RAC is an Oracle database feature that harnesses the processing power of multiple interconnected computers where all active instances can concurrently execute transactions against a shared database disk system. The new tablespace model is critical when implementing RAC on Linux, where currently there is a limitation of 255 raw devices.
OATM provides the following additional benefits:
Facilitates administration and configuration ease
Increases block-packing to reduce the overall number of buffer gets.
The advantages of OATM's product tablespaces are best understood in terms of the tablespace model that preceded it. This model contained two tablespaces for each Oracle E-Business Suite product. One tablespace was allocated for tables and one for indexes. In this model, the standard naming convention for tablespaces contained the product’s Oracle schema name with a suffix of either "D" for "Data" tablespaces or "X" for "Index" tablespaces. For example, the tablespaces APD and APX were the default tablespaces for Oracle Payables tables and indexes, respectively.
In contrast to the previous tablespace model, OATM contains nine default tablespaces for applications objects in addition to Undo, Temp and System database tablespaces. Indexes on transaction tables are held in a separate tablespace dedicated for transaction table indexes whereas all other indexes are held in the same tablespace as the parent/base table. All Oracle E-Business Suite product schemas now have a default tablespace set to point to the TRANSACTION_TABLES tablespace type for data objects and the TRANSACTION_INDEXES tablespace type for index objects.
The Oracle E-Business Suite Tablespace Model uses Locally Managed Tablespaces and supports either Uniform or Autoallocate extent management. Another configuration supported with Locally Managed Tablespaces - User Extent Management, is of relevance only in case of Dictionary Tablespaces that have been migrated over to Locally Managed Tablespaces. Because migration to OATM is performed at the object level and not at the tablespace level, User Extent Management is not relevant. The default recommended configuration in OATM is Uniform Extent Management. The migration utility recommends the default of 128k uniform extents which can be changed to suit the customer database. Tablespace types are listed in the following table:
| Tablespace Type | Tablespace Name | Content |
|---|---|---|
| Transaction Tables | APPS_TS_TX_DATA | Tables that contain transactional data. |
| Transaction Indexes | APPS_TS_TX_IDX | Indexes on transaction tables. |
| Reference | APPS_TS_SEED | Reference and setup data and indexes. |
| Interface | APPS_TS_INTERFACE | Interface and temporary data and indexes. |
| Summary | APPS_TS_SUMMARY | Summary management objects, such as materialized views, fact tables, and other objects that record summary information. |
| Nologging | APPS_TS_NOLOGGING | Materialized views not used for summary management and temporary objects. |
| Advanced Queuing/AQ | APPS_TS_QUEUES | Advanced Queuing and dependent tables and indexes. |
| Media | APPS_TS_MEDIA | Multimedia objects, such as text, video, sound, graphics, and spatial data. |
| Archive | APPS_TS_ARCHIVE | Tables that contain archived purge-related data. |
| Undo | UNDO | Automatic Undo Management (AUM) tablespace. UNDO segments are identical to ROLLBACK segments when AUM is enabled. |
| Temp | TEMP | Temporary tablespace for global temporary table, sorts, and hash joins. |
| System | SYSTEM | System tablespace used by the Oracle Database |
OATM relies on specific explicit and implicit classification rules that are determined based on storage considerations for the object type in question. The Oracle Tablespace Migration Utility migrates objects based on these rules. The following table contains rules for implicit classifications that are applied in OATM, based on object types. Objects that do not have an implicit classification rule or an explicit object classification are migrated to the default tablespaces of the schema in which they reside.
Explicit object classifications are seeded by Oracle based on the I/O characteristics of the object.
The following table contains implicit classification rules for the Oracle E-Business Suite Tablespace Migration Utility.
| Object Type | Tablespace_Type |
|---|---|
| AQ Tables | AQ |
| IOTs (Index Organized Tables) | Transaction_Tables |
| Materialized Views | Summary |
| Materialized View Logs | Summary |
| All other Indexes | Same Tablespace type as the table |
| Domain Indexes | Transaction_Indexes |
| Indexes on Transaction Tables | Transaction_Indexes |
The Oracle E-Business Suite Tablespace Migration Utility is primarily designed to migrate tables, indexes, materialized views, materialized view logs and other database objects that are owned by standard Oracle E-Business Suite schemas from their existing tablespace model to OATM. Custom or third party schemas can also be migrated using the Oracle E-Business Suite Tablespace Migration Utility, customer preferred methods, or a database management tool such as the Oracle Enterprise Manager (OEM). Custom objects in standard Oracle E-Business Suite product schemas are migrated by default.
The Oracle E-Business Suite Tablespace Migration Utility enables the following customizations:
Changing tablespace names
Registering custom tablespace types
Registering custom object-tablespace classifications
Changing existing object classifications.
Login to the Forms-based version of Oracle E-Business Suite with the System Administrator Responsibility. Navigate to Security -> ORACLE -> Register and register the external schema(s) if they are not already registered. Set Privilege to "Enabled".
In some cases, you may not want to migrate some of your schemas such as non-Oracle schemas that are registered with Oracle E-Business Suite. To accomplish this, you must disable those schemas by accessing System Administrator responsibility -> Security -> ORACLE -> Register and then selecting either "External" or "Disabled" for the schema in question. Conversely, if you want to flag specific schemas for migration, you can enable them by accessing System Administrator responsibility -> Security -> ORACLE -> Register and then selecting "Enabled".
The Tablespace Migration Utility is a menu-based PERL program that enables you to estimate future space requirement for the tablespaces and to migrate the Oracle E-Business Suite database to OATM. Log files are available for user viewing and are created in the working directory from which your run the PERL program. The Log file name and location are displayed once you choose the required option.
The Tablespace Migration Utility enables you to perform either a single, comprehensive migration of all schemas or a phased, schema-by-schema migration. To minimize downtime, Oracle recommends that you perform the single comprehensive migration of all schemas, however this requires a sufficient amount of down time and available disk space. If , you do not have sufficient down time or disk space to accomplish this, then you can run the phased schema-by-schema migration. Once you migrate an object from its existing tablespace to OATM, this process cannot be reversed. Oracle does not support the rollback of schemas migrated to OATM in a phased schema-by-schema migration. The only method for achieving this result is to recover the migrated schemas from a backup.
Note: Once you initiate migration of one or more schemas to OATM it is not possible to perform additional migrations from a different PERL menu. You must wait until one migration is completed before beginning another.
Whenever possible, Oracle recommends the following:
perform a single comprehensive migration of all schemas instead of performing a phased schema-by-schema migration.
perform test runs to determine the amount of down time required to perform a comprehensive migration of all schemas
secure twice as much disk space as your existing space to perform the a single comprehensive migration of all schemas, and to be operational using the new model.
When you first install the Tablespace Migration Utility, it does the following:
Copies the PERL menu script, fndtsmig.pl, to the FND_TOP/bin directory.
Copies the SQL scripts for the Tablespace Migration utility to FND_TOP/patch/120/sql directory.
Copies the PLS files for the Tablespace Migration utility to the FND_TOP/patch/120/sql directory and creates packages in the database.
Compiles the Java files into the packages oracle.apps.fnd.tsmig, in FND_TOP/java/apps.zip file.
Creates and seeds the following tables:
FND_TABLESPACES
FND_OBJECT_TABLESPACES
FND_TS_SIZING
FND_TS_MIG_CMDS
FND_TS_MIG_RULES
FND_TS_PROD_INSTS
Invoke the Tablespace Migration Utility main menu by performing the following:
Run the fndtsmig.pl PERL script:
perl $FND_TOP/bin/fndtsmig.pl.
Provide the following information when prompted to access the Tablespace Migration Utility main menu:
OATM configuration file
APPL_TOP[]
FND_TOP[/fnddev/fnd/12.0]
Database Connect String [dummy]: atgtsqa
Password for your 'SYSTEM' ORACLE Schema
Password for your 'SYS' ORACLE Schema
Oracle Application Object Library Schema name [APPS]
Password for APPS
APPLSYS Schema Name [APPLSYS]
Information can be provided in an interactive manner or by providing an OATM configuration file with all information already specified. An OATM configuration file has the following format with the following valid tags:
APPL_TOP - valid APPL_TOP value or $ENV$
FND_TOP - valid FND_TOP value or $ENV$
APPS_SCHEMA - valid apps schema name
APPLSYS_SCHEMA - valid applsys schema name
ALLOC_TYPE - UNIFORM/AUTOALLOCATE
UES - valid integer (uniform extent size)
DBF_DIR - valid directory for generated database file
INDIVIDUAL_DATAFILE_SIZE - maximum datafile size
NUM_WORKER - integer (number of concurrent workers.)
MIGRATION_SCHEMA - % or comma separated list of schemas
CONNECT_STRING - database connect string
AUTO_START_MIGRATION - should migration be started automatically after
preparatory steps are completed Y(default)/N
$ENV$ is a reserved word for the OATM configuration file. If $ENV$ is used as a value for a specific token, then that token's real value will be derived from the customer's environment dynamically during the runtime.
Example
<!-- OATM Migration Configuration File> <OATM> <APPL_TOP> $ENV$ </APPL_TOP> <FND_TOP> $ENV$ </FND_TOP> <CONNECT_STRING> atgtsmqa </CONNECT_STRING> <APPS_SCHEMA> APPS </APPS_SCHEMA> <APPLSYS_SCHEMA> APPLSYS </APPLSYS_SCHEMA> <ALLOC_TYPE> U </ALLOC_TYPE> <UES> 1024 </UES> <DBF_DIR> /slot05/oracle/atgtsmqadata/ </DBF_DIR> <INDIVIDUAL_DATAFILE_SIZE> 2000 </INDIVIDUAL_DATAFILE_SIZE> <NUM_WORKER> 8 </NUM_WORKER> <MIGRATION_SCHEMA> % </MIGRATION_SCHEMA> < AUTO_START_MIGRATION > Y </ AUTO_START_MIGRATION > </OATM>
If an OATM configuration file is provided, values specified in the configuration file can still be overridden by values provided interactively. A summary of all provided information will be displayed and can be reviewed and corrected if needed.
Note: Please note that the configuration file will not be modified accordingly by information entered interactively. Values provided interactively will have an effect only for the OATM session when provided. No new tags added to the configuration files will be recognized. All values specified in the OATM configuration file above are just an example, not Oracle recommended values.
The Tablespace Migration Utility main menu lists six required sequential steps and one optional step to migrate your database objects to OATM. These steps are categorized in three phases. In Phase 1, you perform the necessary preparation steps for migrating your objects to OATM. In Phase 2, you perform the necessary steps to migrate your objects to OATM and in Phase 3 you run the required post migration steps.
(Preparatory Step) Generate Migration Sizing Reports
(Preparatory Step) Create New Tablespaces
(Preparatory Step) Generate Migration Commands
(Migration Step) Execute Migration Commands
(Migration Step) Run Migration Status Reports
(Post Migration Step) Run Post Migration Steps
(Optional) Run Customization Steps
(Optional, batch mode) Run Migration in Batch Mode
Steps 1, 2, 3 can be executed while Oracle E-Business Suite is still available to users. Step 4 must be executed when Oracle E-Business Suite is not available to users and steps 5 and 6 must be completed before making Oracle E-Business Suite available to users again. If you choose to perform optional Step 7 you should do so before the other steps.
Caution: Oracle highly recommends that you back up your database after performing Step 3, which is the final preparatory step. You should have a backup copy of your database before performing the subsequent migration steps.
Oracle highly recommends that you perform a backup of your database twice as follows;
Copy of previous tablespace model. Back up your database before performing any of the migration steps. Because Oracle does not support the rollback of migrated database objects, this is the only available method for restoring your previous tablespace model.
Copy of database that has been prepared for migration. Backup your database after performing Step 3: Generate Migration Commands. This enables you to migrate your database objects to OATM using your last good copy of a database that has been prepared for migration.
If you choose to use the menu option 8. Migration in Batch mode, you should perform the database backup before the migration and/or after the preparatory steps are completed, if you choose to run the preparatory steps separately.
Select Step 1: Generate Migration Sizing Reports to access a list of reports that help you to gauge the space requirements for the new tablespace model and that assist you in determining which migration approach best suits your requirements. The reports perform sizing estimation by executing a program that calculates the size of each object using package DBMS_SPACE.UNUSED_SPACE and by populating table FND_TS_SIZING. The sizing reports use the data in this table to display the required information. The Plan Migration menu contains the following options:
Calculate total space required by each new tablespace to migrate all Oracle E-Business Suite product schemas
Calculate total space required by each new tablespace to migrate each Oracle E-Business Suite product schema (relevant for a schema-by-schema migration)
Calculate total space required by each Oracle E-Business Suite schema, with details for each object
Display Sizing Exception report
Choose option 1 to calculate the total space required by each new tablespace when performing a single comprehensive migration of all Oracle E-Business Suite product schemas, and to generate the report fndtrep1.txt. Before running the report, the program prompts you to specify whether the information in the sizing table is current or must be updated. Enter the following to calculate space requirements for performing a single comprehensive migration of all schemas:
Whether the sizing information is current or must be gathered before running the report.
If you selected "Y" for the previous option, provide the extent management type for the tablespaces, since the space requirements are dependent on this.
If Uniform Extent Management, provide the uniform extent size for the tablespaces or choose the default value provided on the screen.
Sizing Program was last run on 02-SEP-03 Do you want to run the Sizing program again before running the report [N]:y Enter the Extent Allocation type A(utoallocate) or U(niform Extent Size) [U]: U Enter Uniform Extent Size for the Tablespaces in KBytes[128]:
Choose Option 2 to calculate total space required by each new tablespace when migrating individual Oracle E-Business Suite product schemas one at a time, and to generate the report fndtrep2.txt. The program prompts your for the following information:
Schema name, enter the percent sign (%) for all schemas,
Whether the sizing information is current or must be gathered before running the report.
If you selected Y for the previous option, provide the extent management type for the tablespaces, since the space requirements are dependent on this.
If Uniform Extent Management, provide the uniform extent size for the tablespaces or choose the default value provided on the screen.
Enter the Schema name: <HR> Sizing Program was last run on 02-SEP-03 Do you want to run the Sizing program again before running the report [N]:
Choose option 3 to calculate total space required by each Oracle E-Business Suite schema with details for each object and to generate the report fndtrep4.txt. The program prompts your for the following information:
Schema name, enter the percent sign (%) for all schemas,
Whether the sizing information is current or must be gathered before running the report.
If you selected Y for the previous option, provide the extent management type for the tablespaces, since the space requirements are dependent on this.
If Uniform Extent Management, provide the uniform extent size for the tablespaces or choose the default value provided on the screen.
Enter the Schema name: <HR> Sizing Program was last run on 02-SEP-03 Do you want to run the Sizing program again before running the report [N]:
Choose option 4 to generate report fndtrep5.txt, listing all the objects for which sizing estimation generated an error. The program prompts you for the schema name. Enter the percent sign (%) for all schemas.
Enter the Schema name: <%>
Select Step 2: Create New Tablespaces to create the OATM tablespaces to which you will migrate your database objects. The Create New Tablespaces menu contains the following options:
Generate the Tablespace Creation Script
Create New Tablespaces
Option 1 prompts you for the extent allocation type, such as Autoallocate, Uniform Extent, and Uniform Extent Size, which will be used for creating the new tablespaces. The utility prompts for the name of the directory in which the datafile will be created. For every tablespace created as part of OATM, the utility prompts you for information, such as number and size of the datafiles. The utility will append a sequence number to the tablespace name and a .dbf extension to generated the datafile name. For example, if you enter the datafile directory as "/u01/oradata" and for transaction data tablespace, APPS_TS_TX_DATA, you enter the number of datafiles as 2 and size as 2000M, the utility will create tablespace creation script with 2 datafiles for the tablespace named "/u01/oradata/APPS_TS_TX_DATA01.dbf" and "/u01/oradata/APPS_TS_TX_DATA02.dbf", each of size 2000M. To create datafiles of different sizes or in different locations, you must modify the generated script, crtts.sql.
If you do not have limited disk space, create all tablespaces with the estimated sizes listed in report #1. This will eliminate the need to extend the tablespaces as the migration proceeds. If you do not have enough disk space to create all the tablespaces with the total size, use the estimated values in report #2 <Schema Name> for reference. If your operating system has a limit on the size of a dbf file, ensure you enter a value less than this when prompted.
Enter the Extent Allocation type A(utoallocate) or U(niform Extent Size)[U]: ***************************************************************** The utility will append a sequence number to the tablespace name and a .dbf extension to generate the datafile names. Datafile size should not be greater than OS file size limit. Please edit the generated script to change the file name/size ***************************************************************** Enter the absolute path for the datafiles directory: /u01/oradata Enter the Number of Datafiles for Transaction data tablespace[1]: 2 Enter the Datafile Size for Transaction data tablespace (MB): 2000 Enter the Number of Datafiles for Transaction index tablespace[1]: 2 Enter the Datafile Size for Transaction index tablespace (MB): 2000 Enter the Number of Datafiles for Reference tablespace[1]: 1 Enter the Datafile Size for Reference tablespace (MB): 2000 Enter the Number of Datafiles for Interface tablespace[1]: 1 Enter the Datafile Size for Interface tablespace (MB): 1700 Enter the Number of Datafiles for Summary tablespace[1]: 2 Enter the Datafile Size for Summary tablespace (MB): 2000 Enter the Number of Datafiles for Nologging tablespace[1]: Enter the Datafile Size for Nologging tablespace (MB): 60 Enter the Number of Datafiles for Archive tablespace[1]: Enter the Datafile Size for Archive tablespace (MB): 1400 Enter the Number of Datafiles for Queue tablespace[1]: Enter the Datafile Size for Queue tablespace (MB): 150 Enter the Number of Datafiles for Media tablespace[1]: Enter the Datafile Size for Media tablespace (MB): 2000
Select option 2 to create new tablespaces by executing the script crtts.sql that was generated in the previous step. This script does not check the operating system limitation for the maximum size of a file.
Select Step 3: Generate Migration Commands to generate migration commands for your schemas. The Generate Migration Commands menu contains the following options:
Caution: You should not generate migration commands if migration is already in progress. OATM utility will prevent the generation of migration commands while generation of the commands or migration process is already in progress. Oracle does not recommend manually updating table FND_TS_MIG_CMDS, especially while you are generating or executing migration commands.
Invalid Indexes Report.
Generate Migration Commands for all Schemas
Generate Migration Commands for a List of Schemas
Select Option 1 to generate a report listing all the indexes which are invalid in the Oracle E-Business Suite schemas. This report is stored in fndinvld.txt. You must correct or drop all invalid indexes before generating migration commands for all schemas or for a given schema. This is especially relevant for context indexes. Invalid indexes on an object may cause errors during migration of the base table and invalid context indexes will not be moved.
Select Option 2 to generate the commands for migrating the objects in all the schemas to the correct tablespace. The migration commands are stored in the table FND_TS_MIG_CMDS. You can check the generated log file fndgmcmd <timestamp>.log for errors during the generation process. A threshold object size is calculated based on the sizing data in FND_TS_SIZING to determine whether an object will be moved sequentially or in parallel. Migration commands for all objects with total blocks greater than or equal to threshold blocks are generated with the PARALLEL clause and execution mode as sequential. Migration commands for objects with total blocks less than threshold are generated with NOPARALLEL clause and execution mode as parallel. Partitioned objects are always executed sequentially regardless of their size.
Select Option 3 to generate the commands for migrating the objects in a given list of comma separated schema names into the correct tablespace. The migration commands are stored in the table FND_TS_MIG_CMDS. You can check the generated log file fndgmcmd <timestamp>.log for errors during the generation process. A threshold object size is calculated based on the sizing data in FND_TS_SIZING to determine whether an object will be moved sequentially or in parallel. Migration commands for all objects with total blocks greater than or equal to threshold blocks are generated with the PARALLEL clause and are executed sequentially. Migration commands for objects with total blocks less than threshold are generated with NOPARALLEL clause and are executed in parallel using multiple processes. Partitioned objects are always executed sequentially regardless of their size.
Enter a comma separated list of schema names: HR,AP
Caution: Oracle highly recommends that you back up your database after performing Step 3, which is the final preparatory step. You should have a backup copy of your database before performing the subsequent migration steps.
Select Step 4: Execute Migration Commands to execute migration commands for your schemas. The Execute Migration Commands menu contains the following options:
Execute Migration Commands for all Schemas
Execute Migration Commands for a List of Schemas
Migrate CTXSYS Schema
Return to the OATM menu is possible as soon as all migration processes (sequential, parallel, and java process for tables with LONG and LONG RAW columns) have started and you get the prompt – “Press Return key to continue...”. Returning to the menu does not mean that migration has been completed. The migration processes are running in the background but you can return to the menu in order to monitor migration status/errors by running the migration progress report. Migration sessions are internally spawned using nohup and are immune to any hangup signals. Therefore VPN connection expiration, etc. should not pose any problems.
Caution: You should not execute migration commands if migration is already in progress. The OATM utility will prevent generating migration commands as well as starting additional migration execution processes while migration process is in progress. Starting additional migration process can cause errors that can result in data corruption and will seriously impact migration process performance. Oracle does not recommend manually updating table FND_TS_MIG_CMDS, especially while you are generating or executing migration commands.
Select Option 1 to migrate the objects in all the schemas to the correct tablespaces. You are prompted for the number of parallel processes as input. This option executes the generated commands from FND_TS_MIG_CMDS table which generates the following events in sequence:
All constraints, triggers, policies are disabled first and then the queues are stopped.
Java program oracle.apps.fnd.tsmig.TSMigration is executed to migrate all the tables with LONG and LONG RAW columns along with their indexes. A log file for the migration of tables with LONG is generated as fndmlong<timestamp>.log.
A sequential process is started that executes the script fndemseq.sql to move all the objects generated with execution mode as sequential. A log file for the sequential process is generated as fndemseq<timestamp>.log.
Multiple processes are started to execute the SQL script fndemcmd.sql to migrate the objects generated with execution mode as parallel. A log file is generated as fndemcmd<timestamp>.log.
Are you sure you want to migrate all schemas[N]: y Enter the maximum number of parallel processes[4]:10 Starting the Migration process for all schemas. Please wait... Migration processes for tables with LONG and LONG RAW columns started. Please monitor the log file $APPL_TOP/admin/log/fndmlong20050120230037.log for errors Sequential migration process started. Please monitor the log file $APPL_TOP/admin/log/fndemseq20050120230038.log for errors Parallel migration processes started. Please monitor the log file $APPL_TOP/admin/log/fndemcmd20050120230048.log for errors Press Return key to continue...
Select Option 2 to migrate the objects in a given list of schemas to the correct tablespaces. You are prompted for a list of comma separated schema names and then for the number of parallel process as input. This option executes the generated commands from FND_TS_MIG_CMDS table which generates the following events in sequence:
All constraints, triggers, policies are disabled first and then the queues are stopped.
Java program oracle.apps.fnd.tsmig.TSMigration is executed to migrate all the tables with LONG and LONG RAW columns along with their indexes. A log file for the migration of tables with LONG is generated as fndmlong<timestamp>.log.
A sequential process is started that executes the script fndemseq.sql to move all the objects generated with execution mode as sequential. A log file for the sequential process is generated as fndemseq<timestamp>.log.
Multiple processes are started to execute the SQL script fndemcmd.sql to migrate the objects generated with execution mode as parallel. A log file is generated as fndemcmd<timestamp>.log.
Enter a comma separated list of schema names: HR,AP Enter the maximum number of parallel processes [4]: <10>
Note: If your migration process terminates before it is completed, please check enqueue/dequeue status of queue - SYSTEM.TBLMIG_MESSAGEQUE by querying the following:
select NAME, ENQUEUE_ENABLED, DEQUEUE_ENABLED
from dba_queues
where owner = 'SYSTEM'
and name = 'TBLMIG_MESSAGEQUE';
The CTXSYS schema is not an APPS schema and in order to be included in the migration process, the CTXSYS schema has to be registered in the following manner: Login to the Forms-based version of Oracle E-Business Suite with the System Administrator Responsibility. Navigate to Security -> ORACLE -> Register and register the CTXSYS schema if it is not already registered. Then Set Privilege to "Enabled". CTXSYS schema objects are not classified by default and will be migrated to Transaction Tables and Transaction Index tablespaces, for CTXSYS tables and indexes respectively. Using Oracle Tablespace Migration Utility customizations steps, it is possible to classify CTXSYS objects to be migrated to the desired tablespace or tablespace type.
Select Option 3 to migrate CTXSYS schema objects to the correct tablespace. You are prompted for the number of parallel processes as input. This option executes the generated commands from FND_TS_MIG_CMDS table, which generates the following events in sequence:
All constraints, triggers, policies are disabled first and then the queues are stopped.
The Java program oracle.apps.fnd.tsmig.TSMigration is executed to migrate all the tables with LONG and LONG RAW columns along with their indexes. A log file for the migration of tables with LONG is generated as fndmlong.<timestamp>.log.
A sequential process is started that executes the script fndemseq.sql to move all the objects generated with execution mode as sequential. A log file for the sequential process is generated as fndemseq<timestamp>.log.
Multiple processes are started to execute the SQL script fndemcmd.sql to migrate the objects generated with execution mode as parallel. A log file is generated as fndemcmd<timestamp>.log.
Select Step 5: Run Migration Status Report to run progress and error reports on the migration process. The Run Migration Status Report menu contains the following options:
Run Migration Status Report
Run Migration Error Report
Select Option 1 to generate a report containing the number of successfully migrated objects, objects in error (if any) and the percentage of completion, per schema, or for all the schemas and a breakdown of the objects per object type. This option prompts you to enter the schema name and generates report fndtrep8.txt.
Enter the Schema name[%]: <HR>
Select Option 2 to generate a report that provides a list of objects that generated an error during the migration process that includes the error details. This option prompts you to enter the schema name and generates report fndtrep10.txt.
Enter the Schema name[%]: <HR>
Caution: When a standby database exists and/or you want all transactions to be recoverable on a database, tablespace, or object-wide level, it is recommended that you check that all objects have appropriate values for the logging attribute before and after the OATM migration process to ensure that all transactions are logged and can be recovered through media recovery.
Select Step 6: Run Post Migration Steps to determine which objects have not yet been migrated to OATM, to enable constraints, triggers, policies, and start queues, and to re-size old tablespaces. The Run Post Migration Steps menu contains the following options:
Run Audit Reports
Enable the Constraints, Triggers and Policies, and Start Advanced Queues
Re-size Old Tablespaces
Generate script to drop empty tablespaces
Note: After running post migration steps, you must perform a complete refresh of all materialized views. This is a required manual step that is not included in the Tablespace Migration Utility menu.
Select Option 1 to generate a report that provides a list of objects that have not been migrated to the correct tablespace. This option prompts you to enter the schema name and generates the report, fndtrep6.txt.
Enter the Schema name[%]: <HR>
Select Option 2 to enable all the constraints, triggers, policies and start queues, and to generate the log file, fndenabl<timestamp>.log is generated. This option prompts you to enter the schema name.
Enter the Schema name[%]: <HR>
Select Option 3 to reduce the size of the old tablespaces. This option queries the data dictionary for all data files of the previous tablespaces to determine the level at which they can be re-sized, and generates the resize commands in a script resizdb.sql. This script is then executed to resize the data files.
Select Option 4 to generate a script to drop empty tablespaces. This option queries the data dictionary for all the previous tablespaces to determine it they still contain segments. For all the old tablespaces with no remaining segments a drop tablespace ... including contents and datafiles statement is generated in a script fndtsdrp.sql. When executed, this script drops all empty pre-OATM tablespaces including data files.
Note: Please ensure that there is no residual data in the tablespaces to be dropped prior to running the drop tablespace script to avoid irrevocable loss of data.
Select Step 7: Run Customization Steps if you wish to customize tablespaces, tablespace types, and object classifications as required. If you choose run customization steps you should do so before performing any of the other steps for migrating your database objects to OATM. The Run Customization Steps menu contains the following options:
Register new tablespace - tablespace type
Change name of the existing tablespace
Register object classification
Change object classification
Select Option 1 to register any custom tablespace types that are not available by default with OATM. If the tablespace type or tablespace name is already registered, message will be displayed stating that it already exists
Enter the tablespace type: CUSTOM_TABLESPACE_TYPE Enter the tablespace name: CUSTOM_TABLESPACE Tablespace CUSTOM_TABLESPACE registered. Do you want to continue registering tablespaces ?[Y]:
Selecting the default, "Y" prompts you to enter next tablespace type/name pair. Selecting "N" returns you to the previous menu.
Select Option 2: Change name of the existing tablespace to update the tablespace name of any default OATM tablespaces or registered custom tablespaces.
Enter the tablespace type: CUSTOM_TABLESPACE_TYPE Enter the new tablespace name: CUSTOM_TBLSP Tablespace name for CUSTOM_TABLESPACE_TYPE changed to CUSTOM_TBLSP. Do you want to continue changing tablespace names?[Y]:
Selecting the default, "Y" prompts you to enter next existing/new tablespace pair. Selecting "N" returns you to the previous menu.
Select Option 3: Register object classification to register new object-tablespace classifications. This is relevant only for objects such as tables that require explicit classification. These can include custom tables residing in Oracle E-Business Suite product schemas or those residing in custom schemas. If object classification for the object is already registered, a message will be displayed stating that classification already exists, and will prompt you to enter a new object name. Existing classification can be modified by selecting option 4 from the Run Customization Steps menu.
Enter the application short name: FND Enter the object name: FND_TABLES Enter the tablespace type: CUSTOM_TABLESPACE_TYPE Tablespace type CUSTOM_TABLESPACE_TYPE for object FND_TABLES registered. Do you want to continue registering tablespace types for other objects?[Y]:
Selecting the default, "Y" prompts you to register the next object classification and selecting "N" returns you to the previous menu.
Select Option 4: Change object classification to change any existing object-tablespace classifications. If object classification for the object is not already registered, a message will be displayed stating that classification does not exist, and you will be prompted to enter a new object name. New object classification can be entered by selecting Option 3 from the Run Customization Steps menu.
Enter the application short name: FND Enter the object name: FND_LOBS Enter the tablespace type: MEDIA Tablespace type for object FND_LOBS changed to MEDIA. Do you want to continue changing tablespace types for other objects?[Y]:
Selecting the default, "Y" prompts you to enter the next object classification modification and selecting "N" returns you to the previous menu.
Note: Please note that Step 8: Run Migration in Batch Mode is an alternative step to the earlier migration steps. If you have followed OATM menu steps 1-7, you don’t need to perform step 8.
Select Step 8: Run Migration in Batch Mode to execute both preparatory steps and migration commands as a single flow. The OATM menu-based design provides you fine-grained control and maximum flexibility over how the objects will be migrated. If you need to use the OATM utility in a repeated fashion, Step 8: Run Migration in Batch Mode gives you a greater level of automated control.
If you select OATM Batch Mode, the OATM migration utility will first collect all the required information and then perform all necessary checks (including checks for the existence of all required scripts, invalid indexes, database log mode, and so on). If errors are encountered during the check stage, you are prompted to review the logs and correct any issues. Any errors during background execution will be recorded in the status table and the process will be terminated. Once errors are corrected, you can proceed with batch-mode-migration by restarting the process.
Menu option 8, “Run Migration in Batch Mode” has two submenus, one for the invocation of the batch-mode-migration and the other for monitoring the progress of batch mode migration. In batch mode, OATM migration utility will perform the following: a check for invalid indexes, sizing, a check for the disk space usage, a check for relevant system parameters, creation of new tablespaces, generation of migration commands, and execution of migration commands. Based on the value of the AUTO_START_MIGRATION parameter, migration will be either started automatically after preparatory steps are completed (the default); or the process will wait for your input before proceeding with migration execution (in case you want to perform some checks, back up the database, and so on). Each step will be started automatically once the previous step is finished and will be running in the background.
The overall migration status report covers all OATM process phases, that is, the creation of tablespaces, generation of migration commands, and execution of migration commands. For all migration steps, status details such as the parameters passed and the name of the log files are reported, as well as the migration progress based on the total number of the migration commands, number of successfully executed commands and number of commands that failed during execution.