Blog 
Oracle 18 XE and Multitenant

As already mentioned in my blog “Oracle 18 XE is available” the  XE version includes the multitenant database option. So you can create up to three pluggable databases. But in this case it doesn’t matter if it is an “ordinary” PDB, a Proxy PDB  or an Application Root.If this is your first time dealing with the multitenant database Option I would advice to create datafiles as Oracle Managed Files (OMF) because with this the command CREATE PLUGGABLE DATABASE becomes much easier. Unfortunately per default XE is not using Oracle Managed Files.

Oracle Managed Files

Bevore you are going to start you should drop the already created pluggable database XEPDB1. You should gain experience with your own PDBs not by using the precreated ones:

SQL> ALTER PLUGGABLE DATABASE xepdb1 CLOSE IMMEDIATE;
SQL> DROP PLUGGABLE DATABASE xepdb1 INCLUDING DATAFILES;

If you don’t care about the fact that not all datafiles are created as OMF I would recommend not to touch the CDB. In this case it’s sufficient to set the following parameter:

SQL> ALTER SYSTEM SET db_create_file_dest='/u02/oradata';

Please make sure that the directory exists (but you probably already added that directory while creating the database). The variable shouldn’t include the name of the database as this name will be added automatically.
Now it’s time to create the first PDB:

SQL> CREATE PLUGGABLE DATABASE suzanne 
      ADMIN USER pdbadmin IDENTIFIED BY manager
      DEFAULT TABLESPACE users;
SQL> ALTER PLUGGABLE DATABASE suzanne OPEN;
SQL> ALTER PLUGGABLE DATABASE suzanne SAVE STATE;

That’s it!
As always there is one constraint: you shouldn’t try to type the entry path of the OMF datafiles but using “Drag & Drop” instead!

SQL> ALTER SESSION SET "_exclude_seed_cdb_view"=FALSE;
SQL> SELECT p.name, d.tablespace_name, file_name
       FROM cdb_data_files d, v$containers p
      WHERE p.con_id = d.con_id
      ORDER BY p.con_id,2

NAME       TABLESPACE FILE_NAME
---------- ---------- ------------------------------------------------------------------------------------------
CDB$ROOT   SYSAUX     /u02/oradata/XE/sysaux01.dbf
CDB$ROOT   SYSTEM     /u02/oradata/XE/system01.dbf
CDB$ROOT   UNDOTBS1   /u02/oradata/XE/undotbs01.dbf
CDB$ROOT   USERS      /u02/oradata/XE/users01.dbf
PDB$SEED   SYSAUX     /u02/oradata/XE/pdbseed/sysaux01.dbf
PDB$SEED   SYSTEM     /u02/oradata/XE/pdbseed/system01.dbf
PDB$SEED   UNDOTBS1   /u02/oradata/XE/pdbseed/undotbs01.dbf
SUZANNE    SYSAUX     /u02/oradata/XE/7B913842A2980993E0530100007F3756/datafile/o1_mf_sysaux_fzqrrx65_.dbf
SUZANNE    SYSTEM     /u02/oradata/XE/7B913842A2980993E0530100007F3756/datafile/o1_mf_system_fzqrrx5n_.dbf
SUZANNE    UNDOTBS1   /u02/oradata/XE/7B913842A2980993E0530100007F3756/datafile/o1_mf_undotbs1_fzqrrx67_.dbf

The command ALTER SESSION SET "_exclude_seed_cdb_view"=FALSE; adds the datafiles of PDB$SEED to the result set. With version 12.1.0.2 Oracle decided that it’s not useful to show data belonging to PDB$SEED – you can have your own opinion on that.

PDBs and Services

Every PDB is creaded with an associated service. In our case the result of the command lsnrctl status is:

Services Summary...
Service "7b913842a2980993e0530100007f3756" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "XE" has 2 instance(s).
  Instance "XE", status UNKNOWN, has 1 handler(s) for this service...
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "suzanne" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully

This service (suzanne) is, by the way, the reason why PDBs have to be uniquely named on the entire server. This shouldn’t be a problem with XE (as long as we don’t have a newer version) but with the multitenant database Option and multiple databases you should take care that you don’t have PDB naming duplicates.
As a best practice you shouldn’t use the default service in any case!
The recommendation is to have individual services for each task. In our case we will create a service for the application:

