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
- Application container– app_root
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_pdbwith tst_appapplication – 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_app1with two application PDBs – west_app_pdband 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.
Is this information still accurate? this 3 part series is very useful. Some oracle docs make it seem like the application master automatically syncs with the app PDB's. Im very new to app pdb's and just trying to understand it to see if its useful for our applications
Thanks
Dave