Oracle Database Manual Upgrade from 11.2.0.2 to 11.2.0.4 step by step

Manual Database upgrade from 11.2.0.2 to 11.2.0.4

Basic Information Collection

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

show parameter spfile

show parameter control file

column name format a50;

select name from v$datafile;

check listener.ora and tnsnames.ora file

1. Install the 11.2.0.4 in a new Home Location using runInstaller

Ex:

Unzip the patch 13***

$/u01/app/oracle/11.2.0.4

1)Login on Database from old home and copy prerequisite script in /tmp from 11.2.0.4 (New Home) and check pre-requisite by running utlu112i.sql

Note – Review log carefully and complete all recommendation like system and sysaux tablespace , stale stats etc

 

sqlplus / as sysdba

spool upgrade_info19april.log

@utlu112i.sql

Run pre-requisite as per recommendation.

?/rdbms/admin/sqltrpt.sql

Check that National Characterset

select value from nls_database_parameters where parameter ='NLS_NCHAR_CHARACTERSET';

If this is UTF8 or AL16UTF16 then no action is needed.

If is not UTF8 or AL16UTF16 then refer the following article

Note 276914.1 The National Character Set in Oracle 9i and 10g.

Optimizer Statistics:

When upgrading to Oracle Database 11g Release 2 (11.2), optimizer statistics are collected for dictionary tables that lack statistics. 

This statistics collection can be time consuming for databases with a large number of dictionary tables, 

but statistics gathering only occurs for those tables that lack statistics or are significantly changed during the upgrade

execute dbms_stats.gather_dictionary_stats

Check Database Component

select comp_id,comp_name,version,status from dba_registry;

Ensure that no files need media recovery:

Sql> SELECT * FROM v$recover_file;

Check free tablespace for system and sysaux

 

COL TABLESPACE_NAME FORMAT A40

col    "Tablespace"  for a50

col    "USED_IN_MB"     for 9999999

col    "FREE_IN_MB"       for 99999

 

  select

   a.TABLESPACE_NAME,

   a.TOTAL_IN_MB,

   to_char(nvl((b.used),0.00),'999990.99') USED_IN_MB,

    to_char(nvl((c.free),0.00),'999990.99') FREE_IN_MB,

    to_char(nvl(((b.used/a.TOTAL_IN_MB)*100),0.00),'99990.99') PCT_USED

    from

    (select TABLESPACE_NAME,

   to_char(sum(bytes)/(1024*1024),'9999990.99') TOTAL_IN_MB

   from sys.dba_data_files

    group by TABLESPACE_NAME) a,

   (select TABLESPACE_NAME,bytes/(1024*1024) used

   from sys.SM$TS_USED) b,

    (select TABLESPACE_NAME,bytes/(1024*1024) free

    from sys.SM$TS_free) c

   where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+) and

   a.TABLESPACE_NAME=c.tablespace_name(+)

 

Drop all invalid object which belong to sys and system

 

select owner,object_name,object_type from dba_objects where status='INVALID' and owner in ('SYS','SYSTEM');

 

Upgrade Activities- Implementation

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

Note :Start database in upgrade mode and Run upgrade and run catupgrd.sql . it will take approx. 40 to 60 min

 

set Oracle NEW HOME

Go to NEW ORACEL_HOME/rdbms/admin

sqlplus / as sysdba ; 

SQL> startup upgrade; 

Set the system to spool results to a log file for later verification after the upgrade is completed and start the upgrade script.

SQL> set echo on;

SQL>  spool upgrade.log;

SQL>  @catupgrd.sql ; 

spool off

These measures are an important final step to ensure the integrity and consistency of the newly upgraded Oracle Database software. 

Also, if you encountered a message listing obsolete initialization parameters when you started the database for upgrade,

then remove the obsolete initialization parameters from the parameter file before restarting.

If necessary, convert the SPFILE to a PFILE so you can edit the file to delete parameters.

check status of database 

select name,open_mode,db_role from v$database;

check the invalid object_status

 select count(object_name),status from dba_objects group by status

 

Post upgrade

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

startup database and execute Post-Upgrade Status Tool

go to $ORACLE_HOME/rdbms/admin; 

sqlplus / as sysdba; 

SQL>startup; 

SQL> @utlu112s.sql

Recompile Invalid Objects; 

SQL>@utlrp.sql

Configure and Start Listener.ora 

Crosscheck Environment Variables

create spfile from pfile

shutdown database ;

startup database using spfile

Validate the database components as below.

SQL> select name, open_mode from v$database;

SELECT comp_name, version, status FROM dba_registry;