Oracle Labs by Yuri Khazin, Oracle DBA

December 30, 2014

Building a Virtual Oracle RAC – Project Introduction


This article was originally written in 2010 and then updated several times. In 2010 the Virtual Box belonged to Sun Microsystems, now it is a part of Oracle’s toolbox. The interface of Virtual Box has somewhat changed over the years, so my apologies to the reader for screen snapshots that don’t look exactly like today’s version. You can still download older version of the program so that chances are that you won’t encounter issues while trying out this guide.

Introduction: This learning experiment is about building a totally virtual Oracle RAC environment at home using only open source software available to wide public (With the exclusion of Windows, if you happen to use it. I wish there was an open source version of MS Windows, that would benefit MS as well. Works for Red Hat, right?).

My project is generally based on an excellent article by Jeffrey Hunter (and other authors) – Build Your Own Oracle RAC Cluster on Oracle Enterprise Linux and iSCSI

Jeffrey’s article gives instructions on how to build a physical setup. I do recommend reading this article or at least giving it some 10 minutes scan before you attempt to build a RAC. In that article Jeffrey estimates a cost to build a production RAC in physical environment between $20K and $30K. He shows that for the purposes of learning the technology a “mostly physical” RAC with some parts virtualized can be built for under $2700. I am going to take the virtualization to the extreme and build a RAC inside a single PC. Yes, I do not care now about performance, and yes, I am willing to buy some additional memory for the PC. You can not really expect to run two Linux nodes with databases and a storage (SAN) simulation on a PC with 1GB of memory. My estimated cost is, therefore, somewhere under $100, if all we need is a memory upgrade. Sounds good? It is still a lot of work and it will probably take you many hours to do it right. The benefit is a hands on knowledge, which is priceless.

Now to the choice of hardware and software.

  • PC or server with some 200GB space and 3.5GB memory (XP Professional can handle up to 4GB).
  • The host OS: Windows XP SP3 professional (although SP2 will probably do as well)
  • Has been tested on Windows 7 Home and shown no issues
  • Can work on Linux host machine (Ubuntu, Debian, openSUSE, Fedora etc)
  • Virtualization software: Virtual Box 3.1.2 (or higher). Tested and works with no issues on both Intel and AMD processors (may need to adjust some CPU parameters in configuration).
  • Database nodes: Oracle Enterprise Linux 5 update 3 as a guest OS, running Oracle Database 10.2.0.1
  • SAN/NAS emulation software: Openfiler 2.3

My reasons for choosing particular software:

Host OS does not really matter, virtualization software can run on many different platforms. If you desire so, you can run this whole project using Linux as a host. Virtual Box is selected because it is an open source software produced by Oracle.  Virtual Box at 3.1.2 was still owned by Sun Microsystems, who acquired it from Innotek and then maintained from 2008. Oracle maintains this product from 2010.

Why Virtual Box and not VMware? VMware is a popular product but I do not see a consistency in their support of free versions of the software. In my experiments with VMware I have encountered some obstacles while running Linux guests (particularly in networking and CPU clock synchronization). Virtual Box is an open source product and quite a mature one, well documented and supported. So far I was able to run OEL4 and OEL5 with no issues (even with no guest additions installed). I like the product’s built-in networking, it seems to be more flexible and stable. These are all my personal preferences, of course.

Why Oracle’s Linux? This particular flavor of Linux is a modified Red Hat Enterprise Linux 5. Oracle has made a few adjustments that allow Oracle 10g R2 database (32 bit) to install with no issues on it (usually, no additional RPM’s required). Since I am aiming to install Oracle in the guest(s) thus the choice of this Linux flavor. Besides, this Linux comes with OCFS2 and most of the ASMLib software packages installed. If you have a licensing question about Oracle Enterprise Linux (OEL) I can answer it. You only need a license if you want Oracle’s support. For learning purposes you do not need a license. If you will need to download some rpm libraries and updates you can get those for free, the YUM configuration can be pointed to a free repository (not discussed here).

Now the database itself. Which edition of 10.2.0.1 are we going to use? There is no clarity in that article on this matter, for now I assume the Standard Edition will do. There are some additional pieces of software from Oracle (or used by Oracle) to be installed, those are: OCFS2, ASMLib 2.0 and TAF.

