Upgradation – Patch set apply
Upgradation will change the database minor release from one version to another.
Ex: major.minor.build.revision.release
10.2.0.1.0 — 10.2.0.5.0
11.2.0.1.0 — 10.2.0.4.0
Go to oracle website — Choose type as patchset — Search and download.
Ex: p120205_linux_x86.zip ~1000 MB
Why do we need to do an upgrade?
When there is a product bug, we need to do an upgrade, since there is no proper solution, there may be a workaround.
Ex: ORA-006000 – internal error
ORA – 7445
Pre-upgrade checks and tasks
- Stop listener
- Down the database
- $ echo $ORACLE_HOME – Note down it
- Shared_pool, Large_pool & jave_pool – should be > 128 MB
- System, sysAux, Undo, Temporary tablespace – Should be “Auto extent ON”
$ cd /opt
$ unzip p120205_linux_x86.zip
$ ls
Disk1
$cd disk1
It should be applied in both places
- Oracle binaries
- Database
$./ runinstaller
Next –Enter oracle home path
— Next –next — Finish.
(It will take 45 minutes)
Verification:
$ sqlplus –v
10.2.0.5.0
Database upgrade
It has two methods 1. DBUA 2. Manual
Mostly, we will go for manual, since it will run quickly.
GUI
$ dbua
— Next — select Dbname –Next — Next — finish.
(It will take 3 to 5 hours)
Manual
$ export ORACLE_SID = Muthu
Sql> startup upgrade
Sql> @?/rdbms/admin/catupgrade.sql
(It will take 45 minutes)
Internally it will drop all system objects and recreate with the newer version.
Sql> shutdown immediate
Sql> startup
Sql> @?/rdmbs/admin/utlrp.sql
This script will change and recompile all the invalid objects to valid.
(5 to 10 minutes)
Validate
Sql> select comp_id,version,status from dba_registory;
Results:
Catalog 10.2.0.5.0 valid
Catapro 10.2.0.5.0 valid … etc
Note: The Above method is upto 11.1.0.7.0
From 11g r2 – 11.2.0.1.0 onwards
No oracle software upgrade
Database upgrade only
Then, How to upgrade without binary update.
We need to create one more new Oracle home
Ex:
11g R2: 11.2.0.1.0 – /opt/oracle/product/11.2.0.1.0/dbhome
11g R2: 11.2.0.4.0 – /opt/oracle/product/11.2.0.4.0/dbhome_1
So we cannot upgrade the oracle binary, but we can create and upgrade as a fresh installation.
Manual
Copy the S/W into new OH.
$ export ORACLE_HOME =/opt/oracle/product/11.2.0.4/db_home_1
$ export PATH = $ORACLE_HOME/bin:$PATH
$ export ORACLE_SID = Muthu
To make this permeant and best practice, Change this into bash_profile.
Remaining all are same as prior to 11g R2.
Sql> startup upgrade
Sql> @?/rdbms/admin/catupgrade.sql
(It will take 45 minutes)
Internally it will drop all system objects and recreate with the newer version.
Sql> shutdown immediate
Sql> startup
Sql> @?/rdmbs/admin/utlrp.sql
This script will change and recompile all the invalid objects to valid.
(5 to 10 minutes)
Validate
Sql> select comp_id,version,status from dba_registory;
Results:
Catalog 10.2.0.5.0 valid
Catapro 10.2.0.5.0 valid … etc
GUI
$ dbua
Next — Next — Finish
Note: Only minor changes can be done in upgradation. Major changes can only be done in migration.
http://www.oracleflash.com/33/Oracle-11g-Release-2-Pre-Upgrade-tool-utlu112i-sql.html#runpreupgrade
http://www.dbas-oracle.com/2011/06/step-by-step-manual-database-upgrade-to.html
https://dbaprakash.wordpress.com/category/patching-and-upgradation/
