CHAPTER 29

Upgrading Oracle Database Software and Migrating Data

Exam Objectives

• 062.2.6.1    Describe Upgrade Methods

• 062.2.6.2    Describe Data Migration Methods

• 062.2.6.3    Describe the Upgrade Process

• 062.2.10.1    Migrating Data by Using Oracle Data Pump

This chapter covers the technique for upgrading the database software and migrating data from a database of one release to a database of another.

From release 11.2.x, the routine for upgrade is the same whether you are applying a patchset (to move from, for example, release 12.1.0.1 to release 12.1.0.2) or performing a major release upgrade (from, for example, 11.2.0.3 to 12.1.0.1). In either case, it is necessary to install a new Oracle Home. Before release 11.2.x, a major release required a new Oracle Home, but a patchset was applied to the existing Oracle Home. Now, only individual patches or patchset updates are applied, in place, to an existing home.

Having upgraded the database software, you must either upgrade the database itself or create a new database with the new software and move the data into it. Database upgrade is described in the following chapter. Data migration to a new database using the Data Pump utility is described in this chapter.

Describe Upgrade Methods

Moving from one release of the database to another involves two distinct processes: upgrading the database software and upgrading the database. The software and the database must always be at the same release level, except for the short period while the database is being upgraded. The software upgrade is accomplished with the Oracle Universal Installer (OUI). Then there are two options for the database upgrade: using the Database Upgrade Assistant (DBUA), which automates most of the steps, and performing all the steps manually.

Software Upgrade

Oracle products have a multifaceted release number, with five elements:

•  Major release number

•  Database maintenance release number

•  Application server release number (always zero for a database)

•  Component-specific release number

•  Platform-specific release number

To determine the release of your database, query the V$VERSION view. Within the database, it is possible for some installed components to be at different releases. To determine what is installed at what release, query the DBA_REGISTRY view. Figure 29-1 shows these queries on a database running on 64-bit Windows.

Figure 29-1    Determining the release of the database

In the figure, the database release is 12.1.0.1.0, which was the first 12c production version, specifically, release 12, the first maintenance release. The fourth digit indicates the patchset. Patchset 1 is included in the first production release (the final beta release, used for developing much of this book, was 12.1.0.0.0). The second query in the figure shows that all the components installed in the database are also at release 12.1.0.1.0, with the exception of Application Express (APEX). APEX has its own upgrade routine, and the release of APEX is largely independent of the release of the database.

The method for software upgrade is to use the OUI. A patchset (identified by the fourth element of the release number) of the database is delivered as a self-contained installable image that includes a copy of OUI that is appropriate for the patchset. Launch OUI by running the runInstaller.sh script (Linux) or the setup.exe file (Windows) that is included with the patchset, and follow exactly the installation routine detailed in Chapter 1. The software must be installed into a newly created Oracle Home. OUI will prompt for the location. Best practice is to create the new home beneath the Oracle Base directory, next to the existing Oracle Home. To start using the newly installed version of the software, adjust your ORACLE_HOME and PATH environment variables to point to the new home. Then create (or upgrade) a database.

When using OUI to install an Oracle database home (whether a base release or a patchset), the OUI will detect all previous installations. It can do this by reading the content of the OUI Inventory, which will have been created during the first installation. If OUI detects a previous installation, it will prompt for whether any existing databases should be upgraded. Should you answer “yes,” following the installation OUI will launch the DBUA to perform these database upgrades. Alternatively, you can answer “no” and run the DBUA later. This approach means that you can separate the tasks of software upgrade and database upgrade. It also means that you can upgrade databases manually, by running scripts, rather than using the DBUA.

Database Upgrade: DBUA or Manual

DBUA can be launched by the OUI or as a stand-alone tool at any time. It is a graphical interface that guides you through the database upgrade process, although it can also be driven through a command-line interface, in which case no prompts need be answered.

The DBUA functionality is as follows:

•  Automates the entire process

•  Supports single-instance and clustered environments

•  Upgrades databases and Automatic Storage Management (ASM) storage

•  Checks and (where possible) fixes prerequisites

•  Logs errors and generates a Hypertext Markup Language (HTML) report of the upgrade process

Using the DBUA is undoubtedly the easiest way to upgrade and is less prone to error than a manual technique. But it is not always appropriate. For example, if downtime is critical, it may be faster to upgrade manually. DBUA generally does all steps sequentially, many of them while the database is not available for use. A manual upgrade may be able to perform some steps in parallel and others either before or after the essential downtime period.

A manual upgrade will typically involve these tasks:

•  Space check of the SYSTEM and SYSAUX tablespaces

•  Adjust newly obsoleted or deprecated parameters

•  Run scripts to upgrade the data dictionary and installed components

•  Recompile all stored code

Whether you are using DBUA or a manual method, direct upgrade to 12.1 is possible from these releases:

•  10.2.0.5 (the terminal release of 10g)

•  11.1.0.7 (the terminal release of 11g R1)

•  11.2.0.2 or later

All other releases must be upgraded to one of those listed before you upgrade to 12.1.

Describe Data Migration Methods

A database upgrade has two major restrictions. First, it must be performed on the same platform. You cannot move from, for example, Windows to AIX during an upgrade. Second, you cannot carry out any form of reorganization or implementation of new features during the upgrade. After it has completed, nothing has really changed beyond the fact that you are running the database off new binaries. Data migration gets around both these limitations.

A migration is the process of transferring data from one database into another. In the context of upgrade, this means creating a new database of the new release and copying the entire user dataset from the old database into the new. The new database can be configured, in advance, with all the new features enabled that you want to use. Then as the data is inserted, it will take on the characteristics of the new release. Furthermore, the new database can be on a different platform. The downside of data migration as an upgrade technique is the necessary downtime. An upgrade of a database with DBUA might involve downtime of less than an hour; a migration could take hours or even days, depending on the volume of data to be moved.

Data migration is usually accomplished with the Data Pump export/import utility. Particular advantages are as follows:

•  Data Pump can work across platforms.

•  Source and destination character sets need not be the same.

•  The source can be any release from 10.0 upward.

•  It is not necessary to transfer the entire database if only a subset is needed.

•  Data segments will be reorganized as part of the process.

If the migration is from a pre-10g database, it can still be accomplished from any release by using the legacy exp/imp utilities.

Describe the Upgrade Process

Database upgrade is an operation that is fraught with peril. Theoretically, an upgrade can do nothing but good, but there is always the possibility that the behavior of the applications using the database will change. For that reason, testing is vital. There are typically six steps to an upgrade:

1.  Prepare to upgrade. Choose an upgrade method, install the new Oracle Home, develop a test plan, and determine what new features to implement.

2.  Upgrade a test database. Test the upgrade process on a nonproduction clone of the database. In particular, note the necessary downtime.

3.  Test the upgraded database. Complete the planned tests. In particular, ensure that performance has not regressed. Iterate steps 2 and 3 as necessary until all issues are resolved.

4.  Prepare the production database. Stop all user activity according to an agreed schedule for downtime and then take a full backup.

5.  Upgrade the production database. Follow the tried-and-tested upgrade procedure. Take a full backup after completion. The downtime will now be over.

6.  Tune the upgraded database. As the system comes back into use, monitor performance and carry out normal proactive and reactive tuning work.

Migrate Data by Using Oracle Data Pump

The use of Data Pump for transferring individual tables, schemas, and various object types between databases was discussed in Chapter 17. Data Pump can also be used to copy an entire database; all user objects can be read from one database and created in another. Because the objects are represented in a Data Pump dump file logically rather than physically, there is no reason why they cannot be read from a database of one release on one platform and written into a database of another release, possibly on another platform. Thus, the database can (in effect) be upgraded by the export/import process. It is possible to downgrade through the same technique, but only if the objects in the source database do not require any features that are not available in the destination.

If the new database is configured with various features enabled, the data will take on these features during the import. If run completely on defaults, a full import will create tablespaces, schemas, and objects as they were in the source before inserting data—but they can be pre-created. For example, the tablespaces in the source database might be using the old storage mechanisms of dictionary-managed extents and freelist-managed segments; the destination tablespaces would be created, in advance, with the current defaults of local extent management and automatic segment space management.

One issue with using Data Pump for upgrade is the space requirement. A large database will require a great deal of space for the dump files generated by the export. Furthermore, if the upgrade is to a destination database on a different machine, the dump will have to be copied to the remote machine—where the same amount of space will be required again. There is also a time penalty; the export must complete before the copy can begin, and the copy must complete before the import can begin. A network mode import avoids both the space and the time issues.

To use the network mode of Data Pump, a database link must exist from the destination database to the source database. This link must connect to a user with the DATAPUMP_EXP_FULL_DATABASE role. Then, running the import on the destination database (as a user with the DATAPUMP_IMP_FULL_DATABASE role) will launch worker processes on the source database that read the data and write it through the database link to the destination database, where more worker processes write the data to the database. This mechanism avoids the need to stage the data on disk as a dump file and also means that the export, the copy, and the import all run concurrently. Figure 29-2 shows the initiation of this operation.

Figure 29-2    Launching a full import in network mode

In Figure 29-2, the user is connected to a database named orclb. This is a release 12.1.0.1 database. The database link orcla connects to an 11.2.0.3 database. Then the impdp command starts a job that will perform a complete migration of all user data from orcla into orclb, through the database link. Schemas and tablespaces will be created as they were in the source database, although they can be pre-created with appropriate characteristics if desired. During the import there will be many messages of the form “ORA-31684: Object … already exists” as the import encounters objects that are part of Oracle’s seeded schemas. These can safely be ignored. The end result will be a complete migration of all user data.

Exercise 29-1: Perform a Data Pump Full Database Migration    In this exercise, you will simulate a data migration from one database to another. It is assumed that the source database is a database created from the General Purpose template, with the sample schemas installed.

1.  Create a full export of the database, using the default DATA_PUMP_DIR directory.

2.  Drop any tablespaces and schemas that hold user data. These commands will be needed:

At this point, you have a database that is devoid of user data.

3.  Import the user data into the database.

4.  Confirm that the tablespaces and users dropped in step 2 have been created and that the schemas are populated with tables and other objects.

Two-Minute Drill

Describe Upgrade Methods

•  The Database Upgrade Assistant automates the upgrade and all before and after checks.

•  A manual upgrade gives the DBA full control of all steps, which is a more complex process but possibly faster.

•  An upgrade is an in-place operation; it is not possible to change platform, physical location, character set, or indeed anything except the release.

•  Upgrade is possible only from a defined list of previous releases.

Describe Data Migration Methods

•  A Data Pump migration exports from one database and imports into another.

•  A migration can go across platforms as well as releases.

•  The source database can be any release from 10.0 onward.

•  A network mode import obviates the need to stage a dump file on disk.

Describe the Upgrade Process

•  A new Oracle Home, of the new release, must be installed with the OUI.

•  Determine whether to use DBUA or a manual upgrade.

•  Design and implement a test plan.

•  Perform the production upgrade.

•  Verify the success of the operation.

Migrate Data by Using Oracle Data Pump

•  A new Oracle Home, of the new release, must be installed with the OUI.

•  Create a new database.

•  Create any objects that you want to configure (such as tablespaces).

•  Perform a full export of the source database.

•  Transfer the dump file to the destination database.

•  Import the dump file.

•  Redirect users to the new database.

Self Test

1.  What can be accomplished during an upgrade when using the Database Upgrade Assistant? (Choose two answers.)

A.  Conversion from a legacy character set to Unicode

B.  Direct upgrade from any release of 11g to 12c

C.  Direct upgrade from the terminal release of 10g to 12c

D.  Verification of prerequisite conditions

2.  What considerations are needed for space usage during an upgrade? (Choose the best answer.)

A.  None: an upgrade is an in-place operation and requires no significant additional storage.

B.  It is possible that the SYSTEM and SYSAUX tablespaces will expand during an upgrade.

C.  Up to double the space needed for data will be needed during the upgrade.

D.  No archivelogs will be generated because an upgrade is not recoverable and no redo is generated.

3.  Which of the following can be accomplished with a Data Pump migration? (Choose all correct answers.)

A.  Upgrade from any release of 10g or 11g

B.  Character set conversion

C.  A move from a 32-bit to a 64-bit platform

D.  Direct upgrade from the terminal release of 9i

4.  Which of these correctly describes a network mode Data Pump operation? (Choose the best answer.)

A.  Create database links in both the source and destination databases, and run expdp on the source and impdp on the destination.

B.  Create a database link in the source pointing to the destination, and run expdp on the source.

C.  Create a database link in the destination pointing to the source, and run impdp on the destination.

D.  Run expdp in the source, writing a dump file to a pipe, and run impdp on the destination, reading the dump file from the pipe.

5.  What does the Database Upgrade Assistant upgrade? (Choose the best answer.)

A.  Oracle database software and databases

B.  Oracle database software

C.  Oracle databases

D.  User data storage structures and user data logical attributes

6.  Under which circumstance must a Data Pump export be performed when carrying out a database migration? (Choose the best answer.)

A.  The database must be open read-only.

B.  The database must be in mount mode.

C.  The database must have restricted session enabled.

D.  The database must be open.

7.  If a full database import operation attempts to import objects into a tablespace that does not exist, what will happen? (Choose the best answer.)

A.  The tablespace must be pre-created, or the operation will fail.

B.  The tablespace will be created if Oracle Managed Files has been enabled in the destination database.

C.  The import will succeed if the tablespace is specified with the TABLESPACES parameter.

D.  The objects will be imported into the database’s default permanent tablespace.

Self Test Answers

1.      C and D. Direct upgrade is possible from the terminal release of 10g but not from earlier releases. The DBUA runs verification scripts before and after the upgrade.
    A and B are incorrect. A is incorrect because character set conversion is not part of DBUA’s functionality; you must use other utilities for this. B is incorrect because direct upgrade from 11g is possible only for the terminal release of 11.1 or any release from 11.2.0.2.

2.      B. It is usual for data dictionary objects to increase in size as a result of an upgrade (Oracle keeps getting bigger…).
    A, C, and D are incorrect. A is incorrect because space will often be required for data dictionary objects. C is incorrect because user data is not affected by an upgrade. The changes are usually limited to metadata. D is incorrect because an upgrade involves executing a large number of DML and DDL queries against the data dictionary, which does generate redo.

3.      A, B, and C. Data Pump is compatible across all versions that support it, from 10.0 onward. It can move data across any supported platforms, performing character set conversion as it does so.
    D is incorrect. This is incorrect because Data Pump was introduced with release 10g. To migrate from 9i, you must use the legacy exp/imp utilities.

4.      C. A network mode import is initiated by impdp, although worker processes will run at both the source and the destination.
    A, B, and D are incorrect. A and B are incorrect because a network mode operation is managed from the destination only. D is incorrect because it is not possible to import a dump file until it has been completed. The technique described does, however, function with the legacy imp/exp utilities.

5.      C. The DBUA upgrades databases—and nothing else.
    A, B, and D are incorrect. A and B are incorrect because the software must be upgraded with the OUI, not the DBUA. D is incorrect because restructuring storage and objects is a task that must be done after the upgrade, not during the upgrade.

6.      D. The export is a perfectly normal export operation with no special requirements.
    A, B, and C are incorrect. A and B are incorrect because the database must be open read-write; otherwise, it is not possible for Data Pump to create its master table. C is incorrect because although restricted mode might be a good idea, it is not a requirement.

7.      B. If Oracle Managed Files (OMF) has been enabled, tablespaces will be created accordingly.
    A, C, and D are incorrect. A is incorrect because the import will attempt to create the tablespace, using either OMF or the datafile definition from the source database. C is incorrect because the TABLESPACES parameter identifies a list of tablespaces to import; it is not relevant for a full import. D is incorrect because if a suitable tablespace neither exists nor can be created, there will be an error.