Upgrade Oracle Database from 10g to 11g

Upgrade Oracle Database from 10g to 11g

Upgrade Oracle Database from 10g Release 2 to Oracle 11g Release 1

To Upgrade Oracle  Database from 10g to 11g, you should be on 10.2.0.4, so to install 10.2.0.4 patch set level follow the link

Upgrade Oracle Database from 10.2.0.1 to 10.2.0.4

This article is a step by step guide to upgrade oracle database 10gR2 (10.2.0.4) to 11gR1 (11.1.0.6). The current environment is OEL 4.7

1)Preparing for upgrade to 11g Release 1

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

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

$cd /u01/app/oracle/oradata/
$ tar -czf /home/oracle/PRODbackup.tar.gz PROD

$ echo $ORACLE_HOME
/u01/app/oracle/product/10.2.0

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

$ cd /u01/app/oracle/product/
$ tar czf /home/oracle/oraHomeBackup.tar.gz 10.2.0
$ ls /home/oracle/*.tar.gz
/home/oracle/PRODbackup.tar.gz /home/oracle/oraHomeBackup.tar.gz

2)Configure the system for oracle 11g Release 1 Installation

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

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 11gR1 install on a server where 10gR2 was
already installed.

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

If any one of these exist already and value is higher then or equal to the above, then don’t add the new value but if the value is lower remove the already existing value and add 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.– /sbin/sysctl -a | grep shmall

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

rpm -qa | grep binutils-2
rpm -qa | grep compat-libstdc++-33*
rpm -qa | grep elfutils-libelf-0*
rpm -qa | grep elfutils-libelf-devel-0*
rpm -qa | grep glibc-2*`uname -p`*
rpm -qa | grep glibc-common-2*
rpm -qa | grep glibc-devel-2*
rpm -qa | grep glibc-headers-2*
rpm -qa | grep gcc-4*
rpm -qa | grep gcc-c++-4*
rpm -qa | grep libaio-0*
rpm -qa | grep libaio-devel-0*
rpm -qa | grep libgcc-4*
rpm -qa | grep libstdc++-4*
rpm -qa | grep libstdc++-devel-4*
rpm -qa | grep make-3*
rpm -qa | grep numactl-devel-0*
rpm -qa | grep sysstat-7*
rpm -qa | grep unixODBC-2*
rpm -qa | grep unixODBC-devel-2*

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 /media/cdrom/Enterprise/RPMS

and install all the missing packages.  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:500:
# cat /etc/group | grep oinstall
oinstall:x:501:oracle
# 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.

run as a root user in new terminal

# xhost +

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/

$ cd /u01/app/oracle/product
$ mkdir -p 11.1.0/
$ 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; export ORACLE_HOME
#ORACLE_SID=PROD; export ORACLE_SID

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
unset USERNAME
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

# Oracle 11g Home settings
#ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
#ORACLE_HOME=$ORACLE_BASE/product/11.1.0; export ORACLE_HOME
#ORACLE_SID=PROD; export ORACLE_SID

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/; export ORACLE_HOME
ORACLE_SID=PROD; 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
#LD_ASSUME_KERNEL=2.4.1; export LD_ASSUME_KERNEL

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

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 1 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/app/oracle/product/11.1.0/oraInventory
inst_group=dba

3)Download Oracle 11gR1 software

You will have to login to your OTN web account first.

-Download Linux x86 (32-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 *.zip
p6810189_10204_Linux-x86.zip

$ unzip p6810189_10204_Linux-x86.zip

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

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

5)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

errors in prereq check

#grep SwapTotal /proc/meminfo

#dd if=/dev/zero of=/extraswap bs=1M count=10240
#mkswap /extraswap
#cp /etc/fstab /etc/fstab.mybackup
#cat /etc/fstab

#swapoff -a
#swapon -a

6)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

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)

7)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:

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/app/oracle/product/10.2.0/
— Verify that this is the 10g ORACLE_HOME

$ cd /u01/app/oracle/product/11.2.0/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 01-22-2013 23:19:55
.
**********************************************************************
Database:
**********************************************************************
–> name: PROD
–> 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: 241 MB
–> UNDOTBS1 tablespace is adequate for the upgrade.
…. minimum required size: 404 MB
–> SYSAUX tablespace is adequate for the upgrade.
…. minimum required size: 472 MB
…. AUTOEXTEND additional space required: 202 MB
–> TEMP tablespace is adequate for the upgrade.
…. minimum required size: 61 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.
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
…. 53 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.

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

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

SQL>ALTER DATABASE DATAFILE 1 RESIZE 800m;

SQL>ALTER DATABASE DATAFILE 3 RESIZE 500m;

SQL>alter system set java_pool_size = 70m scope = both;

SQL>PURGE DBA_RECYCLEBIN;

SQL>select count(*) from dba_objects where status=’INVALID’;

COUNT(*)
———-
3

SQL> select OBJECT_NAME,OWNER from dba_objects where status=’INVALID’;

OBJECT_NAME
——————————————————————————–
OWNER
——————————
DBMS_REGISTRY
SYS

DBA_REGISTRY_DATABASE
SYS

DBA_REGISTRY_DATABASE
PUBLIC

These invalid objects has to be compiled after the upgradation.

— Stop the listener
$ lsnrctl stop

— Shutdown the database.
$ sqlplus / as sysdba

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

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

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.2.0
$ cd $ORACLE_HOME/dbs
$ cp initPROD.ora orapwPROD /u01/app/oracle/product/11.1.0/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:
– 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.

PROD:/u01/app/oracle/product/10.2.0:N to PROD:/u01/app/oracle/product/11.1.0: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/app/oracle/product/11.1.0/

$ cd $ORACLE_HOME/rdbms/admin/

$ sqlplus / as sysdba

SQL> startup upgrade pfile=’/u01/app/oracle/product/11.1.0/dbs/initPROD.ora’

ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name ‘PROD’
we have to create listener.ora,tnsnames.ora & sqlnet.ora manually using netca.

and startup upgrade again with the same statement

SQL> startup upgrade pfile=’/u01/app/oracle/product/11.1.0/dbs/initPROD.ora’

SQL> spool upgrade.log

SQL> @catupgrd.sql

Archive logs will be generated more because it will update tables in database so db_recovery_file_dest_size will be filled, so increase the destination
size using below command & run again scripts

SQL>@catupgrd.sql

SQL>alter system set db_recovery_file_dest_size = 7G scope=memory;

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

8)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/dbs/initPROD.ora’;
SQL> startup
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1337720 bytes
Variable Size 234882696 bytes
Database Buffers 293601280 bytes
Redo Buffers 5840896 bytes
Database mounted.
Database opened.

9)Post-Upgrade Status tool (utlu112s.sql)

SQL> spool post_upgrade.log
SQL> @utlu112s.sql

SQL>spool off

Post-Upgrade Status Tool must shows all the components VALID or else you have to troubleshoot it & run again the @utlu112s.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 2013-01-23 17:27:25
PL/SQL procedure successfully completed.
This script will migrate the Baseline data on a pre-11g database
to the 11g database.

Move BL Data SYS.WRH$_FILESTATXS
Move BL Data SYS.WRH$_SQLSTAT
……..
.
.
.
Commit complete.
PL/SQL procedure successfully completed.

TIMESTAMP

——————————————————————————–
COMP_TIMESTAMP POSTUP_END 2013-01-23 17:27:31

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

SQL>SPOOL OFF

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;

SQL> shutdown immediate

SQL> startup

SQL> show parameter compatible

 

 

Nagulu Polagani

"We are all apprentices in a craft where no one ever becomes a master."