Oracle Database Upgrade from 11g to 12c

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

• Reason to upgrade database

  • Database upgrade vs migrate

  • Oracle upgrade matrix

  • Before you upgrade

  • Database upgrade methods

• Pre-upgrade tasks

• Upgrade the database using manual method

• Post upgrade tasks

• Oracle Database Downgrade From 12c to 11g

  • Why DB rollback?

  • Rollback methods

  • Before you roll back

• Performing DB roll-back to previous version

• Post rollback tasks

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

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

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

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

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

• 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/

• Configure apex: @apex_epg_config.sql /DB/apex/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;

• Fix invalid_objects_exist by running the following recompilation script

SQL> @ORACLE_HOME/rdbms/admin/utlrp.sql

• Fix amd_exist issue by running the next script

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

• 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

• Purge Recycle-bin

PURGE DBA_RECYCLEBIN;

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

• Shut down the database

shutdown immediate

• 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

• 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

• Run the upgrade tool

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

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

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

• Fix time zone issue

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

• Check time zone version before upgrading

SELECT version FROM v$timezone_file;

• Run upgrade_tzv_apply which will upgrade the time zone.(The script is sent separately)

@upgrade_tzv_apply.sql

• Check time zone version after upgrading

• Re-run the post upgrade fixups script

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

Check and validate the upgrade process

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

• 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

• 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;

• Restart database and check the compatibility parameter again

Shutdown Immediate;

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

• Finally, check your database version

SELECT * FROM v$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

• It's 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

• 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;

• 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

• 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;

• 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;

Performing DB roll-back to previous version

• Shut down the database and start in downgrade mode

shutdown immediate
startup downgrade

• Run the downgrade script from the source Oracle home

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

• Shut down the database

shutdown immediate

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

vi /etc/oratab

• Export Oracle new home

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

• Start listener

lsnrctl START

• Start DB in upgrade mode

sqlplus / as sysdba
startup upgrade;

• 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

• 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;