Friday Nov 29, 2013

Starting up 252 PDBs in Oracle Multitenant

What happens when you start up 252 PDBs (Pluggable Databases) with the Oracle Multitenant Option for the first time?

Interesting question, isn't it? The expectation would be that this will happen within seconds as the SGA and therefore the shared memory segments are already allocated from within the CDB$ROOT (Container Database). But ...

The following happens:
It takes minutes ... hours .... In my tiny lab environment with just as little as 20 PDBs due to space constraints it takes over 30 minutes to startup 21 PDBs. Takashi Ikeda from Fujitsu Hokoriku Systems who did a great demo with the new Fujitsu M10 servers at OOW this year told me that it took over two hours to start up 252 PDBs for the first time.
Why is that?

Let's have a closer look into the alert.log during startup. After issueing the command:


I'd expect all PDBs to get started. With an EXCEPT PDB1, PDB2, PDB3 clause I could exclude some PDBs from this action. Now a look into the alert.log shows a very promising message:

I'm just wondering about the opening sequence of PDBs. I'd expect 1 ... 2 ... 3 ... 4 ... ... ... 21. But the "order" is 3 ... 10 ... 16 ... 15 ... 20 ... 21 etc. telling me that the Resource Manager is not active (which is a must if you take Multitenant serious).
OK, for that strange order there's an explanation:
The open action gets distrubuted to slaves so PDBs may opened in a random order.
Fuuny thing apart from that: I can access the PDB but the system seems to be really under heavy pressure. CPUs are all at 100%. What the heck is going on here in the background?

Well, XDB needs to be installed (at least that is what the message says). Strange, isn't it, as the PDB$SEED has XDB in it and all my PDBs got provisioned from it. The awkward thing here is that the XDB messages appear over 20 minutes AFTER the PDBs signaled the Opening message into the alert.log (see the time stamps above).

Now after exchanging a few emails with some very helpful people in development there's an explanation for the XDB messages as well. Actually it doesn't get really installed but the SGA needs to be initialized for XDB. And I'm guessing that this action takes a lot of resources plus may cause contention when many PDBs get opened at the same time. And there's optimization work going on right now meaning that a problem with port initialization within the PDB will get fixed in a future patch set. So this issue with the very long startups of PDBs because of XDB should disappear in most likely :-)

Finally it took another while to get the PDBs really into OPEN mode. Even though they were showing OPEN before already in V$PDBS. But as the CPUs all went to 100% as XDB got installed/initiallized at more or less the same time in all PDBs you really can't do anything.

Finally ...

... all PDBs got opened and the command ALTER PLUGGABLE DATABASE ALL OPEN returned completed.

The good news:
It takes just this long during the initial startup of a newly provisioned PDB. And you may see this issue only when you try to open many PDBs at the same time. But have a close look into your alert.log if you'll spot the message after creating a fresh PDB.

And btw, just for the records: I was using Oracle Database with Oct 2013 PSU in it.


Tuesday Nov 26, 2013

DOAG Conference - Recap

This year's 2013 conference was the best DOAG Conference I have attended so far (and it was my 11th conference).  Actually due to the fact that I've had just one presentation (Working with Oracle Multitenant in the Real World - thanks to everybody coming by in the huge TOKIO room - that was really fun!) and a DOAG TV interview I've had enough time to see some other presentations. I did actually enjoy the Oak Table Stream in room SHANGHAI a lot - so many good stuff, I have really learned a lot.

So thanks to the organizers from the user group (and especially to Christian Trieb and his colleagues from DOAG for pushing this extra stream).

And in case you'd like to download the slide deck:
Working with Oracle Multitenant in the Real World


Wednesday Nov 13, 2013

Why people don't patch and upgrade?!?

Please see the JAPANESE version of this blog post here!!

Discussing the topic "Why Upgrade" or "Why not Upgrade" is not always fun. Actually the arguments repeat from customer to customer. Typically we hear things such as:

  • A PSU or Patch Set introduces new bugs
  • A new PSU or Patch Set introduces new features which lead to risk and require application verification 
  • Patching means risk
  • Patching changes the execution plans
  • Patching requires too much testing
  • Patching is too much work for our DBAs
  • Patching costs a lot of money and doesn't pay out

And to be very honest sometimes it's hard for me to stay calm in such discussions. Let's discuss some of these points a bit more in detail.

  • A PSU or Patch Set introduces new bugs
    Well, yes, that is really true as there's no software containing more than some lines of code being bug free. This applies to Oracle's code as well as too any application or operating system code. But first of all, does that mean you never patch your OS because the patch may introduce new flaws? And second, what is the point of saying "it introduces new bugs"? Does that mean you will never get rid of the mean issues we know about and we fixed already? Scroll down from MOS Note:161818.1 to the patch release you are on, no matter if it's or and check for the Known Issues And Alerts.
    Will you take responsibility to know about all these issues and refuse to upgrade to I won't.

  • A new PSU or Patch Set introduces new features
    Ok, we can discuss that. Offering new functionality within a database patch set is a dubious thing. It has advantages such as in where we backported Database Redaction to. But this is something you will only use once you have an Advanced Security license. I interpret that statement I've heard quite often from customers in a different way: People don't want to get surprises such as new behaviour. This certainly gives everybody a hard time. And we've had many examples in the past (SESSION_CACHED_CURSROS in,  _DATAFILE_WRITE_ERRORS_CRASH_INSTANCE in and others) where those things weren't documented, not even in the README. Thanks to many friends out there I learned about those as well. So new behaviour is the topic people consider as risky - not really new features. And just to point this out: A PSU never brings in new features or new behaviour by definition!

  • Patching means risk
    Does it really mean risk? Yes, there were issues in the past (and sometimes in the present as well) where a patch didn't get installed correctly. But personally I consider it way more risky to not patch. Keep that in mind: The day Oracle publishes an PSU (or CPU) containing security fixes all the great security experts out there go public with their findings as well. So from that day on even my grandma can find out about those issues and try to attack somebody. Now a lot of people say: "My database does not face the internet." And I will answer: "The enemy is sitting already behind your firewalls. And knows potentially about these things."
    My statement: Not patching introduces way more risk to your environment than patching. Seriously!

  • Patching changes the execution plans
    Do they really? I agree - there's a very small risk for this happening with Patch Sets. But not with PSUs or CPUs as they contain no optimizer fixes changing behaviour (but they may contain fixes curing wrong-query-result-bugs). But what's the point of a changing execution plan? In Oracle Database 11g it is so simple to be prepared. SQL Plan Management is a free EE feature - so once that occurs you'll put the plan into the Plan Baseline. Basta! Yes, you wouldn't like to get such surprises? Then please use the SQL Performance Analyzer (SPA) from Real Application Testing and you'll detect that easily upfront in minutes. And not to forget this, a plan change can also be very positive!
    Yes, there's a little risk with a database patchset - and we have many possibilites to detect this before patching.

  • Patching requires too much testing
    Well, does it really? I have seen in the past 12 years how people test. There are very different efforts and approaches on this. I have seen people spending a heck of money on external licenses or on project team staffing. And I have seen people sailing blindly without any tests just going the John-Wayne-approach.
    Proper tools will allow you to test easily without too much efforts. See the paragraph above. We have used Real Application Testing in so many customer projects reducing the amount of work spend on testing by over 50%.
    But apart from that at some point you will have to stop testing. If you don't stop you'll get lost and you'll burn money. There's no 100% guaranty. You will have to deal with a little risk as reaching the final 5% of certainty will cost you the same as it did cost to reach 95%. And doing this will lead to abnormal long product cycles that you'll run behind forever. And this will cost even more money.

  • Patching is too much work for our DBAs
    Patching is a lot of work. I agree. And it's no fun work. It's boring, annoying. You don't learn much from that. That's why you should try to automate this task. Use the Database's Lifecycle Management Pack. And don't cry about the fact that it costs money. Yes it does. But it will ease the process and you'll save a lot of costs as you don't waste your valuable time with patching. Or use Oracle Database 12c Oracle Multitenant and patch either by unplug/plug or patch an entire container database with all PDBs with one patch at one time in one task.
    We have customer reference cases proofing it saved them 75% of time, effort and cost since they've used Lifecycle Management Pack. So why don't you use it?

  • Patching costs a lot of money and doesn't pay out
    Well, see my statements in the paragraph above. And it pays out as flying with a database with 100 known critical flaws in it which are already fixed by Oracle (such as in the Oct 2013 PSU for Oracle Database 12c) will cost ways more in case of failure or even data loss. Bet with me?

Let me finally ask you some questions.

What cell phone are you using and which OS does it run? Do you have an iPhone 5 and did you upgrade already to iOS 7.0.3? I've just encountered on my iPhone 5 that the alarm (which I rely on when traveling) has gotten now a dependency on the physical switch "sound on/off". If it is switched to "off" physically the alarm rings "silently". What a wonderful example of a behaviour change coming in with a patch set. Will this push you to stay with iOS5 or iOS6? No, because those have security flaws which won't be fixed anymore.And I tell you how I have found out that: I have tested the alarm after updating to iOS 7.0.3 as I heavily rely on that feature. I don't test everything as my time and my resource won't allow that - but I usually check the most important things.

What browser are you surfing with? Do you use Mozilla 3.6? Well, congratulations to all the hackers. It will be easy for them to attack you and harm your system. I'd guess you have the auto updater on.  Same for Google Chrome, Safari, IE. Right?


Wednesday Nov 06, 2013

JPOUG Tech Talk Next Week!


Mike and I are really looking forward to our trip to Japan next week, not least because we will have the opportunity to visit the Japan Oracle User Group for a Tech Talk. You can find all of the details about this event here:

JPOUG Tech Talk, Tuesday, 12-NOV-2013

The topic for our talk will be "Different ways to Upgrade, Migrate, and Consolidate with Oracle Database 12c."We will discuss changes and enhancements to database upgrade, how to move into a multitenant database environment, and new features that make database migration easier and faster than ever.

Thank you to our friends at the JPOUG for making this event possible! 

How to SET TIMING ON for parallel upgrades to 12c?

Have you asked yourself how to get timings in an Oracle Database 12c upgrade for all statements?

When you run the parallel upgrade via, the parallel upgrade Perl driving script in Oracle Database 12c, you may also want to get timings written in your logfile during execution. As does not offer an option yet the best way to achieve this is to edit the catupses.sql script in $ORACLE/rdbms/admin as this script will get called all time over and over again throughout all steps of theupgrade run.

Just add these lines marked in RED to catupses.sql and start your upgrade:

Rem =============================================
Rem Call Common session settings
Rem =============================================

Rem =============================================
Rem  Set Timing On during the Upgrade
Rem =============================================

Rem =============================================
Rem Turn off PL/SQL event used by APPS
Rem =============================================
ALTER SESSION SET EVENTS='10933 trace name context off'


PS: This may become the default in a future patch set ;-)


Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle

Based in Germany. Interlink between customers/partners and the Upgrade Development. Running workshops between Arctic and Antartica. Assisting customers in their reference projects onsite and remotely. Connect via:

- -


« November 2013 »
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Viewlets and Videos
Workshop Map
x Oracle related Tech Blogs
This week on my Rega & Pono
Upgrade Reference Papers