Oracle … as usual

Oracle by Laurent Leturgez

Oracle extra cost options licensing on multitenant databases

Oracle database 12c has been announced few weeks ago with its main feature: multitenant database. This feature is build to consolidate many databases (aka. pluggable databases or PDB) into a unique container database (CDB).
Multitenancy is an extra cost option for the Enterprise Edition if you use more than one pdb in your container. But if you want to consolidate, you probably want to consolidate all the options you bought before for many servers.
In Oracle server, each usage of a specific feature you made is recorded in a dictionary table and can be accessed through the view: DBA_FEATURE_USAGE_STATISTICS.
In a Standard Edition database, features of the Enterprise Edition are tracked. If you want to use an extra cost option, you will probably raise an ORA-00439 (Feature not enabled):
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT              VERSION              STATUS
-------------------- -------------------- --------------------
NLSRTL               12.1.0.1.0           Production
Oracle Database 12c  12.1.0.1.0           64bit Production
PL/SQL               12.1.0.1.0           Production
TNS for Linux:       12.1.0.1.0           Production
We are working on a Standard Edition.
SQL> select NAME,DETECTED_USAGES,CURRENTLY_USED,LAST_USAGE_DATE
  2  from dba_feature_usage_statistics
  3  where name in ('Segment Shrink')
  4  /
NAME                                               DETECTED_USAGES CURRE LAST_USAGE_DATE
-------------------------------------------------- --------------- ----- -------------------
Segment Shrink                                                   0 FALSE

SQL> alter table t shrink space;
Table altered.
SQL> exec dbms_feature_usage_internal.sample_one_feature('Segment Shrink');

PL/SQL procedure successfully completed.

SQL> select NAME,DETECTED_USAGES,CURRENTLY_USED,LAST_USAGE_DATE
   2  from dba_feature_usage_statistics
   3  where name in ('Segment Shrink')
   4  /

NAME                                               DETECTED_USAGES CURRE LAST_USAGE_DATE
 -------------------------------------------------- --------------- ----- -------------------
 Segment Shrink                                                   1 TRUE  2013-10-09 20:46:22
SQL> BEGIN
  2   DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
  3    name          => 'public_privs_capture',
  4    description   => 'Captures privilege use by PUBLIC',
  5    type          => DBMS_PRIVILEGE_CAPTURE.G_ROLE,
  6    roles         => role_name_list('public')
  7  );
  8  end;
  9  /
BEGIN
*
ERROR at line 1:
ORA-00439: feature not enabled: Privilege Analysis
ORA-06512: at "SYS.DBMS_PRIVILEGE_CAPTURE", line 3
ORA-06512: at line 2
Notice that I used dbms_feature_usage_internal.sample_one_feature to force the update of feature usage data (They are normally updated every 7 days).
In an Enterprise Edition database, the behaviour is similar but only extra cost options usages are recorded, features included in the Edition are not (for example, Segment Shrink will stay at 0).
Now let’s see how it’s recorded in a multitenant database. In my case, I have a container database (ORACLE_SID=cdb) which hosts 10 pluggable databases. Those PDBs have been consolidated from many databases hosted on dedicated servers. As we bought database vault option for the database consolidated into PDB10 (for example), we would like to use Privilege analysis function (included in database vault option).
Let’s check how it works in the PDB10:
idle> connect sys/oracle@oel63:1521/pdb10 as sysdba
Connected.

[SYS@PDB10 | SID:CDB]> BEGIN
  2    DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
  3     name          => 'public_privs_capture',
  4     description   => 'Captures privilege use by PUBLIC',
  5     type          => DBMS_PRIVILEGE_CAPTURE.G_ROLE,
  6     roles         => role_name_list('public')
  7     );
  8  END;
  9  /

PL/SQL procedure successfully completed.

[SYS@PDB10 | SID:CDB]> EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('public_privs_capture');

PL/SQL procedure successfully completed.

[SYS@PDB10 | SID:CDB]> exec dbms_feature_usage_internal.sample_one_feature('Privilege Capture');
BEGIN dbms_feature_usage_internal.sample_one_feature('Privilege Capture'); END;

*
ERROR at line 1:
ORA-20009: Unknown Feature
ORA-06512: at "SYS.DBMS_FEATURE_USAGE_INTERNAL", line 614
ORA-06512: at line 1
Well it seems that we cannot call this package in a PDB. So we will do it the CDB$ROOT and check DBA_FEATURE_USAGE_STATISTICS (in the PDB) and CDB_FEATURE_USAGE_STATISTICS (in the CDB$ROOT).
[SYS@CDB$ROOT | SID:CDB]> select con_id,NAME,DETECTED_USAGES,CURRENTLY_USED,LAST_USAGE_DATE
  2  from cdb_feature_usage_statistics
  3  where name in ('Privilege Capture')
  4  order by 1;

    CON_ID NAME                                               DETECTED_USAGES CURRE LAST_USAGE_DATE
---------- -------------------------------------------------- --------------- ----- -------------------
         1 Privilege Capture                                                1 FALSE 2013-10-09 21:59:40
         2 Privilege Capture                                                0 FALSE
         3 Privilege Capture                                                0 FALSE
         4 Privilege Capture                                                0 FALSE
         5 Privilege Capture                                                0 FALSE
         6 Privilege Capture                                                0 FALSE
         7 Privilege Capture                                                0 FALSE
         8 Privilege Capture                                                0 FALSE
         9 Privilege Capture                                                0 FALSE
        10 Privilege Capture                                                0 FALSE
        11 Privilege Capture                                                0 FALSE
        12 Privilege Capture                                                0 FALSE

12 rows selected.

[SYS@CDB$ROOT | SID:CDB]> connect sys/oracle@oel63:1521/pdb10 as sysdba
Connected.
[SYS@PDB10 | SID:CDB]> select NAME,DETECTED_USAGES,CURRENTLY_USED,LAST_USAGE_DATE
  2  from dba_feature_usage_statistics
  3  where name in ('Privilege Capture')
  4  /

NAME                                               DETECTED_USAGES CURRE LAST_USAG
-------------------------------------------------- --------------- ----- ---------
Privilege Capture                                                0 FALSE
We have the proof that feature usage is recorded in the root container and licensing is logically done at the CDB$ROOT level.
If I bought database vault option in my 11g database for 2 CPUs, and now my multitenant is located on a strong server with 24 CPUs, I have to buy 22CPUs of this option, even if it run on a single. On the other side, you can use the option and all of its features in all your PDBs even you don’t need it 😉
To proove it, I will reproduce the same steps in another PDB (the PDB4 for example), and we will see that usage is recorded in the CDB.
idle> connect sys/oracle@oel63:1521/pdb4 as sysdba
Connected.
[SYS@PDB4 | SID:CDB]> BEGIN
  2    DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
  3     name          => 'public_privs_capture',
  4     description   => 'Captures privilege use by PUBLIC',
  5     type          => DBMS_PRIVILEGE_CAPTURE.G_ROLE,
  6     roles         => role_name_list('public')
  7     );
  8  END;
  9  /

PL/SQL procedure successfully completed.

[SYS@PDB4 | SID:CDB]> EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('public_privs_capture');

PL/SQL procedure successfully completed.

[SYS@PDB4 | SID:CDB]> connect sys/oracle as sysdba
Connected.
[SYS@CDB$ROOT | SID:CDB]> exec dbms_feature_usage_internal.sample_one_feature('Privilege Capture');

PL/SQL procedure successfully completed.

[SYS@CDB$ROOT | SID:CDB]> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

[SYS@CDB$ROOT | SID:CDB]> set lines 500
[SYS@CDB$ROOT | SID:CDB]> set pages 500
[SYS@CDB$ROOT | SID:CDB]> select con_id,NAME,DETECTED_USAGES,CURRENTLY_USED,LAST_USAGE_DATE
  2  from cdb_feature_usage_statistics
  3  where name in ('Privilege Capture')
  4  order by 1;

    CON_ID NAME                                               DETECTED_USAGES CURRE LAST_USAGE_DATE
---------- -------------------------------------------------- --------------- ----- -------------------
         1 Privilege Capture                                                2 TRUE  2013-10-09 22:11:37
         2 Privilege Capture                                                0 FALSE
         3 Privilege Capture                                                0 FALSE
         4 Privilege Capture                                                0 FALSE
         5 Privilege Capture                                                0 FALSE
         6 Privilege Capture                                                0 FALSE
         7 Privilege Capture                                                0 FALSE
         8 Privilege Capture                                                0 FALSE
         9 Privilege Capture                                                0 FALSE
        10 Privilege Capture                                                0 FALSE
        11 Privilege Capture                                                0 FALSE
        12 Privilege Capture                                                0 FALSE

12 rows selected.
If you plan to implement multitenant database, you have to be clever and think about options you bought and how you will implement them into your consolidated databases.
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: