Oracle Multi-tenant Application Containers - Part III - Sharing of Data in Application Common Objects

Overview

In my last article, Oracle Multi-tenant Application Containers Part-II, we learnt how to create an application container comprising application root, application seed PDB, and application PDBs. We installed a simple application having a common user and a metadata-linked table in the application root. In this article, I will discuss various types of application common objects and how data in these objects can be shared among containers in an application container.

Application Common Objects

An application common object is a common object created in the application root of an application container and shared with the application PDBs within the container.

The sharing attribute for these objects can have one of the following values:

• Metadata (Default): Application PDBs share, by means of a metadata link, only the objects’ metadata that is stored in application root. The data for the object is unique to the application root and to each application PDB that belongs to the application root. These database objects are referred to as metadata-linked application common objects. For example, a company can use a metadata-linked table to store customer information so that application PDBs for various regional offices can use same data structure while storing region-specific unique customer data.
• Data: Application PDBs share, by means of a data link, both the metadata and the data for the object stored in the application root. These database objects are referred to as data-linked application common objects. For example, a company can use a data-linked table to store information about products manufactured by it so that the product information is consistent across various application PDBs for regional offices.
• Extended Data: Application PDBs share, by means of an extended data link, both the metadata and the data for the object stored in the application root. However, each application PDB in the application container can create its own specific data as well. Therefore, only the data stored in the application root is common for all application PDBs. These database objects are referred to as extended data-linked application common objects. For example, sales application of a company having application PDBs for its various regional offices can use an extended data-linked table to store information about customers’ zip codes.

-       The zip codes of countries having customers serviced by all the regional offices can be stored in the application root so that all the application PDBs can access them.

-       The zip codes of countries having customers serviced by specific regional offices can only be stored in the extended data-linked object in the corresponding application PDB.

• None: The database object is not shared and can be accessed only in the application root.

The valid values for the SHARING attribute for the different types of common application database objects which can be specified are:

 For a metadata-linked sequence, although metadata for sequence (e.g., starting value, increment etc.) are same for each application PDB in the application container, the value of the sequence is specific to each application PDB. Incrementing such a sequence using NEXTVAL in one application PDB does not affect its value in the other application PDBs in the application container.

For a data-linked sequence, sequence metadata as well as value are shared by all the application PDBs in the application root. Incrementing such a sequence using NEXTVAL in one application PDB causes its value to be incremented in the other application PDBs as well.

Creating Application Common Objects

Application common objects can be created in an application root as part of an application installation, upgrade, or patch. The sharing attribute for an object can be specified by

• Setting the DEFAULT_SHARING initialization parameter to METADATA, DATA, or EXTENDED DATA in the application root
• Including the SHARING clause set to METADATA, DATA, or  EXTENDED DATA in the CREATE SQL statement.

Changes to application common objects become visible to an application PDB when it synchronizes with the application in the application root.

Current Scenario

Here, we have an Oracle database 12.2.0.1 CDB called orclcdb as shown. Within this CDB, we have created an application container sales_app, for sales application, whose common objects can be shared by four application PDBs: North_app_pdb, South_app_pdb, East_app_pdb, and West_app_pdb. The application common objects for sales_app are stored in application root sales_app_root. Also, we have an application seed PDB called sales_app_root$seed.

I will demonstrate:

• Application upgrade
• DML on common application objects
• Data sharing for
  • Metadata linked objects
  • Data linked objects
  • Extended data linked objects

Demonstration

Let us first connect to CDB orclcdb and verify that there is one application container root sales_app_root in this CDB.

SQL> conn sys/oracle@orclcdb as sysdba
     set sqlprompt CDB$ROOT>
     sho con_name
 
CON_NAME
------------------------------
CDB$ROOT
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
---------- --------------- ---------- -------- -------- --------
         3 SALES_APP_ROOT  READ WRITE YES      NO       NO

Connect to application root sales_app_root and note that there are four application PDBs (north_app_pdb, east_app_pdb, west_app_pdb, south_app_pdb) and one seed PDB (sales_app_root$seed) associated with it.

SQL> conn sys/oracle@host01:1522/sales_app_root as sysdba
     set sqlprompt  SALES_APP_ROOT>
 
SALES_APP_ROOT>sho con_name
 
CON_NAME
------------------------------
SALES_APP_ROOT
 
 
SALES_APP_ROOT>@get_app_containers
 
    CON_ID NAME                 OPEN_MODE  APP_ROOT APP_PDB  APP_SEED
---------- -------------------- ---------- -------- -------- --------
         3 SALES_APP_ROOT       READ WRITE YES      NO      NO
         4 NORTH_APP_PDB        READ WRITE NO       YES      NO
         5 SOUTH_APP_PDB        READ WRITE NO       YES      NO
         6 WEST_APP_PDB         READ WRITE NO       YES      NO
         8 EAST_APP_PDB         READ WRITE NO       YES      NO
         9 SALES_APP_ROOT$SEED  READ WRITE NO       YES      YES
 
6 rows selected.

-- Besides an implicit application, another application sales_app is currently installed in this container. All the application PDBs and seed PDB are synced with application sales_app version 1.0.

SALES_APP_ROOT>@get_app_status
 
APP_NAME                              APP_VERSION  APP_ID APP_STATUS IMPLICIT
------------------------------------- ------------ ------ ---------- --------
APP$5313F8AEAFA337B0E05364C909C08D65   1.0             2 NORMAL         Y
SALES_APP                              1.0             3 NORMAL     N
 
SALES_APP_ROOT>@get_sales_app_pdb_status
 
NAME                APP_NAME             APP_VERSION APP_STATUS
-------------------- -------------------- ----------- ------------
NORTH_APP_PDB          SALES_APP            1.0       NORMAL
WEST_APP_PDB           SALES_APP            1.0      NORMAL
SALES_APP_ROOT$SEED    SALES_APP            1.0       NORMAL   
EAST_APP_PDB           SALES_APP            1.0       NORMAL
SOUTH_APP_PDB          SALES_APP            1.0       NORMAL

Let us check the common objects in application sales_app currently. There is a metadata linked common application table sales_app_user.customers with an index corresponding to primary key on column cust_id.

SALES_APP_ROOT>@get_sales_app_objects
 
APP_NAME   OWNER           OBJECT_NAME  OBJECT_TYPE SHARING           APPLICATION
---------- --------------- ------------ ----------- ------------ ------------
SALES_APP  SALES_APP_USER  CUSTOMERS    TABLE      METADATA LINK      Y
SALES_APP  SALES_APP_USER  CUST_PK      INDEX      NONE                 Y
 
SALES_APP_ROOT>desc sales_app_user.customers
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------------
 CUST_ID                                  NOT NULL NUMBER
 CUST_NAME                                          VARCHAR2(30)
 CUST_ADD                                           VARCHAR2(30)
 CUST_ZIP                                           NUMBER
 
SALES_APP_ROOT>select con.owner, con.constraint_name, con.constraint_type,
                      con.table_name, con_col.column_name
              from dba_constraints con, dba_cons_columns con_col
              where con.constraint_name = 'CUST_PK'
                and con_col.constraint_name = 'CUST_PK';
 
 
OWNER           CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME COLUMN_NAME
--------------- --------------- --------------- ---------- ---------------
SALES_APP_USER  CUST_PK         P               CUSTOMERS  CUST_ID

Now we will create and populate two more tables to the application sales_app by upgrading the application to version 2.0. We will issue ALTER PLUGGABLE DATABASE APPLICATION statements to upgrade an application in the application root.

-- Connected to the application root, begin upgrade of sales_app application to version 2.0

SALES_APP_ROOT>ALTER PLUGGABLE DATABASE APPLICATION sales_app BEGIN UPGRADE '1.0' TO '2.0';
 Pluggable database altered.

-- Note that status of application sales_app becomes UPGRADING

SALES_APP_ROOT>SALES_APP_ROOT>@get_sales_app_status
 
APP_NAME                       APP_VERSION  APP_ID APP_STATUS   IMPLICIT
------------------------------ ------------ ------ ------------ --------
SALES_APP                      1.0              3 UPGRADING    N

-- Create and populate extended data linked table zip_codes 

SALES_APP_ROOT> create table SALES_APP_USER.zip_codes
               sharing=extended data
                  (zip_code number ,
                  country varchar2(20)); 
 
                insert into  sales_app_user.zip_codes values
          (1, 'India(root)');
 
                commit;

-- Create and populate data linked table products  

SALES_APP_ROOT> create table SALES_APP_USER.products
                sharing=  data
                  (prod_id number,
                   prod_name varchar2(20),
                   price number);
 
               insert into   SALES_APP_USER.products values
                  (1, 'prod1 (root)', 111);
 
                commit;

-- End upgrade of sales_app application to version 2.0

SALES_APP_ROOT>ALTER PLUGGABLE DATABASE APPLICATION sales_app END UPGRADE  TO '2.0';
   
Pluggable database altered.
 
-- Status of sales_app returns to normal from upgrading
SALES_APP_ROOT>@get_sales_app_status
 
APP_NAME                APP_VERSION APP_ID APP_STATUS   IMPLICIT
---------------------- ------------ ------ ------------ --------
SALES_APP                       2.0      3 NORMAL       N

Note that on application upgrade, a read-only clone of the application root gets created automatically. Application root clone is used:

• During the application upgrade: By application PDBs to query and perform DML operations on pre-upgrade common application objects so that applications continue to run during the upgrade operation.
• After the application upgrade: To support application PDBs that have not synced with the upgraded application.  Such PDBs continue to use the clone.

CDB$ROOT>select NAME, OPEN_MODE, APPLICATION_ROOT app_root,  
                APPLICATION_ROOT_CLONE App_root_clone, CREATION_TIME DT
          from v$pdbs
          where application_root = 'YES'
          order by dt;
NAME                OPEN_MODE  APP_ROOT APP_ROOT_CLONE DT
-------------------- ---------- -------- -------------- ---------
SALES_APP_ROOT       READ WRITE YES      NO             29-JUN-17
F2775271524_3_1      READ ONLY  YES     YES            13-JUL-17

Due to creation of application root clone, individual Application PDBs can choose to synchronize with upgraded application on their own schedule. As a result, the impact of application upgrade is restricted merely to the tenants being upgraded and a business-wide upgrade can be performed in a phased manner, leading to a much more agile application development process.

Let us Synchronize the application seed PDB and all the application PDBs except east_app_pdb with the upgraded application sales_app.

SALES_APP_ROOT>conn sys/oracle@host01:1522/sales_app_root$seed as sysdba
               alter pluggable database application sales_app sync;
               alter pluggable database close immediate;
               alter pluggable database open read only;
 
               conn sys/oracle@host01:1522/west_app_pdb as sysdba
               alter pluggable database application sales_app sync;
 
               conn sys/oracle@host01:1522/north_app_pdb as sysdba
               alter pluggable database application sales_app sync;
 
               conn sys/oracle@host01:1522/south_app_pdb as sysdba
               alter pluggable database application sales_app sync;
 
               conn sys/oracle@host01:1522/sales_app_root  as sysdba
 
SALES_APP_ROOT>@get_app_pdb_status
 
NAME                   CON_UID    APP_NAME             APP_VERSIO APP_STATUS
--------------------   ---------- -------------------- ---------- ------------
EAST_APP_PDB           3152762628 SALES_APP            1.0            NORMAL
SOUTH_APP_PDB          2695036509 SALES_APP            2.0            NORMAL
NORTH_APP_PDB          2659474630 SALES_APP            2.0            NORMAL
WEST_APP_PDB           2183572329 SALES_APP            2.0            NORMAL
SALES_APP_ROOT$SEED    71846825   SALES_APP            2.0            NORMAL

