UKOUG RAC & HA SIG June 2009

It was a funny start to my day, my kids woke up in the middle of night, and at 03:00am my fire alarm went off, which gave me a quite a fright, though thankfully nothing was actually on fire. So I was pretty knackered as I got into London on a beautifully sunny day for the UKOUG RAC & HA meeting. So many familiar faces in the audience – which is great to see.

Preliminary Introduction – Dave Burnham

Before the survey of who is using what, Dave Burnham mentioned that the following meeting in Blythe Valley Park, may include an “expert panel” discussion forum. I think this could prove to be an excellent idea, though of course it’s highly dependent on the quality of questions discussed. Also there is the possibility of doing something on virtualisation – which is something I’m very interested in at the minute.

So onto the survey and as always most people have 2 nodes in a cluster with a handful on 3, 4. The highest in attendance today was 6 nodes. Vast majority running Linux and 64-bit, apart from a somewhat fed-up Martin Bach having the misfortune to be on 32-bit Linux. Not one person running RAC on windows, even though there was a presentation on running RAC on windows. A good quantity running RAC on Solaris SPARC.

The vast majority of users are using 10.2 with a handful having upgraded to 11.1, most people using Fibre Channel connectivity with HP being the most popular storage vendor, though EMC close behind, and netapp, IBM, Sun all on similar ammounts. A forest of hands for people using ASM, this is by far and away the most popular choice. A handful using Veritas, but again majority just using clusterware with no 3rd party clusterware.

As for App servers, Weblogic quite popular along with Oracle App Server. Only a few on tomcat, vast, vast majority using home written apps on their RAC clusters. A good amount using physical standby, one guy running active dataguard. Almost everyone using oem. Only 1 person has used Real Application Testing with their RAC install.

Oracle Support Update – Phil Davies

Thing I took away was the increasing importance of patch bundles, several of which were mentioned, in particular ones for Physical/Logical Standby. Phil had a throwaway point that he thought 10.2.0.5 (terminal 10.2 release) was likely next spring. This seems incredible to me as 10.2.0.4 was released (for x86-64) in March 2008 so that would be a 2 year gap. Seems quite astonishing, it’s not like there are not already enough one of patches to make up a patchset I think there are already 500+ one of patches on top of 10.2.0.4.

Complete Upgrade to 11g – Karen Ambrose

This presentation detailed how Karen got on upgrading a 10.2.0.2 cluster up to 11.1.0.6, including Clusterware, ASM, and the RDBMS. She chose to use DBUA and it all seemed to work fairly well, apart from the ASM instance which she said she had to upgrade manually. They also then went upto 11.1.0.7 with an ASM rolling migration. Strange point that they are using multiple listeners, one for ASM and one for the RDBMS. Joel Goodman pointed out this was unnecessary, and I have to say there did not seem to be a compelling reason for running the 2 listeners. The wanted to go to 11g to utilise the secure files feature. They have separate homes for the ASM and RDBMS instances, and have multiple RDBMS versions.

Whats the Point of Oracle Checkpoints? – Harald Van Breederode

For me, this was the stand out presentation of the day. What in the face of it is a fairly mundane topic was explained in such a clear way, with outstanding demonstrations.

SGA Buffer management via double linked lists. List for buffers in use, and list for buffers that available to be used for i/o – this means full SGA does not have to be scanned to find free buffers. Another double linked list is the ckpt-queue.

A checkpoint is a synchronisation event There are lots of different types of checkpoints:

full checkpoint

This writes all dirty buffers from all instances.

thread checkpoint

This does all dirty buffers from one instance in RAC cluster.

file checkpoint

This writes dirty buffers belonging to one tablespace. Caused by taking a tablespace offline, setting it read only or a begin backup statement.

parallel query direct reads

Parallel query does direct reads and this causes dirty buffers to be written out in a parallel query checkpoint. Can be odd that a query can cause lots of write activity.

object checkpiont

An object checkpoint can come about due to drop table and truncate table commands. Caused by the need for Point In Time Recovery.

incremental checkpoint

Incremental checkpointing writes some of the contents out from the CKPT-queue continually, the idea is to dribble out the writing of the dirty buffers rather than having a checkpoint occur in one big splurge. Ensures the fast_start_mttr_target parameter can be maintained.

Sizing your redo logfiles is critical for keeping checkpoints under control. Harald was emphasizing that you can’t make your redo logs too big but you can make them too small. Point was made about using archive_lag_target to control how frequently you have a log switch when you have very large log files.

V$instance_recovery can be very useful in finding out what is driving your checkpointing and what is an optimal size for your redo logs.

Harald stated that in some future version of Oracle LGWR will be the only mechanism for sending redo to a standby no more ARCH – though if you have a network disconnect and have to ship archived redo, not sure how it will do that with LGWR.

Achieving High Availability using Open Source Technology – Andrew Hughes

This talk was how they were using OCFS2 to provide shared storage and connect in multiple nodes, but then use cold failover to provide some form of high availability of instances. It kinda did what they set out to achieve, but as Joel Goodman pointed out, so much more could have been achieved using Oracle Clusterware to automatically restart failed instances and would provide VIPS to avoid the net timeout issue.

Oracle 11g RAC On Windows – Dave Bennet

I think Dave had a bit of an uphill struggle here. Not a single person in the room had a RAC database running on windows, and the main thrust of the presentation seemed to be issues encountered in running on 32-bit Windows, and SE edition of windows at that.

Database Links Masterclass – Joel Goodman

Attending a Joel Goodman presentation really is like trying to drink from a fire hydrant. Joel’s knowledge is truly immense, both in breadth and depth. This was no exception, though it was a shame he ran out of time with the demo, and I think instead of saving the demo all up for the end, that doing a similar thing to Harald and presenting some theory, then a demo of that theory, rather than attempting to digest all the theory may have been a bit easier on the audience!

The next RAC & HA SIG will be on 10th September in Blythe Valley Park in the West Midlands. Hope to see you there.

Fixing up ASM Disk Header Corruption

I’m sure this sort of stuff would never happen to you, you would be far too smart for that.

I was involved in a migration project, that was moving data from one set of drives to another, for space reasons just the drives were being replaced the actual chassis was remaining in place. So this involved replacing a handful of drives at a time, migrating data then rinse and repeat until the capacity of the array was increased. So this project did not require Albert Einstein levels of genius just a little bit of forethought and planning.

Documentation, Documentation, Documentation

You can see that there are several degrees of abstraction in going from the physical disks all the way up to what Diskgroup ASM is presenting to the actual RDBMS. So in this example the RDBMS is using a Diskgroup called DATA to store the various datafiles that make up the database.

This system was also using ASMLib as well as EMC Powerpath for device multipathing.

Now, none of this should have been a problem, in a well documented system the linkage between which physical devices were actually being used in which diskgroups should have been clear. Unfortunately, this system has grown somewhat organically over time accumulating more and more devices.

I went around checking which devices were in use, just in case there were any devices free: the more free physical devices the better to migrate onto the new larger drives.

In particular I was checking which particular devices were marked as being used with ASM. In our use of ASMLib the naming convention was each device was stamped with a volume name of the form VOL#. So in theory each device should have been marked liked that, any device not in use by ASM should have been able to be reclaimed.

Corruption Leading to Confusion

In performing this check I was the /etc/init.d/oracleasm querydisk command and feeding in a device path:

[jason@bdc]$ sudo /etc/init.d/oracleasm querydisk /dev/emcpowera1
Disk "/dev/emcpowera1" is marked an ASM disk with the label "VOL1"

So that is all well and good, and then I ran into the following:

[jason@bdc]$ sudo /etc/init.d/oracleasm querydisk /dev/emcpowerm1
Disk "/dev/emcpowerm1" is marked an ASM disk with the label ""

Huh? Now that did seem odd. I was sure all devices in use had the label VOL#, So I did what a DBA in a hurry to migrate drives might do, and thought this device could not be in use. So I tried to delete it:

[jason@bdc]$ sudo /etc/init.d/oracleasm deletedisk /dev/emcpowerm1
Removing ASM disk "/dev/emcpowerm1":                       [FAILED]

When In a Hole – Stop Digging

At this point I should have stopped and really had a think. In fact I should have checked the disk header to see exactly what was going on with device. I did not not. I incorrectly assumed this was a device that had been in use and was in use no longer. I removed it at the storage level.

After this ASM started up fine and the database even got to the mount stage. Do you think the diskgroup would come online that the datafiles were on? Nope. It was a goner.

I’d just removed a Volume that the diskgroup containing the RDBMS datafiles were depending on. Not only had I removed it from the server, I’d even gone as far to unbind the LUN at the storage array level. Just to make sure it really was a goner.

It was looking like a career limiting move. Thankfully, 7 hours later on the telephone to EMC support, the LUN was able to be resurrected. But that was not the end of the story. ASM still could not understand what to do with this device stamped with “”. I now checked the header of the device:

So this device was actually called VOL7 and part of DATA4 diskgroup, which contained the datafiles for the RDBMS. However now compare this to a device that is labelled correctly:

Seems like a part of the disk header has become corrupted. The following line:

0000040 O R C L D I S K

Should in fact contain the following:

0000040 O R C L D I S K V O L 7

Somehow the VOL7 part of this line has been removed.

KFED to the Rescue!

So the database was down, a volume was missing from the diskgroup because the diskheader was corrupted. Not a good place to be, but I was sure the data was still intact, I was sure it was just a matter of fixing up the header and all would be well. I had heard of kfed before this, and I was wondering if this would be the key. I ran it against my corrupt device:

I could see that the line that had the problem was the following:

kfdhdb.driver.provstr: ORCLDISK ; 0x000: length=8


While running a metalink search for kfed, I came across Note: 787082.1 which, while about a completely separate bug, shows you how to edit the provstr of the disk header:


[jason@bdc]$ sudo /etc/init.d/oracleasm force-renamedisk /dev/emcpowero1 VOL7


And that was it! The ASM diskgroup could now find all the volumes it needed to bring the diskgroup back online and the database came back fine. I'm pretty sure any reboot of this server would have led to this device being unrecognised by ASM so was really just an accident waiting to happen, but still maintaining good documentation can never be underestimated.

Improving an Index Range Scan

I recently had the opportunity to perform a little bit of tuning work, and I thought I would share this here. I think tuning is one of the more satisfying things about being a dba, where you can actually see that you have a made a concrete change for the better.

The query in question was quite simple of the form:


SQL> select count(*) from ns where id = :B1 and deleted is null


The table is of a reasonable size with around 40 Million rows. This is an OLTP environment. Yes there was an index on the id column and indeed the optimizer was choosing this:

----------------------------------------------------------------------------------------------------
| Id  | Operation		     | Name		   | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |			   |	 1 |	11 |	 2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE 	     |			   |	 1 |	11 |		|	   |
|*  2 |   TABLE ACCESS BY INDEX ROWID| NS                  |	 1 |	11 |	 2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN	     | IX_ID_NS            |	 1 |	   |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DELETED" IS NULL)
   3 - access("ID"=TO_NUMBER(:B1))

It’s all Skew-Whiff

So far not all that interesting. However this data is extremely skewed. The id column is not a primary key, and the distribution of id’s looks like this:

SQL> select min(id), max(id), median(id), avg(id)
          from ns;

   MIN(ID)    MAX(ID) MEDIAN(ID)    AVG(ID)
---------- ---------- ---------- ----------
	 1	10774	       1 4.64528267

That does not really do justice to how this data is skewed. So I’ve counted up the number of times each particular id value appears in the table, then done an aggregate of how often each count appears. A little snippet of the results set should explain the data:

1   26816448
2    2064686
3     219876
4      34708
.
.
.
135931     1
136166     7
136167     1
140111     3 

So what are these numbers saying? Well the most popular number of times an id value appears is 1, and there are 26M id values that appear just the once. Next is the count of id values that appear twice in the table and so on.

You can seen from this that there are 3 id values that appear 140,111 times in the table.

Plotting this with on a logarithmic scale looks like this:

So how is this affecting the query?

Well as stated the simple range scan is just fine for the 26Million id values that just appear this once, but whenever an id is used that appears far more frequently the index range scan is not a great idea:

SQL> select count(*) from ns where id=16318609 and deleted is null;

  COUNT(*)
----------
    108559

Elapsed: 00:02:30.35

2 and a 1/2 minutes for an OLTP system is a bit of a problem. Looking at the extended plan information we can see the issue:

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation		     | Name		   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE 	     |			   |	  1 |	   1 |	    1 |00:01:36.12 |   64371 |	56298 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| NS           	   |	  1 |	   1 |	  102K|00:01:35.80 |   64371 |	56298 |
|*  3 |    INDEX RANGE SCAN	     | IX_ID_NS            |	  1 |	   1 |	  132K|00:00:05.83 |	 458 |	  444 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DELETED" IS NULL)
   3 - access("ID"=16318609)

The above is an extended trace and it shows how many rows the optimizer thinks it will get from each operation and the actual number of rows it really found when the database had to do the work for real. You can see the statistics are are out by 6 orders of magnitude for this particular chosen id value.

However even if the optimizer exactly knew the correct number of rows to return, the only other plan available is a full table scan, which is not all that great on a 40M row table.

Improving the response time

Hopefully I took some of Jonathan Lewis’ advice and considered how best to get the data and with this simple query the best implementation really is to avoid going back to the table at all, so creating a fatter index on both the id and deleted column seemed to be the way to do it:

SQL> create index id_removed on ns(id, deleted) parallel 3 nologging;

SQL> select count(*) from ns where id=16318609 and deleted is null;

  COUNT(*)
----------
    108559

Elapsed: 00:00:00.54

That is a pretty pleasing two orders of magnitude improvement in the response time of this query.

Of course best response time of all would have been not to have to run the query at all, but that’s another story

UKOUG 2009 Annual Conference Call for Papers

The call for papers for the 2009 UKOUG Annual Conference Server Technology & E-Business has gone out.

The conference will take place Between 30th November – 2nd December, and as usual takes place at the ICC Birmingham.

The deadline for submissions is 5th June, so get your papers in while you can. Question is, will I be up against Tom Kyte for the 3rd year running?

If you are going, I may well see you there!

Mistake in April2009 CPU Release Instructions

I thought as soon as I saw this set of instructions that something was a bit fishy, a bit unusual:

The instructions are pretty clear and unambiguous: Apply the April2009 CPU to the CRS Home. I’d never seen this recommended on a clusterware patch before. This is actually really the only place in the release instructions for the CPU where it is mentioned.

I assumed it was just a case a changing the ORACLE_HOME to point to the CRS_HOME and running OPatch as normal:


oracle@linuxrac1:DBA1 /var/opt/oracle/product/admin/TAR/8290506> /var/opt/oracle/product/10.2.0/OPatch/opatch napply -skip_subset -skip_duplicate
Invoking OPatch 10.2.0.4.6

Oracle Interim Patch Installer version 10.2.0.4.6
Copyright (c) 2009, Oracle Corporation. All rights reserved.

UTIL session

Oracle Home : /var/opt/oracle/product/crs
Central Inventory : /home/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.4.6
OUI version : 10.2.0.4.0
OUI location : /var/opt/oracle/product/crs/oui
Log file location : /var/opt/oracle/product/crs/cfgtoollogs/opatch/opatch2009-04-28_09-38-05AM.log

