Oracle Database Upgrade to 12c (In a Fail Safe Environment)

This a step-by-step detailed procedure for upgrading one Oracle Database 11gR2 (11.2.0.4) on Windows Server 2012 to the latest version 12c (12.1.0.2) with the latest available Windows Bundle Patch. The database is configure with Fail Safe (Active/Passive) cluster to provide some sort of high availability. This is an actual live upgrade for one of my customers.

Although this is a long post, but I hope it will be of a good value for any person doing the same task in the future.

Current Environment:

What is Fail Safe?

Oracle Fail Safe is a clustering solution from Oracle. It is an extra layer above “Microsoft Failover Cluster”.

Usually, this type of cluster consists of two servers with shared storage, and configured as Active/Passive cluster.

This means that one node will own the storage at any given point of time. This node will have the database up and running. Whenever there is any issue with node, the cluster will failover the database (and the shared storage) to the other node. End users see the cluster as one entity with one virtual IP, they don’t care which is the active node?. They got the same service from whatever active node. There are more complex cluster configuration, but let us keep it simple. Fail Safe is similar to “RAC One Node”, but it is not based on the Oracle Clusterware, and most important it is free!.

Upgrade Plan

• Check certification/Compatibility between all components.
• Install Oracle Software 12c (12.1.0.2) along with the latest patches on both nodes.
• Remove the 11gR2 Database from its FS group
• Upgrade the Database to 12c
• Verify the 12c Standby Database
• Add the database back to its original FS Group
• Finishing Tasks
• Testing and Go-Live

We will proceed with the above plan step-by-step

Check certification/Compatibility between all components

I’m lucky as the installed Fail Safe version is the latest one (4.1.1), so, need for any patching at the Fail Safe level.

Let us check all certification of the different components through Oracle Support

Fail Safe Certification with Windows

Fail Safe is certified with Windows 2012

Fail Safe Certification with Oracle 12c

Oracle FS is supported with the 12c (Non-CDB)

Oracle Fail Safe Release 4.1.1, is supported in a multitenant container database environment (FS 4.1 is not supported).

Non-CDB or CDB Configuration

In database 12c, you can configure a normal database (Non-CDB or not-Multi-Tenant Database as the of 11g) or you may configure a CDB (or Multi-Tenant Database). So, which configuration should we choose for our upgrade?

From the Oracle Upgrade Blog:

https://blogs.oracle.com/UPGRADE/entry/non_cdb_architecture_of_oracle

you will find the following statement:

“Beginning with Oracle Database 12.1.0.2 a non-CDB architecture is deprecated”

But in the same blog in the comments:

I just can tell you now that this (Single/Multi-Tenant is a must) won’t happen in Oracle 12.2.

We officially say (and write it down) that all potential architectures (non-CDB == as you know it, CDB with either one PDB or many) will be available free of choice until at least Oracle Database 12.2.

So, we still have time to enjoy the Non-CDB configuration.

So, my final decision is to go with Non-CDB for the following reasons:

There are many features that are still not available with the CDB/PDB architecture like:

• Database Change Notification
• Continuous Query Notification (CQN)
• Client Side Cache
• Heat Map
• Automatic Data Optimization
• Oracle Streams
• Flashback Pluggable Database (Flashback Database works but will flashback CDB$ROOT including all PDBs)
• DBVERIFY
• Data Recovery Advisor (DRA)
• Flashback Transaction Backout

Officially, you can use Non-CDB with version 12.1.0.2 and the coming 12.2 without any problem, so we have a plenty of time to use Non-CDB

There is no critical need for my client for using CDB/PDB architecture at the moment              We don’t have time luxury to retest the application again in PDB

Preparations

To reduce the down time, the new 12c version can be installed on each node while the old 11gR2 database is still running in the Fail Safe group.

Backup the 11g database

Ensure consistent stat of the cluster

Unzip The Software on both nodes

On Node1:

Extract the 12102 software:

Also, extract the patch p22809813_121020_MSWIN-x86-64:

Repeat the extraction process on Node2

Ensure that you are login to Node 1 with the “Domain Administrator”, which is the user used initially to install the whole cluster.

Stop the currently running Oracle Services on Node 1 (just for safety!):

Note that the current:

11g ORACLE_HOME=C:\app\Administrator\product\11.2.0\dbhome_1

11g ORACLE_BASE= C:\app\Administrator

The current “Regedit” view is as follows:

Install Oracle Software 12c (12.1.0.2) along with the latest patches on both nodes.

Install Oracle Database 12c 12.1.0.2 on Node1

Start the OUI on node1:

It looks like, it is a must to specify non-administrator user in 12c !

Here I need to have a big stop regarding the above screen of choosing Oracle Home owner!!!

As per 12c Oracle Documentation, you can choose an existing Domain or Local user or you can create a new local user as I selected above. This is clear in the following part of the Database Installation Guide:

So, there is no problem of choosing local windows user to be the software owner, Actually I completed the installation successfully and upgraded the database successfully, But at the last step od adding the database back to the Fail Safe cluster, I got an error. After searching about this error, I found that the fix of this error is to use a Domain User as a software owner when using 12c with Fai Safe. This is not documented in any place. I just found the following screen shot on one tutorial:

But this was an example, not a mandatory requirements mentioned.

So, to save your time, please don’t use local user as a software owner. I had to create a domain user called “oinstall” and pass it in the above screen. But I had first to clean my 12c installation on both nodes and re-install them again.

<<<< Don’t use local user a software owner, this is a life safer! >>>>

<<<< Don’t use local user a software owner, this is a life safer! >>>>

<<<< Don’t use local user a software owner, this is a life safer! >>>>

Following is the Windows Services status after the installation:

Install Oracle® Database Windiows Bundle Patch (22809813) version 12.1.0.2.160419

As per the patch readme file:

You must use the OPatch utility version 12.1.0.1.2 or later to apply this patch. Oracle recommends that you use the latest released OPatch 12.1, which is available for download from My Oracle Support patch 6880880 by selecting the 12.1.0.1.0 release.

Install the latest Opatch Utility

The current installed opatch version available with 12102 is:

Although the current installed opatch version is ok, but I decided to download and install the latest opatch

The latest version is 12.1.0.1.12 as per:

Download the above opatch and move to both nodes:

To install the latest opatch patch you just need to unzip it under the ORACLE_HOME after deleting the old OPatch directory. Now the installed opatch version is 12.1.0.1.12

Windows BP Installation (2 min):

Now follow the following steps to install the Windows BP:

Shut down all instances and listeners associated with the Oracle home that you are updating

• We don’t have right now, except the following service:

Explicitly stop the “Distributed Transaction Coordinator” service (which is not an Oracle service) if it is running.

• net stop msdtc

Open CMD window

set ORACLE_HOME=D:\app\pisdb\product\12.1.0\dbhome_1

set PATH=%ORACLE_HOME%\perl\bin;%PATH%

D:\app\pisdb\product\12.1.0\dbhome_1\OPatch\opatch apply

But I got the following errors:

E:\DB12102\22809813>D:\app\pisdb\product\12.1.0\dbhome_1\OPatch\opatch apply

Exception in thread “main” java.lang.NoClassDefFoundError:

D:\app\pisdb\product\12/1/0\dbhome_1\oui\lib\emCfg/jar;

D:\app\pisdb\product\12/1/0\dbhome_1\oui\lib\OraInstaller/jar;

D:\app\pisdb\product\12/1/0\dbhome_1\oui\lib\OraPrereq/jar;

I found that there is an extra space on the above ORACLE_HOME setting. It should be:

set ORACLE_HOME=D:\app\pisdb\product\12.1.0\dbhome_1

instead of:

set ORACLE_HOME= D:\app\pisdb\product\12.1.0\dbhome_1

I corrected and run the opatch again:

Bundle Patch was applied successfully.

Check the installed patches:

D:\app\pisdb\product\12.1.0\dbhome_1\OPatch\opatch lsinv

Install Oracle Database 12c 12.1.0.2 on Node2

Repeat the same steps done above at Node1 on Node2

Although, it should be safe to install 12c while the 11gR2 database is running on Node2, I decided to switchover the database and cluster to Node1, while installing on Node2.

Upgrade the Oracle Database to 12c

Run the Pre-Upgrade Information Tool (preupgrd.sql)

Copy preupgrd.sql and utluppkg.sql from the D:\app\oinstall\product\12.1.0\dbhome_1\rdbms\admin directory to C:\temp

set ORACLE_HOME=C:\app\Administrator\product\11.2.0\dbhome_1

set PATH=%ORACLE_HOME%\bin

set ORACLE_SID=PIS

cd c:\temp

sqlplus / as sysdba

@c:\temp\preupgrd.sql

A subdirectory or file C:\app\Administrator\product\11.2.0\dbhome_1\rdbms\log\ already exists.

Loading Pre-Upgrade Package…

********************************************************************

Executing Pre-Upgrade Checks in PIS…

***************************************************************************************************************************************

====>> ERRORS FOUND for PIS <<====

The following are *** ERROR LEVEL CONDITIONS *** that must be addressed prior to attempting your upgrade.

Failure to do so will result in a failed upgrade.

1) Check Tag:    PURGE_RECYCLEBIN

Check Summary: Check that recycle bin is empty prior to upgrade

Fixup Summary:

“The recycle bin will be purged.”

You MUST resolve the above error prior to upgrade

************************************************************

************************************************************                ====>> PRE-UPGRADE RESULTS for PIS <<====

ACTIONS REQUIRED:

1. Review results of the pre-upgrade checks:

C:\app\Administrator\cfgtoollogs\PIS\preupgrade\preupgrade.log

2. Execute in the SOURCE environment BEFORE upgrade:

C:\app\Administrator\cfgtoollogs\PIS\preupgrade\preupgrade_fixups.sql

3. Execute in the NEW environment AFTER upgrade:

C:\app\Administrator\cfgtoollogs\PIS\preupgrade\postupgrade_fixups.sql************************************************************

********************************************************************

Pre-Upgrade Checks in PIS Completed.

********************************************************************

********************************************************************

********************************************************************

SQL> @C:\app\Administrator\cfgtoollogs\PIS\preupgrade\preupgrade_fixups.sql

Pre-Upgrade Fixup Script Generated on 2016-05-23 11:52:15  Version: 12.1.0.2 Build: 012

Beginning Pre-Upgrade Fixups…

Executing in container PIS

********************************************************************

Check Tag:     EM_PRESENT

Check Summary: Check if Enterprise Manager is present

Fix Summary:   Execute emremove.sql prior to upgrade.

********************************************************************

Fixup Returned Information:

WARNING: –> Enterprise Manager Database Control repository found in the database

In Oracle Database 12c, Database Control is removed during

the upgrade. To save time during the Upgrade, this action

can be done prior to upgrading using the following steps after

copying rdbms/admin/emremove.sql from the new Oracle home

– Stop EM Database Control:

$> emctl stop dbconsole

– Connect to the Database using the SYS account AS SYSDBA:

SET ECHO ON;

SET SERVEROUTPUT ON;

@emremove.sql

Without the set echo and serveroutput commands you will not

be able to follow the progress of the script.

*******************************************************************

********************************************************************

Check Tag:     AMD_EXISTS

Check Summary: Check to see if AMD is present in the database

Fix Summary:   Manually execute ORACLE_HOME/oraolap/admin/catnoamd.sql script to remove OLAP.

********************************************************************

Fixup Returned Information:

INFORMATION: –> OLAP Catalog(AMD) exists in database

Starting with Oracle Database 12c, OLAP Catalog component is desupported.

If you are not using the OLAP Catalog component and want

to remove it, then execute the

ORACLE_HOME/olap/admin/catnoamd.sql script before or

after the upgrade.

********************************************************************

********************************************************************

Check Tag:     PURGE_RECYCLEBIN

Check Summary: Check that recycle bin is empty prior to upgrade

Fix Summary:   The recycle bin will be purged.

********************************************************************

Fixup Succeeded

********************************************************************

********************************************************************

Check Tag:     APEX_UPGRADE_MSG

Check Summary: Check that APEX will need to be upgraded.

Fix Summary:   Oracle Application Express can be manually upgraded prior to database upgrade.

