Infrastructure at your Service

Nicolas Penot

Oracle Application Container: a Swiss Use case

Here we want to start a business in Switzerland in 3 different areas and make it easy to start a new market area as soon as required by that business. We are going to use the Application Container feature in order to:

  • Have a dedicated PDB for each marker with shared and local metadata and data
  • Roll out frequently data model and code to add features in a central manner with one command

We have first to create an master application container, you can have many master application containers within a CDB. We will also create a seed PDB from that master application container. Not mandatory, its role will be to keep a sync copy of the master and improves speed provisioning for new pluggable database creation within the master container.

SQL> create pluggable database B2C_WEB_CON as application container admin user pdbadmin identified by secret roles = (DBA) ;

Pluggable database B2C_WEB_CON created.

SQL> alter pluggable database B2C_WEB_CON open;

Pluggable database B2C_WEB_CON altered.

SQL> alter session set container = B2C_WEB_CON ;

Session altered.

SQL> create pluggable database as seed admin user pdbadmin identified by oracle roles=(DBA)  ;

Pluggable database AS created.

SQL> alter pluggable database B2C_WEB_CON$SEED open;

Pluggable database B2C_WEB_CON$SEED altered.

SQL> select PDB_ID, PDB_NAME, STATUS, IS_PROXY_PDB, APPLICATION_ROOT, APPLICATION_PDB, APPLICATION_SEED, APPLICATION_ROOT_CON_ID from dba_pdbs order by 1;
  PDB_ID PDB_NAME           STATUS   IS_PROXY_PDB   APPLICATION_ROOT   APPLICATION_PDB   APPLICATION_SEED     APPLICATION_ROOT_CON_ID
       4 B2C_WEB_CON        NORMAL   NO             YES                NO                NO
       5 B2C_WEB_CON$SEED   NORMAL   NO             NO                 YES               YES                                        4

 

I will now create an application from zero. An application is a set of command executed in the master application container and on which a version tag is applied. In other words, Oracle will record what happens in the master container and applied a version flag on those commands to replay them in future PDBs.

SQL> connect sys/oracle@//localhost:1521/B2C_WEB_CON as sysdba
Connected.

SQL> alter pluggable database application B2C_WEB_APP begin install '1.0';

Pluggable database altered.

SQL> create tablespace TBS_B2C_WEB datafile size 50M ;

Tablespace created.
SQL> create user USR_B2C_WEB identified by secret quota unlimited on TBS_B2C_WEB ;

User USR_B2C_WEB created.

SQL> alter user USR_B2C_WEB default tablespace TBS_B2C_WEB ;

User USR_B2C_WEB altered.

SQL> grant create session, resource to USR_B2C_WEB ;

Grant succeeded.

SQL> alter session set current_schema = USR_B2C_WEB ;

Session altered.

SQL> create table customers ( customer_id number, name varchar2(50), address varchar2(50) ) ;

Table CUSTOMERS created.

SQL> create table orders ( order_id number, customer_id number, order_date date ) ;

Table ORDERS created.

SQL> create table order_details ( order_detail_id number, order_id number, product_id number, quantity number ) ;

Table ORDER_DETAILS created.

SQL> create table products ( product_id number, name varchar2(50) ) ;

Table PRODUCTS created.

SQL> alter pluggable database application B2C_WEB_APP end install '1.0';

Pluggable database APPLICATION altered.

SQL> select * from dba_applications;
APP_NAME                                 APP_ID APP_VERSION   APP_STATUS   APP_IMPLICIT   APP_CAPTURE_SERVICE   APP_CAPTURE_MODULE
APP$62EA42BE47360FA8E0537A38A8C0A0F3          2 1.0           NORMAL       Y              SYS$USERS             java@VM122 (TNS V1-V3)
B2C_WEB_APP                                   3 1.0           NORMAL       N              b2c_web_con           java@VM122 (TNS V1-V3)

 

We can now synchronize the application tables of our application B2C_WEB_APP from the MASTER to the SEED in order to increase the next pluggable database creations speed

SQL> connect sys/oracle@//localhost:1521/B2C_WEB_CON$SEED as sysdba

Session altered.

SQL> alter pluggable database application B2C_WEB_APP sync ;

 

Then, go to the master and create your pluggable database for each market with the latest B2C_WEB_APP application release which is currently 1.0

SQL> connect pdbadmin/secret@//localhost:1521/B2C_WEB_CON
Connected.

SQL> create pluggable database B2C_WEB_APP_VD admin user pdbadmin identified by secret roles=(DBA) ;

Pluggable database B2C_WEB_APP_VD created.

SQL> create pluggable database B2C_WEB_APP_GE admin user pdbadmin identified by secret roles=(DBA) ;

Pluggable database B2C_WEB_APP_GE created.

SQL> create pluggable database B2C_WEB_APP_ZH admin user pdbadmin identified by secret roles=(DBA) ;

Pluggable database B2C_WEB_APP_ZH created.

 

We open and save state to make them opened at the next CDB restart

SQL> connect sys/oracle@//localhost:1521/B2C_WEB_CON as sysdba
Connected.

SQL> alter pluggable database all open ;

Pluggable database ALL altered.

SQL> alter pluggable database save state ;

Pluggable database SAVE altered.

 

Let’s generate some business activity on each pluggable database corresponding to different Swiss markets

SQL> connect usr_b2c_web/secret@//localhost:1521/B2C_WEB_APP_VD
Connected.
SQL> insert into usr_b2c_web.products select rownum, 'product_VD_00'||rownum from dual connect by level <= 5 ;

5 rows inserted.

SQL> commit ;

Commit complete.

SQL> connect usr_b2c_web/secret@//localhost:1521/B2C_WEB_APP_GE
Connected.

SQL> insert into usr_b2c_web.products select rownum, 'product_GE_00'||rownum from dual connect by level <= 5 ;

5 rows inserted.

SQL> connect usr_b2c_web/secret@//localhost:1521/B2C_WEB_APP_ZH
Connected.

SQL> insert into usr_b2c_web.products select rownum, 'product_ZH_00'||rownum from dual connect by level <= 5 ;

5 rows inserted.

SQL> commit;

Commit complete.

 

Now we can check from the master container if data are well located according to their market

SQL> connect usr_b2c_web/secret@//localhost:1521/B2C_WEB_CON
Connected.

SQL> select con$name, p.*
  2  from containers ( PRODUCTS ) p ;
CON$NAME           PRODUCT_ID NAME               CON_ID
B2C_WEB_APP_ZH              1 product_ZH_001          7
B2C_WEB_APP_ZH              2 product_ZH_002          7
B2C_WEB_APP_ZH              3 product_ZH_003          7
B2C_WEB_APP_ZH              4 product_ZH_004          7
B2C_WEB_APP_ZH              5 product_ZH_005          7
B2C_WEB_APP_GE              1 product_GE_001          6
B2C_WEB_APP_GE              2 product_GE_002          6
B2C_WEB_APP_GE              3 product_GE_003          6
B2C_WEB_APP_GE              4 product_GE_004          6
B2C_WEB_APP_GE              5 product_GE_005          6
B2C_WEB_APP_VD              1 product_VD_001          3
B2C_WEB_APP_VD              2 product_VD_002          3
B2C_WEB_APP_VD              3 product_VD_003          3
B2C_WEB_APP_VD              4 product_VD_004          3
B2C_WEB_APP_VD              5 product_VD_005          3

 

We have different products for each of our markets. Now, we would like to Upgrade the data model and add some code (a procedure) to add a basic feature: add a customer

SQL> connect pdbadmin/secret@//localhost:1521/B2C_WEB_CON
Connected.

SQL> alter pluggable database application B2C_WEB_APP begin upgrade '1.0' to '1.1';

Pluggable database APPLICATION altered.

SQL> alter session set current_schema = USR_B2C_WEB ;

Session altered.

SQL> alter table customers drop ( address ) ;

Table CUSTOMERS altered.

SQL> alter table customers add ( email varchar2(35) ) ;

Table CUSTOMERS altered.

SQL> alter table products add ( price number (8, 2) ) ;

Table PRODUCTS altered.

SQL> create sequence customer_seq ;

Sequence CUSTOMER_SEQ created.

SQL> create procedure customer_add ( name in varchar2, email in varchar2 ) as
  2  begin
  3    insert into customers values ( customer_seq.nextval, name, email ) ;
  4    commit ;
  5  end;
  6  /

Procedure CUSTOMER_ADD compiled

SQL> alter pluggable database application B2C_WEB_APP end upgrade to '1.1';

Pluggable database APPLICATION altered.

 

Let push in production the release 1.1 one market after each other

SQL> connect pdbadmin/oracle@//localhost:1521/B2C_WEB_APP_VD
Connected.

SQL> alter pluggable database application B2C_WEB_APP sync ;

Pluggable database APPLICATION altered.

SQL> connect pdbadmin/oracle@//localhost:1521/B2C_WEB_APP_GE
Connected.

SQL> alter pluggable database application B2C_WEB_APP sync ;

Pluggable database APPLICATION altered.

SQL> connect pdbadmin/oracle@//localhost:1521/B2C_WEB_APP_ZH
Connected.

SQL> alter pluggable database application B2C_WEB_APP sync ;

Pluggable database APPLICATION altered.

 

Some business activity happens and new customer are going to appears with the new feature we deployed at the release 1.1 of our B2C_WEB_APP application

SQL> connect usr_b2c_web/secret@//localhost:1521/B2C_WEB_APP_VD
Connected.

SQL> exec customer_add ('Scotty Hertz', 'SCOTTY@GMAIL.COM') ;

PL/SQL procedure successfully completed.

SQL> exec customer_add ('Scotty Hertz', 'SCOTTY@GMAIL.COM') ;

PL/SQL procedure successfully completed.

SQL> exec customer_add ('Smith Watson', 'SMITH@YAHOO.COM') ;

PL/SQL procedure successfully completed.

SQL> exec customer_add ('John Curt', 'JOHN@GMAIL.COM') ;

PL/SQL procedure successfully completed.

SQL> exec customer_add ('Dalton X', 'DALTON@AOL.COM') ;

PL/SQL procedure successfully completed.

SQL> connect usr_b2c_web/secret@//localhost:1521/B2C_WEB_APP_GE
Connected.

SQL> exec customer_add ('Scotty Hertz', 'SCOTTY@GMAIL.COM') ;

PL/SQL procedure successfully completed.

SQL> exec customer_add ('Sandeep John', 'SANDEEP@YAHOO.COM') ;

PL/SQL procedure successfully completed.

SQL> exec customer_add ('Smith Curt', 'SMITH@YAHOO.COM') ;

PL/SQL procedure successfully completed.

SQL> exec customer_add ('Orlondo Watson', 'ORLONDO@GMAIL.COM') ;

PL/SQL procedure successfully completed.

SQL> connect usr_b2c_web/secret@//localhost:1521/B2C_WEB_APP_ZH
Connected.

SQL> exec customer_add ('Maria Smith', 'MARIA@GMAIL.COM') ;

PL/SQL procedure successfully completed.

SQL> exec customer_add ('Smith Scotty', 'SMITH@YAHOO.COM') ;

PL/SQL procedure successfully completed.

 

Now, have a look on the new customer data from the master master container

SQL> connect usr_b2c_web/secret@//localhost:1521/B2C_WEB_CON
Connected.

SQL> select con$name, c.*
  2  from containers ( CUSTOMERS ) c ;
CON$NAME           CUSTOMER_ID NAME             EMAIL                 CON_ID
B2C_WEB_APP_VD               1 Scotty Hertz     SCOTTY@GMAIL.COM           3
B2C_WEB_APP_VD               2 Scotty Hertz     SCOTTY@GMAIL.COM           3
B2C_WEB_APP_VD               3 Smith Watson     SMITH@YAHOO.COM            3
B2C_WEB_APP_VD               4 John Curt        JOHN@GMAIL.COM             3
B2C_WEB_APP_VD               5 Dalton X         DALTON@AOL.COM             3
B2C_WEB_APP_GE               1 Scotty Hertz     SCOTTY@GMAIL.COM           6
B2C_WEB_APP_GE               2 Sandeep John     SANDEEP@YAHOO.COM          6
B2C_WEB_APP_GE               3 Smith Curt       SMITH@YAHOO.COM            6
B2C_WEB_APP_GE               4 Orlondo Watson   ORLONDO@GMAIL.COM          6
B2C_WEB_APP_ZH               1 Maria Smith      MARIA@GMAIL.COM            7
B2C_WEB_APP_ZH               2 Smith Scotty     SMITH@YAHOO.COM            7


11 rows selected.

 

As we don’t like the email format because it’s ugly in the web interface, we now are going to release a “data” patch on top of the release 1.1 in order to format customer’s emails in a proper manner

SQL> connect pdbadmin/secret@//localhost:1521/B2C_WEB_CON
Connected.

SQL> alter pluggable database application B2C_WEB_APP begin patch 1 minimum version '1.1' ;

Pluggable database APPLICATION altered.

SQL> update usr_b2c_web.customers set email = trim(lower(email)) ;

0 rows updated.

SQL> alter pluggable database application B2C_WEB_APP end patch 1 ;

Pluggable database APPLICATION altered.

SQL> select * from dba_app_patches;
APP_NAME        PATCH_NUMBER PATCH_MIN_VERSION   PATCH_STATUS   PATCH_COMMENT
B2C_WEB_APP                1 1.1                 INSTALLED

 

As we know have a patch ready to cleanup the email format we are ready to deploy it on each market

SQL> connect pdbadmin/oracle@//localhost:1521/B2C_WEB_APP_VD
Connected.

SQL> alter pluggable database application B2C_WEB_APP sync;

Pluggable database APPLICATION altered.

SQL> connect pdbadmin/oracle@//localhost:1521/B2C_WEB_APP_GE
Connected.

SQL> alter pluggable database application B2C_WEB_APP sync;

Pluggable database APPLICATION altered.

SQL> connect pdbadmin/oracle@//localhost:1521/B2C_WEB_APP_ZH
Connected.

SQL> alter pluggable database application B2C_WEB_APP sync;

Pluggable database APPLICATION altered.

 

Let’s check if the data patch has been applied successfully and the email format is now OK for all markets

SQL> connect usr_b2c_web/secret@//localhost:1521/B2C_WEB_CON
Connected.

SQL> select con$name, c.*
  2  from containers ( CUSTOMERS ) c ;
CON$NAME           CUSTOMER_ID NAME             EMAIL                 CON_ID
B2C_WEB_APP_VD               1 Scotty Hertz     scotty@gmail.com           3
B2C_WEB_APP_VD               2 Scotty Hertz     scotty@gmail.com           3
B2C_WEB_APP_VD               3 Smith Watson     smith@yahoo.com            3
B2C_WEB_APP_VD               4 John Curt        john@gmail.com             3
B2C_WEB_APP_VD               5 Dalton X         dalton@aol.com             3
B2C_WEB_APP_ZH               1 Maria Smith      maria@gmail.com            7
B2C_WEB_APP_ZH               2 Smith Scotty     smith@yahoo.com            7
B2C_WEB_APP_GE               1 Scotty Hertz     scotty@gmail.com           6
B2C_WEB_APP_GE               2 Sandeep John     sandeep@yahoo.com          6
B2C_WEB_APP_GE               3 Smith Curt       smith@yahoo.com            6
B2C_WEB_APP_GE               4 Orlondo Watson   orlondo@gmail.com          6


11 rows selected.

 

A new feature has now been claimed from the business. We need an upgrade of the application to add a parameters table that should contains USR_B2C_WEB application’s parameters which must be shared on all PDB applications. Also each market want be able to add its own parameters without impacting existing one or others markets.
We are going to use the attribute “SHARING” set to “EXTENDED DATA” for that table to make possible a mix of shared data in the master and PDB local data in the same table (deeper explanation and others sharing modes here).

SQL> connect pdbadmin/secret@//localhost:1521/B2C_WEB_CON
Connected.

SQL> alter pluggable database application B2C_WEB_APP begin upgrade '1.1' to '1.2';

Pluggable database APPLICATION altered.

SQL> create table usr_b2c_web.settings sharing = extended data ( name varchar2(50), value varchar2(50) );

Table USR_B2C_WEB.SETTINGS created.

SQL> insert into usr_b2c_web.settings values ( 'compagny_name', 'wisdom IT' ) ;

1 row inserted.

SQL> insert into usr_b2c_web.settings values ( 'head_quarter_address', 'street village 34, 3819 Happiness, Switzerland' ) ;

1 row inserted.

SQL> commit ;

Commit complete.

SQL> alter pluggable database application B2C_WEB_APP end upgrade to '1.2';

Pluggable database APPLICATION altered.

 

Upgrade 1.2 for all market and addition of a local parameter “market_name” for each market

SQL> connect pdbadmin/oracle@//localhost:1521/B2C_WEB_APP_VD
Connected.

SQL> alter pluggable database application B2C_WEB_APP sync;

Pluggable database APPLICATION altered.

