How to run OATM migration utility Note: 404954.1
Applies to: Oracle Application Object Library - Versio 11.5.10.2
Information in this document applies to any platform.
Goal
1- Review OATM Model for Oracle Applications 11i
2- Install OATM migration utility
3- Run OATM migration utility
4- Parameters needed while starting OATM utility
5- OATM Menu Structure
Solution
1- Review OATM Model for Oracle Applications 11i
The Oracle Applications Tablespace Model (OATM) introduces a new, consolidated tablespace model in Oracle Applications uses 12 locally managed tablespaces for all products including the temporary tablespace, system tablespace, and undo segments. In this revised Oracle Applications Tablespace Model, each Oracle Applications database object is mapped to a tablespace based on its Input/Output characteristics. This new model allows for easier maintenance, reduces space usage, and allows for potential run-time performance gains.
The Oracle Applications Tablespace Migration Utility is an interactive menu-based PERL program including a set of sizing estimate reports. It enables customers to convert their Oracle Applications databases to the new tablespace model (OATM) for either all schemas at once or partially for select schema(s) at a time, depending on acceptable down time and available disk space.
You can find more information about OATM migration utility in the following note:
Note 248857.1 - OATM Release 11i - Tablespace Migration Utility
Also please refer to Oracle Documentation:
http://download-uk.oracle.com/docs/cd/B25516_14/current/acrobat/115sacg.zip
2- Install OATM migration utility
You can install the OATM migration utility by downloading & applying the following patch through Metalink:Patch 3942506 - AOL/FND: OATM MIGRATION UTILITY ROLLUP C
(Note: Above patch is already included in 11.5.10 and higher, so if you have Oracle Applications 11.5.10 or higher you do not need to install it)
/u01/MIG/apps/prodappl/admin/mig/log
3- Run OATM migration utility
After installing the utility through above patch, please use the following command in order to start running the utility:
perl $FND_TOP/bin/fndtsmig.pl
4- Parameters needed while starting OATM utility
After running the PERL script in order to start the OATM utility, the following parameters screen will appear:
+=======================================================================+
Copyright (c) 2003 Oracle Corporation Redwood Shores, California, USA
All rights reserved.
+=======================================================================+
Welcome to the Oracle Applications Tablespace Migration Utility
+=======================================================================+
Warning: If DB was upgraded from 8.1.7 to a version prior to 9.2.0.6
please run $FND_TOP/patch/115/sql/fndupglb.sql before the migration.
+=======================================================================+
Enter OATM configuration file or press enter to continue:
Enter APPL_TOP [/u01/MIG/apps/prodappl]:
Enter FND_TOP [/u01/MIG/apps/prodappl/fnd/11.5.0]:
Enter the Database Connect String [MIG]:
Enter the password for your 'SYSTEM' ORACLE schema:
Successfully connected to database MIG using SYSTEM user.
Enter the ORACLE APPS Schema name[APPS]: apps
Enter the password for APPS:
Successfully connected to database MIG using APPS user.
Enter the ORACLE Application Object Library Schema name[APPLSYS]: applsys
================================================================
CONNECT_STRING =MIG
FND_TOP =/u01/MIG/apps/prodappl/fnd/11.5.0
APPLSYS_SCHEMA =APPLSYS
APPS_SCHEMA =APPS
APPL_TOP =/u01/MIG/apps/prodappl
================================================================
Please review and press 'Y' to accept or 'N' to re-enter[Y]: y
Performing database check...
Database check completed.
Checking required components...
Required components check completed.
5- OATM Menu Structure
After providing all parameters as in the previous step, you will get the Main Menu for Oracle Applications Tablespace Migration Utility which consists of 8 options as following:
Important Note: Some Menu Options are available only on 11.5.10.2 and later such as: Migrate CTXSYS Schema, Generate script to drop empty tablespaces.
Oracle Applications Tablespace Migration Utility
Main Menu
1. Migration Sizing Reports
2. Create New Tablespaces
3. Generate Migration Commands
4. Execute Migration Commands
5. Run Migration Status Reports
6. Run Post Migration Steps
7. Run Customization Steps
8. Run Migration in Batch Mode
[Q]uit [N]ext
Please enter your option -
Here are the details for each menu option:
5.1. Migration Sizing Reports
Migration Sizing Reports
These reports are provided to help you gauge the space requirements for the new Tablespaces. Additionally, they will also help you determine the type of migration approach that best suits your needs
1. Generate a Report with the list of all the Oracle Application product schemas that can be migrated
2. Calculate total space required by each new tablespace to migrate all Oracle Application product schemas (relevant for a complete migration)
3. Calculate total space required by each new tablespace to migrate each Oracle Application product schema (relevant for a schema-by-schema migration)
4. Calculate total space required by each Oracle Applications schema, with details for each object
5. Display Sizing Exception report
[Q]uit [B]ack [N]ext
Please enter your option -
5.2. Create New Tablespaces
Create New Tablespaces
1. Generate new tablespace creation script
2. Create new tablespaces
[Q]uit [B]ack [N]ext
Please enter your option -
5.3. Generate Migration Commands
Generate Migration Commands
Generation of Migration commands including disable/enable
commands for triggers, constraints, policies, stop/start for queues.
1. Invalid Indexes Report. Please correct/drop these before generating migration commands
2. Generate migration commands for all schemas
3. Generate migration commands for a list of schemas
[Q]uit [B]ack [N]ext
Please enter your option -
5.4. Execute Migration Commands
Execute Migration Commands
Execution of Migration commands including disable commands for triggers constraints, stop/start for queues.
PLEASE TAKE A COMPLETE BACKUP OF THE DATABASE BEFORE MIGRATION
1. Migrate all Schemas
2. Migrate a list of Schemas
3. Migrate CTXSYS Schema
Note: Migrate CTXSYS schema when no other migration process is in progress.
[Q]uit [B]ack [N]ext
Please enter your option -
5.5. Run Migration Status Reports
Run Migration Status Reports
1. Run migration progress report
2. Run migration error report
[Q]uit [B]ack [N]ext
Please enter your option -
5.6. Run Post Migration Steps
Run Post Migration Steps
1. Run audit report
2. Enable constraints/triggers/policies & start queues
3. Resize old tablespaces
4. Generate script to drop empty tablespaces
Note: Please complete refresh all the Materialized Views
in Oracle Applications before using the system.
[Q]uit [B]ack
Please enter your option -
5.7. Customization
Customization
1. Register new tablespace - tablespace type
2. Change name of the existing tablespace
3. Register object classification
4. Change object classification
[Q]uit [B]ack
Please enter your option -
5.8. Batch Mode Migration
Batch Mode Migration
1. Run Migration in Batch Mode
2. Run Migration Monitor
[Q]uit [B]ack
Please enter your option -
OATM migration
applprod@vezora1:~ $ cd /u01/MIG/apps/prodappl/fnd/11.5.0/bin
applprod@vezora1:/u01/MIG/apps/prodappl/fnd/11.5.0/bin $ perl fndtsmig.pl
+=======================================================================+
Copyright (c) 2003 Oracle Corporation Redwood Shores, California, USA
All rights reserved.
+=======================================================================+
Welcome to the Oracle Applications Tablespace Migration Utility
+=======================================================================+
Warning: If DB was upgraded from 8.1.7 to a version prior to 9.2.0.6
please run $FND_TOP/patch/115/sql/fndupglb.sql before the migration.
+=======================================================================+
Enter OATM configuration file or press enter to continue:
Enter APPL_TOP[/u01/MIG/apps/prodappl]:
Enter FND_TOP[/u01/MIG/apps/prodappl/fnd/11.5.0]:
Enter the Database Connect String[MIG]:
Enter the password for your 'SYSTEM' ORACLE schema:
Successfully connected to database MIG using SYSTEM user.
Please create log directory /u01/MIG/apps/prodappl/admin/mig/log to continue:
Enter the ORACLE APPS Schema name[APPS]: ma6utoza
Enter the ORACLE APPS Schema name[APPS]:
Enter the password for APPS:
Successfully connected to database MIG using APPS user.
Enter the ORACLE Application Object Library Schema name[APPLSYS]:
================================================================
CONNECT_STRING =MIG
FND_TOP =/u01/MIG/apps/prodappl/fnd/11.5.0
APPLSYS_SCHEMA =APPLSYS
APPS_SCHEMA =APPS
APPL_TOP =/u01/MIG/apps/prodappl
================================================================
Please review and press 'Y' to accept or 'N' to re-enter[Y]: Y
Performing database check...
Database check completed.
Checking required components...
Required components check completed.
Oracle Applications Tablespace Migration Utility
Main Menu
1. Migration Sizing Reports
2. Create New Tablespaces
3. Generate Migration Commands
4. Execute Migration Commands
5. Run Migration Status Reports
6. Run Post Migration Steps
7. Run Customization Steps
8. Run Migration in Batch Mode
[Q]uit [N]ext
Please enter your option - 1
Migration Sizing Reports
These reports are provided to help you gauge the space requirements for the new Tablespaces. Additionally, they will also help you determine the type of migration approach that best suits your needs
1. Generate a Report with the list of all the Oracle Application product schemas that can be migrated
2. Calculate total space required by each new tablespace to migrate all Oracle Application product schemas (relevant for a complete migration)
3. Calculate total space required by each new tablespace to migrate each Oracle Application product schema (relevant for a schema-by-schema migration)
4. Calculate total space required by each Oracle Applications schema, with details for each object
5. Display Sizing Exception report
[Q]uit [B]ack [N]ext
Please enter your option - 1
Migration Progress Report
Report Date : February 14, 2008 PAGE: 1
Total generated % commands
Schema commands successfully executed
--------------- --------------- -----------------------------------------
ABM 0 0%
AHL 0 0%
AHM 0 0%
AK 0 0%
ALR 0 0%
AMF 0 0%
………………………
…………………………
………………….
XNB 0 0%
XNC 0 0%
XNI 0 0%
XNM 0 0%
XNP 0 0%
XNS 0 0%
XTR 0 0%
ZFA 0 0%
ZPB 0 0%
ZSA 0 0%
ZX 0 0%
Report created /u01/MIG/apps/prodappl/admin/mig/log/fndtrep0.txt
Press Return key to continue...
Migration Sizing Reports
These reports are provided to help you gauge the space requirements for the new Tablespaces. Additionally, they will also help you determine the type of migration approach that best suits your needs
1. Generate a Report with the list of all the Oracle Application product schemas that can be migrated
2. Calculate total space required by each new tablespace to migrate all Oracle Application product schemas (relevant for a complete migration)
3. Calculate total space required by each new tablespace to migrate each Oracle Application product schema (relevant for a schema-by-schema migration)
4. Calculate total space required by each Oracle Applications schema, with details for each object
5. Display Sizing Exception report
[Q]uit [B]ack [N]ext
Please enter your option - 2
Sizing Program has never been run.
Enter Uniform Extent Size for the Tablespaces in KBytes[1024]:
Executing the Sizing program for all schemas. This may take upto 20min. Please wait...
Sizing program executed successfully.
PAGE: 1
Summary Report-Amount of space by tablespace
Report Date : February 14, 2008
Last Sizing Run Date : February 14, 2008
Tablespace Extent Management - Local
Allocation type -Uniform Extents<1024kb>
Current space
Recommended taken up by
Estimated space space in KB existing
Tablespace Name required in KB (20% Buffer) objects in KB
------------------------------ ---------------- --------------- ---------------------------------------
APPS_TS_ARCHIVE 6,228,992 7,474,791 2,061,240
APPS_TS_INTERFACE 15,163,392 18,196,071 11,089,784
APPS_TS_MEDIA 3,638,272 4,365,927 3,570,144
APPS_TS_NOLOGGING 662,528 795,034 342,200
APPS_TS_QUEUES 3,369,984 4,043,981 2,825,328
APPS_TS_SEED 7,327,744 8,793,293 2,129,744
APPS_TS_SUMMARY 6,267,904 7,521,485 1,135,208
APPS_TS_TX_DATA 61,418,496 73,702,196 50,858,192
APPS_TS_TX_IDX 66,878,464 80,254,157 45,371,752
------------------------------------- --------------- ------------------------------------------------
Total (in KB): 170,955,776 205,146,935 119,383,592
Report created /u01/MIG/apps/prodappl/admin/mig/log/fndtrep1.txt
Report created /u01/MIG/apps/prodappl/admin/mig/log/fndtrep1.txt
Press Return key to continue...
Migration Sizing Reports
These reports are provided to help you gauge the space requirements for the new Tablespaces. Additionally, they will also help you determine the type of migration approach that best suits your needs
1. Generate a Report with the list of all the Oracle
Application product schemas that can be migrated
2. Calculate total space required by each new tablespace to migrate all Oracle Application product schemas (relevant) for a complete migration)
3. Calculate total space required by each new tablespace to migrate each Oracle Application product schema (relevant for a schema-by-schema migration)
4. Calculate total space required by each Oracle Applications schema, with details for each object
5. Display Sizing Exception report
[Q]uit [B]ack [N]ext
Please enter your option - 2
Sizing Program was last run on 14-FEB-08
Do you want to run the Sizing program again before running the report[N]: Y
PAGE: 1
Summary Report-Amount of Space by Schema by Object
Report Date : February 15, 2008
Last Sizing Run Date : February 15, 2008
Tablespace Extent Management - Local
Allocation type -Uniform Extents<1024kb>
Object Estimated Space Sizing
Schema Name Required (in KB) Errors
-------------------- ------------------------------ ---------------- ------------
ABM ABM_VIEW_SEC_OBJS_U2 1,024 No Error
ABM_CALC_TMPLTS_TL_PK 1,024 No Error
ABM_CALC_TMPLT_VARS_TL_PK 1,024 No Error
ABM_CURRS_TL_PK 1,024 No Error
ABM_IMP_ENT_TYPES_TL_PK 1,024 No Error
ABM_IMP_TAGS_TL_PK 1,024 No Error
ABM_IMP_TAG_SETS_TL_PK 1,024 No Error
ABM_REP_TMPLTS_TL_PK 1,024 No Error
ABM_BOR_WIZ_CHILD_RES_TREE_PK 1,024 No Error
ABM_API_TEMPLATES_PK 1,024 No Error
ABM_API_TEMPLATE_ATTRIBUTES_PK 1,024 No Error
ABM_NAV_STEPS_PK 1,024 No Error
ABM_NAV_STEPS_U1 1,024 No Error
ABM_IMP_TAGS 1,024 No Error
ABM_IMP_TAG_ENTS 1,024 No Error
ABM_IMP_TAG_SETS 1,024 No Error
ABM_IMP_UOM_CV_RULES 1,024 No Error
ABM_LAUNCHPAD_METADATA 1,024 No Error
ABM_MAP_BASES 1,024 No Error
ABM_MAP_EXPTN_REP 1,024 No Error
ABM_MAP_FORM_LOG 1,024 No Error
ABM_MAP_FORM_MASTERS 1,024 No Error
ABM_MAP_RULE_SETS 1,024 No Error
ABM_MAT_UNIT_COST 1,024 No Error
******************** ----------------
Total (in KB) : 424,960
PAGE: 2
Summary Report-Amount of Space by Schema by Object
Report Date: February 15, 2008
Last Sizing Run Date: February 15, 2008
Tablespace Extent Management - Local
Allocation type -Uniform Extents<1024kb>
Migration Sizing Reports
These reports are provided to help you gauge the space requirements for the new Tablespaces. Additionally, they will also help you determine the type of migration approach that best suits your needs
1. Generate a Report with the list of all the Oracle Application product schemas that can be migrated
2. Calculate total space required by each new tablespace to migrate all Oracle Application product schemas (relevant for a complete migration)
3. Calculate total space required by each new tablespace to migrate each Oracle Application product schema (relevant for a schema-by-schema migration)
4. Calculate total space required by each Oracle Applications schema, with details for each object
5. Display Sizing Exception report
[Q]uit [B]ack [N]ext
Please enter your option - 5
Enter the Schema name[%]:
Running Exception Report ..............
There are no objects in errors
Report created /u01/MIG/apps/prodappl/admin/mig/log/fndtrep5.txt
Press Return key to continue...
Oracle Applications Tablespace Migration Utility
Main Menu
1. Migration Sizing Reports
2. Create New Tablespaces
3. Generate Migration Commands
4. Execute Migration Commands
5. Run Migration Status Reports
6. Run Post Migration Steps
7. Run Customization Steps
8. Run Migration in Batch Mode
[Q]uit [N]ext
Please enter your option - 2
Create New Tablespaces
1. Generate new tablespace creation script
2. Create new tablespaces
[Q]uit [B]ack [N]ext
Please enter your option - 1
*****************************************************************
The utility will append a sequence number to the tablespace name
and a .dbf extension to generate the datafile names.
Datafile size should not be greater than OS file size limit.
Please edit the generated script to change the file name/size
*****************************************************************
Enter the Extent Allocation type A(utoallocate) or U(niform Extent Size)[U]: U
Enter Uniform Extent Size for the Tablespaces in KBytes[1024]:
Enter the absolute path for the datafiles directory: /u02/MIG/dbf
Enter the Number of Datafiles for APPS_TS_TX_DATA tablespace[1]: 10
Enter the Datafile Size for APPS_TS_TX_DATA tablespace (MB): 2000
Enter the Number of Datafiles for APPS_TS_TX_IDX tablespace[1]: 10
Enter the Datafile Size for APPS_TS_TX_IDX tablespace (MB): 2000
Enter the Number of Datafiles for APPS_TS_SEED tablespace[1]: 2
Enter the Datafile Size for APPS_TS_SEED tablespace (MB): 2000
Enter the Number of Datafiles for APPS_TS_INTERFACE tablespace[1]: 5
Enter the Datafile Size for APPS_TS_INTERFACE tablespace (MB): 2000
Enter the Number of Datafiles for APPS_TS_SUMMARY tablespace[1]: 5
Enter the Datafile Size for APPS_TS_SUMMARY tablespace (MB): 2000
Enter the Number of Datafiles for APPS_TS_NOLOGGING tablespace[1]: 2
Enter the Datafile Size for APPS_TS_NOLOGGING tablespace (MB): 1000
Enter the Number of Datafiles for APPS_TS_ARCHIVE tablespace[1]: 7
Enter the Datafile Size for APPS_TS_ARCHIVE tablespace (MB): 1000
Enter the Number of Datafiles for APPS_TS_QUEUES tablespace[1]: 5
Enter the Datafile Size for APPS_TS_QUEUES tablespace (MB): 1000
Enter the Number of Datafiles for APPS_TS_MEDIA tablespace[1]: 5
Enter the Datafile Size for APPS_TS_MEDIA tablespace (MB): 1000
Tablespace creation script /u01/MIG/apps/prodappl/admin/mig/log/crtts.sql created
/u01/MIG/apps/prodappl/admin/mig/log ***scripts is in this directory **
Press Return key to continue...
Create New Tablespaces
1. Generate new tablespace creation script
2. Create new tablespaces
[Q]uit [B]ack [N]ext
Please enter your option - 2
Creating Tablespaces. Please wait...
Connected.
PL/SQL procedure successfully completed.
Tablespace created.
Tablespace created.
Tablespace created.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespaces creation script executed. Please check the log /u01/MIG/apps/prodappl/admin/mig/log/crtts20080216215009.log for errors.
Press Return key to continue...
Create New Tablespaces
1. Generate new tablespace creation script
2. Create new tablespaces
[Q]uit [B]ack [N]ext
Please enter your option - B
Oracle Applications Tablespace Migration Utility
Main Menu
1. Migration Sizing Reports
2. Create New Tablespaces
3. Generate Migration Commands
4. Execute Migration Commands
5. Run Migration Status Reports
6. Run Post Migration Steps
7. Run Customization Steps
8. Run Migration in Batch Mode
[Q]uit [N]ext
Please enter your option - 3
Generate Migration Commands
Generation of Migration commands including disable/enable
commands for triggers, constraints, policies, stop/start for queues.
1. Invalid Indexes Report. Please correct/drop these before
generating migration commands
2. Generate migration commands for all schemas
3. Generate migration commands for a list of schemas
[Q]uit [B]ack [N]ext
Please enter your option - 1
Report created /u01/MIG/apps/prodappl/admin/mig/log/fndinvld.txt
Press Return key to continue...
Press Return key to continue...
Generate Migration Commands
Generation of Migration commands including disable/enable
commands for triggers, constraints, policies, stop/start for queues.
1. Invalid Indexes Report. Please correct/drop these before generating migration commands
2. Generate migration commands for all schemas
3. Generate migration commands for a list of schemas
[Q]uit [B]ack [N]ext
Please enter your option - 2
Generating Migration commands for all schemas. This may take upto 30min. Please wait...
Command generation successful. Please check the log /u01/MIG/apps/prodappl/admin/mig/log/fndgmcmd20080216224419.log
Press Return key to continue...
Generate Migration Commands
Generation of Migration commands including disable/enable
commands for triggers, constraints, policies, stop/start for queues.
1. Invalid Indexes Report. Please correct/drop these before generating migration commands
2. Generate migration commands for all schemas
3. Generate migration commands for a list of schemas
[Q]uit [B]ack [N]ext
Please enter your option - 2
Generating Migration commands for all schemas. This may take upto 30min. Please wait...
Command generation successful. Please check the log /u01/MIG/apps/prodappl/admin/mig/log/fndgmcmd20080216224419.log
Press Return key to continue...
Generate Migration Commands
Generation of Migration commands including disable/enable
commands for triggers, constraints, policies, stop/start for queues.
1. Invalid Indexes Report. Please correct/drop these before
generating migration commands
2. Generate migration commands for all schemas
3. Generate migration commands for a list of schemas
[Q]uit [B]ack [N]ext
Please enter your option - B
Oracle Applications Tablespace Migration Utility
Main Menu
1. Migration Sizing Reports
2. Create New Tablespaces
3. Generate Migration Commands
4. Execute Migration Commands
5. Run Migration Status Reports
6. Run Post Migration Steps
7. Run Customization Steps
8. Run Migration in Batch Mode
[Q]uit [N]ext
Please enter your option - 4
Execute Migration Commands
Execution of Migration commands including disable
commands for triggers constraints, stop/start for queues.
PLEASE TAKE A COMPLETE BACKUP OF THE DATABASE BEFORE MIGRATION
1. Migrate all Schemas
2. Migrate a list of Schemas
3. Migrate CTXSYS Schema
Note: Migrate CTXSYS schema when no other migration process is in progress.
[Q]uit [B]ack [N]ext
Please enter your option - 1
Are you sure you want to migrate all schemas[N]: Y
Enter the maximum number of parallel processes for MIG[8]:
Starting the Migration process for all schemas. Please wait...
Migration processes for tables with LONG and LONG RAW columns started in the background. Please monitor the log file /u01/MIG/apps/prodappl/admin/mig/log/fndmlong20080216230605.log for errors and completion of this migration process.
Sequential migration process started in the background for MIG. Please monitor the log file /u01/MIG/apps/prodappl/admin/mig/log/fndemseq20080216230606.log for errors and completion of this migration process.
Parallel migration processes started in the background for MIG. Please monitor the log file /u01/MIG/apps/prodappl/admin/mig/log/fndemcmd20080216230622.log for errors and completion of this migration process.
OATM Utility processes are running in the background. sql*plus sessions and one JRE session are spawned in the background. You can monitor those sessions from the OS. Please do not kill any of those sessions as it will adversely affect migration process and result in errors. Monitor migration by running migration progress report.
Press Return key to return to the menu...
Press Return key to continue...
Run Post Migration Steps
1. Run audit report
2. Enable constraints/triggers/policies & start queues
3. Resize old tablespaces
4. Generate script to drop empty tablespaces
Note: Please complete refresh all the Materialized Views
in Oracle Applications before using the system.
[Q]uit [B]ack
Please enter your option - 2
Enter a comma separated list of Schema names[%]:
Enabling Constraints/triggers/policies and starting queues. Please wait...
All constraints/trigger/policies are enabled and queues started
Press Return key to continue...
Run Post Migration Steps
1. Run audit report
2. Enable constraints/triggers/policies & start queues
3. Resize old tablespaces
4. Generate script to drop empty tablespaces
Note: Please complete refresh all the Materialized Views
in Oracle Applications before using the system.
[Q]uit [B]ack
Please enter your option - 4
+=======================================================================+
Warning: Please ensure you do not have any residual data segments
in the tablespaces attempted to be dropped to avoid irrevocable
loss of data.
+=======================================================================+
Drop empty tablespace script /u01/MIG/apps/prodappl/admin/mig/log/fndtsdrp.sql has been created.
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @fndtsdrp.sql;
Tablespace dropped.
Tablespace dropped.
Tablespace dropped.
Tablespace dropped.
Tablespace dropped.
SQL> commit;
Commit complete.
OATM How to move objects left in the old tablespace after oatm migration?
Solution
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
Drop SWALLACE and SPOTLIGHT USERS
drop user spotlight cascade;
drop user swallace cascade;
Note:430604.1
CREATE TEXT INDEX DRG-11446 DROLDUS.DAT NOT INSTALLED
PATCH 4575748 OATM RE-SIZE OLD TABLESPACES DOES NOT RESIZE DATAFILES
IMPORTANT NOTE :
One-off patches are not fully certified by all Oracle products, and so there is some risk of unexpected side effects. If the fix in this patch is included in the latest released ATG Family Packs and/or ATG PF Consolidated Updates (CU), then it is highly recommended to upgrade to latest released ATG Family Pack or ATG PF Consolidated Update (CU) that is available in Recommended Patch List (Metalink Note 123456.1). It is also recommended to upgrade to latest released ATG Family Pack or ATG PF Consolidated Update (CU) before applying this patch.
This patch provides implementation that will correctly resize database file after migration is done
ERROR resolution
SQL> @irctxido1.sql HR APPLSYS APPS CTXSYS
PL/SQL procedure successfully completed.
Commit complete.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
applprod@vezora1:/u01/MIG/apps/prodappl/per/11.5.0/patch/115/sql
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @irctxipt HR APPLSYS APPS CTXSYS
PL/SQL procedure successfully completed.
Commit complete.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
applprod@vezora1:/u01/MIG/apps/prodappl/per/11.5.0/patch/115/sql
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @peredind.sql HR APPLSYS APPS CTXSYS
PL/SQL procedure successfully completed.
Commit complete.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
applprod@vezora1:/u01/MIG/apps/prodappl/per/11.5.0/patch/115/sql
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @ibcinctx.sql
PL/SQL procedure successfully completed.
Commit complete.
SQL*Plus: Release 8.0.6.0.0 - Production on Mon Feb 18 19:26:35 2008
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @icxintm2.sql icx icx CTXSYS
DECLARE
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10502: index ICX_QUES_CTX does not exist
DRG-13201: KOREAN_LEXER is desupported
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at line 31
HACK THE USER SEE SCRIPTS
applprod@vezora1:/u01/MIG/apps/prodappl/icx/11.5.0/patch/115/sql
SQL*Plus: Release 8.0.6.0.0 - Production on Mon Feb 18 19:29:25 2008
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @jtfiimt.sql jtf jtf
ERROR:
ORA-01017: invalid username/password; logon denied
HACK THE USER SEE SCRIPTS
applprod@vezora1:/u01/MIG/apps/prodappl/jtf/11.5.0/patch/115/sql
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @amviimt.sql amv amv
PL/SQL procedure successfully completed.
Grant succeeded.
Grant succeeded.
Grant succeeded.
DECLARE
*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-10701: preference AMV.KOREAN_LEXER already exists
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_DDL", line 26
ORA-06512: at line 76
CHECK ISSUE
applprod@vezora1:/u01/MIG/apps/prodappl/amv/11.5.0/patch/115/sql
POST MIGRATION
1) APPLSPE
OWNER SEGMENT_NAME SEGMENT_TYPE
SYS 17.22 SPACE HEADER
SQL> alter tablespace APPLSPE offline;
Tablespace altered.
SQL> drop tablespace APPLSPE including contents and datafiles cascade constraints;
Tablespace dropped.
2) AMVD
select table_name, column_name, tablespace_name, segment_name from dba_lobs
where tablespace_name = 'CSD';
alter table cs.DR$CS_INCIDENTS_ALL_TL_N1$I move lob(token_info) store as (tablespace APPS_TS_TX_DATA);
alter table cs.DR$CS_INCIDENTS_ALL_TL_N1$R move lob(data) store as (tablespace APPS_TS_TX_DATA);
alter table cs.DR$SUMMARY_CTX_INDEX$I move lob(token_info) store as (tablespace APPS_TS_TX_DATA);
alter table cs.DR$SUMMARY_CTX_INDEX$R move lob(data) store as (tablespace APPS_TS_TX_DATA);
select OWNER, INDEX_NAME, INDEX_TYPE, TABLE_NAME,TABLESPACE_NAME
from DBA_INDEXES
where INDEX_TYPE LIKE 'IOT%'
and TABLESPACE_NAME = 'CSD';
OWNER INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME
CS SYS_IOT_TOP_2516234 IOT - TOP DR$CS_INCIDENTS_ALL_TL_N1$K CSD
CS SYS_IOT_TOP_2516196 IOT - TOP DR$SUMMARY_CTX_INDEX$K CSD
CS SYS_IOT_TOP_2516249 IOT - TOP DR$CS_INCIDENTS_ALL_TL_N1$N CSD
CS SYS_IOT_TOP_2516231 IOT - TOP DR$SUMMARY_CTX_INDEX$N CSD
3) CTXD
select table_name, column_name, tablespace_name, segment_name from dba_lobs
where tablespace_name = 'CTXD';
alter table ctxsys.DR$PART_STATS_PRE10I move lob(statistics) store as (tablespace APPS_TS_TX_DATA);
alter table ctxsys.DR$STATS_PRE10I move lob(statistics) store as (tablespace APPS_TS_TX_DATA);
alter table ctxsys.DR$NVTAB move lob(val) store as (tablespace APPS_TS_TX_DATA); ISSUE !!
4) APPLSYSX
select table_name, column_name, tablespace_name, segment_name from dba_lobs
where tablespace_name = 'APPLSYSX';
no rows selected
select OWNER, INDEX_NAME, INDEX_TYPE, TABLE_NAME,TABLESPACE_NAME
from DBA_INDEXES
where INDEX_TYPE LIKE 'IOT%'
and TABLESPACE_NAME = ' APPLSYSX';
no rows selected
---------------------------------------------
alter table AQ$_WF_CONTROL_D move lob(data) store as (tablespace APPS_TS_TX_DATA)
*
ERROR at line 1:
ORA-08108: may not build or rebuild this type of index online
Procedures to Remove Orphan AQ Objects which Fail to Drop via DBMS_AQADM Note:236898.1
DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'APPLSYS.AQ$_WF_CONTROL_D');
OATM migration leaves WF_CONTROL objects in the old tablespace Note:418238.1
Solution
1.First stop WF related services like WF Mailer Agent, WF listener, etc.
2.Then run following SQL :
sqlplus apps/apps @$FND_TOP/patch/115/sql/wfctqrec.sql apps/apps
(This script should recreate the WF_CONTROL queue) PROBLEM
Migration of 02-March 2008
1) APPLSYSX
select OWNER, INDEX_NAME, INDEX_TYPE, TABLE_NAME,TABLESPACE_NAME from DBA_INDEXES where INDEX_TYPE LIKE 'IOT%' and TABLESPACE_NAME = 'APPLSYSX';
OWNER INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME
APPLSYS SYS_IOT_TOP_2516161 IOT - TOP DR$FND_LOBS_CTX$N APPLSYSX
APPLSYS SYS_IOT_TOP_2516156 IOT - TOP DR$FND_LOBS_CTX$K APPLSYSX
Alter table APPLSYS.DR$FND_LOBS_CTX$N move tablespace APPS_TS_QUEUES;
Alter table APPLSYS. DR$FND_LOBS_CTX$K move tablespace APPS_TS_QUEUES;
2) APPLSYSD
SQL> select segment_name, segment_type from dba_segments where tablespace_name ='APPLSYSD';
SEGMENT_NAME SEGMENT_TYPE
AD_FRAGMENT_REPORT TABLE
FND_LOBS TABLE
SYS_IL0000076155C00004$$ LOBINDEX
SYS_LOB0000076155C00004$$ LOBSEGMENT
18.51829 SPACE HEADER
select OWNER, TABLE_NAME, SEGMENT_NAME from dba_lobs where SEGMENT_NAME like 'AD_FRAGMENT_REPORT';
select OWNER, TABLE_NAME, SEGMENT_NAME from dba_lobs where SEGMENT_NAME like 'SYS_IL0000076155C00004$$';
select OWNER, TABLE_NAME, SEGMENT_NAME from dba_lobs where SEGMENT_NAME like 'SYS_LOB0000076155C00004$$';
OWNER TABLE_NAME SEGMENT_NAME
APPLSYS FND_LOBS SYS_LOB0000076155C00004$$
select OWNER, TABLESPACE_NAME from dba_tables where TABLE_NAME like 'FND_LOBS';
OWNER TABLESPACE_NAME
----------------------------------------------------------- ------------------------------
APPLSYS APPS_TS_TX_DATA
select OWNER, TABLESPACE_NAME from dba_tables where TABLE_NAME like 'AD_FRAGMENT_REPORT';
OWNER TABLESPACE_NAME
--------------------------------------------------------------------- ------------------------------
SYSTEM APPLSYSD
APPS APPS_TS_TX_DATA
alter table system.AD_FRAGMENT_REPORT move tablespace APPS_TS_TX_DATA;
Table altered.
3) APPLSYSX
select segment_name, segment_type from dba_segments where tablespace_name ='APPLSYSX';
SEGMENT_NAME SEGMENT_TYPE
FND_LOBS_U1 INDEX
19.38358 SPACE HEADER
alter index APPLSYS.FND_LOBS_U1 rebuild tablespace APPS_TS_TX_IDX;
alter tablespace APPLSYSX offline;
drop tablespace APPLSYSX including contents and datafiles cascade constraints;
4) SCSX
select segment_name, segment_type from dba_segments where tablespace_name ='SCSX';
SEGMENT_NAME SEGMENT_TYPE
384.14 SPACE HEADER
alter tablespace SCSX offline;
drop tablespace SCSX including contents and datafiles cascade constraints;
5) APPLSCS
select segment_name, segment_type from dba_segments where tablespace_name ='APPLSCS';
SEGMENT_NAME SEGMENT_TYPE
16.72 SPACE HEADER
alter tablespace APPLSCS offline;
drop tablespace APPLSCS including contents and datafiles cascade constraints;
6) CTXD
select segment_name, segment_type from dba_segments where tablespace_name ='CTXD';
SEGMENT_NAME SEGMENT_TYPE
37.1161 SPACE HEADER
SQL> alter tablespace CTXD offline;
Tablespace altered.
SQL> drop tablespace CTXD including contents and datafiles cascade constraints;
MIGRATION 09/03/2008
APPLSYSD
select segment_name, segment_type from dba_segments where tablespace_name ='APPLSYSD';
SEGMENT_NAME SEGMENT_TYPE
AD_FRAGMENT_REPORT TABLE
FND_LOB TABLE
DR$FND_LOBS_CTX$ TABLE
DR$FND_LOBS_CTX$R TABLE
SYS_IL0000076155C00004$$ LOBINDEX
SYS_IL0002516153C00006$$ LOBINDEX
SYS_IL0002516158C00002$$ LOBINDEX
SYS_LOB0000076155C00004$$ LOBSEGMENT
SYS_LOB0002516158C00002$$ LOBSEGMENT
SYS_LOB0002516153C00006$$ LOBSEGMENT
18.51829 SPACE HEADER
11 rows selected.
select OWNER, TABLE_NAME, SEGMENT_NAME from dba_lobs where SEGMENT_NAME like 'SYS_LOB0000076155C00004$$';
OWNER TABLE_NAME SEGMENT_NAME
APPLSYS FND_LOBS SYS_LOB0000076155C00004$$
select OWNER, TABLESPACE_NAME from dba_tables where TABLE_NAME like 'FND_LOBS';
OWNER TABLESPACE_NAME
APPLSYS APPLSYSD
SQL> select OWNER, TABLESPACE_NAME from dba_tables where TABLE_NAME like 'AD_FRAGMENT_REPORT';
OWNER TABLESPACE_NAME
SYSTEM APPLSYSD
APPS APPS_TS_TX_DATA
Alter table AD_FRAGMENT_REPORT move tablespace apps_ts_tx_data;
Table altered.
Alter table applsys.FND_LOBS move tablespace apps_ts_tx_data;
Table altered.
Alter table applsys.DR$FND_LOBS_CTX$R move tablespace apps_ts_tx_data;
Table altered.
Alter table applsys.DR$FND_LOBS_CTX$I move tablespace apps_ts_tx_data;
Table altered.
Alter table sys.AD_FRAGMENT_REPORT move tablespace apps_ts_tx_data;
APPLSYSX
select segment_name, segment_type from dba_segments where tablespace_name ='APPLSYSX';
SEGMENT_NAME SEGMENT_TYPE
FND_LOBS_U1 INDEX
SYS_IOT_TOP_2516161 INDEX
SYS_IOT_TOP_2516156 INDEX
DR$FND_LOBS_CTX$X INDEX
19.38358 SPACE HEADER
alter index APPLSYS.FND_LOBS_U1 rebuild tablespace APPS_TS_TX_IDX;
Index altered.
alter index applsys.DR$FND_LOBS_CTX$X rebuild tablespace APPS_TS_TX_IDX;
Index altered.
select OWNER, INDEX_NAME, INDEX_TYPE, TABLE_NAME,TABLESPACE_NAME from DBA_INDEXES where INDEX_TYPE LIKE 'IOT%' and TABLESPACE_NAME = 'APPLSYSX';
OWNER INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME
APPLSYS SYS_IOT_TOP_2516161 IOT - TOP DR$FND_LOBS_CTX$N APPLSYSX
APPLSYS SYS_IOT_TOP_2516156 IOT - TOP DR$FND_LOBS_CTX$K APPLSYSX
Alter table APPLSYS.DR$FND_LOBS_CTX$N move tablespace APPS_TS_TX_IDX;
Alter table APPLSYS. DR$FND_LOBS_CTX$K move tablespace APPS_TS_TX_IDX;
select segment_name, segment_type from dba_segments where tablespace_name ='APPLSYSX';
SEGMENT_NAME SEGMENT_TYPE
19.38358 SPACE HEADER
SQL> alter tablespace APPLSYSX offline;
Tablespace altered.
SQL> drop tablespace APPLSYSX including contents and datafiles cascade constraints;
Tablespace dropped.
Applies to: Oracle Application Object Library - Versio 11.5.10.2
Information in this document applies to any platform.
Goal
1- Review OATM Model for Oracle Applications 11i
2- Install OATM migration utility
3- Run OATM migration utility
4- Parameters needed while starting OATM utility
5- OATM Menu Structure
Solution
1- Review OATM Model for Oracle Applications 11i
The Oracle Applications Tablespace Model (OATM) introduces a new, consolidated tablespace model in Oracle Applications uses 12 locally managed tablespaces for all products including the temporary tablespace, system tablespace, and undo segments. In this revised Oracle Applications Tablespace Model, each Oracle Applications database object is mapped to a tablespace based on its Input/Output characteristics. This new model allows for easier maintenance, reduces space usage, and allows for potential run-time performance gains.
The Oracle Applications Tablespace Migration Utility is an interactive menu-based PERL program including a set of sizing estimate reports. It enables customers to convert their Oracle Applications databases to the new tablespace model (OATM) for either all schemas at once or partially for select schema(s) at a time, depending on acceptable down time and available disk space.
You can find more information about OATM migration utility in the following note:
Note 248857.1 - OATM Release 11i - Tablespace Migration Utility
Also please refer to Oracle Documentation:
http://download-uk.oracle.com/docs/cd/B25516_14/current/acrobat/115sacg.zip
2- Install OATM migration utility
You can install the OATM migration utility by downloading & applying the following patch through Metalink:Patch 3942506 - AOL/FND: OATM MIGRATION UTILITY ROLLUP C
(Note: Above patch is already included in 11.5.10 and higher, so if you have Oracle Applications 11.5.10 or higher you do not need to install it)
/u01/MIG/apps/prodappl/admin/mig/log
3- Run OATM migration utility
After installing the utility through above patch, please use the following command in order to start running the utility:
perl $FND_TOP/bin/fndtsmig.pl
4- Parameters needed while starting OATM utility
After running the PERL script in order to start the OATM utility, the following parameters screen will appear:
+=======================================================================+
Copyright (c) 2003 Oracle Corporation Redwood Shores, California, USA
All rights reserved.
+=======================================================================+
Welcome to the Oracle Applications Tablespace Migration Utility
+=======================================================================+
Warning: If DB was upgraded from 8.1.7 to a version prior to 9.2.0.6
please run $FND_TOP/patch/115/sql/fndupglb.sql before the migration.
+=======================================================================+
Enter OATM configuration file or press enter to continue:
Enter APPL_TOP [/u01/MIG/apps/prodappl]:
Enter FND_TOP [/u01/MIG/apps/prodappl/fnd/11.5.0]:
Enter the Database Connect String [MIG]:
Enter the password for your 'SYSTEM' ORACLE schema:
Successfully connected to database MIG using SYSTEM user.
Enter the ORACLE APPS Schema name[APPS]: apps
Enter the password for APPS:
Successfully connected to database MIG using APPS user.
Enter the ORACLE Application Object Library Schema name[APPLSYS]: applsys
================================================================
CONNECT_STRING =MIG
FND_TOP =/u01/MIG/apps/prodappl/fnd/11.5.0
APPLSYS_SCHEMA =APPLSYS
APPS_SCHEMA =APPS
APPL_TOP =/u01/MIG/apps/prodappl
================================================================
Please review and press 'Y' to accept or 'N' to re-enter[Y]: y
Performing database check...
Database check completed.
Checking required components...
Required components check completed.
5- OATM Menu Structure
After providing all parameters as in the previous step, you will get the Main Menu for Oracle Applications Tablespace Migration Utility which consists of 8 options as following:
Important Note: Some Menu Options are available only on 11.5.10.2 and later such as: Migrate CTXSYS Schema, Generate script to drop empty tablespaces.
Oracle Applications Tablespace Migration Utility
Main Menu
1. Migration Sizing Reports
2. Create New Tablespaces
3. Generate Migration Commands
4. Execute Migration Commands
5. Run Migration Status Reports
6. Run Post Migration Steps
7. Run Customization Steps
8. Run Migration in Batch Mode
[Q]uit [N]ext
Please enter your option -
Here are the details for each menu option:
5.1. Migration Sizing Reports
Migration Sizing Reports
These reports are provided to help you gauge the space requirements for the new Tablespaces. Additionally, they will also help you determine the type of migration approach that best suits your needs
1. Generate a Report with the list of all the Oracle Application product schemas that can be migrated
2. Calculate total space required by each new tablespace to migrate all Oracle Application product schemas (relevant for a complete migration)
3. Calculate total space required by each new tablespace to migrate each Oracle Application product schema (relevant for a schema-by-schema migration)
4. Calculate total space required by each Oracle Applications schema, with details for each object
5. Display Sizing Exception report
[Q]uit [B]ack [N]ext
Please enter your option -
5.2. Create New Tablespaces
Create New Tablespaces
1. Generate new tablespace creation script
2. Create new tablespaces
[Q]uit [B]ack [N]ext
Please enter your option -
5.3. Generate Migration Commands
Generate Migration Commands
Generation of Migration commands including disable/enable
commands for triggers, constraints, policies, stop/start for queues.
1. Invalid Indexes Report. Please correct/drop these before generating migration commands
2. Generate migration commands for all schemas
3. Generate migration commands for a list of schemas
[Q]uit [B]ack [N]ext
Please enter your option -
5.4. Execute Migration Commands
Execute Migration Commands
Execution of Migration commands including disable commands for triggers constraints, stop/start for queues.
PLEASE TAKE A COMPLETE BACKUP OF THE DATABASE BEFORE MIGRATION
1. Migrate all Schemas
2. Migrate a list of Schemas
3. Migrate CTXSYS Schema
Note: Migrate CTXSYS schema when no other migration process is in progress.
[Q]uit [B]ack [N]ext
Please enter your option -
5.5. Run Migration Status Reports
Run Migration Status Reports
1. Run migration progress report
2. Run migration error report
[Q]uit [B]ack [N]ext
Please enter your option -
5.6. Run Post Migration Steps
Run Post Migration Steps
1. Run audit report
2. Enable constraints/triggers/policies & start queues
3. Resize old tablespaces
4. Generate script to drop empty tablespaces
Note: Please complete refresh all the Materialized Views
in Oracle Applications before using the system.
[Q]uit [B]ack
Please enter your option -
5.7. Customization
Customization
1. Register new tablespace - tablespace type
2. Change name of the existing tablespace
3. Register object classification
4. Change object classification
[Q]uit [B]ack
Please enter your option -
5.8. Batch Mode Migration
Batch Mode Migration
1. Run Migration in Batch Mode
2. Run Migration Monitor
[Q]uit [B]ack
Please enter your option -
OATM migration
applprod@vezora1:~ $ cd /u01/MIG/apps/prodappl/fnd/11.5.0/bin
applprod@vezora1:/u01/MIG/apps/prodappl/fnd/11.5.0/bin $ perl fndtsmig.pl
+=======================================================================+
Copyright (c) 2003 Oracle Corporation Redwood Shores, California, USA
All rights reserved.
+=======================================================================+
Welcome to the Oracle Applications Tablespace Migration Utility
+=======================================================================+
Warning: If DB was upgraded from 8.1.7 to a version prior to 9.2.0.6
please run $FND_TOP/patch/115/sql/fndupglb.sql before the migration.
+=======================================================================+
Enter OATM configuration file or press enter to continue:
Enter APPL_TOP[/u01/MIG/apps/prodappl]:
Enter FND_TOP[/u01/MIG/apps/prodappl/fnd/11.5.0]:
Enter the Database Connect String[MIG]:
Enter the password for your 'SYSTEM' ORACLE schema:
Successfully connected to database MIG using SYSTEM user.
Please create log directory /u01/MIG/apps/prodappl/admin/mig/log to continue:
Enter the ORACLE APPS Schema name[APPS]: ma6utoza
Enter the ORACLE APPS Schema name[APPS]:
Enter the password for APPS:
Successfully connected to database MIG using APPS user.
Enter the ORACLE Application Object Library Schema name[APPLSYS]:
================================================================
CONNECT_STRING =MIG
FND_TOP =/u01/MIG/apps/prodappl/fnd/11.5.0
APPLSYS_SCHEMA =APPLSYS
APPS_SCHEMA =APPS
APPL_TOP =/u01/MIG/apps/prodappl
================================================================
Please review and press 'Y' to accept or 'N' to re-enter[Y]: Y
Performing database check...
Database check completed.
Checking required components...
Required components check completed.
Oracle Applications Tablespace Migration Utility
Main Menu
1. Migration Sizing Reports
2. Create New Tablespaces
3. Generate Migration Commands
4. Execute Migration Commands
5. Run Migration Status Reports
6. Run Post Migration Steps
7. Run Customization Steps
8. Run Migration in Batch Mode
[Q]uit [N]ext
Please enter your option - 1
Migration Sizing Reports
These reports are provided to help you gauge the space requirements for the new Tablespaces. Additionally, they will also help you determine the type of migration approach that best suits your needs
1. Generate a Report with the list of all the Oracle Application product schemas that can be migrated
2. Calculate total space required by each new tablespace to migrate all Oracle Application product schemas (relevant for a complete migration)
3. Calculate total space required by each new tablespace to migrate each Oracle Application product schema (relevant for a schema-by-schema migration)
4. Calculate total space required by each Oracle Applications schema, with details for each object
5. Display Sizing Exception report
[Q]uit [B]ack [N]ext
Please enter your option - 1
Migration Progress Report
Report Date : February 14, 2008 PAGE: 1
Total generated % commands
Schema commands successfully executed
--------------- --------------- -----------------------------------------
ABM 0 0%
AHL 0 0%
AHM 0 0%
AK 0 0%
ALR 0 0%
AMF 0 0%
………………………
…………………………
………………….
XNB 0 0%
XNC 0 0%
XNI 0 0%
XNM 0 0%
XNP 0 0%
XNS 0 0%
XTR 0 0%
ZFA 0 0%
ZPB 0 0%
ZSA 0 0%
ZX 0 0%
Report created /u01/MIG/apps/prodappl/admin/mig/log/fndtrep0.txt
Press Return key to continue...
Migration Sizing Reports
These reports are provided to help you gauge the space requirements for the new Tablespaces. Additionally, they will also help you determine the type of migration approach that best suits your needs
1. Generate a Report with the list of all the Oracle Application product schemas that can be migrated
2. Calculate total space required by each new tablespace to migrate all Oracle Application product schemas (relevant for a complete migration)
3. Calculate total space required by each new tablespace to migrate each Oracle Application product schema (relevant for a schema-by-schema migration)
4. Calculate total space required by each Oracle Applications schema, with details for each object
5. Display Sizing Exception report
[Q]uit [B]ack [N]ext
Please enter your option - 2
Sizing Program has never been run.
Enter Uniform Extent Size for the Tablespaces in KBytes[1024]:
Executing the Sizing program for all schemas. This may take upto 20min. Please wait...
Sizing program executed successfully.
PAGE: 1
Summary Report-Amount of space by tablespace
Report Date : February 14, 2008
Last Sizing Run Date : February 14, 2008
Tablespace Extent Management - Local
Allocation type -Uniform Extents<1024kb>
Current space
Recommended taken up by
Estimated space space in KB existing
Tablespace Name required in KB (20% Buffer) objects in KB
------------------------------ ---------------- --------------- ---------------------------------------
APPS_TS_ARCHIVE 6,228,992 7,474,791 2,061,240
APPS_TS_INTERFACE 15,163,392 18,196,071 11,089,784
APPS_TS_MEDIA 3,638,272 4,365,927 3,570,144
APPS_TS_NOLOGGING 662,528 795,034 342,200
APPS_TS_QUEUES 3,369,984 4,043,981 2,825,328
APPS_TS_SEED 7,327,744 8,793,293 2,129,744
APPS_TS_SUMMARY 6,267,904 7,521,485 1,135,208
APPS_TS_TX_DATA 61,418,496 73,702,196 50,858,192
APPS_TS_TX_IDX 66,878,464 80,254,157 45,371,752
------------------------------------- --------------- ------------------------------------------------
Total (in KB): 170,955,776 205,146,935 119,383,592
Report created /u01/MIG/apps/prodappl/admin/mig/log/fndtrep1.txt
Report created /u01/MIG/apps/prodappl/admin/mig/log/fndtrep1.txt
Press Return key to continue...
Migration Sizing Reports
These reports are provided to help you gauge the space requirements for the new Tablespaces. Additionally, they will also help you determine the type of migration approach that best suits your needs
1. Generate a Report with the list of all the Oracle
Application product schemas that can be migrated
2. Calculate total space required by each new tablespace to migrate all Oracle Application product schemas (relevant) for a complete migration)
3. Calculate total space required by each new tablespace to migrate each Oracle Application product schema (relevant for a schema-by-schema migration)
4. Calculate total space required by each Oracle Applications schema, with details for each object
5. Display Sizing Exception report
[Q]uit [B]ack [N]ext
Please enter your option - 2
Sizing Program was last run on 14-FEB-08
Do you want to run the Sizing program again before running the report[N]: Y
PAGE: 1
Summary Report-Amount of Space by Schema by Object
Report Date : February 15, 2008
Last Sizing Run Date : February 15, 2008
Tablespace Extent Management - Local
Allocation type -Uniform Extents<1024kb>
Object Estimated Space Sizing
Schema Name Required (in KB) Errors
-------------------- ------------------------------ ---------------- ------------
ABM ABM_VIEW_SEC_OBJS_U2 1,024 No Error
ABM_CALC_TMPLTS_TL_PK 1,024 No Error
ABM_CALC_TMPLT_VARS_TL_PK 1,024 No Error
ABM_CURRS_TL_PK 1,024 No Error
ABM_IMP_ENT_TYPES_TL_PK 1,024 No Error
ABM_IMP_TAGS_TL_PK 1,024 No Error
ABM_IMP_TAG_SETS_TL_PK 1,024 No Error
ABM_REP_TMPLTS_TL_PK 1,024 No Error
ABM_BOR_WIZ_CHILD_RES_TREE_PK 1,024 No Error
ABM_API_TEMPLATES_PK 1,024 No Error
ABM_API_TEMPLATE_ATTRIBUTES_PK 1,024 No Error
ABM_NAV_STEPS_PK 1,024 No Error
ABM_NAV_STEPS_U1 1,024 No Error
ABM_IMP_TAGS 1,024 No Error
ABM_IMP_TAG_ENTS 1,024 No Error
ABM_IMP_TAG_SETS 1,024 No Error
ABM_IMP_UOM_CV_RULES 1,024 No Error
ABM_LAUNCHPAD_METADATA 1,024 No Error
ABM_MAP_BASES 1,024 No Error
ABM_MAP_EXPTN_REP 1,024 No Error
ABM_MAP_FORM_LOG 1,024 No Error
ABM_MAP_FORM_MASTERS 1,024 No Error
ABM_MAP_RULE_SETS 1,024 No Error
ABM_MAT_UNIT_COST 1,024 No Error
******************** ----------------
Total (in KB) : 424,960
PAGE: 2
Summary Report-Amount of Space by Schema by Object
Report Date: February 15, 2008
Last Sizing Run Date: February 15, 2008
Tablespace Extent Management - Local
Allocation type -Uniform Extents<1024kb>
Migration Sizing Reports
These reports are provided to help you gauge the space requirements for the new Tablespaces. Additionally, they will also help you determine the type of migration approach that best suits your needs
1. Generate a Report with the list of all the Oracle Application product schemas that can be migrated
2. Calculate total space required by each new tablespace to migrate all Oracle Application product schemas (relevant for a complete migration)
3. Calculate total space required by each new tablespace to migrate each Oracle Application product schema (relevant for a schema-by-schema migration)
4. Calculate total space required by each Oracle Applications schema, with details for each object
5. Display Sizing Exception report
[Q]uit [B]ack [N]ext
Please enter your option - 5
Enter the Schema name[%]:
Running Exception Report ..............
There are no objects in errors
Report created /u01/MIG/apps/prodappl/admin/mig/log/fndtrep5.txt
Press Return key to continue...
Oracle Applications Tablespace Migration Utility
Main Menu
1. Migration Sizing Reports
2. Create New Tablespaces
3. Generate Migration Commands
4. Execute Migration Commands
5. Run Migration Status Reports
6. Run Post Migration Steps
7. Run Customization Steps
8. Run Migration in Batch Mode
[Q]uit [N]ext
Please enter your option - 2
Create New Tablespaces
1. Generate new tablespace creation script
2. Create new tablespaces
[Q]uit [B]ack [N]ext
Please enter your option - 1
*****************************************************************
The utility will append a sequence number to the tablespace name
and a .dbf extension to generate the datafile names.
Datafile size should not be greater than OS file size limit.
Please edit the generated script to change the file name/size
*****************************************************************
Enter the Extent Allocation type A(utoallocate) or U(niform Extent Size)[U]: U
Enter Uniform Extent Size for the Tablespaces in KBytes[1024]:
Enter the absolute path for the datafiles directory: /u02/MIG/dbf
Enter the Number of Datafiles for APPS_TS_TX_DATA tablespace[1]: 10
Enter the Datafile Size for APPS_TS_TX_DATA tablespace (MB): 2000
Enter the Number of Datafiles for APPS_TS_TX_IDX tablespace[1]: 10
Enter the Datafile Size for APPS_TS_TX_IDX tablespace (MB): 2000
Enter the Number of Datafiles for APPS_TS_SEED tablespace[1]: 2
Enter the Datafile Size for APPS_TS_SEED tablespace (MB): 2000
Enter the Number of Datafiles for APPS_TS_INTERFACE tablespace[1]: 5
Enter the Datafile Size for APPS_TS_INTERFACE tablespace (MB): 2000
Enter the Number of Datafiles for APPS_TS_SUMMARY tablespace[1]: 5
Enter the Datafile Size for APPS_TS_SUMMARY tablespace (MB): 2000
Enter the Number of Datafiles for APPS_TS_NOLOGGING tablespace[1]: 2
Enter the Datafile Size for APPS_TS_NOLOGGING tablespace (MB): 1000
Enter the Number of Datafiles for APPS_TS_ARCHIVE tablespace[1]: 7
Enter the Datafile Size for APPS_TS_ARCHIVE tablespace (MB): 1000
Enter the Number of Datafiles for APPS_TS_QUEUES tablespace[1]: 5
Enter the Datafile Size for APPS_TS_QUEUES tablespace (MB): 1000
Enter the Number of Datafiles for APPS_TS_MEDIA tablespace[1]: 5
Enter the Datafile Size for APPS_TS_MEDIA tablespace (MB): 1000
Tablespace creation script /u01/MIG/apps/prodappl/admin/mig/log/crtts.sql created
/u01/MIG/apps/prodappl/admin/mig/log ***scripts is in this directory **
Press Return key to continue...
Create New Tablespaces
1. Generate new tablespace creation script
2. Create new tablespaces
[Q]uit [B]ack [N]ext
Please enter your option - 2
Creating Tablespaces. Please wait...
Connected.
PL/SQL procedure successfully completed.
Tablespace created.
Tablespace created.
Tablespace created.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespaces creation script executed. Please check the log /u01/MIG/apps/prodappl/admin/mig/log/crtts20080216215009.log for errors.
Press Return key to continue...
Create New Tablespaces
1. Generate new tablespace creation script
2. Create new tablespaces
[Q]uit [B]ack [N]ext
Please enter your option - B
Oracle Applications Tablespace Migration Utility
Main Menu
1. Migration Sizing Reports
2. Create New Tablespaces
3. Generate Migration Commands
4. Execute Migration Commands
5. Run Migration Status Reports
6. Run Post Migration Steps
7. Run Customization Steps
8. Run Migration in Batch Mode
[Q]uit [N]ext
Please enter your option - 3
Generate Migration Commands
Generation of Migration commands including disable/enable
commands for triggers, constraints, policies, stop/start for queues.
1. Invalid Indexes Report. Please correct/drop these before
generating migration commands
2. Generate migration commands for all schemas
3. Generate migration commands for a list of schemas
[Q]uit [B]ack [N]ext
Please enter your option - 1
Report created /u01/MIG/apps/prodappl/admin/mig/log/fndinvld.txt
Press Return key to continue...
Press Return key to continue...
Generate Migration Commands
Generation of Migration commands including disable/enable
commands for triggers, constraints, policies, stop/start for queues.
1. Invalid Indexes Report. Please correct/drop these before generating migration commands
2. Generate migration commands for all schemas
3. Generate migration commands for a list of schemas
[Q]uit [B]ack [N]ext
Please enter your option - 2
Generating Migration commands for all schemas. This may take upto 30min. Please wait...
Command generation successful. Please check the log /u01/MIG/apps/prodappl/admin/mig/log/fndgmcmd20080216224419.log
Press Return key to continue...
Generate Migration Commands
Generation of Migration commands including disable/enable
commands for triggers, constraints, policies, stop/start for queues.
1. Invalid Indexes Report. Please correct/drop these before generating migration commands
2. Generate migration commands for all schemas
3. Generate migration commands for a list of schemas
[Q]uit [B]ack [N]ext
Please enter your option - 2
Generating Migration commands for all schemas. This may take upto 30min. Please wait...
Command generation successful. Please check the log /u01/MIG/apps/prodappl/admin/mig/log/fndgmcmd20080216224419.log
Press Return key to continue...
Generate Migration Commands
Generation of Migration commands including disable/enable
commands for triggers, constraints, policies, stop/start for queues.
1. Invalid Indexes Report. Please correct/drop these before
generating migration commands
2. Generate migration commands for all schemas
3. Generate migration commands for a list of schemas
[Q]uit [B]ack [N]ext
Please enter your option - B
Oracle Applications Tablespace Migration Utility
Main Menu
1. Migration Sizing Reports
2. Create New Tablespaces
3. Generate Migration Commands
4. Execute Migration Commands
5. Run Migration Status Reports
6. Run Post Migration Steps
7. Run Customization Steps
8. Run Migration in Batch Mode
[Q]uit [N]ext
Please enter your option - 4
Execute Migration Commands
Execution of Migration commands including disable
commands for triggers constraints, stop/start for queues.
PLEASE TAKE A COMPLETE BACKUP OF THE DATABASE BEFORE MIGRATION
1. Migrate all Schemas
2. Migrate a list of Schemas
3. Migrate CTXSYS Schema
Note: Migrate CTXSYS schema when no other migration process is in progress.
[Q]uit [B]ack [N]ext
Please enter your option - 1
Are you sure you want to migrate all schemas[N]: Y
Enter the maximum number of parallel processes for MIG[8]:
Starting the Migration process for all schemas. Please wait...
Migration processes for tables with LONG and LONG RAW columns started in the background. Please monitor the log file /u01/MIG/apps/prodappl/admin/mig/log/fndmlong20080216230605.log for errors and completion of this migration process.
Sequential migration process started in the background for MIG. Please monitor the log file /u01/MIG/apps/prodappl/admin/mig/log/fndemseq20080216230606.log for errors and completion of this migration process.
Parallel migration processes started in the background for MIG. Please monitor the log file /u01/MIG/apps/prodappl/admin/mig/log/fndemcmd20080216230622.log for errors and completion of this migration process.
OATM Utility processes are running in the background. sql*plus sessions and one JRE session are spawned in the background. You can monitor those sessions from the OS. Please do not kill any of those sessions as it will adversely affect migration process and result in errors. Monitor migration by running migration progress report.
Press Return key to return to the menu...
Press Return key to continue...
Run Post Migration Steps
1. Run audit report
2. Enable constraints/triggers/policies & start queues
3. Resize old tablespaces
4. Generate script to drop empty tablespaces
Note: Please complete refresh all the Materialized Views
in Oracle Applications before using the system.
[Q]uit [B]ack
Please enter your option - 2
Enter a comma separated list of Schema names[%]:
Enabling Constraints/triggers/policies and starting queues. Please wait...
All constraints/trigger/policies are enabled and queues started
Press Return key to continue...
Run Post Migration Steps
1. Run audit report
2. Enable constraints/triggers/policies & start queues
3. Resize old tablespaces
4. Generate script to drop empty tablespaces
Note: Please complete refresh all the Materialized Views
in Oracle Applications before using the system.
[Q]uit [B]ack
Please enter your option - 4
+=======================================================================+
Warning: Please ensure you do not have any residual data segments
in the tablespaces attempted to be dropped to avoid irrevocable
loss of data.
+=======================================================================+
Drop empty tablespace script /u01/MIG/apps/prodappl/admin/mig/log/fndtsdrp.sql has been created.
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @fndtsdrp.sql;
Tablespace dropped.
Tablespace dropped.
Tablespace dropped.
Tablespace dropped.
Tablespace dropped.
SQL> commit;
Commit complete.
OATM How to move objects left in the old tablespace after oatm migration?
Solution
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
Drop SWALLACE and SPOTLIGHT USERS
drop user spotlight cascade;
drop user swallace cascade;
Note:430604.1
CREATE TEXT INDEX DRG-11446 DROLDUS.DAT NOT INSTALLED
PATCH 4575748 OATM RE-SIZE OLD TABLESPACES DOES NOT RESIZE DATAFILES
IMPORTANT NOTE :
One-off patches are not fully certified by all Oracle products, and so there is some risk of unexpected side effects. If the fix in this patch is included in the latest released ATG Family Packs and/or ATG PF Consolidated Updates (CU), then it is highly recommended to upgrade to latest released ATG Family Pack or ATG PF Consolidated Update (CU) that is available in Recommended Patch List (Metalink Note 123456.1). It is also recommended to upgrade to latest released ATG Family Pack or ATG PF Consolidated Update (CU) before applying this patch.
This patch provides implementation that will correctly resize database file after migration is done
ERROR resolution
SQL> @irctxido1.sql HR APPLSYS APPS CTXSYS
PL/SQL procedure successfully completed.
Commit complete.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
applprod@vezora1:/u01/MIG/apps/prodappl/per/11.5.0/patch/115/sql
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @irctxipt HR APPLSYS APPS CTXSYS
PL/SQL procedure successfully completed.
Commit complete.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
applprod@vezora1:/u01/MIG/apps/prodappl/per/11.5.0/patch/115/sql
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @peredind.sql HR APPLSYS APPS CTXSYS
PL/SQL procedure successfully completed.
Commit complete.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
applprod@vezora1:/u01/MIG/apps/prodappl/per/11.5.0/patch/115/sql
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @ibcinctx.sql
PL/SQL procedure successfully completed.
Commit complete.
SQL*Plus: Release 8.0.6.0.0 - Production on Mon Feb 18 19:26:35 2008
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @icxintm2.sql icx icx CTXSYS
DECLARE
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10502: index ICX_QUES_CTX does not exist
DRG-13201: KOREAN_LEXER is desupported
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at line 31
HACK THE USER SEE SCRIPTS
applprod@vezora1:/u01/MIG/apps/prodappl/icx/11.5.0/patch/115/sql
SQL*Plus: Release 8.0.6.0.0 - Production on Mon Feb 18 19:29:25 2008
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @jtfiimt.sql jtf jtf
ERROR:
ORA-01017: invalid username/password; logon denied
HACK THE USER SEE SCRIPTS
applprod@vezora1:/u01/MIG/apps/prodappl/jtf/11.5.0/patch/115/sql
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @amviimt.sql amv amv
PL/SQL procedure successfully completed.
Grant succeeded.
Grant succeeded.
Grant succeeded.
DECLARE
*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-10701: preference AMV.KOREAN_LEXER already exists
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_DDL", line 26
ORA-06512: at line 76
CHECK ISSUE
applprod@vezora1:/u01/MIG/apps/prodappl/amv/11.5.0/patch/115/sql
POST MIGRATION
1) APPLSPE
OWNER SEGMENT_NAME SEGMENT_TYPE
SYS 17.22 SPACE HEADER
SQL> alter tablespace APPLSPE offline;
Tablespace altered.
SQL> drop tablespace APPLSPE including contents and datafiles cascade constraints;
Tablespace dropped.
2) AMVD
select table_name, column_name, tablespace_name, segment_name from dba_lobs
where tablespace_name = 'CSD';
alter table cs.DR$CS_INCIDENTS_ALL_TL_N1$I move lob(token_info) store as (tablespace APPS_TS_TX_DATA);
alter table cs.DR$CS_INCIDENTS_ALL_TL_N1$R move lob(data) store as (tablespace APPS_TS_TX_DATA);
alter table cs.DR$SUMMARY_CTX_INDEX$I move lob(token_info) store as (tablespace APPS_TS_TX_DATA);
alter table cs.DR$SUMMARY_CTX_INDEX$R move lob(data) store as (tablespace APPS_TS_TX_DATA);
select OWNER, INDEX_NAME, INDEX_TYPE, TABLE_NAME,TABLESPACE_NAME
from DBA_INDEXES
where INDEX_TYPE LIKE 'IOT%'
and TABLESPACE_NAME = 'CSD';
OWNER INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME
CS SYS_IOT_TOP_2516234 IOT - TOP DR$CS_INCIDENTS_ALL_TL_N1$K CSD
CS SYS_IOT_TOP_2516196 IOT - TOP DR$SUMMARY_CTX_INDEX$K CSD
CS SYS_IOT_TOP_2516249 IOT - TOP DR$CS_INCIDENTS_ALL_TL_N1$N CSD
CS SYS_IOT_TOP_2516231 IOT - TOP DR$SUMMARY_CTX_INDEX$N CSD
3) CTXD
select table_name, column_name, tablespace_name, segment_name from dba_lobs
where tablespace_name = 'CTXD';
alter table ctxsys.DR$PART_STATS_PRE10I move lob(statistics) store as (tablespace APPS_TS_TX_DATA);
alter table ctxsys.DR$STATS_PRE10I move lob(statistics) store as (tablespace APPS_TS_TX_DATA);
alter table ctxsys.DR$NVTAB move lob(val) store as (tablespace APPS_TS_TX_DATA); ISSUE !!
4) APPLSYSX
select table_name, column_name, tablespace_name, segment_name from dba_lobs
where tablespace_name = 'APPLSYSX';
no rows selected
select OWNER, INDEX_NAME, INDEX_TYPE, TABLE_NAME,TABLESPACE_NAME
from DBA_INDEXES
where INDEX_TYPE LIKE 'IOT%'
and TABLESPACE_NAME = ' APPLSYSX';
no rows selected
---------------------------------------------
alter table AQ$_WF_CONTROL_D move lob(data) store as (tablespace APPS_TS_TX_DATA)
*
ERROR at line 1:
ORA-08108: may not build or rebuild this type of index online
Procedures to Remove Orphan AQ Objects which Fail to Drop via DBMS_AQADM Note:236898.1
DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'APPLSYS.AQ$_WF_CONTROL_D');
OATM migration leaves WF_CONTROL objects in the old tablespace Note:418238.1
Solution
1.First stop WF related services like WF Mailer Agent, WF listener, etc.
2.Then run following SQL :
sqlplus apps/apps @$FND_TOP/patch/115/sql/wfctqrec.sql apps/apps
(This script should recreate the WF_CONTROL queue) PROBLEM
Migration of 02-March 2008
1) APPLSYSX
select OWNER, INDEX_NAME, INDEX_TYPE, TABLE_NAME,TABLESPACE_NAME from DBA_INDEXES where INDEX_TYPE LIKE 'IOT%' and TABLESPACE_NAME = 'APPLSYSX';
OWNER INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME
APPLSYS SYS_IOT_TOP_2516161 IOT - TOP DR$FND_LOBS_CTX$N APPLSYSX
APPLSYS SYS_IOT_TOP_2516156 IOT - TOP DR$FND_LOBS_CTX$K APPLSYSX
Alter table APPLSYS.DR$FND_LOBS_CTX$N move tablespace APPS_TS_QUEUES;
Alter table APPLSYS. DR$FND_LOBS_CTX$K move tablespace APPS_TS_QUEUES;
2) APPLSYSD
SQL> select segment_name, segment_type from dba_segments where tablespace_name ='APPLSYSD';
SEGMENT_NAME SEGMENT_TYPE
AD_FRAGMENT_REPORT TABLE
FND_LOBS TABLE
SYS_IL0000076155C00004$$ LOBINDEX
SYS_LOB0000076155C00004$$ LOBSEGMENT
18.51829 SPACE HEADER
select OWNER, TABLE_NAME, SEGMENT_NAME from dba_lobs where SEGMENT_NAME like 'AD_FRAGMENT_REPORT';
select OWNER, TABLE_NAME, SEGMENT_NAME from dba_lobs where SEGMENT_NAME like 'SYS_IL0000076155C00004$$';
select OWNER, TABLE_NAME, SEGMENT_NAME from dba_lobs where SEGMENT_NAME like 'SYS_LOB0000076155C00004$$';
OWNER TABLE_NAME SEGMENT_NAME
APPLSYS FND_LOBS SYS_LOB0000076155C00004$$
select OWNER, TABLESPACE_NAME from dba_tables where TABLE_NAME like 'FND_LOBS';
OWNER TABLESPACE_NAME
----------------------------------------------------------- ------------------------------
APPLSYS APPS_TS_TX_DATA
select OWNER, TABLESPACE_NAME from dba_tables where TABLE_NAME like 'AD_FRAGMENT_REPORT';
OWNER TABLESPACE_NAME
--------------------------------------------------------------------- ------------------------------
SYSTEM APPLSYSD
APPS APPS_TS_TX_DATA
alter table system.AD_FRAGMENT_REPORT move tablespace APPS_TS_TX_DATA;
Table altered.
3) APPLSYSX
select segment_name, segment_type from dba_segments where tablespace_name ='APPLSYSX';
SEGMENT_NAME SEGMENT_TYPE
FND_LOBS_U1 INDEX
19.38358 SPACE HEADER
alter index APPLSYS.FND_LOBS_U1 rebuild tablespace APPS_TS_TX_IDX;
alter tablespace APPLSYSX offline;
drop tablespace APPLSYSX including contents and datafiles cascade constraints;
4) SCSX
select segment_name, segment_type from dba_segments where tablespace_name ='SCSX';
SEGMENT_NAME SEGMENT_TYPE
384.14 SPACE HEADER
alter tablespace SCSX offline;
drop tablespace SCSX including contents and datafiles cascade constraints;
5) APPLSCS
select segment_name, segment_type from dba_segments where tablespace_name ='APPLSCS';
SEGMENT_NAME SEGMENT_TYPE
16.72 SPACE HEADER
alter tablespace APPLSCS offline;
drop tablespace APPLSCS including contents and datafiles cascade constraints;
6) CTXD
select segment_name, segment_type from dba_segments where tablespace_name ='CTXD';
SEGMENT_NAME SEGMENT_TYPE
37.1161 SPACE HEADER
SQL> alter tablespace CTXD offline;
Tablespace altered.
SQL> drop tablespace CTXD including contents and datafiles cascade constraints;
MIGRATION 09/03/2008
APPLSYSD
select segment_name, segment_type from dba_segments where tablespace_name ='APPLSYSD';
SEGMENT_NAME SEGMENT_TYPE
AD_FRAGMENT_REPORT TABLE
FND_LOB TABLE
DR$FND_LOBS_CTX$ TABLE
DR$FND_LOBS_CTX$R TABLE
SYS_IL0000076155C00004$$ LOBINDEX
SYS_IL0002516153C00006$$ LOBINDEX
SYS_IL0002516158C00002$$ LOBINDEX
SYS_LOB0000076155C00004$$ LOBSEGMENT
SYS_LOB0002516158C00002$$ LOBSEGMENT
SYS_LOB0002516153C00006$$ LOBSEGMENT
18.51829 SPACE HEADER
11 rows selected.
select OWNER, TABLE_NAME, SEGMENT_NAME from dba_lobs where SEGMENT_NAME like 'SYS_LOB0000076155C00004$$';
OWNER TABLE_NAME SEGMENT_NAME
APPLSYS FND_LOBS SYS_LOB0000076155C00004$$
select OWNER, TABLESPACE_NAME from dba_tables where TABLE_NAME like 'FND_LOBS';
OWNER TABLESPACE_NAME
APPLSYS APPLSYSD
SQL> select OWNER, TABLESPACE_NAME from dba_tables where TABLE_NAME like 'AD_FRAGMENT_REPORT';
OWNER TABLESPACE_NAME
SYSTEM APPLSYSD
APPS APPS_TS_TX_DATA
Alter table AD_FRAGMENT_REPORT move tablespace apps_ts_tx_data;
Table altered.
Alter table applsys.FND_LOBS move tablespace apps_ts_tx_data;
Table altered.
Alter table applsys.DR$FND_LOBS_CTX$R move tablespace apps_ts_tx_data;
Table altered.
Alter table applsys.DR$FND_LOBS_CTX$I move tablespace apps_ts_tx_data;
Table altered.
Alter table sys.AD_FRAGMENT_REPORT move tablespace apps_ts_tx_data;
APPLSYSX
select segment_name, segment_type from dba_segments where tablespace_name ='APPLSYSX';
SEGMENT_NAME SEGMENT_TYPE
FND_LOBS_U1 INDEX
SYS_IOT_TOP_2516161 INDEX
SYS_IOT_TOP_2516156 INDEX
DR$FND_LOBS_CTX$X INDEX
19.38358 SPACE HEADER
alter index APPLSYS.FND_LOBS_U1 rebuild tablespace APPS_TS_TX_IDX;
Index altered.
alter index applsys.DR$FND_LOBS_CTX$X rebuild tablespace APPS_TS_TX_IDX;
Index altered.
select OWNER, INDEX_NAME, INDEX_TYPE, TABLE_NAME,TABLESPACE_NAME from DBA_INDEXES where INDEX_TYPE LIKE 'IOT%' and TABLESPACE_NAME = 'APPLSYSX';
OWNER INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME
APPLSYS SYS_IOT_TOP_2516161 IOT - TOP DR$FND_LOBS_CTX$N APPLSYSX
APPLSYS SYS_IOT_TOP_2516156 IOT - TOP DR$FND_LOBS_CTX$K APPLSYSX
Alter table APPLSYS.DR$FND_LOBS_CTX$N move tablespace APPS_TS_TX_IDX;
Alter table APPLSYS. DR$FND_LOBS_CTX$K move tablespace APPS_TS_TX_IDX;
select segment_name, segment_type from dba_segments where tablespace_name ='APPLSYSX';
SEGMENT_NAME SEGMENT_TYPE
19.38358 SPACE HEADER
SQL> alter tablespace APPLSYSX offline;
Tablespace altered.
SQL> drop tablespace APPLSYSX including contents and datafiles cascade constraints;
Tablespace dropped.