Now that we know what we need to get started let’s split the work into more manageable mini projects, follow them just in this order, and hopefully all pieces will fit:

Update (2015). There seems to be a viable alternative to Openfiler from the FreeNAS. First off, it is Free. Second, it is open source (or, maybe that was first). Then, they give you full documentation. There was time when we were on our own with Openfiler, as they wanted money for the tech documentation.

July 18, 2014

How to check patchlevel of Oracle Database

Filed under: Oracle Database, Uncategorized — oraclelabs @ 11:18

One way would probably be going through Oracle Inventory, assuming that it is in sync with the actual software. Another way is to query the registry (the registry history).

The following query applies to 11g.

select
b.host_name,
b.instance_name,
to_char(a.action_time, ‘yyyy-mm-dd hh24:mi:ss’) action_time,
substr(a.id,1,10) id,
substr(a.action,1,15) action,
substr(a.version,1,8) version,
substr(a.BUNDLE_SERIES,1,6) bundle,
substr(a.comments,1,30) comments
from sys.registry$history a,
v$instance b
order by a.action_time desc;

If you are into running this query in sqlplus, you can use these column settings:

set lines 130 pages 60
col host_name for a15
col instance_name for a8
col action_time for a20
col id for a8
col action for a15
col version for a12
col bundle for a6
col comments for a30

June 6, 2013

On DATE and TIMESTAMP math

Filed under: date and timestamp, Oracle Database — oraclelabs @ 15:38

Ever faced with a need to specify a time range like this: “between first and last day of the previous month”?

So how about this one:

select
add_months(trunc(sysdate,’MM’), -1) “First Day of Prev Month”,
trunc(sysdate,’MM’) “Last Day of Prev Month”
from dual;

OK, this was easy. But what is they say “between first and last second of the last day of the previous month”
Well, we just subtract an interval of 1 second:

select
add_months(trunc(sysdate,’MM’), -1) “First Day of Prev Month”,
trunc(sysdate,’MM’) – interval ‘1’ second “Last Day of Prev Month”
from dual;

Good, they say. Now how about give us all this in timestamp, not a date type.

Precisely, give us the range “between first moment and the last microsecond of the last day in previous month”. That was a long thing to say.

So here it is, in dollars, cents and microseconds:

select
to_timestamp(add_months(trunc(sysdate,’MM’), -1)) “First moment”
to_timestamp(trunc(sysdate,’MM’)) – INTERVAL ‘0 00:00:00.00001’ DAY TO SECOND(6) “Last moment”
from dual;

Was it a microsecond or millisecond? I always get confused with kilos and pounds…

May 2, 2013

New in Oracle 11g. Rollback on exit in sqlplus

Filed under: Oracle Database — Tags: , , , — oraclelabs @ 10:25

For a very long time it’s been a standard feature of sqlplus to commit on exit. This feature could have been overwritten only by a “WHENEVER SQLERROR EXIT ROLLBACK”, but there was no control for rollbacks when we exit normally. Having autocommit disabled (I am grateful it is disabled by default) and now disabling commit on exit will be very useful in a situation when your sqlplus session is really interactive and you would not appreciate a commit if you exit accidentally.

Here comes in a handy new feature in 11g:

SET EXITCOMMIT OFF (Default is ON)

Refer to the documentation on this feature (there is also info on AUTOCOMMIT and how that affects EXIT behavior).

If you want to set this and other features in your sqlplus automatically, you can use what Oracle calls “Default Site Profile Script“:

$ORACLE_HOME/sqlplus/admin/glogin.sql

%ORACLE_HOME%\sqlplus\admin\glogin.sql

A user profile (login.sql) can also be used for this purpose, provided that it is located some place where SQLPATH variable points (this is also described in the doc links above).

 

For some reason, most of the Google search links on this subject led me to the “whenever” command, rather than to “set”.

March 13, 2013

Oracle Virtual Server (Oracle VM, OVM) inside Virtual Box


As if Oracle Virtual Box was not virtual enough, now you can put one dream into another dream, just like in my favorite movie Inception.