Since application PDB east_app_pdb has not synced with the upgraded application sales_app, it points to the application root clone, and tables created as part of the upgrade (zip_codes, products) are not visible to it.

SQL> conn sys/oracle@host01:1522/east_app_pdb as sysdba
     set sqlprompt EAST_APP_PDB>
 
EAST_APP_PDB>select object_name, object_type, sharing
             from dba_objects
             where owner = 'SALES_APP_USER'
             order by object_type, object_name;   
 
OBJECT_NAME              OBJECT_TYPE             SHARING
------------------------------ ----------------------- ------------------
CUST_PK                  INDEX                   NONE
CUSTOMERS                TABLE                   METADATA LINK
 
EAST_APP_PDB>desc sales_app_user.zip_codes
ERROR:
ORA-04043: object sales_app_user.zip_codes does
not exist
 
EAST_APP_PDB>desc sales_app_user.products
ERROR:
ORA-04043: object sales_app_user.products does
not exist

Now, we will connect to the synced application PDB north_app_pdb as application common user sales_app_user and verify that it can access the common application objects created in root as part of the application upgrade. Records added to the extended data-linked table zip_codes and the data-linked table products in the application root are visible to the application PDB north_app_pdb.

SQL>conn sales_app_user/oracle@host01:1522/north_app_pdb 
    set sqlprompt 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
CUSTOMERS                TABLE             METADATA LINK
PRODUCTS                 TABLE             DATA LINK
ZIP_CODES                TABLE             EXTENDED DATA LINK
 
NORTH_APP_PDB>select * from sales_app_user.zip_codes;
 
  ZIP_CODE COUNTRY
---------- --------------------
       1 India(root)
 
NORTH_APP_PDB>select * from sales_app_user.products;
 
   PROD_ID PROD_NAME               PRICE
---------- -------------------- ----------
       1 prod1 (root)          111

Now, we will issue DML statements on common application tables from the synced application PDB north_app_pdb. It is worth mentioning here that DML statements need to be committed for the changes to be visible.

-- Try to add a record to the table products - fails as products is a data-linked table and DML statements on such objects can be issued only in the application root.

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

-- Add another record to the table zip_codes - it succeeds as zip_codes is an extended data-linked table and for such objects, each application PDB can create its own specific data while sharing the common data in the application root.

NORTH_APP_PDB>insert into zip_codes values (2, 'USA (north)');
             commit;
             select * from zip_codes;
 
  ZIP_CODE COUNTRY
---------- --------------------
       1 India(root)
       2 USA (north)

-- Add another record to table customers - it succeeds as customers is a metadata-linked table and for such objects, each container can create its own specific data.

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

-- Try to add duplicate record for CUST_ID = 1 - fails as CUST_ID is the primary key

NORTH_APP_PDB>insert into customers values ('1', 'Another Cust1(north)', 'USA (north) address', 2);
 
insert into customers values ('1', 'Another Cust1(north)', 'USA (north) address', 2)
*
ERROR at line 1:
ORA-00001: unique constraint (SALES_APP_USER.CUST_PK) violated

Besides issuing DML statements on common application objects, each application PDB can create its own local tables as well.  Let us create and populate a table local_tbl in the application PDB north_app_pdb.

NORTH_APP_PDB>create table local_tbl(id number);
             insert into local_tbl values (1);
             commit;
 
             select * from local_tbl;
      ID
----------
       1

Now we will connect to another synced application PDB, south_app_pdb, as common application user sales_app_user to verify that data is appropriately shared / isolated across various application PDBs within the same container.

SQL> conn sales_app_user/oracle@host01:1522/south_app_pdb 
     set sqlprompt SOUTH_APP_PDB>
 
SOUTH_APP_PDB>

