Application Containers in Oracle Database

Application Containers are used to simplify the maintenance of the different application versions. It is easy to handle the different versions of application changes in SQL with an application container at the database level.

Suppose one PDB1 database is working on Application version 10 and another PDB2 wants to work with the latest version 12 of the application. You can easily manage with the application container feature of Oracle to sync the PDB2 with the latest version 12 and do not sync the PDB1 database so it will work on the old version of the application.

An Application Container is composed of One Application Root having application metadata, linked with zero or more Application Pluggable Databases,
zero or one Application Seed and zero or more Applications.

An Application Root is a Pluggable Database where the “Applications” are installed and maintained by the application master data which is the minimum required to run the application’s particular version.
On new version of the application has new master data which is maintained in the new version. Later they can sync the PDB as the customer wants to move to a new version of the application.
There may be only one Application Root per Application Container.

Creating an Application Root Container

1. To create an Application Root you have to be connected with SYSDBA privileges user:

Creating an Application Pluggable Database

Application pluggable database is created when you are connected with the application root container. then created PDB which is called an application pluggable database.

SQL> alter session set container=ROOTAPP;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
ROOTAPP

-- Create a pluggable database

SQL> create pluggable database PDB1APP admin user pdb1appadmin identified by pdb1appadmin123;
Pluggable database created.

--Opening the Application PDB

SQL> alter pluggable database PDB1APP open;
Pluggable database altered.

--Check the Application PDBs were created
SQL> select con_id, name , open_mode, application_root, application_pdb from v$pdbs;
CON_ID NAME OPEN_MODE APPLICATION_ROOT APPLICATION_PDB
-------- -------- ---------- ---------------- ---------------
5 ROOTAPP READ WRITE YES NO
6 PDB1APP READ WRITE NO YES

Create an application in Root Container

-- Check the current container
SQL> show con_name
CON_NAME
---------------
APPROOT

--Create a sales application on the app container
alter pluggable database application SalesApp begin install '1.0';
Pluggable database altered.

-- Create a separate tablespace for application sales
CREATE TABLESPACE sales_app_ts DATAFILE 'location' SIZE 100M AUTOEXTEND ON NEXT 10M;
Tablespace created.

-- Create user for the sales application
create user sales identified by password1 default tablespace sales_app_ts;

-- Grant permission
GRANT CREATE SESSION, CREATE TABLE TO sales;
-- Create a table for version 1.0
create table sales.tran SHARING=DATA (
id NUMBER,
name varchar2(100),
description VARCHAR2(100),
CONSTRAINT id_pk PRIMARY KEY (id)
);

-- insert data into the tran table
Insert into sales.tran values (1, 'shoes', ' Black color');

-- End the salesapp process
alter pluggable database application SalesApp end install;

-- Check the application is created
COLUMN app_name FORMAT A10
COLUMN app_version FORMAT A10
SELECT app_name,
app_version,
app_status
FROM dba_applications
WHERE app_name = 'SALESAPP';
APP_NAME APP_VERSIO APP_STATUS
---------- ---------- ------------
SALESAPP 1.0 NORMAL

SYNC the PDB database with an application created

-- Switch to the application pdb database
ALTER SESSION SET container = PDB1APP;
SHOW CON_NAME
CON_NAME
----------------

PDB1APP- try to check the salesapp object
SQL> select * from sales.tran;
select * from sales.tran
*
ERROR at line 1:
ORA-00942: table or view does not exist

-- Sync the pdb with application root container
SQL> alter pluggable database application SALESAPP sync;
Pluggable database altered.

-- Again select the table
SQL> select name from sales.tran;
NAME
-------
SHOES

-- you can check the status from DBA_APP_PDB_STATUS view
ALTER SESSION SET container = PDB1APP;

COLUMN name FORMAT A20
COLUMN app_name FORMAT A20
COLUMN app_version FORMAT A10
SELECT con_uid,
app_name,
app_version,
app_status
FROM dba_app_pdb_status WHERE app_name = 'SALESAPP';

CON_UID    APP_NAME    APP_VERSIO APP_STATUS
---------- ----------- ---------- ------------
4291055883 SALESAPP    1.0        NORMAL

Upgrade the application present in the application root container

-- connect with root container for modify the application
ALTER SESSION SET container = APPROOT;

-- Fire the command for upgrade the application
ALTER PLUGGABLE DATABASE APPLICATION SALESAPP BEGIN UPGRADE '1.0' TO '2.0';

-- Suppose create one more table
create table sales.region ( name varchar(100));

-- insert data into region table
insert into sales.region ('NORTH');

-- end the upgrade process
ALTER PLUGGABLE DATABASE APPLICATION SALESAPP END UPGRADE;

Use the upgraded application in the Pluggable database to sync the PDB database again

-- Connect with pdb of app container

alter session set container=PDB1APP;

-- sync with upgraded version
ALTER PLUGGABLE DATABASE APPLICATION SALESAPP SYNC;

Uninstall the application from the root application container

— first start the uninstall command
ALTER PLUGGABLE DATABASE APPLICATION SALESAPP BEGIN UNINSTALL;

— Drop the created user
drop user sales cascade;

— Drop the tablespace assoicated with user
Drop tablespace INCLUDING CONTENTS AND DATAFILES;

— End the uninstall command:
ALTER PLUGGABLE DATABASE APPLICATION SALESAPP END UNINSTALL;

Note: To remove from the application PDB you need to again sync the PDB database