Application Containers in Oracle 12c Database

Application Containers in Oracle Database 12c Release 2 (12.2)

An Application Containers is used for simplify the maintenance of the different application version. It easy to handle the different version of application changes in SQL with application container at database level.

Suppose one PDB1 database is working on Application version 10 and other PDB2 is want to work with latest version 12 of application. You can easily managed with application container feature of Oracle to sync the PDB2 with latest version 12 and donot sync the PDB1 database so it will work on old version of application.

An Application Container is composed by One Application Root having application metadata, link 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 the application master data which is minimum required to run the application particular version.
On new version released of application has new master data which is maintained in new version. Later they can sync the PDB as customer wants to move to new version of application.
There may be only one Application Root per Application Container.
 
Creating an Application Root Container

1. For create an Application Root you have to be connected with sysdba privileges user:

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show user
USER is "SYS"
-- Create the application root container
SQL> create pluggable database ROOTAPP as application container admin user sysappadmin identified by sysappadmin123;
Pluggable database created.
-- Open the application root container
SQL> alter pluggable database ROOTAPP open;
Pluggable database altered.
-- Check the application root container is created
SQL> select con_id, name , open_mode, application_root from v$pdbs where application_root='YES';
CON_ID NAME OPEN_MODE APP
------- -------- ---------- ---
5 ROOTAPP READ WRITE YES

Creating an Application Pluggable Database
Application pluggable database is created when you are connected with application root container. then created PDB in this is called 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 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 sales application
create user sales identified by password1 default tablespace sales_app_ts;

-- Grant permission
GRANT CREATE SESSION, CREATE TABLE TO sales;
-- Create 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 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 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;

For use the upgraded application in Pluggable database need 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 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: for removed from application pdb you need to again sync the pdb database

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s