Supported DB Direct Upgrade Paths to 12c are:
-Oracle Database 10g (10.2.0.5)
-Oracle Database 11g (11.1.0.7)
-Oracle Database 11g (11.2.0.2 or later)
There are three supported upgrade paths/tools. The upgrade options that are supported with Oracle Database 12c are:
-Database Upgrade Assistant (DBUA)
-Manual Upgrade (script based)
-Export/Import
For the purpose of this article, let’s focus on using the Database Upgrade Assistant (DBUA).
1. Run the PREUPGRD.SQL
In order to run the preupgrd.sql file, we first need to install the new binaries into an Oracle home for 12c. Once the binaries are in place, we need to setup our environment to connect to the database we want to upgrade.
Oracle Database 11g settings that will be upgraded are:
ORACLE_SID=ora11g
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/11.2.0.3.0/db_1
Next we need to go to the directory where the preupgrd.sql file is located:
# cd /opt/oracle/product/12.1.0.2/dbhome_1/rdbms/admin
Finally, we need to connect to the 11g database with SQL*Plus and run the preupgrd.sql file:
# sqlplus / as sysdba
SQL> @preupgrd.sql
When the preupgrd.sql script is done, we will be given the locations of the files that we need to reference for verifying and correcting any issues with our environment.
Results of the checks are located at:
/opt/oracle/cfgtoollogs/ora11g/preupgrade/preupgrade.log
Pre-Upgrade Fixup Script (run in source database environment):
/opt/oracle/cfgtoollogs/ora11g/preupgrade/preupgrade_fixups.sql
Post-Upgrade Fixup Script (run shortly after upgrade):
/opt/oracle/cfgtoollogs/ora11g/preupgrade/postupgrade_fixups.sql
Review these scripts and correct anything that needs to be fixes. Once these corrections are made, running the DBUA will be simpler. If there are any errors listed in the preupgrade.log, these need to be corrected before proceeding.
2. Running DBUA
Once everything has been corrected after reviewing the preupgrade.log, we can start the Database Upgrade Assistant (DBUA).
To start the DBUA, we need to go to the Oracle Database 12c home and run dbua:
# cd /opt/oracle/product/12.1.0.2/dbhome_1/bin
# ./dbua &
This will start the GUI to begin the upgrade. You will notice that I did not change anything in my environment. I’m still pointing to the 11g environment.
ORACLE_SID=ora11g
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/11.2.0.3.0/db_1
Once the DBUA starts, you will notice that we are at step 1 of 11. The number of steps will change depending on the options that are selected. Step 1, we have two options:
•Upgrade an Oracle Database
•Move an existing 12c database to a new 12c oracle home
For the purpose of the update, we can just click next and move on.
Below are the step by step screenshots.
At this point, the upgrade is done and the DBUA can be closed. Click the ‘Close’ button to exit the GUI.
Verify the Upgrade
There are multiple ways that the upgrade can be verified. The easiest way is the check the /etc/oratab file. Once the upgrade is done, this oratab file should have changed the Oracle home to match the 12c binary location.
Another way to verify is to check the environment variables from the command line:
# env | grep ORA
ORACLE_SID=ora11g
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/12.1.0.2/dbhome_1
Lastly, we can use SQL*Plus to check the version of the database:
# sqlplus / as sysdba
SQL> select banner from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
Upgrade database from 12.1.0.2.0 to 12.1.0.2.1 using Bundle patch (19720843)
Bundle Patch (BP) patches are cumulative. That is, the content of all previous BPs is included in the latest BP patch.
A. If on windows OS, Stop all services.
Distributed Transaction coordinator
OracleServiceora12c
OracleListener
B. export PATH=$ORACLE_HOME/perl/bin:$ORACLE_HOME/OPatch:$PATH
C. unzip p19720843_12102_<platform>.zip
cd 19720843
opatch apply
D. Start services
Distributed Transaction coordinator
OracleServiceora12c
OracleListener
E. For each database instance running on the oracle home being patched, run the 'datapatch' utility
# sqlplus / as sysdba
SQL> STARTUP;
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;
SQL> EXIT;
# cd $ORACLE_HOME/OPatch
# datapatch -verbose
The 'datapatch' utility will then run the necessary apply scripts to load the modified SQL files into the database. An entry will be added to the DBA_REGISTRY_SQLPATCH and DBA_REGISTRY views reflecting the patch application.
SQL> select patch_id,version,status,bundle_series,description from dba_registry_sqlpatch;
PATCH_ID VERSION STATUS BUNDLE_SERIES DESCRIPTION
---------------------------------------------------------------------------
19720843 12.1.0.2 SUCCESS PSU WINDOWS DB BUNDLE PATCH 12.1.0.2.1(64bit):19720843
F. Check the following log files in $ORACLE_HOME/sqlpatch/19648840/ for errors:
19648840_apply_<database SID>_<CDB name>_<timestamp>.log
where database SID is the database SID, CDB name is the name of the multitenant container database, and timestamp is of the form YYYYMMMDD_HH_MM_SS.
In addition, you can check the log files for catbundle in $ORACLE_HOME/cfgtoollogs/catbundle or $ORACLE_BASE/cfgtoollogs/catbundle for any errors:
catbundle_PSU_<database SID>_APPLY_<TIMESTAMP>.log
catbundle_PSU_<database SID>_GENERATE_<TIMESTAMP>.log
where TIMESTAMP is of the form YYYYMMMDD_HH_MM_SS
G. If you are using the Oracle Recovery Manager, the catalog needs to be upgraded. Enter the following command to upgrade it:
$ rman catalog username/password@alias
RMAN> UPGRADE CATALOG;
Patch deinstallation
Shutdown the database and listener and then run the below command to deinstall the patch.
cd 19720843
opatch rollback -id 19720843
I hope this article has shown you how easy it is to upgrade an Oracle Database 11g up to the latest release of the Oracle Database 12c and to apply the bundle patch to upgrade your database to higher patch level i.e from 12.1.0.2 to 12.1.0.2.1.
For any further assistance please don't hesitate to contact me on samiora@gmail.com