Patch history file: /var/opt/oracle/product/crs/cfgtoollogs/opatch/opatch_history.txt

OPatchSession cannot load inventory for the given Oracle Home /var/opt/oracle/product/crs. Possible causes are:
No read or write permission to ORACLE_HOME/.patch_storage
Central Inventory is locked by another OUI instance
No read permission to Central Inventory
The lock file exists in ORACLE_HOME/.patch_storage
The Oracle Home does not exist in Central Inventory

UtilSession failed: Locker::lock() mkdir /var/opt/oracle/product/crs/.patch_storage

OPatch failed with error code 73

I then looked at the permissions on the CRS_HOME directory:


[jason@linuxrac1 ~]$ ls -ltar /var/opt/oracle/product/
drwxr-xr-x 45 root oinstall 4096 Jun 25 2008 crs


I even attempted to apply this patch to the CRS_HOME as root:


root@linuxrac1 8290506]# /opt/oracle/product/10.2.0/OPatch/opatch napply -skip_subset -skip_duplicate
Invoking OPatch 10.2.0.4.6
The user is root. OPatch cannot continue if the user is root.

OPatch failed with error code 255
[root@linuxrac1 8290506]#


Lots of head scratching ensued until Oracle support pointed out Support Note 786803.1, where it states:

If the home has CRS installed, then CPUApr2009 does not need to be applied.

Gee, thanks Oracle, might be an idea to remove that line from the actual instructions that get downloaded with the CPU!

As a bootnote, the CPU applied without a hitch into my 10.2.0.4 RAC instance.

Oracle Buys Sun

Well I had been advocating this for some time, but it looks like the deal has been done.

I started my career as a Solaris System Administrator and I cut my dba teeth working with Oracle running on Solaris. I am a big fan of Sun and love the design of (most) of their servers.

Will Oracle keep the hardware business going? Or will they flog it off as soon as possible? Will the exadata hardware move in-house?

Oracle plans to deliver these benefits by offering a broad range of products, including servers and storage, with all the integrated pieces: hardware operating system, database, middleware and applications.

Looks like Oracle may after all be really entering the hardware business!

As everyone will say Sun has some outstanding software, JAVA of course, but a lot of the stuff in Solaris is outstanding, ZFS in particular really, really rocks, DTrace being another. The mind very much boggles about mysql – a kind of starter Oracle database now?

However if you read the Charles Phillips letter one thing does stand out for me as being a little bit on the side of an exaggeration:

The Sun Solaris operating system is the leading platform for the Oracle database

Hmm, I’m not sure the release cycles would really indicate that.

It is going to absolutely fascinating seeing what products survive, which products get binned and which products get flogged off to the highest bidder.

One thing is for certain, job losses are surely inevitable.

Mac OS X on a Dell Mini 9

I quite luckily had the good fortune to win a Dell Mini 9, in a rackspace event raffle. It’s perhaps a little on the small side (8.9″) for prolonged work, but quick note taking and casual web browsing are quite bearable.

The netbook came with an 16GB SSD drive and 1GB of RAM, but sadly also had the exceedingly tired looking Windows XP on board. After a day or so of using XP I really had enough. Ubuntu is another netbook OS regular, but I’m so used to using Apple computers now (its been 4-5 years of OS X for me now) that what I really wanted was to get back to my familiar territory.

Thankfully, installing OS X on the dell mini 9 is indeed possible (legalities may be another thing though…). The procedure is detailed quite nicely on the web, and while it did take a couple of goes, it really is so well documented that you don’t need to be an SA guru to install this.

So here is a (truly terrible) snap of it running:

Everything pretty much works as you would expect with OS X. I’m updated to 10.5.6 and wifi was picked up without issue at all. Spaces and expose work excellently, and I have evernote installed for synching meeting notes to my desktop and Macbook Pro. Speaking of the Macbook Pro, going back to that after using the mini 9 – boy does it seem HUGE, the keyboard seems like it’s made for a giant in comparison!

OS X seems to run so much snappier than XP, in fact the 1.6 GHz atom processor seems snappy enough for the simple tasks I’ll be using this machine for. Sure I ain’t going to be installing the new release of Oracle DB for OS X mind.

One top tip I do have is to install Eternal Storms PresentYourApps. This superb app, selectively hides the OS X taskbar – in a similar way to how you hide the dock. When you are working at 1024 x 600 – you need all the screen real estate you can get your hands on.

In my opinion Apple are nuts not to release a $499 netbook, OS X seems to work great on devices of this size and power – they could really own this market if they had put their mind to it. Question is would it cannibalize their macbook market – i’m not too sure about that as surely even the low end macbook has a more powerful cpu and much larger screen real estate than what any potential Apple netbook would have.

It does not matter to me now whether they do release a netbook, as I already have my OS X enabled netbook and it rocks!

Book Review: Mastering Oracle PL/SQL

I have never been a developer and I would never attempt to sell myself on the basis of my coding abilities. I come from an SA/storage background, which may put me in a minority amongst DBAs but is sure useful when you are talking about luns and ASM and I think I fulfill the criteria of DBA 2.0 as set out by Joel Goodman and Harald van Breederode.

However, I’ve often felt that I should brush up on my PL/SQL knowledge, but just never found a book that particularly appealed to me. Now I have. I’ve seen some of Connor McDonald’s fantastic presentations at the UKOUG Birmingham Conferences, but until now I had not read his book.

I have found Mastering Oracle PL/SQL to have been an absolutely cracking read. The book is subtitled Practical Solutions and that is what it delivers in spades, it is not just a walkthrough of the syntax.

One of the most enjoyable features of the way this book is written is how Connor shows different ways of tackling a particular problem and then provides concrete benchmarking of each of them so you can scientifically see which is the most performant solution and he then explains why.

Chapters I particularly liked were:

Chapter 1: Efficient PL/SQL

This is a big chapter and has a strong focus on performance. Connor starts by stating that it’s important to use the right tool for the right job and that by keeping it simple often by using PL/SQL it leads to less coding effort than fancy expensive fat mid tiers. I particularly like the statement:

“not using PL/SQL with Oracle is coding with one hand tied behind your back”.

The point was made that performance is all about what is acceptable to your users, not making something go as fast as possible. Any solution of course must also have acceptable concurrency levels as well.

Good discusion about parsing with some nice, clear examples of why you want to minimise this as much as possible.

Chapter 2: Package it All Up

As you may have guessed this is a chapter about packages, and Connor is a big fan of them. He goes on to show various advantages of packages, including a detailed look at how they may lead to less invalidations when you perform changes to your code. Liked the bit on package overloading here as well.

Chapter 3: Cursors

There is a bit of a comparison between implicit and explicit cursors – Connor thinks implicit cursors have had a bit of a bad press and goes on to show how they are actually often times the best thing to use and at least (if not more so) as performant as explicit cursors. He demonstrates his points about cursors with good scientific examples. Talks a little about pre-fetching and why this helps implicit cursors. Claims there are a lot of myths out on the internet about implicit cursors and they really are good thing.

Of course sometimes only an explicit cursor will do.

Chapter 4: Effective Data Handling

Really important to assign the correct datatype to a variable. Test harness to show the inefficiency of having to do implicit conversions – it’s definitely something to avoid.
Good discussion on %TYPE and how it’s used to tie the definition of a variable to how the column is defined within a table. Both %TYPE and %ROWTYPE make making changes to your schema much easier as the changes ripple throughout your PL/SQL rather than having to search everywhere to make changes.

Good amount of detail on bulk collections, showing with an example how more performant it is. Also very interesting discussion, diagram, and examples on context switching between PL/SQL and SQL. It’s something you want to minimise for sure.

Chapter 5: PL/SQL Optimisation

This chapter showed examples of minimising parsing and that you can’t just suck everything up into memory as you wont scale beyond a tiny amount of users, Connor showed examples of how much memory various techniques used. Basically large collections equals large amounts of memory used.

Bit about triggers and showing that coding the work inside a package is more efficient than doing the same work in a trigger: it does less parsing.

Code in SQL if you can (seen a caveat that sometimes PL/SQL is more maintainable sometimes than over complicated SQL) PL/SQL is an extension of SQL.

Chapter 6: Triggers

Big chapter on triggers. Begins with comparing before row triggers and after row triggers. The after row trigger being more efficient. Triggers run with the privileges of the triggers owner and run with roles disabled. Discussions on “instead of triggers”, mutating tables, and autonomous transactions.

I had not heard of table versioning available from DBMS_WM (workspace manager). Looks like a potential way of auditing changes in tables so you can see a history of them.

Chapter 8: Security Packages

Insightful details on definer rights and invoker rights. PL/SQL procedures only run with directly granted privileges NOT those granted through a role. Privs granted via Roles only enabled in execution under invoker rights, never under definer and never upon compilation.

All in all I thought this was a really excellent book, in particular the first 5 chapters really stood out for me, I surprised myself by how much I enjoyed reading them. It’s definitely not for the total beginner, but the first 5 chapters in particular I think can be picked up fairly straightforwards by someone with only slight exposure to PL/SQL.

The book actually came out in 2004 but I would not let that put you off, it covers up to and including 10gR2, so I think the techniques are still current.

I’d highly recommend this book if you have not availed yourself of reading it already.

Adventures in Dataguard

Here is a presentation on Dataguard I have given a number of times. In particular I go through several issues we have encountered while running a Physical Standby in production.

ASM, Pillar Data, and IBM XIV

There I was at a VMWARE event happily minding my own business getting up to speed on the major IT trend that is virtulisation. This was not really Oracle related, and ASM was very, very far from my mind.

However it seems I’m never very far away from ASM, as the event was run in conjunction with Pillar Data Systems and inevitably the fact I’m DBA came up in conversation and it turns out Pillar have added a special feature to their Axiom range of arrays all to take advantage of the ASM 1MB stripe depth.

The Impact of Stripe Size

Lets first consider what a typical 128K stripe depth would be like when writing a 1MB ASM coarse stripe:

So it requires 8 128K writes to write a full ASM 1MB coarse stripe, and any read of a 1MB ASM extent will require the participation of all the drives within the stripe set.

It’s not too difficult to imagine this may not be the best bet for scaling the number of concurrent requests. Of course with the 1MB stripe depth we have the following:

Basically breaking an I/O request into multiple requests over multiple drives hurts throughput. Oracle themselves advocate a 1MB stripe depth.

The reason being is that it strikes the correct balance between ensuring disks are spending more of their time transfering data compared with the seek time, but at the same time allowing multiple drives to come into play.

Another interesting feature of Pillar Data systems is it’s ability to have multiple stripe depths within a RAID set. This enables you to store your redo, and control files with the fine grained ASM stirpe of 128K in a stripe depth of 128K.

XIV – ASM in hardware?

IBM’s XIV Storage came on the market last year and to me seems like it contains a lot of the features of ASM but done in hardware.

Each logical volume within a XIV array is divided into stripes of 1MB in size. XIV also uses a fairly familiar mirroring algorithim in that copies of the 1MB chunks of data are kept on 2 independent physical devices. Does this sound familiar to ASM users? It should.

These stripes are spread over all disks within the system

Like ASM it does not use traditional mirroring and there is no conept of devices being mirror pairs of each other.

Not that ASM needs much vinidication but they do say that imitation is the sincerest form of flattery, but clearly the ideas behind ASM have been found to be good ideas by other vendors as well.

Of course these stonking great arrays don’t come cheap and at least with ASM you are getting some of this redundancy for free with your Oracle database license. Though it would be nice if there was some snapshot or clone features included in some future ASM release.