The November 2017 issue of the Red Stack Magazine published by DOAG and AOUG contains one of my articles about Oracle 12c Multitenant 🙂

Common vs Local Users

A 12c Multitenant Database is designed to make consolidation easier. The PDBs have not only their application data in separate tablespaces, they also have their application metadata in separate SYSTEM tablespaces. That’s what makes it so easy and fast to unplug a PDB.

The SYSTEM tablespace of the CDB contains the internal metadata that is shared by all PDBs. Internal metadata (the dictionary tables) and internal objects (like the DBMS* packages) belong to Common Users, most prominently to SYS. We don’t have to install all that internal stuff inside of the PDBs, they can just refer to it.

The point is that Common Users are what enables the Multitenant Architecture in the first place. You as a DBA may also create them as a side aspect of the matter. Now what does that mean in practice?

[oracle@uhesse ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Feb 6 11:34:20 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name,open_mode,con_id from v$pdbs;

NAME	   OPEN_MODE	  CON_ID
---------- ---------- ----------
PDB$SEED   READ ONLY	       2
PDB1	   READ WRITE	       3
PDB2	   READ WRITE	       4

My demo system looks like the picture above with two PDBs apart from the seed. Let’s try to clarify the difference between SYS as a common user and SCOTT as two local users.

SQL> connect sys/oracle@pdb1 as sysdba
Connected.
SQL> select common from dba_users where username='SCOTT';

COM
---
NO

SQL> select common from dba_users where username='SYS';

COM
---
YES
SQL> delete from scott.dept where deptno=40;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from scott.dept;

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
SQL> connect sys/oracle@pdb2 as sysdba
Connected.
SQL> select common from dba_users where username='SCOTT';

COM
---
NO

SQL> select common from dba_users where username='SYS';

COM
---
YES

SQL> select * from scott.dept;

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	40 OPERATIONS	  BOSTON

There are two local users with incidentally the same name SCOTT in the two PDBs. The common user SYS is almighty in both PDBs. What common users do we have else visible in the PDBs?

SQL> select username from dba_users where common='YES';

USERNAME
-------------------------------------------------------
SYS
SYSTEM
SYSRAC
SYS$UMF
OUTLN
DBSNMP
APPQOSSYS
CTXSYS
SI_INFORMTN_SCHEMA
GSMADMIN_INTERNAL
ORDPLUGINS
MDSYS
ORDDATA
XDB
WMSYS
ORDSYS
GGSYS
ANONYMOUS
GSMCATUSER
SYSBACKUP
GSMUSER
DIP
SYSKM
ORACLE_OCM
SYSDG
REMOTE_SCHEDULER_AGENT
DBSFWUSER
XS$NULL
OJVMSYS
AUDSYS

30 rows selected.

Apart from SYS and SYSTEM, you see some more internal users on display here. Most of them are related to certain options. This is how these options become available throughout all PDBs. Notice that DBA_USERS shows only what is visible to that PDB. The second SCOTT from PDB2 is not listed, because that user is relevant only for PDB2. It is in other words a local user inside PDB2. CDB_USERS confirms that there are two SCOTT users:

SQL> connect / as sysdba
Connected.
SQL> select con_id,common from cdb_users where username='SCOTT';

    CON_ID COM
---------- ---
	 3 NO
	 4 NO

If you want to create a common user yourself, it must be prefixed with C##. A common user is immediately visible in all PDBs. That doesn’t mean that user has any rights, though.

SQL> connect / as sysdba
Connected.
SQL> create user c##adam identified by oracle container=all;

User created.

SQL> connect sys/oracle@pdb1 as sysdba
Connected.
SQL> select username,common from dba_users where username like 'C##%';

USERNAME   COM
---------- ---
C##ADAM    YES

SQL> connect sys/oracle@pdb2 as sysdba
Connected.
SQL> select username,common from dba_users where username like 'C##%';

USERNAME   COM
---------- ---
C##ADAM    YES

SQL> connect c##adam/oracle@pdb1
ERROR:
ORA-01045: user C##ADAM lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect c##adam/oracle@pdb2
ERROR:
ORA-01045: user C##ADAM lacks CREATE SESSION privilege; logon denied

Common users can get privileges granted as common or as local privileges:

SQL> grant create session to c##adam container=all;

Grant succeeded.

SQL> connect c##adam/oracle@pdb1
Connected.
SQL> connect c##adam/oracle@pdb2
Connected.
SQL> connect / as sysdba
Connected.
SQL> revoke create session from c##adam;
revoke create session from c##adam
*
ERROR at line 1:
ORA-65092: system privilege granted with a different scope to 'C##ADAM'


SQL> revoke create session from c##adam container=all;

Revoke succeeded.

Notice that container=all must be specified upon the revoke as well. That was a common privilege. Now for the local privilege:

SQL> connect sys/oracle@pdb1 as sysdba
Connected.
SQL> grant  create session to c##adam container=current;

Grant succeeded.

SQL> connect c##adam/oracle@pdb1
Connected.
SQL> connect c##adam/oracle@pdb2
ERROR:
ORA-01045: user C##ADAM lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.

C##ADAM has the create session privilege only for PDB1, not for PDB2. It is in other words local to PDB1. The clause container=current is optional here and can be left out to achieve the same effect:

SQL> connect sys/oracle@pdb1 as sysdba
Connected.
SQL> revoke create session from c##adam container=current;

Revoke succeeded.

SQL> grant  create session to c##adam;

Grant succeeded.

SQL> revoke create session from c##adam;

Revoke succeeded.

SQL> grant  create session to c##adam container=current;

Grant succeeded.

The two revokes and the two grants have had the same effect. A typical use case for the creation of common users would be to get less powerful superusers like in this example. I would recommend to implement those with some reservation, not to speak about common roles, granting a mixture of local and common privileges to roles and roles to (common) roles. Much is possible but that doesn’t necessarily mean it is a good idea to do it 🙂

This article is pretty close to what I have presented in Mainz last week during the very well run DOAG Noon2Noon event:

Only that I was talking in German, which made it a bit easier 🙂

Hot cloning PDBs is new in 12.2, so you don’t have to put the source PDB into READ ONLY mode before the cloning if you have it in local undo mode. I suppose shared undo mode will become very unpopular. Another 12.2 New Feature is the option to clone a PDB that can be refreshed from the source PDB. I will show both features with this article, but you may of course do hot cloning without a later refresh. In this case, just leave out the REFRESH MANUAL clause and you don’t have to OPEN READ ONLY the cloned PDB afterwards. On a high level, what I demonstrate is this:

Hot cloning & refreshing a PDB

Now let’s see that with details:

SQL> connect sys/oracle_4U@cdb1 as sysdba
Connected.
SQL> select name,open_mode from v$pdbs;

NAME	   OPEN_MODE
---------- ----------
PDB$SEED   READ ONLY
PDB1	   READ WRITE

I have prepared a tnsnames.ora with connect descriptors for pdb1 and the future pdb2:

PDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = uhesse)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1)
    )
  )
