In this article, I will talk about how to Upgrade Oracle Database From 11.2.0.4 To 12.1.0.2.<\/p>\n
Current ORACLE_HOME :<\/p>\n
\/u01\/app\/oracle\/product\/11.2.0\/dbhome_1<\/pre>\nNew ORACLE_HOME :<\/p>\n
\/u02\/app\/oracle\/product\/12.1.0.2\/db_1<\/pre>\nDatabase name : TESTDB<\/p>\n
Prerequisites<\/h4>\n
1. We create directories and give permissions.<\/p>\n
$ mkdir -p \/u02\/app\/oracle\/product\/12.1.0.2\/db_1\r\n$ chown -R oracle:oinstall \/u02\r\n$ chmod -R 775 \/u02<\/pre>\n2. We empty the Recyclebin.<\/p>\n
SQL> PURGE DBA_RECYCLEBIN;\r\n<\/pre>\n3. We compile invalid objects.<\/p>\n
SQL> @?\/rdbms\/admin\/utlrp.sql\r\n<\/pre>\nManual Upgrade<\/h4>\n
1. We copy the script files of the Preupgrade tool.<\/p>\n
$ mkdir -p \/tmp\/upgrade\r\n$ cp \/u02\/app\/oracle\/product\/12.1.0.2\/db_1\/rdbms\/admin\/preupgrd.sql \/tmp\/upgrade\r\n$ cp \/u02\/app\/oracle\/product\/12.1.0.2\/db_1\/rdbms\/admin\/utluppkg.sql \/tmp\/upgrade<\/pre>\n2. We run the Preupgrade tool.<\/p>\n
SQL> @\/tmp\/upgrade\/preupgrd.sql\r\nACTIONS REQUIRED:\r\n1. Review results of the pre-upgrade checks:\r\n \/tmp\/upgrade\/preupgrade.log\r\n2. Execute in the SOURCE environment BEFORE upgrade:\r\n \/tmp\/upgrade\/preupgrade_fixups.sql\r\n3. Execute in the NEW environment AFTER upgrade:\r\n \/tmp\/upgrade\/postupgrade_fixups.sql\r\n$ cd \/tmp\/upgrade\r\n$ ls -ltr\r\n-rw-r--r-- 1 oracle oinstall 8049 Sep 10 11:36 preupgrade.log\r\n-rw-r--r-- 1 oracle oinstall 3567 Sep 10 11:36 preupgrade_fixups.sql\r\n-rw-r--r-- 1 oracle oinstall 2656 Sep 10 11:36 postupgrade_fixups.sql\r\n<\/pre>\n3. We run preupgrade_fixup.sql and check the changes.<\/p>\n
SQL> @\/tmp\/upgrade\/preupgrade_fixups.sql\r\n<\/pre>\n4. The suggested changes as a result of running preupgrade_fixup.sql are as follows.<\/p>\n
ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE\r\n- Stop EM Database Control:\r\n $> emctl stop dbconsole\r\ncp \/u02\/app\/oracle\/product\/12.1.0.2\/db_1\/rdbms\/admin\/emremove.sql $ORACLE_HOME\/rdbms\/admin\/\r\n \r\n - Connect to the Database using the SYS account AS SYSDBA:\r\n SET ECHO ON;\r\n SET SERVEROUTPUT ON;\r\n @emremove.sql\r\n \r\nORACLE_HOME\/olap\/admin\/catnoamd.sql script before or \r\n after the upgrade.\r\n \r\n EXECUTE dbms_stats.gather_dictionary_stats;<\/pre>\n5. We check the dbms_scheduler jobs and disable the enabled jobs.<\/p>\n
SQL> set pagesize 2000\r\nSQL> set lines 2000\r\nSQL> set long 99999\r\nSQL> select owner,JOB_NAME,ENABLED,state from dba_scheduler_jobs;\r\nSQL> execute dbms_scheduler.disable('RLM$EVTCLEANUP');<\/pre>\n6. We check the default tablespace of system and sys. Both must be the same.<\/p>\n
SQL> SELECT username, default_tablespace\r\nFROM dba_users\r\nWHERE username in ('SYS','SYSTEM');\r\nUSERNAME |DEFAULT_TABLESPACE\r\n------------------------------|------------------------------\r\nSYSTEM |SYSTEM\r\nSYS |SYSTEM<\/pre>\n7. We stop the Listener and close the database.<\/p>\n
$ lsnrctl stop listener\r\nSQL> shu immediate<\/pre>\n8. The new ORACLE HOME path is set.<\/p>\n
$ export ORACLE_HOME=\/u02\/app\/oracle\/product\/12.1.0.2\/db_1\r\n$ export PATH=$ORACLE_HOME\/bin:$PATH\r\n$ export ORACLE_BASE=\/u02\/app\/oracle\/<\/pre>\n9. Copy the spfile and password file under 11G $ORACLE_HOME\/dbs to 12C $ORACLE_HOME\/dbs.<\/p>\n
$ cp \/u01\/app\/oracle\/product\/11.2.0\/dbhome_1\/dbs\/spfileTESTDB.ora \/u02\/app\/oracle\/product\/12.1.0.2\/db_1\/dbs\/\r\n$ cp \/u01\/app\/oracle\/product\/11.2.0\/dbhome_1\/dbs\/orapwTESTDB \/u02\/app\/oracle\/product\/12.1.0.2\/db_1\/dbs\/<\/pre>\n10. We start the database in upgrade mode.<\/p>\n
$ cd $ORACLE_HOME\/rdbms\/admin\r\n$ pwd\r\n\/u02\/app\/oracle\/product\/12.1.0.2\/db_1\/rdbms\/admin\r\n$ sqlplus \u201c\/ as sysdba\u201d\r\nSQL> startup UPGRADE\r\nSQL> exit<\/pre>\n11. We run the catupgrd script file in 4 parallels.<\/p>\n
$cd $ORACLE_HOME\/rdbms\/admin\r\n$ORACLE_HOME\/perl\/bin\/perl catctl.pl -n 4-l $ORACLE_HOME\/diagnostics catupgrd.sql<\/pre>\n12. We follow the log.<\/p>\n
$ cd \/uv1172\/apps\/oracle\/product\/12.1.0.2\/diagnostics\r\n$ tail -100f catupgrd0.log<\/pre>\n13. We open the database and run the Post-Upgrade tool.<\/p>\n
$ sqlplus \"\/as sysdba\"\r\nSQL> startup\r\nSQL> @utlu121s.sql<\/pre>\n14. We run catuppst.sql.<\/p>\n
SQL>@$ORACLE_HOME\/rdbms\/admin\/catuppst.sql\r\n<\/pre>\n15. We run postupgrade_fixups.sql and do the suggested actions.<\/p>\n
SQL>@$ORACLE_HOME\/rdbms\/admin\/catuppst.sql\r\n-- The following item is probably included in your postupgrade_fixups.sql script.\r\nEXECUTE DBMS_STATS.gather_fixed_objects_stats;\r\n-- Recompile invalid objects.\r\n@utlrp.sql\r\n-- Check for newly invalid objects.\r\n@utluiobj.sql\r\n-- Run again to check the final outcome of the upgrade.\r\n@utlu121s.sql<\/pre>\n16. We check the upgraded database.<\/p>\n
$ sqlplus \/ as sysdba\r\nSQL> SELECT name, open_mode FROM v$database;\r\nNAME OPEN_MODE\r\n--------- --------------------\r\nTESTDB READ WRITE<\/pre>\nAs we have seen above, the database is available in READ\/WRITE mode after the upgrade.<\/p>\n
<\/div>\n