Oracle 18c Upgrade – Windows Failover Cluster 2012 R2

Overview:

I am writing this article to help my colleagues who have queries on how to upgrade to Oracle 18c on a Windows Failover Environment

I have recently completed the Oracle 18c upgrade from Oracle 12c on my Productive environment which is on Windows 2012 R2 Failover Cluster

The sapnote 2660027 is excellent for the oracle upgrade but when it comes to upgrading the Failover environment you need to take care of some extra steps (especially the listener part and removing / adding the database to the cluster group)

Based on my experience, I will try to highlight all the Key points(wherever necessary) which will be imperative to avoid any failures

Pre-requisites:

The following sapnotes are important: Please go through these sapnotes especially 2660027

2660027 – Upgrading to Oracle Database 18c

2660020 – Central Technical Note for Oracle Database 18c

2660044 – Patches for 18c: Database

1865953 – Current Patchset for Oracle Fail Safe (OFS)

The upgrade guide is available at the following url:

https://help.sap.com/viewer/ora_upgr_win_18c

Source Information:

EHP 8 ERP 6 / Kernel 7.53 PL 500

Oracle 12.1.0.2

Windows 2012 R2

OFS 4.1.1

Target Information:

EHP 8 ERP 6 / Kernel 7.53 PL 500

Oracle 18.0.0

Windows 2012 R2

OFS 4.2.1

High Level Steps:

The following are the high-level steps for oracle upgrade (18c)

• Install Windows Powershell 5.1 version
• Install Oracle 12c Patch Bundle
• Install Oracle 18c Software
• Install Oracle 18c Patch Bundle
• Take backup of <drive>:<ORACLE_HOME>\Network\Admin (all *.ora files)
• Remove Oracle database from the cluster group in OFS
• Again,Take backup of <drive>:<ORACLE_HOME>\Network\Admin (all *.ora files)
• Uninstall the Oracle fail safe software (OFS 4.1.1) (both server and client) – on both cluster nodes
• Install the new Oracle fail safe software (OFS 4.2.1) on both cluster nodes
• Perform the Oracle Pre-requisite Tasks as mentioned in sapnote 2660027
• Take all the relevant backups (offline & filesystem)
• Remove the oracle listener using net configuration assistant
• Start the Upgrade using DBUA
• Perform Post-Upgrade Tasks as mentioned in sapnote 2660027
• Start the Oracle failsafe software
• Add the database back to the cluster group in OFS
• Validate the Upgrade

Upgrade Process:

Installing Windows Powershell 5.1:

As a basic pre-requisite upgrade your windows powershell to version 5.1

Installing oracle 12c patch bundle

Download the latest patch bundle and generic patches from SAP software download center:

Always use the latest patch bundle

To apply the oracle patch bundle, refer sapnote: 839182 – Oracle patch installation with OPatch

NOTE: patch bundle must be applied on both the cluster nodes

Before applying the patch bundle move the all resources to secondary node including the quorum. This is needed because to apply the patch bundle we need to stop failover cluster service.

The following Windows Services must be stopped before applying the patch bundle

• Stop all SAP services
• Stop all Oracle services
• Stop Distributed link cordinator
• Stop Windows Instrumentation management
• Stop cluster failover service

Now you can apply the patches using ‘opatch apply‘ command

once all the patches are applied, REMEMBER to start the Cluster Failover Service

Installing Oracle 18c Software

Next step,we install the oracle 18c software

(NOTE: in oracle upgrade guide it is mentioned to remove the oracle database from the resource group and then to install the oracle 18c software. However, I removed the group only after installing the oracle 18c software and its bundle patches. This was done to optimize my recovery option)

NOTE: install the software on both the cluster nodes

Oracle 18c Patch Installation:

refer sapnote: 839182 – Oracle patch installation with OPatch for patch installation commands

NOTE: Here, you may receive the following error while applying oracle 18c patch bundle 1907:

This error is mentioned in sapnote 2660027 and the workaround for this error is also mentioned in the sapnote which is:

##############################################

Symptom

Verifying environment and performing prerequisite checks…
Prerequisite check “CheckActiveFilesAndExecutables” failed.
The details are:

Following files are active :
<ORACLE_HOME>\bin\msvcr120.dll
<ORACLE_HOME>\bin\msvcr120.dll
UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables” failed.

OPatch failed with error code = 73

Here’s a list of files that can be still in use:

<ORACLE_HOME>\bin\msvcr120.dll
<ORACLE_HOME>\jdk\bin\msvcr100.dll
<ORACLE_HOME>\jdk\jre\bin\msvcr100.dll
<ORACLE_HOME>\jdk\jre\bin\plugin2\msvcr100.dll

Cause

unknown, Oracle home has been fresh installed.

Workaround

1. Open a command or powershell prompt.
2. Rename the corresponding file(s) that were reported as ‘active’.
E.g. CMD> RENAME MSVCR120.DLL MSVCR120.DLL.OPATCH_APPLY
E.g. CMD> RENAME MSVCR100.DLL MSVCR100.DLL.OPATCH_APPLY
3. Rerun ‘opatch apply’ command.
4. After successful applying the patch with OPatch, delete the files that were renamed in step 2.
E.g. CMD> DELETE MSVCR120.DLL.OPATCH_APPLY
E.g. CMD> DELETE MSVCR100.DLL.OPATCH_APPLY

##############################################

You can apply the patch bundle using sapnote 839182 – Oracle patch installation with OPatch

Again, as done for oracle 12c patch bundle, you must stop the following services:

• SAP services
• Oracle services
• Distributed link cordinator
• WMI service

Removing database from the cluster group

Now this is a very important step in the upgrade process and we must take good care while performing this step

It is highly recommended to take backup of all files(listener.ora, sqlnet.ora & tnsnames.ora) in NETWORK\ADMIN subfolder of ORACLE_HOME both before removing the oracle database from the cluster group and after removal of the database from the cluster group.

To do this activity log on with your cluster administrator account on the primary node(with quorum drive)

open the oracle fail safe, right-click on the oracle resource group and select remove. Alternatively, you can also use the powershell script ‘RemoveOracleDbFromOracleClusterGroup.ps1’ from <ORACLE_HOME>\SAP\ora_mscs folder

After you have removed oracle database from the cluster group, you must take backup of all files(listener.ora, sqlnet.ora & tnsnames.ora) in NETWORK\ADMIN subfolder of ORACLE_HOME_OLD and ensure that the hostname points to the Physical HOSTNAME of the primary node

Uninstalling OFS4.1

To Uninstall OFS4.1, goto OFS 4.1 media and run the following file as administrator

sapofs.cmd

Here, you need to select on Installed Products and then select the OFS 4.1 version and then click ‘Remove’

Its better to restart the servers after the removal of OFS 4.1

Installing Oracle failsafe 4.2.1

oracle failsafe 4.2.1 is available at following path:

support.sap.com->Software downloads->databases->Oracle->Downloads->Oracle Patches->Oracle patches miscallaneous

once you have downloaded the OFS4.2.1, begin the installation using cluster administrator user

you must install OFS on both the nodes

This is a simple installation and you can perform this installation by following the steps mentioned in the upgrade guide

Pre-requisite Tasks

To perform pre-requisite tasks follow sapnote 2660027

NOTE:

Here, in reference to listener migration, better to follow the recommendation mentioned in the sapnote 2660027 (which is to perform manual migration of local listener)

This step needs to be done only on the primary node.

DO NOT PERFORM LOCAL LISTENER MIGRATION ON SECONDARY NODE (this is valid for cluster setup)

IMPORTANT:

delete old failsafe listener on the primary node and delete old standard listener on the primary node using net configuration assistant

Perform Backup

Before starting the Upgrade Take:

• Offline database backup
• backup of both old oracle home & new oracle home
• kernel backup

Running Upgrade with DBUA

Now once you have successfully completed all the pre-requisite tasks mentioned in sapnote 2660027 and ensured that there are no invalid objects then you can proceed with upgrade

Use DBUA tool to run the upgrade process

Goto oracle18c home on all programs

run the Database Upgrade Assistant

Here, I followed the inputs provided in sapnote 2660027

Next screen, enter the password for oracle home user

Here, enter the path for post_upgrade.tasks.sql & compatible.sql scripts in After Upgrade section

It’s recommended to create Guaranteed restore point (GRP) as mentioned in sapnote 2660027

Also, You MUST perform OFFLINE DATABASE BACKUP before starting the upgrade

Since I had taken offline backup I selected the option ‘I have my own backup and restore strategy’

Next, keep everything empty in the listener screen

Here, uncheck the box for EM database express

Finally, the summary screen: cross-check the details and begin the upgrade

Post-Upgrade tasks:

Now you must perform all the post-upgrade tasks as mentioned in sapnote 2660027

also in preparation for adding the database to cluster group, remember to create a junction link between \ORACLE_HOME\database & <SHARED DRIVE>:\oracle\SID\sapdata1\database

This part is mentioned in NW Installation guide under High Availability section:

Copy sqlnet.ora from primary node to secondary node

Adding Database back to Cluster Group

Before adding database to cluster group, open the database manually using the pfile from the share drive:

First,stop the database instance

• shutdown immediate;

then start the database instance using the pfile from the shared drive:

• startup pfile='<SHARED DRIVE>:\oracle\SID\sapdata1\database\initSID.ora’

Now, logon to primary node using cluster administrator user id

NOTE: Before adding the database to the cluster group,

• take backup of .ora files (listener.ora, tnsnames.ora,sqlnet.ora)
• ensure the hostname in listener.ora files point to the physical hostname of the primary node and not the virtual hostname

Add the database to cluster group using the powershell script ‘AddOracleDbToOracleClusterGroup.ps1’ available in folder <ORACLE_HOME>\SAP\ora_mscs

you must enter here ‘initSID_OFS.ora’ file path which is created as part of the post-upgrade tasks mentioned in oracle upgrade guide and sapnote 2660027

After the database is successfully added to the cluster group ensure the following points:

• restart the database using the pfile from \ORACLE_HOME\database folder (for eg: startup pfile=’\ORACLE_HOME\database\initSID.ora’
• Ensure, listener.ora and tnsnames.ora contains virtual hostname and NOT local hostname
• check SID_LIST_<FailsafeListener> parameter in listener.ora
• update the database statistics
• adapt database parameters
• maintain the virtual hostname in the tnsnames.ora in \sapmnt\SYS\profile\oracle folder (this is important otherwise the application servers will not start without the virtual hostname)

Re-Start SAP System

Now you can restart the SAP system and validate your upgrade is successfully completed.

(its always better to take backup after the upgrade as well)

I hope this would be of help to all those people who are planning to upgrade their oracle database to version 18c on windows failover cluster

Let me know if you have any queries on the post.