Wednesday, August 25, 2010

Procedure to reconfigure EM on 11gR2 running Oracle Application 11.5.10.2

This is a complete procedure I used to reconfigure the dbconsole on 11gR2. I migrated my source Oracle application database version 9.2.0.8 running on a 32 bit architecture listener to port 1523 single node to a dual node 64bit architecture for the 11gR2 database and the appsTier running on a HS22blade with 32 GB.

For clarity I used used the export import as its a cross platform migration. The destination 11gR2 I installed with the complete options Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options Release 11.2.0.1.0 - 64bit Production the source had Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.8.0 - Production 32 bit. Follow the
Metalink Doc: 946413.1 Using Oracle E-Business Suite with a Split Configuration Database Tier on Oracle 11gR2
Metalink Doc: 881505.1 Oracle E-Business Suite Release 11i with Oracle Database 11g Release 2 (11.2.0)
Metalink Doc: 230627.1 Export/Import Process for Oracle Applications Release 11i Database Instances Using Oracle9i Enterprise Edition

The detail of the patching and spliting is not included here see my next posting. The initial 11gR2 db Console was functional.

Shutdown the EM dbconsole from $ORACLE_HOME/bin using $emctl stop dbconsole
Shutdown the listener from $ORACLE_HOME/bin using $lsnrctl stop LISTENER (note my Oracle Applications listener name is PROD)
Shutdown the Oracle configuration manager from $ORACLE_HOME/ccr using $ emCCR stop

Drop the following user ,role and sysnonym

applprod@gva2ebsdbprod:/u01/PROD/apps/proddb/cfgtoollogs/emca/PROD $ sqlplus '/as sysdba';
SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 25 14:13:00 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> drop user sysman cascade;
User dropped.

SQL> drop role MGMT_USER;
Role dropped.

SQL> commit;
Commit complete.

SQL> drop user sysman cascade;
User dropped.

SQL> drop public synonym SETEMVIEWUSERCONTEXT;
Synonym dropped.

SQL> drop role MGMT_USER;
Role dropped.

SQL> drop PUBLIC SYNONYM MGMT_TARGET_BLACKOUTS;
Synonym dropped.

SQL> drop user MGMT_VIEW;
User dropped.


I had to register the new Listener PROD which is listening on port 1523 whereas the default IIg install listener was listener on port 1521

SQL> ALTER SYSTEM SET LOCAL_LISTENER="PROD" scope=spfile;
System altered.

First deconfigured the original EM dbconsole

applprod@gva2ebsdbprod:/u01/PROD/apps/proddb/11.2.0/bin $ emca -deconfig dbcontrol db

STARTED EMCA at Aug 25, 2010 12:07:32 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: PROD
Do you wish to continue? [yes(Y)/no(N)]: y
Aug 25, 2010 12:07:38 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/PROD/apps/proddb/cfgtoollogs/emca/PROD/emca_2010_08_25_12_07_32.log.
Aug 25, 2010 12:07:39 PM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration
WARNING: EM is not configured for this database. No EM-specific actions can be performed.
Enterprise Manager configuration completed successfully

Now use the recreate option

applprod@gva2ebsdbprod:/u01/PROD/apps/proddb/11.2.0/bin $ emca -config dbcontrol db -repos recreate

STARTED EMCA at Aug 25, 2010 12:55:25 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: PROD
Listener port number: 1523
Listener ORACLE_HOME [ /u01/PROD/apps/proddb/11.2.0 ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional): gshelton@pajago.com
Outgoing Mail (SMTP) server for notifications (optional): vezfe1.europe.pajagogroup.com
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /u01/PROD/apps/proddb/11.2.0

Local hostname ................ gva2ebsdbprod.pajagogroup.com
Listener ORACLE_HOME ................ /u01/PROD/apps/proddb/11.2.0
Listener port number ................ 1523
Database SID ................ PROD
Email address for notifications ............... gshelton@pajago.com
Outgoing Mail (SMTP) server for notifications ............... vezfe1.europe.pajagogroup.com

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Aug 25, 2010 12:55:53 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/PROD/apps/proddb/cfgtoollogs/emca/PROD/emca_2010_08_25_12_55_25.log.
Aug 25, 2010 12:55:54 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Aug 25, 2010 12:56:47 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Aug 25, 2010 12:56:47 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Aug 25, 2010 1:01:26 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Aug 25, 2010 1:01:29 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Aug 25, 2010 1:02:06 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Aug 25, 2010 1:02:08 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Aug 25, 2010 1:02:08 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Aug 25, 2010 1:02:28 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Aug 25, 2010 1:02:28 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Aug 25, 2010 1:03:08 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Aug 25, 2010 1:03:08 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Aug 25, 2010 1:03:29 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Aug 25, 2010 1:03:29 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://gva2ebsdbprod.pajagogroup.com:1158/em <<<<<<<<<<<
Aug 25, 2010 1:03:31 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /u01/PROD/apps/proddb/11.2.0/gva2ebsdbprod.pajagogroup.com_PROD/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Aug 25, 2010 1:03:31 PM
applprod@gva2ebsdbprod:/u01/PROD/apps/proddb/11.2.0/bin $



Cmments are welcome
sheltong@gmail.com
Senior Oracle Applications and OBIA DBA

Thursday, July 15, 2010

Configuring EM console and repository on Oracle 11gR2

There are 3 database users that must MUST be configured to successfully deploy EM dbconsole on 11gR2

if by accident you dropped DBSNMP user please use the script below to recreate the user and SNMPAgent role to access the v$ tables
cd $catsnmp.sql

You must drop these 2 users using the cascade option as they are recreated by the emca if you don't rop them the EM dbconsole creation will fail

SQL> drop user sysman cascade;
SQL> drop user MGMT_VIEW cascade;

You can now proceed with the EM configuration all the file are found under $ORACLE_HOME/bin


Create Database Console

To create the configuration files and repository for Database Console, run:
$ emca -config dbcontrol db -repos create

STARTED EMCA at Jul 14, 2010 11:28:03 AM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: BIDEV
Listener port number: 1521
Password for SYS user: bimaster
Password for DBSNMP user: bimanager
Password for SYSMAN user: bimanager
Email address for notifications ...............gshelton@amuru.com
Email address Outgoing Mail (SMTP) server for notifications ...............vezfe1.uganda.amurugroup.com

You have specified the following settings

Database ORACLE_HOME ................ /u01/BIDEV/apps/oracle/product/11.1.0.7

Database hostname ................ gva2bidevdb.amurugroup.com
Listener port number ................ 1521
Database SID ................ BIDEV
Email address for notifications ...............gshelton@amuru.com
Email address Outgoing Mail (SMTP) server for notifications ...............vezfe1.uganda.amurugroup.com

Do you wish to continue? [yes(Y)/no(N)]: Y
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/BIDEV/apps/oracle/product/11.1.0.7/cfgtoollogs/emca/BIDEV/emca_2010-07-15_09-59-39-PM.log.
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://gva2bidevdb.amurugroup.com:1158/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jul 14, 2010 11:28:03 AM


Drop Database Console

To drop (remove) the configuration files and repository for Database Console, run:
$ emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Jul 14, 2010 11:28:03 AM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: BIDEV
Listener port number: 1521
Password for SYS user: bimaster
Password for SYSMAN user: bimanager

Do you wish to continue? [yes(Y)/no(N)]: Y
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/BIDEV/apps/oracle/product/11.1.0.7/cfgtoollogs/emca/BIDEV/emca_2010-07-15_09-53-55-PM.log.
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jul 14, 2010 11:28:03 AM


Recreate Database Console

To recreate the configuration files and repository for Database Console, run:
$ emca -config dbcontrol db -repos recreate

STARTED EMCA at Jul 14, 2010 11:28:03 AM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: BIDEV
Database Control is already configured for the database BIDEV
You have chosen to configure Database Control for managing the database BIDEV
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: Y
Listener port number: 1521
Password for SYS user: bimaster
Password for DBSNMP user: bimanager
Password for SYSMAN user: bimanager
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):

You have specified the following settings

Database ORACLE_HOME ................ /u01/BIDEV/apps/oracle/product/11.1.0.7

