Oracle Scratchpad

August 9, 2013


Filed under: 12c,Infrastructure,Oracle — Jonathan Lewis @ 10:03 am BST Aug 9,2013

Just glancing through the 12c manuals (Server Reference 12.1 June 2013 – E17615-16) to check a particular database limit, I came across the following: “Services – maximum per instance – 115”. That’s a bit of a problem, given that you can have 254 pluggable (tenant) databases in a single container database, and each plugged database gets its own service – but I’m guessing that that bit of the manual is wrong, after all it didn’t say anything about pluggable databases at all. It’s hard to keep documentation up to date as things change.

Here’s a random thought, though, loosely linked to database limits. If you’re looking ahead to a time when you have lots of tenants in a container database, you might want to start by migrating your existing databases from smallfile tablespaces to bigfile tablespaces (which may make it a good idea to run with change tracking enabled) so that the final container database doesn’t have a totally unmanageable number of database files.

Update 13th Aug 2013

Read the comments for a limit on the total number of services a container database can run.



  1. “It’s hard to keep documentation up to date as things change.”

    That’s a sad state of affairs that plagues Oracle’s products. I’ve worked on plenty of products as complex or more complex than Oracle e.g z/OS and they manage very well to keep documentation up to date. It’s a shame that a lot of people seem to have your view and don’t push Oracle to produce clear, precise and up to date documentation.

    Comment by David — August 9, 2013 @ 10:13 am BST Aug 9,2013 | Reply

  2. Yup. Started to use and move to bigfiles since the upgrade to 11gr2 last year.
    Almost all tablespaces on them now, ready for the future.

    Comment by Nuno Pinto do Souto (@wizofoz2k) — August 9, 2013 @ 11:49 am BST Aug 9,2013 | Reply

  3. Just a couple of comments on your suggestion to move to bigfile tablespaces (which I broadly agree with).

    1) its much easier to do this when the tablespaces and segments in them are reasonably sized than should they already be sgnificant in size (100’s of gb to tbs in size). This is really ust the usual argument for thinking ahead about how large your objects might grow.
    2) You probably need to change your backup routine to include the SECTION_SIZE parameter for backups of the bigfile tablespaces. A simple backup database .. probably won’t cut it. So far as I know section_size can’t be applied at the database level.
    3) If you follow “best practice” and backup your standby database rather than the primary, there are a large number of bugs related to implementing change tracking on standby databases in most versions of 11.2. Commonly you can crash the standby with ora-600[krcccb_busy] – there’s also an interesting bug reported where RMAN will almost silently turn off BCT because it determines that the BCT file is invalid.
    4) if you are running 11.1 or earlier you run a risk of standby backups with BCT enabled not being valid.

    At this moment then if using BCT it’s probably prudent to backup your primary db, and for bigfile tablespaces remember to set section size if the files will grow very large.

    Comment by Niall Litchfield (@nlitchfield) — August 10, 2013 @ 9:52 am BST Aug 10,2013 | Reply

    • Niall,

      Thanks for the warnings and suggestions.

      As you point out in (1) – planning ahead is always VERY important, so it’s nice to have warnings of a few threats before you get started.

      At present I think the strategy for moving to plugged/tenant databases first requires an upgrade to 12c anyway, and there may be some features to help the migration to bigfiles even in the worst cases. (dbms_redefinition has been improved, for example, to reduce the time an object is unavailable as the final resynch takes place – which could be really important if you want to move a very large segment online.)

      Comment by Jonathan Lewis — August 10, 2013 @ 4:06 pm BST Aug 10,2013 | Reply

  4. I think maximum number of pluggable databases is 252 at least that amount I was able to create and for 253 I got the error. Even with PDB$SEED it is 253 which possibly should not be considered as tenant

    Comment by Andrey Goryunov — August 10, 2013 @ 2:30 pm BST Aug 10,2013 | Reply

    • Andrey,

      Thanks for the correction – I knew it was nearly 256 (one byte’s worth), but couldn’t remember exactly (which is why I checked the Database Limits, and ended up writing this note).

      Comment by Jonathan Lewis — August 10, 2013 @ 3:58 pm BST Aug 10,2013 | Reply

  5. I just checked number of services available under listener and there are 254 of them totally and 252 of them for all related pluggable databases
    [oracle@o644 ~]$ lsnrctl stat | grep “^Service \”pdb” |wc -l
    so looks like documentation was not updated properly

    Comment by Andrey Goryunov — August 10, 2013 @ 2:41 pm BST Aug 10,2013 | Reply

    • Andrey,
      Thanks for the results. If you can spare the time, it would be interesting to see how many more services you can launch: I regularly see systems with “a few” (i.e. 4 or 5) services nowadays, so it would be interesting to see if you could get a few services active per PDB at the same time. (Or maybe even check if you can get 115 for a PDB, since that was the only limit for a DB).

      Comment by Jonathan Lewis — August 10, 2013 @ 4:00 pm BST Aug 10,2013 | Reply

  6. I created and started 254 additional services through dbms_service, got all of them listed in lsnrctl and v$services, connected to the 1st and last one
    [oracle@o644 trace]$ lsnrctl services | grep “Service \”pdb200\_” |wc -l

    cdb> select count(*) from dba_services where pdb = ‘PDB200’;


    Comment by Andrey Goryunov — August 11, 2013 @ 4:01 am BST Aug 11,2013 | Reply

  7. tested and could not get more than 1023 services created in CDB

    Comment by Andrey Goryunov — August 13, 2013 @ 6:52 am BST Aug 13,2013 | Reply

  8. Andrey,

    Thank you for taking the time to do the experiment and post the results.

    Comment by Jonathan Lewis — August 13, 2013 @ 7:16 am BST Aug 13,2013 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by