Upgrade to 11g using Import / Export

Datapump Import / Export

This procedure involves building a new database and importing all the application data from the old database to the new database.  Since the database is prebuilt as an 11g database there is no upgrade needed it is just the movement of data. This type of migration works best with smaller data sets. There needs to be at least enough space to equal the size of the current data (unless compression is implemented).  If there is limited network connectivity then enough space for dump files will need to exist.

Benefits

• When importing data from an earlier release, the Import utility of the new oracle database 11g release makes appropriate changes to the data definitions as it reads export dumps from earlier versions.
• Easy Fallback because Export/Import method does not change the source database. 
• Defragments the data.  You can compress the imported data to improve performance 
• Restructures the database.  Can create new tablespaces, modify existing, tables, tablespaces, or partitions.
• Enables the copying of specified objects or users allowing for the merging or separating of applications within databases.
• If a full database export to the file system is used then it can serve as a logical backup archive.
• Export can be done across the network and eliminate the need of creating export files..  this will not work on LONG/LONG RAW or nested tables…limited by network bandwidth
• Can change the database charactersets.**** It needs to be noted that changing charactersets does require a lot of research on the current database objects, procedures, triggers, data and application to make sure that it will not corrupt the data. It is not recommended to do this as part of the upgrade.****

Limitations

• In order to maintain data integrity database must remain in restricted mode (or otherwise protected from changes). 
• This creates an entire new database therefore any factors that might affect performance such as tuning parameters, disk placement of data, non-default initialization parameters need to be accounted for outside the upgrade process.
•  May take an exceptional period of time to accomplish.  (Probably the largest downtime of all options)
•  Does not handle XMLSCHEMA data types in 10.2
•  Standby database will need to be recreated.

Assumptions:

1.       Disk space exists for importing entire database

2.       Network stable and bandwidth large enough to handle network traffic or new servers can access disks export dumps are written to.

High Overview

1.       Create new database

2.       Perform full export using SYSTEM from SOURCE database (depends on size)

a.        Grants on SYS’s objects have to be exported separately (a few hours)

3.       Perform full import using SYSTEM to TARGET database (depends on size and method)

a.        Rebuild indexes (depends on the number of indexes but could be several hours)

4.       Verify all objects exist

5.       Backup Database / Recreate Standby Database

Visual Overview