Oracle

Oracle Upgradation Patch set apply-23

Advertisements

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

  1. Stop listener
  2. Down the database
  3. $ echo $ORACLE_HOME – Note down it
  4. Shared_pool, Large_pool & jave_pool – should be > 128 MB
  5. 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

  1. Oracle binaries
  2. 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/

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: