How to Create a PDB-Application

By /

CREATE AN APPLICATION PDB DATABASE

Application containers" is a feature of version 12 version 2 (12.2). An application container is an optional CDB component to store data and metadata for an application. A CDB has 0 or more application containers. Each application is composed of a configuration, metadata and shared objects. We can therefore define it as a shared application container, where different versions of the same application can be hosted.

Application Containers can be created in different ways: from PDBD seed, cloning PDBS, pluggind or unplugged from 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 root CDB, and it must be open in "read write" mode, and the name of the applicaton 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 kind of PDBs we have created in our architecture.

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

3) We open the database we have just created.

alter pluggable database TOYS_ROOT open;

Once the Application Container PDB is created, we can test how the APP versions are managed and how these versions can be replicated between the different PDBSs.

4) We create an application version of the PDB.

Connected to the PDB TOYS_ROOT we execute.

ALTER PLUGGABLE DATABASE application TOYS_APP begin install '1.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 '1.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$SEED READ ONLY NO
3 TOYS_ROOT READ WRITE NO
4 PDB1 READ WRITE NO
5 T OYS_R OOT$SEED READ WRITE NO
6 ROBOTS READ WRITE N O
7 DO LLS READ WRITE NO 6 ROBOTS READ WRITE 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

We are going to 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 have "versions" of different applications, which can be synchronized through PDBs, upgraded, cloned, etc.

Scroll to top