Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Thursday, December 4, 2008

Resume OATM migration after errors

OATM migration process details are stored in a table FND_TS_MIG_CMDS.  This is the structure of the table:

SQL> desc fnd_ts_mig_cmds
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LINENO                                    NOT NULL NUMBER
 SUBOBJECT_TYPE                                     VARCHAR2(30)
 START_DATE                                         DATE
 OWNER                                     NOT NULL VARCHAR2(30)
 OBJECT_TYPE                                        VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(80)
 OLD_TABLESPACE                                     VARCHAR2(30)
 NEW_TABLESPACE                                     VARCHAR2(30)
 MIGRATION_CMD                                      VARCHAR2(4000)
 MIGRATION_STATUS                                   VARCHAR2(30)
 ERROR_TEXT                                         VARCHAR2(4000)
 LAST_UPDATE_DATE                                   DATE
 GENERATION_DATE                                    DATE
 END_DATE                                           DATE
 TOTAL_BLOCKS                                       NUMBER(15)
 INDEX_PARALLEL                                     VARCHAR2(10)
 PARENT_OBJECT_NAME                                 VARCHAR2(30)
 PARENT_LINENO                                      NUMBER
 EXECUTION_MODE                                     VARCHAR2(1)
 PARTITIONED                                        VARCHAR2(3)
 PARENT_OWNER                                       VARCHAR2(30)

The MIGRATION_STATUS column stores the status of the object to be migrated.  The valid values for MIGRATION_STATUS column are:

GENERATED: Migration command has been generated but not executed.
SUCCESS       : Migration successfully completed.
ERROR          : Migration errored out

You can check the details of error by querying the ERROR_TEXT column of the table.

To resume an OATM migration, you need to do the steps described in metalink note 369198.1:
1. Stop all services except the Database

2. Bounce DB to be sure locks are released

3. Restart migration with next steps :

3.1. Make sure OATM migration is stopped , running the following at the OS level :

ps -ef | grep fndtsmig

3.2. Stop the migration queue, running from sqlplus as the apps user :

exec dbms_aqadm.stop_queue('SYSTEM.TBLMIG_MESSAGEQUE');

3.3. Restart the OATM migration one more time :


You should also check Metalink Note 463271.1:

) Run the 'Migration Error Report' and correct the errors that cause the migration to fail.

2) Make sure that queue 'TBLMIG_MESSAGEQUE' is started

from dba_queues
where owner ='SYSTEM'
and name = 'TBLMIG_MESSAGEQUE' ;
In case the queue is not started, run from sqlplus as the system user:

exec dbms_aqadm.start_queue('SYSTEM.TBLMIG_MESSAGEQUE');  

3) Run the migration command again and the migration utility will try to move the objects which are still in the old tablespaces

No comments: