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 (terminal 10.2 release) was likely next spring. This seems incredible to me as 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

Complete Upgrade to 11g – Karen Ambrose

This presentation detailed how Karen got on upgrading a cluster up to, 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 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.


    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!

    UKOUG RAC & HA SIG 11 February 2009

    Cold journey downwards to Slough, and somehow I managed to completely tear to shreds the back of my shirt  just by stretching too much, a bit like the Incredible Hulk. Oh dear, at least I did not turn green.

    Astonishingly quiet attendance at the first RAC sig of the year. Not sure whether it is a sign of economic doom and gloom, or a reflection on the agenda, which seemed a bit light, and far too much vendor led.

    Julian Dyke outgoing chairman gave a valedictory speech as he has to stand down as chairman of the RAC SIG, now that he has been elected to the UKOUG board.

    The days agenda has a theme around replication – not a bad idea having a theme, as I say just a shame it was so vendor led.

    Survey of who is using what still going:

    #nodes in cluster:  vast majority of 2 a sprinkling of 3 and 4.

    vast majority running on Linux almost 50/50 32bit and 64bit, majority on RHEL. none on oracle enterprise linux.

    sprinkling on windows, a little bit more on Solaris Sparc, and a couple on HP-UX.

    A few on 9.2, a couple on 10.1, vast majoirty on 10gR2, no one has got 11.1 RAC in production.

    No one using RAC with iSCSI, a couple on NAS, and most on FC.

    EMC largest storage vendor, a few HP’s, IBMs, Netapp.

    A good number using ASM, a few with OCFS1/2

    I think these survey numbers have been fairly constant for a while now.

    Oracle Support Update – Phil Davies.

    Usual support update from Phil.

    Of note is that will be the terminal release, for 10gR2.

    Looks like an interesting bug in Rolling upgrade to ASM fails MetaLink Note: 742647.1 There is a patch for this issue.

    Streams Based Replication – Martin Bach

    Martin is one of the most knowledgeable guys around, and in December he obtained the Oracle Certified Master qualification.

    This was a nice presentation giving a grounding in how Oracle Streams works.

    Streams primer:

    • Captiure
    • Propagation
    • Apply

    Streams is versitile but complex, the Oracle documentation runs to 3 manuals just for streams. As streams is not really a well used database feature there can be a lot of bugs with it. Note 413353.1

    There was also a good discussion of some issues Martin has encountered while running streams.

    Minimising downtime when upgrading/migrating Oracle with Goldengate – Ian Richardson

    Goldengate does replication including bi-directional replication

    The architecture of goldengate seems quite similar to oracle streams, has a capture, paropagate, and deliver (or apply) process. It’s completely platform independent can use it to go from 1 type of platform to another, or indeed 1 oracle version to another. It does seem like a great way of minimising downtime during migrations/upgrades.

    Lessons From a 2 year stretch with an extended RAC Cluster – Pavel Rabel

    Extended RAC is having RAC nodes separated by quite a distance. This is lessons from BT.

    In 11g docs Extended RAC is becoming an accepted architecture. ASM with normal redundancy can be used to spread the data across multiple disk arrays in the separate datacenters.

    30KM between the two datacenters, I think he said 0.2ms ping round trip time.

    Used ASM to migrate from original storage to new storage

    Could not add/remove css votedisk while nodes were up, cluster downtime was required. Fixed 11g

    ouch! They run dd on the incorrect disk

    Created 3rd failgroup which meant some extents got mirrored within a datacenter

    ASMLib using emcpower device, but after restart single path devices used after restart.

    Found accessing local devices were 10-20% times faster than accessing the remote disks.

    11g ASM benefits for extended RAC

    ASM_PREFFERED_READ_FAILURE_GROUPS allows you to specify only local access to diskgroups – allowing secondary extents to be read as well, rather than just always primary extents

    FAST Mirror Resync changes are tracked

    Contiuent Replication & Oracle – Eter Pani

    I did not like this talk. I’m afraid it semed a little shambolic.

    Oh my god, he has just said it’s “almost working” does replication master-slave, and master-master might work someday. originally built with mysql. Might work with oracle.

    Continuent is cheap, that seems to be the main selling point.

    Continuent seems to work by extracting sql from the redo logs and firing these sql statements at the target database. No parallel applier.

    I don’t think you would want to use this product for serious Oracle deployments.

    Next meeting RAC & HA SIG meeting is 16th June in London, see you there!

    UKOUG 2008 – Friday

    Just for completeness, I thought I’d share my experiences of the final day of the 2008 UKOUG.

    Interpreting Execution Plans – Christian Antognini

    Packed in like sardines for Christian Antognini’s talk on reading explain plans, I’ve already reviewed his book, and this talk was covering chapter 6.

    First bit is a run through of getting plans. I think this is easy bit, the hard bit is actually understanding the plan you’ve found and then actually doing something about it.

    Ways of getting a plan are:



    select * from table(DBMS_XPLAN.DISPLAY_AWR (sql_id, hash_value)

    @?/rdbms/admin/awrsqrpt.sql – realy, really, useful

    Already used this script and it gives you a plan and  the resources consumed by this bit of sql – looks excellent, just plug in a time period when you know the sql was running and plug in a sql_id. Could not be easier to get historical explain plans.



    Reading plans

    execution plan is a tree with every node being an operation.

    parent has 1 or multiple child

    a child has a single parent

    only node in tree without a parent is the root

    child to indented to the right of parent

    parent id is always < child id

    about 200 types of operations

    3 different types:


    has at most 1 child
    generally children executed before parent (though not always)
    child executed at most
    every child feeds it’s parent.


    operations having multiple children that are independently executed
    every child executed only once

    related combine

    all operations having multiple children where one of the children controls the execution of all other children are related-combine operations.

    child with smallest id controls the exectuon. children are not executed sequentially but a kind of interleaving is performed.

    only the first child is executed at most once. All others may be exected several times or not at all.

    not every child feeds it’s parents.

    Chris showed examples of each type and had quite a large example to follow. He showed quite nicely how to split the larger plan into chunks of the type he covered, but when you just see the big plan, it is really hard to know where to start,.

    At the end Chris mentioned the extended explain plan information were you compare what the optimiser estimates to be the number of rows returned by a query, and the actual amount it really got when it ran. When the estimate and the actual vary by a lot, this is often where a plan is going awry.

    Another way of trying to optimise plans it to spot how many rows are returned and if there are steps that are throwing away large numbers of rows then try to get these steps down as early as possible – so there is less throwaway work being done – which is back to what Dan Fink was saying on Thursday.

    Advanced Oracle Troubleshooting – Tanel Poder

    For my money, this was the best presentation of the whole week.

    I’ve been avidly following Tanel’s blog for a very long time and he was basically showing his systematic procedure for obtaining the cause of various issues. This was mostly demos which he handled with aplomb.

    Most interesting for me was using pstack to see what a process was up to.

    UKOUG In Summary

    So that is UKOUG over for another year. 5 days is probably a bit too much for me, I’ll be happy when it’s down to 3. I think I learned this year that you must rehearse before you present, rather than just rely on the fact you have presented the same material 3 months earlier. If I get to present next year, maybe I’ll wait to write it to closer to the conference – I’ve been presenting on ASM for the best part of a year now, but I think it’s time to get back to dataguard!

    I also learned this year that the after conference beer is probably more interesting than the actual presentations. Though it seems like I missed out on of the best presentations this year, “Drive Heads Revisited”.

    UKOUG 2008 – Thursday

    Too many late nights in the pub meant I started off the day feeling a bit rough, and I managed to pay £15.75 for a bowl of museli. Hotel breakfasts really have an enormous markup.

    CERN – Luca Canali & Dawid Wojcik

    Good slide on why they use ASM, in particular they are using it to hook up multiple RAID arrays and use ASM to mirror and stripe across them.

    stripe alignment

    Luca stress tested ASM using a 14 node RAC cluster connected to 26 storage arrays with 16 SATA drives per array.

    seeing IOPS per drive of 100 and this scales all the way to 40K IOPS with 410 drives.

    WOW. CERN have 550TB of storage.

    LUCA seeing that a rebalance operation moves more data than you would initially think it would: excessive re-partnering.

    Not using ASMLib but use device mapper instead.

    partition disks to ensure only outer/faster part of the disk is used for storing data.

    Some scripts that give them devid, device name, multipath name, & status.

    RMAN Optimizations- Stephan Haisley

    unused block compression

    now using locally managed tablespace bitmap. if extent is not allocated. Gah only works to tape with oracle secure backup  – that is a marketing decision not technical. Does go to disk

    Does not work when you have a restore point set!?!

    Undo Optimization

    in 11gR1 undo blocks that are part of committed transactions are not backed up. reduces the number of blocks in the backup, uses SCN numbers. Again only to tape with Secure backup. Though will work to disk

    Backup Compression

    10gR1 using bzip2 great (~70%) compression but slow. 11gR1 added ZLIB compression.

    To use ZLIB you need advanced compression option

    Multisection Backups

    11gR1 split large datafile into smaller contiguous blocks or sections created in parallel. if backup fails it can restart from the failed section.

    When doing block media recovery the recovery will only use the section required rather than have to read a full huge backup of a datafile.

    Good demo of Data Recovery Advisor, which Stephan had a hand in creating.

    Virtual Reality?: Can Oracle VM deliver the virtual RAC Grid – Steve Shaw

    I really quite liked this presentations and I did learn a lot. Virtualisation in some ways is like an oncoming train and I suspect for some people it may be get aboard it or get ran over by it.

    In fact, in some ways Steve’s style of presenting is a bit like a bullet train he really barely stops but he packed a lot of good stuff into his presentation.

    RAC is supported on Oracle VM with 32-bit linux
    there is a choice between HVM (Hardware virtualisation) and PV (paravirtualized)

    PV means the guest O/S has to be modified.

    Hardware virtualisation built into the chip

    talking about live migrations (to a different server) of RAC hosts using Oracle VM – not yet possible, but one day?

    performance of paravirtualisation better than HVM. However Intel VT-d seems to make a huge difference to performance, graph showing perf almost as good as native.

    Inside ASM – Julian Dyke

    Julian’s presentation concentrated on how extents (including the metadata) is placed on disk in normal and high redundancy and what happens to them when you perform various rebalancing tasks.

    Variance as a Tool – Robyn Sands

    Robyn has such a great way of presenting she appears very calm and in control while on stage and has such a mellifluous voice that you could listen for longer than 45 minutes.

    Robyn comes from a process engineering background, and I think the main idea was to check for processes/sql that have wild variations in response time and try to understand why this is happening as these tend to be the problematic sql statements – though some variation could be due to cyclical events.

    Optimising by throwaway – Daniel Fink

    I think there is a lot to this technique – find where in an explain that lots of rows are being processed and then later on in someway being filtered out and tune this part so that you do less of the work that is getting thrown away.

    This idea makes good logical sense I think. Dan is obviously a highly accomplished speaker but it was a real shame he got the timing badly muddled up and spent really far too long explain the (pretty simple concept) and then had practically no time left to show the power of the idea in practice – I thought a wasted opportunity.

    Spent a long time in the pub afterwards.

    UKOUG 2008 – Wednesday

    HA Roundtable

    Oracle’s Stephan Haisley was asking if you could have any changes implemented in the Oracle HA sphere would they be?

    I was thinking I would like physical standby to be able to operate across versions so you could upgrade your standby to say 12 while your primary is 11 sync it back up and switchover – giving you a very quick (and easy) way of upgrading database versions.

    Not many people using the Observer.

    Some chat about licensing Oracle when using clusterware to failover a single node instance. You don’t need a RAC license just to use clusterware or indeed run clustered ASM.

    Moans Norgaard – Shock Upgrades

    Very funny talk.

    “All developers have left earth and gone into the cloud where they will reinvent the things that the database can do already”

    Talking about a customer who implemented 4-node windows RAC had major problems, mentioning 9 months of pain and the customer is moving to 1 node RAC!?!

    The danish experiment involoves using the diagnostic & tuning pack with Real Applications Testing.

    Idea is to capture workload and replay it into the 11g upgraded production copy.

    back to the real world:

    Upgrades are hell!

    10g EM did not work for capture – but PL/SQL did

    11g PL/SQL only gives XML reports!?!

    nice and useful replay reports.

    With these caveats RAT seems to work.

    Moans stating that the upgrade does not work, but now talking about going 9i  ->

    Not clear yet why a  -> 11g upgrade did not work. Saying upgrades/patches have become worse, not really seeing that between 10g versions.

    So a real shock upgrade is going -> 11g

    Saying no one wants to upgrade to 11g

    Oracle Performance Management – Gaja Vaidyanatha

    Performance tuning methodology

    Trap: though normally true, the most expensive step from an elapsed time point of view is not always the step with the most rows processed.

    service time = f(hardware specs, laws of physics)

    wait time = f(wasteful processing, resource contention)

    8 real world case studies

    case study 0 shows 15 logon per seconds – mw not using connection pooling.

    More case studies, perhaps the most interesting at the end which was a Solaris 10 problem and one which the standard performance methodology could not help with , needed to look at O/S.

    This talk was at least 1.5 hours, and I just felt it was maybe just a bit too long!

    Dataguard 11g New Features – Michael Moller

    This was meant to have been delivered by Carl Jan Engel but he could not make it.

    This was a run through of some of the more interesting new features with good observations on what new features were useful, and which were pretty pointless. I found it particularly amusing when he used the term the broken rather than the broker.

    Michael’s demos went AWOL, but I don’t think it harmed things too much.

    It’s a shame that most of the good stuff is a paid option, including active dataguard.

    The evening was the 25th Anniversary party with the night being rounded of far too late in the tap & spile

    UKOUG 2008 – Tuesday

    Today was mostly a working day for me, so I did not get to see many presentations. I did however manage to bunk off and attend my good friend and colleague Patrick Hurley’s presentation.


    His talk was titled “Practical Data Pump” and a very well attended Hall 5 saw a couple of very well executed demos. I know Patrick worked really hard on this and the reaction from the crowd was pretty positive so it looked like the work paid off in spades.

    Though to be fair, his jokes are still terrible.

    In the evening I was at the UKOUG focus pubs to hear Julian Dyke get elected as a Director of the UKOUG. Afterwards had a really entertaining evening with the great CERN DBAs, and the exuberant (mad) RAL folks.

    Three more pretty full conference days to go!

    UKOUG 2008 – Monday

    I arrived on the Monday morning for the UKOUG this year, as I figured I’d sleep best at home. The train was packed and it was £46 to stand all the way. And there I was hoping for a nice hour or so to go over the presentation a final time 😦

    Oracle 10g Streams: Data Replication – Kirtikumar Deshpande

    I’ve always thought streams was more complicated than dataguard. I still think that after this talk, but DBMS_STREAMS_ADM.MAINTAIN procedures seem to make streams a little bit easier to set up.

    Streams is a free option with Enterprise Edition. Streams is a solution for data sharing.

    3 functional components of streams


    Asynchronously captures locally or at downstream database

    Extracts changes from the redo stream

    changes are formatted as a Logical Change Record (LCR)

    DML LCR has row change, object name, owner type of DML, SCN. Most importantly OLD and NEW values for columns.

    DDL LCR object name, owner, type of DDL, SCN, DDL text

    propagation (staging)

    capture publishes messages into an in-memory staging area: streams pool of SGA

    Messages remain in staging area until consumed by all subscribers


    Apply engine will directly apply the DML or DDL represented by the LCR.

    automatic conflict resolution.

    Rule based configuration

    rules govern capture, staging and apply

    Demo of streams being set up with the SCOTT schema using DBMS_STREAMS_ADM.MAINTAIN_TABLES

    even less information required when using DBMS_STREAMS_ASM.MAINTAIN_SCHEMA

    set _job_queue_interval = 1 (10g recommendation)

    streams_pool_size = 200M (minimum set at source and destination)

    for wans set SDU

    streams uses data pump, dataguard, & advanced queueing.

    In chat later in the day, Martin Bach mentioned that streams was still prone to lots of bugs and perhaps was not all that reliable.

    Introduction to ASM & ZFS – Me

    Well it went ok, but I thought I was a little flat at the beginning. It was a small audience and the stage was also quite a small creaky affair.

    Good discussion sprung up amongst the audience around 1/2 way through, though as Doug Burns points out, the question about the SAN was a little bit odd.

    Some really nice feedback from Joel Goodman at the end with some tips for improvement.

    FAN – Bernhard De Cock Buning

    A really small turnout for this, maybe a post lunch thing. Bernhard’s talk was based around quite a few demos, but unfortunately about half way through his laptop gave up the ghost. He stoically carried on, but I think the lack of demos really took the wind out of his sails – what an unfortunate time to have a laptop failure!

    Thank Goodness for Dataguard – Rachel Slade

    Rachel runs the DBA team at Oxford Brookes, which is just up the road from Nominet, and incidentally where my partner works. Her talk was a good user group talk, in that it consisted of her real world experiences rather than what *should* happen.

    Essentially during maintenance/power outages brookes suffered some problems with their storage meaning that had to utilise their dataguard instance. They are really just using dataguard as a “hot” offsite backup, and essentially performed rman backups to recover then rolled forward using the archivelogs found at their DR site.

    I personally felt this architecture lacks a little innovation, i.e. Brookes has no potential to failover/switchover to their DR site,  but Rachel was stressing the point of keeping it simple and they have seen a return on their dataguard investment.

    The Best Way – Tom Kyte

    Tom is such an engaging speaker that the time just flies past when he is on stage. The best bit for me, was the cartoon on questioning authority – always question, question, question – I was just saying that to my partner the other day that this is how one should always be and it’s the attitude I want the kids to have.

    Come to think of it, I pretty much constantly here why, why, why at home!

    ‘Twas the night before UKOUG 2008

    I’ve not quite made it up to Birmingham yet, decided to travel up in the morning. My presenting slot is 12:20 on Monday, so I’m hoping for a good nights sleep at home beforehand, I don’t travel well (or often) and can find hotels take a while to get used to.

    I’ve stayed at the City Inn in Birmingham for the last 7 or 8 years, but this year we have a new system at work and I’ve been booked into the Copthorne. I’ve just looked up trip advisor and was delighted to find that most guests are royally slagging it off. Dirty, noisy that kind of thing. The review mentioning the mice particularly caught my eye. It’s not cheap either!

    This year as last I’m scheduled to speak at the same time as Tom Kyte. This has a couple of drawbacks – Hall 1 is obviously taken 😉 – I’m in Hall 11a this year, and I can’t get to see Tom myself!

    This year I’m particularly looking forwards to Chris Antognini’s 2 hour masterclass, as well as the 2 hours from Tanel Poder. These are both on Friday, which should make it a great end to the conference – if I can keep concentrating until then.

    With it being the 25th Anniversary of the UKOUG there are quite a few parties lined up. I’m hoping it will be an entertaining few days – the weather being suitably damp and cold – well it is December in Birmingham, sure ain’t San Francisco in September.