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

11g Database Upgrade Options

11g Database Upgrade Options 

The next several Blog entries will be centered on research that I conducted on the possible upgrade options when moving the system to new hardware on both the production and backup servers.

Today we briefly look a some of the options available and I ranked them from  1-5 in  several areas.
 

The simplest upgrade to perform is by installing 11g software on the same server as the 10g instance resides and upgrade the datafiles in place.  The time it takes to upgrade in this manner does not depend on the size of the database but rather the options installed (partitioning, xml, ect.). If all pre-upgrade tasks are completed before the actual time of the upgrade then a database upgrade using Database Upgrade Assistant usually runs between 30 and 90 minutes. There are methods of moving a database to 11g that can either increase or dramatically decrease this downtime. Some of them involve running the DBUA or manually running upgrade scripts and some do not. This paper highlights some of the different options available. They can be modified and adjusted according to needs but the general steps are outlined.

The following table summarizes the upgrade methods and rates them (1 to5) in several areas.  The difficulty in setup describes how hard it would be to setup the scenario. The higher the difficulty means that there is more that needs to be organized between teams, communicated, work done on both the current operations environment (OE) and rehost operations environments (ROE). The usability for testing measures how difficult it would be to use the environment for testing transactions multiple times. The higher the difficulty means that there is more preliminary setup, steps to assure recoverability to designated point in times, or steps to create a repeatable process. The higher the execution difficulty relates to the complexity the actual upgrade process including tasks that need to be performed by non-dbas involved with migrating to 11g on the day of cutover. The fallback ability describes how difficult it would be to fall back to the OE 10g environment. As an example a 1 would indicate that you just need to point the clients to the OE system. A 5 would indicate you would need to recover from a previous backup. The final column estimated down time is just a SWAG (Scientific Wild Butt Guess) on how long the database would be unusable. This can be adjusted as testing occurs.

Method	Difficulty in Setup	Usability for Testing	Execution Difficulty	Fallback Ability	Estimated Down Time Guess
Datapump Import / Export	2	3	2	1	??? Depends on dataset size
Transportable Tablespaces	2	Not an Option	2	2	60 Minutes
Logical Standby Database	4	1	1	1	Less than 15 minutes
Upgrade using DBUA	2	Not an Option	3	5	Depends on options installed 30- 90 minutes
Upgrade using command line interface	2	Not an Option	5	5	Depends on options installed 30- 90 minutes
Physical Standby	4	3	2	1	Depends on options installed 30- 90 minutes
Oracle Streams / Golden Gate (not really considered because of cost / setup)	>5	5	3	1	Less than 15 minutes