Oracle & MySQL Support

InfraStack-Labs Oracle & MySQL DBA Services help you manage, maintain, and optimize your critical Oracle systems. We deliver 24/7, year-round support with flexible monthly contracts that don’t lock you in.

Please contact me :-

Wednesday, July 10, 2013

OATM Migration - A Part Of R12 Upgrade

For R12 upgrade, OATM migration is required. This will consolidate over 300 tablespaces into a total of only 9 tablespaces. In 11i system, OATM is not applied, so we have to migrate the table spaces to OATM model. Later, increment tablespaces APPS_TS_TX_DATA and APPS_TS_TX_IDX by at least 10%.


Note:- Before OATM migration is started
ð  Ensure that your Oracle database version is RDBMS or higher
ð  If the Database Version is Then Run $FND_TOP/patch/115/sql/fndupglb.sql
ð  Unregister the CUSTOM SCHEMAS if we don’t want to migrate (Explained Below)

ð  Register the CTXSYS schema (because is not an APPS schema ) (Explained Below)

ð  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

 System Administrator Responsibility -> Security: ORACLE ->  Register

Select Schema CTXSYS and need to set the Privilege to "Enabled

ð  Disable the Schemas which are not required to migrate like CUSTOM Schemas. In our case we are not moving the objects for custom below custom schemas.


 System Administrator Responsibility -> Security: ORACLE ->  Register

Select Schema XXCUSTOM and need to set the Privilege to Disable

NOTE: In some cases, you may not want to migrate some of your schemas such as non-Oracle schemas that are registered with Oracle Applications. To accomplish this, you must disable those schemas 

Applying the patch 3942506 ( AOL/FND : OATM MIGRATION UTILITY ROLLUP C ) and we can get the script for OATM migration (
SQL> select bug_number, from ad_bugs where bug_number='3942506';

2. Verify OATM migration script, $FND_TOP/bin/, is present or not

[ajithpathiyilap:applmgr:/home/applmgr]cd $FND_TOP/bin
[ajithpathiyilap:applmgr:/u023/app/applmgr/AJITHLAB/appl/fnd/11.5.0/bin]ls -ltr
-rwxr-xr-x    1 applmgr  dba          167359 Mar  9 2009
[ajithpathiyilap:applmgr:/u023/app/applmgr/AJITHLAB/appltop/fnd/11.5.0/bin]perl $FND_TOP/bin/

Parameters are needed while starting OATM utility as per note ID 404954.1

$ sqlplus '/as sysdba'
SQL> startup mount
SQL> alter database noarchivelog
SQL> alter database open

Before start the OATM migration we need to increase the value for below DB parameters like below
Incease SGA, Increase undo_retention, job_queue_processes=0, aq_tm_processes=0,    (required for OATM)

$ sqlplus '/as sysdba'
SQL> alter system set sga_max_size=2G scope=spfile;
SQL> alter system set undo_retention=10200 scope=spfile;
SQL> alter system set job_queue_processes=0 scope=spfile;
SQL> alter system set aq_tm_processes=0 scope=spfile;

Shut down the db and Startup it again then check the replication of the parameters

Resize the Temp Tablespace with 40GB

$sqlplus ‘/as sysdba’
SQL> alter tablespace temp add tempfile ‘+DATA_FILE’ size 10g;

(F) Invoke the Oracle Applications Tablespace Migration Utility (fndtsmig.sql)

Go to $FND_TOP/bin and run the perl script fndtsmig.sql 
NOTE # if the script is not there then apply the patch as per step 2.3 in the above #
Please pass the parameters (please see below example for require fields) and it will invoke the


ð  To Determine the space how much require to complete the OATM migration activity we need to select 1 option (Migration Sizing Reports ) 

ð  under 1st  Option we have to select 2nd  option to get the space requirement.

ð  Based on this Sizing Report we need to get the storage ( space ) and continue

ð  Invalid Indexes Report. Please correct/drop these before generating migration commands

ð  Generate migration commands now for migration of all the schemas.

ð  Generate migration commands for CTXSYS schemas.

SQL> spool autoextend_ts.sql
SQL> select 'alter database datafile ''' || file_name || ''' ' || ' autoextend on;' from dba_data_files;
SQL> spool off    
Ran the autoextend_ts.sql to set auto extend on for all the data files to avoid, issues while running the migration

ð  Execute migration commands now for migration of all the schemas.

ð    Migration progress can be monitored by checking the status report.

ð  We may have to re – run migration for schemas which are not migrated 100% till the point we have a 100% migration of tables and indexes.

ð  Once migration commands completes with 100% status, enable the triggers and constraints. 

ð    Run utlrp.sql script from database to reduce the invalid object.
ð    Run additional compile script to compile the objects manually as more than 90000 objects will be invalid due to OATM.
Run below script for reference.

OATM migration locks down the application accounts, hence verify that all accounts are open as before.
Use below sql to create a script to open the accounts.
select ' Alter user '||username|| ' account unlock;' from dba_users where ACCOUNT_STATUS like '%LOCK%';

ð    Run Use adadmin to rec create grants and synonyms in APPS schema. We should check the invalid objects in each stage after OATM migration to monitor object count. Run utlrp.sql script from database to reduce the invalid object.


Issue -1

Issue -2

Issue -3

Issue -4 



  1. Excellent Job.
    Step by Step Approach

    1. Thanks Buddy...Hope you enjoy this blogpost, keep sending me your feedbacks

  2. Thanks for the post, Ajith. its quite useful !

  3. Hi Ajit,

    I am doing OATM migration for Oracle EBS and database version is :

    I am unable to migrate APPLSYS.FND_LOBS table. I am getting below error :

    Migration Error Report

    Report Date : September 10, 2015 PAGE: 1
    Object Object SubObject
    Schema name type type
    ------- ------------------------------ ---------- -------------
    Migration Error
    ORA-01013: user requested cancel of current operation

    Report created /stgebs/apps/stgebsappl/admin/stgebs/log/fndtrep10.txt

    Press Return key to continue...

    I have restarted the migration of APPLSYS schema, but still getting the same error. So do you have any idea how to fix it.




Thanks for you valuable comments !