Database hostname ................ gva2bidevdb.amurugroup.com
Listener port number ................ 1521
Database SID ................ BIDEV
Email address for notifications ...............gshelton@amuru.com
Email address Outgoing Mail (SMTP) server for notifications ...............vezfe1.uganda.amurugroup.com

Do you wish to continue? [yes(Y)/no(N)]: Y
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/BIDEV/apps/oracle/product/11.1.0.7/cfgtoollogs/emca/BIDEV/emca_2010-07-15_10-08-20-PM.log.
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://gva2bidevdb.amurugroup.com:1158/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jul 14, 2010 11:28:03 AM


Using EMCA with Real Application Clusters

In this section, I will be using two Database Control consoles running; one on gva2bidevdb1 and the other on linux2. From either of these consoles, you can manage and monitor all targets in the cluster.

For information on the current cluster configuration, you can run:
$ emca -displayConfig dbcontrol -cluster

STARTED EMCA at Jul 14, 2010 11:28:03 AM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database unique name: biprod

Do you wish to continue? [yes(Y)/no(N)]: Y
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/BIDEV/apps/oracle/product/11.1.0.7/cfgtoollogs/emca/orcl/emca_2010-07-15_10-30-51-PM.log.
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMDBPostConfig showClusterDBCAgentMessage
INFO:
**************** Current Configuration ****************
INSTANCE NODE DBCONTROL_UPLOAD_HOST
---------- ---------- ---------------------

biprod1 gva2bidevdb1 gva2bidevdb1
biprod2 gva2bidevdb2 gva2bidevdb1


Enterprise Manager configuration completed successfully
FINISHED EMCA at Jul 14, 2010 11:28:03 AM
The above command prompts for the database unique name for the cluster database. This will print the current configuration onto the screen, indicating the nodes that have consoles running on them and the consoles where each agent is uploading.

Create Database Console

$ emca -config dbcontrol db -repos create -cluster

STARTED EMCA at Jul 14, 2010 11:28:03 AM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database unique name: orcl
Listener port number: 1521
Cluster name: crs
Password for SYS user: bimaster
Password for DBSNMP user: bimanager
Password for SYSMAN user: bimanager
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
ASM ORACLE_HOME [ /u01/BIDEV/apps/oracle/product/11.1.0.7 ]: /u01/BIDEV/apps/oracle/product/11.1.0.7
ASM port [ 1521 ]: 1521
ASM user role [ SYSDBA ]: SYSDBA
ASM username [ SYS ]: SYS
ASM user password:

You have specified the following settings

Database ORACLE_HOME ................ /u01/BIDEV/apps/oracle/product/11.1.0.7

Database instance hostname ................ gva2bidevdb1
Listener port number ................ 1521
Cluster name ................ crs
Database unique name ................ orcl
Email address for notifications ...............gshelton@amuru.com
Email address Outgoing Mail (SMTP) server for notifications ...............vezfe1.uganda.amurugroup.com
ASM ORACLE_HOME ................ /u01/BIDEV/apps/oracle/product/11.1.0.7
ASM port ................ 1521
ASM user role ................ SYSDBA
ASM username ................ SYS

Do you wish to continue? [yes(Y)/no(N)]: y
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/BIDEV/apps/oracle/product/11.1.0.7/cfgtoollogs/emca/orcl/emca_2007-06-12_11-07-56-AM.log.
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMDBCConfig instantiateOC4JConfigFiles
INFO: Propagating /u01/BIDEV/apps/oracle/product/11.1.0.7/oc4j/j2ee/OC4J_DBConsole_gva2bidevdb1_biprod1 to remote nodes ...
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMDBCConfig instantiateOC4JConfigFiles
INFO: Propagating /u01/BIDEV/apps/oracle/product/11.1.0.7/oc4j/j2ee/OC4J_DBConsole_linux2_biprod2 to remote nodes ...
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMAgentConfig deployStateDirs
INFO: Propagating /u01/BIDEV/apps/oracle/product/11.1.0.7/gva2bidevdb1_biprod1 to remote nodes ...
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMAgentConfig deployStateDirs
INFO: Propagating /u01/BIDEV/apps/oracle/product/11.1.0.7/linux2_biprod2 to remote nodes ...
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://gva2bidevdb1:1158/em <<<<<<<<<<<
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMDBPostConfig showClusterDBCAgentMessage
INFO:
**************** Current Configuration ****************
INSTANCE NODE DBCONTROL_UPLOAD_HOST
---------- ---------- ---------------------

