Issues with Oracle Multi-tenant Application Containers

Introduction

The Oracle Database 12c Release 1 introduced the Oracle Multi-tenant architecture, which allowed consolidation of multiple pluggable databases (PDBs) together in a multi-tenant container database (CDB). Various PDBs store data in objects independently from other PDBs and are maintained separately while using a single CDB instance. The only objects common across various PDBs were Oracle-supplied schemas which could exist in the CDB root only. If multiple PDBs shared an application, there was no provision for a single master definition of the application on top of PDBs. As a result, multiple copies of application definition needed to be stored individually in all the PDBs sharing an application. Also, the application upgrade script had to be executed in all PDBs individually, which is time consuming.

 In order to resolve these issues, Oracle Database 12.2 introduces the concept of Multi-tenant Application Containers, which enables many PDBs to share application objects such as code, metadata, and data. An application container is a special type of PDB consisting of an application root, an optional application seed, and all of the application PDBs associated with it. The application root stores a master definition containing application common objects for one or more applications. This master definition gets propagated to the application PDBs when the application PDBs synchronize with the application in the application root.

 There are three types of application common objects:

• Metadata linked : The metadata for the object is stored once in the application root and shared across all the application PDBs associated with the application container. The data for the object is unique to the application root and each application PDB that belongs to the application root.
• Data linked: Both the metadata and the data for the object are stored once in the application root and shared across all the application PDBs associated with the application container.
• Extended data linked: Data stored in the application root is common for all application PDBs, while each application PDB can create its own specific data as well.

In the process of exploring this new feature of Oracle Multi-tenant Application Containers, I have come across various issues which I would like to share with the community by means of this article.

Current scenario

• Oracle database version – 12.2.0.1
• Type of database – Container database (CDB)
• Name of CDB – orclcdb
  • Application container– app_root
    • Application seed – app_root$seed
    • Application PDBs
      • North_app_pdb
      • East_app_pdb
      • West_app_pdb
      • South_app_pdb

Overview

-      Create an application tst_app in the application container app_root. While connected to app_root

• Create common application user tst_app_user
• Create table tst_app_user .zip_codes with sharing = extended data and populate with primary key zip_code=1
• Create table tst_app_user .customers with sharing = metadata, primary key cust_id and foreign key cust_zip referencing tst_app_user .zip_codes.zip_code
• Populate tst_app_user .customers table with a record having a valid foreign key cust_zip =1 
• Create and populate table tst_app_user .products with sharing = data

-      Attempt to synchronize application PDB west_app_pdb with tst_app application - fails

-      Upgrade the application tst_app and delete the offending record from tst_app_user .customers table

-      Attempt to synchronize  application PDB west_app_pdb with tst_app application – fails again

-      Uninstall application tst_app and try to reinstall application tst_app - fails

-      Create a new application tst_app1 without the offending data in tst_app_user .customers table

-      Sync new application with two application PDBs - west_app_pdb and north_app_pdb

-      Access common application objects in a synced application PDB north_app_pdb

• Report various issues faced 

Demonstration

-      Note that currently there is one application container root app_root in this CDB

SQL>conn sys/oracle@host01:1522/orclcdb as sysdba
 
CDB$ROOT> SELECT con_id, name, open_mode, application_root
                 app_root, application_pdb app_pdb,
                  application_seed app_seed
           FROM  v$containers
           WHERE application_root = 'YES'
            and application_PDB = 'NO';
 
    CON_ID NAME             OPEN_MODE  APP_ROOT APP_PDB APP_SEED
---------- --------------- ---------- -------- --------  --------
   12         APP_ROOT      READ WRITE      YES      NO     NO
 
-      Connect to application root app_root
CDB$ROOT> conn sys/oracle@host01:1522/app_root as sysdba
                       sho con_name
 
CON_NAME
------------------------------
APP_ROOT 

-      Note that there are four application PDBs (north_app_pdb, east_app_pdb, west_app_pdb, south_app_pdb) and one application seed PDB (app_root$seed) associated with app_root application root

APP_ROOT> SELECT con_id, name, open_mode, application_root app_root,
                 application_pdb app_pdb, application_seed app_seed
          FROM   v$containers 
          ORDER by con_id; 
 
 
    CON_ID NAME             OPEN_MODE APP_ROOT APP_PDB APP_SEED
