Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In this article, I will talk about how to Upgrade Oracle Database From 11.2.0.4 To 12.1.0.2.
Current ORACLE_HOME :
1 | /u01/app/oracle/product/11.2.0/dbhome_1 |
New ORACLE_HOME :
1 | /u02/app/oracle/product/12.1.0.2/db_1 |
Database name : TESTDB
Prerequisites
1. We create directories and give permissions.
1 2 3 | $ mkdir -p /u02/app/oracle/product/12.1.0.2/db_1 $ chown -R oracle:oinstall /u02 $ chmod -R 775 /u02 |
2. We empty the Recyclebin.
1 | SQL> PURGE DBA_RECYCLEBIN; |
3. We compile invalid objects.
1 | SQL> @?/rdbms/admin/utlrp.sql |
Manual Upgrade
1. We copy the script files of the Preupgrade tool.
1 2 3 | $ mkdir -p /tmp/upgrade $ cp /u02/app/oracle/product/12.1.0.2/db_1/rdbms/admin/preupgrd.sql /tmp/upgrade $ cp /u02/app/oracle/product/12.1.0.2/db_1/rdbms/admin/utluppkg.sql /tmp/upgrade |
2. We run the Preupgrade tool.
1 2 3 4 5 6 7 8 9 10 11 12 13 | SQL> @/tmp/upgrade/preupgrd.sql ACTIONS REQUIRED: 1. Review results of the pre-upgrade checks: /tmp/upgrade/preupgrade.log 2. Execute in the SOURCE environment BEFORE upgrade: /tmp/upgrade/preupgrade_fixups.sql 3. Execute in the NEW environment AFTER upgrade: /tmp/upgrade/postupgrade_fixups.sql $ cd /tmp/upgrade $ ls -ltr -rw-r--r-- 1 oracle oinstall 8049 Sep 10 11:36 preupgrade.log -rw-r--r-- 1 oracle oinstall 3567 Sep 10 11:36 preupgrade_fixups.sql -rw-r--r-- 1 oracle oinstall 2656 Sep 10 11:36 postupgrade_fixups.sql |
3. We run preupgrade_fixup.sql and check the changes.
1 | SQL> @/tmp/upgrade/preupgrade_fixups.sql |
4. The suggested changes as a result of running preupgrade_fixup.sql are as follows.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE - Stop EM Database Control: $> emctl stop dbconsole cp /u02/app/oracle/product/12.1.0.2/db_1/rdbms/admin/emremove.sql $ORACLE_HOME/rdbms/admin/ - Connect to the Database using the SYS account AS SYSDBA: SET ECHO ON; SET SERVEROUTPUT ON; @emremove.sql ORACLE_HOME/olap/admin/catnoamd.sql script before or after the upgrade. EXECUTE dbms_stats.gather_dictionary_stats; |
5. We check the dbms_scheduler jobs and disable the enabled jobs.
1 2 3 4 5 | SQL> set pagesize 2000 SQL> set lines 2000 SQL> set long 99999 SQL> select owner,JOB_NAME,ENABLED,state from dba_scheduler_jobs; SQL> execute dbms_scheduler.disable('RLM$EVTCLEANUP'); |
6. We check the default tablespace of system and sys. Both must be the same.
1 2 3 4 5 6 7 | SQL> SELECT username, default_tablespace FROM dba_users WHERE username in ('SYS','SYSTEM'); USERNAME |DEFAULT_TABLESPACE ------------------------------|------------------------------ SYSTEM |SYSTEM SYS |SYSTEM |
7. We stop the Listener and close the database.
1 2 | $ lsnrctl stop listener SQL> shu immediate |
8. The new ORACLE HOME path is set.
1 2 3 | $ export ORACLE_HOME=/u02/app/oracle/product/12.1.0.2/db_1 $ export PATH=$ORACLE_HOME/bin:$PATH $ export ORACLE_BASE=/u02/app/oracle/ |
9. Copy the spfile and password file under 11G $ORACLE_HOME/dbs to 12C $ORACLE_HOME/dbs.
1 2 | $ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileTESTDB.ora /u02/app/oracle/product/12.1.0.2/db_1/dbs/ $ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwTESTDB /u02/app/oracle/product/12.1.0.2/db_1/dbs/ |
10. We start the database in upgrade mode.
1 2 3 4 5 6 | $ cd $ORACLE_HOME/rdbms/admin $ pwd /u02/app/oracle/product/12.1.0.2/db_1/rdbms/admin $ sqlplus “/ as sysdba” SQL> startup UPGRADE SQL> exit |
11. We run the catupgrd script file in 4 parallels.
1 2 | $cd $ORACLE_HOME/rdbms/admin $ORACLE_HOME/perl/bin/perl catctl.pl -n 4-l $ORACLE_HOME/diagnostics catupgrd.sql |
12. We follow the log.
1 2 | $ cd /uv1172/apps/oracle/product/12.1.0.2/diagnostics $ tail -100f catupgrd0.log |
13. We open the database and run the Post-Upgrade tool.
1 2 3 | $ sqlplus "/as sysdba" SQL> startup SQL> @utlu121s.sql |
14. We run catuppst.sql.
1 | SQL>@$ORACLE_HOME/rdbms/admin/catuppst.sql |
15. We run postupgrade_fixups.sql and do the suggested actions.
1 2 3 4 5 6 7 8 9 | SQL>@$ORACLE_HOME/rdbms/admin/catuppst.sql -- The following item is probably included in your postupgrade_fixups.sql script. EXECUTE DBMS_STATS.gather_fixed_objects_stats; -- Recompile invalid objects. @utlrp.sql -- Check for newly invalid objects. @utluiobj.sql -- Run again to check the final outcome of the upgrade. @utlu121s.sql |
16. We check the upgraded database.
1 2 3 4 5 | $ sqlplus / as sysdba SQL> SELECT name, open_mode FROM v$database; NAME OPEN_MODE --------- -------------------- TESTDB READ WRITE |
As we have seen above, the database is available in READ/WRITE mode after the upgrade.
