Upgrade Oracle Database from 11.2.0.4 to 12.2.0.1 (Exadata RAC on Premise)

Posted: October 9, 2017 in Uncategorized

Good morning, Oracle Database Experts, rookies, and everyone in between. I have now done a couple dozen upgrades and have encountered a few bumps and bruises. It is now time for me to create an abbreviated list of steps unique to Oracle RAC on Exadata. It is not fully comprehensive and neither is the DOC ID I have been using. My document plus the Doc ID and your own round of testing should do the trick.

I am assuming you are aware of the requirements and prerequisites for upgrading. One of those that we have run into is the requirement to be on 12.2 Grid, 12.2 Exadata and 12.2 Storage. I am also assuming you have created a new Oracle home and uploaded the 12.2 Software.

  • Pre-Upgrade
    • Check for Invalid Objects:
    • SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
    • Check Registry for Invalid Packages:
      1. Remove any Invalid Packages. The necessary 12C Packages will be recreated during the DBUA Upgrade.
      2. SQL> select comp_id, comp_name, version, status from dba_registry;
      3. **EX** @drop_spatial.sql
    • Gather Optimizer Statistics and Fixed Object Stats.
    • SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;                     SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
    • Verify Materialized View Refreshes are Complete before Upgrade.
    • SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
      • For our environment, the few databases that utilized MVIEWS have frequent refresh jobs running. So we turned off the CRON prior to upgrade and turned them back on after the upgrade.
    • Purge DBA Recycle bin.
    • SQL> PURGE DBA_RECYCLEBIN;
    • Copy Encryption Wallets to 12.2 Oracle Home. Copy to all nodes in RAC. **Missing this step or performing this step incorrectly will either cause the upgrade to fail or will cause problems with future connections to the database.
    • cp cwallet.sso /u01/app/oracle/product/12.2.0.1/dbhome_1/admin/recover_db/wallet                                                    cp ewallet.p12 /u01/app/oracle/product/12.2.0.1/dbhome_1/admin/recover_db/wallet
    • Drop the audit_admin user and role. (self explanatory)
    • Ensure your SGA is set to at least 3GB. We set ours to a minimum of 5GB.
    • Take a full backup of the database.
    • Check your accounts for Case-Insensitive Password Version (10g or below)

Log in to SQL*Plus as an administrative user, and enter the following SQL query
SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

If there are any 10g versions, suggest you to refer Oracle documentation to fix 10g versions, failing to do so, user accounts with LOCKED after upgrade is completed. We updated our sqlnet.ora file to allow earlier password versions for legacy systems.

 

  • Upgrade with DBUA. Don’t forget to set your DISPLAY Variable.
    • Invoke DBUA with the X-Server of your choice: I borrowed the image from the MOS Document. Check for syntax in your own environment.
  • $ export DISPLAY=10.212.72.49:0.0
    $ export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
    $ export PATH=$ORACLE_HOME/bin:$PATH
  • $ dbua &
    
    
    • Select a database for upgrade. This version lists single instance and RAC DB’s together.
    • Pre-Requisite Checks. If there are no errors, click next. If you have done a good job with the pre-upgrade steps, the warnings can be noted and you can move on. If you missed a step, it will show up as an error. You will need to fix this before proceeding.
    • Select Upgrade Options: I chose Enable Parallel Upgrade, Upgrade Timezone Data only. We already gathered stats and recompiled invalid objects. I will do so again manually after the upgrade. I invite you to choose which options. I have found that doing as much as I can manually will speed up the DBUA process and make less room for failure.
    • Select Recovery Options:
      • I always take a full backup in my pre-steps.
      • I let DBUA set a Flashback and Guaranteed Restore Point. This is way faster than trying to restore a database from a Full Backup.
    • Un-Check the Enterprise Management Configuration.
    • Study the Database Upgrade Summary and click Finish.
    • Tail the logs. I tail the oracle_server log, the alert log and the parallel log that is running all the sql steps.
  • Post-Upgrade Steps:
    • Verify /etc/oratab is set correctly. DBUA doesn’t do that correctly for RAC Databases in my experience.
    • Verify tnsnames.ora is updated in the 12.2 Oracle Home.
    • Verify the registry. Same as pre-step.
    • Run utlrp to validate invalid objects. Same as pre-step.
    • Don’t forget to drop your Restore Point and turn off Flashback. You may get a surprise when you run out of recovery space!
    • Run the post upgrade fixup script on the upgraded database.

What challenges have you encountered and overcome? I’d love to hear your stories.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s