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%.

(A) PREREQUISTES FOR OATM MIGRATION
==================================

Note:- Before OATM migration is started

ð  Ensure that your Oracle database version is RDBMS 9.2.0.4 or higher

ð  If the Database Version is 9.2.0.6 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.

XXCUSTOM

 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 

(B) Install OATM Migration Utility

===========================

Applying the patch 3942506 ( AOL/FND : OATM MIGRATION UTILITY ROLLUP C ) and we can get the script for OATM migration ( fndtsmig.pl)

  

SQL> select bug_number, from ad_bugs where bug_number='3942506';

        

BUG_NUMBER  

      -----------      

3942506         

2. Verify OATM migration script, $FND_TOP/bin/fndtsmig.pl, 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 fndtsmig.pl

-rwxr-xr-x    1 applmgr  dba          167359 Mar  9 2009  fndtsmig.pl

[ajithpathiyilap:applmgr:/u023/app/applmgr/AJITHLAB/appltop/fnd/11.5.0/bin]perl $FND_TOP/bin/fndtsmig.pl

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

(C) Defer the standby log destinations and put the database in no archivelog mode

===================================================================

$ sqlplus '/as sysdba'

SQL> startup mount

SQL> alter database noarchivelog

SQL> alter database open

(D) Update the DB parameters accordingly

==================================

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

(E) Extend the temp tablespace with a higher value

==========================================

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

OATM TABLESPACE MIGRATION UTILITY MENU Please perform the MIGRATION using with OATM

(G) Extend the temp tablespace with a higher value

==========================================

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

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

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

(H) Create Tablespace Script

==========================================

(I) Create OATM Tablespaces

==========================================

(J) Invalid Indexes Report

==========================================

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

(K) Generate Migration Commands For All Schemas

==========================================

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

(L) Generate Migration Commands For CTXSYS

==========================================

ð  Generate migration commands for CTXSYS schemas.

(M) Set Auto-Extend ON For All New Datafiles

==========================================

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

(N) Execute Migration Commands

==========================================

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

(O) Generate Migration Status Report

==========================================

ð    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.

(P) Enable Trigger & Constraints

==========================================

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

(Q) Drop Empty Tablespaces

==========================================

ð  After migration completes, first resize, then drop the tablespaces to recover the space.

ð     Run fndtsdrp.sql generated in previous step to drop the tablespaces

(R) Recompile Database Objects(utlrp.sql)

==========================================

ð    Run utlrp.sql script from database to reduce the invalid object.

      $ORACLE_HOME/rdbms/admin/utlrp.sql

ð    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.

(S)Unlock Application Accounts

==========================================

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%';

(T)Recreate Grants & Synonyms

==========================================

ð    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.

1        ISSUES FACED DURING MIGRATION

Issue -1

Issue -2

Issue -3

Issue -4 

HAPPY LEARNING!