I was chatting with the lady doing OCP Lounge registrations at OOW11. During this chat I mentioned I hadn’t received a certificate for the SQL Expert certification. It never crossed my mind to re-request it, since my certifications are visible on certview.oracle.com anyway. Yesterday, a DHL man delivered the missing certificate, which prompted me to look though my certifications and scan this image.
First, check out the card on the bottom right. I was unaware the “Expert” certifications had a different colour card.
Second, notice anything funny about the 9i DBA OCP certification?
It’s hard to believe it’s over 12 years since I first completed one of these certifications…
The book Der Oracle DBA (Hanser, 2011), which was written in German, is at last available!
I say “at last” because the authors worked on this project for not less than two years.
For my part, I wrote two chapters: Speicherplatzverwaltung and Optimierung. The first one, Speicherplatzverwaltung, is available here. Also available online are the table of content, the preface and the index.
October 14, 2011 (Modified October 15, 2011) When reading various Oracle Database books, I often perform mental exercises related to the book’s contents, asking “what could go wrong?” That is probably why some of my book reviews have extended to record setting lengths… who knew that Amazon imposed a limit on book review lengths… I [...]
When its time to start booking hotel rooms and planning your agenda for the UKOUG conference in Birmingham (UK) for the 4th to the 7th December. Yep, all the cool sessions from Oracle Open World, minus an awful lot of the pure marketing. So just to wet your appetite here are my highlights in advance [...]
I mentioned in a previous post that I went to the OTN Night on the Monday at OOW11. I also spoke about eating far too much when I was there, but forgot to mention were the circus performers…
Anyone who knows me knows I’m obsessed with flexibility. I love stretching and I love watching anything involving flexibility. San Francisco has a famous Circus School, so previous OTN parties have had assorted circus performers to entertain the punters. In addition to the usual clowns, this year there was a contortionist with a hula hoop. She did elements of the Cirque du Soleil act made famous by Elena Lev in Alegria. You can see the original act here.
Needless to say I watched the contortionist at the OTN Night while stuffing noodles into my face.
Do I need to say more………. Why not join us for OakTable day on Sunday 4th December? In a smaller setting, you can attend a dedicated set of sessions directly from a selection of OakTable members away from any potential session contention of the main conference. The sessions will be presented in just two streams, …
As part of a server move from one data centre to another I enjoyed working in the depths of Clusterware. This one has been a rather simple case though: the public IP addresses were the only part of the package to change: simple. One caveat though was the recreation of the OCR disk group I am using for the OCR and 3 copies of the voting file. I decided to reply on the backups I took before the server move.
Once the kit has been rewired in the new data centre, it was time to get active. The /etc/multipath.conf file had to be touched to add the new LUNs for my +OCR disk group. I have described the processes in a number of articles, for example here:
A few facts before we start:
I have already described how to restore the OCR and voting files in 126.96.36.199 in “Pro Oracle Database RAC 11g on Linux”, but since then the procedure has changed slightly I thought I’d add this here. The emphasis is on “slightly”.In this blog post I’ll describe what you need to do if you lose the disk group containing OCR and voting disks on a Linux system using ASMLib. Before the server move I recorded the location of the OCR/voting disk disk group:
SQL> select d.name, d.path, dg.name as dg_name 2 from v$asm_disk d, v$asm_diskgroup dg 3 where d.group_number = dg.group_number 4 and dg.name = 'OCR' 5 / NAME PATH DG_NAME ---------- -------------------- ---------- OCR0001 ORCL:OCR0001 OCR OCR0002 ORCL:OCR0002 OCR OCR0003 ORCL:OCR0003 OCR SQL>
After the server has come back on the network, I first ensured everything was stopped:
[root@node1 cluster01]# crsctl stop crs -f CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'node1' CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'node1' CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'node1' CRS-2673: Attempting to stop 'ora.crf' on 'node1' CRS-2673: Attempting to stop 'ora.diskmon' on 'node1' CRS-2673: Attempting to stop 'ora.mdnsd' on 'node1' CRS-2677: Stop of 'ora.cssdmonitor' on 'node1' succeeded CRS-2677: Stop of 'ora.mdnsd' on 'node1' succeeded CRS-2677: Stop of 'ora.crf' on 'node1' succeeded CRS-2673: Attempting to stop 'ora.gipcd' on 'node1' CRS-2677: Stop of 'ora.drivers.acfs' on 'node1' succeeded CRS-2677: Stop of 'ora.gipcd' on 'node1' succeeded CRS-2673: Attempting to stop 'ora.gpnpd' on 'node1' CRS-2677: Stop of 'ora.diskmon' on 'node1' succeeded CRS-2677: Stop of 'ora.gpnpd' on 'node1' succeeded CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'node1' has completed CRS-4133: Oracle High Availability Services has been stopped.
The next step is to start the cluster in exclusive mode. In 188.8.131.52 it was enough to just use the crsctl start crs -excl, from 184.108.40.206 onwards you also have to add the -nocrs flag. If you don’t, crsd will try to start, but can’t find a voting file and everything spins/hangs until the Clusterware runs out of retries and the command fails. Here’s the example output with the correct command syntax:
[root@node1 cluster01]# crsctl start crs -excl -nocrs CRS-4123: Oracle High Availability Services has been started. CRS-2672: Attempting to start 'ora.mdnsd' on 'node1' CRS-2676: Start of 'ora.mdnsd' on 'node1' succeeded CRS-2672: Attempting to start 'ora.gpnpd' on 'node1' CRS-2676: Start of 'ora.gpnpd' on 'node1' succeeded CRS-2672: Attempting to start 'ora.cssdmonitor' on 'node1' CRS-2672: Attempting to start 'ora.gipcd' on 'node1' CRS-2676: Start of 'ora.cssdmonitor' on 'node1' succeeded CRS-2676: Start of 'ora.gipcd' on 'node1' succeeded CRS-2672: Attempting to start 'ora.cssd' on 'node1' CRS-2672: Attempting to start 'ora.diskmon' on 'node1' CRS-2676: Start of 'ora.diskmon' on 'node1' succeeded CRS-2676: Start of 'ora.cssd' on 'node1' succeeded CRS-2672: Attempting to start 'ora.drivers.acfs' on 'node1' CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'node1' CRS-2672: Attempting to start 'ora.ctssd' on 'node1' CRS-2676: Start of 'ora.drivers.acfs' on 'node1' succeeded CRS-2676: Start of 'ora.ctssd' on 'node1' succeeded CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'node1' succeeded CRS-2672: Attempting to start 'ora.asm' on 'node1' CRS-2676: Start of 'ora.asm' on 'node1' succeeded
As I said, I have created the disk group with exactly the same name as the one lost. This is very important, or the restore won’t work. The $GRID_HOME/log/`hostname`/client directory contains logs in case you have to troubleshoot. Inside the $GRID_HOME/cdata/
[root@node1 cluster01]# ocrconfig -restore backup00.ocr
This worked, as shown in the alert
[/u01/crs/product/220.127.116.11/bin/oraagent.bin(32015)]CRS-5019:All OCR locations are on ASM disk groups [OCR], and none of these disk groups are mounted. Details are at "(:CLSN00100:)" in "/u01/crs/product/18.104.22.168/log/node1/agent/ohasd/oraagent_oracle/oraagent_oracle.log". 2011-10-06 11:03:38.877 [client(1444)]CRS-1002:The OCR was restored from file backup00.ocr.
All right, that sorts the OCR out. Now it’s time to restore the voting disks:
[root@node1 cluster01]# crsctl query css votedisk Located 0 voting disk(s). [root@node1 cluster01]# crsctl replace votedisk +OCR Successful addition of voting disk 361e36921dd64f89bfd63cdbade79651. Successful addition of voting disk 58f769be54e74fbcbfc655afe290268d. Successful addition of voting disk af6c1890bb594f72bf39ef626b8fcc8f. Successfully replaced voting disk group with +OCR. CRS-4266: Voting file(s) successfully replaced [root@node1 cluster01]# crsctl query css votedisk ## STATE File Universal Id File Name Disk group -- ----- ----------------- --------- --------- 1. ONLINE 361e36921dd64f89bfd63cdbade79651 (ORCL:OCR0001) [OCR] 2. ONLINE 58f769be54e74fbcbfc655afe290268d (ORCL:OCR0002) [OCR] 3. ONLINE af6c1890bb594f72bf39ef626b8fcc8f (ORCL:OCR0003) [OCR] Located 3 voting disk(s).
This, too, is also recorded, for example in the CSSD log file:
2011-10-06 11:04:59.169 [cssd(32100)]CRS-1605:CSSD voting file is online: ORCL:OCR0001; details in /u01/crs/product/22.214.171.124/log/node1/cssd/ocssd.log. 2011-10-06 11:04:59.169 [cssd(32100)]CRS-1605:CSSD voting file is online: ORCL:OCR0002; details in /u01/crs/product/126.96.36.199/log/node1/cssd/ocssd.log. 2011-10-06 11:04:59.169 [cssd(32100)]CRS-1605:CSSD voting file is online: ORCL:OCR0003; details in /u01/crs/product/188.8.131.52/log/node1/cssd/ocssd.log. 2011-10-06 11:04:59.170 [cssd(32100)]CRS-1626:A Configuration change request completed successfully 2011-10-06 11:04:59.179 [cssd(32100)]CRS-1601:CSSD Reconfiguration complete. Active nodes are node1 .
Now all that remains is to get the cluster back into “normal” mode-stop it and start it, as shown here:
[root@node1 cluster01]# crsctl stop crs -f CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'node1' CRS-2673: Attempting to stop 'ora.mdnsd' on 'node1' CRS-2673: Attempting to stop 'ora.ctssd' on 'node1' CRS-2673: Attempting to stop 'ora.asm' on 'node1' CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'node1' CRS-2677: Stop of 'ora.asm' on 'node1' succeeded CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'node1' CRS-2677: Stop of 'ora.drivers.acfs' on 'node1' succeeded CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'node1' succeeded CRS-2677: Stop of 'ora.mdnsd' on 'node1' succeeded CRS-2677: Stop of 'ora.ctssd' on 'node1' succeeded CRS-2673: Attempting to stop 'ora.cssd' on 'node1' CRS-2677: Stop of 'ora.cssd' on 'node1' succeeded CRS-2673: Attempting to stop 'ora.gipcd' on 'node1' CRS-2673: Attempting to stop 'ora.diskmon' on 'node1' CRS-2677: Stop of 'ora.gipcd' on 'node1' succeeded CRS-2673: Attempting to stop 'ora.gpnpd' on 'node1' CRS-2677: Stop of 'ora.diskmon' on 'node1' succeeded CRS-2677: Stop of 'ora.gpnpd' on 'node1' succeeded CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'node1' has completed CRS-4133: Oracle High Availability Services has been stopped. [root@node1 cluster01]# crsctl start cluster CRS-4639: Could not contact Oracle High Availability Services CRS-4000: Command Start failed, or completed with errors. [root@node1 cluster01]# crsctl start crs CRS-4123: Oracle High Availability Services has been started.
Now all you need to do is wait, and check for the cluster status:
[root@node2 ~]# crsctl check cluster -all ************************************************************** node1: CRS-4537: Cluster Ready Services is online CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online ************************************************************** node2: CRS-4537: Cluster Ready Services is online CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online **************************************************************
All is well that ends well, the procedure is much the same it always was. Just remember the “-nocrs” flag.
How to restore ASM based OCR after complete loss of the CRS diskgroup on Linux/Unix systems [ID 1062983.1]
This year's OOW was definitely a game of two halves for me ... before and after my presentation ... so here is the first part.
I remember bemoaning the fact that the availability of Twitter would kill conference blogging and whining about those who couldn't be bothered to produce blog posts at conference so, sure enough, the disease infected me too and I ended up tweeting like a hyperactive child (@orcldoug) and producing one pathetic attempt at a blog post. I did try to start blog posts, but it's difficult and pretty rude during presentations and time outside of presentations is severely limited because of all the time spent catching up with people and ... erm ... polishing my slides. I suppose I could blog mid-conversation but I don't see that working!
One advantage of blogging now that I'm back home is that it's given me longer to reflect on the usual exciting but hectic week-and-a-bit and I can do it when I wake up at 3:30 because of this stupid jetlag that doesn't seem to want to go away completely. It's a bit of a long post, though!
Friday morning was spent finding our way to the new location for the second day of the ACE Directors Briefing. (Lesson Learned - Jonathan Lewis is not expert at everything!) There were always going to be less sessions of interest but Wim Coekaerts presentation was probably the highlight of the two days for me. He's an interesting guy to listen to and is really down to earth and when he mentioned the likelihood that DTrace in Oracle Enterprise Linux would be announced during the next week, I sensed the interest peak in a small but handsome and intelligent minority of the attendees. As the briefings were Twitter-tastic, I knew it would be moments before something appeared but James Morle was careful about what he wrote! It's fair to say that it's very early days for DTrace but from a personal perspective I was presenting on DTrace and excited by the possibilities 4 or 5 years ago and it's been disappointing that I haven't been able to use it as much as I'd like in the Linux-dominated commerical world. If the other development areas in Oracle are as committed, one day we should be able to get even more information about what the RDBMS code is doing. Wim talks about it more in his blog post and make sure you read Adam Leventhal's post for a different perspective. I also noticed in my Inbox this week that DTrace discussion is moving.
The afternoon was largely dedicated to Randolf Geist and I catching up with Maria Colgan over coffee. Maria is Product Manager in the Optimiser Development Group and Randolf and I wanted to take the opportunity to discuss how 11g Incremental Statistics were working out for us (summary - ok, but with a few creases) and I had a few questions about SQL Plan Management (SPM) for my upcoming presentation. Maria was interested in my experiences of SPM too as the last time I'd seen her at the Hotsos Symposium I was embarking on a project to use SPM on a complex Data Warehouse and I've noticed the Oracle development people snatch any opportunity to get some customer feedback. The idea was that I would show her my slides too because she wasn't going to be able to make my presentation but there was, erm, a slight problem with that plan so I agreed to drop by the demogrounds on Monday and do it then Maria's good fun and having Randolf there capped it off for me as he's a very smart guy so I got to pick two brains at once!
By the time we were done with that, we just about had time to head back to the briefing and a couple of quick beers by the pool at the Oracle Fitness Centre before we were whisked off to San Francisco on a couple of buses to check in at the hotel. The hotel computers were playing up so the check-in queue was absurd and we simply had to adjourn to the hotel bar while it cleared
I woke up early in a minor panic about my rather sparse presentation slides, but didn't let that get in the way of picking up my ACE D goodies from reception including snacks for the boys and a T-shirt that I might actually wear in polite company from time to time! Well done, OTN people
Most of the day was spent working on slides but later on I met up with a guy I know from Scotland and a colleague of his from London who were attending for the first time. As well as just enjoying their company, I love hanging out with first-time attendees because it brings back some of the sense of awe and excitement of my first visit. Don't worry, it still does it for me, but there's nothing like the first time!
In fact, I enjoyed it so much I almost made myself late for what has become the event of the week - Graham Wood's party! It's changed a bit though. It now takes two mini-bus loads and, added to those who made their own way there, Graham and Joan deserve medals for laying this on! There was one welcome innovation - Tacos in the front yard - and one less so - the Doug Burns Memorial Ashtray (Graham's words, not mine) was now being shared amongst several smokers. This has to stop!
I was determined to attend Tim Quinlan's SQL Plan Management presentation early on Sunday morning to see how he was presenting the same subject. I'm glad I did because it was enjoyable, good to hear someone elses Real World(TM) experiences and helped me to decide how to put my own presentation together. His presentation was full of syntax and how to do stuff so I decided to go in a different direction and aim for a very high level user experience story highlighting a couple of main points. But that means his presentation was very complimentary to mine for anyone looking to use SPM. I can't find his slides online but I'll drop him a note to see if they're available.
From then on it was just more slide work (yes, I know, it's getting dull - imagine what it's like for me) before meeting up with Lisa Dobson and others with the intention of going to the opening Keynote. I emphasise to all first timers that they should attend at least one keynote but that it might be one of the last they attend The production and excitement can't be faulted but, once you're about 45 minutes in, most people's interest starts to waver a little and then you realise that you can watch them online. Knowing that most of the people Lisa knows wouldn't attend, I offered to go along with her but, erm, Chevys being Chevys, things didn't go to plan and a group of us found ourselves walking to the annual ACE Dinner. This event has now grown to around 200 people, so it was some feat to manage to organise an event like that and an opporunity for me to finally bump into friends like Jacco. I need new glasses badly and have trouble seeing people at distance, but I just need to listen out for the Dutch table! LOL
I believe this may have been the first night that I finished in the bar at the W hotel with the Pythian crowd. I believe so, anyway
The main presentation I planned to attend on Monday was Juan Loaiza talking about the "Future of Oracle Exadata: Developments for OLTP, Warehousing, and Consolidation". The two main pieces I picked up here was Juan's comment that by being able to control the whole stack, it's easier for Oracle to implement further optimisations over time and the section on Exadata Smart Flash Logging. Another benefit of delaying my blog post is that Greg Rahn has conveniently blogged about this while I'm typing I suppose I was also pleased to hear yet another Oracle person talking about the effort that has had to go into maturing the platform over the past year or so. Indeed.
Other than that, the rest of my day was spent worrying about my presentation, being rained on and catching the occasional drink with friends to break the tedium of looking at my laptop screen.
Feeling that my slides were almost finished on the morning of presentation day I realised that it would all be over soon and there was probably little I could do to improve it at this stage so I decided to chill out (a little bit) and check out a few more presentations.
First was "CERN Achieves Oracle Database Scalability and Performance by Building on NetApp" with my friend Eric Grancher from CERN and the Netapp guy. It was a very polished theatre style presentation and not the sort of thing Eric normally goes in for so I thought he did extremely well for a geek People should pick up on this though - CERN is almost definitely demanding better performance over higher volumes of data across more sites than you could dream of and all on the back of Direct NFS which makes things much easier for them and delivers the performance they're looking for. If it works for them, then it should work for you.
My next presentation was "Real-World Performance: How Oracle Does It" which was primarily a presentation by Greg Rahn about Real Time SQL Monitoring which is one of my great loves these days. Greg's a very polished presenter so even though I didn't exactly learn much about SQL Mon, I think I probably learnt a few things about how I can present it better next time and thank goodness for the zoom facility to help old guys like Marco Gralike and I
After a brief visit to Mogens office in Chevys, I had to decide whether to attend "AWR Performance Data Mining" with Yuri Velinaknov or not. I'm a big fan of AWR and had recently met Yuri because he works for Pythian in Australia (i.e. We were camped out in the W bar together) but it was immediately before my presentation and I hardly ever attend any presentations immediately before or after my own because I'm too stressed out. In the end I decided I would pop in for half of it and check out the slides more later but I stupidly sat at the front so I felt embarassed when I left at half-time. Sorry, Yuri - I should have thought it through better! Yuri talked about how you can mine AWR for more detailed or more focussed information than just sticking with the standard AWR report and there's sure to be stuff I can pick up from his slides later.
The moment had arrived that I'd been dreading on the one hand (I was still really unhappy with my content) but had been excited about on the other (once done I could kick back and enjoy the rest of the conference properly!).
In the end I think it went pretty well. Although the content definitely wasn't what I hoped it would be (Because hope doesn't exactly produce slides) I think I was just in a good presentation mode that day, it felt like fun and I think people picked up a few high level points. The feedback I got was certainly very good from some people I have tons of respect for and I don't think they were just being polite. Without glasses on it's very difficult for me to recognise anyone beyond the first few rows these days, but I detected Graham Wood lurking at the back of the room which is always a bit unnerving! He dropped me a mail later to question who might have said 'Use narrow scope solutions to fix narrow scope problems' which I'm sure I first heard personally during a Jonathan Lewis presentation but it would be no surprise if I've remembered that incorrectly! It's still an important message, regardless, and Graham gave me another useful tip for when I next give the presentation at UKOUG in December. I won't post the slides yet, even though they're available on the Openworld website (although what they did to the fonts is beyond me) because I'm hoping they'll have improved by then
The relief was wrapped up nicely by catching up with more friends including a young man setting out on an adventure and just sticking my feet up as I became progressively more tired and happy
Disclosure - I attended the ACE Directors briefing and Openworld 2011 courtesy of
the Oracle ACE Director program, which covered my travel and accommodation expenses, gave me an actually-much-cooler-than-I-was-expecting T-shirt and took me out for a very nice dinner. Cuddly Toy incidentals and sizeable bar bills were on Pythian, Miracle or me in most cases.
I’ve just put iOS 5 on my iPad, who wants to touch me?
It took about 30 minutes in total, but I’ve heard some on Twitter saying it took them 3 hours. The update does a full backup and restore, so I guess the more stuff you have on your iPad or iPhone, the longer it takes.
What has changed? Still hasn’t turned it into a white iPad 2…
For the casual user like me it seems pretty much the same. I only use the browser and play the odd little game, so I guess I’m not the person to ask about the life-changing nature of iOS 5.
I’ve seen some posts on the blogosphere where people attempt to explain (or should I say guess) how Exadata Smart Flash Logging works and most of them are wrong. Hopefully this post will help clear up some the misconceptions out there.
The following is an excerpt from the paper entitled “Exadata Smart Flash Cache Features and the Oracle Exadata Database Machine” that goes into technical detail on the Exadata Smart Flash Logging feature.
Smart Flash Logging works as follows. When receiving a redo log write request, Exadata will do
parallel writes to the on-disk redo logs as well as a small amount of space reserved in the flash
hardware. When either of these writes has successfully completed the database will be
immediately notified of completion. If the disk drives hosting the logs experience slow response
times, then the Exadata Smart Flash Cache will provide a faster log write response time.
Conversely, if the Exadata Smart Flash Cache is temporarily experiencing slow response times
(e.g., due to wear leveling algorithms), then the disk drive will provide a faster response time.
Given the speed advantage the Exadata flash hardware has over disk drives, log writes should be
written to Exadata Smart Flash Cache, almost all of the time, resulting in very fast redo write
performance. This algorithm will significantly smooth out redo write response times and provide
overall better database performance.
The Exadata Smart Flash Cache is not used as a permanent store for redo data – it is just a
temporary store for the purpose of providing fast redo write response time. The Exadata Smart
Flash Cache is a cache for storing redo data until this data is safely written to disk. The Exadata
Storage Server comes with a substantial amount of flash storage. A small amount is allocated for
database logging and the remainder will be used for caching user data. The best practices and
configuration of redo log sizing, duplexing and mirroring do not change when using Exadata
Smart Flash Logging. Smart Flash Logging handles all crash and recovery scenarios without
requiring any additional or special administrator intervention beyond what would normally be
needed for recovery of the database from redo logs. From an end user perspective, the system
behaves in a completely transparent manner and the user need not be aware that flash is being
used as a temporary store for redo. The only behavioral difference will be consistently low
latencies for redo log writes.
By default, 512 MB of the Exadata flash is allocated to Smart Flash Logging. Relative to the 384
GB of flash in each Exadata cell this is an insignificant investment for a huge performance
benefit. This default allocation will be sufficient for most situations. Statistics are maintained to
indicate the number and frequency of redo writes serviced by flash and those that could not be
serviced, due to, for example, insufficient flash space being allocated for Smart Flash Logging.
For a database with a high redo generation rate, or when many databases are consolidated on to
one Exadata Database Machine, the size of the flash allocated to Smart Flash Logging may need
to be enlarged. In addition, for consolidated deployments, the Exadata I/O Resource Manager
(IORM) has been enhanced to enable or disable Smart Flash Logging for the different databases
running on the Database Machine, reserving flash for the most performance critical databases.