May 19, 2017 9:56:31 AM by Anju Garg
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:
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.
- Create an application tst_app in the application container app_root. While connected to app_root
- 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
- 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
- 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:
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 :
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/
We use cookies to improve your experience with our site. By continuing to use this site, you consent to our use of cookies. Learn more.