Different Upgrade Methods For Upgrading Your Database (Doc ID 419550.1)
APPLIES TO:
Oracle Database - Standard Edition - Version 9.2.0.1 to 12.1.0.1 [Release 9.2 to 12.1]
Oracle Database - Enterprise Edition - Version 12.1.0.2 to 19.12.0.0.0 [Release 12.1 to 19]
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
GOAL
This note applies to all the database upgrades to 9iR2 and higher.
SOLUTION
The different upgrade methods you can use to upgrade your database to the new Oracle Database 9i release or higher are:
1) AutoUpgrade Tool (Oracle Database 12c Release 2 and higher)
2) Database Upgrade Assistant (DBUA)
3) Manual Upgrade
4) Export/Import
5) Data Copying
6) Golden Gate
1) AutoUpgrade Tool :
AutoUpgrade Tool can be used on upgrading to below Oracle Database releases:
Oracle Database 21c (21.3 and newer)
Oracle Database 19c (19.3 and newer)
Oracle Database 18c (18.5 and newer)
Oracle Database 12c Release 2 (12.2 + DBJAN2019RU and newer)
Oracle Database AutoUpgrade allows DBAs to upgrade one or many databases without human intervention, all with one command and a single configuration file.
Refer: AutoUpgrade Tool (Doc ID 2485457.1)
2) DBUA (Database Upgrade Assistant)
The Database Upgrade Assistant (DBUA) interactively steps you through the upgrade process and configures the database for the new Oracle Database release. The DBUA automates the upgrade process by performing all of the tasks normally performed manually. The DBUA makes appropriate recommendations for configuration options such as tablespaces and redo logs. You can then act on these recommendations. This method is very easy and user friendly. But if any error occurs it will take time to diagnose the error as the upgrade process is done automatically by the upgrade assistant.
For more information, refer to the following link:
10.2=> http://docs.oracle.com/cd/B19306_01/server.102/b14238/upgrade.htm#i1011482
11.1=> http://download.oracle.com/docs/cd/B28359_01/server.111/b28300/upgrade.htm#i1011482
11.2 => http://docs.oracle.com/cd/E11882_01/server.112/e23633.pdf
NOTE 556477.1 Complete Checklist for Upgrades to 11gR1 using DBUA
NOTE 870814.1 Complete checklist to upgrade the database to 11g R2 using DBUA
NOTE 1516557.1 Complete Checklist for Upgrading to Oracle Database 12c Release 1 (12.1) using DBUA
3) Manual upgrade
A manual upgrade consists of running SQL scripts and utilities from a command line to upgrade a database to the new Oracle Database release. A manual upgrade gives you finer control over the upgrade process as it is done step by step manually. So if any error occurs, it is easier to diagnose the error. While a manual upgrade gives you finer control over the upgrade process, it is more susceptible to error if any of the upgrade or pre-upgrade steps are either not followed or are performed out of order.
When manually upgrading a database, perform the following pre-upgrade steps:
- Analyze the database using the Pre-Upgrade Information Tool. The Upgrade Information Tool is a SQL script that ships with the new Oracle Database release, and must be run in the environment of the database being upgraded.
The Upgrade Information Tool displays warnings about possible upgrade issues with the database. It also displays information about required initialization parameters for the new Oracle Database release. - Prepare the new Oracle Home.
- Perform a backup of the database.
Depending on the release of the database being upgraded, you may need to perform additional pre-upgrade steps (adjust the parameter file for the upgrade, remove obsolete initialization parameters and adjust initialization parameters that might cause upgrade problems).
Refer to the following support notes for manual upgrade:
NOTE 1503653.1 Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1)
NOTE 837570.1 Complete Checklist for Manual Upgrades to 11gR2
NOTE 429825.1 Complete Checklist for Manual Upgrades to 11gR1
NOTE 263809.1 Complete checklist for manual upgrades to 10gR1 (10.1.0.x).
NOTE 316889.1 Complete checklist for manual upgrades to 10gR2.
NOTE 466181.1 10g Upgrade Companion
NOTE 601807.1 Oracle 11gR1 Upgrade Companion
Note: DBUA can be used only if the source & target Oracle homes are on the same Server.
But manual upgrade will work even if the source and target Home are on different servers provided that the Hardware architecture and operating system on source and target Home are the same.
Example :
- You cannot manually upgrade a database from an AIX Operating System to Solaris Operating System.
- You cannot manually upgrade a database from a Solaris X86-64 Machine to Solaris SPARC 64-bit.
4) Export/Import
The Export/Import upgrade 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 new Oracle 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 new Oracle Database.
Most importantly, the Export/Import operation results in a completely new database. Although the current database ultimately contains a copy of the specified data, the upgraded database may perform differently from the original database. For example, although Export/Import creates an identical copy of the database, other factors, such as disk placement of data and unset tuning parameters, may cause unexpected performance problems.
Upgrading an entire database by using Export/Import can take a long time, especially compared to using the DBUA or performing a manual upgrade. Therefore, 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.
For more information, refer to the following link:
http://docs.oracle.com/cd/B19306_01/server.102/b14238/expimp.htm#i262247
NOTE: Export/Import works for all versions. But for 10g and above, DataPump or Transportable Table Spaces methods are better.
NOTE 351598.1 Export/Import DataPump: The Minimum Requirements to Use Export DataPump and Import DataPump (System Privileges)
\5) Data Copying
You can copy data from one Oracle Database to another using database links. For example, you can create new tables and fill the tables with data by using the INSERT INTO statement and the CREATE TABLE ... AS statement. Copying data and Export/Import offer the same advantages for upgrading. Using either method, you can defragment data files and restructure the database by creating new tablespaces or modifying existing tables or tablespaces. In addition, you can copy only specified database objects or users.
Copying data, however, unlike Export/Import, enables the selection of specific rows of tables to be placed into the new database. Copying data is a good method for copying only part of a database table. In contrast, using Export/Import, you can copy only entire tables
Oracle Database Upgrade Guide
10g Release 2 (10.2)
Part Number B14238-02
http://docs.oracle.com/cd/B19306_01/server.102/b14238/upgrade.htm
6) Golden Gate
Check on the OTN site with the following url for how to upgrade with zero downtime using Golden Gate:
http://www.oracle.com/technetwork/middleware/goldengate/overview/index.html
REFERENCES
NOTE:837570.1 - Complete Checklist for Manual Upgrade to Oracle Database 11gR2 (11.2)
NOTE:870814.1 - Complete Checklist to Upgrade the Database to 11gR2 Using DBUA
NOTE:316889.1 - Complete Checklist for Manual Upgrades to 10gR2
NOTE:351598.1 - Export/Import DataPump: The Minimum Requirements to Use Export DataPump and Import DataPump (System Privileges)
NOTE:263809.1 - Complete Checklist for Manual Upgrades to 10gR1 (10.1.0.x)
NOTE:466181.1 - Oracle 10g Upgrade Companion
NOTE:556477.1 - Complete Checklist for Upgrades to 11gR1 Using DBUA
NOTE:601807.1 - Oracle 11gR1 Upgrade Companion
NOTE:429825.1 - Complete Checklist for Manual Upgrades to 11gR1