---------- --------------- ---------- -------- -------- --------
    4       APP_ROOT$SEED   READ ONLY  NO       YES         YES
    5       SOUTH_APP_PDB   READ WRITE NO       YES         NO
    6       EAST_APP_PDB    READ WRITE NO       YES         NO
    7       WEST_APP_PDB    READ WRITE NO       YES         NO
    8       NORTH_APP_PDB   READ WRITE NO       YES         NO
   12       APP_ROOT    READ WRITE YES      NO      NO
 
6 rows selected.

-      Create an application tst_app in application container app_root

APP_ROOT>ALTER PLUGGABLE DATABASE APPLICATION tst_app BEGIN INSTALL '1.0';

-      Create common application user tst_app_user

APP_ROOT> CREATE USER tst_app_user IDENTIFIED BY oracle   
          CONTAINER=ALL;
 
          GRANT CREATE SESSION, create procedure, CREATE TABLE,
                unlimited tablespace TO tst_app_user;
 
-      Create table tst_app_user .zip_codes with sharing = extended data and populate with primary key zip_code =1
APP_ROOT> create table tst_APP_USER.zip_codes
                      SHARING=EXTENDED DATA
          (zip_code number constraint zip_pk primary key,
           country varchar2(20)); 
 
          insert into  tst_app_user.zip_codes
          values (1, 'Root India');
          commit;

-      Create table tst_app_user .customers with sharing = metadata, primary key cust_id and foreign key cust_zip referencing tst_app_user .zip_codes.zip_code

      APP_ROOT> CREATE TABLE tst_app_user.customers SHARING=METADATA
   ( cust_id    NUMBER constraint cust_pk primary key,
     cust_name  varchar2(30),
     cust_add   varchar2(30),
     cust_zip   NUMBER constraint cust_zip_fk references
                       tst_app_user.zip_codes(zip_code)
   );

-      Populate tst_app_user .customers table with a record having a valid foreign key cust_zip =1 . Note that customers table is a metadata linked table whose data will not be visible in application PDBs

APP_ROOT> insert into tst_app_user.customers
          values ('0', 'Root Customer0', 'India address', 1);
 
1 row created.
 
APP_ROOT>commit;
 
Commit complete

-      Create and populate table tst_app_user .products with sharing = data

APP_ROOT> create table tst_APP_USER.products
                 SHARING=  DATA
          (prod_id number constraint prod_pk primary key,
           prod_name varchar2(20),
           price number);
 
APP_ROOT> insert into tst_APP_USER.products values (1, 'Root product1', 111);
         
1 row created.
 
APP_ROOT>  commit;
 
Commit complete.

-      End installation of application tst_app and check data in various common application objects created so far

APP_ROOT>ALTER PLUGGABLE DATABASE APPLICATION tst_app END INSTALL '1.0';
 
APP_ROOT>select * from tst_app_user.zip_codes;
 
 ZIP_CODE COUNTRY
---------- --------------------
    1 Root India
 
 
APP_ROOT> select * from tst_app_user.products;
 
   PROD_ID PROD_NAME               PRICE
---------- -------------------- ----------
    1 Root product1        111
 
APP_ROOT>select * from tst_app_user.customers;
 
   CUST_ID CUST_NAME                  CUST_ADD            CUST_ZIP
---------- ------------------------------ ------------------------------ ----------
    0 Root Customer0             India address           1

-      Attempt to synchronize application PDB west_app_pdb with tst_app application – fails

APP_ROOT> conn sys/oracle@host01:1522/west_app_pdb as sysdba
 
Connected.
 
WEST_APP_PDB>alter pluggable database application tst_app sync;
 
WEST_APP_PDB >alter pluggable database application tst_app sync
*
ERROR at line 1:
ORA-02291: integrity constraint (TST_APP_USER.CUST_ZIP_FK) violated - parent key not found

As displayed by the error message , the application PDB west_app_pdb has failed to sync with tst_app application since foreign key constraint on customers table has been violated which is strange since

• customers table is metadata linked; its data created in application root should not be visible to application PDB and hence should not be checked for constraint violation in any application PDB.
• zip_code  1 is very much there in table zip_codes and since the table extended data linked, its data should be visible in application PDBs.

-      Upgrade the application tst_app and delete the offending record from tst_app_user .customers table

APP_ROOT>ALTER PLUGGABLE DATABASE APPLICATION tst_app BEGIN UPGRADE '1.0' to '2.0';
 
APP_ROOT>delete from tst_app_user.customers;
         commit;
 
APP_ROOT>ALTER PLUGGABLE DATABASE APPLICATION tst_app end UPGRADE to '2.0';
 
APP_ROOT>select * from tst_app_user.customers;
 
no rows selected

-      Attempt to synchronize application PDB west_app_pdb with tst_app application – fails again.

WEST_APP_PDB>alter pluggable database application tst_app sync;
 
alter pluggable database application tst_app sync
*
ERROR at line 1:
ORA-02291: integrity constraint (TST_APP_USER.CUST_ZIP_FK) violated - parent key not found

As can be seen, I cannot sync the PDB even now. It may be because first it will be synced with version 1 followed by version 2. Since the first step itself is failing, it cannot move on to the next step.

-      Uninstall application tst_app and try to reinstall application tst_app – fails

APP_ROOT> ALTER PLUGGABLE DATABASE APPLICATION tst_app begin uninstall;
 
Pluggable database altered.
 
APP_ROOT>drop user tst_app_user cascade;
 
User dropped.
 
APP_ROOT>ALTER PLUGGABLE DATABASE APPLICATION tst_app end uninstall;
 
Pluggable database altered.
 
APP_ROOT>select app_name, app_version, app_id, app_status,
                app_implicit implicit
         from dba_applications
         where app_name =‘TST_APP';
 
APP_NAME       APP_VERSION  APP_ID APP_STATUS   IMPLICIT
------------- -----------  ------ ------------ --------
TST_APP               2.0     61 UNINSTALLED  N
 
APP_ROOT>ALTER PLUGGABLE DATABASE APPLICATION tst_app BEGIN INSTALL '1.0';
 
ALTER PLUGGABLE DATABASE APPLICATION tst_app BEGIN INSTALL '1.0'
*
ERROR at line 1:
ORA-65245: application TST_APP has been uninstalled

It seems that I cannot reinstall same application.  Let me try to drop application tst_app

APP_ROOT>ALTER PLUGGABLE DATABASE APPLICATION tst_app drop;
 
ALTER PLUGGABLE DATABASE APPLICATION tst_app drop
                                             *
ERROR at line 1:
ORA-00922: missing or invalid option

It seems it is not possible to drop an application once it has been created.

Now the only option left for me is to install another application; say, tst_app1. This time I won't populate the metadata linked customers table.

-      Create a new application tst_app1 without the offending data in tst_app_user .customers table

APP_ROOT>ALTER PLUGGABLE DATABASE APPLICATION tst_app1
         BEGIN INSTALL '1.0';
 
APP_ROOT>CREATE USER tst_app1_user IDENTIFIED BY oracle 
         CONTAINER=ALL;
 
         GRANT CREATE SESSION, create procedure, CREATE TABLE,
         unlimited tablespace TO tst_app1_user;
 
APP_ROOT> create table tst_APP1_USER.zip_codes
           SHARING=EXTENDED DATA
          (zip_code number constraint zip_pk primary key,
           country varchar2(20)); 
 
          insert into  tst_app1_user.zip_codes
           values (1, 'Root India');
          commit;
 
APP_ROOT> CREATE TABLE tst_app1_user.customers
          SHARING=METADATA
              ( cust_id    NUMBER constraint cust_pk primary key,
                cust_name  varchar2(30),
                cust_add   varchar2(30),
                cust_zip   NUMBER constraint cust_zip_fk
               references tst_app1_user.zip_codes(zip_code)
              );
 
 
APP_ROOT> create table tst_APP1_USER.products
          SHARING=  DATA
          (prod_id number constraint prod_pk primary key,
          prod_name varchar2(20),
          price number);
 
          insert into tst_APP1_USER.products
          values (1, 'Root product1', 111);
          commit;
 
APP_ROOT>ALTER PLUGGABLE DATABASE APPLICATION tst_app1
         END INSTALL '1.0';
 
APP_ROOT> select * from tst_app1_user.zip_codes;
 
  ZIP_CODE COUNTRY
---------- --------------------
    1 Root India
 
APP_ROOT> select * from tst_app1_user.customers;
 
no rows selected
 
APP_ROOT> select * from tst_app1_user.products;
 
   PROD_ID PROD_NAME               PRICE
---------- -------------------- ----------
    1 Root product1        111

-      Sync new application tts_app1 with two application PDBs - west_app_pdb and north_app_pdb - succeeds

WEST_APP_PDB>alter pluggable database application tst_app1 sync;
 
Pluggable database altered.
 
NORTH_APP_PDB>alter pluggable database application tst_app1 sync;
 
Pluggable database altered.
 
APP_ROOT> SELECT c.name,
          aps.app_name,
          aps.app_version,
          aps.app_status
   FROM   dba_app_pdb_status aps
          JOIN v$containers c ON c.con_uid = aps.con_uid
   WHERE  aps.app_name = 'TST_APP1';
 
NAME               APP_NAME           APP_VERSION APP_STATUS
-------------------- -------------------- ----------- ------------
WEST_APP_PDB       TST_APP1           1.0       NORMAL
NORTH_APP_PDB      TST_APP1           1.0       NORMAL

Finally I have been able to sync application PDBs west_app_pdb and north_app_pdb with application tst_app1.

Now I will try to access common objects of tst_app1 application in synced application PDB north_app_pdb.

-      Connect to north_app_pdb as tst_app_user and verify that application objects and constraints created in root are visible.  

APP_ROOT>conn tst_app1_user/oracle@host01:1522/NORTH_app_pdb 
 
NORTH_APP_PDB>select object_name, object_type, sharing
              from   user_objects
              order by object_type, object_name;
 
OBJECT_NAME      OBJECT_TYPE   SHARING
------------ ---------------  ------------------
CUST_PK       INDEX            NONE
PROD_PK       INDEX            NONE
ZIP_PK        INDEX            NONE
CUSTOMERS     TABLE            METADATA LINK
PRODUCTS       TABLE            DATA LINK
ZIP_CODES     TABLE            EXTENDED DATA LINK
 
6 rows selected.
 
NORTH_APP_PDB>select constraint_name, table_name, constraint_type ,
                     R_CONSTRAINT_NAME
              from user_constraints;
 
CONSTRAINT_NAME    TABLE_NAME CONSTRAINT_TYPE R_CONSTRAINT_NAME
---------------    ---------- --------------- -----------------
ZIP_PK              ZIP_CODES  P
CUST_PK            CUSTOMERS  P
PROD_PK             PRODUCTS   P
CUST_ZIP_FK         CUSTOMERS  R             ZIP_PK

-      Check that the common application tables customers, products and zip_codes can be queried.

NORTH_APP_PDB>select * from customers;
 
no rows selected
 
NORTH_APP_PDB>select * from zip_codes;
 
  ZIP_CODE COUNTRY
---------- --------------------
    1 Root India
 
 
NORTH_APP_PDB>select * from products;
 
   PROD_ID PROD_NAME               PRICE
---------- -------------------- ----------
    1 Root product1        111

-      Try to add record in data linked table products – it fails as expected.

NORTH_APP_PDB>insert into   products values (2, 'prod2', 111);
 insert into   products values (2, 'prod2', 111)
              *
ERROR at line 1:
ORA-65097: DML into a data link table is outside an application action

-      Add a zip_code  (1) same as that inserted in root - succeeds although there is primary key constraint. Also note that the newly inserted record is not visible.

NORTH_APP_PDB>insert into zip_codes values (1, 'NORTH IndiA');
 
1 row created.
 
NORTH_APP_PDB>select * from zip_codes;
 
  ZIP_CODE COUNTRY
---------- --------------------
    1 Root India

-      If I again attempt to add the same zip_code  (1) as that inserted in root, it rightfully fails this time, as Primary key constraint is violated. It seems that the earlier record with zip_code  =1 added in north_app_pdb is causing the Primary Key constraint to fail.

NORTH_APP_PDB>insert into zip_codes values (1, 'NORTH IndiA again');
insert into zip_codes values (1, 'NORTH IndiA again')
*
ERROR at line 1:
ORA-00001: unique constraint (TST_APP1_USER.ZIP_PK) violated 

-      Let me add another zip_code  (2) now. Although a record gets inserted, new zip_code is also not visible

NORTH_APP_PDB>insert into zip_codes values (2, 'NORTH USA');
 
1 row created.
 
NORTH_APP_PDB>select * from zip_codes;
 
  ZIP_CODE COUNTRY
---------- --------------------
    1 Root India

-      Note that all the records are visible as soon as changes are committed.

NORTH_APP_PDB>commit;
             select * from zip_codes;
 
  ZIP_CODE COUNTRY
---------- --------------------
    1 Root India
    1 NORTH IndiA
    2 NORTH USA 

-      If I try to duplicate zip_code  2 – it rightfully fails because of the primary key constraint violation.

NORTH_APP_PDB>insert into zip_codes values (2, 'NORTH RUSSIA');
 
insert into zip_codes values (2, 'NORTH RUSSIA')
*
ERROR at line 1:
ORA-00001: unique constraint (TST_APP1_USER.ZIP_PK) violated

-      If I add another zip_code  (3), it is again not visible since it is uncommitted yet.

NORTH_APP_PDB>insert into zip_codes values (3, 'NORTH RUSSIA');
 
1 row created.
 
NORTH_APP_PDB>select * from zip_codes;
 
  ZIP_CODE COUNTRY
---------- --------------------
    1 Root India
    1 NORTH IndiA
    2 NORTH USA

-      Although zip_code  3 is not visible, I can delete it successfully.

NORTH_APP_PDB>delete from zip_codes where zip_code = 3;
 
1 row deleted. 

-      Currently there are two records with zip_code = 1. If I try to update the records with zip_code =1,  while the record added in root is not updated , the record added in north_app_pdb is updated and the updated record is visible only after commit.

NORTH_APP_PDB>update zip_codes set country = 'NORTH india' where zip_code = 1;
 
1 row updated.
 
NORTH_APP_PDB>select * from zip_codes;
 
  ZIP_CODE COUNTRY
---------- --------------------
    1 Root India
    1 NORTH IndiA
    2 NORTH USA
 
 
NORTH_APP_PDB>commit;
Commit complete.
 
NORTH_APP_PDB>select * from zip_codes;
 
  ZIP_CODE COUNTRY
---------- --------------------
    1 Root India
    1 NORTH india
    2 NORTH USA

-      Now, I will delete the record for zip_code  = 1 added in north_app_pdb so that the only record for zip_code =1 is the one that was added in root.

NORTH_APP_PDB>delete from zip_codes where country = 'NORTH india';
 
1 row deleted.
 
NORTH_APP_PDB>commit;
 
Commit complete.
 
NORTH_APP_PDB>select * from zip_codes;
 
  ZIP_CODE COUNTRY
---------- --------------------
    1 Root India
    2 NORTH USA

-      Try to add a record to the metadata linked table customers with a zip_code  (1) that was added in root – it fails as the parent key is not found and foreign key constraint is violated; i.e., record created in extended data table zip_codes in root is not visible to foreign key constraint

NORTH_APP_PDB>insert into customers values ('1', 'NORTH Cust1', 'Root India address', 1);
insert into customers values ('1', 'NORTH Cust1', 'Root India address', 1)
*
ERROR at line 1:
ORA-02291: integrity constraint (TST_APP1_USER.CUST_ZIP_FK) violated - parent key not found

-      However, I am able to add a customer with the zip_code  = 2 that was added in north_app_pdb and the customer record is visible even without commit.

NORTH_APP_PDB>insert into customers values ('1', 'NORTH Cust1', 'NORTH USA address', 2);
 
1 row created.
 
NORTH_APP_PDB>select * from customers;
 
CUST_ID CUST_NAME       CUST_ADD                    CUST_ZIP
------- --------------- -------------------------- ---------
      1 NORTH Cust1 NORTH USA address                  2

As can be seen, I encountered many issues with Oracle multi-tenant application containers. I would like to summarize them as follows:

• Although data for a metadata linked object is unique to the application root and each application PDB, data added in root is checked for constraints by the application PDB when attempting to sync with the application.

• The parent record in an extended data linked table (zip_codes) created in root is not recognized by the primary / foreign key constraints in application PDB. As a result,
  • Even after a record is successfully created in application root in a metadata linked table (customers) with a foreign key constraint referring to an extended data linked table (zip_codes), the application PDB fails to sync with the application.
  • It is not possible to add a record to the metadata linked table customer with a zip_code  (1) that was added in root – it fails as the parent key is not found and the foreign key constraint is violated.
  • In an extended data linked table having a record created in application root, it is possible to insert a record with the same primary key value as in the root on first attempt. However, a second attempt at it fails with primary key violation error.

• An application cannot be dropped even after it has been uninstalled. As a result, it is not possible to create another application with the same name in the same application container. To recreate the application with the same name, either another application container needs to be created or the entire existing application container has to be dropped and created again.

• Changes in the data as a result of insert/update/delete in an extended data linked table in an application PDB are not visible until committed.