Oracle offers several methods to upgrade your database, which support the complexities of your enterprise.
There are differences between between upgrading and migrating data.
The Automated (DBUA) Method for Upgrading Oracle Database
Database Upgrade Assistant (DBUA) interactively steps you through the upgrade process and configures the database for the new Oracle Database release.
DBUA starts the Pre-Upgrade Tool, which automatically fixes some configuration settings to the values required for the upgrade. For example, the Pre-Upgrade Tool can change initialization parameters to values required for the upgrade. The Pre-Upgrade Tool also provides you with a list of items that you need to fix manually before you can continue with the upgrade.
The Manual, Command-line Method for Upgrading Oracle Database
Manual upgrades provide benefits and risks.
A manual upgrade consists of running SQL scripts and utilities from a command line to upgrade a database to the new Oracle Database release.
Manual upgrades give you finer control over the upgrade process. However, manual upgrades are susceptible to error when upgrade or pre-upgrade steps either are not followed, or are performed out of order.
Before the Upgrade
Analyze the database using the Pre-Upgrade Information Tool.
The Pre-Upgrade Information Tool is a Java JAR file that is supplied with Oracle Database. When you start the tool, it self-extracts, and then executes SQL scripts.
The Pre-Upgrade Information Tool displays warnings about possible upgrade issues with the database, and generates fixup scripts for you to use to address any issues. It also displays information about required initialization parameters for the new release of Oracle Database.
Prepare the new Oracle home.
Perform a backup of the database.
Depending on the Oracle Database release you upgrade, you can be required to perform more pre-upgrade steps. These steps can include adjusting the parameter file for the upgrade, removing desupported initialization parameters, or adjusting initialization parameters that can cause upgrade problems.
The Export/Import Method for Migrating Data When Upgrading Oracle Database
Oracle recommends using Data Pump Export and Import for higher performance, and to ensure support for new data types.
Unlike DBUA or a manual command-line upgrade, the Oracle Data Pump Export and Import utilities physically migrate a copy of data from your current database to a new database in the new release. Before you can load export dump file contents, either the new database must exist, or you must create the database in the new Oracle home.
During a data import from an earlier release, the new Oracle Database release import utility makes appropriate changes to data definitions as it reads the export dump files from the earlier release.
If your database is earlier than Oracle Database release 10.1, then you can use the original Export and Import utilities to perform a full or partial export from your database, followed by a full or partial import into a new database for the upgraded Oracle Database. Export/Import can copy a subset of the data in a database, leaving the original database unchanged.
The original Export utility is no longer being updated to support new data types.
The Effects of Export/Import on Upgraded Oracle Databases
Review this topic to understand the benefits of Export/Import data migration.
The Export/Import data migration method does not change the current database, which enables the database to remain available throughout the upgrade process. However, if a consistent snapshot of the database is required (for data integrity or other purposes), then the database must run in restricted mode or must otherwise be protected from changes during the export procedure. Because the current database can remain available, you can, for example, keep an existing production database running while the newly upgraded Oracle Database database is being built at the same time by Export/Import. During the upgrade, to maintain complete database consistency, changes to the data in the database cannot be permitted without the same changes to the data in the newly upgraded Oracle database.
Most importantly, the Export/Import operation results in a completely new database. Although the current target database ultimately contains a copy of the specified data that you migrated, the upgraded database can perform differently from the original source database. Although Export/Import creates an identical copy of the database, other factors can cause unexpected performance issues. (For example: disk placement of data, and unset tuning parameters).
Export/Import Benefits for Migrating Data for Oracle Database
Migrating data when upgrading Oracle Database using Export/Import provides benefits that can increase performance.
Using Export/Import to migrate data provides the following benefits:
Defragments the data. You can compress the imported data to improve performance.
Restructures the database. You can create new tablespaces or modify existing tables, tablespaces, or partitions that you want to populate with imported data.
Facilitates side-by-side testing of the old and new releases of Oracle Database because an entirely new database is created.
Enables the copying of specified database objects or users. Importing only the objects, users, and other items you need is useful for establishing a test environment for the new software on only a subset of the production data. Data Pump Export/Import provides flexible data-subsetting capabilities.
Serves as a backup archive. You can use a full database export as an archive of the current database.
Enables you to establish the upgraded database on a different operating system or hardware platform than the platform on which your earlier release database is placed.
Network-based Data Pump Import enables you to load the new release Oracle Database directly across the network for your earlier release Oracle Database. By using network-based Data Pump import, you are not required to use intervening dump files.
Time Requirements for Migrating Data and Upgrading with Export/Import
Understand the time it takes for data migration and software upgrades.
Migrating data and upgrading an entire Oracle database by using Export/Import can take a long time, especially compared to using DBUA or performing a manual upgrade. You may need to schedule the upgrade during non-peak hours or make provisions for propagating to the new database any changes that are made to the current database during the upgrade.