CHAPTER 30

Upgrading an Oracle Database

Exam Objectives

• 062.2.7.1    Describe Upgrade Requirements When Certain Features or Options Are Used in Oracle Database

• 062.2.7.2    Use the Pre-Upgrade Information Tool Before Performing an Upgrade

• 062.2.7.3    Prepare the New Oracle Home Prior to Performing an Upgrade

• 062.2.8.1    Upgrade the Database to Oracle Database 12c by Using the Database Upgrade Assistant (DBUA)

• 062.2.8.2    Perform a Manual Upgrade to Oracle Database 12c by Using Scripts and Tools

• 062.2.9.1    Migrate to Unified Auditing

• 062.2.9.2    Perform Post-Upgrade Tasks

This chapter describes the process of upgrading an Oracle database. This could be an upgrade to a new major release (such as from 11.2.0.4 to 12.1.0.1) or could be applying a patchset update within a release (such as from 12.1.0.1 to 12.1.0.2). Either way, the principle is the same. Assuming that the software has already been upgraded (by using the Oracle Universal Installer [OUI] to create a new Oracle Home), certain steps must be followed before, during, and after the upgrade process.

Describe Upgrade Requirements when Certain Features or Options Are Used in Oracle Database

An upgrade involves running scripts against the data dictionary that will upgrade various database features and options. Depending on what options have been installed and configured, some preparatory work may be needed.

Oracle Label Security and Oracle Data Vault

These options are beyond the scope of the Oracle Certified Professional (OCP) curriculum, but their impact on upgrade may be tested. In summary, Oracle Label Security (OLS) is a technique for filtering access by users to rows. This is in addition to the usual mechanism of privileges. Two users may have the same SELECT or Data Manipulation Language (DML) privileges on a table, but even though they run identical SQL statements, they will see a different subset of the table. The filtering is based on session attributes over which the users have no control, and they will not be aware that the filtering has occurred.

Oracle Data Vault adds another layer to Oracle’s privilege and role model. Users can be placed in groups, and policies can be designed that control groups’ access to user objects even though they may have the requisite privilege. These policies apply to all users, including those with the SYSDBA privilege. This is the only way to limit access to data by the otherwise all-powerful database administrators.

To determine whether either OLS or Data Vault has been installed in the database, run this query:

If either option is installed, then it is possible that the SYS.AUD$ table will need to be relocated. A script is provided in the new 12.x Oracle Home to accomplish this:

The script is well documented and includes precise instructions on what to do if either OLS or Data Vault or both are installed, with variations depending on the exact release of the database to be upgraded. Copy the script from the newly installed 12.x Oracle Home to the Oracle Home currently in use, study the instructions in the script, and then run it while connected as SYSDBA.

Oracle Warehouse Builder

Oracle Warehouse Builder (OWB) is a graphical tool for designing, building, managing, and maintaining data integration processes in business intelligence systems. It is beyond the scope of the OCP curriculum. OWB is not shipped with the 12c database; therefore, if it is installed in the database to be upgraded, the upgrade routine will not upgrade it, and it will no longer be usable. It is, however, possible for a 12c database to interoperate with a separate release 11.2.0.3 (nothing earlier) OWB installation. There are three techniques for this:

•  Give the upgraded 12c database access to an existing stand-alone (that is, not installed as part of an 11g database) OWB installation.

•  Retain an already configured OWB installation in an 11g database running off the 11g Oracle Home, and continue to use this.

•  Configure a new stand-alone OWB installation.

All these methods come down to the same thing: you can no longer use OWB in the database after upgrade and must therefore provide an OWB installation external to the upgraded database.

To determine whether OWB is installed in the database, run this query:

Use the Pre-Upgrade Information Tool Before Performing an Upgrade

