Oracle Database Upgrade step by step 11gR1 to 11gR2

Oracle Database Upgrade Process

1) Must install the new Oracle version in separate ORACLE_HOME to which we are upgrading the database.

2)  Both the old Oracle Version and New/Upgraded Oracle Version software must be installed properly before starting the upgrade.

3)  Take the backup of database before starting the upgrade.

4)  Before starting the upgrade Run the pre upgrade script from new ORACLE_HOME/rdbms/admin

utlu112i.sql      11.2

5)  Run the database upgrade from New Oracle Home using

DBUA  or catupgrd.sql  (Manual Upgrade )

6)      Once the upgrade is complete validate the upgrade using Post Upgrade Script  from new ORACLE_HOME/rdbms/admin/

utlu112s.sql      11.2

Step:-1

My 11gr1 or 11gr2 Oracle_home path

11gr1 ORACLE_HOME=/u01/app/oracle/product/11gr1

11gr1 ORACLE_SID=orcl

11gr2 ORACLE_HOME=/u01/app/oracle/product/11gr2

Step:-2
Preupgrade steps :- 

Run the utlu112i.sql in 11GR1 oracle home.

Copy the preupgrade script to any temp location for example /u01/app/oracle

cp /u01/app/oracle/product/11gr2/rdbms/admin/utlu112i.sql /u01/app/oracle

cd /u01/app/oracle

export ORACLE_HOME=/u01/app/oracle/product/11gr1

export ORACLE_SID=orcl

If you are upgrading to 11gr2 run below script

@utlu112i.sql

SQL> @/u04/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/utlu112i.sql

Step:-3
Purge recyclebin of database. 
 SQL> PURGE DBA_RECYCLEBIN;

           DBA Recyclebin purged.

Step:-4
Gather Schema and Database statistics

 SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

           PL/SQL procedure successfully completed.

Step:-5
 SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');

           PL/SQL procedure successfully completed.


 SQL> !

Step:-6

Shutdown 11gr1 database.

 SQL> shut immediate

Step:-7

Take a complete  backup of datafiles,controlfiles, redo logfiles, Spfile/pfile and password file.

The complete backup of database is needed to revert back if the Database upgrade fails.

Step:-8

Upgrading Database to 11GR2 

Below we are spooling to a log file so that we have the pre upgrade validations stored in upgrade_info.log file that we can refer later.

sql>      spool upgrade_info.log

sql>      @utlu112i.sql

sql>      spool off

The Pre upgrade script suggest any database changes that needs to be done before starting the Database Upgrade using DBUA or catupgrd.sql Script.

The pre upgrade script validates following information:

• Database version.
• Tablespace sizes.
• Updated, renamed and deprecated initialization parameters.
• Init.ora or Spfile Parameters that needs to be resize before starting the actual upgrade. ( This step should not be ignored)
• Database Components that will be upgraded or installed
• SYSAUX tablespace present (if missing).
• Miscellaneous Warnings
• Warning for Old Timezone
• Warning for Stale Optimizer Statistics
• Warning for EM Database Control Repository

Note: Each of the warning suggested by Pre Upgrade tool must be fixed.

Fix all the issues reported by Pre-upgrade script before starting DBUA

Step:-5
(Two Methods to do the actual Database Upgrade, either use DBUA

or catupgrd.sql (manual method)  which has many steps but it is more flexible)

Oracle recommends using DBUA to upgrade database.

export ORACLE_SID=orcl

Run the DBUA from New 11gr2 ORACLE_HOME/bin

$ cd /u01/app/oracle/product/11gr2/bin

$./dbua

Once the DBUA (Database Upgrade Assistant) GUI starts. Follow below points to upgrade the database. DBUA takes about 60 minutes to complete even for very large databases.

• Select the Database that needs to be upgraded.
• If the database name that needs to be upgraded is not present in DBUA,

Add the entry $ORACLE_SID:$ORACLE_HOME:N to /etc/oratab

For Example: ora11g:/u01/app/oracle/product/orcl:N

Note: The $ORACLE_HOME should be the old oracle home that is getting  upgraded.

• Oracle 11g requires the “Diagnostic Destination”. Set diagnostic destination to oracle base.
• Select “Do not move Database as part of upgrade”
• Do not specify Flash Recovery Area option
• Select “Configure database with Enterprise Manager” or Database Control if required.
• Select “Recompile Invalid objects at the end of upgrade” options in dbua
• Do not select the backup option if we have already have taken backup before starting Database Upgrade, Also it is not needed if we already have RMAN backup.

Step:-6

 Post Upgrade Steps

Once the upgrade is complete modify the profile file to use the new ORACLE_HOME

Run the post upgrade script to validate the upgrade using new ORACLE_HOME

SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql

SQL> @/u01/app/oracle/product/11gr2/rdbms/admin/utlu112s.sql

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

****************************END***********************************