Upgrading Oracle database from 11.2.0.3 to 11.2.0.4

The following is a generic procedure to upgrade from 11.2.0.3 to 11.2.0.4 . As many of you know that 11.2.0.3 release has been desupported and last PSU released was in JULY 2015. so you need to either upgrade to 12cR1 OR to 11.2.0.4 . The right move is of course to move forward to 12c however, some vendor applications are not certified  Yet 😦 !!!

So you will need to upgrade 11.2.0.4.

I hope this will help. Please note that the below procedure assumes that you have already installed 11.2.0.4 binaries successfully.

 

Procedure upgrade in Unix environment:

//create a directory called “upgrade_11gR4”

mkdir upgrade_11gR4

cd upgrade_11gR4

//now copy 2 scripts (utlu112i.sql) and (dbupgdiag.sql )

cp /oraclex/oradbp05/product/11.2.0.4/rdbms/admin/utlu112i.sql .

cp /oracle_software/database/dbupgdiag.sql .

//gather general database information

sql

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

SQL> set pages 300

SQL> set lines 250

SQL> col owner for a23;

SQL> col object_name for a35;

SQL> col object_type for a20;

SQL> col action for a15;

SQL> col comments for a40;

SQL> col comp_name for a40;

SQL> col version for a15;

 

SQL> spool before_upgrading_11gR4.log

SQL> SELECT NAME FROM V$DATABASE;

SQL> SELECT * from V$VERSION;

SQL> SELECT ACTION,VERSION,COMMENTS FROM SYS.REGISTRY$HISTORY;

SQL> SELECT COMP_NAME,VERSION,STATUS FROM DBA_REGISTRY;

SQL> select count(*) from dba_objects where  status = ‘INVALID’;

SQL> spool off

SQL>upgrade_information_gather.log

SQL>@utlu112i.sql

SQL>spool off

exit

//check the the log output and read it carefully

cat upgrade_information_gather.log

sql

SQL>alter session set nls_language=’American’;
SQL>@dbupgdiag.sql

//then check the log output for diagnostic information

//Purging the Database Recycle Bin

SQL> PURGE dba_recyclebin;

//Copy the LISTENER.ORA &  TNSNAMES.ORA & SQLNET.ORA from 11.2.0.3 directory to 11.2.0.4 directory

//shutdown the database

//change  your .profile to point to the new Oracle HOME binaries for 11.2.0.4

cd $ORACLE_HOME/rdbms/admin

sqlplus “/ as sysdba”

SQL>startup UPGRADE

SQL>@catupgrd.sql

//enter a directory where you want to log to be generated to

SQL> shutdown immediate;

sqlplus “/ as sysdba”

SQL>STARTUP

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

SQL>set pages 300

SQL>set lines 250

SQL>col owner for a17;

SQL>col object_name for a35;

SQL>col object_type for a20;

SQL>Select owner,object_type,object_name from dba_objects where status=’INVALID’ ORDER BY 1,2,3;

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

 

//Run the following script to identify any new invalid objects due to the upgrade:

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

 

//check your time zone

SQL>SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE ‘DST_%’ ORDER BY PROPERTY_NAME;

//if the the time zone is 14 then you are OK…and your upgrade is DONE…congratulations 🙂

//if the time zone value is less than 14 then proceed with the following:

SQL>TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

SQL>TRUNCATE TABLE sys.dst$affected_tables;

SQL>TRUNCATE TABLE sys.dst$error_table;      

 

//Start upgrade window

SQL>EXEC DBMS_DST.BEGIN_UPGRADE(14);

//The message “An upgrade window has been successfully started.” will be seen

//check

SQL>SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE ‘DST_%’

ORDER BY PROPERTY_NAME;

//restart the database

SQL>shutdown immediate;

SQL> startup;

SQL>set serveroutput on

VAR numfail number

BEGIN

DBMS_DST.UPGRADE_DATABASE(:numfail,

parallel => TRUE,

log_errors => TRUE,

log_errors_table => ‘SYS.DST$ERROR_TABLE’,

log_triggers_table => ‘SYS.DST$TRIGGER_TABLE’,

error_on_overlap_time => FALSE,

error_on_nonexisting_time => FALSE);

DBMS_OUTPUT.PUT_LINE(‘Failures:’|| :numfail);

END;

/

SQL> VAR fail number

BEGIN

DBMS_DST.END_UPGRADE(:fail);

DBMS_OUTPUT.PUT_LINE(‘Failures:’|| :fail);

END;

/

//Output that will be seen: “An upgrade window has been successfully ended”  “Failures:0”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s