Thursday, January 19, 2017

UDEV updated

In my previous post, I wrote about automating the creation of your Oracle RAC cluster.  One of the more complicated parts to that is using shared VMDK's and configuring UDEV to create aliases so all your RAC nodes have the same name for the same ASM disk, no matter what order udev finds the devices.

Assuming you used the VM create script in the previous post, the prerequisites of:

1.  "disk.EnableUUID"="true"; 
2. Data on SCSI adapter 1, Redo on SCSI adapter 2, and FRA on SCSI adapter 3
3. Node equivalence

...should already be met, and this should just work.  This needs to be executed as root, and should be tested in a sandbox environment until you're confident.  I've used it for years, but like everything on the internet, no warranties or promises implied or otherwise.  It may be found to connect to a secret government computer and play Global Thermonuclear War.


#! /bin/sh
###################################
# Name: udev_rules.sh
# Date: 5/9/2012
# Purpose:  This script will create all the udev rules necessary to support
#        Oracle ASM for RH 5 or RH6.  It will name the aliased devices
#        appropriately for the different failgroups, based on the contoller
#        they're assigned to.
# Revisions:
#   5/8/2012  - JAB: Created
#   5/10/2012 - JAB: Will now modify the existing rules to allow the addition of a
#          single new disk.
#   1/8/2013  - JAB: assorted RH6 related issues corrected.
###################################
data_disk=0
redo_disk=0
arch_disk=0
release_test=`lsb_release -r | awk 'BEGIN {FS=" "}{print $2}' | awk 'BEGIN {FS="."}{print $1}'`
echo "Detected RH release ${release_test}"

if [ -f "/etc/udev/rules.d/99-oracle-asmdevices.rules" ]; then
  echo -e "Detected a pre-existing asm rules file.  Analyzing...\c"
  for y in {1..50}
  do
    found_data_disk=`cat /etc/udev/rules.d/99-oracle-asmdevices.rules|grep asm-data-disk${y}`
    found_redo_disk=`cat /etc/udev/rules.d/99-oracle-asmdevices.rules|grep asm-redo-disk${y}`
    found_arch_disk=`cat /etc/udev/rules.d/99-oracle-asmdevices.rules|grep asm-arch-disk${y}`
    if [ -n "${found_data_disk}" ]; then
      let "data_disk++"
    fi
    if [ -n "${found_redo_disk}" ]; then
      let "redo_disk++"
    fi
    if [ -n "${found_arch_disk}" ]; then
      let "arch_disk++"
    fi
    echo -e ".\c"
  done
  echo "complete."
  echo "Existing rules file contains:"
  echo " ASM Data Disks: ${data_disk}"
  echo " ASM Redo Disks: ${redo_disk}"
  echo " ASM Arch Disks: ${arch_disk}"
  new_file="false"
else
  echo "Detected no pre-existing asm udev rules file.  Building..."
  new_file="true"
fi

for x in {a..z}
do
  if [ -n "`ls /dev/sd* | grep sd${x}1 `" ] ; then
    asm_test1=`file -s /dev/sd${x}1 |grep "/dev/sd${x}1: data" `
    asm_test2=`file -s /dev/sd${x}1 |grep "Oracle ASM" `
    #echo "Testing for sd${x}1 complete."
    if [[ -n "${asm_test1}" || -n "${asm_test2}" ]] ; then
      # ie: scsi_device:1:0:1:0
      if [ "${release_test}" = "5" ]; then
        controller=`ls /sys/block/sd${x}/device|grep scsi_device | awk 'BEGIN {FS=":"}{print $2}'`
        result=`/sbin/scsi_id -g -u -s /block/sd${x}`
      elif [ "${release_test}" = "6" ]; then
        controller=`ls /sys/block/sd${x}/device/scsi_device | awk 'BEGIN {FS=":"}{print $1}'`
        result=`/sbin/scsi_id -g -u -d /dev/sd${x}`
      fi
      if [ "${controller}" = "3" ]; then
        if [ -f "/etc/udev/rules.d/99-oracle-asmdevices.rules" ]; then
          found_uuid=`cat /etc/udev/rules.d/99-oracle-asmdevices.rules|grep $result`
        else
          found_uuid=
        fi
        if [[ -z "${found_uuid}" || "${new_file}" = "true" ]]; then
          echo "Detected a new data disk.  Adding rule to /etc/udev/rules.d/99-oracle-asmdevices.rules"
          let "data_disk++"
          if [ "${release_test}" = "5" ]; then
            echo "KERNEL==\"sd?1\", BUS==\"scsi\", PROGRAM==\"/sbin/scsi_id -g -u -s /block/\$parent\", RESULT==\"${result}\", NAME=\"asm-data-disk${data_disk}\", OWNER=\"oracle\", GROUP=\"dba\", MODE=\"0660\"" >> /etc/udev/rules.d/99-oracle-asmdevices.rules
          elif [ "${release_test}" = "6" ]; then
            echo "KERNEL==\"sd?1\", BUS==\"scsi\", PROGRAM==\"/sbin/scsi_id -g -u -d /dev/\$parent\", RESULT==\"${result}\", NAME=\"asm-data-disk${data_disk}\", OWNER=\"oracle\", GROUP=\"dba\", MODE=\"0660\"" >> /etc/udev/rules.d/99-oracle-asmdevices.rules
          fi
        fi
      elif [ "${controller}" = "4" ]; then
        if [ -f "/etc/udev/rules.d/99-oracle-asmdevices.rules" ]; then
          found_uuid=`cat /etc/udev/rules.d/99-oracle-asmdevices.rules|grep $result`
        else
          found_uuid=
        fi
        if [[ -z "${found_uuid}" || "${new_file}" = "true" ]]; then
          echo "Detected a new Redo disk.  Adding rule to /etc/udev/rules.d/99-oracle-asmdevices.rules"
          let "redo_disk++"
          if [ "${release_test}" = "5" ]; then
            echo "KERNEL==\"sd?1\", BUS==\"scsi\", PROGRAM==\"/sbin/scsi_id -g -u -s /block/\$parent\", RESULT==\"${result}\", NAME=\"asm-redo-disk${redo_disk}\", OWNER=\"oracle\", GROUP=\"dba\", MODE=\"0660\"" >> /etc/udev/rules.d/99-oracle-asmdevices.rules
          elif [ "${release_test}" = "6" ]; then
            echo "KERNEL==\"sd?1\", BUS==\"scsi\", PROGRAM==\"/sbin/scsi_id -g -u -d /dev/\$parent\", RESULT==\"${result}\", NAME=\"asm-redo-disk${redo_disk}\", OWNER=\"oracle\", GROUP=\"dba\", MODE=\"0660\"" >> /etc/udev/rules.d/99-oracle-asmdevices.rules
          fi
        fi
      elif [ "${controller}" = "5" ]; then
        if [ -f "/etc/udev/rules.d/99-oracle-asmdevices.rules" ]; then
          found_uuid=`cat /etc/udev/rules.d/99-oracle-asmdevices.rules|grep $result`
        else
          found_uuid=
        fi
        if [[ -z "${found_uuid}" || "${new_file}" = "true" ]]; then
          echo "Detected a new Arch disk.  Adding rule to /etc/udev/rules.d/99-oracle-asmdevices.rules"
          let "arch_disk++"
          if [ "${release_test}" = "5" ]; then
            echo "KERNEL==\"sd?1\", BUS==\"scsi\", PROGRAM==\"/sbin/scsi_id -g -u -s /block/\$parent\", RESULT==\"${result}\", NAME=\"asm-arch-disk${arch_disk}\", OWNER=\"oracle\", GROUP=\"dba\", MODE=\"0660\"" >> /etc/udev/rules.d/99-oracle-asmdevices.rules
          elif [ "${release_test}" = "6" ]; then
            echo "KERNEL==\"sd?1\", BUS==\"scsi\", PROGRAM==\"/sbin/scsi_id -g -u -d /dev/\$parent\", RESULT==\"${result}\", NAME=\"asm-arch-disk${arch_disk}\", OWNER=\"oracle\", GROUP=\"dba\", MODE=\"0660\"" >> /etc/udev/rules.d/99-oracle-asmdevices.rules
          fi
        fi
      fi
    else
      echo "/dev/sd${x} is not an asm disk."
    fi
  fi
done
echo "Complete."

echo "To see the ASM UDEV rules: cat /etc/udev/rules.d/99-oracle-asmdevices.rules"

Updated VMWare 6 Oracle RAC vm create script

I don't know if its just me, but it seems like everything I need to do has an impossible timeline.  When I make VM's for testing, its SOOOO tedious to use a point-and-click interface like virtual center. In the past for large proof of concepts I've had to create literally hundreds of VM's running RAC. To make things faster and to keep my sanity, I wrote a series of scripts to make setting up an Oracle environment nearly automated.

The steps are:
1. Create the RAC node vm's (See this post below)
2. Install the OS via kickstart (or just boot from the OS install ISO and manually configure the OS)
3. Install the Oracle pre-install rpm
        - If you aren't running Oracle Enterprise Linux, or if you want to customize the UID/GID etc, click < HERE >
4. Configure udev/passwordless SSH for root/Oracle
        - For the udev part, this is automated via my script here
5. For Oracle Virtual Machine, Oracle Database Appliance and Exadata, the RAC Pack wrote a wonderful script called OneCommand.  I find this to be a *huge* timesaver, so I extract that from the OVM OVF (found at edelivery.oracle.com) and modify the params.ini and netconfig.ini files.

That's it...if you have everything prepped, depending on your hardware you can go from nothing to running a new 4 node RAC environment in about 20 minutes, with very little "human" time.

A friend of mine integrated these steps into his company's internal cloud...filling in a few variables on a web page (server names, cpu, memory, storage sizes, db name, etc) he can click a button, go to lunch and when he comes back he can log in to his new cluster.

A few years ago I posted my VMWare Oracle RAC node create script, here's an updated version for VSphere 6 and PowerCLI 6.3 Release 1.  It has the changes needed for shared VMDK's and a way to disable the change block tracking that occasionally finds its way into the VMX's.  If you're setting up a flex cluster, you'll need to uncomment the asm_network lines.

Just modify the parameters in the top section.  Hopefully this saves you some time!

#################################################################
## This script works with VMWare 6's change in eager zeroing
# Modify variables below
#################################################################

connect-viserver -Server VC06.poc.local -Protocol https

$vmName1 = "strora01"
$vmName2 = "strora02"
$vmName3 = "strora03"
$vmName4 = "strora04"
$rac_vm_cpu = 50
$rac_vm_ram_mb = (16GB/1MB)
$rac_vm_ram_mb_rez = (8GB/1MB)
# below is the network label in vmware for the vlan
$public_network_name = "VLAN100(pub)"
$private_network_name = "VLAN101(priv)"
#$asm_network_name = "VLAN102(asm)"
#$backup_network_name = "VLAN103(backup)"
$osstore = "os_binaries"
$osstore_size_MB = (50GB/1MB)
$orastore = "os_binaries"
$orastore_size_KB = (100GB/1KB)
$datastore1 = "ora_data1"
$datastore2 = "ora_data2"
$datastore3 = "ora_data3"
$datastore4 = "ora_data4"
$datastore_size_KB = (400GB/1KB)
$recostore1 = "ora_redo1"
$recostore2 = "ora_redo2"
$recostore3 = "ora_redo3"
$recostore4 = "ora_redo4"
$recostore_size_KB = (16GB/1KB)
$archstore1 = "ora_fra1"
$archstore2 = "ora_fra2"
$archstore3 = "ora_fra3"
$archstore4 = "ora_fra4"
$archstore_size_KB = (25GB/1KB)    
$vm_hostname = "ucsblade135.poc.local"            
$OS_CDROM = "[os_binaries]OS_IMAGE/V77197-01.iso"

#################################################################
#  End variable section
#  No edits should be necessary below this line
#################################################################
 
$VM1 = new-vm `
        -Host "$vm_hostname" `
        -Name $vmName1 `
        -Datastore (get-datastore "$osstore") `
        -GuestID rhel6_64Guest `
        -MemoryMB 4096 `
        -DiskMB $osstore_size_MB `
        -NetworkName "$public_network_name" `
        -DiskStorageFormat "Thin"

$vm2 = new-vm `
        -Host "$vm_hostname" `
        -Name $vmName2 `
        -Datastore (get-datastore "$osstore") `
        -GuestID rhel6_64Guest `
        -MemoryMB 4096 `
        -DiskMB $osstore_size_MB `
        -NetworkName "$public_network_name" `
        -DiskStorageFormat "Thin"

$VM3 = new-vm `
        -Host "$vm_hostname" `
        -Name $vmName3 `
        -Datastore (get-datastore "$osstore") `
        -GuestID rhel6_64Guest `
        -MemoryMB 4096 `
        -DiskMB $osstore_size_MB `
        -NetworkName "$public_network_name" `
        -DiskStorageFormat "Thin"

$VM4 = new-vm `
        -Host "$vm_hostname" `
        -Name $vmName4 `
        -Datastore (get-datastore "$osstore") `
        -GuestID rhel6_64Guest `
        -MemoryMB 4096 `
        -DiskMB $osstore_size_MB `
        -NetworkName "$public_network_name" `
        -DiskStorageFormat "Thin"

Function Change-Memory {
      Param (
            $VM,
            $MemoryMB
      )
      Process {
            $VMs = Get-VM $VM
            Foreach ($Machine in $VMs) {
                  $VMId = $Machine.Id

                  $VMSpec = New-Object VMware.Vim.VirtualMachineConfigSpec
                  $VMSpec.memoryMB = $MemoryMB
                  $RawVM = Get-View -Id $VMId
                  $RawVM.ReconfigVM_Task($VMSpec)
            }
      }
}

Change-Memory -MemoryMB $rac_vm_ram_mb -VM $VM1
Change-Memory -MemoryMB $rac_vm_ram_mb -VM $VM2
Change-Memory -MemoryMB $rac_vm_ram_mb -VM $VM3
Change-Memory -MemoryMB $rac_vm_ram_mb -VM $VM4

Set-VM -vm(get-vm $VM1) -NumCpu $rac_vm_cpu -RunAsync -Version v11 -Confirm:$false
Set-VM -vm(get-vm $vm2) -NumCpu $rac_vm_cpu -RunAsync -Version v11 -Confirm:$false
Set-VM -vm(get-vm $VM3) -NumCpu $rac_vm_cpu -RunAsync -Version v11 -Confirm:$false
Set-VM -vm(get-vm $VM4) -NumCpu $rac_vm_cpu -RunAsync -Version v11 -Confirm:$false

Get-VM $VM1 | Get-VMResourceConfiguration | Set-VMResourceConfiguration -MemReservationMB $rac_vm_ram_mb_rez
Get-VM $vm2 | Get-VMResourceConfiguration | Set-VMResourceConfiguration -MemReservationMB $rac_vm_ram_mb_rez
Get-VM $VM3 | Get-VMResourceConfiguration | Set-VMResourceConfiguration -MemReservationMB $rac_vm_ram_mb_rez
Get-VM $VM4 | Get-VMResourceConfiguration | Set-VMResourceConfiguration -MemReservationMB $rac_vm_ram_mb_rez

New-NetworkAdapter -VM $vm1 -NetworkName "$private_network_name" -StartConnected -Type vmxnet3 -Confirm:$false
New-NetworkAdapter -VM $vm2 -NetworkName "$private_network_name" -StartConnected -Type vmxnet3 -Confirm:$false
New-NetworkAdapter -VM $vm3 -NetworkName "$private_network_name" -StartConnected -Type vmxnet3 -Confirm:$false
New-NetworkAdapter -VM $vm4 -NetworkName "$private_network_name" -StartConnected -Type vmxnet3 -Confirm:$false

Function Enable-MemHotAdd($vm){
    $vmview = Get-vm $vm | Get-View
    $vmConfigSpec = New-Object VMware.Vim.VirtualMachineConfigSpec

    $extra = New-Object VMware.Vim.optionvalue
    $extra.Key="mem.hotadd"
    $extra.Value="true"
    $vmConfigSpec.extraconfig += $extra

    $vmview.ReconfigVM($vmConfigSpec)
}

enable-memhotadd $vm1
enable-memhotadd $vm2
enable-memhotadd $vm3
enable-memhotadd $vm4

Function Enable-vCpuHotAdd($vm){
    $vmview = Get-vm $vm | Get-View
    $vmConfigSpec = New-Object VMware.Vim.VirtualMachineConfigSpec

    $extra = New-Object VMware.Vim.optionvalue
    $extra.Key="vcpu.hotadd"
    $extra.Value="true"
    $vmConfigSpec.extraconfig += $extra

    $vmview.ReconfigVM($vmConfigSpec)
}

enable-vCpuHotAdd $vm1
enable-vCpuHotAdd $vm2
enable-vCpuHotAdd $vm3
enable-vCpuHotAdd $vm4

New-HardDisk -vm($VM1) -CapacityKB $orastore_size_KB -StorageFormat Thin -datastore "$orastore"
New-HardDisk -vm($vm2) -CapacityKB $orastore_size_KB -StorageFormat Thin -datastore "$orastore"
New-HardDisk -vm($VM3) -CapacityKB $orastore_size_KB -StorageFormat Thin -datastore "$orastore"
New-HardDisk -vm($VM4) -CapacityKB $orastore_size_KB -StorageFormat Thin -datastore "$orastore"

$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $datastore_size_KB -StorageFormat EagerZeroedThick -datastore "$datastore1"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

$New_SCSI_1_1 = $New_Disk1 | New-ScsiController -Type ParaVirtual -Confirm:$false
$New_SCSI_2_1 = $New_Disk2 | New-ScsiController -Type ParaVirtual -Confirm:$false
$New_SCSI_3_1 = $New_Disk3 | New-ScsiController -Type ParaVirtual -Confirm:$false
$New_SCSI_4_1 = $New_Disk4 | New-ScsiController -Type ParaVirtual -Confirm:$false

$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $datastore_size_KB -StorageFormat EagerZeroedThick -datastore "$datastore2"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

set-harddisk -Confirm:$false -harddisk $New_Disk1 -controller $New_SCSI_1_1
set-harddisk -Confirm:$false -harddisk $New_Disk2 -controller $New_SCSI_2_1
set-harddisk -Confirm:$false -harddisk $New_Disk3 -controller $New_SCSI_3_1
set-harddisk -Confirm:$false -harddisk $New_Disk4 -controller $New_SCSI_4_1

$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $datastore_size_KB -StorageFormat EagerZeroedThick -datastore "$datastore3"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

set-harddisk -Confirm:$false -harddisk $New_Disk1 -controller $New_SCSI_1_1
set-harddisk -Confirm:$false -harddisk $New_Disk2 -controller $New_SCSI_2_1
set-harddisk -Confirm:$false -harddisk $New_Disk3 -controller $New_SCSI_3_1
set-harddisk -Confirm:$false -harddisk $New_Disk4 -controller $New_SCSI_4_1

$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $datastore_size_KB -StorageFormat EagerZeroedThick -datastore "$datastore4"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

set-harddisk -Confirm:$false -harddisk $New_Disk1 -controller $New_SCSI_1_1
set-harddisk -Confirm:$false -harddisk $New_Disk2 -controller $New_SCSI_2_1
set-harddisk -Confirm:$false -harddisk $New_Disk3 -controller $New_SCSI_3_1
set-harddisk -Confirm:$false -harddisk $New_Disk4 -controller $New_SCSI_4_1

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

$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $recostore_size_KB -StorageFormat EagerZeroedThick -datastore "$recostore1"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

$New_SCSI_1_2 = $New_Disk1 | New-ScsiController -Type ParaVirtual -Confirm:$false
$New_SCSI_2_2 = $New_Disk2 | New-ScsiController -Type ParaVirtual -Confirm:$false
$New_SCSI_3_2 = $New_Disk3 | New-ScsiController -Type ParaVirtual -Confirm:$false
$New_SCSI_4_2 = $New_Disk4 | New-ScsiController -Type ParaVirtual -Confirm:$false

$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $recostore_size_KB -StorageFormat EagerZeroedThick -datastore "$recostore2"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

set-harddisk -Confirm:$false -harddisk $New_Disk1 -controller $New_SCSI_1_2
set-harddisk -Confirm:$false -harddisk $New_Disk2 -controller $New_SCSI_2_2
set-harddisk -Confirm:$false -harddisk $New_Disk3 -controller $New_SCSI_3_2
set-harddisk -Confirm:$false -harddisk $New_Disk4 -controller $New_SCSI_4_2

$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $recostore_size_KB -StorageFormat EagerZeroedThick -datastore "$recostore3"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

set-harddisk -Confirm:$false -harddisk $New_Disk1 -controller $New_SCSI_1_2
set-harddisk -Confirm:$false -harddisk $New_Disk2 -controller $New_SCSI_2_2
set-harddisk -Confirm:$false -harddisk $New_Disk3 -controller $New_SCSI_3_2
set-harddisk -Confirm:$false -harddisk $New_Disk4 -controller $New_SCSI_4_2

$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $recostore_size_KB -StorageFormat EagerZeroedThick -datastore "$recostore4"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

set-harddisk -Confirm:$false -harddisk $New_Disk1 -controller $New_SCSI_1_2
set-harddisk -Confirm:$false -harddisk $New_Disk2 -controller $New_SCSI_2_2
set-harddisk -Confirm:$false -harddisk $New_Disk3 -controller $New_SCSI_3_2
set-harddisk -Confirm:$false -harddisk $New_Disk4 -controller $New_SCSI_4_2


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


$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $archstore_size_KB -StorageFormat EagerZeroedThick -datastore "$archstore1"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

$New_SCSI_1_3 = $New_Disk1 | New-ScsiController -Type ParaVirtual -Confirm:$false
$New_SCSI_2_3 = $New_Disk2 | New-ScsiController -Type ParaVirtual -Confirm:$false
$New_SCSI_3_3 = $New_Disk3 | New-ScsiController -Type ParaVirtual -Confirm:$false
$New_SCSI_4_3 = $New_Disk4 | New-ScsiController -Type ParaVirtual -Confirm:$false

$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $archstore_size_KB -StorageFormat EagerZeroedThick -datastore "$archstore2"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

set-harddisk -Confirm:$false -harddisk $New_Disk1 -controller $New_SCSI_1_3
set-harddisk -Confirm:$false -harddisk $New_Disk2 -controller $New_SCSI_2_3
set-harddisk -Confirm:$false -harddisk $New_Disk3 -controller $New_SCSI_3_3
set-harddisk -Confirm:$false -harddisk $New_Disk4 -controller $New_SCSI_4_3

$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $archstore_size_KB -StorageFormat EagerZeroedThick -datastore "$archstore3"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

set-harddisk -Confirm:$false -harddisk $New_Disk1 -controller $New_SCSI_1_3
set-harddisk -Confirm:$false -harddisk $New_Disk2 -controller $New_SCSI_2_3
set-harddisk -Confirm:$false -harddisk $New_Disk3 -controller $New_SCSI_3_3
set-harddisk -Confirm:$false -harddisk $New_Disk4 -controller $New_SCSI_4_3

$New_Disk1 = New-HardDisk -vm($VM1) -CapacityKB $archstore_size_KB -StorageFormat EagerZeroedThick -datastore "$archstore4"
$New_Disk2 = new-harddisk -vm($vm2) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk3 = new-harddisk -vm($vm3) -diskpath ($New_Disk1 | %{$_.Filename})
$New_Disk4 = new-harddisk -vm($vm4) -diskpath ($New_Disk1 | %{$_.Filename})

set-harddisk -Confirm:$false -harddisk $New_Disk1 -controller $New_SCSI_1_3
set-harddisk -Confirm:$false -harddisk $New_Disk2 -controller $New_SCSI_2_3
set-harddisk -Confirm:$false -harddisk $New_Disk3 -controller $New_SCSI_3_3
set-harddisk -Confirm:$false -harddisk $New_Disk4 -controller $New_SCSI_4_3

$ExtraOptions = @{
    # per VMware, SAP and Oracle VMware Best Practices
    "disk.EnableUUID"="true";
    "ctkEnabled"="false";
    "scsi0:0.ctkEnabled"="false";
    "scsi0:1.ctkEnabled"="false";
    "scsi1:0.ctkEnabled"="false";
    "scsi1:1.ctkEnabled"="false";
    "scsi1:2.ctkEnabled"="false";
    "scsi1:3.ctkEnabled"="false";
    "scsi2:0.ctkEnabled"="false";
    "scsi2:1.ctkEnabled"="false";
    "scsi2:2.ctkEnabled"="false";
    "scsi2:3.ctkEnabled"="false";
    "scsi3:0.ctkEnabled"="false";
    "scsi3:1.ctkEnabled"="false";
    "scsi3:2.ctkEnabled"="false";
    "scsi3:3.ctkEnabled"="false";
    "ethernet2.coalescingScheme"="disabled";
    "sched.mem.pshare.enable"="false";
    "numa.vcpu.preferHT"="true";
    "tools.syncTime" = "False";

    # per VMware's Hardening Guide - Enterprise Level
    "isolation.tools.diskShrink.disable"="true";
    "isolation.tools.diskWiper.disable"="true";
    "isolation.tools.copy.disable"="true";
    "isolation.tools.paste.disable"="true";
    "isolation.tools.setGUIOptions.enable"="false";
    "isolation.device.connectable.disable"="true";
    "isolation.device.edit.disable"="true";
    "vmci0.unrestricted"="false";
    "log.keepOld"="10";
    "log.rotateSize"="1000000";
    "tools.setInfo.sizeLimit"="1048576";
    "guest.command.enabled"="false";
    "tools.guestlib.enableHostInfo"="false"
}
$vmConfigSpec = New-Object VMware.Vim.VirtualMachineConfigSpec;
Foreach ($Option in $ExtraOptions.GetEnumerator()) {
    $OptionValue = New-Object VMware.Vim.optionvalue
    $OptionValue.Key = $Option.Key
    $OptionValue.Value = $Option.Value
    $vmConfigSpec.extraconfig += $OptionValue
}

$vmview=get-vm $vmName1 | get-view
$vmview.ReconfigVM_Task($vmConfigSpec)
$vmview=get-vm $vmName2 | get-view
$vmview.ReconfigVM_Task($vmConfigSpec)
$vmview=get-vm $vmName3 | get-view
$vmview.ReconfigVM_Task($vmConfigSpec)
$vmview=get-vm $vmName4 | get-view
$vmview.ReconfigVM_Task($vmConfigSpec)

new-CDDRIVE -VM $VM1  -isopath "$OS_CDROM" -startConnected
new-CDDRIVE -VM $VM2  -isopath "$OS_CDROM" -startConnected
new-CDDRIVE -VM $VM3  -isopath "$OS_CDROM" -startConnected
new-CDDRIVE -VM $VM4  -isopath "$OS_CDROM" -startConnected

function Set-MultiWriter{
  param($VM, $DiskName)
  echo $VM
  $TempVM = $VM | Get-View
  $Devicy=$TempVM.Config.Hardware.Device
  foreach ($device in $Devicy) {
    if($device -is [VMware.Vim.VirtualDisk] -and $device.deviceInfo.Label -eq $DiskName) {
      $diskDevice = $device
      $diskDeviceBaking = $device.backing
      break
    }
  }

  $spec = New-Object VMware.Vim.VirtualMachineConfigSpec
  $spec.deviceChange = New-Object VMware.Vim.VirtualDeviceConfigSpec
  $spec.deviceChange[0].operation = 'edit'
  $spec.deviceChange[0].device = New-Object VMware.Vim.VirtualDisk
  $spec.deviceChange[0].device = $diskDevice
  $spec.DeviceChange[0].device.backing = New-Object VMware.Vim.VirtualDiskFlatVer2BackingInfo
  $spec.DeviceChange[0].device.backing = $diskDeviceBaking
  $spec.DeviceChange[0].device.backing.sharing = "sharingMultiWriter"
  Write-Host "`nEnabling Multiwriter flag on on VMDK:" $diskName "for VM:" $vmname
  $task = $TempVM.ReconfigVM_Task($spec)
  $task1 = Get-Task -Id ("Task-$($task.value)")
  $task1 | Wait-Task
}
Set-MultiWriter $VM1 "Hard disk 3"
Set-MultiWriter $VM1 "Hard disk 4"
Set-MultiWriter $VM1 "Hard disk 5"
Set-MultiWriter $VM1 "Hard disk 6"
Set-MultiWriter $VM1 "Hard disk 7"
Set-MultiWriter $VM1 "Hard disk 8"
Set-MultiWriter $VM1 "Hard disk 9"
Set-MultiWriter $VM1 "Hard disk 10"
Set-MultiWriter $VM1 "Hard disk 11"
Set-MultiWriter $VM1 "Hard disk 12"
Set-MultiWriter $VM1 "Hard disk 13"
Set-MultiWriter $VM1 "Hard disk 14"
Set-MultiWriter $VM2 "Hard disk 3"
Set-MultiWriter $VM2 "Hard disk 4"
Set-MultiWriter $VM2 "Hard disk 5"
Set-MultiWriter $VM2 "Hard disk 6"
Set-MultiWriter $VM2 "Hard disk 7"
Set-MultiWriter $VM2 "Hard disk 8"
Set-MultiWriter $VM2 "Hard disk 9"
Set-MultiWriter $VM2 "Hard disk 10"
Set-MultiWriter $VM2 "Hard disk 11"
Set-MultiWriter $VM2 "Hard disk 12"
Set-MultiWriter $VM2 "Hard disk 13"
Set-MultiWriter $VM2 "Hard disk 14"
Set-MultiWriter $VM3 "Hard disk 3"
Set-MultiWriter $VM3 "Hard disk 4"
Set-MultiWriter $VM3 "Hard disk 5"
Set-MultiWriter $VM3 "Hard disk 6"
Set-MultiWriter $VM3 "Hard disk 7"
Set-MultiWriter $VM3 "Hard disk 8"
Set-MultiWriter $VM3 "Hard disk 9"
Set-MultiWriter $VM3 "Hard disk 10"
Set-MultiWriter $VM3 "Hard disk 11"
Set-MultiWriter $VM3 "Hard disk 12"
Set-MultiWriter $VM3 "Hard disk 13"
Set-MultiWriter $VM3 "Hard disk 14"
Set-MultiWriter $VM4 "Hard disk 3"
Set-MultiWriter $VM4 "Hard disk 4"
Set-MultiWriter $VM4 "Hard disk 5"
Set-MultiWriter $VM4 "Hard disk 6"
Set-MultiWriter $VM4 "Hard disk 7"
Set-MultiWriter $VM4 "Hard disk 8"
Set-MultiWriter $VM4 "Hard disk 9"
Set-MultiWriter $VM4 "Hard disk 10"
Set-MultiWriter $VM4 "Hard disk 11"
Set-MultiWriter $VM4 "Hard disk 12"
Set-MultiWriter $VM4 "Hard disk 13"
Set-MultiWriter $VM4 "Hard disk 14"


Thursday, April 28, 2016

What amuses Tom Kyte? (aka: To wrap, or not to wrap?) Selective Encryption Part 3 of 3

So...this post is not about rap, its about wrapping plsql.  If you've come here looking for a music site...run away! There's nothing about Jay-Z, the Beyonce formation Tour, Kanye West, Lil Wayne or Drake.

If you think you're getting security by wrapping your plsql, stick around...its not protection.  AS Tom said, "I put "protection" in quotes because I've always been amused by 'wrapping' code."

In my previous post, I had a function that returned a 256-bit key, and mentioned a few ways of protecting it...but cautioned about using plsql wrap.  This is only slightly better than doing nothing.

The function in the previous post is:

create or replace function blackan.my_key return varchar2 as
begin
  return '0F9157255C4CB6292D5C7E5FE96D90B2B4DE33CF4FEF2742366705F36D40A143';
end;

I run the wrap utility and it creates my_function.plb:

wrap iname=my_function.sql

Taking a peek in the file I see:

create or replace function blackan.my_key wrapped
a000000
367
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
7e ba
mtACEgpb2btQRc85YlHfKReZyCowgyr6f8tqynSm3f80Fbor4H4vlzbU3ANp9BtM2jLlkqnk
vF5IANpFigWMarvPqiRJdFXQs+5cQGjZZxauuFyxO9bW1UkgQotGR+YJ78l+VqExIcanEJXy
LoQv715qogTgWUFi22C8R47oRG4zYWj5tlQcWQ==
/

You might be thinking, "Wow!  That sure looks secure!  Comparing it to the original, I can't see anything that's the same!"  Don't think that...its a false sense of security.  There are TONS of post/utilities...even websites that will "unwrap" your plsql.  

Here's one:
http://www.codecrete.net/UnwrapIt

If you paste the wrapped code above in to the website, you'll see:
FUNCTION my_key RETURN VARCHAR2 AS 
BEGIN 
  RETURN '0F9157255C4CB6292D5C7E5FE96D90B2B4DE33CF4FEF2742366705F36D40A143'; 
END;

So...don't trust the security of wrapping plsql.

On the other hand, this allows you to get insight...when metalink is failing you, you may be able to paste the internal wrapped plsql packages from Oracle and diagnose your own issues.

I hope this helps you see you shouldn't trust the plsql wrap to keep your data secure.

SDE Part 1
SDE Part 2
YOU ARE HERE>> SDE Part 3

Selective Encryption Part 2 of 3

In my previous post I talked about the importance of encryption in the database.  To be fair, sometimes it really isn't important, its just required for some reason.  This is more of a solution to the 2nd, as part of an overall security scheme.  Also, for a scenario where some of the data in a table is replicated to other databases/platforms, etc, this could be a way to auto-obfuscate.  The encrypted data would be moved through GG or w/e CDC you use without a key...making it very secure after it leaves the database.

We're going to use a block cipher...so first, you need to pick a cipher algorithm (here are the block options), a block cipher chaining modifier and a padding modifier from below, and add up their associated numbers. (ie: AES256 with CBC and PKCS5 would be 8+256+4096=4360)  We'll use 4360 for our example.

Here are some of the options in dbms_crypto:

    -- Block Cipher Algorithms
    ENCRYPT_DES                            :=     1;
    ENCRYPT_3DES_2KEY              :=     2;
    ENCRYPT_3DES                          :=     3;
    ENCRYPT_AES                            :=     4;
    ENCRYPT_PBE_MD5DES          :=     5;
    ENCRYPT_AES128                      :=     6;
    ENCRYPT_AES192                      :=     7;
    ENCRYPT_AES256                      :=     8;

    -- Block Cipher Chaining Modifiers
    CHAIN_CBC                                 :=   256;
    CHAIN_CFB                                 :=   512;
    CHAIN_ECB                                 :=   768;
    CHAIN_OFB                                 :=  1024;

    -- Block Cipher Padding Modifiers
    PAD_PKCS5                                  :=  4096;
    PAD_NONE                                   :=  8192;
    PAD_ZERO                                    := 12288;
    PAD_ORCL                                    := 16384;

1. First, create a key and capture the string that gets generated...don't lose this key:

set serveroutput on size 1000000
declare
  v_Number number := 256/8; --256 bit key
  v_Key RAW(32);
begin
  v_Key := DBMS_CRYPTO.RANDOMBYTES(v_Number);
  dbms_output.put_line(v_Key);
end;

2. Now replace the red string below with the output generated above.  This is meant as a very simple example.  In real life you'd want to do everything you can to make this complex and difficult to read. Putting it in a package and wrapping it would make it slightly less simple to get at.  Putting this data somewhere else encypted (like the OS or a different db) would be better.  How you do this is up to you...but just creating a plain text function isn't sufficient, and depending on plsql wrap is a bad idea...as Tom Kyte put it, "Using the latest wrap would provide the highest degree of "protection".  That is all.  I put "protection" in quotes because I've always been amused by 'wrapping' code."  I'll explain that more in a different post.

create or replace function blackan.my_key return varchar2 as 
begin 
  return '0F9157255C4CB6292D5C7E5FE96D90B2B4DE33CF4FEF2742366705F36D40A143'; 
end;

3. Now generate the base table and the decrypting view on the table:

drop table blackan.hacker_booty_base;
create table blackan.hacker_booty_base (EmpName raw(120), Emp_Num raw(88), SSN raw(80), Credit_Card raw(120), CC_Ex_Date raw(88), Address raw(120), PW raw(120));

create or replace view blackan.hacker_booty as
select 
  SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(EmpName,4360,my_key), 'AL32UTF8'),1,30) EmpName,
  SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(Emp_Num,4360,my_key), 'AL32UTF8'),1,22) Emp_Num,
  SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(SSN,4360,my_key), 'AL32UTF8'),1,20) SSN,
  SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(Credit_Card,4360,my_key), 'AL32UTF8'),1,30) Credit_Card,
  SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(CC_Ex_Date,4360,my_key), 'AL32UTF8'),1,30) CC_Ex_Date,
  SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(Address,4360,my_key), 'AL32UTF8'),1,30) Address,
  SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(PW,4360,my_key), 'AL32UTF8'),1,30) PW
from hacker_booty_base;

4. Now create the encrypting "instead of" trigger on the view to redirect the dml to the base table after encrypting it:
CREATE OR REPLACE TRIGGER blackan.ioiudr_hacker_booty
INSTEAD OF INSERT OR UPDATE OR DELETE
ON blackan.hacker_booty
FOR EACH ROW
BEGIN
  if INSERTING then
    insert into hacker_booty_base values (
      DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW(:NEW.EmpName,'AL32UTF8'),4360,my_key),
      DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Emp_Num,  'AL32UTF8'),4360,my_key),
      DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.SSN,  'AL32UTF8'),4360,my_key),
      DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Credit_Card,  'AL32UTF8'),4360,my_key),
      DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.CC_Ex_Date,  'AL32UTF8'),4360,my_key),
      DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Address,  'AL32UTF8'),4360,my_key),
      DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.PW,  'AL32UTF8'),4360,my_key));
  elsif updating then
    update hacker_booty_base set 
      EmpName=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW(:NEW.EmpName,'AL32UTF8'),4360,my_key),
      Emp_Num=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Emp_Num,  'AL32UTF8'),4360,my_key),
      SSN=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.SSN,  'AL32UTF8'),4360,my_key),
      Credit_Card=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Credit_Card,  'AL32UTF8'),4360,my_key),
      CC_Ex_Date=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.CC_Ex_Date,  'AL32UTF8'),4360,my_key),
      Address=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Address,  'AL32UTF8'),4360,my_key),
      PW=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.PW,  'AL32UTF8'),4360,my_key)
    where SSN=:OLD.SSN;
  else
    delete from hacker_booty_base where SSN=:OLD.SSN;
  end if;    
end;

...and that's it.  Let's insert a row of fake data:
insert into blackan.hacker_booty values ('Richy Rich',1,'123-45-6789','111 2222 3333 4444', sysdate+365, '1 Main St, St Louis, MO, 12345', 'Shhh');

When you look at the actual data-at-rest, its encrypted:

select empname, emp_num, ssn from blackan.hacker_booty_base;
EMPNAME EMP_NUM SSN
454460100E82C91F8B26AEE3E35EE3E8 9F1BE1034151056A760A880E5D7BC5C3 29B471D31D8E2255D1B0C7B6D5084759

When you look at the data via your application, which would use the view, you see the data exactly as you entered it:

select empname, emp_num, ssn from blackan.hacker_booty;

EMPNAME,EMP_NUM,SSN
Richy Rich    1       123-45-6789

SDE Part 1
YOU ARE HERE>> SDE Part 2
SDE Part 3

Selective Encryption - Part 1 of 3

Encryption in databases is nothing new, but its becoming a more prominent topic every day.  One of my first experiences with it was in dealing with a database that had extremely confidential data, and it was a known target to foreign countries who were actively attempting to access the network to get to the database.  Not to be overly dramatic, but if this data was compromised, people would be killed. I wasn't working for a Gov't agency at the time...so don't read too much into all I just said.  The point is, every company stores data that needs to be secured, and the reality of it is, security is an illusion we can tell ourselves we have until we're proven wrong.  So from that you have two choices:

1. Give up, its inevitable our data will be breached.
2. Make it as difficult as possible for our data to be breached, and if it is, encrypt everything valuable to make it more difficult at that point.

I choose 2.  :)  In the scenario I mentioned, we had security layer on security layer..and I don't mean to say this is the complete solution to make your data secured.  Given the data in your database will eventually be in the hands of people who took it, what can you do?  Oracle  sells TDE, which does a lot of the work for you, but its a licensed feature.  It used to include transparent data encryption at rest and data in flight.  Thanks to a security issue a few years ago that exposed encrypted network traffic, encryption of network traffic in flight is now part of your normal db license (aka no additional cost.)

   Strongauthentication services (Kerberos, PKI, and RADIUS) and network encryption (native           network encryption and SSL/TLS) are no longer part of Oracle Advanced Security andare available in all licensed editions of all supported releases of the Oracle database.

What about data at rest and backups?  Every company keeps PII (personal identifiable information) on their employees...Name, SSN, Address, Phone number, etc.  What about your company's customers?  If your unencrypted data is breached, your company is required by law to notify the people who may have had PII stolen. Would your company lose future business if this happened? I've been told the FCC requires databases storing network endpoint addresses to be 256-bit encrypted. For almost all dba's, encryption isn't really optional anymore.

So...We talked about TDE...there's also something Oracle refers to as SDE (selective data encryption) ...and this is free (1930738.1).  This is really easy to implement...its not perfect, but its better than nothing.  I'll show you how in the next post.

YOU ARE HERE>> SDE Part 1
SDE Part 2
SDE Part 3

Tuesday, March 29, 2016

Goldengate Monitor Memory Leak/monitoring

I've seen issues with Goldengate Monitor crashing over and over.  I've had very limited success from Oracle support...one company I worked with scheduled something in cron to stop/start Monitor during a low activity time to keep it from crashing unexpectedly.

Although that may be a work around, that shouldn't be necessary.  Monitor has been around for a few years now...it should be more stable than this.  I found a blog today where somebody had this issue and dug pretty deeply into the root issue...here's the link:

http://darthdba.blogspot.com/

Wednesday, March 9, 2016

To OEM 13c, or not to OEM 13c?

Last week I met with some Oracle people for an OEM 13c presentation with some other customers to look at the great new features offered. 

When they asked, "Who has installed 13c?". I was the only one in the group of ~30 who raised my hand.  By the end of the presentation and at the urging of the Oracle guys presenting, everybody was going to rush back to the office and download and install it to take advantage of all the great new features.

This week I'm at Hotsos in Dallas at a presentation by Kellyn Pot'Vin-Gorman (DBA Kevlar), and she just mentioned a new revision of OEM (13.2) is going to be released in the next quarter with new features and a lot of refinements.

So...you might want to wait for it.  :)