Upgrade Database from Oracle 10.2.0.4 To 11gR1

We cannot upgrade Oracle software from 10g to 11g like upgrading 10.2.0.1.0 to 10.2.0.4.0 , because it is not a patchset. 

So here we need to upgrade database from 10g to 11g. 

below steps are for upgrading database from 10g to11g

Assume that we have already installed Oracle 10g s/w in our system with database PROD.

Oracle 10g home -->/u01/Oracle/product/10.2.0/db_1

(here Oracle 10g os  user is oracle)

then

Step1: we have to download Oracle 11gR1 software from metalink site using username and password.

 

unzip that Oracle 11g r1 s/w

Unzip linux_11gr1.zip

Then it generates database directory.  Then we have to change the ownership of that s/w or set the appropriate permissions to our user

Chown –R oracle:dba /Oracle11g/database

Before installing software ,we need to check prerequisites for installing Oracle 11g. so we need to verify the installation guide

-->we have to check the hardware requirements

-->we need to create appropriate directories

-->we need to check kernel parameters

xhost +

Then  su – oracle

Cd /database

./runInstaller

We need to select directory where to install s/w and install s/w only without database

-->assume that oracle11g home --> /u01/Oracle/product/11.1.0/db_1

After successful completion of installation

Step 2: Prerequisites for Oracle 10g to 11g Upgradation

In Oracle 11g home --> /uo1/Oracle/product/11.1.0/db_1/rdbms/admin/

There is one script i.e., utlu111i.sql(which is pre upgrade information tool)

Then run that script from Oracle 10g database

Export ORACLE_HOME=/u01/Oracle/product/10.2.0/db_1

Export ORACLE_SID=PROD

Export PATH=/u01/Oracle/product/10.2.0/db_1/bin:$PATH

After exporting env variables

-->sqlplus ‘/as sysdba’

Startup

@u01/Oracle/product/11.1.0/db_1/rdbms/admin/utlu111i.sql

Then it generates the pre upgrade information tool.. inorder to upgrade the database we need to solve those warnings which are generated after running the preupgrade information tool

Here after running utlu111i.sql script I got some warnings 

Warning: Data Timezon version error

Means if the data timezone version is <4 at that time only this timezone warning is generated.

So to solve this issue we need to download utltzpv4.sql from metalink

And run that script

Sql>@utltzpv4.sql

Warning: Obsolete Parameters

Background_dump_dest -à diagnostic_dest

User_dump_dest-àdiagnostic_dest

Core_dump_destàdiagnostic_dest

To solve this problem we need to remove those adump,bdump,cdump and udump from the parameter file and replace with single diagnostic_dest

Warning: Database contains stale optimizer statistics

Component schemas with stale statistics..

SYS

SYSMAN

To solve this problem

Connect as sys user and gather statistics

Conn /as sysdba

Connected

Sql> Exec DBMS_STATS.GATHER_DICTIONARY_STATS;

Sql>Exec DBMS_STATS.GATHER_SCHEMA_STATS(‘SYS);

Sql>Exec DBMS_STATS.GATHER_SCHEMA_STATS(‘SYSMAN’);

Warning: Invalid objects

To solve this issue run the utlrp.sql (recompile)

Sql>@?/rdbms/admin/utlrp.sql

It recompiles the objects

To verify that there is invalid objects are not

Sql>select count(*) from dba_object where status = ‘INVALID’

These are some warnings. We need to solve those problems to perform upgradation. Because it reduces the time when upgrading.

Step 3: 

After successful completion of prerequisites of upgradation.

We need to copy the parameter file (pfile ) of Oracle 10 database prod to Oracle 11g HOME/dbs location

And edit that pfile

In pfile

Db_compatible = ’11.1.0’

Remove adump,bdump,cdump,udump 

Replace with diagnostic_dest=’/u01/Oracle/product/diagns’

Export ORACLE_HOME=/u01/Oracle/product/11.1.0/db_1

Export ORACLE_SID=PROD

Export PATH=/u01/Oracle/product/11.1.0/db_1/bin:$PATH

After exporting the env variables

Sqlplus ‘/as sysdba’

Sql>startup upgrade;

Sql>@?/rdbms/admin/catupgrd.sql

This script upgrades the database it takes lot of time

For me it takes 2 hours minutes because my database is moderate in size so it takes 2 hours

After successfully completion of this script

Sql>@?/rdbms/admin/utlu111s.sql

This script shows the post upgrade status

 It shows the entire info after upgradations and how much time it takes to upgrade the database

Sql>@?/rdbms/admin/catuppst.sql

Sql>@?/rdbms/admin/utlrp.sql

Sql>select comp_name, version, status from sys.dba_registry;

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

Sql>select * from v$version;