Application containers are new concept from 12.2 and they allow us to group PDB based on their purpose. Furthermore, we can treat these PDB as one whole in the way that we can partition among them, even if they are remote and thus increase the performance:

From 12.2 onward we are allowed to have a Proxy PDB, Application Root Container and a single user-defined PDB (regular or Application PDB) inside a single CDB without having to pay for the Multitenant Option. Notice we are still limited to a single user-defined PDB.

The management of an application root container is very similar to the management of a single PDB.

The operations of application PDBs and application containers are the same as normal PDBs:

• Creation
• Deletion
• Alteration

To create application root container, just create a normal PDB, but AS application root

CONN / AS SYSDBA

CREATE PLUGGABLE DATABASE appcon1 AS APPLICATION CONTAINER ADMIN USER app_admin IDENTIFIED BY Password1;
ALTER PLUGGABLE DATABASE appcon1 OPEN;
<

To delete a application container, use the following command:

CONN / AS SYSDBA

ALTER PLUGGABLE DATABASE appcon1 CLOSE;
DROP PLUGGABLE DATABASE appcon1 INCLUDING DATAFILES;

To create an application PDB, we can use the same syntax as normal PDB, just we need to change the CDB :)

CONN / AS SYSDBA
ALTER SESSION SET container = appcon1;
CREATE PLUGGABLE DATABASE apppdb1 ADMIN USER pdb_admin IDENTIFIED BY Password1;
ALTER PLUGGABLE DATABASE apppdb1 OPEN;

To drop an application PDB, we can use the same command as a normal PDB

CONN / AS SYSDBA

ALTER PLUGGABLE DATABASE appcon1 CLOSE;
DROP PLUGGABLE DATABASE appcon1 INCLUDING DATAFILES;

Application containers and application roots are mostly useful when we install and deal with applications. But what is application in a DB term.

Within an application container, an application is the named, versioned set of common data and metadata stored in the application root. In this context of an application container, the term “application” means “master application definition.” For example, the application might include definitions of tables, views, and packages. For example, you might create multiple sales-related PDBs within one application container, with these PDBs sharing an application that consists of a set of common tables and table definitions. You might store multiple HR-related PDBs within a separate application container, with their own common tables and table definitions.

To have an application, firstly we need to install an application.

Firstly we need to create an application PDB, you know already the steps from above so I will make them shorter :)

#Create application root
CONN / AS SYSDBA

CREATE PLUGGABLE DATABASE appcon1 AS APPLICATION CONTAINER ADMIN USER app_admin IDENTIFIED BY Password1;
ALTER PLUGGABLE DATABASE appcon1 OPEN;

#Change the CDB to the newly created container
CONN / AS SYSDBA
ALTER SESSION SET container = appcon1;

#Create one or more application PDB (repeat if needed)
CREATE PLUGGABLE DATABASE apppdb1 ADMIN USER pdb_admin IDENTIFIED BY Password1;
ALTER PLUGGABLE DATABASE apppdb1 OPEN;

#Sync them all (repeat if needed)
ALTER SESSION SET container = apppdb1;
ALTER PLUGGABLE DATABASE APPLICATION ALL SYNC;

To install an application, we can use the following process:

#Change to the application root
CONN / AS SYSDBA
ALTER SESSION SET container = appcon1;

#Install the application
ALTER PLUGGABLE DATABASE APPLICATION ref_app BEGIN INSTALL '1.0';

Now we can start configuring all that is needed for that application:

CREATE TABLESPACE ref_app_ts DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE USER ref_app_user IDENTIFIED BY ref_app_user
  DEFAULT TABLESPACE ref_app_ts
  QUOTA UNLIMITED ON ref_app_ts
  CONTAINER=ALL;

GRANT CREATE SESSION, CREATE TABLE TO ref_app_user;

CREATE TABLE ref_app_user.reference_data SHARING=DATA (
  id          NUMBER,
  description VARCHAR2(50),
  CONSTRAINT t1_pk PRIMARY KEY (id)
);

INSERT INTO ref_app_user.reference_data
SELECT level,
       'Description of ' || level
FROM   dual
CONNECT by level <= 5;
COMMIT;

To end the installation we simply have to do the following command:

ALTER PLUGGABLE DATABASE APPLICATION ref_app END INSTALL;
COLUMN app_name FORMAT A20
COLUMN app_version FORMAT A10

SELECT app_name,
       app_version,
       app_status
FROM   dba_applications
WHERE  app_name = 'REF_APP';

APP_NAME             APP_VERSIO APP_STATUS
-------------------- ---------- ------------
REF_APP              1.0        NORMAL

SQL>

Bare in mind, at this point the PDBs, don't know about that installation, so if we try to see it from their point of view, it doesn't exist:

-- Connect to application container.
CONN / AS SYSDBA
ALTER SESSION SET container = apppdb1;

SHOW CON_NAME

CON_NAME
------------------------------
APPPDB1
SQL>


-- Check for presence of application objects.
DESC ref_app_user.reference_data;
ERROR:
ORA-04043: object ref_app_user.reference_data does not exist

SQL>

To sync the PDBs with the application container, just execute the following:

-- Sync the application with the application root.
ALTER PLUGGABLE DATABASE APPLICATION ref_app SYNC;
--ALTER PLUGGABLE DATABASE APPLICATION ALL SYNC;


-- Check for presence of application objects.
DESC ref_app_user.reference_data;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 DESCRIPTION                                        VARCHAR2(50)

SQL>

We can now verify that the application is visible from the PDBs

CONN / AS SYSDBA
ALTER SESSION SET container = appcon1;

COLUMN name FORMAT A20
COLUMN app_name FORMAT A20
COLUMN app_version FORMAT A10

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 = 'REF_APP';

NAME                    CON_UID APP_NAME             APP_VERSIO APP_STATUS
-------------------- ---------- -------------------- ---------- ------------
APPPDB1              4291055883 REF_APP              1.0        NORMAL

SQL>

The upgrade in that case is the modification of the existing structure, either be: index creation / deletion, new column, etc. To upgrade an application, the process is very similiar:

#Connect to the application root
CONN / AS SYSDBA
ALTER SESSION SET container = appcon1;

#Edit the tag
ALTER PLUGGABLE DATABASE APPLICATION ref_app BEGIN UPGRADE '1.0' TO '1.1';

#Do the changes
ALTER TABLE ref_app_user.reference_data ADD (
  created_date DATE DEFAULT SYSDATE
);

CREATE OR REPLACE FUNCTION ref_app_user.get_ref_desc (p_id IN reference_data.id%TYPE)
  RETURN reference_data.description%TYPE
AS
  l_desc reference_data.description%TYPE;
BEGIN
  SELECT description
  INTO   l_desc
  FROM   reference_data
  WHERE  id = p_id;

  RETURN l_desc;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
/

GRANT EXECUTE ON ref_app_user.get_ref_desc TO PUBLIC;

#End upgrade
ALTER PLUGGABLE DATABASE APPLICATION ref_app END UPGRADE;

We have to, of course, still sync with the PDBs, otherwise it won't be visible:

-- Connect to application container.
CONN / AS SYSDBA
ALTER SESSION SET container = apppdb1;

SHOW CON_NAME

CON_NAME
------------------------------
APPPDB1
SQL>


-- Check for presence of application objects.
DESC ref_app_user.reference_data;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 DESCRIPTION                                        VARCHAR2(50)

SQL>


SELECT ref_app_user.get_ref_desc(1) FROM dual;
SELECT ref_app_user.get_ref_desc(1) FROM dual
       *
ERROR at line 1:
ORA-00904: "REF_APP_USER"."GET_REF_DESC": invalid identifier

SQL>


-- Sync the application with the application root.
ALTER PLUGGABLE DATABASE APPLICATION ref_app SYNC;
--ALTER PLUGGABLE DATABASE APPLICATION ALL SYNC;


-- Check for presence of application objects.
DESC ref_app_user.reference_data;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 DESCRIPTION                                        VARCHAR2(50)
 CREATED_DATE                                       DATE

SQL>


SELECT ref_app_user.get_ref_desc(1) FROM dual;

REF_APP_USER.GET_REF_DESC(1)
--------------------------------------------------------------------------------
Description of 1

SQL>

We can again verify the version as follows:

CONN / AS SYSDBA
ALTER SESSION SET container = appcon1;

COLUMN name FORMAT A20
COLUMN app_name FORMAT A20
COLUMN app_version FORMAT A10

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 = 'REF_APP';

NAME                    CON_UID APP_NAME             APP_VERSIO APP_STATUS
-------------------- ---------- -------------------- ---------- ------------
APPPDB1              4291055883 REF_APP              1.1        NORMAL

SQL>

To uninstall an application, we have to:

1. Uninstall it
2. Delete all the data
3. Sync (again :D )

So let's do it:

#Connect to the application root
CONN / AS SYSDBA
ALTER SESSION SET container = appcon1;

#Star the uninstall
ALTER PLUGGABLE DATABASE APPLICATION ref_app BEGIN UNINSTALL;

#Delete all the data
DROP USER ref_app_user CASCADE;
DROP TABLESPACE ref_app_ts INCLUDING CONTENTS AND DATAFILES;

#End the uninstallation
ALTER PLUGGABLE DATABASE APPLICATION ref_app END UNINSTALL;

Done, we have uninstalled and deleted the data, but we still have to SYNC the changes:

-- Connect to application container.
CONN / AS SYSDBA
ALTER SESSION SET container = apppdb1;

SHOW CON_NAME

CON_NAME
------------------------------
APPPDB1
SQL>


-- Check for presence of application objects.
DESC ref_app_user.reference_data;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 DESCRIPTION                                        VARCHAR2(50)
 CREATED_DATE                                       DATE

SQL>

SELECT ref_app_user.get_ref_desc(1) FROM dual;

REF_APP_USER.GET_REF_DESC(1)
--------------------------------------------------------------------------------
Description of 1

SQL>


-- Sync the application with the application root.
ALTER PLUGGABLE DATABASE APPLICATION ref_app SYNC;
--ALTER PLUGGABLE DATABASE APPLICATION ALL SYNC;


-- Check for presence of application objects.
DESC ref_app_user.reference_data;
ERROR:
ORA-04043: object ref_app_user.reference_data does not exist

SQL>

SELECT ref_app_user.get_ref_desc(1) FROM dual;
SELECT ref_app_user.get_ref_desc(1) FROM dual
       *
ERROR at line 1:
ORA-00904: "REF_APP_USER"."GET_REF_DESC": invalid identifier

SQL>

After that, we can verify that the application has been uninstalled:

CONN / AS SYSDBA
ALTER SESSION SET container = appcon1;

COLUMN name FORMAT A20
COLUMN app_name FORMAT A20
COLUMN app_version FORMAT A10

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 = 'REF_APP';

NAME                    CON_UID APP_NAME             APP_VERSIO APP_STATUS
-------------------- ---------- -------------------- ---------- ------------
APPPDB1              4291055883 REF_APP              1.1        UNINSTALLED

SQL>

Don't forget that any modification of the application PDB, has to be done via either:

• Installation
• Upgrade
• Patching

From Oracle 12.2, we can clone the entire application container as well.

In order to put objects into the application container, they have to be partition. The way we partition such objects is called: Container map:

So let's see how it is done on table level.

Firstly we have to create several databases to store the database, alternatively, we can add proxy PDBs on different servers across the globe.

#Create application root
SQL> create pluggable database appcdb as application container admin user pdbadmin identified by Pass123; 
Pluggable database created.
SQL> alter pluggable database appcdb open;
Pluggable database altered.
 
#In order to create “Application PDB” you must be connected to the “Application Root”:
SQL> alter session set container=appcdb;
Session altered.
SQL> show con_name
CON_NAME
-----------------------------
APPCDB

#Create all PDBs
SQL> create pluggable database north admin user app1admin identified by Pass123;
Pluggable database created.

SQL> create pluggable database central admin user app1admin identified by Pass123;
Pluggable database created.

SQL> create pluggable database south admin user app1admin identified by Pass123;
Pluggable database created.

#Open all PDBs
SQL> alter pluggable database all open;
Pluggable database altered.

After we have created the container and the PDBs, we can create the container MAP

#Create the map
SQL> CREATE TABLE c##andonovj.containermap (
country VARCHAR2(30) NOT NULL)
PARTITION BY LIST (country) (
PARTITION north VALUES ('CANADA','USA'),
PARTITION central VALUES ('GUATEMALA','NICARAGUA'),
PARTITION south VALUES ('ARGENTINA','BRAZIL'));
Table created.

#Set the map for the container
SQL> ALTER PLUGGABLE DATABASE SET CONTAINER_MAP='C##ANDONOVJ.CONTAINERMAP'; 
Pluggable database altered.

Now we can start all installation

#Start installation
SQL> ALTER PLUGGABLE DATABASE APPLICATION Application_JULIEN BEGIN INSTALL '1.0'; 
Pluggable database altered.

SQL> CREATE TABLE c##andonovj.revenue (
country VARCHAR2(30),
revenue number);
Table created.

#Enable container MAP
SQL> ALTER TABLE c##andonovj.revenue ENABLE CONTAINER_MAP;
Table altered.
SQL>  ALTER TABLE c##andonovj.revenue ENABLE CONTAINERS_DEFAULT;
Table altered.

#End installation
SQL> ALTER PLUGGABLE DATABASE APPLICATION Application_JULIEN END INSTALL '1.0';
Pluggable database altered.

We can verify if container map is used for a specific table as follows:

SQL> select owner, table_name, CONTAINER_MAP from dba_tables where table_name='REVENUE';
OWNER      TABLE_NAME CONTAINER_MAP
---------- ---------- ---------------
C##ANDONOVJ  REVENUE    YES

We can test if everything is fine, by connecting to the application root and insert couple records:

SQL> alter session set container=north;
Session altered.
SQL> insert into c##andonovj.revenue values ('CANADA',1000);
SQL> insert into c##andonovj.revenue values ('USA',2000);
SQL> commit;
SQL> alter session set container=central;
Session altered.
SQL> commit;
SQL> insert into c##andonovj.revenue values ('GUATEMALA',3000);
SQL> insert into c##andonovj.revenue values ('NICARAGUA',4000);
SQL> alter session set container=south;
Session altered.
SQL> commit;
SQL> insert into c##andonovj.revenue values ('ARGENTINA',5000);
SQL> insert into c##andonovj.revenue values ('BRAZIL',6000);
SQL> commit;

After that you can end the upgrade or installation and sync every application PDB

Now, when you select from that table, the query will be redirect to all PDBs and thus the data will be sharded across the PDBs. If the PDBs are proxy PDBs, it can be even distributed in different continents. The logic is, if you connect to the ROOT, you can see the information across all PDBs, if you connect to a specific PDB, you can select the data only in that PDB and what is shared in the root, you CANNOT see data from other PDBs.

In this part we will perform the following operations:

• Clone a remote non-application PDB into application Container
• Clone application container

The procedure is the same as cloning a PDB, but after we create it, the PDB will be in restricted mode:

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         6 APDB2                          READ WRITE YES
SQL>
SQL>

That is because when we clone a nonapp PDB into application container we need to convert it:

NAME                           ACTION                                                       MESSAGE
------------------------------ ------------------------------------------------------------ ----------------------------------------------------------------------------------------------------
APDB2                          Run pdb_to_apppdb.sql.                                       Non-Application PDB plugged in as an Application PDB, requires pdb_to_apppdb.sql be run.

SQL>

Then we need to run:

SQL> alter session set container=apdb2;
SQL> $@ORACLE_HOME/rdbms/admin/pdb_to_apppdb.sql
........................
SQL>
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         6 APDB2                          READ WRITE YES

SQL> alter pluggable database apdb2 close;

Pluggable database altered.

SQL> alter pluggable database apdb2 open;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         6 APDB2                          READ WRITE NO
SQL>

Also this procedure can be done with a local PDB.

Last but not least, in this part we will:

• Convert normal PDB into application root container
• Convert regular PDB into application PDB
• Relocating a PDB