Upgrading Oracle database is transforming existing Oracle environment into newer version of oracle executables, libraries, configuration files and newer version of existing database. User data won't be affected at all. Database upgrade include following changes:
- Modifying data dictionary.
- Adding, dropping, or modifying columns in system tables and views
- Creating new system packages or procedures
- Modifying existing system packages or procedures
- Creating, modifying, or dropping database users, roles, and privileges
- Modifying seed data that is used by Oracle database components
We can perform Database upgradation in 2 steps.
- Upgrade newer version of Oracle Database Software.
- Perform Oracle database version upgrade.
Minimum Version required to upgrade to 12.1.0.2
- Oracle Database 11g Release 2 11.2.0.2 and later
- Oracle Database 11g Release 1 11.1.0.7
- Oracle Database 10g 10.2.0.5
Unzip Oracle Database software
unzip linuxamd64_12102_database_1of2.zip
unzip linuxamd64_12102_database_2of2.zip
Create new oracle_home
mkdir -p /u01/app/oracle/product/12.1.0.1/db_1
Set new oracle home in ORACLE_HOME environment variable
export ORACLE_HOME=/u01/app/oracle/product/12.1.0.1/db_1
./runInstaller
Above screen will be displayed once runInstaller executable file is executed.
Uncheck "I wish to receive security updates via My Oracle Support". Click on Next to continue.
Warning Message will be displayed. Click on Yes to continue.
I prefer to upgrade database later with dbua command so I have selected Install database software only.
Select Single instance database installation and Click on Next to continue.
Select languages and Click on Next to continue.
Oracle database version is already select and there is no option to select Standard and Standard One. Click Next to continue.
Select Oracle base and Software location. Select newly created Oracle Home directory. Click on Next to continue.
Select appropriate OS groups for OSDBA, OSOPER, OSBACKUPDBA, OSDGDBA, OSKMDBA. Click on Next to continue.
Prerequisite check is on progress.
Click on Save Response File.
On Save Response File window. Select the location and file name and Click on Save.
Click on Install to start database software installation.
Installation Oracle 12c software is on progress. ##########
Execute root.sh script window will be displayed. Execute the script as user root. Once script is executed click on OK to continue.
Database software installation completion window will be displayed. Click on Close.
Before upgrading Oracle database with DBUA. We need to check few stuff.
Verify the oracle database version with SQL command below
select instance_name,host_name, version from v$instance;
Verify component, its version and status from dba_registry, All Component should be VALID before upgradation.
select comp_name,version, status from dba_registry;
Execute preupgrd.sql script
Execute preupgrd.sql script to check prerequisite before upgrading database to 12c. The script file is located on new ORACLE_HOME/rdbms/admin. The new preupgrd.sql script replaces the utlu121i.sql script. This new utility generate 3 files contains detail log of the script execution, fixup script that need to be execute before upgradation and post fix script to be executed after upgradataion.
/u01/app/oracle/product/12.1.0.1/db_1/rdbms/admin/preupgrd.sql
- preupgrade.log
- preupgrade_fixups.sql
- post_upgrade_fixups.sql
On basic of preupgrade.log findings we need to increase process count by at least 300 and remove the Enterprise Manager Database Control repository.
Increasing Process database parameter.
ALTER SYSTEM SET PROESSES=300 SCOPE=SPFILE;
Removing the Enterprise Manager Database Control repository. We can simply execute emremove.sql on the location of New Oracle_home/rdbms/admin.
Before executing dbua set ORACLE_HOME to new Oracle location.
Execute dbau.
Once dbua is executed Operation selection window will be displayed. Click on Upgrade Oracle database and Click on Next.
Select the database that is need to be update. Click on Next to continue.
Prerequisite checks is on progress. ##########
Info message will be displayed. In case there is any failure it need to be fixed prior to database upgradation.
Check Recompile Invalid Object, Upgrade Timezone Data and Gather Statistics and Click on Next.
Check Configure Enterprise Manager (EM) Database Express. Specify the desired Port.
Click on Next to continue. In case we need to move database file check Move Database files option.
If Listener already exists then the upgraded version of database will be registered with the listed listener. In case there is no listener configured we can create a new listener.
Select appropriate option for the backup. This backup will be used to restore in case of failure.
Summary page will be displayed. Click on Finish to begin database upgradation.
Database upgradation is on progress. ##########
Database upgradation is on progress. ####################
Database upgradation is on progress. ##############################
Once Upgradation is completed it will display the summary window.
Click on Close once the upgradation is completed.
