Home » Oracle » Oracle Ebuisness Suite » Oracle Applications tablespace model (OATM)

Oracle Applications tablespace model (OATM)

OATM stands for Oracle Applications tablespace model. As per this new model, All the tables ,view etc are categorized in followings types

Oracle Applications tablespace model

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

See also  Key Technology changes in E-Business Suite 12.2
  • 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

See also  How to Configure Auto Login TDE Wallet

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
Oracle Applications tablespace model (execute migration commands)

Please enter your option – 4

See also  oracle PLSQL interview questions

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
Oracle Applications tablespace model (Run Migration status reports)

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

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top