********************************************************************

Fixup Returned Information:

INFORMATION: –> Oracle Application Express (APEX) can be

manually upgraded prior to database upgrade

APEX is currently at version 3.2.1.00.12 and will need to be  upgraded to APEX version 4.2.5 in the new release.

Note 1: To reduce database upgrade time, APEX can be manually upgraded outside of and prior to database upgrade.

Note 2: See MOS Note 1088970.1 for information on APEX installation upgrades.

********************************************************************

********************************************************************

[Pre-Upgrade Recommendations]

********************************************************************

*****************************************

********* Dictionary Statistics *********

*****************************************

Please gather dictionary statistics 24 hours prior to upgrading the database.

To gather dictionary statistics execute the following command while connected as SYSDBA:

EXECUTE dbms_stats.gather_dictionary_stats;

^^^ MANUAL ACTION SUGGESTED ^^^

**************************************************

************* Fixup Summary ***********

1 fixup routine was successful.

3 fixup routines returned INFORMATIONAL text that should be reviewed.

**************** Pre-Upgrade Fixup Script Complete *********************

So, based on the outcome from the preupgd.sql script, I’m going to take the following actions:

• Remove the EM:

Copy the D:\app\pisdb\product\12.1.0\dbhome_1\rdbms\admin\emremove.sql to c:\temp

emctl stop dbconsole

sqlplus / as sysdba

SET ECHO ON;

SET SERVEROUTPUT ON;

@emremove.sql

• Remove the OLAP Catalog component as follows:

@%ORACLE_HOME%\olap\admin\catnoamd.sql

Where ORACLE_HOME here is referring to the 11g home

• Purge Recyclebin è already done by the sql script
• Gather dictionary statistics:

EXECUTE dbms_stats.gather_dictionary_stats;

• There are 181 invalid objects (non-sys objects)

select owner,object_type,count(*) from sys.registry$nonsys_inv_objs group by owner,object_type order by owner,object_type;

Compile invalid objects:

@?/rdbms/admin/utlrp.sql

This reduced the number of invalid objects from 181 to 98

I will run the preupgrd.sql script again after the above fixes:

@c:\temp\preupgrd.sql

Now there is no errors

Remove the database from the Fail Safe Configuration

On Node1, from the “Fail Safe” Manager:

Now the group contains the following resources:

Now, the Oracle services on Node1 looks as follows:

and, the Oracle services on Node2 looks as follows:

Upgrade the Database using the DBUA

Take another final offline backup for the database.

Start > Programs > Oracle-HOME_NAME > Configuration and Migration Tools >Database Upgrade Assistant.

{{Take care: Start the DBUA from the new oracle home 12c, not that of 11g!!}}

I asked to create a new non-default listener , until we register with the FS listener.

M:\ is the shared storage

Actually we are using pfile with FS not spfile

I clicked “Abort” in the above dialog

I created spfile from pfile:

It looks it continued successfully after that!

Upgrade Results

Database upgrade has been completed successfully, and the database is ready to use.

	Source Database	Target Database
Name:	PIS	PIS
Release:	11.2.0.4.0	12.1.0.2.0
Oracle Home:	c:\app\administrator\product\11.2.0\dbhome_1	D:\app\pisdb\product\12.1.0\dbhome_1

Pre-Upgrade Checks

Warnings

Database contains INVALID objects prior to upgrade. The list of invalid SYS/SYSTEM objects was written to registry$sys_inv_objs. The list of non-SYS/SYSTEM objects was written to registry$nonsys_inv_objs unless there were over 5000. Use utluiobj.sql after the upgrade to identify any new invalid objects due to the upgrade.

Oracle Application Express (APEX) can be manually upgraded prior to database upgrade.

Upgrade Details

The following summary lists the steps performed during the upgrade process. Log files for all the steps, as well as this summary, are available at “D:\app\pisdb\cfgtoollogs\dbua\PIS\upgrade1”.

Step Name	Log File Name	Status
Database Backup	Backup.log	Successful
Pre Upgrade	PreUpgrade.log	Successful
RDBMS Upgrade	Oracle_Server.log	Successful
Post Upgrade	PostUpgrade.log	Successful
Enterprise Manager Configuration	emConfigUpgrade.log	Successful
Timezone Upgrade	UpgradeTimezone.log	Successful
Generate Summary	generateSummary.log	Successful

Step Execution Information:

Database Backup

ORA-01565: error in identifying file ‘%ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA’ORA-27041: unable to open file

The Database can be restored by running the following script “M:\Oracle\PIS\FLASH_RECOVERY_AREA\PIS\PIS_restore.bat” from the backup location.NOTE: This operation will remove all the upgrade changes made by the Database Upgrade Assistant.

Post Upgrade

A persistent initialization parameter file (spfile) has been created at the following location: D:\app\pisdb\product\12.1.0\dbhome_1\database\spfilePIS.ora.

Enterprise Manager Configuration

The Database will be managed by Enterprise Manager Database Express.

https://PIS01.UNC.COM:5500/em

Timezone Upgrade

The time zone version and TIMESTAMP WITH TIME ZONE data of the Database has been successfully upgraded from version 14 to version 18.

Initialization Parameter changes

The following changes have been made in the initialization parameters:

Parameters Added:

Name	Value
pga_aggregate_limit	2147483648

Parameters Updated:

Name	Old Value	New Value
diagnostic_dest	C:\APP\ADMINISTRATOR	D:\app\pisdb

Parameters Commented:

Name	Value
local_listener	“(ADDRESS=(PROTOCOL=IPC)(KEY=REGISTER_FslPISDB))”

Now Oracle services at Node1 looks like:

As a validation:

Post Upgrade Tasks

As a post upgrade steps:

• Collect fixed objects statistics:

EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

• Change the database to run in archivelog mode(This is independent of the upgrade process):

• Compile invalid objects again:

@?/rdbms/admin/utlrp

• Testing the EM Database Express 12c connection:

https://pis01:5500/em

Looks like it is working (from iexplorer, but has some issues with Chrome)

Verify the 12c Standby Database

Now it is the time to bring back our database to the cluster umbrella. The first step is to validate this database from the “Fail Safe Manager”.

Before doing that, and as I had bad experience configuring database with spfile with Fail Safe, I’m going to create a pfile from the current spfile and put it on the shared storage.

Backup the old pfile/spfile(s)

Sqlplus / as sysdba

Create pfile=’M:\Oracle\PIS\Database\initPIS.ora’ from spfile;

Now run the “Fail Safe Manager” to verify the standalone database:

It failed as expected, as the database was started using a local spfile.

As another test case I moved the spfile to the shared storage and created a new pfile in the shared storage also that contains the following line:

SPFILE=’M:\Oracle\PIS\Database\SPFILEPIS.ORA’

I stopped the database and started it again with the above pfile/spfile that reside on the shared storage.

Now I’ll try to validate the standalone database again:

The validation passed.

Add the database back into the group

I doubt that the above error is due to the referring to spfile, but to isolate this point, I deleted the one-line file init.ora file and used the original pfile, and I’ll not use any spfile at this stage.

Restart the database with the pfile:

‘M:\Oracle\PIS\Database\initPIS.ORA’

Server parameter files (spfile) are now required to be stored on a shared disk. The text parameter file (pfile) must contain a spfile reference to the server parameter file. The text parameter file may be stored on a local disk if there are node specific parameters that need to be set. In general, unless there is a need to use different parameters on different cluster nodes, Oracle suggests storing the text parameter file on a shared disk.

Revalidate the standalone database

Now try to add the database to the group, following the same above steps:
I got the following error:

0x523: The name provided is not a properly formed account name

As I mentioned earlier this error was due to using local windows owner as the oracle software owner, after using a domain user, I was able to add the database successfully to the Fail Safe Group.

References:

Fail Safe Tutorial

https://docs.oracle.com/cd/E59133_01/doc.411/e57061/add_sidg.htm#OFSTU164

Database Upgrade Guide

https://docs.oracle.com/database/121/UPGRD/upgrade.htm#UPGRD003

 -Oracle® Fail Safe Concepts and Administration Guide

Release 4.1.1

for Microsoft Windows

E57057-01

Thanks
Ahmed