By Franck Pachot

.
Matching the Feature Usage Statistics to licensed options is not always easy. And once a feature usage is recorded, it is usually stored definitively. Let’s see how the multitenant option is recorded there.

exec_db_usage_sampling

I have a single-tenant database with only one user pluggable database:


16:19:20 SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO

Two pluggable databases are displayed. The forst one is the seed (CON_ID=2) and user pluggable databases have CON_ID>2.
and I run the Database Feature Usage Sampling:


16:19:20 SQL> exec sys.dbms_feature_usage_internal.exec_db_usage_sampling(sysdate); commit;
 
PL/SQL procedure successfully completed.
 

Usually this is run automatically on weekly basis, but we can run it manually as I did above.

Let’s check the result from DBA_FEATURE_USAGE_STATISTICS:


16:19:34 SQL> select name, version, detected_usages, aux_count, feature_info,last_usage_date from dba_feature_usage_statistics where name = 'Oracle Pluggable Databases';
 
NAME                       VERSION     DETECTED_USAGES  AUX_COUNT FEAT LAST_USAGE_DATE
-------------------------- ----------- --------------- ---------- ---- ---------------
Oracle Pluggable Databases 12.1.0.2.0               11          1      27-MAR 16:19:20

Here is the first conclusion: ‘Oracle Pluggable Database’ is the initial name of ‘Oracle Multitenant’ feature and is used as long as we are in CDB.
You don’t need the Multitenant Option for that as long as AUX_COUNT is 1 because AUX_COUNT is the number of user pluggable databases.

Multitenant Option

I’ll create an additional pluggable database. I Standard Edition, I can’t (ORA-65010: maximum number of pluggable databases created) but in Enterprise Edition there is no supported way to prevent it:


16:19:34 SQL> create pluggable database PDB2 from PDB file_name_convert=('/PDB/','/PDB2/');
 
Pluggable database created.
 
16:20:54 SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO
         4 PDB2                           MOUNTED

And I run again the sampling:


16:20:54 SQL> exec sys.dbms_feature_usage_internal.exec_db_usage_sampling(sysdate); commit;
 
PL/SQL procedure successfully completed.
 
16:21:15 SQL> select name, version, detected_usages, aux_count, feature_info,last_usage_date from dba_feature_usage_statistics where name = 'Oracle Pluggable Databases';
 
NAME                       VERSION     DETECTED_USAGES  AUX_COUNT FEAT LAST_USAGE_DATE
-------------------------- ----------- --------------- ---------- ---- ---------------
Oracle Pluggable Databases 12.1.0.2.0               12          2      27-MAR 16:20:54

The difference here is the AUX_COUNT. When higher than 1 you need to buy the Multitenant Option.

Enterprise Manager

The DBA_FEATURE_USAGE_STATISTICS is displayed in Enterprise Manager:
Capture1
but you will not find the AUX_COUNT there:
CaptureMTFU2
So unfortunatly you have no clue about the need to licence the option from there.

Back to conformity

When you don’t have the Multitenant Option, as you don’t have a way to prevent licence violation, you need to monitor that AUX_COUNT and raise an alert. Then what to do? Here, because the difference between single-tenant and multitenant is only in AUX_COUNT, and only the latest AUX_COUNT value is stored you can easily repair your error.

First, you drop the additional pluggable database (plug it into another CDB if you still need it):


16:21:15 SQL> drop pluggable database PDB2 including datafiles;
 
Pluggable database dropped.
 
16:21:16 SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO

Then run sampling again:


16:21:16 SQL> exec sys.dbms_feature_usage_internal.exec_db_usage_sampling(sysdate); commit;
 
PL/SQL procedure successfully completed.

and the temporary violation of single-tenant disappeared:


16:21:30 SQL> select name, version, detected_usages, aux_count, feature_info,last_usage_date from dba_feature_usage_statistics where name = 'Oracle Pluggable Databases';
 
NAME                       VERSION     DETECTED_USAGES  AUX_COUNT FEAT LAST_USAGE_DATE
-------------------------- ----------- --------------- ---------- ---- ---------------
Oracle Pluggable Databases 12.1.0.2.0               13          1      27-MAR 16:21:16

dbms_pdb_num

You don’t need to run the full sampling in order to check the number of pluggable databases. You can call the DBMS_PDB_NUM:


16:21:30 SQL> variable feature_boolean number
16:21:30 SQL> variable aux_count number
16:21:30 SQL> variable feature_info clob
16:21:30 SQL> exec dbms_pdb_num(:feature_boolean,:aux_count,:feature_info);
 
PL/SQL procedure successfully completed.
 
16:21:30 SQL> print
 
FEATURE_BOOLEAN
---------------
              1
 
 AUX_COUNT
----------
         1
 
FEATURE_INFO
--------------------------------------------------------------------------------
 

If you check catfusrg.sql in rdbms/admin you know how it is collected: the usage consider that V$DATABASE.CDB=’YES’ and the AUX_COUNT counts V$PDBS where CON_ID > 2

dbms_feature_usage_report

While we are there, let’s show another way to report from DBA_FEATURE_USAGE_STATISTICS:


16:21:30 SQL> SELECT output FROM TABLE(dbms_feature_usage_report.display_text);
 
OUTPUT
--------------------------------------------------------------------------------
DB FEATURE USAGE report for
 
DB Name         DB Id     Release    Total Samples Last Sample Time
------------ ----------- ----------- ------------- ------------------
CDB           2021614852 12.1.0.2.0              1 24-Mar-16 10:02:09
 
          -------------------------------------------------------------
 
DB Feature Usage Summary                            DB/Release: CDB/12.1.0.2.0
-> This section displays the summary of Usage for Database Features.
-> The Currently Used column is TRUE if usage was detected for
-> the feature at the last sample time.
 
                                          Curr-
                                          ently Detected    Total Last Usage
Feature Name                              Used    Usages  Samples    Time
----------------------------------------- ----- -------- -------- --------------
Resource Manager                          TRUE         1        1 03/24/16 10:02
...
Oracle Pluggable Databases                FALSE       13        1 03/27/16 16:21
...
DB Feature Usage Details                            DB/Release: CDB/12.1.0.2.0
-> This section displays the detailed usage data for the features
-> that have at least one detected usage
 
Feature Name
------------------------------------------------------------
Detected Usages  Total Samples  First Usage Date   Last Usage Date  Aux Count
--------------- -------------- ----------------- ----------------- ----------
Feature Info
--------------------------------------------------------------------------------
...
Oracle Pluggable Databases
             13              1    03/24/16 10:02    03/27/16 16:21          1

So what?

There are currently (in 12.1) no way to limit to single-tenant when you are in Enterprise Edition, but there is an Evolution Request filled about it and we can expect to be able to set something like a ‘max pdbs’ parameters in future release. Anyway, it’s easy to monitor usage and get back to single-tenant.

Post Views: 1,647
Thumbnail [60x60]
by
Oracle Team