Upgrading to Oracle 11g using Import/Export

For migrating your Emptoris® applications, you must upgrade your database to Oracle 11g.

To migrate to Oracle 11g using Import/Export, complete the following tasks:

1. Stop all application servers that are pointing to the schema that is running on the Oracle 10g database server to get a consistent copy.
2. Export the schema from the Oracle 10g database using the exp/expdp command.
3. Create a table space for the application objects.
   Note: For the table space size, consider a 5% additional space for new objects that are created during the upgrade process.

   If using export/import, use the same table space name that you were using for the 10g database. Otherwise, the tables with CLOB columns are not imported properly.

   If using datapump impdp/expdp, you can change the table space using one of the following options:

   remap_tablespace=old:new
   transform=segment_attributes:n

4. Create a schema on the Oracle 11g database server.
5. Replace the user name, password, and table space using the following command.

   CREATE USER <username> IDENTIFIED BY <password> DEFAULT TABLESPACE <tablespace_name> TEMPORARY TABLESPACE TEMP
   PROFILE DEFAULT ACCOUNT UNLOCK;

6. Assign unlimited quota to the user on <tablespace_name>.
7. Grant the following privileges to the user.

   GRANT CONNECT
   GRANT CTXAPP
   GRANT RESOURCE
   GRANT CREATE ANY CONTEXT
   GRANT CREATE ANY SYNONYM
   GRANT CREATE TABLE
   GRANT CREATE VIEW
   GRANT DROP ANY CONTEXT
   GRANT QUERY REWRITE
   EXECUTE ON SYS.DBMS_AQ_BQVIEW
   EXECUTE ON SYS.DBMS_LOB
   EXECUTE ON SYS.UTL_RAW

8. Restore the dump file that is created in step 2 in the newly created schema on the 11g database server using the imp/impdp command.
9. Collect statistics on the imported schema on the 11g database server using the analyze/dbms_stats command.