• Arun Kumar

Oracle Database Upgrade from 11g to 12c

As an Oracle DBA, database upgrades is one of the critical tasks that you must be confident about. Time and again, Oracle releases new versions of Oracle database and your client would ask you to upgrade their databases from previous version to latest version.


Reason to upgrade database


Let us first look at few reasons to upgrade your database:

  • New versions have security fixes compared to old versions

  • Latest versions will have lot of bug fixes and this will simplify your job

  • Old version support gets discontinued by Oracle

  • Hackers always attack old versions when you do not upgrade their DB

  • Latest version will have additional features and performance improvements

For the sake of previous reasons, we have made this article, to show you how to upgrade your Oracle 11g database to 12c. You need to know that its highly recommended to backup your database before starting in the upgrading process.


Database upgrade vs migrate


Let us first start with understanding the difference between database upgrade vs database migrate.


Upgrading means converting an already existing database environment which includes installed components and applications into new database environment. This means the data dictionary for the existing database will be upgraded to a new release. During the upgrading process your data will not be touched, moved, changed or even affected by any means.


Migrating means moving data from one database into another previously created one. The process of migration is done when you need to move your database environment to a new operating system or new hardware. Migrating does not contain upgrading to the latest database release, but you may handle upgrading separately after migration.


Oracle upgrade matrix


Before upgrading your database, you need to check Oracle compatibility matrix first and the minimum versions of database that can support Oracle direct upgrade.

oracle_database_matrix

For example, if your current installed database version is 11.2.0.1 then you will need first upgrade to 11.2.0.4 then to 12.2.x. You cannot directly upgrade from 11.2.0.1 to 12.2.x.


Before you upgrade


There are some points that needs to be taken in consideration before upgrading your database. Here you are some of these points:

  • Review the Pre-Upgrade Information Tool provided by Oracle, which you can run on earlier Oracle database versions to check if your version is ready to be upgraded or not.

  • Put a test plan for the upgrading process. And review this plan with all the involved teams.

  • Before upgrading the database, you need to understand first what is the requirements and recommendations of the new version of database. Also, you have to review the additional features provided by the new version of database. For this point you can check Oracle documentation.

  • It is always recommended to practice the upgrading process on test servers first, before upgrading on the production.

  • Take a backup from your database before upgrading. And ensure that the backup has finished successfully and no files are hold in the backup mode.


Database upgrade methods


There are four recommended methods by Oracle to help you upgrade your database safely, lets take a look on the four methods:

  • Oracle Database Upgrade Assistance (DBUA): Database Upgrade Assistance is a graphical user interface tool provided by Oracle that can help you through the upgrade process. This tool can work for both CDB and non-CDB database systems. Its recommended by Oracle to run the Pre-Upgrade Information tool before running the DBUA. To analyze the current database and take needed actions that can minimize the upgrading downtime.

  • Manual Upgrade: It is a command line upgrade method using SQL scripts. It is commonly used by database administrators who need more control over the upgrade process.

  • Oracle Data Pump: In this method, you can use export and import tools provided by Oracle to make a full export from your current database, then perform an import into the new Oracle database release.

  • Using SQL statement CREATE TABLE AS: In this method, you will copy data from the current database into the new Oracle database release.

After you have quickly reviewed the above-mentioned methods, you can choose which one that is best for you. For this article we are going to use the Manual upgrade method.


Pre-upgrade tasks


In this section we will start preparing our Oracle Database for upgrading by preforming some pre-upgrade tasks, but first let’s have a look on some of my environment details that will be used during this tutorial.

  • OS: OEL 6.5

  • Database SID: orcl

  • Database version: 11.2.0.4

  • Oracle 11g home: /u01/app/Oracle/product/11.2.0/dbhome_2

Check current db version


Connect to SQL, and use the next query to get your database version

SELECT * FROM v$version;

As you can see in the below screenshot, the database version is 11.2.0.4 which supports direct upgrade to 12c version

check-db-version

Install 12cR2 software


Download Oracle 12cR2 for Linux from Oracle e-cloud website. Un-compress archived file and start the runInstaller

unzip linuxx64_12201_database.zip
cd database
./runinstaller

Choose “Install the database software only” option

oracle-12c-installer

Continue with the installation process with the default options. After the installation process completes successfully, set your ORACLE_HOME to the

new 12cR2 home

export ORACLE_HOME=/u01/app/oracle/product/12R2

Run pre-upgrade tool


Run the pre-upgrade tool from your new Oracle home

/u01/app/oracle/product/11.2.0/dbhome_2/jdk/bin/java -jar /u01/app/oracle/product/12R2/rdbms/admin/preupgrade.jar FILE DIR /u01/sw

Here we have mentioned the old Oracle home that we need to upgrade. Also, we have mentioned the directory where the pre-upgrade tool files will be saved.

After running the previous command, three files will be created under the “/u01/sw” directory as following:

  • /u01/sw/preupgrade.log → Log file and recommendations by Oracle to follow

  • /u01/sw/preupgrade_fixups.sql → Script to run before upgrading to configure your system for upgrading

  • /u01/sw/postupgrade_fixups.sql→Script to run after upgrading process completes to fix things up

Connect to SQL and run the pre-upgrade fixups script generated from the previous step.

sqlplus / as sysdba

SQL> @/u01/sw/preupgrade_fixups.sql

preupgrade-fixup

As you can see there are some recommendations needed to be done manually as follows:

  • Fix “em_present” issue by executing the next script

SQL> @/u01/app/oracle/product/12R2/rdbms/admin/emremove.sql

emremove-sql
  • Fix “apex_upgrade_msg” issue by upgrading the apex

  • Download apex package from https://www.Oracle.com/technetwork/developertools/apex/downloads/index.html

  • Unzip .zip

  • Change your working directory to apex.

  • Connect to sqlplus and install apex sqlplus / as sysdba @apexins SYSAUX SYSAUX TEMP /i/

install-apex
  • Configure apex: @apex_epg_config.sql /DB/apex/apex

configure-apex
  • Fix “trgowner_no_admndbtrg” by granting administrator privilege:

SELECT OWNER, TRIGGER_NAME FROM DBA_TRIGGERS WHERE
BASE_OBJECT_TYPE='DATABASE' AND OWNER NOT IN (SELECT GRANTEE FROM
DBA_SYS_PRIVS WHERE PRIVILEGE='ADMINISTER DATABASE TRIGGER');

grant ADMINISTER DATABASE TRIGGER to MDSYS;

dba_trigger
  • Fix “invalid_objects_exist” by running the following recompilation script.

  • Fix “amd_exist” issue by running the next script.

/u01/app/oracle/product/12R2/olap/admin/catnoamd.sql

oracle12cupgrade
  • Fix “mv_refresh” issue by running the next procedure.

declare
num_failures integer(3) :=0;
begin
DBMS_MVIEW.REFRESH_ALL_MVIEWS(num_failures,'C','', TRUE, FALSE);
end

oracle_12c
  • Purge Recycle-bin.

PURGE DBA_RECYCLEBIN;

dba-recyclebin
  • Re-run the pre-upgrade fixups script after fixing all the errors, and notice that everything is fixed.

oracle-preupgrade
  • Shutdown the database.

shutdown immediate

oracle-upgrade
  • Stop the listener.

lsnrctl stop


Upgrade the database using manual method

  • Copy the parameter and password file from the old Oracle home to the new 12c home.

cp /u01/app/oracle/product/11.2.0/dbhome_2/dbs/spfileorcl.ora
/u01/app/oracle/product/12R2/dbs

cp /u01/app/oracle/product/11.2.0/dbhome_2/dbs/orapworcl
/u01/app/oracle/product/12R2/dbs

8-oracle-12c
  • Set Oracle home to the new 12c location.

export ORACLE_HOME=/u01/app/oracle/product/12R2

  • Connect to SQL and open the database in upgrade mode.

sqlplus / as sysdba
startup nomount
alter database mount
alter database open upgrade

9-upgrade
  • Run the upgrade tool.

cd /u01/app/oracle/product/12R2/rdbms/admin/

@/u01/app/oracle/product/12R2/perl/bin/perl catctl.pl catupgrd.sql

10-catupgrade-summary

As you can see, it will take some time to finish upgrading the database. So, don’t panic if it took too long because it depends on your machine resources.


Post upgrade tasks

  • Run the post upgrade fixups script.

11-oracle-post-upgrade
  • Fix time zone issue.

Run the check “upgrade_tzv_check” script. (The script is sent separately)
@upgrade_tzv_check.sql

12-oracle-upgrade-timezone
  • Check time zone version before upgrading:

SELECT version FROM v$timezone_file;

13-oracle-timezone
  • Run ““upgrade_tzv_apply”” which will upgrade the time zone.(The script is sent separately)

@upgrade_tzv_apply.sql

14-oracle-upgrade
  • Check time zone version after upgrading.

15-oracle-timezone-file
  • Re-run the post upgrade fixups script.

15-oracle-timezone-file-1

As you can see the time zone issue was fixed.But for the “depend_usr_tables” issue and as recommended by Oracle we will ignore it:


“If Oracle database was Upgraded without using -T option,then the FAILED status can be ignored. But if the Database was Upgraded using -T option. Then you need to execute the utluptabdata.sql script (which is found in $ORACLE_HOME/rdbms/admin/utluptabdata.sql) after the upgrade is complete.”

Post upgrade checks inside the database

  • Check and validate the upgrade process.

@/u01/app/oracle/product/12R2/rdbms/admin/utlu122s.sql

17-oracle-post-upgrade
  • Run the following script to enhance the performance.

@/u01/app/oracle/product/12R2/rdbms/admin/catuppst.sql

  • Run “utlrp.sql” script to compile invalid objects.

@/u01/app/oracle/product/12R2/rdbms/admin/utlrp.sql

18-oracle-sql-procedure
  • Set COMPATIBALE parameter value to 12.2.0. But be careful after changing this parameter to the new value, you will not be able to downgrade your database to any earlier version.

SELECT name, value FROM v$parameter WHERE name = 'compatible';

ALTER SYSTEM SET COMPATIBLE = '12.2.0' SCOPE=SPFILE;

19-oracle-dba-compatible
  • Restart database and check the compatibility parameter again.

Shutdown Immediate;

Startup
SELECT name, value FROM v$parameter WHERE name = 'compatible';

20-oracle-compatible
  • Finally, check your database version.

SELECT * FROM v$version;

21-oracle-dba-version

Oracle Database Downgrade From 12c to 11g


Normally rolling back your code is very easy. But when it comes to rolling back your database to an earlier version it’s a little bit tricky and difficult. In the next sections, we will discuss the following topics:

  • Why to rollback your database?

  • What are the pre-rollback steps to be done?

  • How to perform a successful rollback without losing your data?

  • What are the post-rollback steps to check that everything is back to normal?


Why DB rollback?


Here are some scenarios of why you need to rollback your database or in other words downgrade your database to an earlier version:

  • Sometimes while upgrading your database, the upgrade process terminates unexpectedly.

  • Issue with the control files while opening the database.

  • An error with “catjava” and then the java packages becomes invalid.

  • You may get stuck in the middle of the upgrade process and your system hangs.


Rollback methods


Next, we will give you a quick look on the most common methods for rolling back your database:

  • Restore from a backup:

This is the most straight forward and reliable method. You can restore your database as long as you have a full working backup for your database. Yet this method involves a downtime which may impact your business.

  • Rollback Scripts:

Many system administrators call for a rollback script corresponding to every upgrade script, this is one of the most used approaches by successful system administrators. This forces the involved teams to prepare rollback scripts and often test those scripts in advance.


Before you roll back


Next are the pre-downgrade steps you need to complete first before starting in the downgrade process.

  • Its highly recommended by Oracle that you check your “COMPATIBLE” initialization parameter before starting in the rollback or the downgrade process. By default, Oracle gives you the ability to change the “COMPATIBLE” initialization parameter across your parameter file while upgrading. But once you have changed this parameter, you can not downgrade to a lower release than you have set in the “COMPATIBLE” initialization parameter.

For example, let’s say you have upgraded to Oracle database release 12cR2(12.2) and you have changed the “COMPATIBLE” initialization parameter to (12.1) or higher, then you cannot downgrade to earlier release like 11.2.0.4. But in case you have upgraded to release (12.2) and did not change the “COMPATIBLE” initialization parameter then you can downgrade to 11.2.0.4.

  • To check your “COMPATIBLE” initialization parameter use the next command.

SELECT name, value FROM v$parameter WHERE name = 'compatible';

  • Output should be like that:

22-oracle-compatible
  • Ensure that the destination home that you need to downgrade database to contains the right versions of the time zone files that your database is using.

  • First check your database time zone.

SELECT version FROM v$timezone_file;

23-oracle-timezone-file
  • Go to your destination Oracle home and search for time zone file version 26.

ls -l
/u01/app/oracle/product/11.2.0/dbhome_2/oracore/zoneinfo/*_26.
dat

  • You should find 2 files like the next output:

24-oracle-linux-server
  • If you did not find the files, then copy them from the source Oracle home (which is in our case is 12.2) to the destination Oracle home (11.2.0.4)

cp /u01/app/oracle/product/12R2/oracore/zoneinfo/*_26.dat
/u01/app/oracle/product/11.2.0/dbhome_2/oracore/zoneinfo/

  • Check the state of each component

select substr (comp_id, 1,15) comp_id, substr (comp_name, 1,30)
comp_name, substr (version, 1,10) version, status from dba_registry;

25-oracle-dba-registry
  • Check invalid components.

select owner, count (object_name) "Invalid object count" from
dba_objects where status! = 'VALID' and owner in ('SYS', 'SYSTEM')
group by owner;

26-oracle-object-name

Performing DB roll-back to previous version

  • Shut down the database and start in downgrade mode

shutdown immediate
startup downgrade

27-oracle-shutdown
  • Run the downgrade script from the source Oracle home.

@/u01/app/oracle/product/12R2/rdbms/admin/catdwgrd.sql

28-oracle-12c-upgrade
  • Shut down the database.

shutdown immediate

  • As a root, modify /etc/oratab file to 11.2.0.4 home.

vi /etc/oratab

29-oracle-modify
  • Export Oracle new home.

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_2; export ORACLE_HOME

  • Start listener

lsnrctl START

  • Start DB in upgrade mode.

sqlplus / as sysdba
startup upgrade

30-oracle-upgrade-mode
  • Run the “catrelod” script.

@/u01/app/oracle/product/11.2.0/dbhome_2/rdbms/admin/catrelod.sql


Post rollback tasks

  • Check the registry components.

SELECT comp_name, status, substr (version,1,10) as version from
dba_server_registry order by modified;

As you can see there is an invalid “Oracle Database Catalog Views”, so we will recompile them in the next step.

31-oracle-dba-catalog-view
  • Recompile all existing invalid objects.

@/u01/app/oracle/product/11.2.0/dbhome_2/rdbms/admin/utlrp.sql

  • Finally, check the database version.

SELECT * FROM v$version;

32-oracle-database-version

Recent Posts

See All

MySQL Installation on Linux

This article demonstrates MySQL installation on Oracle Enterprise Linux 6.5 version. Installation of MySQL Make sure you are able to connect internet via virtual machine. Try to ping google.com and pr

Add ISO image storage repository on XenServer

If you have used VMware ESXI, its very easy to upload ISO files to the data store and create VMs. With XenServer, you must create your own ISO repository to upload ISO files. Create ISO directory Acce

Automate RMAN Backups using Shell Scripts

In a real environment, off course you will not manually trigger all the Oracle database backups. You need an automated mechanism to trigger RMAN backups. In this article we will look at RMAN backup au

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback