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 :

perl fndtsmig.pl

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

select NAME, ENQUEUE_ENABLED, DEQUEUE_ENABLED
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