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
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
$ 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
OATM TABLESPACE
MIGRATION UTILITY MENU
Please perform the MIGRATION using with OATM
ð
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.
$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.
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.
1
ISSUES FACED DURING
MIGRATION
Issue -1
Issue -2
Issue -3
Issue -4
HAPPY LEARNING!
