Tuesday Dec 03, 2013

Starting up 252 PDBs automatically?

In my recent posting I have explained the startup of many PDBs at the same time.

But once you startup the container database CDB$ROOT the PDBs will stay in MOUNT status. So how do you start them during CDB$ROOT startup (or immediately afterwards) in an automatic fashion?

A startup trigger will do this job.

CREATE OR REPLACE TRIGGER startup_all_pdbs
AFTER STARTUP ON DATABASE

BEGIN

EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';

END;

/

And of course you can use the EXCEPT command option to exclude one or more PDBs from the automatic startup.

CREATE OR REPLACE TRIGGER startup_all_pdbs_except_a_few
AFTER STARTUP ON DATABASE

BEGIN
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN EXCEPT PDB100, PDB101';
END;
/

How does this work in an Oracle Real Application Clusters environment?
In an RAC environment you won't need the startup trigger as clusterware takes over this role of ensuring the automatic startup of a PDB on designated nodes within the CDB$ROOT's instances.

srvctl add service -db rac -service pdbrac_srv -pdb pdbrac -preferred "rac1,rac2"

A snipet from the crsctl status output will look like this:

   crsctl status resource -t
    :
   ora.rac.db
         1    ONLINE  ONLINE   rac-server01       Open,STABLE
         2    ONLINE  ONLINE   rac-server02       Open,STABLE
   ora.rac.pdbrac_srv.svc
         1    ONLINE  ONLINE   rac-server01       STABLE
         2    ONLINE  ONLINE   rac-server02       STABLE
    :

-Mike

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:

ALTER PLUGGABLE DATABASE ALL OPEN;

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 12.1.0.2 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 12.1.0.1 with Oct 2013 PSU in it.

-Mike

About

Mike Dietrich - Oracle Mike Dietrich
Senior Principal Technologist - Database Upgrade Development Group - Oracle Corporation

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite with reference customers. Acting as interlink between customers and the Upgrade Development.

Contact me either via XING or LinkedIn

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
4
5
6
9
10
12
13
15
16
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
Slides Download Center
OOW Slides Download
Visitors since 17-OCT-2011
White Paper and Docs
Oracle Blogs
Workshops
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers