Question

How to upgrade from 10g to 11g

Answer 1

DATA UPGRADATION
                             ===================
NOTE:-
-----
Upgrading from 10g to 11g in the same server.
so that we should ensure having oracle 11g s/w installed on the same server.

11g installed in the location:
ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1

1) start the 10g database

2) goto following location and execute sql file
   $cd /u02/app/oracle/product/11.2.0/dbhome_1/rdbms/admin
   $ ls -ltr utlu*
   > @utlu112i.sql
NOTE:-
-----
   This is the pre-upgrade information tool. It is available in 11g, but we are running in 10g.

3) Info says us to do the following warnings:

a)compatible changes
  version : 10.2.0.4.0
  compatible: 10.2.0.1.0
  > show parameter compatible
  If require change it through pfile,( 10.2.0.1.0 to 10.2.0.4.0)

b)tablespaces
  If require make changes to tablespaces. If no enough space then add datafile.

c)deprecated parameters
  Note:- In 11g, the 3 folders i.e., b,c,u dump are replaced with diagnostic_dest.

d)components
  Make sure they are valid.
  If any component found invalid, run the following script
  >@?/rdbms/admin/utlrp.sql

e)verify timezone upgrade to 4
  >select * from v$timezone_file;

f)Gathering statistics on database contains schemas with stale optimizer(not analyzed) statistics
  >execute dbms_stats.gather_schema_stats (ownname=>'SYS',CASCADE=>TRUE);
  Then, verify last_analyzed from dba_tables.
  Keep all stats in one file by changing ownname and run
  $vi gatherstats.sql
  >@/home/oracle/gatherstats.sql

g)Invalid objects
  run utlrp script
  >@?/rdbms/admin/utlrp.sql

h)recyclebin
  >purge recyclebin
  >purge dba_recyclebin

4) copy 10g pfile to 11g $ORACLE_HOME/dbs(11g pfile location) and edit it
  $vi inittest.ora (test is 11g database)
  Now change as follows:
  - comment a,b,c,u dump destinations. i.e., include # before them
  - Include
    *.diagnostic_dest='/u02/app/oracle'
  - Change control file destinations
 
5) change bash profile to 11g
  $vi .bash_profile
    change HOME & BASE to 11g destination
  $. .bash_profile
  Then verify them.

6) creating directories for 11g TEST database in 11g home
   $cd /u02/app/oracle/oradata
   $mkdir test
   
7) creating directories in diag folder
   $cd /u02/app/oracle/diag/rdbms
   $mkdir test
   $cd test
   $mkdir test
   $pwd
    /u02/app/oracle/diag/rdbms/test/test
   $mkdir alert cdump hm incident incpkg ir iclc metadata stage sweep trace
   Copy previous dbfiles to here

8) $dbua
        Next
   (.) test   (select 10g database)
        Next
   continue with upgrade: YES
   [.] recompile
   Degree parallelism: 2
   [.] turnoff archaving for duration of upgrade
   (.) move database files during upgrade
       (.) file system
                     Next
   (.) use common location for all database files
       location: /u02/app/oracle/oradata
                                      Next
       Diagnostic destination: /u02/app/oracle
                                           Next

   Make sure upgrading summary:
   
             database       target database
            ----------      ----------------
  Name:        test               test
  version:  10.2.0.4.0        11.2.0.1.0

  FINISH
       Continue