SQL> ALTER SESSION SET CONTAINER=suzanne;

SQL> execute dbms_service.create_service('appl_suzanne','appl_suzanne');

SQL> execute dbms_service.start_service('appl_suzanne');

SQL> ALTER PLUGGABLE DATABASE SAVE STATE;

With the command ALTER PLUGGABLE DATABASE SAVE STATE you ensure that the service will be started automatically each time the instance is started. Unfortunately this doesn’t work if you close and open the pluggable database on its own. The application should now only use the service appl_suzanne.

PDB Cloning

You can of course copy or clone a PDB. The command is the easiest one:

SQL> CREATE PLUGGABLE DATABASE marianne FROM suzanne;

Pluggable database created.

SQL> ALTER PLUGGABLE DATABASE marianne OPEN;

Pluggable database altered.

The result looks fine but there is a problem: The view pdb_plug_in_violations will show the details:

SQL> SELECT name, cause, type message, status FROM pdb_plug_in_violations;

NAME            CAUSE                          MESSAGE                                            STATUS
--------------- ------------------------------ -------------------------------------------------- ---------
MARIANNE        Service Name Conflict          WARNING                                            PENDING

The reason for this warning is that cloning will clone the service as well. So we need to take an action as explained in the view:

SQL> SELECT action FROM pdb_plug_in_violations;

ACTION
------------------------------------------------------------
Drop the service and recreate it with an appropriate name.

Let’s go:

SQL> ALTER SESSION SET CONTAINER=marianne;

SQL> SELECT name FROM dba_services;

NAME
---------------
appl_suzanne
MARIANNE

SQL> execute dbms_service.delete_service('appl_suzanne');

SQL> execute dbms_service.create_service('appl_marianne','appl_marianne');

SQL> execute dbms_service.start_service('appl_marianne');

SQL> ALTER PLUGGABLE DATABASE SAVE STATE;

Limits

As said there are some limits comming with XE databases. You cannot create more than three pluggable databases regardless if they are opened or only mounted:

CREATE PLUGGABLE DATABASE hallelujah
                          *
ERROR at line 1:
ORA-65010: maximum number of pluggable databases created

The second limitation is the amount of user data with 12 GByte. It doesn’t matter if this data is in one PDB or spreaded across multiple PDBs. While testing this I found that it doesn’t matter if the PDBs are opened or mounted.

SQL> ALTER PLUGGABLE DATABASE leonard OPEN;
ALTER PLUGGABLE DATABASE leonard OPEN 
*
ERROR at line 1:
ORA-12954: The request exceeds the maximum allowed database size of 12 GB.

Saving PDBs

But what can you do if a PDB is reaching this border and you need to create an additional PDB? It’s very simple: you close the PDB, create a manifest (that’s an XML-File with the definition of the PDB) and drop the PDB. Dont be afraid for the saved data I’ll explain how to restore it with minimal effort.

SQL> ALTER PLUGGABLE DATABASE leonard CLOSE IMMEDIATE;
SQL> ALTER PLUGGABLE DATABASE leonard UNPLUG INTO '/home/oracle/leonard.xml';
SQL> DROP PLUGGABLE DATABASE leonard;

Please ensure that you don’t use INCLUDING CONTENTS with the command DROP PLUGGABLE DATABASE – otherwise your PDB is dropped irreversably. But you have a backup do you?
Now you can create new PDBs and use them. If you need the big PDB later on you drop the newly created PDBs and reuse the original one:

SQL> CREATE PLUGGABLE DATABASE leonard USING '/home/oracle/leonard.xml' NOCOPY;
SQL> ALTER PLUGGABLE DATABASE leonard OPEN;
SQL> ALTER PLUGGABLE DATABASE leonard SAVE STATE;

Conclusion

This was the first impression on multitenant database in XE. But the examples will work for Enterprise Edition as well. Without the XE limitations of course.
If you have any questions or comments feel free to write a blog comment or drop an mail at info@carajandb.com.