Get this, you can run Oracle Virtual Server (other names: Oracle VM, OVM) inside Virtual Box. Wrap you mind around that…

Now, for confused, Oracle VM Server is a product separate from Virtual Box and comes with it’s own history. As far as I know, Oracle VM Server is a Xen Linux based software that is not hosted, i.e. installs onto a bare metal hardware. Virtual Box, in turn, is hosted and can run within almost any known operating system. Virtual Box was not always Oracle’s product and not even Sun’s, it was originally developed by Innotek.

So… if you wanted to test drive a VM Server farm but don’t have some 4-5 physical servers lying around, you can still do so if you put it all in Virtual Box environment.

Oracle provides VirtualBox Templates for Oracle VM Manager and Oracle VM Server. From these templates you can create actual guests in Vritual Box. (Once again, this is not an installation media for physical servers but VM templates. If you are after that sort of media, you need to go to Oracle Software Delivery Cloud – Oracle Linux and Oracle VM).

To run this virtual farm you will need a lot of RAM on a single machine or, even better, run this setup on two machines. One PC can run the VM Manager and another will run one or two VM Servers. So much fun with the networking!

Follow the instructions on the download page, they are quite thorough. The only trouble I have had with version OVM 3.1.1  template (build 365) was that VM Manager comes with Oracle Express Edition 11g as an internal repository and it won’t start after guest starts for the first time. Turns out, there is a MEMORY_TARGET parameter in spfileXE.ora that is set to about 1GB and there was not enough free memory. Once this parameter was removed the database started and all works like a charm after guest reboot. I run this guest with 1.6GB RAM and the VM Servers needs 4GB at least (starts with 3GB but not sure if it works well). Honestly, I have not tried to create a guest inside VM Server yet, but I suspect I may need much more RAM for that (notice to self, update this post when I try).

One more thing. VM Manager is a browser based control interface for OVM, you can run it within your guest machine but it is better to do so from your PC. In that case you will need to check that firewall is turned off in the VM Manager guest (service iptables stop, then set it in chkconfig so it will stay stopped after reboot). Check firewall on your PC as well. Direct your browser to the guest IP address and appropriate port. If you somehow happened to install OVM 3.2.1, notice that http is now replaced by https and default port is not 7001 but 7002.

Good luck on your test drive.

April 26, 2011

VirtualBox 4.0.4 and network


It is always exciting to play with new release of this favorite toy of mine. Not this time though. There are some changes in release 4.0.4 that I could not cope with. I was using release 3.2.12 before, it looks very modest and just works well. So first thing I have noticed in the new release was the sweeping changes to overall GUI, then I was puzzled by a disappearance of the button “New” in the “Virtual Media Manager” interface and finally, when installing OEL 5.2 (Red Hat), I could not get the Host-Only networks to actually work (the adapters were up and running but not accessible from host).

Some people have already complained about the problem that manifests itself as error message:

“the value of the MAC address field on the network page is not complete”

You can look at the discussion on the topic here (but there was no resolution at that time):

http://forums.virtualbox.org/viewtopic.php?f=6&t=38253

According to some, the problem surfaces when you have more than one network interface, according to others it is only with some operating systems. As of this moment I gave up on this new release and reverted back to 3.2.12

February 10, 2011

Shareable disks in Virtual Box


Recent versions of Virtual Box (4 and up) have support for “shareable” disks. This feature lets you build a RAC without using Openfiler or some other sort of SAN/NAS simulation software. Saves you one virtual machine in the setup.

I am not sure if this setup can be used with Oracle 10g RAC and whether this feature is stable enough, but here is an article by Tim Hall that describes Oracle 11g RAC built around this kind of disks.

Update: created shareable disks in 3.2.12 and used them under OEL 5.2 (you still need some clusterware-like software to properly share the disks).

November 18, 2009

Linux, UNIX and Oracle – automating database startup and shutdown

Filed under: Oracle Database — Tags: , , , , — oraclelabs @ 15:14

How to automate database startup and shutdown when your server restarts.

Technorati : , , , ,

Create a free website or blog at WordPress.com.