PDB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = uhesse)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb2)
    )
  )

Now I create the clone user inside of the source PDB and the database link inside the destination CDB:

SQL> connect sys/oracle_4U@pdb1 as sysdba
Connected.
SQL> grant create session,create pluggable database to cloner identified by cloner;

Grant succeeded.

SQL> connect sys/oracle_4U@cdb2 as sysdba
Connected.
SQL> select name,open_mode from v$pdbs;

NAME	   OPEN_MODE
---------- ----------
PDB$SEED   READ ONLY

SQL> create database link clone_link connect to cloner identified by cloner using 'pdb1';

Database link created.

SQL> select * from session_privs@clone_link;

PRIVILEGE
----------------------------------------
CREATE PLUGGABLE DATABASE
CREATE SESSION

The steps so far are the same as with 12.1. My databases are single instances running on the same Linux server without ASM.

SQL> connect sys/oracle_4U@cdb2 as sysdba

SQL> host mkdir /u01/app/oracle/oradata/pdb2
SQL> create pluggable database pdb2 from pdb1@clone_link file_name_convert=('pdb1','pdb2')
     refresh mode manual;  

Pluggable database created.

SQL> alter pluggable database pdb2 open read only;

Pluggable database altered.

The source pdb1 remains OPEN READ WRITE all the time during the demo. Now I change something there and refresh pdb2 afterwards:

SQL> connect adam/adam@pdb2
Connected.
SQL> select count(*) from sales;

  COUNT(*)
----------
    748999

SQL> connect adam/adam@pdb1
Connected.
SQL> delete from sales where rownum<1000; 

999 rows deleted. 

SQL> commit;

Commit complete.

SQL> select count(*) from sales;

  COUNT(*)
----------
    748000

SQL> connect sys/oracle_4U@cdb2 as sysdba
Connected.
SQL> alter pluggable database pdb2 close immediate;

Pluggable database altered.

Notice that you need to perform the refresh from inside the cloned PDB, else you get this error message:

SQL> alter pluggable database pdb2 refresh;
alter pluggable database pdb2 refresh
*
ERROR at line 1:
ORA-65118: operation affecting a pluggable database cannot be performed from
another pluggable database

So I connect into the cloned PDB to do the refresh there:

SQL> connect sys/oracle_4U@pdb2 as sysdba
Connected.
SQL> alter pluggable database refresh;

Pluggable database altered.

SQL> alter pluggable database open read only;

Pluggable database altered.

SQL> select count(*) from adam.sales;

  COUNT(*)
----------
    748000

Fortunately, you are not allowed to open this cloned PDB in normal READ WRITE mode because of the REFRESH clause added to the CREATE PLUGGABLE DATABASE command:

SQL> shutdown immediate
Pluggable Database closed.
SQL> connect sys/oracle_4U@cdb2 as sysdba
Connected.
SQL> alter pluggable database pdb2 open;
alter pluggable database pdb2 open
*
ERROR at line 1:
ORA-65341: cannot open pluggable database in read/write mode

Another possible implementation would have been to go to OPEN READ ONLY silently like with a Physical Standby. Don’t believe it, test it! 🙂

Watch me explaining and demonstrating the above on YouTube: