DMK_HA virtual IP solution for Microsoft Windows

The script called “dmk_vip.ps1” manages such VIP, it is furnished within this package and it allows to manage a vitual IP linked to a database in the same manner we recommend on Unix/Linux systems.

Purpose

The script “dmk_vip.ps1” allows starting and stopping a VIP configured in a database context. The VIP configuration goes through a simple and easy configuration file (see later).

The purpose of the script is to manage VIP. It is easy to manage and robust. The main specifications of the tool are the following ones :

• “dmk_vip.ksh” can manage several VIP (one per database)

• before starting up a VIP it must make sure that the same VIP is not running somewhere else (in this case this VIP must be stopped)

Specifications

The VIP specification consist in a list of parameters:

• Oracle instance: SID

• Address of the VIP (IP which will be used by Oracle clients to connect to the database)

• Network mask in use for the VIP definition

• Physical network interface to bind the VIP to

• Number of iterations to stop the VIP if the VIP is already running of multiple nodes (default: 2)

Requirements

In order to be able to start a VIP on a Microsoft Windows network interface, the dmk_vip.ps1 requires Administrator privileges to access the WMI classes and Netsh utility from PowerShell.

1. Add to the PowerShell console “Run as Administrator” privilege per default

   • Navigate to Powershell console Advanced Properties (right click on the shortcut)

   • Depending on the Microsoft Windows version you might find the “Run as Administrator” property either in the security or advanced tab

2. Verify the Oracle database Windows Service (OracleService<ORACLE_SID>) starts with a domain user

   • Required to manage the Virtual IP remotely

3. Windows Firewall configured to

   • Allow remote queries on the WMI classes

4. Proper name for the public network interface (ie: LAN)

⚠️ Wrong firewall settings (Blocked WMI classes and no answer from ping) can potentially lead to split-brain situations! For instance “duplicate Virtual IP”

Follows the required FireWall enabled Inbound rules Microsoft Windows Server 2012:

• File and Printer Sharing (Echo Request - ICMPv4-In)

• Windows Management Instrumentation (DCOM-In)

• Windows Management Instrumentation (WMI-In)

Use following PowerShell command to check the state and scope of a rule:

Get-NetFirewallRule | Select DisplayName,profile,enabled| Where {$_.DisplayName -like 'File*ICMPv4-In'}

Enable those inbound rules from a PowerShell Console as follows:

Set-NetFirewallRule -DisplayName "File and Printer Sharing (Echo Request -
ICMPv4-In)" -profile any -enabled True
Set-NetFirewallRule -DisplayName "Windows Management Instrumentation (DCOMIn)" -profile any -enabled True
Set-NetFirewallRule -DisplayName "Windows Management Instrumentation (WMIIn)" -profile any -enabled True

Configure the WinRM service, to start automatically with your system and adapt the firewall rules:

Enable-PSRemoting -Force

Configuration

The complete “dmk_vip.ps1” configuration should be performed in the dmk_vip.conf file located in $DMK_HA/etc. A template configuration file available in $DMK_HA/templates:

cp ${DMK_HA}/templates/dmk_vip.cfg.win \\
 ${DMK_HA}/etc/dmk_vip.cfg

The syntax of the configuration file is presented below:

#---------------------------------------------------------------------------
# Configuration file for VIP management of databases
#
# PURPOSE :
# Configures VIP attached to Oracle databases
#---------------------------------------------------------------------------
# Configuration format :
#
# SID:VIP_ADR:VIP_MASK:VIP_IF:
#
# SID : Oracle SID for which the VIP is configured - mandatory
# VIP_ADR : Address of the VIP - mandatory
# VIP_MASK : VIP Network address Mask - mandatory
# VIP_IF : Network interface to attach the VIP (i.e Local Area
Connection) - mandatory
# NBR_NODES : Number of nodes
# VIP_Node1 : Name of Node 1 - mandatory
# VIP_Node2 : Name of Node 2 - mandatory
#
# Note :
#
# The usage of parameters delimitors allows to have potentially empty
# (not defined parameters)
#---------------------------------------------------------------------------
# Example:
#CDB1:172.22.30.33:255.255.255.0:LAN:2:win2012r2ora1:win2012r2ora02

The VIP (parameter VIP_ADR) will be started on the physical interface (VIP_IF).

It is possible to define a number of iteration during dmk_vip.ksh will trying to stop existing VIPs with the same address. If, due to some wrong manipulations, the VIP has been started on several nodes, the dmk_vip.ps1 script will try to stop all of them before starting the VIP on the local node. Per default these "zombies" VIP will be stopped on maximum two nodes. If the Data Guard or Dbvisit cluster has 4 nodes, it might be useful to try to stop the VIP "zombies" on all the nodes of the cluster before starting it on the local node, therefore increase NBR_NODES to 4.

⚠️Usually the dmk_vip.cfg file is the same on all nodes of the cluster, do not forget to deploy it on all nodes after any modification.

Usage

Configuration: dmk_vip.cfg

CDB1:172.22.30.33:255.255.255.0:LAN:2:win2012r2ora1:win2012r2ora02

Start the VIP:

Oracle@WIN2012R2ORA1:C:\\\\ [rdbms12101] dmk_vip.ps1 start CDB1
…

Stop the VIP:

Oracle@WIN2012R2ORA1:C:\\\\ [rdbms12101] dmk_vip.ps1 stop CDB1
…

Check Virtual IP on interface LAN (VIP 172.22.30.33):

Oracle@WIN2012R2ORA1:C:\\\\ [rdbms12101] ipconfig.exe
Windows IP Configuration
Ethernet adapter lan:

 Connection-specific DNS Suffix . :
 Link-local IPv6 Address . . . . . : fe80::e867:350b:9659:8f67%12
 IPv4 Address. . . . . . . . . . . : 172.22.30.31
 Subnet Mask . . . . . . . . . . . : 255.255.0.0
 IPv4 Address. . . . . . . . . . . : 172.22.30.33
 Subnet Mask . . . . . . . . . . . : 255.255.255.0
 Default Gateway . . . . . . . . . : 172.22.1.1

Database integration/automation

The “dmk_vip.ps1” integration with the database is quite simple as it consists of two (2) components:

• A STARTUP_TRIGGER which fires after “startup on database” and if the current database role (V$DATABASE.database_role) is PRIMARY.

• Executable DBMS_SCHEDULER jobs VIP_STOP and VIP_START

⚠️Please create the STARTUP_TRIGGER and the DBMS_SCHEDULER jobs as user SYS. DBMS_SCHEDULER jobs are executed as O.S user “ORACLE” which must exist in the Windows Active Directory. Otherwise the script cannot manag the VIP remotely. Additionally, The Oracle Scheduler cannot start a PowerShell script; Therefore a small wrapper script “dmk_vip.bat” is called by the job.

Install trigger and scheduler jobs as follows:

SQL> @${DMK_HA}/sql/create_trigger_for_dmk_vip_win.sql
SQL> @${DMK_HA}/sql/create_job_for_dmk_vip_win.sql

1. Or