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', '[email protected]') ; PL/SQL procedure successfully completed. SQL> exec customer_add ('Scotty Hertz', '[email protected]') ; PL/SQL procedure successfully completed. SQL> exec customer_add ('Smith Watson', '[email protected]') ; PL/SQL procedure successfully completed. SQL> exec customer_add ('John Curt', '[email protected]') ; PL/SQL procedure successfully completed. SQL> exec customer_add ('Dalton X', '[email protected]') ; PL/SQL procedure successfully completed. SQL> connect usr_b2c_web/secret@//localhost:1521/B2C_WEB_APP_GE Connected. SQL> exec customer_add ('Scotty Hertz', '[email protected]') ; PL/SQL procedure successfully completed. SQL> exec customer_add ('Sandeep John', '[email protected]') ; PL/SQL procedure successfully completed. SQL> exec customer_add ('Smith Curt', '[email protected]') ; PL/SQL procedure successfully completed. SQL> exec customer_add ('Orlondo Watson', '[email protected]') ; PL/SQL procedure successfully completed. SQL> connect usr_b2c_web/secret@//localhost:1521/B2C_WEB_APP_ZH Connected. SQL> exec customer_add ('Maria Smith', '[email protected]') ; PL/SQL procedure successfully completed. SQL> exec customer_add ('Smith Scotty', '[email protected]') ; 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 [email protected] 3 B2C_WEB_APP_VD 2 Scotty Hertz [email protected] 3 B2C_WEB_APP_VD 3 Smith Watson [email protected] 3 B2C_WEB_APP_VD 4 John Curt [email protected] 3 B2C_WEB_APP_VD 5 Dalton X [email protected] 3 B2C_WEB_APP_GE 1 Scotty Hertz [email protected] 6 B2C_WEB_APP_GE 2 Sandeep John [email protected] 6 B2C_WEB_APP_GE 3 Smith Curt [email protected] 6 B2C_WEB_APP_GE 4 Orlondo Watson [email protected] 6 B2C_WEB_APP_ZH 1 Maria Smith [email protected] 7 B2C_WEB_APP_ZH 2 Smith Scotty [email protected] 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 [email protected] 3 B2C_WEB_APP_VD 2 Scotty Hertz [email protected] 3 B2C_WEB_APP_VD 3 Smith Watson [email protected] 3 B2C_WEB_APP_VD 4 John Curt [email protected] 3 B2C_WEB_APP_VD 5 Dalton X [email protected] 3 B2C_WEB_APP_ZH 1 Maria Smith [email protected] 7 B2C_WEB_APP_ZH 2 Smith Scotty [email protected] 7 B2C_WEB_APP_GE 1 Scotty Hertz [email protected] 6 B2C_WEB_APP_GE 2 Sandeep John [email protected] 6 B2C_WEB_APP_GE 3 Smith Curt [email protected] 6 B2C_WEB_APP_GE 4 Orlondo Watson [email protected] 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.