Toad World Blog
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:
|
SHARING clause values |
Types of database objects |
|
METADATA, DATA, EXTENDED DATA, NONE |
|
|
|
|
|
|
|
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_SHARINGinitialization 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:
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';
-------------------------------------------------------------
Tags: Oracle
Written by Anju Garg
Anju Garg is an Oracle Ace with over 14 years of experience in IT Industry in various roles. Since 2010, she has been involved in teaching and has trained more than a hundred DBAs from across the world in various core DBA technologies like RAC, Data guard, Performance Tuning, SQL statement tuning, Database Administration etc.
She is a regular speaker at Sangam and OTNYathra. She writes articles about Oracle and is one of the reviewers of the following book published by Pearson Oracle Problem-Solving and Troubleshooting Handbook
She is certified for :
- Oracle 9i Database Administration OCP
- Oracle 11g Database Administration OCP
- Oracle 11g Performance Tuning OCE
- Oracle 11g R2 RAC OCE
- Oracle 11g SQL Tuning OCE
- Oracle 12c Database Administration OCP
- Oracle Real Application Clusters 12c Certified Implementation Specialist
She is passionate about learning and has keen interest in RAC and Performance Tuning. She shares her knowledge via her technical blog at http://oracleinaction.com/