-- Verify that a record added to the metadata-linked table customers and extended data-linked table zip_codes by north_app_pdb are not visible in south_app_pdb. However, records added to the tables zip_codes and products in the application root is visible.

SOUTH_APP_PDB>select * from zip_codes;
 
  ZIP_CODE COUNTRY
---------- --------------------
       1 India(root)
 
SOUTH_APP_PDB>select * from customers;
 
no rows selected
 
SOUTH_APP_PDB>select * from products;
 
   PROD_ID PROD_NAME                 PRICE
---------- -------------------- ----------
         1 prod1 (root)                111

For metadata-linked and extended data-linked tables, each application PDB in the application container can create its own specific data.

-- We can create a zip code (=2) which is the same as that created in north_app_pdb but specific to this container

SOUTH_APP_PDB>insert into zip_codes values (2, 'USA (south)');
              commit;
              select * from zip_codes;
 
  ZIP_CODE COUNTRY
---------- --------------------
       1 India(root)
       2 USA (south)

-- Also, we can create a customer with the customer ID (=1) which is same as that created in north_app_pdb but specific to this container

SOUTH_APP_PDB>insert into customers
              values ('1', 'Cust1(south)', 'USA (south) address', 2);
              commit;
              select * from customers;
 
   CUST_ID CUST_NAME           CUST_ADD                         CUST_ZIP
---------- -------------------- ------------------------------ ----------
         1 Cust1(south)         USA (south) address                     2

The local table local_tbl created in the  application PDB north_app_pdb is not visible in south_app_pdb and we can even create a table with the same name in south_app_pdb that is specific to it.

SOUTH_APP_PDB>select * from local_tbl;
select * from local_tbl
              *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SOUTH_APP_PDB>create table local_tbl(id number);
            insert into local_tbl values (2);
            commit;
 
           select * from local_tbl;
 
      ID
----------
       2

Summary

• Application common objects are user-created database objects in an application container. They are created in the application root and shared with the application PDBs that belong to the application root.
• There are three types of application common objects:
  • Metadata-linked common objects:  Application PDBs share only the metadata, but contain different sets of data.
  • Data-linked common objects:  Application PDBs share a single set of data in the application root. 
  • Extended data-linked common objects:  Application PDBs share the data in the application root.  However, each application PDB can store its own specific data as well.
  • On application upgrade, a read-only clone of the application root gets created automatically.

References:

https://docs.oracle.com/database/122/CNCPT/overview-of-the-multitenant-architecture.htm#CNCPT-GUID-9900D18F-989B-4746-A5CE-04A8D9F9751C

https://docs.oracle.com/database/122/ADMIN/administering-application-containers-with-sql-plus.htm#ADMIN-GUID-DC57BCFA-8DC5-46B6-809F-E3570F44D801

Scripts used in this article:

get_app_containers.sql

-- Find out the containers in an application root when executed from application 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; 

get_app_pdb_status.sql

-- Find out the available versions of application sales_app with which various application / seed PDBs are in sync currently

SELECT c.name,  aps.con_uid,   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 = 'SALES_APP';

get_app_status.sql

-- Find out the status of various applications in an application container when executed from an application root

select app_name, app_version, app_id, app_status, app_implicit implicit from dba_applications;

get_sales_app_pdb_status

-- Find out various versions of application SALES_APP and synchronization status of various application PDBs with it. Execute from application 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 = 'SALES_APP';

get_sales_app_objects

-- Find out objects belonging to sales_app application.

      select app.app_name, obj.owner, obj.object_name, obj.object_type,
               obj.sharing, obj.application
      from dba_objects obj, dba_applications app
      where obj.owner in
          (select username from dba_users
           where oracle_maintained = 'N')
          and obj.application = 'Y'
      and obj.created_appid = app.app_id;

get_sales_app_status

-- Find out the status of sales_app application in application container when executed from application root

          select app_name, app_version, app_id, app_status,
                app_implicit implicit
         from dba_applications
         where app_name = 'SALES_APP';
 
-------------------------------------------------------------