What's in the toolbox....

Over the years I have picked up a few bits of software that form part of my personal SOE.  The list below is some of what is currently included.

Desktop

My preferred desktop is openSUSE (currently version 13.1). I have been using openSUSE as my primary desktop O/S for almost as long as I have been using Linux - nearly 20 years now. It is not just the familiarity I have with it, although that certainly helps. Things like YaST, the openSUSE Build Service and repositories mean pretty much anything I need to do I can do with a mimimum of fuss.

My preference for a Linux desktop is also driven by the fact that nearly all of my work is done on Linux or UNIX systems, and I don't have to do a 'context switch' between working on the customer systems and my desktop.

Productivity

As you would expect, along with a Linux desktop, I prefer to use Firefox for browsing, Libre Office for documents/spreadsheet/presentations, and Thunderbird for email. These have the added benefit of being cross-platform, so when I am forced to use a Windows desktop, I can use the same applications there.

Using Thunderbird for email allows me to have all my email accounts in the one client, with the exception of my work Microsoft Exchange email account. For whatever reason, they don't have the Exchange IMAP service enabled. However, to get around that I use davmail gateway. This gives me access to everything I need from Exchange, but gives Thunderbird local standards-based services to talk to..

For diagramming, I normally use Libre Office Draw, or Dia. I have never used Visio, and to be honest have never run into anything that I couldn't do with Draw or Dia and some free clipart.

System Utilities

Once again, the standard Linux desktop give me everything I need by way of utilities to access customer systems. Most remote access scenarios have a Linux equivalent, and ssh with X-Forwarding  and/or tunneling gives me everything else.

However, if I must use Windows, my preferred utility is MobaXterm. Where previously you might have needed PuTTY + XMing +Winscp, MobaXterm has all of those plus more, all in the one utility. You can get it as a standalone executable or as an installer.  In addition, it gives me a 'bash' shell on Windows, so once again that is one less thing I have to adjust to.

sqluldr2 is a utility to quickly extract large amounts of data from Oracle tables to character separated text files. It is a lot quicker and easier to run than the equivalent Oracle utilities. Sadly, it seems to be unmaintained, but should still work with 10.2.0.4 client libraries.

SQLDeveloper is not bad. Pretty easy to install and run, does pretty much everything I need for poking around in a database, and has some additional functionality if you connect 'as sysdba'.

CamStudio for doing screen recordings, if you need to do that sort of thing. For example, if you need to document an installation, just start CamStudio, do the installation, then later playback the recording through VLC, and take use VLC to save screenshots at the appropriate times

PDFtk is a toolkit for working with pdf files - merging and splitting.

Other stuff

For Windows, PortableAPPS is invaluable. Over 300 FOSS applications that can be downloaded and run from a USB, or just downloaded and installed as a 'local user' i.e. without requiring administrative permissions on the desktop. So if you want Firefox but don't have permission to install software, you can install the portable version in your user directory, and just delete it when you are finished.

And finally PokerTH, which is 'solitaire' for grown-ups.

Build an Oracle VM 3 demo environment in VirtualBox - from scratch.

Hypervisor

"A hypervisor or virtual machine monitor (VMM) is a piece of computer software, firmware or hardware that creates and runs virtual machines. " - Wikipedia

In the world of server virtualisation, there are two kinds of hypervisor. 'Type 2' hypervisors are also known as 'hosted' because they rely on an underlying host operating system (i.e Linux, Windows et al) on which to run. VirtualBox is an example of a Type 2 hypervisor. 'Type 1' hypervisors on the other hand are 'bare metal' hypervisors. They are in effect specialised operating systems in their own right, running directly on the host hardware and dedicated to managing guest virtual machines. VMWare ESX/ESXi, Microsoft Hyper-V and Oracle VM Server are examples of this kind of hypervisor.

If you want to set up a demo environment of Oracle VM Server, because it takes over the whole machine, you have a couple of choices.  If you have enough spare hardware lying around, you can set up an environment on physical infrastructure.  Not everyone has that much spare hardware lying around. The alternative is to do it on virtual hardware.

Inception

With VirtualBox, running on a decent host, you can create virtual versions of all the infrastructure you need. As far as the guest systems are concerned, they are running on real hardware. It is just in this case, the guest system will be Oracle VM Server. And, just like in the movie 'Inception', you can go multiple layers deep in virtualisation.

