Upgrading to Oracle 11g or Oracle 12c using Import/Export data pump

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

Before you begin

To upgrade your database from Oracle 10g to 11g or later, refer to the following Oracle documentation:

Best Practices for Upgrading to Oracle Database 11g Release 2: http://www.oracle.com/technetwork/database/upgrade/best-practices-for-upgrading-11gr2-174946.pdf
Oracle Database Upgrade Guide 11g Release 2 (11.2) E10819-02: http://docs.oracle.com/cd/E11882_01/server.112/e10819/toc.htm
Oracle Database Upgrade Guide 12c: http://docs.oracle.com/database/121/UPGRD/toc.htm

Procedure

To migrate to Oracle 11g or later by using Import/Export, complete the following steps:

Stop all application servers that are pointing to the schema that is running on the database server to get a consistent copy.
Log in to the server as Oracle owner.
Run the following command as the user with DBA rights to export the data.
```
expdp <USER NAME>/<PASSWORD> dumpfile=expdp_v9_data.dmp directory=dumps logfile=expdp_v9_data.log
```
where

directory=dumps is database directory that must be created before exporting data using data pump

Note: Ensure to keep enough space for the database directory.
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 by using one of the following options:
```
remap_tablespace=old:new
transform=segment_attributes:n
```
Configure the database by creating the schema, user, and assigning permissions.
For information about the required configurations, see Configurations for the Oracle Database.
Optional: Use the Checklist for Database Dump Import to ensure that all necessary steps are completed for importing the data dump.
Restore the dump file that is created in step 2 in the newly created schema on the 11g or 12c database server by using the following commands.
```
impdp <USER NAME>/<PASSWORD> directory=dumps dumpfile=expdp_v9_data.dmp logfile=impdp_v9.log
```
After the import is complete, complete the following steps.
1. Log in as the schema owner.
2. Run the following command to create the SPLIT_TBL type.
```
CREATE OR REPLACE TYPE SPLIT_TBL AS TABLE OF VARCHAR2(4000);
```
3. Optional: Run the following command to compile all invalid objects.
```
begin
dbms_utility.compile_schema(schema => USER);
end;
```
Collect statistics on the imported schema on the 12c database server.