To PDB or not to PDB

 

I’m about to start a Proof of Concept (POC) for a 12c upgrade of one of our databases. The production database in question is running on Oracle Linux inside a VMware virtual machine, so the starting point I’ve been given for the POC is a clone of the whole VM…

Probably the biggest decision I’ve got to make is “to PDB or not to PDB” *. I mentioned it on Twitter earlier and got some conflicting opinions. I guess the pros and cons of the PDB approach go something like this in my head.

Pros:

  • The multitenant architecture is the future of Oracle. Depending on which rumours you believe, it’s possible that 12.2 will no longer allow the pre-12c style instances. Putting it off is delaying the inevitable.
  • As long as you only use a single PDB, there is no extra cost.
  • The multitenant architecture has some neat features related to cloning, especially remote clones. That potentially makes provisioning new environments pretty quick.
  • Even with a single PDB per CDB, there are potential advantages regarding patching and upgrades. Caveats apply as always.
  • I’m going to upgrade to a pre-12c style instance first anyway, so I will have a natural fallback position ready to go if I need it.
  • It would be good to invest the time up front to convert stuff now, rather than wait a few years to clean up the mess of CRON jobs and connections using SIDs, rather than services. This choice would force our hand.
  • If some of the technologies we are using are not going to “play well” with the multitenant architecture, I would rather know now than later.

Cons:

  • Using a PDB is definitely going to break a number of things for us, especially CRON jobs that run scripts using OS authentication. See here.
  • Once the decision has been made to “switch the multitenant architecture on”, it would be really easy for someone to create an extra PDB and incur additional licensing costs. As far as I’m aware, there is nothing to restrict the number of PDBs to 1, to prevent an uninitiated DBA from copying a script from the net and creating more. If someone knows an undocumented parameter for this I would be interested in knowing it. Note, “_max_pdbs” isn’t the answer here! 🙂
  • I’m going to upgrade to a pre-12c style instance first, so why add on the extra effort of cloning that to a PDB?
  • Why make life hard for yourself? You can use 12.1 as a half-way house and make the final step later.

I don’t think there is really a right or wrong answer in this debate. I could probably put forward a convincing argument in favour of either option. I’m leaning on the side of the “to PDB” choice. If this proves to be a no-go, then I’ll start a POC of a pre-12c style instance… 🙂

Despite my leaning for the PDB choice, I am interested to know what others think, especially those that have done something a bit more extensive than running this stuff on their laptop. 🙂

Cheers

Tim…

* I forgot to mention previously, we will almost definitely be going with a single PDB per CDB (the free option) initially. So this is not a “consolidate using multitenant” issue from the outset.

Author: Tim...

DBA, Developer, Author, Trainer.

8 thoughts on “To PDB or not to PDB”

  1. re: Once the decision has been made to “switch the multitenant architecture on”, it would be really easy for someone to create an extra PDB and incur additional licensing costs

    Pfft…..if you noticed it, you’d simply move it to a new CDB. If you didnt notice it, then there’s probably 20 other license violations you havent noticed as well…and you shouldnt be in charge of those databases 🙂

  2. We’ve moved a few db’s to single PDB – mainly its been smooth, but we’ve have a few dramas when it comes to performance monitoring. Dictionary / fixed object stats seems to be dodgey when it comes to pdb’s / cdb’s…so some standard Oracle facilities are running like junk in pdb.

  3. Connor: I was hoping you would reply as I know you have done something real with 12c.

    RE: PDB move. Very true. Hadn’t thought of that! 🙂
    RE: Performance monitoring. Good to know in advance!

    Thanks for the comment dude!

    Cheers

    Tim…

  4. @Tim: Wouldn’t it work to use DDL trigger on root level to prevent adding more PDBs.

    @Connor: Could you please shet some light on your performance monitoring drama / junk oracle facilities? Would be good to know beforehand.

    Thanks, Markus

  6. In Oracle SE on the otherhand, it will politely inform you about the lack of multitenant feature since we are in a single tenant environment. Not possible to create a second PDB.

    I also noticed by implementing the statspack in an oracle SE environment (more of that on my blog), oracle has split the statistics depending on are you interested in cdb$root or a pdb.
    For example
    the documentation about v$sysstat displaying system statistics is one
    v$sysstat:
    The ID of the container to which the data pertains. Possible values include:
    0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.
    1: This value is used for rows containing data that pertain to only the root
    n: Where n is the applicable container ID for the rows containing data

    V$CON_SYSSTAT
    That displays system statistics, including OLAP kernel statistics for the container from which it is queried.

    When queried from a non-CDB, the statistics in that instance are returned, and the CON_ID value is set to 0.
    When queried from the root of a CDB, the statistics in every container are returned, and the CON_ID value indicates the container to which the statistics belong.
    When queried from a PDB, statistics in that PDB are returned, and the CON_ID value is set to the container ID for that PDB.

    So I guess it is just time to relearn; basic assumptions are no longer available when jumping on this “train”
    – Ann

  7. Here’s an example of some of the kind of things we’ve seen…

    SQL> conn / as sysdba
    Connected.

    SQL> exec dbms_stats.gather_fixed_objects_stats;

    PL/SQL procedure successfully completed.

    SQL> SELECT num_rows, last_analyzed
    2 FROM DBA_TAB_STATISTICS
    3 WHERE OBJECT_TYPE=’FIXED TABLE’
    4 AND TABLE_NAME = ‘X$ASH’

    NUM_ROWS LAST_ANAL
    ———- ———
    52561 05-AUG-14

    which looks about right, ie,

    SQL> select count(*) from x$ash;

    COUNT(*)
    ———-
    52567

    I then look at the pluggable database:

    SQL> SELECT num_rows, last_analyzed
    2 FROM DBA_TAB_STATISTICS
    3 WHERE OBJECT_TYPE=’FIXED TABLE’
    4 AND TABLE_NAME = ‘X$ASH’

    NUM_ROWS LAST_ANAL
    ———- ———
    35 28-JUN-14

    So obviously thats wrong, so I collect fixed object stats in the pluggable…

    SQL> alter session set container = PDB12;

    Session altered.

    SQL> select count(*) from x$ash;

    COUNT(*)
    ———-
    22472

    SQL> exec dbms_stats.gather_fixed_objects_stats;

    PL/SQL procedure successfully completed.

    SQL> SELECT num_rows, last_analyzed
    2 FROM DBA_TAB_STATISTICS
    3 WHERE OBJECT_TYPE=’FIXED TABLE’
    4 AND TABLE_NAME = ‘X$ASH’

    NUM_ROWS LAST_ANAL
    ———- ———
    35 28-JUN-14

    Hmmm…..that doesnt look good

    In our case, that manifested itself as some AWR queries running very very slowly…

Comments are closed.