Oracle Database Upgrade from 11g to 12c Step by Step

Step 1: Ask your Application Team whether their Application is compatible with new Oracle version to be upgraded i.e. 12c. If yes, then proceed with upgrade otherwise do not start upgrade process. For this, complete application testing should be done on 12c version on UAT environment. Mandatory Prerequisites: Operating System must be 64-bit Minimum 20 GB Hard Disk space Install Oracle 12c Binaries in a new separate mount-point.

Step 2 : Take full database backup of existing database plus archivelog, control file and spfile. Step 3: Capture below details from existing 11g database. –dbinfo.sql spool dbinfo.txt set lines 300 pages 3000 set lines 300 pages 3000 col DB_UNIQUE_NAME for a12 col OPEN_MODE for a12 col host_name for a17 select name,instance_name,db_unique_name,open_mode,database_role,controlfile_type,log_mode,flashback_on,to_char(startup_time,'dd-mm-yyyy hh24:mi:ss') startup_time,host_name from v$database,v$instance; select * from v$version; set lines 300 pages 3000 col COMP_ID for a10 col COMP_NAME for a40 col STATUS for a17 select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry; set lines 300 pages 3000 col property_name for a33 col property_value for a33 select property_name,property_value from database_properties where property_name like '%NLS_%' order by 1; select * from v$timezone_file; set lines 300 pages 3000 select owner,OBJECT_TYPE,status,count(*) from dba_objects where owner not in('SYS','SYSTEM') and status='INVALID' group by owner,OBJECT_TYPE,status order by 1; select count(*) from dba_objects where status='INVALID' and owner not in('SYS','SYSTEM'); select object_name, object_type from dba_objects where object_name||object_type in (select object_name||object_type from dba_objects where owner = 'SYS') and owner = 'SYSTEM'; SELECT * FROM DBA_DEPENDENCIES WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR','DBMS_LDAP') AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS'); SELECT version FROM v$timezone_file; select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#, bitand(s.mflags, 8) from obj$ o, sum$ s where o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8; SELECT * FROM v$recover_file; SELECT * FROM v$backup WHERE status != 'NOT ACTIVE'; select * from v$database_block_corruption: SELECT * FROM dba_2pc_pending; SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1) FROM v$parameter WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%'; –Disable any cronjob set pagesize 2000 set lines 2000 set long 99999 select owner,JOB_NAME,ENABLED,state from dba_scheduler_jobs where owner not in('SYS','SYSTEM'); SELECT username, default_tablespace FROM dba_users WHERE username in ('SYS','SYSTEM'); SELECT name FROM sys.user$ WHERE ext_username IS NOT NULL AND password = 'GLOBAL'; !ps -ef | grep agent !emctl stop dbcontrol SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name; spool off;

Step 4: Copy preupgrd.sql and utluppkg.sql scripts from Oracle 12c Home to 11g Home in the same location and run both from 11g Home. These scrips will generate below three files. preupgrade.log => Check the logfile to see if any error. preupgrade_fixups.sql => This should be run before the upgrade. postupgrade_fixups.sql => This should be run after the upgrade. Run preupgrd_fixups.sql script by Oracle11g Home and make any changes recommended by the script.(This will create commands and you have to execute these manually). Make the changes in spfile and restart the database to take effort.

Step 5: Shut down 11g database and associated listener and also set below 12c Home environment variables. ORACLE_SID ORACLE_HOME PATH

Step 6: Startup the database in upgrade mode. This will not upgrade your database, instead it will put the database in upgrade mode. SQL> startup upgrade;

Step 7: Run catupgrd.sql script using parallel option. $$ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l $ORACLE_HOME/diagnostics catupgrd.sql The above script will create multiple files in the location $ORACLE_HOME/diagnostics Tail the below file and check if any error. tail -400f catupgrd0.log

Step 8: The above script will shut down the database finally. You have to start it up manually. SQL> startup;

Step 9: Run script utlu121s.sql which will give the upgrade process status. SQL> @?/rdbms/admin/utlu121s.sql

Step 10: Run catuppst.sql script and see if any error.  SQL> @?/rdbms/admin/catuppst.sql Now you have upgraded your database successfully. Follow the below procedure to perform the post check.

Step 11: Run postupgrade_fixups.sql and utlurp.sql script Step 12: Upgrade Time Zone Download DBMS_DST_scriptsV1.9.zip file from Oracle Metalink Support, transfer this file to actual server and unzip the same. Use below two files for Time Zone. upg_tzv_check.sql   => This script will gets the latest time zone exist in the database. upg_tzv_apply.sql => This will actually upgrade the Time Zone. SQL> select version from v$timezone_file; SQL> @upg_tzv_check.sql SQL> @upg_tzv_apply.sql SQL> select version from v$timezone_file; Note: For upgrading Time Zone, your database must be in upgrade mode. This script will automatically puts the database in upgrade mode after shutting down the instance. Step 13: Change the Oracle Home entry in listener.ora file and update it to Oracle 12c Home. Step 14: Follow the step no. 3 to verify everything after the upgrade process.

Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get future blog updates !