logo

Speak to an Expert+91-813-000-9878

mobile-icon
slide
blog-2

Oracle Database Upgrade to R12c

This blog will help you upgrade your current database (assuming 11.2.0.4) to 12.1.0.2
There are direct paths available to upgrade to version R12.1.x from some versions and there are indirect paths available if your current database version is lower. And your DBA has to upgrade to intermediate version first before he upgrades to the final version which R12.1.0.2
The matrix below represents the different possibilities to upgrade the database to R12.1.x versions based on your current database version

  • Install Oracle Database 12.1.0.2 media on target server
  • Install Example CD on top of 12.1.0.2
  • Create nls/data/9i directory
  • Apply additional 12.1.0.2 rdbms patches – few listed below, one need to check with Oracle Support
Patch 6880880 OPatch version for 12.1.0.2
Patch 19382851
Patch 19393542
Patch 19627012
Patch 19649152
Patch 20204035
Patch 20132434
  • Execute preupgrd.sql
Create following directory under 11g ORACLE_HOME : $ORACLE_HOME/cfgtoollogs/$ORACLE_SID/preupgrade/
Copy preupgrd.sql and utluppkg.sql from 12c ORACLE_HOME to 11g $ORACLE_HOME/rdbms/admin
SQL> @$ORACLE_HOME/rdbms/admin/preupgrd.sql
Check the issues reported and resolve them manually or execute preupgrade_fixups.sql
SQL> @$ORACLE_HOME/cfgtoollogs/$ORACLE_SID/preupgrade/preupgrade_fixups.sql
  • Gather Statistics
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
  • Verify If Materialized View Refreshes are completed
SQL> select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#, bitand(s.mflags, 8) from obj$ o, sum$ swhere o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8;  
  • Ensure That No Files Need Media Recovery
SQL> SELECT * FROM v$recover_file;
  • Ensure That No Files Are in Backup Mode
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
  • Resolve Outstanding Distributed Transactions
SQL> SELECT * FROM dba_2pc_pending;
  • Purge the Database Recycle Bin
SQL> PURGE DBA_RECYCLEBIN;
  • Verify SYS and SYSTEM Default tablespace
SQL> SELECT username, default_tablespaceFROM dba_usersWHERE username in ('SYS','SYSTEM'); 
  • Check whether database has any externally authenticated SSL users
SQL> SELECT username, default_tablespaceFROM dba_usersWHERE username in ('SYS','SYSTEM'); 
  • Record Location of datafiles, redo logs and control files
SQL> SELECT name FROM v$controlfile;
SQL> SELECT file_name FROM dba_data_files;
SQL> SELECT group#, member FROM v$logfile; 
  • Prepare the new Oracle home
SQL> create pfile='$ORACLE_HOME /dbs/11g_init_$ORACLE_SID.ora' from spfile;
Copy pfile to 12c Oracle Home

Remove the following deprecated parameters from init.ora :
#*._b_tree_bitmap_plans=FALSE# Required 11i setting
#*._fast_full_scan_enabled=FALSE
#*._like_with_bind_as_equality=TRUE
#*._optimizer_autostats_job=false# Turning off auto statistics
#*._sort_elimination_cost_ratio=5
#*._system_trig_enabled=true
#*._TRACE_FILES_PUBLIC=FALSE
#*.local_listener='FSB2_LOCAL'  
#*.sec_case_sensitive_logon=FALSE

Copy $ORACLE_HOME/network/admin from 11g Oracle Home to 12c Oracle Home and change 11.2.0 to 12.1.0 in listener.ora
Copy wallet file to 12c Oracle home ($ORACLE_HOME/admin//wallet) from 11g Oracle home ($ORACLE_HOME/admin//wallet)

Note : Crosscheck wallet path from sqlnet.ora or sqlnet_ifile.ora

Bring down Database services including database listener
Set ORA_NLS10

export ORA_NLS10=$ORACLE_HOME/nls/data/9idata

Start listener from 12c Oracle Home

  • Upgrade Database to 12cR1
Startup upgrade database
SQL> startup UPGRADE

The previous upgrade script catupgrd.sql has been replaced with the new catctl.pl Parallel Upgrade Utility script. This script provides the ability to run upgrade scripts in parallel. Run the new Parallel Upgrade Utility (catctl.pl).

cd $ORACLE_HOME/rdbms/admin
SQL>$ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql

Run utlu121s.sqlthe Post-Upgrade Status Tool

Startup the upgraded database and run the “utlu121s.sql” script to check the summary of the upgrade results

SQL>startup;
SQL>@$ORACLE_HOME/rdbms/admin/utlu121s.sql/
SQL>@$ORACLE_HOME/rdbms/admin/catuppst.sql
If the “postupgrade_fixups.sql” file contained any recommendations, run it now.
SQL> @/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
Recompile invalid objects
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
Check for newly invalid objects.
SQL> @$ORACLE_HOME/rdbms/admin/utluiobj.sql
Run again to check the final outcome of the upgrade.
SQL> @$ORACLE_HOME/rdbms/admin/utlu121s.sql
Now the upgraded database is available for use and you must alter any script or profile files that contain references to the ORACLE_HOME environment variable.