Following the installation of the new 12c database Oracle Home, you will have access to the Pre-Upgrade Information Tool. This is a script, preupgrd.sql, installed into the ORACLE_HOME/rdbms/admin directory. Run this script against the database to be upgraded. It will generate a report detailing any issues, as well as a “fix-up” script that will fix some issues; other issues will have to be addressed manually.

Figure 30-1 shows an example of running this script on a Windows database.

Figure 30-1    Running the Pre-Upgrade Information Tool

In the figure, the database is currently at release 11.2.0.3. The script is run from the newly installed (but not yet used) 12c Oracle Home. It produces three files, generated in a directory (cfgtoollogs<db_name>preupgrade) below the Oracle Base directory:

•  preupgrade.log details all the checks that were run with advice on items that should be addressed.

•  preupgrade_fixups.sql contains commands that should be run in the database before commencing the upgrade to fix any problems.

•  postupgrade_fixups.sql contains commands that should be run in the database after the upgrade has completed.

The log file and the scripts should be studied with care; they give information and instructions that may make the upgrade run more smoothly.

Prepare the New Oracle Home Prior to Performing an Upgrade

The new Oracle Home must be installed, using the OUI. This installation (either interactive or a silent install driven by a response file) should be a software-only install, as shown in Figure 30-2.

Figure 30-2    Installing an Oracle Home prior to upgrading a database

Do not take the option to create a database. It is possible to select the option to upgrade an existing database. This will chain the software install to a run of the Database Upgrade Assistant (DBUA). There is nothing wrong with this in theory, but most database administrators (DBAs) will want to separate these two processes in order to take the time to run the Pre-Upgrade Information Tool manually and consider thoroughly its advice. There is also the issue of downtime. Doing the install plus upgrade in one operation will usually involve a longer period of downtime than separating these actions.

At this point, consider the database listener. If you are using a database listener running from a Grid Infrastructure (GI) home, no further action is needed. A GI listener can support connections to any release of the database. But if your database listener is running off the database Oracle Home, you will want to shut down the listener running off the old Oracle Home and replace it with a listener from the new Oracle Home. This listener upgrade need not be related to the database upgrade and can be carried out before or after. The process is to copy the Oracle Net configuration files from the old home to the new and make any edits that may be necessary to entries that include directory paths.

Upgrade the Database to Oracle Database 12c by Using the Database Upgrade Assistant

The DBUA is a guided wizard that automates the process of configuring an existing database to run with the new release of the software. It can also be run noninteractively by a single statement supplied with appropriate command-line arguments that answer all the questions.

For an interactive upgrade, in a graphical terminal connect to the newly installed release 12c Oracle Home by setting the ORACLE_BASE, ORACLE_HOME, and PATH variables appropriately. Then launch the DBUA executable. On Linux, this is the file $ORACLE_HOME/bin/dbua, and on Windows, it will be on your Start button in the Configuration and Migration Tools menu for the new Oracle Home. These are the major steps:

1.  Select Operation Choose Upgrade Oracle Database to upgrade from a previous major release, or choose Move Database From A Different Release 12.1 Oracle Home to upgrade to a patchset within the major release.

2.  Select Database A list of all detected databases is presented. Choose the database to be upgraded in this operation, and supply logon details for a database user with SYSDBA privileges. On Windows, you must supply the Windows password for the operating system account under which the 12c Oracle Home is installed.

3.  Prerequisite Checks DBUA runs some checks (similar to the Pre-Upgrade Information Tool checks) against the database. In Figure 30-3, two issues have been identified.

Figure 30-3    The third step of the DBUA dialog box

The first issue shown in Figure 30-3 is informative. DBUA has detected that Enterprise Manager Database Control (the management facility shipped with release 10g and 11g databases) is installed. This will be removed during the upgrade. The second issue is that the database has been configured to work with a Lightweight Directory Access Protocol (LDAP) directory, and some adjustments to this will be needed. DBUA can do this during the upgrade, or it can be ignored for now and dealt with later.

4.  Upgrade Options There are some choices that give a limited amount of control over the upgrade process and how long it will take. The defaults are usually correct. The following are choices to consider:

•  Select Upgrade Parallelism    The default is based on the number of central processing units (CPUs) detected. You might think it is too high and would flood the machine with work or that it’s too low and that a higher value would push the upgrade through faster.

•  Recompile Invalid Objects During Post Upgrade, Recompilation Parallelism    Allowing DBUA to recompile during the upgrade will make the operation comprehensive but will also delay the postupgrade restart. You may want to recompile manually afterward instead.

•  Upgrade Timezone Data    Depending on what is already installed, you may need to update the database’s time zone files, either as part of the upgrade or later.

•  Gather Statistics Before Upgrade    If you have already gathered statistics for the database, this will not be necessary.

5.  Management Options Select whether to configure the database with Database Express (default), to register it with Cloud Control, or neither.

6.  Move Database Files DBUA can physically relocate the database and the Fast Recovery Area during the upgrade. This could be useful if, for example, you want to take the opportunity to convert from file system storage to Automatic Storage Management (ASM) storage. It will, of course, increase the duration of the operation significantly.

7.  Network Configuration This is a prompt for registering the database with an LDAP directory and with a database listener.

8.  Recovery Options Should DBUA perform a full offline backup before upgrading? The default is Yes, but if you already have a backup, considerable time can be saved by disallowing this.

9.  Summary This is a description of what will be done.

10.  Progress This is a display of what is happening, updated as the upgrade proceeds.

Following completion, DBUA will display the result, as shown in Figure 30-4.

Figure 30-4    The completion of a DBUA upgrade exercise

The Results screen may give some instructions (such as that regarding time zone files, as shown in Figure 30-4), as well as the status of the upgrade. Follow them, as well as any steps advised by the Pre-Upgrade Information Tool.

Perform a Manual Upgrade to Oracle Database 12c by Using Scripts and Tools

A manual upgrade is perhaps a little more work than using DBUA, but it does give more control to the DBA. These are the steps to follow:

1.  While connected to the old Oracle Home and with the database open, log on to the database and run the Pre-Upgrade Information Tool provided with the new software. Study the log file and scripts it generates and take any appropriate action.

2.  Back up the source database.

3.  Prepare the newly installed Oracle Home. Copy the password file and the instance parameter file into the new Oracle Home. If any parameters need to be adjusted (the Pre-Upgrade Information Tool will have informed you of this), make the adjustments first, using the SCOPE=SPFILE clause of ALTER SYSTEM.

4.  Shut down the source database.

On Windows, it is necessary to remove the old Windows service under which the database runs and to create a new one. For example, for the database named ORCLA, run these commands from the old Oracle Home:

Run this command from the new Oracle Home:

5.  Set your environment to connect to the new Oracle Home and open the database with SQL*Plus. Use the command STARTUP UPGRADE. The UPGRADE mode disables logons that do not use the SYSDBA privilege, disables system triggers, and, most importantly, permits connections to a database that has a data dictionary in an indeterminate state: neither one release nor another.

6.  Execute the parallel upgrade script, which is provided in the ORACLE_HOME/rdbms/admin directory. This is a Perl script that invokes parallelism to reduce the necessary downtime. A suitable Perl interpreter is provided in the Oracle Home. Here is the code for Windows:

Here is the code for Linux:

7.  The parallel upgrade script finishes by shutting down the database. Open it as normal.

8.  Run the Post-Upgrade Status Tool to generate a summary of the result of the upgrade exercise. If it shows any errors or any components as not VALID, take appropriate action. To run the tool, execute the script utlu121s.sql.

9.  Run scripts to complete the postupgrade operations. The catuppst.sql script attempts to fix any problems encountered by the upgrade, and the ultrp.sql script recompiles all invalid PL/SQL. Here is a Linux example:

Re-run the Post-Upgrade Status Tool to ensure that there are no remaining issues.

Migrate to Unified Auditing

Chapter 16 covered how to configure and enable unified audit. Here is a summary:

•  By default, the 12c unified audit facility is not enabled.

•  All previous auditing (standard audit, fine-grained audit, and component-specific audit) will continue to function unchanged in the upgraded database.

•  Grant the AUDIT_ADMIN and AUDIT_VIEWER roles as necessary.

•  Configure unified audit policies that match the existing audit regime.

•  During a period of downtime, enable unified audit. On Linux, relink the executables; on Windows, copy in the appropriate dynamic link library.

Perform Postupgrade Tasks

In addition to the steps identified by the Pre-Upgrade Information Tool and documented in the postupgrade_fixups.sql script, various actions may or may not be necessary depending on the environment and whether the upgrade was manual or done with DBUA. All these items should, however, be considered:

•  Update environment variables in all relevant scripts. Critical variables are ORACLE_HOME, PATH, and ORACLE_SID.

•  On Linux, update the /etc/oratab file.

•  On Windows, check all registry settings for the database.

•  If a Recovery Manager (RMAN) catalog database is in use, connect with the RMAN executable and upgrade the catalog (it is not necessary to upgrade the catalog database itself).

•  If Database Express has been installed as part of the upgrade, confirm and (if necessary) configure its listening ports. The routine for this is as follows:

•  Enable the Database Vault (if installed).

•  Reset all passwords. If the upgrade is from release 10.x, all passwords should be changed to take on the password attributes of the current release.

•  Set thresholds for tablespace usage alerts. Tablespace alerts are disabled by the upgrade and will need to be reestablished.

•  Implement new features as appropriate.

Exercise 30-1: Simulate Some Upgrade Tasks    For this exercise, it is assumed that there is not, in fact, a database that needs to be upgraded. It is, however, possible to run some of the tasks that would be required.

1.  Determine what components are installed in the database.

2.  Run the Pre-Upgrade Information Tool.

Study the output and the files generated. These are the log file preupgrade.log and the fix-up scripts preupgrade_fixups.sql and postupgrade_fixups.sql.

3.  Open the database in UPGRADE mode, confirm this, and attempt to connect as a non-SYSDBA user. Restart the database. Use commands such as these:

4.  From a command prompt, display all the options for running DBUA.

Observe the wide range of options that can be applied when running DBUA—many more than are prompted for when running it interactively.

5.  Run the script to identify and recompile all invalid objects.

Study the output of the script.

Two-Minute Drill

Describe Upgrade Requirements when Certain Features or Options Are Used in Oracle Database

•  Installed components, their version, and status are listed in the DBA_REGISTRY view.

•  Some components (such as APEX) have their own upgrade mechanisms; others (such as Oracle Warehouse Builder, Label Security, and Data Vault) have particular requirements.

Use the Pre-Upgrade Information Tool Before Performing an Upgrade

•  The Pre-Upgrade Information Tool is a SQL script provided with the new software that is run while still using the previous release.

•  The tool inspects the database and generates a report that describes the necessary actions and provides fix-up scripts with commands to be run before and after the upgrade.

Prepare the New Oracle Home Prior to Performing an Upgrade

•  The new Oracle Home must be instantiated before you upgrade the database.

•  If performing a manual upgrade, you must copy certain configuration files (such as the instance parameter file and the password file) to the new home.

Upgrade the Database to Oracle Database 12c by Using the Database Upgrade Assistant

•  The DBUA is run from the new Oracle Home and automates the entire upgrade process.

•  DBUA can be run interactively, or for silent upgrade, it can be executed from the command line with arguments that replace the dialog box.

Perform a Manual Upgrade to Oracle Database 12c by Using Scripts and Tools

•  A manual upgrade follows the same steps as the DBUA, but they are invoked individually from the command line.

Migrate to Unified Auditing

•  Following upgrade to 12c, unified auditing is not enabled.