SQL> insert into usr_b2c_web.settings values ( 'market_name', 'VAUD' ) ;

1 row inserted.

SQL> commit ;

Commit complete.

SQL> connect pdbadmin/oracle@//localhost:1521/B2C_WEB_APP_GE
Connected.

SQL> alter pluggable database application B2C_WEB_APP sync;

Pluggable database APPLICATION altered.

SQL> insert into usr_b2c_web.settings values ( 'market_name', 'GENEVA' ) ;

1 row inserted.

SQL> commit ;

Commit complete.

SQL> connect pdbadmin/oracle@//localhost:1521/B2C_WEB_APP_ZH
Connected.

SQL> alter pluggable database application B2C_WEB_APP sync;

Pluggable database APPLICATION altered.

SQL> insert into usr_b2c_web.settings values ( 'market_name', 'ZURICH' ) ;

1 row inserted.

SQL> commit ;

Commit complete.

 

Now we check if shared parameters are available for all markets and each one of them has a dedicated “market_name” value

SQL> connect usr_b2c_web/secret@//localhost:1521/B2C_WEB_CON
Connected.

SQL> select con$name, c.*
  2  from containers ( SETTINGS ) c ;

CON$NAME         NAME                   VALUE                                              CON_ID
B2C_WEB_CON      compagny_name          wisdom IT                                               4
B2C_WEB_CON      head_quarter_address   street village 34, 3819 Happiness, Switzerland          4
B2C_WEB_APP_GE   compagny_name          wisdom IT                                               6
B2C_WEB_APP_GE   head_quarter_address   street village 34, 3819 Happiness, Switzerland          6
B2C_WEB_APP_GE   market_name            GENEVA                                                  6
B2C_WEB_APP_VD   compagny_name          wisdom IT                                               3
B2C_WEB_APP_VD   head_quarter_address   street village 34, 3819 Happiness, Switzerland          3
B2C_WEB_APP_VD   market_name            VAUD                                                    3
B2C_WEB_APP_ZH   compagny_name          wisdom IT                                               7
B2C_WEB_APP_ZH   head_quarter_address   street village 34, 3819 Happiness, Switzerland          7
B2C_WEB_APP_ZH   market_name            ZURICH                                                  7

 

Looks all good.

Now the business need to extend the startup activity to a new market area of Switzerland. We are so going to add a new pluggable database for that marker. This market will benefit immediately of the latest application release.

SQL> alter session set container = B2C_WEB_CON ;

Session altered.

SQL> create pluggable database B2C_WEB_APP_ZG admin user pdbadmin identified by secret roles=(DBA) ;

Pluggable database B2C_WEB_APP_ZG created.

SQL> alter pluggable database B2C_WEB_APP_ZG open;

Pluggable database B2C_WEB_APP_ZG altered.

 

Let’s check with the parameter table if all data have been synchronized

SQL> select con$name, c.*
  2  from containers ( SETTINGS ) c ;
CON$NAME         NAME                   VALUE                                              CON_ID
B2C_WEB_APP_VD   compagny_name          wisdom IT                                               3
B2C_WEB_APP_VD   head_quarter_address   street village 34, 3819 Happiness, Switzerland          3
B2C_WEB_APP_VD   market_name            VAUD                                                    3
B2C_WEB_CON      compagny_name          wisdom IT                                               4
B2C_WEB_CON      head_quarter_address   street village 34, 3819 Happiness, Switzerland          4
B2C_WEB_APP_GE   compagny_name          wisdom IT                                               6
B2C_WEB_APP_GE   head_quarter_address   street village 34, 3819 Happiness, Switzerland          6
B2C_WEB_APP_GE   market_name            GENEVA                                                  6
B2C_WEB_APP_ZH   compagny_name          wisdom IT                                               7
B2C_WEB_APP_ZH   head_quarter_address   street village 34, 3819 Happiness, Switzerland          7
B2C_WEB_APP_ZH   market_name            ZURICH                                                  7
B2C_WEB_APP_ZG   compagny_name          wisdom IT                                               5
B2C_WEB_APP_ZG   head_quarter_address   street village 34, 3819 Happiness, Switzerland          5

 

I wish this post will help to understand how to implement Container Application in real life and please do not hesitate to contact us if you have any questions or require further information.

 

Leave a Reply


+ one = 3

Nicolas Penot
Nicolas Penot