biprod1 gva2bidevdb1 gva2bidevdb1
biprod2 linux2 gva2bidevdb1


Enterprise Manager configuration completed successfully

FINISHED EMCA at Jul 14, 2010 11:28:03 AM

Drop Database Console

$ emca -deconfig dbcontrol db -repos drop -cluster

STARTED EMCA at Jul 14, 2010 11:28:03 AM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database unique name: orcl
Listener port number: 1521
Password for SYS user: bimaster
Password for SYSMAN user: bimanager
Do you wish to continue? [yes(Y)/no(N)]: y
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/BIDEV/apps/oracle/product/11.1.0.7/cfgtoollogs/emca/orcl/emca_2007-06-12_10-53-57-AM.log.
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...
Jul 14, 2010 11:28:03 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jul 14, 2010 11:28:03 AM


Resolving install EM issues

It’s paramount to have create the password file created in $ORACLE_HOME/dbs using orapwd otherwise you will have some errors like
Jul 14, 2010 6:13:39 PM oracle.sysman.emcp.EMReposConfig unlockMGMTAccount
CONFIG: Unlocked mgmt_view account
Jul 14, 2010 6:13:39 PM oracle.sysman.emcp.EMReposConfig invoke
CONFIG: Successfully unlocked mgmt_view account
Jul 14, 2010 6:13:39 PM oracle.sysman.emcp.ParamsManager getParam
CONFIG: No value was set for the parameter ORACLE_HOSTNAME.
Jul 14, 2010 6:13:39 PM oracle.sysman.emcp.util.GeneralUtil initSQLEngine
CONFIG: SQLEngine connecting with SID: BIDEV, oracleHome: /u01/BIDEV/apps/oracle/product/11.1.0.7, and user: SYS
Jul 14, 2010 6:13:39 PM oracle.sysman.emcp.util.GeneralUtil initSQLEngine
CONFIG: SQLEngine created successfully and connected
Jul 14, 2010 6:13:40 PM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl
CONFIG: ORA-01031: insufficient privileges

oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-01031: insufficient privileges
at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeImpl(SQLEngine.java:1530)
at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeSql(SQLEngine.java:1771)
at oracle.sysman.emcp.ParamsManager.checkListenerStatusForDBControl(ParamsManager.java:2682)
at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:368)
at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:147)
at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:222)
at oracle.sysman.emcp.EMConfigAssistant.invokeEMCA(EMConfigAssistant.java:535)
at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1215)
at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:519)
at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:468)
Jul 14, 2010 6:13:40 PM oracle.sysman.emcp.EMConfig perform
SEVERE: Listener is not up. Start the Listener and run EM Configuration Assistant again .
Refer to the log file at /u01/BIDEV/apps/oracle/product/cfgtoollogs/emca/BIDEV/emca_2010_07_14_18_08_29.log for more details.
Jul 14, 2010 6:13:40 PM oracle.sysman.emcp.EMConfig perform
CONFIG: Stack Trace:
oracle.sysman.emcp.exception.EMConfigException: Listener is not up. Start the Listener and run EM Configuration Assistant again .at oracle.sysman.emcp.ParamsManager.checkListenerStatusForDBControl(ParamsManager.java:2689)
at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:368)
at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:147)
at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:222)
at oracle.sysman.emcp.EMConfigAssistant.invokeEMCA(EMConfigAssistant.java:535)at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1215)
at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:519)
at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:468)

If you have any problems when running emca, check the log file under:
$ORACLE_HOME/cfgtoollogs/emca//

Tuesday, June 8, 2010

Autoconfig errors with icxwebprf.sh INSTE8_PRF 1

Running a post clone process on a test server results in a failure to successfully run all components and the log shows the below error


[AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution. Errors are grouped by directory and phase.
The report format is:

[PROFILE PHASE]
AutoConfig could not successfully execute the following scripts:
Directory: /u01/UAT/apps/prodcomn/admin/install/UAT_gva2ebs
icxwebprf.sh INSTE8_PRF 1

AutoConfig is exiting with status 1

select constraint_name from user_cons_columns where table_name = 'FND_OAM_CONTEXT_FILES' and column_name = 'CTX_TYPE';


/u01/UAT/apps/prodcomn/admin/log/UAT_gva2ebs

applprod@gva2ebs:/u01/UAT/apps/prodcomn/admin/log/UAT_gva2ebs $ more icxwebprf.txt
Enter value for 4: http
Enter value for 5: 8002
Updated profile option value - 1 row(s) updated
Application Id : 178
Profile Name : POR_CACERT_FILE_NAME
Level Id : 10001
New Value : /u01/UAT/apps/prodora/iAS/Apache/Apache/conf/ssl.crt/ca-bundle.crt
Old Value : /u01/PROD/apps/prodora/iAS/Apache/Apache/conf/ssl.crt/ca-bundle.crt
declare
*
ERROR at line 1:
ORA-04091: table APPLSYS.FND_PROFILE_OPTION_VALUES is mutating,
trigger/function may not see it
ORA-06512: at "APPS.FND_CORE_LOG", line 25
ORA-06512: at "APPS.FND_CORE_LOG", line 432
ORA-06512: at "APPS.FND_PROFILE", line 110
ORA-06512: at "APPS.FND_GLOBAL", line 539
ORA-06512: at "APPS.FND_PROFILE_OPTION_VALUES_AI", line 1
ORA-04088: error during execution of trigger
'APPS.FND_PROFILE_OPTION_VALUES_AI'
ORA-06512: at line 61
ORA-06512: at line 120


Solution

Disable the following 3 triggers and run autoconfig all should be okay

ALTER TRIGGER FND_PROFILE_OPTION_VALUES_AD DISABLE;
ALTER TRIGGER FND_PROFILE_OPTION_VALUES_AI DISABLE;
ALTER TRIGGER FND_PROFILE_OPTION_VALUES_AU DISABLE;

Sunday, February 28, 2010

VMWARE ESX Server 3.5 update 2 Installation

Products such as VMware Server and VMware Workstation are hypervisors that sit on top of a guest operating system, so some of the server resources are taken up by the host operating system. ESX Server is the enterprise level hypervisor from VMware, which installs on the "bare-metal", making it significantly more efficient.
Once installed, you are able to create virtual machines, each running as a complete system, with processors, memory, networking, storage and BIOS.

Download the following software.

VMware ESX Server

VMware ESX Server Installation
Start the VMware ESX Server installation by booting from the VMware ESX Server CD.



Tab to the "Skip" button and press the "Enter" key to skip the media test.




Select the "Install" installation type option, then click the "Next" button.


Accept the license agreement, then click the "Next" button.





Accept the default partitioning option by clicking the "Next" button





Click the "Next" button on the "Advanced Options" screen.



Enter the appropriate network information for your site, then click the "Next" button.



Select your time zone by clicking on the map or selecting the location on the "Location" tab, then click the "Next" button.



Enter the "root" password, and create any additional users required. Additional users can also be created from the client tool at a later time. Click the "Next" button.



Check the summary information for the installation, then click the "Next" button.



Wait while the installation takes place



Once the installation is complete, click the "Finish" button.



Once the server has rebooted, you are presented with the console window, which lists the URL for the VMware ESX Server 3 welcome page.



Open a browser on a client PC and got to the link specified on console screen.



The resulting page includes a link to the VMware Infrastructure Client installation, which we will install next.

VMWARE INFRASTRUCTURE CLIENT INSTALLATION

Navigate to the VMware ESX Server 3 welcome page using the URL listed on the console and click the "Download VMware Infrastructure Client" link. Start the installation once the download is complete.
Select the appropriate setup language and click the "OK" button.



Click the "Next" button on the welcome page



Accept the license agreement and click the "Next" button



Enter the customer information and click the "Next" button.



Click the "Next" button on the "Custom Setup" screen.



Accept the default destination folder by clicking the "Next" button.



Wait while the installation takes place.



Start the client application and log in to the ESX server with the credentials specified during the installation.



You are then presented with the console.



You are now ready to start creating virtual machines.

VIRTUAL MACHINE SETUP
The virtual machine creation process will vary a little depending on your requirement. To give you a feel for the process I will set up a small Red Hat Enterprise Linux (RHEL) 5 virtual machine.
Right-click on the ESX server in the left-hand pane and select the "New Virtual Machine..." menu option.



Select the custom option and click the "Next" button.



Enter the virtual machine name as you would like it to appear in the right-hand pane of the client, then click the "Next" button.



Select the appropriate operating system and version, then click the "Next" button.



Enter the required amount of memory for the virtual machines, then click the "Next" button.



Accept the default storage adapter by clicking the "Next" button.



Enter the appropriate disk capacity, then click the "Next" button.



If you are happy with the summary information, click the "Finish" button.




To start the virtual machine, click the play button on the toolbar. The virtual machine will start and attempt to boot from any installable media, or the network.

GUEST OPERATING SYSTEM INSTALLATION

Place the operating system disk in the client PC's CD drive and start the virtual machine by clicking the play button on the toolbar. The right pane of the client should display a boot loader, then the operating system installation screen



Continue through the operating system installation as you would for a normal server.
VMware Tools Installation
Once the operating system installation is complete, you should install the VMware tools. The process my vary a little depending on the operating system. This example shows the installation on Red Hat Enterprise Linux (RHEL) 5.
From the "VM" menu, select the "Install/Upgrade VMware Tools" menu option.



Click the "OK" button in the information dialog.



A folder group will appear containing an "RPM" file. Right-click on the file and select the "Open with "Software Installer"" menu option.



Once the software has installed, click the "OK" button and the CD should unmount automatically



You must then run the "vmware-config-tools.pl" script as the root user.
# vmware-config-tools.pl
Accept all the default settings and pick the screen resolution of your choice. The VMware tools are now installed.

Start the VMware Toolbox using the "vmware-toolbox" command.
# /usr/bin/vmware-toolbox
Select the time synchronization options, then close the dialog.



Reboot the server and the virtual machine installation is complete. You can now start using your virtual machine just like any other server.




Enjoy

VNC setup on Oracle servers running Linux Redhat operating system

VNC setup on Oracle servers running Linux Redhat operating system.
VNC is a headless display server which proves the same options than system display servers. They do not require video adapter hardware nor a monitor.
Here are the steps to set it up on Oracle servers.

1. Setting up runlevel 3
First the server runlevel must be set to 3.
This is done in the /etc/inittab file:
We replace
id:5:initdefault:
with
id:3:initdefault:

2. Running the VNC server at system startup

We configure VNC to start with runlevel 3. We can use the serviceconf system tool to do so:

root@hostname:~ # export LANG=C
root@hostname:~ # export DISPLAY=10.42.10.9:0
root@hostname:~ # serviceconf &



3. VNC server configuration

The VNC server must be started on display :0.

To do this we add/uncomment the following line in the /etc/sysconfig/vncservers file:

VNCSERVERS="0:root"

To authorize the localhost – here vernier - to access the DISPLAY, we add the red line to /root/.vnc/xstartup

#!/bin/sh

xhost +hostname
xrdb $HOME/.Xresources
xsetroot -solid grey
xterm -geometry 80x24+10+10 -ls -title "$VNCDESKTOP Desktop" &
twm &


4. VNC server status

To check if the VNC server is running, we type the following command:

root@hostname:~ # ps -ef | grep vnc
root 7019 1 0 Apr19 ? 00:00:00 Xvnc :0 -desktop X -httpd /usr/s
root 2754 2673 0 19:53 pts/0 00:00:00 grep vnc


5. VNC server manual start/stop

To start the VNC server manually, we type the following command:
/usr/bin/vncserver :0

Example with DISPLAY :1:
root@hostname:~ # vncserver :1
New 'X' desktop is hostname:1

Starting applications specified in /root/.vnc/xstartup
Log file is /root/.vnc/hostname:1.log

To stop it we type:
/usr/bin/vncserver –kill :0

Example with DISPLAY :1:
root@hostname:~ # vncserver -kill :1
Killing Xvnc process ID 17005

Wednesday, February 3, 2010

OATM Migration on 11.5.10 CU2

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.