•  To enable unified auditing, configure appropriate audit policies and then relink the Oracle executable.

Perform Post-Upgrade Tasks

•  The Pre-Upgrade Information Tool (run either manually or by DBUA) will have generated instructions on necessary post-upgrade tasks.

•  Optional (but advisable) post-upgrade tasks include gathering statistics for the database and recompiling all invalid objects.

•  Bringing new features into use is a step that can be done at any time after the upgrade.

Self Test

1.  Which of these components is no longer available in the database following an upgrade to 12c? (Choose the best answer.)

A.  Data Vault

B.  Oracle Label Security

C.  Oracle Warehouse Builder

D.  Standard database auditing

2.  Before running the Pre-Upgrade Information Tool, preupgrd.sql, how should you start the database? (Choose the best answer.)

A.  Start the database with STARTUP UPGRADE from the old Oracle Home.

B.  Start the database with STARTUP OPEN from the old Oracle Home.

C.  Start the database with STARTUP UPGRADE from the new Oracle Home.

D.  Start the database with STARTUP OPEN from the new Oracle Home.

3.  What is the output of the Pre-Upgrade Information Tool, preupgrd.sql? (Choose the best answer.)

A.  Fix-up scripts with commands to be run before and after upgrade

B.  Scripts to run during the upgrade

C.  Instructions for how to run the upgrade

D.  The script prompts for what reports and scripts should be generated

4.  When you are upgrading a database manually, what files would usually be copied from the old Oracle Home to the new? (Choose all correct answers.)

A.  Database controlfile

B.  Instance parameter file

C.  Password file

D.  Time zone files

E.  The AUDIT_FILE_DEST directory

5.  What is the correct method for using the DBUA? (Choose the best answer.)

A.  Start the database in UPGRADE mode from the new Oracle Home and then run DBUA from the new Oracle Home.

B.  Start the database in UPGRADE mode from the old Oracle Home and then run DBUA from the new Oracle Home.

C.  Start the database in OPEN mode from the old Oracle Home and then run DBUA from the new Oracle Home.

D.  Shut down the database and then run DBUA from the new Oracle Home.

6.  To minimize downtime for your users, which of these operations can be carried out while the database is open for use? (Choose all correct answers.)

A.  The backup before starting the upgrade

B.  Running the Pre-Upgrade Information Tool, the script preupgrd.sql

C.  Running the catctl.pl Perl script that parallelizes the upgrade process

D.  Recompiling all invalid PL/SQL after upgrade

E.  Gathering statistics on the data dictionary after upgrade

F.  Running the Post-Upgrade Status Tool, the utlu121s.sql script

7.  You are performing a manual upgrade of a database and have opened the database in UPGRADE mode. If a user attempts to connect to the database at this time, what will be the result? (Choose the best answer.)

A.  The connection will succeed but may compromise the success of the upgrade.

B.  The connection will succeed, but all DML and Data Definition Language (DDL) will be blocked.

C.  The connection will fail because the database listener will not spawn sessions against a database in UPGRADE mode.

D.  The connection will fail unless the user has the SYSDBA privilege.

8.  What options do you have following the 12c upgrade, if you have configured both standard audit and fine-grained audit in the source 11g database? (Choose the best answer.)

A.  You must disable both standard and fine-grained audit before the upgrade and configure unified audit after the upgrade.

B.  After upgrade, you can enable unified audit and leave the standard and fine-grained audit running in parallel for a while to ensure that the results are the same.

C.  After upgrade, the standard audit and fine-grained audit will continue to run until you enable unified audit, at which time they will cease to function.

D.  After upgrade, you can migrate first the standard audit to unified auditing and then the fine-grained audit to unified auditing.

9.  Some of the post-upgrade tasks are time consuming, and you may want to perform them after opening the database for use. If you choose to open the database for use before recompiling invalid PL/SQL with the utlrp.sql script, what may result? (Choose the best answer.)

A.  The database will not open if any of the SYS-owned supplied PL/SQL packages are invalid.

B.  The database will open, but if any users attempt to use invalid PL/SQL that has not yet been recompiled, they will receive errors.

C.  The database will open, but if any users attempt to use invalid PL/SQL that has not yet been recompiled, it will be compiled automatically.

D.  The database will open, but if any users attempt to use invalid PL/SQL that has not yet been recompiled, it will run in interpreted mode rather than native mode.

10.  Following a successful upgrade with the DBUA, you find that remote users can no longer connect to the database. What might be the problem? (Choose two correct answers.)

A.  You are running your database listener off the Grid Infrastructure home, and it must be reconfigured to point to the new release of the database.

B.  You have shut down the database listener running off the old home and have not started a listener from the new home.

C.  You have omitted copying the tnsnames.ora file from the old home to the new home.

D.  You have omitted copying the password file from the old home to the new home.

E.  You did not restart the database after the upgrade.

Self Test Answers

1.      C. Following an upgrade, Oracle Warehouse Builder can still be used, but it must be installed externally.
    A, B, and D are incorrect. A and B are incorrect because although Data Vault and OLS have special requirements for upgrade, they are still available. D is incorrect because standard auditing is enabled after upgrade, although you may want to convert to unified auditing instead.

2.      B. The tool is supplied with the new Oracle Home but run against a database opened from the old Oracle Home.
    A, C, and D are incorrect. A and C are incorrect because STARTUP UPGRADE (from the new Oracle Home) is used for the upgrade itself, not the preparatory steps. D is incorrect because the database cannot be opened from the new Oracle Home at this point.

3.      A. The scripts are preupgrade_fixups.sql and postupgrade_fixups.sql.
    B, C, and D are incorrect. B and C are incorrect because the tool is concerned with steps before and after upgrade, not during. D is incorrect because the tool is not interactive; there are no prompts.

4.      B and C. The parameter file and password file must be copied because their default location is derived from the Oracle Home.
    A, D, and E are incorrect. A is incorrect because no datafiles should be moved; an upgrade is an in-place operation. D is incorrect because the new Oracle Home will have new time zone files. E is incorrect because the AUDIT_FILE_DEST remains unchanged following upgrade.

5.      C. The DBUA must be run from the new home and will take care of all the necessary startups and shutdowns.
    A, B, and D are incorrect. A and B are incorrect because the DBUA will manage the start in UPGRADE mode. D is incorrect because the DBUA automates the whole process, including the shutdown before upgrade.

6.      A, B, D, E, and F. All of these steps can be performed against an open database.
    C is incorrect. The catctl.pl script can be run only when the database is in UPGRADE mode, during which time no regular users can connect.

7.      D. A database instance started in UPGRADE mode will not accept any logons other than those with the SYSDBA privilege.
    A, B, and C are incorrect. These are incorrect because a database in UPGRADE mode is protected against all non-SYSDBA connections.

8.      C. There is no necessity to enable unified auditing, but when you do, other audits will stop.
    A, B, and D are incorrect. A is incorrect because you can continue to use older methods of audit after upgrade. B and D are incorrect because it is not possible to run the older methods in conjunction with the new.

9.      C. Invalid packages are recompiled when necessary, but they will be a performance hit on the session that does this.
    A, B, and D are incorrect. A and B are incorrect because recompilation is automatic when needed. D is incorrect because whether the PL/SQL is set for native compilation or interpretation, it will still be recompiled on demand.

10.      B and E. B is correct because if you stop the old listener, you must configure and start a replacement. E is correct because following upgrade, DBUA leaves the database shut down.
    A, C, and D are incorrect. A is incorrect because the new release of the database will register with the GI listener, as it did before the upgrade. C and D are incorrect because although failing to copy these files may cause problems for the DBA, it should not be relevant to users.