CREATE AN APPLICATION PDB

"Application containers" are a feature of version 12.2. An "application container" is an optional CDB component used to store data and metadata for an application. A CDB can have zero or multiple application containers. Each application consists of a configuration, metadata, and shared objects. We can therefore define it as a container for shared applications, where different versions of the same application can be hosted.

Application Containers can be created in different ways: from a PDB seed, by cloning PDBs, or by plugging or unplugging a PDB, but in all cases the CREATE PLUGGABLE DATABASE command is used, including the“APPLICATION CONTAINER” clause. To create an application container, you must be connected to the CDB root, which must be open in "read-write" mode, and the name of the application container must be unique.

1) Sentence to create the database as an Application Container.

As an example we will create an Application Container of toys which we will call "toys_root".

CREATE PLUGGABLE DATABASE toys_root AS APPLICATION CONTAINER
ADMIN USER admin identified by ****
ROLES=(CONNECT)
CREATE_FILE_DEST=’/u02/app/oracle/oradata/ORCL/toys_root’;
/

2) With the following query, we can check what types of PDBs we have created in our architecture.

The result will show our Application PDB "Application root" set to YES.

3) We open the database we have just created.

alter pluggable database TOYS_ROOT open;

Once PDB Application Container PDB has been created, we can test how app versions are managed and how these versions can be replicated across different PDBs.

4) We create an application version of the PDB.

With the PDB open, we run the command.

ALTER PLUGGABLE DATABASE application TOYS_APP BEGIN install .0';

With this we are telling it to create a version of an application which we will call TOYS_APP and whose version will be 1.0.

5) We check if the CBD has been created correctly.

select app_name, app_version, app_status
from dba_applications
where app_name='TOYS_APP';

6) We create a test tbs, a user and give the necessary permissions:

set echo on
create tablespace toys_tbs datafile size 100M autoextend on next 10M maxsize 200M;
create user toys_owner identified by **** container=all;
grant create session, dba to toys_owner;
alter user toys_owner default tablespace toys_tbs;

We create a new table in order to check that it replicates correctly. It will be version 1.0. of our TOYS_APP application.

SQL> CREATE TABLE toys_owner.raul (one number, two varchar(2));
Table created.
SQL> insert into toys_owner. raul values(1,'r');
1 row created.
SQL> commit;
Commit complete.

7) Close the installation of the "application".

ALTER PLUGGABLE DATABASE application TOYS_APP end install .0';

8) We now check the status of the application

select app_name, app_version, app_status
from dba_applications
where app_name='TOYS_APP';

APP_NAME APP_VERSION APP_STATUS
-------------------------------
TOYS_APP 1.0 NORMAL

9) Inter-container replication

To perform the test, we are going to create two new PDBS, in this case "robots" and "dolls".

CREATE PLUGGABLE database robots
admin user admin identified by ***
CREATE_FILE_DEST=
'/u02/app/oracle/oradata/ORCL/toys_root/robots'
Pluggable database created.

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

CREATE PLUGGABLE database dolls
admin user admin identified by ***
CREATE_FILE_DEST=
‘/u02/app/oracle/oradata/ORCL/toys_root/dolls’;

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

SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
2 PDB READ ONLY NO
3 TOYS_ROOT READ WRITE NO
4 PDB1 READ WRITE NO
5 TOYS_ROOT$SEED READ WRITE NO
6ROBOTS READWRITE NO
7DOLLS READWRITE NO

Now we go to the newly recreated containers to perform the synchronization of our application.

We switch to the ROBOTS container and refresh the application, in this case tst_app

SQL> ALTER SESSION SET CONTAINER=ROBOTS;

Session altered.

SQL> ALTER PLUGGABLE DATABASE APPLICATION TOYS_APP SYNC ;

Pluggable database altered.

We check that the table has been refreshed

SQL> show con_name

CON_NAME
------------------------------
ROBOTS

SQL> DESC TOYS_OWNER.RAUL
Name                               Null?    Type
---------------------------------- -------- -----------------------
UNO                                         NUMBER
DOS                                         VARCHAR2(2)


SQL> select * from toys_owner.raul;

       UNO DO
---------- --
	 1 r

Let's perform the same synchronization operation on the other PDB.

SQL> ALTER SESSION SET CONTAINER=DOLLS;
Session altered.

And we perform the same operation. We synchronize the application:

SQL> ALTER PLUGGABLE DATABASE APPLICATION TOYS_APP SYNC ;
Pluggable database altered.

We check that the table has been replicated:

SQL> desc toys_owner.raul
Name Null? Type
UNO NUMBER
DOS VARCHAR2(2)

SQL> select * from toys_owner.raul;
UNO DO
1 r

At any moment you will be able to know the status of your APP, how it is and its version

You can check it with a query similar to the following one, where the name of the APP, its id, its status, if it is implicit or not, etc.

select app_name, app_version, app_id, app_status,
app_implicit implicit
from dba_applications;

APP_NAME APP_VERSION APP_ID APP_STATUS I
---------------------------------------- ------------------------------ ---------- ------------ -
TOYS_APP 1.0 2 NORMAL N

10) Upgrade our APP.

Once we have created and checked the functioning of the Application Container, we can create different versions of it, or delete an APP version when it is no longer valid.

To upgrade the version of our APP, we have to use the begin upgrade / end upgrade commands, of course inside the APP_ROOT.

SQL> alter pluggable database application TOYS_APP1 begin upgrade '1.0' to '1.1';
Pluggable database altered.

SQL> insert into toys_owner. raul values(2,'d');
1 row created.
SQL> commit;
Commit complete.

SQL> alter pluggable database application TOYS_APP1 end upgrade;
Pluggable database altered.

SQL> alter session set container=DOLLS;
SQL> ALTER PLUGGABLE DATABASE APPLICATION TOYS_APP1 SYNC;
Pluggable database altered.

 SQL> SELECT * FROM TOYS_OWNER.RAUL;
   ONE TWO
---------- --
     1 r
     2 d

11) Uninstall an application

To uninstall an application we must perform the following actions:

SQL> ALTER PLUGGABLE DATABASE APPLICATION TOYS_APP BEGIN UNINSTALL;
Pluggable database altered.

SQL> drop user toys_owner cascade;
user dropped.

SQL> drop tablespace TOYS_TBS INCLUDING CONTENTS AND DATAFILES;
tablespace dropped

SQL> ALTER PLUGGABLE DATABASE APPLICATION TOYS_APP END UNINSTALL;
Pluggable database altered.

In short, Oracle allows us to maintain “versions” of different applications, which can be synchronized via PDB, updated, cloned, and so on.