Post Oracle 12c announcement, testing the capabilities of new release become one of the top priorities of an Oracle DBA. Majority of the organization are now looking forward or thinking towards upgrading their current database to Oracle database 12c. Therefore, this blog is dedicated and focused on upgrading to Oracle database 12c. It will explore the various methods available for upgrade, and which is the best suited method to consider. Additionally, the blog is going to cover those new additions to upgrade method and explains how to downgrade an Oracle database 12 to its previous release.
This article will explain the following topics in details:
A billion dollar question that comes into everyone’s mind is whether to upgrade or not their existing Oracle environment on every Oracle new release announcement. It is indeed a pretty difficult question to answer just with YES or NO. In my perspective, the following are the key points/conditions that encourage an organization either to hold or proceed with the upgrade:
No to upgrade:
Yes to upgrade:
Upgrade is the process which transforms the existing Oracle dictionary information to reflect the new Oracle database release without actually touching the data directly. During the database upgrade process, all the database components will be upgraded to the new release. In the context, Oracle provides several methods/options to upgrade the current Oracle database to the new Oracle database release. The following are the major methods/options that are typically used/followed by the major section of the Oracle DBA to perform the task:
It is a well-known fact that every method comes with its share of advantages and limitations. Though there are various methods exists to upgrade an Oracle database, what is really important for a DBA is to investigate the pros and cons of each method thoroughly and choose the right approach that typically fits into their requirements.
Before commencing the database upgrade process, it is necessary to become familiar with those new additions or enhancements introduced in the context of Oracle database upgrade procedure. There are two essential elements that every DBA should realize before the database upgrade: 1) Database upgrades path, 2) Understanding and taking the right advantage of new additions and enactments in the upgrade process.
The following table depicts the compatible matrix for a direct and indirect database upgrade paths:
If your current database version doesn’t match with the direct upgrade list of versions, you therefore need to choose an indirect upgrade path. In this context, you must perform an upgrade of your current database to one of the versions listed in the direct upgrade lists of version and subsequently proceed with the Oracle database 12c upgrade.
The following new concepts were introduced in Oracle 12c with regards to database upgrade procedure:
Pre-upgrade tool: The new pre-upgrade information tool (preupgrd.sql ) is capable of verifying a detailed pre-upgrade checks, and generates pre/post-upgrade scripts that likely to address all the key concerns reported. The new tool is replaced with the traditional utlu__i.sql script, which offers more advantages over the old script.
The DBUA tool inherits the script internally and executes it before actually kicking off the database upgrade procedure. However, if you choose manual method over DBUA method for database upgrade, you must execute this script manually from the Oracle 12c DB home. A log file and two sql scripts will be generated upon executing the pre-upgrade information tool. The log file, preupgrade.log contains the details result about the verification checks and the pre/postupgrade.sql scripts consist of fixes of pre/post upgrade database steps.
Parallel processing: Pre Oracle 12c, there was no option to run the database upgrade process in parallel in order to take the advantage of multiple CPU on the server and cut off the total downtime required for the process. The catctl.pl utility loads the data dictionary and database components in parallel to improve the overall database upgrade process time. The DBUA inherits the tool, and triggers automatically. You must manually execute the utility whilst doing the manual database upgrade.
This goal of this section is to explain you a step-by-step procedure which will assist you upgrading a database to Oracle 12c manually. Let me demonstrate how to manually upgrade an Oracle 18.104.22.168 database – MYDB, to Oracle database 12c:
export ORACLE_SID=MYDBsqlplus / as sysdba
--- Refer the log file and sql scripts generated under the $ORACLE_BASE/cfgtoollogs/db_unique_name location to verify the concerns raised and fixup scripts for pre/post upgrade process
SQl> SHUTDOWN DATABASE;SQL> EXIT;
--- switch to Oracle 12c Oracle Home, startup the database in upgrade mode
cd $ORACLE_12C_HOMESQL> STARTUP UPGRADE;SQL> EXIT;
--- initiate the upgrade procedure in parallel using the new parallel upgrade utility
cd $ORACLE_12C_HOME/rdbms/admin$ ./perl catctl.pl –n 0 catupgrd.sql [to run in serial mode]$ ./perl catctl.pl –n 4 catupgrd.sql
Upon completing the catupgrd.sql script, the database will be shutdown. You are therefore need to start the database from the Oracle 12c home and run couple of post database upgrade steps.
SQL> STARTUP;SQL> @?/rdbms/admin/utlu121s.sql; -- to view the post-upgrade summarySQL> @?/rdbms/admin/utlrp.sql; -- recompile invalid objectsSQL> execute dbms_stasts.gather_fixed_objects_stats;SQL> exit;
The Database Upgrade Assistant (dbua) tool provides more options and automates a few manual steps in contrast to other upgrade methods during the course database upgrade procedure. The dbua tool certainly has an edge over other approaches. Therefore, it is best recommended to use this tool for database upgrade. The following will explain a procedure to upgrade a database using the dbua tool with important screenshots:
It is always safeguard and important to put-in place a workaround to back-out plan whilst proceeding with the upgrade process. This is essential because there could be some complications that would arises post database upgrade either from the database side or application side. The goal of this section of explain you a procedure to downgrade an Oracle database 12c to an earlier version.
export ORACLE_SID=MYDBsqlplus / as sysdba
SQL> shutdown immediate;SQL> STARTUP DOWNGRADE;SQL> SPOOL /tmp/downgrade.logSQL> @?/rdbms/admin/catdwgrd.sqlSQL> SPOOL OFFSQL> SHUTDOWN IMMEDIATE;
From the earlier Oracle version, run through the following steps:
SQL> startup upgrade;SQL> spool /tmp/reload.logSQL> @?/rdbms/admin/catreload.sqlSQL> SPOOL OFF;SQL> SHUTDOWN IMMEDIATE;SQL> STARTUP;
For more information and other details, refer Oracle upgrade documentations.