Create and Manage Application Containers in Oracle Database

Application Containers act like a root container for the application which holds application releases. An application container has one or more applications. We can create Pluggable Databases in Application Containers like CDB root Container.

Create Application Containers database as name APPCONT:

SQL> CREATE PLUGGABLE DATABASE appcont AS APPLICATION CONTAINER
 ADMIN USER pdb_appadm IDENTIFIED BY manager FILE_NAME_CONVERT= 'C:\ORACLE\ORADATA\XE\','C:\ORACLE\ORADATA\APPCONT\');

Pluggable database created.
  
SQL> ALTER PLUGGABLE DATABASE appcont OPEN;

Drop application Containers database as Name APPCONT:

SQL> ALTER PLUGGABLE DATABASE appcont CLOSE;
SQL> DROP PLUGGABLE DATABASE appcont INCLUDING DATAFILES;

Manage application Pluggable Database in APPCONT application Container:

For managing the pluggable database in Application Container, we need to create the pluggable database by connection or switching to Application Container first.

--Switch to app container or login with app container for managing pluggable database
Sqlplus / as sysdba
ALTER SESSION SET CONTAINER = appcont;


-- Create a application pluggable database in APPCONT container:

CREATE PLUGGABLE DATABASE appspdb ADMIN USER pdb_appadm IDENTIFIED BY manager FILE_NAME_CONVERT=('C:\ORACLE\ORADATA\XE\','C:\ORACLE\ORADATA\APPSPDB\');

--Open the pluggable database:
ALTER PLUGGABLE DATABASE appspdb open;

--Sync the pluggable database with applications of APPCONT Container:
ALTER SESSION SET CONTAINER=APPSPDB;
ALTER PLUGGALBE DATABASE APPLICATION ALL SYNC;

--CLOSE or DROP the pluggable database if not needed:
ALTER SESSION SET container = appcont;

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

Manage the application in application Containers:

Syntax:

ALTER PLUGGABLE DATABASE APPLICATION
{ { app_name
    { BEGIN INSTALL 'app_version' [ COMMENT 'comment' ]
    | END INSTALL [ 'app_version' ]
    | BEGIN PATCH number [ MINIMUM VERSION 'app_version' ] [ COMMENT 'comment' ]
    | END PATCH [ number ]
    | BEGIN UPGRADE 'start_app_version' TO 'end_app_version' [ COMMENT 'comment' ]
    | END UPGRADE [ TO 'end_app_version' ]
    | BEGIN UNINSTALL
    | END UNINSTALL
    | SET PATCH number
    | SET VERSION 'app_version'
    | SET COMPATIBILITY VERSION { 'app_version' | CURRENT }
    | SYNC  }
  |
  { ALL SYNC }
}

Example of using Begin Install application

SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 APPCONT                        READ WRITE NO
         5 APPSPDB                        READ WRITE NO

-- Create an application on APPCONT
SQL> ALTER SESSION SET CONTAINER=APPCONT;

SQL> ALTER PLUGGABLE DATABASE APPLICATION sales_app BEGIN INSTALL '1.0';
Pluggable database altered.

SQL> create table test (id number,name varchar(100));
Table created.

SQL> ALTER PLUGGABLE DATABASE APPLICATION sales_app END INSTALL '1.0';
Pluggable database altered.
-- Test on APPCONT
SQL> select * from test;
no rows selected

--Switch to PDB database
SQL> alter session set container=appspdb;
Session altered.

SQL> select * from test;
select * from test
              *
ERROR at line 1:
ORA-00942: table or view does not exist

--Check on PDB database with SYNC command:
SQL> ALTER PLUGGABLE DATABASE APPLICATION sales_app SYNC TO '1.0';
Pluggable database altered.

SQL> select * from test;
no rows selected

Example of using patching in APP container:

--Connect with APP Container database and apply changes as Patch
SQL> ALTER SESSION SET CONTAINER=APPCONT;

SQL> ALTER PLUGGABLE DATABASE APPLICATION sales_app BEGIN PATCH 101 MINIMUM VERSION '1.0';
Pluggable database altered.

SQL> create table test_new (id number);
Table created.

SQL> insert into test_new values (1);
1 row created.

SQL> insert into test_new values (2);
1 row created.

SQL> insert into test_new values (3);
1 row created.

SQL> ALTER PLUGGABLE DATABASE APPLICATION sales_app END PATCH 101;
Pluggable database altered.

--Switch to Pluggable database:
SQL> ALTER SESSION SET CONTAINER=APPSPDB;
SQL> ALTER PLUGGABLE DATABASE APPLICATION sales_app SYNC TO PATCH 101;
Pluggable database altered.

SQL> select * from test_new;

        ID
----------
         1
         2
         3

Example of using BEGIN UPGRADE AND END UPGRADE:

Note: Upgrade will create a new application container database, After end the upgrade you have two application containers one for old version and one for new version.

SQL> ALTER SESSION SET CONTAINER=APPCONT;

SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 APPCONT                        READ WRITE NO
         5 APPSPDB                        READ WRITE NO

-- Begin upgrade of application:
SQL> ALTER PLUGGABLE DATABASE APPLICATION sales_app  BEGIN UPGRADE '1.0' to '2.0';

Pluggable database altered.

SQL> insert into test values (1,'2');
1 row created.

SQL> insert into test values (2,'2');
1 row created.

SQL> ALTER PLUGGABLE DATABASE APPLICATION sales_app END UPGRADE TO '2.0';
Pluggable database altered.

--After upgrade the application, we have new created PDBs:
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APPSCONT                       READ WRITE NO
         4 F1271862627_3_1                READ WRITE NO
         5 APPSPDB                        READ WRITE NO
SQL> show pdbs;

Check application status from the following query:

ALTER SESSION SET CONTAINER=APPSCONT;

col name for a10
col app_name for a20
col app_version for a11
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;

NAME          CON_UID APP_NAME             APP_VERSION APP_STATUS
---------- ---------- -------------------- ----------- ------------
APPPDB1      71859119 SAAS_SALES_APP       2.0         NORMAL