Manual Upgrade Database from Oracle to Oracle


This document applies for  database management software Oracle Ent. Edition on Red Hat Linux 5. This document will guide you how to upgrade Oracle Ent Edition single Instance to database management software Oracle Ent. Edition.

The following topics guide you through the process of performing a manual upgrade. They assume that you have previously run the Pre-Upgrade Information Tool

•    Backing up the Database

•    Preparing the New Oracle Home

•    Manually Upgrading the Database

•    Troubleshooting the Upgrade

•    Cancelling the Upgrade 

Backing Up the Database

After running the Pre-Upgrade Information Tool and cleanly shutting down the instance, Oracle recommends that you back up the database as described in this section. If you encounter problems with the upgrade and wish to abandon the upgrade completely, then you must restore the database from this backup. Therefore, Oracle recommends that you back up your database now as a precaution.

There are two ways to take backup:

  1. Cold Backup
  2. Rman Backup
Cold Backup:

Copy all the datafiles, control files and Redo log files to a different location.

Rman Backup:

Sign on to RMAN:

1.    rman "target / nocatalog"

2.    Issue the following RMAN commands:




BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade;

BACKUP CURRENT CONTROLFILE FORMAT 'controlfile location and name';


Preparing the New Oracle Home

After backing up the database to be upgraded, prepare the new Oracle home in a new location. Do this for any release of Oracle Database for which you are upgrading, whether the database is release 11.2 or earlier.

To prepare the new Oracle home

1.    Copy configuration files from the Oracle home of the database being upgraded to the new Oracle Database 11g Release 2 (11.2) Oracle home:
2.    If your parameter file resides within the old environment's Oracle home, then copy it to the new Oracle home. By default, Oracle looks for the parameter file in the ORACLE_HOME/dbs directory on Linux or UNIX platforms. The parameter file can reside anywhere you wish, but it should not reside in the old environment's Oracle home after you upgrade to Oracle Database 11g Release 2 (11.2).

Note:    It might be necessary to create a text initialization parameter file (PFILE) from the server parameter file (SPFILE) so that you can edit the initialization parameters.

Sql> CREATE pfile FROM spfile;

3.    If you have a password file that resides within the old environment's Oracle home, then move or copy the password file to the new Oracle Database 11g Release 2 (11.2) Oracle home.The name and location of the password file are operating system-specific. On Linux or UNIX platforms, the default password file is orapwsid, located in the ORACLE_HOME/dbs directory. In both cases, sid is your Oracle instance ID.     
4.   The Pre-Upgrade Information Tool displays any deprecated parameters and obsolete parameters it finds in the Deprecated Parameters and Obsolete Parameters sections. Make sure the COMPATIBLE initialization parameter is properly set for Oracle Database 11g Release 2 (11.2). The Pre-Upgrade Information Tool displays a warning in the Database section if COMPATIBLE is not properly set.

5.    Adjust the values of the initialization parameters to at least the minimum values indicated by the Pre-Upgrade Information Tool.

6.    Make sure all path names in the parameter file are fully specified. You should not have relative path names in the parameter file.

7.     Make sure you save all of the files you modified after making these adjustments.

Manually Upgrading the Database

After preparing the new Oracle home, you are ready to proceed with the manual upgrade.

To manually upgrade the database

•    Shut down the instance:


•    On Linux or UNIX operating system perform the following checks:

Your ORACLE_SID is set correctly. The oratab file points to your Oracle Database 11g Release 2 (11.2) Oracle home

  • The following environment variables should point to the Oracle Database 11g Release 2 (11.2) directories:

Any scripts that clients use to set the ORACLE_HOME value must point to the new Oracle home.

•    Log in to the system as the owner of the Oracle Database 11g Release 2 (11.2) Oracle home directory.

•    At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.

    [oracle@ ~]$ cd $ ORACLE_HOME/rdbms/admin

•    Start SQL*Plus.

 [oracle@ ~]$ export ORACLE_SID=[instance_name]
[oracle@ ~]$ sqlplus sys as sysdba

SQL*Plus: Release Production on Fri Apr 29 10:29:11 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password:
•    Start the instance by issuing the following command:

            Sql> STARTUP UPGRADE

ORACLE instance started.
Total System Global Area 1000189952 bytes
Fixed Size 1337492 bytes
Variable Size 708839276 bytes
Database Buffers 285212672 bytes
Redo Buffers 4800512 bytes
Database mounted.
Database opened.
Note: The UPGRADE keyword enables you to open a database based on an earlier Oracle Database release. It also restricts logons to AS SYSDBA sessions, disables system triggers, and performs additional operations that prepare the environment for the upgrade. You might be required to use the PFILE option to specify the location of your initialization parameter file.
Once the database is started in upgrade mode, only queries on fixed views execute without errors until after the catupgrd.sql script is run. Before running catupgrd.sql, queries on any other view or the use of PL/SQL returns an error.

Common Error & Solution for Database Upgrade:

The following are common errors that might occur when attempting to start the new Oracle Database 11g Release 2 (11.2) database. Some of these errors are written to the alert log and not to your session. If you receive any of these errors, then issue the SHUTDOWN ABORT command to shut down the database and correct the problem.

ORA-00401: the value for parameter compatible is not supported by this release
Solution: The COMPATIBLE initialization parameter is set to a value less than 10.0.0.

ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE
Solution: The CLUSTER_DATABASE initialization parameter is set to TRUE instead of FALSE.

ORA-39700: database must be opened with UPGRADE option
Solution: The STARTUP command was issued without the UPGRADE keyword.

ORA-00336: log file size xxxx blocks is less than minimum 8192 blocks
Solution: A redo log file size is less than 4 MB:

If errors appear listing obsolete initialization parameters, then make a note of the obsolete initialization parameters and continue with the upgrade. Remove the obsolete initialization parameters the next time you shut down the database.
•   Sql> SPOOL upgrade.log

•    Run the Pre-Upgrade Information Tool by executing the utlu112i.sql script:

Sql> @utlu112i.sql
•    Run the catupgrd.sql script:

    Sql> @catupgrd.sql

    Note:    If you did not run the Pre-Upgrade Information Tool, the catupgrd.sql script terminates with one of    the following errors:

RA-00942: table or view does not exist
ORA-00904: "TZ_VERSION": invalid identifier
ORA-01722: invalid number
If you receive any of these errors, issue the SHUTDOWN ABORT statement, revert to the original Oracle home directory, and run the Pre-Upgrade Information Tool (utlu112i.sql)
The catupgrd.sql script determines which upgrade scripts must be run, runs them, and then shuts down the database. You must run the script in the Oracle Database 11g Release 2 (11.2) environment.
•    Restart the instance to reinitialize the system parameters for normal operation.

      Sql> STARTUP
       This restart, following the database shutdown performed as part of the catupgrd.sql script, flushes   all caches, clears buffers, and performs other housekeeping activities. These measures are an important final step to ensure the integrity and consistency of the newly upgraded Oracle Database software.
Note:    If you encountered a message listing obsolete initialization parameters when you started the database, then remove the obsolete initialization parameters from the parameter file before restarting. If necessary, convert the SPFILE to a PFILE so you can edit the file to delete parameters  

•   Run utlu112s.sql, the Post-Upgrade Status Tool, which provides a summary of the upgrade at the end of the spool log. You must run utlu112s.sql only immediately after running catupgrd.sql. Run utlu112s.sql to display the results of the upgrade as follows:
Sql> @utlu112s.sql
If the Post-Upgrade Status Tool returns errors or shows components that are not VALID or not the most recent release, then see "Troubleshooting the Upgrade" for more information.

•    Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.

Sql> @utlrp.sql
Verify that all expected packages and classes are valid:
Sql> SELECT count(*) FROM dba_invalid_objects;
Sql> SELECT distinct object_name FROM dba_invalid_objects;
Note:    If the pre-upgrade information tool detected INVALID objects and populated the registry$sys_inv_objs and registry$nonsys_inv_objs tables, then execute ORACLE_HOME/rdbms/admin/utluiobj.sql to display only those objects which are newly invalid because of the upgrade process. The utluiobj.sql script only displays objects that are now INVALID but which were VALID before the upgrade.
•    Exit SQL*Plus.

Your database is now upgraded to the new Oracle Database 11g release. You are ready to complete the procedures described in Chapter 4, "After Upgrading to the New Release".


If you retain the old Oracle software, then never start the upgraded database with the old software. Only start the database with the executables in the new Oracle Database installation. Also, before you remove the old Oracle environment, make sure you relocate any data files in that environment to the new Oracle Database environment. See the Oracle Database Administrator's Guide for information about relocating data files.

About the Post-Upgrade Status Tool

The Post-Upgrade Status Tool, which is the utlu112s.sql script, displays the status of the database components in the upgraded database and the time required to complete each component upgrade. Any errors that occur during the upgrade are listed with each component and must be addressed. The utlu112s.sql script must only be run immediately after catupgrd.sql

The Post-Upgrade Status Tool displays a report similar to the following output:

Oracle Database 11.2 Post-Upgrade Status Tool 10-18-2010 22:48:55

Component Status Version HH:MM:SS

Oracle Server. VALID 00:17:31

JServer JAVA

Virtual Machine. VALID 00:02:32



Oracle Application Express. VALID 00:23:25

Gathering Statistics. 00:05:12

Total Upgrade Time: 01:29:03

Note:    Any time after utlrp.sql is run instead of using utl112s.sql to determine the STATUS of a component, run this query:


This will return the most up-to-date information.

Troubleshooting the Upgrade:

This section explains what to do if something goes wrong with your upgrade. This section contains the following topics:

•    Resource Limits

•    Edition Session Startup Error

•    Manual Workaround for ORA-01408

•    Running the DBMS_DST Package After Upgrade Can Result in ORA-01822

•    DBUA May Mark Invalid Components with an X Before Entire Upgrade is Done

•    Component Status

•    Rerunning the Upgrade

•    Cancelling the Upgrade

Resource Limits

If you run out of resources during the upgrade, then increase the resource allocation. After increasing the resource allocation, you should perform a SHUTDOWN ABORT and restart the instance (in UPGRADE mode) before rerunning the catupgrd.sql script or restarting DBUA.

The resources that generally require increases for a new Oracle Database release are as follows:

•    SYSTEM and SYSAUX tablespaces

Typically you receive one of the following messages during the upgrade if your SYSTEM tablespace size is insufficient:

ORA-01650: unable to extend rollback segment string by string in tablespace string

ORA-01651: unable to extend save undo segment by string for tablespace string

ORA-01652: unable to extend temp segment by string in tablespace string

ORA-01653: unable to extend table string.string by string in tablespace string

ORA-01654: unable to extend index string.string by string in tablespace string

ORA-01655: unable to extend cluster string.string by string in tablespace string

To avoid these errors, set AUTOEXTEND ON MAXSIZE UNLIMITED for the SYSTEM and SYSAUX tablespaces.

ORA-04031: unable to allocate string bytes of shared memory ("string","string","string","string")

You might require larger shared memory pool sizes in some cases. The error message indicates which shared memory initialization parameter must be increased.

If you are using an undo tablespace, then be sure it is at least 400 MB.

Rerunning the Upgrade

You can rerun the upgrade with the catupgrd.sql script as described in the following steps.

To rerun the upgrade

1.    Shut down the database as follows:


3.    Restart the database in UPGRADE mode:


5.    Set the system to spool results to a log file for later verification of success:

6.  Sql> SPOOL upgrade.log

7.    Rerun catupgrd.sql:

Sql> @catupgrd.sql
Note:    You can rerun the catupgrd.sql script as many times as necessary. The first time you run the script, there should be no error messages returned. If you rerun the script, then the ORA-00001 message is displayed. You can safely ignore this message.

Cancelling the Upgrade

If you completed the steps in "Backing Up the Database" to back up your database, then the easiest way to cancel the upgrade is to restore that backup as described in the following procedure.

To cancel the upgrade by restoring the previous backup

Restore Usingin RMAN:

1.    Log in to the system as the owner of the Oracle home directory of the previous release.

2.    Sign on to RMAN:

3.    rman "target / nocatalog"

4.    Issue the following RMAN commands:




    RESTORE CONTROLFILE FROM 'save_controlfile_location';


    RESTORE DATABASE FROM TAG before_upgrade



Restore Using Cold Backup:

    Copy database backup files to original location and database is restored.


  1. Soon, I am going to write step by step method to upgrade Oracle or above database to Oracle

  2. For step by step Manual Upgrade please visit

  3. Hi,
    I am facing below error while upgrading in Windows environment as:

    SELECT TO_NUMBER('MUST_BE_11_2') FROM v$instance
    ERROR at line 1:
    ORA-01722: invalid number

    Above error is faced when executing catupgrd.sql.

  4. Avinash,

    There is no filed like MUST_BE_11_2 in v$instance view. What do you want to achieve using this query ?

    I think you are looking for this.

    SQL> select version from v$instance;


  5. One more reason for same, you did not start your database in upgrade mode. Please start your database using "startup upgrade". Then you will not see this error.