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”
Geodata Master 