There are pre-built 'appliances' of Oracle VM Manager and Oracle VM Server for VirtualBox.  If you just want to get it up and running that way, you can find the instructions for doing so here. Personally, I prefer to 'learn by doing'. In the real world, you would have to build this infrastructure in 'bare metal' so I wanted to replicate that.

The documents below describe in detail the process for building a complete demo of Oracle VM 3 in VirtualBox.  It is based on Oracle VM 3.2. It should still work for version 3.3 which was released earlier this month, with the exception that you can no longer use an external database for the Oracle VM Manager repository - you must use the MySQL database that comes with the distribution.

Part 1 - Introduction, Initial Configuration, and installing and configuring Openfiler

Part 2 - Installing and configuring Oracle VM 3 Manager

Part 3 - Installing and configuring Oracle VM 3 Server

Part 4 - Creating an Oracle VM 3 Guest

Starting VirtualBox 'host-only' interfaces before starting VMs

I really like VirtualBox. For any kind of testing, proofs-of-concept builds, or just trying out new stuff (like the latest Android x86 release), pretty much anything I need to do I can do in VirtualBox.

Occasionally, however, I come across something that doesn't quite work the way I need it to. I ran into one of those things while doing the development of my 'Oracle SE Physical Standby'.

The problem

Creating a RAC cluster in VirtualBox is reasonably straightforward. When you create multi-node Oracle RAC clusters, beginning with Oracle 11g Release 2, the clusterware provides a Single Client Access Name (SCAN) to simplify configuration of clients which need to access a RAC database. The SCAN is a single virtual host name that can resolve to multiple IPs. The Oracle clusterware creates and manages a Virtual IP (VIP) for each of those IPs, and a TNS listener listening on each of those VIPs. The VIPs and listeners can migrate to any node in the cluster. The minimum recommended IPs for the SCAN is 3. It is possible to have the SCAN defined in /etc/hosts, but then you are restricted to just one IP (and therefore one listener). The way to do it properly is to have the SCAN defined in a DNS server.

One of the network options in VirtualBox is a 'host-only' network. This creates a private network between all the guest virtual machines on that network, and the VirtualBox host machine.  However, by default none of the guest VMs have network access beyond the host machine, so to provide DNS, ideally you have a DNS server running on your host machine, and in order for the guest VMs to use the DNS server on the host machine, it must be listening on the host's 'host-only' network IP.  VirtualBox creates a network interface on the host machine for the 'host-only' network, and assigns an IP to that interface on the 'host-only' network. The final thing is that in order for the clusterware on each guest to start correctly, it needs to be able to access the DNS server.

Now to the core of the problem. In order for named to bind to the 'host-only' IP, the interface has to be created, have an IP assigned, and be up. However, the interface for the VirtualBox 'host-only' network does not get created until you run VirtualBox, either the VirtualBox Manager GUI application, or running 'VBoxManage' on the command-line. And the interface does not get configured with an IP and brought up until a guest VM that uses that 'host-only' network is started.  So what I have to do is start a VM, and while it is starting, quickly restart named so it will bind to the newly-created 'host-only' interface on the host system, before the clusterware tries to start. Not my ideal way of working.

The solution

So, having established that just running 'VBoxManage list hostonlyifs' will not only create the interfaces on the host, but also give all the information required to configure each interface, it was just left to write a script to do just that.  Now I can run the script, restart named and then start the guest VMs, all in my own time.

The script

#!/bin/bash
# vboxstartnet.sh

bcastcalc(){

    local IFS='.' ip i
    local -a oct msk
   
    read -ra oct <<<"$1"
    read -ra msk <<<"$2"

    for i in ${!oct[@]}; do
        ip+=( "$(( oct[i] + ( 255 - ( oct[i] | msk[i] ) ) ))" )
    done

    echo "${ip[*]}"
}

mask2cidr() {

    local nbits dec
    local -a octets=( [255]=8 [254]=7 [252]=6 [248]=5 [240]=4
                      [224]=3 [192]=2 [128]=1 [0]=0           )
   
    while read -rd '.' dec; do
        [[ -z ${octets[dec]} ]] && echo "Error: $dec is not recognised" && exit 1
        (( nbits += octets[dec] ))
        (( dec < 255 )) && break
    done <<<"$1."

    echo "/$nbits"

}

for line in `VBoxManage list hostonlyifs | sed 's/: */=/' | grep '^Name\|^IPAddr\|^NetworkMask'`
do
eval $line
case "$line" in
    NetworkMask* )
           BCAST=`bcastcalc "$IPAddress" "$NetworkMask"`
           CIDR=`mask2cidr "$NetworkMask"`
           sudo /bin/ip addr add ${IPAddress}${CIDR} broadcast ${BCAST} dev ${Name}
           sudo /bin/ip link set dev ${Name} up
       ;;
esac
done
 

Physical Standby Database with Oracle Standard Edition - an alternative approach - Part 2

In Part 1, I described the background and the decision process in coming up with this solution. In this part, I will provide the implementation details.

Preliminaries

But first, a little light reading.  My assumption is that you will be familiar with Linux, Oracle Database and Grid Infrastructure. However, here is the reference for managing 3rd party applications with Oracle Clusterware.

This implementation was developed on:

• Oracle Linux 6.5 x86_64 running in VirtualBox VMs
• Oracle Grid Infrastructure 11.2.0.4
• Oracle Database Standard Edition 11.2.0.4

I wont go through all the details of getting your environment built, but basically you will need to have completed:

• Installing Oracle Linux/RHEL/CentOS - any version of Linux that will run the database and clusterware.,
• When configuring the O/S, you will need to disable SELINUX, by setting 'SELINUX=permissive' or 'SELINUX=disabled' in /etc/sysconfig/selinux,
• Install rsync, if it is not already there,
• Install lsyncd - available from the EPEL repositories,
• configure ssh user equivalence between all the nodes for the 'oracle' user. If you are using RAC, and are therefore connecting to the database with the SCAN address, add “StrictHostKeyChecking No” to ~/.ssh/config – this is because the SCAN ip can switch between hosts and consequently the host key in ~/ssh/known_hosts for the SCAN ip's can change.
• Install and configure Oracle Grid Infrastructure, either clustered or stand-alone,
• Configure ASM disk groups to hold the database data and recovery area,
• Install Oracle database software,
• Create the primary database,
• Create an entry in tnsnames.ora for the remote database on both hosts. i.e.if your database is 'ORCL' and you want to use a suffix of 'REMOTE' for the tns alias of the database on the other host, create a tnsnames.ora entry like
      ORCL_REMOTE =
       (DESCRIPTION =
         (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = remotehost.domain)(PORT = 1521))
         )
         (CONNECT_DATA =
           (SERVICE_NAME = ORCL)
           (UR = A)
         )
       )
• Create a directory on the file system to store the archived logs. This directory must be the same on all nodes. In the primary database set this directory as one of the 'log_archive_dest_n' parameters. You will also need to set one of the 'log_archive_dest_n' parameters to the value 'LOCATION=USE_DB_RECOVERY_FILE_DEST'
• Create a standby database via the usual methods i.e. RMAN from active database etc. Note that even though we are not in a Data Guard environment, you still need to set db_unique_name, but it is OK to set it to the same value in both primary and standby databases. You will need to manually register the standby database with the Oracle Grid infrastructure. When you do, include '-n <db name>' in the srvctl command

The main event

Phew.

Now that you have your environment built, we can actually start synchronising the standby database from the primary.

First, download the lsyncd.sh script.  Place it in a location on all the primary and standby nodes. For example, if you are following the Oracle Optimal Flexible Architecture, you could create a directory '/u01/app/oracle/admin/bin'. Make sure the file permissions allow the 'oracle' user to execute the script.  The only thing in the script that is site-specific is the value for STANDBY_DB_SUFFIX. This is used to reference the TNS alias for the remote database, so we can get the HOST the remote database is located on. Edit the lsyncd.sh script for your particular environment.

Next, we register the script with Oracle Grid Infrastructure. Set the environment to the Grid Infrastructure home. The easiest way to do this is:

$ ORACLE_SID=`grep '+ASM' /etc/oratab | awk -F\: '{ print $1 }'`
$ . oraenv
ORACLE_SID = [+ASM1] ?
The Oracle base has been set to /u01/app/oracle

Then run the following command:

$ crsctl add resource lsyncd.orcl -type local_resource \
-attr "ACTION_SCRIPT=/u01/app/oracle/admin/bin/lsyncd.sh, \
CHECK_INTERVAL=60,RESTART_ATTEMPTS=2, \
START_DEPENDENCIES=hard(intermediate:ora.orcl.db), \
STOP_DEPENDENCIES=hard(shutdown:ora.orcl.db)”

where

• 'lsyncd.orcl' is the name of the resource in Oracle GI – use your own naming convention, but it cannot start with 'ora.', and if you want to manage more than one database on the server, each database will have its own lsyncd resource,
• ACTION_SCRIPT is the path to the lsyncd.sh script,
• START_DEPENDANCIES and STOP_DEPENDANCIES references the resource name for the database being managed. START_DEPENDANCIES must be 'hard(intermediate:' to account for the standby database being in INTERMEDIATE state. STOP_DEPENDANCIES must be 'hard(shutdown:' so that lsyncd will keep running even if the database is stopped.

And that's it. No, really. The clusterware should start the lsyncd resource automatically. To check you can run:

$ crsctl status resource lsyncd.orcl
NAME=lsyncd.orcl
TYPE=local_resource
TARGET=ONLINE
STATE=ONLINE

If it shows 'TARGET=OFFLINE', then run

$ crsctl start resource lsyncd.orcl

To diagnose problems, there is a log created under $ORACLE_BASE/admin/<database name>/lsyncd

Physical Standby Database with Oracle Standard Edition - an alternative approach - Part 1

Introduction

Oracle Data Guard is a great product with features which, when configured correctly, allow full replication of Oracle databases, with zero data loss (via 'Real Time Apply' of redo), and almost zero loss of availability (through Fast Start Failover and Client Fast Connection Failover). Combined with Oracle Real Application Clusters (RAC) and Oracle Flashback Technologies, it is one of the major components in building a Maximum Availability Architecture (MAA).  Additional features such as Active Data Guard give further return on investment from Business Continuity infrastructure.

However, all that comes at a price. Data Guard and Flashback are only available with Oracle Enterprise Edition (EE). Active Data Guard is an additional license, as is RAC for Enterprise Edition.  Some businesses are happy to pay the premium, and will be able to justify the additional cost.  Not everyone needs, or can afford, an Enterprise Edition license, and make do with Oracle Standard Edition (SE).

So if you are using Oracle SE, and still want to maintain a physical standby database as part of your Business Continuity solution, what do you do?  There are commercial solutions such as Dbvisit Standby, that offer added ease-of-use features, but once again they come at a cost, although not as much as an Oracle EE license. I would also suggest every Oracle DBA who has encountered this scenario has implemented a home-grown solution, either built from scratch, or modified from various solutions published on the internet over the years.  It is typically a mixture of scripts - shell, perl, SQL, etc - whatever the particular DBA was proficient in, run via cron at various intervals.  They are generally site specific, not very robust and have a lot of hard-coding of path names, passwords and the like.

Approach

In approaching a problem like this, and in designing and implementing Oracle infrastructure in general, I like to fall back to a philosophy I have adapted from that developed for creating 'low maintenance' gardens by horticulturist Don Burke.  Don's approach was to use a smart selection of plants and landscaping that would look after itself, with minimal maintenance, leaving the gardener to simply enjoy the result.  He called himself 'The Lazy Gardener'. Likewise, my approach is 'The Lazy DBA'. By that I mean the DBA should use simple and robust tools and processes, as well as taking advantage of all the technology available, to build infrastructure that can (more-or-less) run itself, requiring minimal input on the part of the DBA, leaving him or her to more interesting tasks, such as planning for the next big project.

So my criteria in building a solution to this particular issue included:

• event-driven rather than continuous polling
• use existing infrastructure or Oracle technology
• work with RAC and single instance
• any code developed should run with zero (or minimal) modification for local environment
• new technology should be lightweight and minimally intrusive on existing infrastructure
• should make use of the existing environment for all information
• no passwords stored anywhere
• minimal development effort

I wanted to have a cross-platform solution, but the more I looked into it, the more unlikely that seemed without having to build something from scratch, something I wanted to avoid. Given most of my experience is in the Linux/Unix arena I decided to stick with Linux as a first implementation, and look at cross-platform later.

Lets breakdown what needs to happen to update the standby database. The basic process is:

• A log switch occurs in the primary database
• The previous log is archived
• The system detects the existence of the new archived log
• The archived log is transferred to the standby system
• The standby system detects the existence of the new archived log
• The new archived log is registered with the standby database
• The standby database performs recovery with the new archived log

The process with gap resolution is:

• The standby database is queried for archive gaps
• The primary database is notified of the missing archived logs
• the primary database places the missing archived logs into the stream for transfer and application against the standby database

Detecting archive logs

Detecting archived logs written to a file system on Linux is not that big a deal. The 'inotify' infrastructure built into the Linux kernel allows monitoring for changes to files and directories. There are a number of utilities and language extensions that hook into this infrastructure.

If you are using ASM - something I think modern Oracle implementations should be doing whether RAC or single instance - it is slightly more problematic, since you cannot use inotify directly with ASM, and AFAICT there is no way to trigger existance of a new archived log from within the database. The workaround is to simply have a second archived log destination pointing to a local file system. Just as an aside, if you are using RAC with Oracle SE, the license conditions state you must use a recovery area located on ASM as your primary log archive destination. You can also have a log archive destination to a local file system, but you cannot use any other shared (i.e. NFS) or clustered file system (i.e. OCFS2) other than ASM.

Log shipping

The options here are the usual suspects - rsync, scp or sftp. All of these have the advantage of being able to work without requiring passwords once ssh user equivalence between the primary and standby nodes have been configured.

Log application

Once the archived log has been copied to the standby system, 'inotify' takes care of detecting its arrival. It is just then a matter of registering and applying it to the standby database. Standard Oracle database utilities like RMAN and SQLPlus can handle this. Once again, no passwords will be required because, as long as we are the 'oracle' user, we can 'connect / as sysdba'.

Other requirements

Other requirements like making use of the existing infrastructure and getting configuration information from the existing environment are admirable solved by Oracle Grid Infrastructure. If you are using RAC, you will already have GI, and even if you aren't using RAC, I would recommend implementing Oracle Restart, which is just Oracle GI on a stand-alone system. You get ASM, and HA components like auto-start on reboot and auto-restart of failed instances. Oracle GI can also manage third party applications (like ours).  All the configuration information we need is already available from the database or from the Oracle Cluster Registry (OCR) so there is no need to create separate configuration files for what we need. Actually, that is not quite true, but the configuration file we need can be built automatically from all the other information we have.

Putting it together

So, what we end up with is an environment built on Oracle Grid Infrastructure and ASM, for both single-instance and RAC databases, some application that will get notifications of new files, rsync to transfer the files, and another app that will act once the new files arrive. The apps will be registered with Oracle GI as resources to be managed.

Initially, I was looking at writing something in shell or python to do all the missing logic at both ends, but that was starting to look like too much hard work. It was then that I found lsyncd.

Lsyncd

This is how lsyncd is described on the project page

"Lsyncd watches a local directory trees event monitor interface (inotify or fsevents). It aggregates and combines events for a few seconds and then spawns one (or more) process(es) to synchronize the changes. By default this is rsync. Lsyncd is thus a light-weight live mirror solution that is comparatively easy to install not requiring new filesystems or blockdevices and does not hamper local filesystem performance.
...

Fine-grained customizaton can be achieved through the config file. Custom action configs can even be written from scratch in cascading layers ranging from shell scripts to code written in the Lua language. This way simplicity can be balanced with powerfulness."

What that means for us is the one application, using a very basic configuration file, will monitor, and act upon, file system events, and transfer files via rsync.  Not only that, but it has the ability to perform arbitrary actions on file system events, just by including what needs to happen in the configuration file. So instead of having to write a specific 'detect and act upon file system event' application, we have a generic 'detect and act upon file system event' utility, with the rsync stuff built in, already available. Awesome.

All that is left to do is to work out what needs to go into the configuration file. Then write a script that can generate a specific configuration file based on the specific environment.  Which is what I have done.

In Part 2, I describe the implementation of the solution.