Oracle … as usual

Oracle by Laurent Leturgez

Category Archives: multitenant

When an Oracle 12c common user transforms himself as a local user … but keeps his username.

In Oracle 12c new architecture, you have two kinds of users. Users who are declared in the root container, and users who are declared in a pluggable database.

The first ones are named common user, and the others are name local user. The particularity of those common user is that they are synchronised with the pluggable database(s) to be known in each of them.

Oracle make the distinction between common user and local user by their name. Common usernames have a constraint, they have to start with “C## “(or the value of the parameter “COMMON_USER_PREFIX”). For example, C##LAURENT is a common username whereas LAURENT is a local username.

Oracle controls that, because if you create a common user in a PDB, you will throw an “ORA-65094: invalid local user or role name”. At the opposite, you will throw an “ORA-65096: invalid common user or role name” if you try to create a local user in the CDB$ROOT container.

A weird behaviour happened when you plug a PDB which contained synced definition of a common user.

Below, I paste a demo of what I’m saying (this example has been taken in a SE2 database so … with only one PDB, but I reproduced the problem with a PDB which was unplugged from one container and plugged into another one).

  • In the pluggable database, common user creation

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

SQL> create user c##laurent identified by laurent container=ALL;

User created.

SQL> grant connect to c##laurent container=ALL;

Grant succeeded.

  • In the PDB the common user is synced

SQL> conn sys/oracle@pdb1 as sysdba
Connected.

SQL> select username,common from dba_users where username like 'C##%';

USERNAME                   COM
-------------------------- ---
C##LAURENT                 YES

 

  • Now I unplug my PDB and drop it from the container

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> alter pluggable database pdb1 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb1 unplug into '/home/oracle/laurent.xml';

Pluggable database altered.

SQL> drop pluggable database pdb1;

Pluggable database dropped.

  • Now, let’s drop the common user in the root container to simulate a new and fresh container database
SQL> select con_id,username,common from cdb_users where username like 'C##%';

    CON_ID USERNAME      COM
---------- ------------- ---
	     1 C##LAURENT    YES

SQL> drop user c##laurent cascade;

User dropped.
  • Finally I plug my PDB in the container, open it, and query CDB_USERS to see what happened to my common user c##LAURENT
SQL> create pluggable database newone
  2  using '/home/oracle/laurent.xml'
  3  nocopy tempfile reuse;

Pluggable database created.

SQL> alter pluggable database newone open;

Pluggable database altered.

SQL> select con_id,username,common from cdb_users where username like 'C##%';

    CON_ID USERNAME       COM
---------- -------------- ---
         3 C##LAURENT     YES

SQL> conn c##laurent/laurent@oel6:1521/NEWONE
ERROR:
ORA-28000: the account is locked

Well if you have a closer look to the CON_ID, my “common user” has been affected as a local user (because defined in the PDB). So, if I’m ok with the definition of  local user … it’s a local user, but with a common username … oh dear !!!

Following this URL in the documentation (https://docs.oracle.com/database/121/SQLRF/statements_8003.htm#SQLRF54735), Oracle recommends to set a value for COMMON_USER_PREFIX even with this particular behaviour.

UPDATE : A friend of mine and a fellow Oracle consultant, Pierre Labrousse, gave me a clue In the Oracle MOS Note 1511619.1, Oracle says that if you face a common user who is locked in a newly plugged PDB to do one of the following:

  • Leave the user account locked and use the objects of its schema.
  • Use Oracle Data Pump to copy these objects to another schema, and then drop the locked user account.
  • Close the PDB, connect to the root, and then create a common user with the same name as the locked account. When you re-open the PDB, Oracle Database resolves the differences in the roles and privileges that were commonly granted to the locked user. Afterward, you can unlock this user account in the PDB. Privileges and roles that were locally granted to the user will remain unchanged.

 

 

 

Advertisements

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.