OATM stands for Oracle Applications tablespace model. As per this new model, All the tables ,view etc are categorized in followings types
Tables and Indexes:
Tables are allocated to a tablespace based on their classification. Each table is classified as one of the following
- TRANSACTION: Tables that are populated by data entry screens or batch processes and used in transactions
- SEED: Seed/static data – Static tables where little or no growth is expected
- INTERFACE: Interface/transient – transient data that may be purged or deleted
- NOLOGGING: ‘Scratchpad’ data that is created and dropped in a given workflow – no archive logging
- SUMMARY: Materialized view and other summary data for reporting; data is refreshed on a regular basis
Transaction tables are to be mapped to the TRANSACTION_TABLES tablespace, seed tables map to the APPS_TS_SEED tablespace and interface tables map to the INTERFACE tablespace.
Indexes are allocated based on the classification of the table whose columns are being indexed. For example, if the AR_ADJUSTMENTS_ALL table was classified as a transaction table it would be allocated to the TRANSACTION_TABLE tablespace and its indexes (AR_ADJUSTMENTS_U1, 2 ….) would be allocated to the TRANSACTION_INDEX tablespaces. Indexes on other types of table (currently seed and interface) are assigned to the same tablespace as the table
The NOLOGGING tablespace is reserved for data that does not require archive logging. Transient/temporary data that does require archive logging should be placed in the INTERFACE tablespace.
Temporary Data
The TEMP tablespace is reserved for the database’s temporary data. Future versions of the database (10i) will provide additional control facilities for temporary data. We will use these to separate global temporary table data from other temporary storage such as sorts and hash joins.
Undo, System and Default Tablespaces
The UNDO tablespace is for automatic undo management. Customers may create a conventional rollback tablespace or additional undo tablespaces (e.g., for RAC installs) if they want to. The SYSTEM tablespace is reserved for the SYS and SYSTEM users. The default tablespace for all other database users will be TRANSACTION_TABLES. The temporary tablespace for each user will be TEMP. The default temporary tablespace for the database will be TEMP.
OATM(Oracle Applications tablespace model) Utility
OATM(Oracle Applications tablespace model) migration is happened through the Perl utility
$FND_TOP/bin/fndtsmig.pl
Migration Sizing report
First step of OATM migration is to determine the Migration Sizing requirement through Option 1 of the OATM menu
We need to run the OATM utlity through application manager OS owner. In this case we are assuming that to be applmgr
[applmgr] cd $APPL_TOP/admin [applmgr] perl $FND_TOP/bin/fndtsmig.pl Enter OATM configuration file or press enter to continue: <ENTER> Enter APPL_TOP[/xyz/oracle/xyzappl]: <ENTER> Enter FND_TOP[/xyz/oracle/xyzappl/fnd/11.5.0]: <ENTER> Enter the Database Connect String[TEST]: <ENTER> Enter the password for your 'SYSTEM' ORACLE schema: <SYSTEM-Password> Please create log directory /xyz/oracle/xyzappl/admin/test/log to continue: Create the path Enter the ORACLE APPS Schema name[APPS]: <ENTER> Enter the password for APPS: <APPS-Password> Enter the ORACLE Application Object Library [APPLSYS]: ================================================================ CONNECT_STRING=TEST FND_TOP=/xyz/oracle/xyzappl/fnd/11.5.0 APPLSYS_SCHEMA=APPLSYS APPS_SCHEMA=APPS APPL_TOP=/xyz/oracle/xyzappl Please review: press 'Y' to accept or 'N' to re-enter[Y]: <ENTER> Oracle Applications Tablespace Migration Utility
Please enter your option – 1
- This menu gives a set of reports to estimate the size of the tablespaces in the new tablespace model.
- Sizing estimation is performed by executing a program that calculates the size of each object using DBMS_SPACE.UNUSED_SPACE package and populates FND_TS_SIZING table. The sizing reports use the data in this table to display the required information
Option 1: It will list all oracle application products schemas that can be migrated
Option 2:Calculate total space for all schemas ,Compare reports with different extent sizes (64k > 512k) for best size ,smaller extent size generates more extents (Redo-Chunk-Allocation performance hits) ,larger extent size requires more space
For Option-2
Please enter your option – 2
Extent Allocation: A(utoallocate) or U(niform Size)[U]: <ENTER>
Enter Uniform Extent Size Tablespaces in KBytes[1024]: <ENTER>
Report created /xyz/oracle/xyzappl/admin/TEST/log/fndtrep0.txt
Report created /xyz/oracle/xyzappl/admin/TEST/log/fndtrep1.txt
Create New Tablespaces
Now we will need to create the new tablespace where data will be migrated
Oracle Applications Tablespace Migration Utility
We need to choose Option -2
We can choose either option 1 to create new tablespace script or directly create the tablespace using option 2
I personally prefer option 1 as in the generate script you can managed ,monitor and change whichever we way you like
So by choosing option 1
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 Extent Allocation type A(utoallocate) or U(niform Extent Size)[U]: A Enter the absolute path for the datafiles directory: /xyz/oracle/DATA Enter the Number of Datafiles for APPS_TS_TX_DATA tablespace[1]: 100 Enter the Datafile Size for APPS_TS_TX_DATA tablespace (MB): 10240 Enter the Number of Datafiles for APPS_TS_TX_DATA tablespace: 100 Enter the Number of Datafiles for APPS_TS_TX_IDX tablespace[1]: 50 Enter the Datafile Size for APPS_TS_TX_IDX tablespace (MB): 10240 Enter the Number of Datafiles for APPS_TS_SEED tablespace[1]: 1 Enter the Datafile Size for APPS_TS_SEED tablespace (MB): 10240 Enter the Number of Datafiles for APPS_TS_INTERFACE tablespace[1]: 2 Enter the Datafile Size for APPS_TS_INTERFACE tablespace (MB): 10240 Enter the Number of Datafiles for APPS_TS_SUMMARY tablespace[1]: 2 Enter the Datafile Size for APPS_TS_SUMMARY tablespace (MB): 10240 Enter the Number of Datafiles for APPS_TS_NOLOGGING tablespace[1]: 2 Enter the Datafile Size for APPS_TS_NOLOGGING tablespace (MB): 10240 Enter the Number of Datafiles for APPS_TS_ARCHIVE tablespace[1]: 2 Enter the Datafile Size for APPS_TS_ARCHIVE tablespace (MB): 10240 Enter the Number of Datafiles for APPS_TS_QUEUES tablespace[1]: 5 Enter the Datafile Size for APPS_TS_QUEUES tablespace (MB): 10240 Enter the Number of Datafiles for APPS_TS_MEDIA tablespace[1]: 5 Enter the Datafile Size for APPS_TS_MEDIA tablespace (MB): 10240 Enter the Number of Datafiles for APPS_TS_TOOLS tablespace[1]: 1 Enter the Datafile Size for APPS_TS_TOOLS tablespace (MB): 10240 Tablespace creation script /xyz/oracle/xyzappl/admin/TEST/log/crtts.sql created
Now we can edit the script as required and use this script to create the tablespace
Generate Migration Commands
The next space after tablespace creation would be generating migration commands
[applmgr] cd $APPL_TOP/admin [applmgr] perl $FND_TOP/bin/fndtsmig.pl Enter OATM configuration file or press enter to continue: <ENTER> Enter APPL_TOP[/xyz/oracle/xyzappl]: <ENTER> Enter FND_TOP[/xyz/oracle/xyzappl/fnd/11.5.0]: <ENTER> Enter the Database Connect String[TEST]: <ENTER> Enter the password for your 'SYSTEM' ORACLE schema: <SYSTEM-Password> Please create log directory /xyz/oracle/xyzappl/admin/test/log to continue: Create the path Enter the ORACLE APPS Schema name[APPS]: <ENTER> Enter the password for APPS: <APPS-Password> Enter the ORACLE Application Object Library [APPLSYS]: <ENTER> ================================================================ CONNECT_STRING=TEST FND_TOP=/xyz/oracle/xyzappl/fnd/11.5.0 APPLSYS_SCHEMA=APPLSYS APPS_SCHEMA=APPS APPL_TOP=/xyz/oracle/xyzappl Please review: press 'Y' to accept or 'N' to re-enter[Y]: <ENTER> Oracle Applications Tablespace Migration Utility
Please enter your option – 3
Option 1: It should be run to find out the invalid index report.Then these index either should be dropped or recreated
Option 2: Generate migration commands for all schemas.Run Option 1 to generate the commands for migrating the objects in all the schemas 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 asked 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 will always get executed sequentially irrespective of their size.
Option 3 is to generate the commands for migrating the objects in list of schema 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 asked 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
will get executed sequentially. Migration commands for objects with total blocks less than threshold are generated with NOPARALLEL clause and will get executed in parallel using multiple processes. Partitioned objects will always get executed sequentially irrespective of their size.
Please enter your option – 2
Enter the threshold blocks for Sequential processing[10000]: <Enter>
Command generation successful. Please check the log
$APPL_TOP/admin/$TWO_TASK/log/fndgmcmd*.log
Execute Migrate command
The next space after generation of migration command would be executing the commands.Here is the final Oracle applications tablespace Migration happens
We need to follow below things before moving on this steps
(1) Take a full backup of the database
(2) The migration is very I/O intensive,So we need to make sure everything is good from OS and network perspective
(3) Check all the database parameter and set as per the guidelines for optimal performance
(4) There should be enough TEMP tablespace for this to succeed
[applmgr] cd $APPL_TOP/admin [applmgr] perl $FND_TOP/bin/fndtsmig.pl Enter OATM configuration file or press enter to continue: <ENTER> Enter APPL_TOP[/xyz/oracle/xyzappl]: <ENTER> Enter FND_TOP[/xyz/oracle/xyzappl/fnd/11.5.0]: <ENTER> Enter the Database Connect String[TEST]: <ENTER> Enter the password for your 'SYSTEM' ORACLE schema: <SYSTEM-Password> Please create log directory /xyz/oracle/xyzappl/admin/test/log to continue: Create the path Enter the ORACLE APPS Schema name[APPS]: <ENTER> Enter the password for APPS: <APPS-Password> Enter the ORACLE Application Object Library [APPLSYS]: <ENTER> ================================================================ CONNECT_STRING=TEST FND_TOP=/xyz/oracle/xyzappl/fnd/11.5.0 APPLSYS_SCHEMA=APPLSYS APPS_SCHEMA=APPS APPL_TOP=/xyz/oracle/xyzappl Please review: press 'Y' to accept or 'N' to re-enter[Y]: <ENTER> Oracle Applications Tablespace Migration Utility
Please enter your option – 4
Please enter your option – 1
Please enter your option - 1 Are you sure you want to migrate all schemas[N]: Y Enter the maximum number of parallel processes for TEST[15]: 15 Starting the Migration process for all schemas. Please wait... Migration processes for tables with LONG and LONG RAW columns started in the background. Please monitor the log file //xyz/oracle/xyzappl/admin/TEST/log/fndmlongzdfff.log for errors and completion of this migration process. Sequential migration process started in the background for TEST. Please monitor the log file /xyz/oracle/xyzappl/admin/TEST/log/fndemseqDDDdd.log for errors and completion of this migration process. Parallel migration processes started in the background for TEST. Please monitor the log file //xyz/oracle/xyzappl/admin/TEST/log/fndemcmdufyrufuf.log for errors and completion of this migration process.
OATM Utility processes are running in the background. sql*plus sessions and one JRE session are spawned in the background. You can monitor those sessions from the OS. Please donot kill any of those sessions as it will adversely affect migration process and result in errors. Monitor migration by running migration progress report.
The session overview can be done using the below query
sqlplus /nolog
CONNECT / as SYSDBA
SELECT status, module, sid, serial#
FROM v$session
WHERE module in
('TS_MIGRATE_SEQUENTIAL_OBJECTS',
'TS_MIGRATE_PARALLEL_OBJECTS', 'TS_MIGRATE_LONGS',
'TS_SIZING', 'TS_GEN_SCRIPT', 'TS_SET_DEFAULTS',
'TS_CREATE_TABLESPACE', 'TS_DISABLE_CMDS',
'TS_GENERATE_STATEMENTS', 'TS_POSTMIGRATION_STEPS')
AND status <> 'KILLED';
Migration processes for tables with LONG and LONG RAW columns runs in a single session.
Sequential migration process started in the background . It is run with parallel option.So it used parallel_max_servers as the parameter to maximize the throughput
alter table AP.AP_CHECKS_ALL move APPS_TS_TX_DATA parallel;
Parallel migration processes started in the background. It is run with number of parallel threads given with no parallel option
alter table APPLSYS.FND_USERS move APPS_TS_SEED noparallel;
Migration Status Reports
The next space when execution of migration command happen would be to check and monitor it. How much it is completed
[applmgr] cd $APPL_TOP/admin [applmgr] perl $FND_TOP/bin/fndtsmig.pl Enter OATM configuration file or press enter to continue: <ENTER> Enter APPL_TOP[/xyz/oracle/xyzappl]: <ENTER> Enter FND_TOP[/xyz/oracle/xyzappl/fnd/11.5.0]: <ENTER> Enter the Database Connect String[TEST]: <ENTER> Enter the password for your 'SYSTEM' ORACLE schema: <SYSTEM-Password> Please create log directory /xyz/oracle/xyzappl/admin/test/log to continue: Create the path Enter the ORACLE APPS Schema name[APPS]: <ENTER> Enter the password for APPS: <APPS-Password> Enter the ORACLE Application Object Library [APPLSYS]: <ENTER> ================================================================ CONNECT_STRING=TEST FND_TOP=/xyz/oracle/xyzappl/fnd/11.5.0 APPLSYS_SCHEMA=APPLSYS APPS_SCHEMA=APPS APPL_TOP=/xyz/oracle/xyzappl Please review: press 'Y' to accept or 'N' to re-enter[Y]: <ENTER> Oracle Applications Tablespace Migration Utility
Enter option 5
Option 1:It will give us report how much it is completed in % term.
Option 2 :it can be used to find if any error happened in some objects
The below command can be used to find the migration status
SELECT migration_status, count(*) FROM APPS.FND_TS_MIG_CMDS GROUP BY migration_status
This completes the OATM(Oracle Applications tablespace model) migration. Please let me know if questions
Also Reads
Oracle EBS Administration Tutorials
how to change default tablespace in oracle
How to check temp tablespace in Oracle
https://en.wikipedia.org/wiki/Oracle_Applications