Zahid Karim
Recent Posts
1.Write fast and efficient PLSQL - DBMS_PROFILER
2.Fix physically corrupt data blocks using PLSQL - DBMS_REPAIR
3.Encrypt or Decrypt sensitive data using PLSQL - DBMS_CRYPTO
4.ORA-24248 XMLDB extensible security not installed
5.Upgrade Oracle 10g Release 2 to Oracle 11g Release 1
Find me on
Oracle 11g Release 2 Pre Upgrade tool utlu112i sql Install Oracle 11g Release 1 on RHEL 5

Upgrade Oracle 10g Release 2 to Oracle 11g Release 2

Posted by Zahid on July 11, 2010.

This article is a step by step guide to upgrade oracle database 10gR2 10.2.0.4 to 11gR2 11.2.0.1. The current environment is RHEL 5.4.

Preparing for upgrade to 11g Release 2

The first step is to stop all the components of the oracle 10g database.

-- stop isqlplus
$ isqlplusctl stop
iSQL*Plus 10.2.0.4.0
Copyright (c) 2003, 2007, Oracle.  All Rights Reserved.
Stopping iSQL*Plus ...
iSQL*Plus stopped.

-- stop enterprise manager dbconsole
$ emctl stop dbconsole
TZ set to US/Eastern
Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0  
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
Stopping Oracle Enterprise Manager 10g Database Control ... 
 ...  Stopped. 

-- stop listener
$ lsnrctl stop

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully

$ sqlplus / as sysdba

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Now take a cold backup of the database and the ORACLE_HOME directory.

$cd /u01/apps/oracle/oradata/

/*
 All my data files , control files and log files are in a directory ora10g
 at the location /u01/apps/oracle/oradata/.
 I am going to make a tar archive of ora10g directory. If these files
 are at separate locations then add all those locations into the tar archive.
 And since this is just a test database and is very small in size so tar archive
 works much better then every thing else. But if it would be a production db and 
 is big in size then I would consider other faster ways to take a cold backup of
 my data files.
*/

$ tar czf /home/oracle/ora10g.tar.gz ora10g

$ echo $ORACLE_HOME
/u01/apps/oracle/product/10.2.0/db_1

/*
 This is Home Directory for Oracle 10g so I would make a tar archive of "db_1" directory.
*/

$ cd /u01/apps/oracle/product/10.2.0/
$ tar czf /home/oracle/oraHomeBackup.tar.gz db_1

$ ls /home/oracle/*.tar.gz
/home/oracle/ora10g.tar.gz  /home/oracle/oraHomeBackup.tar.gz

Configure the system for oracle 11g Release 2 Installation

Now is the time to install oracle 11g R2 in a separate home directory from the oracle 10g.

I would recommend that before going ahead you should have a look at this article, at least the pre-installation configurations.
Since Oracle 10g is already installed on this server so you may find some of the pre-installation configurations already there.
I will go through the adjustments in the pre-installation configurations that I had to make when preparing for 11gR2 install on a server where 10gR2 was already installed.

Open /etc/sysctl.conf and the following lines:

# Oracle settings
fs.aio-max-nr = 1048576
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
net.ipv4.tcp_wmem = 262144 262144 262144
net.ipv4.tcp_rmem = 4194304 4194304 4194304

If any one of these exist already and value is higher, then don't add the new value but if the value is lower remove the already existing value and ad the above.

-- Make the kernel parameters changes effective immediately:
# /sbin/sysctl -p

-- Verify the parameters are changed or not? 
# /sbin/sysctl -a | grep name_of_kernel_parameter -- e.g. shmall

Now we need to make sure we have all necessary packages for Oracle. Most of the rpm packages listed in the article I mentioned above would be installed. However to verify you may go ahead and issue the following command for each of them.

# rpm -qa | grep binutils-2
binutils-2.17.50.0.6-12.el5

It will show you if the package is installed or not.

Now which ever package you find missing install them. Put your Linux Media into DVD and go to the "Server" directory.

cd /dvd_mount_point/Server

-- In my case only following three were missing.
rpm -Uvih numactl-devel-0*
rpm -Uvih unixODBC-2*
rpm -Uvih unixODBC-devel-2*

NOTE: If you are using RHEL5 DVD then you should find them all in the "Server" directory in your DVD. And if you don't find one there you may download it from the Linux vendor's Web site. If you have your Linux distribution in 3 CDs then these will be scattered on all three CDs in the Server directory on all CDs.

In my case the linux user that owns the oracle software installation is oracle. Check for the existence of the groups it needs.

# cat /etc/group | grep dba
dba:x:501:oracle
# cat /etc/group | grep oinstall
oinstall:x:500:
# cat /etc/group | grep oper

-- Group oper doesn't exist. Create it and assign it to the user oracle as secondary group.

groupadd oper
usermod -g oinstall -G dba,oper oracle

Allow the user oracle to use X server, which it will need to run Oracle Universal Installer.

# xhost +SI:localuser:oracle
localuser:oracle being added to access control list

Now switch to the user oracle.

# su - oracle

Create a new directory for 11g Oracle Home under your Oracle Base directory.

$ echo $ORACLE_BASE
/u01/apps/oracle
$ echo $ORACLE_HOME
/u01/apps/oracle/product/10.2.0/db_1
$ 
$ cd /u01/apps/oracle/product
$ mkdir -p 11.2.0/db_1
$ mkdir -p 11.2.0/oraInventory
-- We will use a separate inventory location for 11g installation.

Add these lines into the file ~/.bash_profile

# Oracle 11g Home settings
ORACLE_HOSTNAME=ora10.home.com; export ORACLE_HOSTNAME
ORACLE_BASE=/u01/apps/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=ora10g; export ORACLE_SID

With these changes the ~/.bash_profile should look like this:

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

# Oracle 11g Home settings
#ORACLE_HOSTNAME=ora10.home.com; export ORACLE_HOSTNAME
#ORACLE_BASE=/u01/apps/oracle; export ORACLE_BASE
#ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
#ORACLE_SID=ora10g; export ORACLE_SID

# Oracle 10g Home settings
ORACLE_BASE=/u01/apps/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=ora10g; export ORACLE_SID

# Do not change these.
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
SHLIB_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export SHLIB_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

As you can see I have made two sections in the file. One to setup the Environemnt for Oracle 11g and one for 10g. During the Upgrade we will need to switch back and forth in the 10g and 11g environemnts, and these settings will make it easy for us.
If we want to set the user environemnt to 11g Oracle Home we will edit the file and uncomment 11g setting and comment out the 10g settings and will do the vice versa to set the user environemnt to 10g.

How to set the oracle user environment to 10g:
- Edit the ~/.bash_profile, comment out the 11g settings and uncomment the 10g settings.
- Logout and Login again to bring the changes in effect.

How to set the oracle user environment to 11g:
- Edit the ~/.bash_profile, comment out the 10g settings and uncomment the 11g settings.
- Logout and Login again to bring the changes in effect.

Install Oracle 11g Release 2 software

Open another console and login as user root. Edit the file /etc/oraInst.loc to change the Oracle Inventory location.

The contents of the file should look like this.

inventory_loc=/u01/apps/oracle/product/11.2.0/oraInventory
inst_group=oinstall

Download Oracle 11gR2 software

- For Linux x86 (32-bit)
- For Linux x86_64 (64-bit)

Once download is complete unzip the files that you downloaded.

-- Do this in the console where user oracle is logged in.
$ cd /home/oracle
-- This is where I downloaded the zip files.
$ ls
linux_11gR2_database_1of2.zip  linux_11gR2_database_2of2.zip
$ 
$ unzip linux_11gR2_database_1of2.zip
$ unzip linux_11gR2_database_2of2.zip

Set the oracle user environment to 11g using the way described above.

$ cd /home/oracle/database/
$ ./runInstaller -invPtrLoc /etc/oraInst.loc

12. Once DBCA has finished creating the database, it will show a page like this. It is a summary of the database that has just been created.

Configure Security Updates

This is the first screen you should see when you run Installer. You may leave the email field empty and uncheck the check box that says "I wish to receive security updates via My Oracle Support".

  • Configure Security Updates
  • Email address not specified confirmation

    If you did not provide your email address in the last screen, you will see this confirmation, whether you want to proceed with the installation or not. Press Yes.

  • Email address not specified confirmation
  • Select Installation Option

    Select "Install database software only" and press next.

  • Select Installation Option
  • Node Selection

    Choose if it is a single node installation or a RAC. Select "Single instance database installation".

  • Node Selection
  • Select Product Languages

    Choose the language.

  • Select Product Languages
  • Select Database Edition

    Choose "Enterprise Edition" and press next.

  • Select Database Edition
  • Specify Installation Location

    Provide the ORACLE_BASE and ORACLE_HOME locations for Oracle 11g software.

  • Specify Installation Location
  • Privileged Operating System Groups

    Provide the OSDBA and OSOPER groups.

  • Privileged Operating System Groups
  • Perform Prerequisite Checks

    Oracle will perform all prerequisite checks here and if it finds any thing missing it will show a report other wise it will continue. If you have configured your environment properly before starting the installation all checks should pass.

  • Perform Prerequisite Checks
  • Installation Summary

    If all prerequisite checks are passed in the previous screen then you should see the summary page otherwise another screen will come up telling you which prerequisite check has failed.

  • Installation Summary
  • Install Product

    Sit back and relax until the progress bar goes to 100%.

  • Install Product
  • Execute Configuration Scrips

    This screen suggests the scripts that you need to run as root user.

  • Execute Configuration Scrips
  • Open another console, log in as root and perform run the root.sh script suggested by the Oracle installer.

    # which dbhome
    /usr/local/bin/dbhome
    # cd /usr/local/bin/
    
    # mv dbhome dbhome_10204
    # mv oraenv oraenv_10204
    # mv coraenv coraenv_10204
    
    # /u01/apps/oracle/product/11.2.0/db_1/root.sh
    Running Oracle 11g root.sh script...
    
    The following environment variables are set as:
        ORACLE_OWNER= oracle
        ORACLE_HOME=  /u01/apps/oracle/product/11.2.0/db_1
    
    Enter the full pathname of the local bin directory: [/usr/local/bin]: 
       Copying dbhome to /usr/local/bin ...
       Copying oraenv to /usr/local/bin ...
       Copying coraenv to /usr/local/bin ...
    
    Entries will be added to the /etc/oratab file as needed by
    Database Configuration Assistant when a database is created
    Finished running generic part of root.sh script.
    Now product-specific root actions will be performed.
    Finished product-specific root actions.

    Finish: End of Installation

    Press the close close button to exit out the installer.

  • Finish: End of Installation
  • Run the Pre-Upgrade tool (utlu112i.sql)

    Oracle 11g Release 2 ships a script utlu112i.sql aka Pre-Upgrade tool. This script can be found in the ORACLE_HOME/rdbms/admin directory. This script checks the current database which you are trying to upgrade to 11gR2 and display a report, if there are any changes need to be done before and after the upgrade. Besides its reporting nature, it is mandatory to run before starting the upgrade itself. If you did not run the Pre-Upgrade Information Tool, the catupgrd.sql (upgrade script) script will terminate with one of the following errors:
    ORA-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) again.

    How to run Oracle 11gR2 Pre-Upgrade tool (utlu112i.sql)

    To run the Pre-Upgrade tool the environment should be set like this:

    $ORACLE_HOME = Oracle Home which you are planning to upgrade (Old Oracle Home).
    $ORACLE_SID = SID of the database being upgraded.
    $PATH = should point to the original/old Oracle Home.

    Copy the script utlu112i.sql from 11gR2 ORACLE_HOME/rdbms/admin to another directory say /tmp, change to that directory and start sqlplus. Run the script and view the output.

    $ echo $ORACLE_HOME
    /u01/apps/oracle/product/10.2.0/db_1
    -- Verify that this is the 10g ORACLE_HOME
    $ cd /u01/apps/oracle/product/11.2.0/db_1/rdbms/admin/
    $ cp utlu112i.sql /tmp
    $ cd /tmp
    $ sqlplus / as sysdba
    
    SQL> startup
    
    SQL> spool pre_upgrade.log
    SQL> @utlu112i.sql
    Oracle Database 11.2 Pre-Upgrade Information Tool    07-11-2010 13:20:15
    .
    **********************************************************************
    Database:
    **********************************************************************
    --> name:          ORA10G
    --> version:       10.2.0.4.0
    --> compatible:    10.2.0.1.0
    --> blocksize:     8192
    --> platform:      Linux IA (32-bit)
    --> timezone file: V4
    .
    **********************************************************************
    Tablespaces: [make adjustments in the current environment]
    **********************************************************************
    --> SYSTEM tablespace is adequate for the upgrade.
    .... minimum required size: 741 MB
    .... AUTOEXTEND additional space required: 251 MB
    --> UNDOTBS1 tablespace is adequate for the upgrade.
    .... minimum required size: 425 MB
    --> SYSAUX tablespace is adequate for the upgrade.
    .... minimum required size: 453 MB
    .... AUTOEXTEND additional space required: 213 MB
    --> TEMP tablespace is adequate for the upgrade.
    .... minimum required size: 61 MB
    .... AUTOEXTEND additional space required: 32 MB
    --> EXAMPLE tablespace is adequate for the upgrade.
    .... minimum required size: 69 MB
    .
    **********************************************************************
    Flashback: OFF
    **********************************************************************
    **********************************************************************
    Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
    **********************************************************************
    WARNING: --> "java_pool_size" needs to be increased to at least 64 MB
    .
    **********************************************************************
    Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
    **********************************************************************
    -- No renamed parameters found. No changes are required.
    .
    **********************************************************************
    Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
    **********************************************************************
    --> background_dump_dest         11.1       DEPRECATED   replaced by
    "diagnostic_dest"
    --> user_dump_dest               11.1       DEPRECATED   replaced by
    "diagnostic_dest"
    --> core_dump_dest               11.1       DEPRECATED   replaced by
    "diagnostic_dest"
    .
    **********************************************************************
    Components: [The following database components will be upgraded or installed]
    **********************************************************************
    --> Oracle Catalog Views         [upgrade]  VALID
    --> Oracle Packages and Types    [upgrade]  VALID
    --> JServer JAVA Virtual Machine [upgrade]  VALID
    --> Oracle XDK for Java          [upgrade]  VALID
    --> Oracle Workspace Manager     [upgrade]  VALID
    --> OLAP Analytic Workspace      [upgrade]  VALID
    --> OLAP Catalog                 [upgrade]  VALID
    --> EM Repository                [upgrade]  VALID
    --> Oracle Text                  [upgrade]  VALID
    --> Oracle XML Database          [upgrade]  VALID
    --> Oracle Java Packages         [upgrade]  VALID
    --> Oracle interMedia            [upgrade]  VALID
    --> Spatial                      [upgrade]  VALID
    --> Data Mining                  [upgrade]  VALID
    --> Expression Filter            [upgrade]  VALID
    --> Rule Manager                 [upgrade]  VALID
    --> Oracle OLAP API              [upgrade]  VALID
    .
    **********************************************************************
    Miscellaneous Warnings
    **********************************************************************
    WARNING: --> Database is using a timezone file older than version 11.
    .... After the release migration, it is recommended that DBMS_DST package
    .... be used to upgrade the 10.2.0.4.0 database timezone version
    .... to the latest version which comes with the new release.
    WARNING: --> Database contains schemas with stale optimizer statistics.
    .... Refer to the Upgrade Guide for instructions to update
    .... schema statistics prior to upgrading the database.
    .... Component Schemas with stale statistics:
    ....   SYS
    ....   WMSYS
    ....   OLAPSYS
    ....   SYSMAN
    ....   CTXSYS
    ....   XDB
    ....   MDSYS
    WARNING: --> Database contains INVALID objects prior to upgrade.
    .... The list of invalid SYS/SYSTEM objects was written to
    .... registry$sys_inv_objs.
    .... The list of non-SYS/SYSTEM objects was written to
    .... registry$nonsys_inv_objs.
    .... Use utluiobj.sql after the upgrade to identify any new invalid
    .... objects due to the upgrade.
    .... USER PUBLIC has 1 INVALID objects.
    .... USER SYS has 2 INVALID objects.
    WARNING: --> Database contains schemas with objects dependent on network
    packages.
    .... Refer to the Upgrade Guide for instructions to configure Network ACLs.
    .... USER SCOTT has dependent objects.
    WARNING: --> EM Database Control Repository exists in the database.
    .... Direct downgrade of EM Database Control is not supported. Refer to the
    .... Upgrade Guide for instructions to save the EM data prior to upgrade.
    WARNING:--> recycle bin in use.
    .... Your recycle bin is turned on and it contains
    .... 22 object(s).  It is REQUIRED
    .... that the recycle bin is empty prior to upgrading
    .... your database.
    .... The command:  PURGE DBA_RECYCLEBIN
    .... must be executed immediately prior to executing your upgrade.
    .
    
    PL/SQL procedure successfully completed.
    
    SQL> spool off
    SQL> exit

    Fix the warnings reported by the Pre-Upgrade tool. The warnings about parameters and tablespaces if there are any needs to be done before the database upgrade. As far as the Miscellaneous Warnings are concerned some you have to fix before the upgrade and some after the upgrade.

    Please look at the article Oracle 11g Release 2 Pre Upgrade tool utlu112i.sql to see how to fix warnings and cautions reported by the Pre-Upgrade tool.

    I have fixed the following warnings prior to the upgrade with the help of the link "Oracle 11g Release 2 Pre Upgrade tool utlu112i.sql".

    WARNING: --> Database contains schemas with stale optimizer statistics.
    WARNING: --> EM Database Control Repository exists in the database.
    WARNING:--> recycle bin in use.

    While fixing the warning about EM Database Control Repository, I have started the Oracle listener and EM console. Shut them down to start the Oracle 11g Upgrade process.

    -- Stop the listener
    $ lsnrctl stop
    
    Copyright (c) 1991, 2007, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
    The command completed successfully
    
    -- Stop the EM database console.
    $ emctl stop dbconsole
    TZ set to US/Eastern
    Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0  
    Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
    Stopping Oracle Enterprise Manager 10g Database Control ... 
     ...  Stopped. 
    
    -- Shutdown the database.
    $ sqlplus / as sysdba
    
    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> exit

    Once the database is shutdown log in as sysdba and create a pfile from spfile, that we will ship to the 11g ORACLE_HOME and make the changes suggested by the Pre-Upgrade tool.

    $ sqlplus / as sysdba
    
    Connected to an idle instance.
    
    SQL> create pfile from spfile;
    
    File created.
    
    SQL> exit

    Now copy the newly created pfile (init[SID].ora) and the password file (orapw[SID]) from oracle 10g ORACLE_HOME/dbs to oracle 11g ORACLE_HOME/dbs.

    $ echo $ORACLE_HOME
    /u01/apps/oracle/product/10.2.0/db_1
    $ cd $ORACLE_HOME/dbs
    $ cp initora10g.ora orapwora10g /u01/apps/oracle/product/11.2.0/db_1/dbs/
    $ 
    

    Now edit the newly copied pfile 11g ORACLE_HOME/dbs/init[SID].ora and make adjustments as suggested by the pre upgrade tool. In my case the changes will be:
    - Increase the initialization parameter java_pool_size to at least 64MB
    - Remove the obsolete parameters background_dump_dest, user_dump_dest , core_dump_dest from the parameter file.

    Now open another console, log in as root and edit /etc/oratab to change the oracle home with the sid ora10g to 11g ORACLE_HOME. After the change the /etc/oratab should look like this.

    ora10g:/u01/apps/oracle/product/11.2.0/db_1:N

    Now set the oracle user environment to 11g Oracle Home using the way described above in this article.

    Once the environment is set to 11g ORACLE_HOME, open sqlplus and startup the database in upgrade mode.

    $ echo $ORACLE_HOME
    /u01/apps/oracle/product/11.2.0/db_1
    
    $ cd $ORACLE_HOME/rdbms/admin/
    
    $ sqlplus / as sysdba
    
    Connected to an idle instance.
    
    

    SQL> startup upgrade pfile='/u01/apps/oracle/product/11.2.0/db_1/dbs/initora10g.ora'

    ORACLE instance started. Total System Global Area 606806016 bytes Fixed Size 1338196 bytes Variable Size 201327788 bytes Database Buffers 398458880 bytes Redo Buffers 5681152 bytes Database mounted. Database opened. SQL> purge dba_recyclebin; DBA Recyclebin purged. SQL> spool upgrade.log SQL> @catupgrd.sql . . [output trimmed] . . SQL> /*****************************************************************************/ SQL> /* Step 10 - SHUTDOWN THE DATABASE..!!!!! SQL> */ SQL> /*****************************************************************************/ SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> SQL> SQL> DOC DOC>####################################################################### DOC>####################################################################### DOC> DOC> The above sql script is the final step of the upgrade. Please DOC> review any errors in the spool log file. If there are any errors in DOC> the spool file, consult the Oracle Database Upgrade Guide for DOC> troubleshooting recommendations. DOC> DOC> Next restart for normal operation, and then run utlrp.sql to DOC> recompile any invalid application objects. DOC> DOC>####################################################################### DOC>####################################################################### DOC># SQL> SQL> Rem Set errorlogging off SQL> SET ERRORLOGGING OFF; SQL> SQL> Rem ********************************************************************* SQL> Rem END catupgrd.sql SQL> Rem ********************************************************************* SQL> SQL> SPOOL OFF SQL> exit

    When catupgrd.sql finishes it will automatically shutdown the database. Exit from sqlplus and open the spool file for the upgrade process and see if there were any errors during the upgrade. If you don't see any errors proceed further with the upgrade. If you see errors refer to the link below to trouble shoot them.
    Troubleshoot the Upgrade

    Post upgrade steps: Oracle 11g Release 2

    Create the spfile from the pfile so that the system should use the spfile for next startup.

    $ sqlplus / as sysdba
    
    Connected to an idle instance.
    
    SQL> create spfile from pfile='/u01/apps/oracle/product/11.2.0/db_1/dbs/initora10g.ora';
    
    File created.
    
    SQL> startup
    ORACLE instance started.
    
    Total System Global Area  606806016 bytes
    Fixed Size                  1338196 bytes
    Variable Size             201327788 bytes
    Database Buffers          398458880 bytes
    Redo Buffers                5681152 bytes
    Database mounted.
    Database opened.
    SQL> 
    

    Post-Upgrade Status tool (utlu112s.sql)

    Now run the post upgrade tool to verify the status of the upgrade.
    SQL> spool post_upgrade.log
    SQL> @utlu112s.sql
    .
    Oracle Database 11.2 Post-Upgrade Status Tool           07-11-2010 15:24:48
    .
    Component                                Status         Version  HH:MM:SS
    .
    Oracle Server
    .                                         VALID      11.2.0.1.0  00:16:24
    JServer JAVA Virtual Machine
    .                                         VALID      11.2.0.1.0  00:10:08
    Oracle Workspace Manager
    .                                         VALID      11.2.0.1.0  00:00:37
    OLAP Analytic Workspace
    .                                         VALID      11.2.0.1.0  00:00:30
    OLAP Catalog
    .                                         VALID      11.2.0.1.0  00:01:02
    Oracle OLAP API
    .                                         VALID      11.2.0.1.0  00:00:35
    Oracle Enterprise Manager
    .                                         VALID      11.2.0.1.0  00:08:57
    Oracle XDK
    .                                         VALID      11.2.0.1.0  00:02:35
    Oracle Text
    .                                         VALID      11.2.0.1.0  00:00:54
    Oracle XML Database
    .                                         VALID      11.2.0.1.0  00:04:16
    Oracle Database Java Packages
    .                                         VALID      11.2.0.1.0  00:00:24
    Oracle Multimedia
    .                                         VALID      11.2.0.1.0  00:05:16
    Spatial
    .                                         VALID      11.2.0.1.0  00:05:12
    Oracle Expression Filter
    .                                         VALID      11.2.0.1.0  00:00:12
    Oracle Rule Manager
    .                                         VALID      11.2.0.1.0  00:00:09
    Gathering Statistics
    .                                                                00:06:11
    Total Upgrade Time: 01:03:33
    
    PL/SQL procedure successfully completed.
    
    SQL> SPOOL OFF

    If the Post-Upgrade Status Tool returns errors or shows components that are not VALID or not the most recent release, then see:
    Troubleshoot the Upgrade

    There are some further actions that need to be done, but they do not require the database to be in upgrade mode. Now is the time to perform those action via catuppst.sql script.

    SQL> SPOOL catuppst.log
    SQL> @catuppst.sql
    
    TIMESTAMP
    --------------------------------------------------------------------------------
    COMP_TIMESTAMP POSTUP_BGN 2010-07-11 19:12:43
    
    
    PL/SQL procedure successfully completed.
    
    
    This script will migrate the Baseline data on a pre-11g database
    to the 11g database.
    .
    [output trimmed]
    .
    ...                                       ...
    ... Completed Moving the Baseline Data    ...
    ...                                       ...
    ... If there are no Move BL Data messages ...
    ... above, then there are no renamed      ...
    ... baseline tables in the system.        ...
    ...                                       ...
    .
    [output trimmed]
    .
    ...                                       ...
    ... Completed the Dropping of the         ...
    ... Renamed Baseline Tables               ...
    ...                                       ...
    ... If there are no Drop Table messages   ...
    ... above, then there are no renamed      ...
    ... baseline tables in the system.        ...
    ...                                       ...
    
    PL/SQL procedure successfully completed.
    .
    .
    [output trimmed]
    .
    .
    TIMESTAMP
    --------------------------------------------------------------------------------
    COMP_TIMESTAMP POSTUP_END 2010-07-11 19:12:52
    
    SQL> SPOOL OFF

    Now run the utlrp.sql script to compile the objects which were invalidated during the upgrade.

    SQL> SPOOL utlrp.log
    SQL> @utlrp.sql           
    
    TIMESTAMP
    --------------------------------------------------------------------------------
    COMP_TIMESTAMP UTLRP_BGN  2010-07-11 19:04:10
    
    DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
    DOC>   objects in the database. Recompilation time is proportional to the
    DOC>   number of invalid objects in the database, so this command may take
    DOC>   a long time to execute on a database with a large number of invalid
    DOC>   objects.
    DOC>
    DOC>   Use the following queries to track recompilation progress:
    DOC>
    DOC>   1. Query returning the number of invalid objects remaining. This
    DOC>      number should decrease with time.
    DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
    DOC>
    DOC>   2. Query returning the number of objects compiled so far. This number
    DOC>      should increase with time.
    DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
    DOC>
    DOC>   This script automatically chooses serial or parallel recompilation
    DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
    DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
    DOC>   On RAC, this number is added across all RAC nodes.
    DOC>
    DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
    DOC>   recompilation. Jobs are created without instance affinity so that they
    DOC>   can migrate across RAC nodes. Use the following queries to verify
    DOC>   whether UTL_RECOMP jobs are being created and run correctly:
    DOC>
    DOC>   1. Query showing jobs created by UTL_RECOMP
    DOC>         SELECT job_name FROM dba_scheduler_jobs
    DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
    DOC>
    DOC>   2. Query showing UTL_RECOMP jobs that are running
    DOC>         SELECT job_name FROM dba_scheduler_running_jobs
    DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
    DOC>#
    
    PL/SQL procedure successfully completed.
    
    
    TIMESTAMP
    --------------------------------------------------------------------------------
    COMP_TIMESTAMP UTLRP_END  2010-07-11 19:10:14
    
    DOC> The following query reports the number of objects that have compiled
    DOC> with errors (objects that compile with errors have status set to 3 in
    DOC> obj$). If the number is higher than expected, please examine the error
    DOC> messages reported with each object (using SHOW ERRORS) to see if they
    DOC> point to system misconfiguration or resource constraints that must be
    DOC> fixed before attempting to recompile these objects.
    DOC>#
    
    OBJECTS WITH ERRORS
    -------------------
                      0
    
    DOC> The following query reports the number of errors caught during
    DOC> recompilation. If this number is non-zero, please query the error
    DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
    DOC> are due to misconfiguration or resource constraints that must be
    DOC> fixed before objects can compile successfully.
    DOC>#
    
    ERRORS DURING RECOMPILATION
    ---------------------------
                              0
    
    
    PL/SQL procedure successfully completed.
    
    
    PL/SQL procedure successfully completed.
    
    SQL> SPOOL OFF

    The upgrade is now done. Now is the time to fix the Miscellaneous Warnings that were suggested by the Pre-Upgrade tool as to be done after the upgrade. In my case I need to fix the following:

    WARNING: --> Database is using a timezone file older than version 11.
    WARNING: --> Database contains INVALID objects prior to upgrade.
    WARNING: --> Database contains schemas with objects dependent on network

    Refer to the article Oracle 11g Release 2 Pre Upgrade tool utlu112i.sql to see how to fix these.

    Now change the compatible initialization parameter to 11.2.0.1.0 to use all the features of Oracle 11g Release 2.

    SQL> alter system set compatible='11.2.0.1.0' scope=spfile;
    
    System altered.
    
    SQL> shutdown immediate
    
    SQL> startup
    
    SQL> show parameter compatible
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    compatible                           string      11.2.0.1.0

    Configure Listener for New Oracle 11 g Database in 11g ORACLE_HOME

    Copy the 10g ORACLE_HOME/network/admin/listener.ora to 11g ORACLE_HOME/network/admin, once copied change the ORACLE_HOME in the file to point to 11g HOME whereever required.

    This is how the listener.ora should look like under the 11g ORACLE_HOME/network/admin directory.

    # listener.ora Network Configuration File: /u01/apps/oracle/product/11.2.0/db_1/network/admin/listener.ora
    # Generated by Oracle configuration tools.
    
    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (SID_NAME = PLSExtProc)
          (ORACLE_HOME = /u01/apps/oracle/product/11.2.0/db_1)
          (PROGRAM = extproc)
        )
      )
    
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
          (ADDRESS = (PROTOCOL = TCP)(HOST = ora10.home.com)(PORT = 1521))
        )
      )
    

    Once listener is setup just start the listener.

    $ lsnrctl start
    
    TNSLSNR for Linux: Version 11.2.0.1.0 - Production
    System parameter file is /u01/apps/oracle/product/11.2.0/db_1/network/admin/listener.ora
    Log messages written to /u01/apps/oracle/diag/tnslsnr/ora10/listener/alert/log.xml
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora10.home.com)(PORT=1521)))
    .
    [output trimmed]
    .
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora10.home.com)(PORT=1521)))
    Services Summary...
    Service "PLSExtProc" has 1 instance(s).
      Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully

    The listener is just started and shortly it will register the SID ora10g.

    $ lsnrctl status
    
    Copyright (c) 1991, 2009, Oracle.  All rights reserved.
    .
    [output trimmed]
    .
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora10.home.com)(PORT=1521)))
    Services Summary...
    Service "PLSExtProc" has 1 instance(s).
      Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Service "ora10g" has 1 instance(s).
      Instance "ora10g", status READY, has 1 handler(s) for this service...
    Service "ora10gXDB" has 1 instance(s).
      Instance "ora10g", status READY, has 1 handler(s) for this service...
    The command completed successfully

    ora10g which is an upgraded database is now registered with the LISTENER.

    Upgrade Enterprise Manager Repository to 11g

    The enterprise manager is unusable yet. You will have to upgrade it too from 10g to 11g and then you may use it.

    $ export ORACLE_UNQNAME=ora10g
    $ emctl start dbconsole
    OC4J Configuration issue. 
    /u01/apps/oracle/product/11.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_ora10.home.com_ora10g 
    not found. 
    -- As you see the dbconsole cannot start.
    
    $ emca -upgrade db
    
    STARTED EMCA at Jul 11, 2010 3:35:03 PM
    EM Configuration Assistant, Version 11.2.0.0.2 Production
    Copyright (c) 2003, 2005, Oracle.  All rights reserved.
    
    Enter the following information:
    ORACLE_HOME for the database to be upgraded: /u01/apps/oracle/product/10.2.0/db_1
    Database SID: ora10g
    Listener port number: 1521
    Password for SYS user:  
    
    Do you wish to continue? [yes(Y)/no(N)]: Y
    Jul 11, 2010 3:35:36 PM oracle.sysman.emcp.EMConfig perform
    INFO: This operation is being logged at /u01/apps/oracle/cfgtoollogs/emca/ora10g/emca_2010_07_11_15_35_01.log.
    Jul 11, 2010 3:35:36 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
    INFO: Stopping Database Control (this may take a while) ...
    Jul 11, 2010 3:35:44 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
    INFO: Uploading configuration data to EM repository (this may take a while) ...
    Jul 11, 2010 3:36:34 PM oracle.sysman.emcp.EMReposConfig invoke
    INFO: Uploaded configuration data successfully
    Jul 11, 2010 3:36:51 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
    INFO: Software library configured successfully.
    Jul 11, 2010 3:36:51 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
    INFO: Deploying Provisioning archives ...
    Jul 11, 2010 3:37:23 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
    INFO: Provisioning archives deployed successfully.
    Jul 11, 2010 3:37:23 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
    INFO: Securing Database Control (this may take a while) ...
    Jul 11, 2010 3:38:02 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
    INFO: Database Control secured successfully.
    Jul 11, 2010 3:38:02 PM oracle.sysman.emcp.util.DBControlUtil startOMS
    INFO: Starting Database Control (this may take a while) ...
    Jul 11, 2010 3:39:14 PM oracle.sysman.emcp.EMDBPostConfig performUpgrade
    INFO: Database Control started successfully
    Jul 11, 2010 3:39:22 PM oracle.sysman.emcp.EMDBPostConfig performUpgrade
    INFO: >>>>>>>>>>> The Database Control URL is https://ora10.home.com:1158/em <<<<<<<<<<<
    Jul 11, 2010 3:39:22 PM oracle.sysman.emcp.EMDBPostConfig invoke
    WARNING: 
    ************************  WARNING  ************************
    
    Management Repository has been placed in secure mode wherein Enterprise Manager data will 
    be encrypted.  The encryption key has been placed in the file: 
    /u01/apps/oracle/product/11.2.0/db_1/ora10.home.com_ora10g/sysman/config/emkey.ora.
    Please ensure this file is backed up as the encrypted data will become unusable if this file
    is lost. 
    
    ***********************************************************
    Enterprise Manager configuration completed successfully
    FINISHED EMCA at Jul 11, 2010 3:39:22 PM

    Lets now check the status of the dbconsole.

    $ emctl status dbconsole
    Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0 
    Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
    Oracle Enterprise Manager 11g is running. 
    ------------------------------------------------------------------
    Logs are generated in directory /u01/apps/oracle/product/11.2.0/db_1/ora10.home.com_ora10g/sysman/log 
    $