MANUAL UPGRADATION 10gR2 to 11gR1

Pre-Requisite:

You should have enough MEMORY, server space and SWAP SPACE on Oracle database 10g, which you want to migrate.

Also here we are upgrading 10g to Oracle Database 11g – Beta 6 (11.1.0.6)

Step 1) Installing Oracle 11g Home

We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home as a separate ORACLE_HOME in parallel to 10g Oracle Home.

Step 2) Pre-Upgrade Utility

In 11g Home you installed, go to $ORACLE_HOME/rdbms/admin and copy the file utlu111i.sql to some temp location.

$  cp   utlu111i.sql   /tmp

For more about utlu111i.sql :

 http://oracleflash.com/38/Oracle-11g-Release-1-Pre-Upgrade-tool-utlu111i-sql.html

Step 3) Executing the recommended steps

Following are the critical steps to be executed based on above warnings. These commands are to be executed while connecting to database from 10g Oracle Home

WARNING: –> Database is using an old timezone file version.
…. Patch the 10.2.0.1.0 database to timezone file version 4
…. BEFORE upgrading the database. Re-run utlu111i.sql after
…. patching the database to record the new timezone file version.

Finding the Version of existing timezone files:

SQL> select version from v$timezone_file;

          

          version

          ------------

                   2  

here oracle 10.2.0.1 has timezone file version of 2

Before you upgrade your timezone file version, you must run utltzuv2.sql to see if there is data that will be affected by the timezone file version upgrade. The script utltzuv2.sql creates a table named sys.sys_tzuv2_temptab, and fills it with data that may get affected by the timezone file version.

SQL >  select * from sys.sys_tzuv2_temptab ;

           no rows selected

here we see no rows but if we see any rows then we need to take that data backup and restore after timezone file version upgrade

Step 4) Run Pre-Upgrade Utility again

Run utlrp.sql to validate the objects if there is any invalidate objects.

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

Check for any INVALID Objects in the database before upgrade starts:

SQL> select count(*),object_type,owner from dba_objects  where status = ‘INVALID’ group by object_type,owner;

SQL> purge DBA_RECYCLEBIN

Gather dictionary stats to run UPGRADE process fast,

SQL> exec dbms_stats.gather_dictionary_stats;

Create pfile from spfile 

Shutdown  à shut down the database in oracle 10g.

Copy the pfile of database from ORACLE_HOME/dbs(10g) to ORACLE_HOME/dbs(11g) using cp command.

In that make changes shown below,

 1.     Remove BDUMP, CDUMP, UDUMP and their locations

 2.     Add Diagnostic_dest=/’$ORACLE_BASE/diag’-àlocation for diagnostic dest

 3.     Change audit_file_dest location

 4.     Change compatibility to 11g and save it.

After executing the recommended steps, run the pre-upgrade utility once again to make sure, you don’t get any critical warnings. If everything looks fine, shut down the database from 10g Oracle Home.

Step 5) Starting Upgrade

Source the following variables for 11g Oracle Home

[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export ORACLE_HOME=/opt/oracle/11.1.0/db_1
[oracle@/opt/oracle/11.1.0/db_1/dbs]$  export PATH=$ORACLE_HOME/bin:$PATH
[oracle@/opt/oracle/11.1.0/db_1/dbs]$  export ORACLE_SID=orcl
[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export TNS_ADMIN=$ORACLE_HOME/network/admin

connected to the database sys as sysdba

sqlplus “/ as sysdba” –> will be connected to idle instance

SQL> startup upgrade

SQL> SPOOL upgrade.log
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

Once the upgrade finishes, It will shut down the database automatically.

Now export New oracle HOME and startup the database

[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export ORACLE_HOME=/opt/oracle/11.1.0/db_1

[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export PATH=$ORACLE_HOME/bin:$PATH

[oracle@/opt/oracle/11.1.0/db_1/dbs]$  export ORACLE_SID=orcl

[oracle@/opt/oracle/11.1.0/db_1/dbs]$  export TNS_ADMIN=$ORACLE_HOME/network/admin

Login again as sysdba and startup in normal mode.

Check the dba_registry for the components and its status

SQL> startup 

SQL> select substr(comp_id,1,10) comp_id, substr(comp_name,1,25) comp_name, substr(version,1,10) version, status from dba_registry order by modified;

All the components should be VALID state

Step 6) Post-Upgrade Steps

Once the upgrade completes, restart the instance to reinitialize the system parameters for normal operation.

SQL> STARTUP

Run utlu111s.sql to display/verify the results of the upgrade:

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

Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode:

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

Run utlrp.sql to recompile invalid objects after upgrade

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

SQL> select count(*),object_type,owner from dba_objects  where status = ‘INVALID’ group by object_type,owner;

Run utluiobj.sql after the upgrade to identify any new invalid objects

Edit /etc/oratab file with new 11g Oracle home and comment out 10g oracle home