Upgrade 10g to 11g with DBUA

1. Install oracle 11g software.
2. Run utlu111i.sql (located in 11g_ORACLE_HOME/rdbms/admin) on 10g database and examin the output.
3. update the timezone to 4.
   

   select * from v$timezone_file;
   FILENAME VERSION
   ------------ ----------
   timezlrg.dat 3

4. SELECT CASE COUNT(DISTINCT(tzname))
   WHEN 183 then 1
   WHEN 355 then 1
   WHEN 347 then 1
   WHEN 377 then 2
   WHEN 186 then case COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 end
   WHEN 185 then 3
   WHEN 386 then 3
   WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end
   WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end
   WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end
   WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end
   WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end
   ELSE 0 end VERSION
   FROM v$timezone_names;
   
   
   VERSION
   ----------
   3

5. If the Version of the existing timezone is less than 4, then apply the patch for Version 4 timezone files. Download patch 5632264 and apply to update the timezone

6. select * from v$timezone_file;
   
   FILENAME VERSION
   ------------ ----------
   timezlrg.dat 4
   
   SELECT CASE COUNT(DISTINCT(tzname))
   WHEN 183 then 1
   WHEN 355 then 1
   WHEN 347 then 1
   WHEN 377 then 2
   WHEN 186 then case COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 end
   WHEN 185 then 3
   WHEN 386 then 3
   WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end
   WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end
   WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end
   WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end
   WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end
   ELSE 0 end VERSION
   FROM v$timezone_names;
   
   
   VERSION
   ----------
   4

7. Gather statistics on schemas instructed by utlu111i.sql
   EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
   EXEC DBMS_STATS.GATHER_SCHEMA_STATS(’user_name’);

8. Grant privileges revoked from public to resolve invalid objects

9. Save Oracle Enterprise Manager Database Control Data
   

   
   1. Install Oracle Database 11g Release 1 (11.1).
   2. Set ORACLE_HOME to your old Oracle home.
   3. Set ORACLE_SID to the SID of the database being upgraded.
   4. Set PATH, LD_LIBRARY_PATH and SHLIB_PATH to point to the Oracle home from
   which the database is being upgraded.
   5. Go to the Oracle Database 11g Release 1 (11.1) home.
   6. Execute one of the following:
   a. For a single-instance database, run the following command, where old_SID is
   the SID of the database being upgraded and save_directory is the path to
   the storage place you have chosen for your Database Control files and data:
   emdwgrd -save -sid old_SID -path save_directory
   b. If the database is an Oracle RAC database, remote copy is required across the
   cluster nodes. Define an environment variable to indicate which remote copy
   is configured. For example: setenv EM_REMCP /usr/bin/scp
   Then, execute the following save command:
   emdwgrd -save -cluster -sid old_SID -path save_directory
   If 10g Oracle home is on a shared device, add -shared to the previous command line.
   7. Enter the SYS password for the database to be upgraded.

10. Create listener on 11g for upgrade
11. Run dbua from 11g environment to uprgade.
12. set the compatible parameter.

    alter system set compatible='11.1.0.0.0' scope=spfile ;