Upgrading Oracle 10g to Oracle 11g

Upgrade Oracle 10g Release 2 to Oracle 11g Release 1
Thanks to Author http://www.oracleflash.com having such good technical information sharing

Here I’m try to brief upgrade process and Hands On purpose

1. Preparing for upgrade

-- stop isqlplus
$ isqlplusctl stop

-- stop enterprise manager dbconsole
$ emctl stop dbconsole

-- stop listener
$ lsnrctl stop

-- Shutdown the database.
$ sqlplus / as sysdba

SQL> shutdown immediate

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

1- Perform Cold Backup
tar czf u01.tar.gz /u01

(or)

2- Take a backup using RMAN

Connect to RMAN:

rman "target / nocatalog"

RUN
{
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT '%U' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE TO '';
}

2. Configure the system for oracle 11g and Installation

Open /etc/sysctl.conf and the following lines:
# Oracle settings
fs.file-max = 65536
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65500
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

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

--  make sure we have all necessary packages for Oracle
# rpm -qa | grep binutils-2
binutils-2.17.50.0.6-12.el5

cd /dvd_mount_point/Server

-- In my case only following three were missing.
rpm -Uvih libaio-devel-0*`uname -p`*
rpm -Uvih numactl-devel-0*`uname -p`*
rpm -Uvih unixODBC-2*`uname -p`*
rpm -Uvih unixODBC-2*i386*
rpm -Uvih unixODBC-devel-2*`uname -p`*

--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/app/oracle
$ echo $ORACLE_HOME
/u01/app/oracle/product/10.2.0/db_1
$
$ cd /u01/app/oracle/product
$ mkdir -p 11.1.0/db_1
$ mkdir -p 11.1.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_BASE=/u01/app/oracle; export ORACLE_BASE
#ORACLE_HOME=$ORACLE_BASE/product/11.1.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

# Oracle settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

# If /tmp doesn't have 400mb space free then you can workaround it
# by pointing the variables TMP AND TMPDIR to a location where you
# have sufficient space. Oracle will then use this directory for
# temporary files.

# Oracle 11g Home settings
#ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
#ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1; export ORACLE_HOME
#ORACLE_SID=ora10g; export ORACLE_SID
# Oracle 10g Home settings
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=ora10g; export ORACLE_SID
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
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
export CLASSPATH

if [ $USER = "oracle" ]; then
      if [ $SHELL = "/bin/ksh" ]; then
            ulimit -p 16384
            ulimit -n 65536
      else
            ulimit -u 16384 -n 65536
      fi
fi

--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.

---Edit the file /etc/oraInst.loc to change the Oracle Inventory location.
inventory_loc=/u01/app/oracle/product/11.1.0/oraInventory
inst_group=oinstall

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

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

Choose "Install Software Only"

—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/app/oracle/product/11.1.0/db_1/root.sh
Running Oracle 11g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/product/11.1.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.
You have mail in /var/spool/mail/root

3. Pre-Upgrade tool (utlu111i.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 utlu111i.sql from 11gR1 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/app/oracle/product/10.2.0/db_1
-- Verify that this is the 10g ORACLE_HOME
$ cd /u01/app/oracle/product/11.1.0/db_1/rdbms/admin/
$ cp utlu111i.sql /tmp
$ cd /tmp
$ sqlplus / as sysdba

SQL> startup

SQL> spool pre_upgrade.log
SQL> @utlu111i.sql
Oracle Database 11.1 Pre-Upgrade Information Tool    07-28-2010 17:53:06
.
**********************************************************************
Database:
**********************************************************************
--> name:          ORA10G
--> version:       10.2.0.4.0
--> compatible:    10.2.0.1.0
--> blocksize:     8192
--> platform:      Linux x86 64-bit
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 742 MB
.... AUTOEXTEND additional space required: 242 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 440 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 421 MB
.... AUTOEXTEND additional space required: 181 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
.
**********************************************************************
Update Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
WARNING: --> "sga_target" needs to be increased to at least 672 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
--> "background_dump_dest" replaced by  "diagnostic_dest"
--> "user_dump_dest" replaced by  "diagnostic_dest"
--> "core_dump_dest" 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 contains stale optimizer statistics.
.... Refer to the 11g Upgrade Guide for instructions to update
.... 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.
.... 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 11g Upgrade Guide for instructions to configure Network ACLs.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... 11g Upgrade Guide for instructions to save the EM data prior to 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 1 Pre Upgrade tool utlu111i.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 1 Pre Upgrade tool utlu111i.sql.

Now log in as sysdba and change the initialization parameters as suggested by the Pre-Upgrade Tool. Once done, shutdown the database and create a pfile from spfile, that we will ship to the 11g ORACLE_HOME.

$ sqlplus / as sysdba

SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 572M

SQL> show parameter sga_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 572M

-- change sga_max_size to 700m and bounce the database.

SQL> alter system set sga_max_size=700m scope=spfile;

System altered.

SQL> shutdown immediate

SQL> startup

-- Change the sga_target to 700m now.

SQL> alter system set sga_target=700m scope=both;

System altered.

-- remove *_dump_dest parameters from spfile.

SQL> alter system reset background_dump_dest scope=spfile sid='*';

System altered.

SQL> alter system reset core_dump_dest scope=spfile sid='*';

System altered.

SQL> alter system reset user_dump_dest scope=spfile sid='*';

System altered.

-- Shutdown the database and create pfile from spfile.

SQL> shutdown immediate

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/app/oracle/product/10.1.0/db_1
$ cd $ORACLE_HOME/dbs
$ cp initora10g.ora orapwora10g /u01/app/oracle/product/11.1.0/db_1/dbs/
$

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/app/oracle/product/11.1.0/db_1:N

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

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

$ echo $ORACLE_HOME
/u01/app/oracle/product/11.1.0/db_1

$ cd $ORACLE_HOME/rdbms/admin/

$ sqlplus / as sysdba

Connected to an idle instance.

SQL> startup upgrade pfile='/u01/app/oracle/product/11.1.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> 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>
SQL>
SQL>
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/app/oracle/product/11.1.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>

5. Post-Upgrade Status tool (utlu111s.sql)

 Now run the post upgrade tool to verify the status of the upgrade.
SQL> spool post_upgrade.log
SQL> @utlu111s.sql
.
Oracle Database 11.1 Post-Upgrade Status Tool           07-28-2010 20:29:10
.
Component                                Status         Version  HH:MM:SS
.
Oracle Server
.                                         VALID      11.1.0.6.0  00:17:33
JServer JAVA Virtual Machine
.                                         VALID      11.1.0.6.0  00:09:05
Oracle Workspace Manager
.                                         VALID      10.2.0.4.3  00:00:01
OLAP Analytic Workspace
.                                         VALID      11.1.0.6.0  00:00:24
OLAP Catalog
.                                         VALID      11.1.0.6.0  00:00:55
Oracle OLAP API
.                                         VALID      11.1.0.6.0  00:00:24
Oracle Enterprise Manager
.   ORA-06550: line 5, column 35:
.   PL/SQL: ORA-00942: table or view does not exist
.   ORA-06550: line 5, column 1:
.   PL/SQL: SQL Statement ignored
.   ORA-00001: unique constraint (SYSMAN.PARAMETERS_PRIMARY_KEY) violated
.   ORA-06512: at "SYSMAN.MGMT_TIME_SYNC", line 108
.   ORA-06512: at "SYSMAN.MGMT_TIME_SYNC", line 166
.   ORA-06512: at line 2
.                                         VALID      11.1.0.6.0  00:08:48
Oracle XDK
.                                         VALID      11.1.0.6.0  00:00:41
Oracle Text
.                                         VALID      11.1.0.6.0  00:00:50
Oracle XML Database
.                                         VALID      11.1.0.6.0  00:03:48
Oracle Database Java Packages
.                                         VALID      11.1.0.6.0  00:00:17
Oracle Multimedia
.                                         VALID      11.1.0.6.0  00:03:20
Spatial
.                                         VALID      11.1.0.6.0  00:04:13
Oracle Expression Filter
.                                         VALID      11.1.0.6.0  00:00:11
Oracle Rule Manager
.                                         VALID      11.1.0.6.0  00:00:09
Gathering Statistics
.                                                                00:03:59
Total Upgrade Time: 00:54:50

PL/SQL procedure successfully completed.

SQL> SPOOL OFF

The following errors will arise if you are upgrading from Oracle 10.2.0.4:

. ORA-06550: line 5, column 35:
. PL/SQL: ORA-00942: table or view does not exist
. ORA-06550: line 5, column 1:
. PL/SQL: SQL Statement ignored
. ORA-00001: unique constraint (SYSMAN.PARAMETERS_PRIMARY_KEY) violated
. ORA-06512: at “SYSMAN.MGMT_TIME_SYNC”, line 108
. ORA-06512: at “SYSMAN.MGMT_TIME_SYNC”, line 166
. ORA-06512: at line 2

These errors do not result in any data loss. Therefore, you can ignore these errors.

This issue is tracked with Oracle bug 6705429.
See Metalink Note: 559255.1

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

Troubleshoot the Upgrade

6.catuppst.sql

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-28 20:44:04

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.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END 2010-07-28 20:44:11

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-28 20:46:23
.
.
[output trimmed]
.
.
PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2010-07-28 20:51:09

PL/SQL procedure successfully completed.

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
-------------------
                  1

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

SQL> SELECT count(*) FROM dba_invalid_objects;

  COUNT(*)
----------
         1

SQL> select owner , object_name FROM dba_invalid_objects;

OWNER     OBJECT_NAME
--------- ---------------------------
SH        FWEEK_PSCAT_SALES_MV

/*
   Even after running utlrp.sql if you see objects invalid in dba_invalid_objects, then
   review those objects and see why they are invalid and try to fix them.
   In my case this is a materialized view in SH schema that is invalid. Since SH is not
   a schema used by my application and is an Oracle sample schema so I don't care
   about it.
*/

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 contains schemas with objects dependent on network

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

7. Now change the compatible initialization parameter to 11.1.0.6.0 to use all the features of Oracle 11g Release 1.

SQL> alter system set compatible='11.1.0.6.0' scope=spfile;

System altered.

SQL> shutdown immediate

SQL> startup

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.1.0.6.0

8. Configure Listener for New Oracle 11g 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/app/oracle/product/11.1.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/app/oracle/product/11.1.0/db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora11gR1.home.com)(PORT = 1521))
    )
  )

Once listener is setup just start the listener.

$ lsnrctl start

TNSLSNR for Linux: Version 11.1.0.6.0 - Production
System parameter file is /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ora11gR1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora11gR1.home.com)(PORT=1521)))

.
[output trimmed]
.
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora11gR1.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, 2007, Oracle.  All rights reserved.
.
[output trimmed]
.
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora11gR1.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.home.com" has 1 instance(s).
  Instance "ora10g", status READY, has 1 handler(s) for this service...
Service "ora10gXDB.home.com" has 1 instance(s).
  Instance "ora10g", status READY, has 1 handler(s) for this service...
Service "ora10g_XPT.home.com" 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.

9. 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
[oracle@ora11gR1 admin]$ emctl start dbconsole
OC4J Configuration issue.
/u01/app/oracle/product/11.1.0/db_1/oc4j/j2ee/OC4J_DBConsole_ora11gR1.home.com_ora10g
not found.

-- As you see the dbconsole cannot start.

$ emca -upgrade db

STARTED EMCA at Jul 28, 2010 9:14:23 PM
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Jul 28, 2010 9:14:23 PM oracle.sysman.emcp.util.GeneralUtil initSQLEngine
SEVERE: No SID specified
ORACLE_HOME for the database to be upgraded: /u01/app/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 28, 2010 9:15:01 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/ora10g/emca_2010_07_28_21_14_23.log.
Jul 28, 2010 9:15:01 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Jul 28, 2010 9:15:13 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Jul 28, 2010 9:16:09 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Jul 28, 2010 9:16:32 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Jul 28, 2010 9:16:32 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Jul 28, 2010 9:17:12 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Jul 28, 2010 9:17:12 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Jul 28, 2010 9:17:25 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Jul 28, 2010 9:17:25 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Jul 28, 2010 9:19:04 PM oracle.sysman.emcp.EMDBPostConfig performUpgrade
INFO: Database Control started successfully
Jul 28, 2010 9:19:08 PM oracle.sysman.emcp.EMDBPostConfig performUpgrade
INFO: >>>>>>>>>>> The Database Control URL is https://ora11gR1.home.com:1158/em <<<<<<<<<<<
Jul 28, 2010 9:19:08 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/app/oracle/product/11.1.0/db_1/ora11gR1.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 28, 2010 9:19:08 PM

Lets now check the status of the dbconsole.

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

10. At last lets check the status of the oracle components upgraded.

SQL> column comp_name format a40
SQL> column version format a15
SQL> column status format a7
SQL> select comp_name , version , status from dba_registry;

COMP_NAME                                VERSION         STATUS
---------------------------------------- --------------- -------
Oracle Enterprise Manager                11.1.0.6.0      VALID
OLAP Catalog                             11.1.0.6.0      VALID
Spatial                                  11.1.0.6.0      VALID
Oracle Multimedia                        11.1.0.6.0      VALID
Oracle XML Database                      11.1.0.6.0      VALID
Oracle Text                              11.1.0.6.0      VALID
Oracle Data Mining                       11.1.0.6.0      VALID
Oracle Expression Filter                 11.1.0.6.0      VALID
Oracle Rule Manager                      11.1.0.6.0      VALID
Oracle Workspace Manager                 10.2.0.4.3      VALID
Oracle Database Catalog Views            11.1.0.6.0      VALID
Oracle Database Packages and Types       11.1.0.6.0      VALID
JServer JAVA Virtual Machine             11.1.0.6.0      VALID
Oracle XDK                               11.1.0.6.0      VALID
Oracle Database Java Packages            11.1.0.6.0      VALID
OLAP Analytic Workspace                  11.1.0.6.0      VALID
Oracle OLAP API                          11.1.0.6.0      VALID

17 rows selected.

If they are all VALID with 11.1.0.xxx, the upgrade was successful. 🙂

Manual Database Upgrade Oracle 10.2.0.4 database software to Oracle 11.2.0.2

http://www.dbas-oracle.com/2011/06/step-by-step-manual-database-upgrade-to.html

Upgrade 10.2.0.1.0 to Oracle Database 11g – Beta 6 (11.1.0.6)
http://samadhandba.wordpress.com/2011/02/24/step-by-step-upgrading-oracle-10g-to-oracle-11g/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.