Application Containers
Lab 7: Sharing Common Data
Overview
In Lab7 we introduce the advanced concept of data sharing.
We have already seen how Multitenant, with Application Containers, can provide an
instant SaaS architecture for an application previously architected for
standalone deployment. Technically this is done by installing a master
application definition in an Application Root. Application PDBs for each tenant
/ franchise are plugged into this Application Root and the metadata for the
database components of the Application definition is served from the
Application root. However, so far all data, including
data which may be considered part of the application definition ("seed
data") has been local. In other words, there's a replica of this seed data
in every Application PDB. various franchise PDBs. In this lab we'll see how, in
addition to metadata, common data may also be shared from Application Root. To
do this we'll upgrade application wmStore to v3.0 and
introduce various powerful data sharing capabilities.
Execution
- Open a terminal window
- Change directory to /u01/HOL/app_containers/
- source the environment. At command prompt execute source cdb1.env
- sqlplus /nolog
- At the SQL prompt execute SQL>@07.Data_Sharing.sql
Review of Application Container Lab7:
In this lab, we have explored the advanced concept of data sharing,
through the following steps:
1. Upgrade Application wmStore to v3.0.
a. Add a new table to the application, defined as sharing = data.
b. Populated this table with a common set of data.
c.
Change table wm_Campaigns
to be sharing = data
(centrally-defined data only).
d.
Change table wm_Products
to be sharing = extended data
(both centrally-defined and locally-defined data).
e. This upgrade requires careful timing of redefinition of seed data in these two tables. (The seed data needs to be removed from the Application PDBs and re-created in the Application Root.)
f. Update data in wm_Orders to set the appropriate value for the newly added denormalized column.
2. Propagate the Upgrade to all franchises.
3. Query the wm_Products table in a franchise PDB to see the sources of data.
Detailed Steps
Phase 1: Upgrade wmStore application to v3.
Connect to wmStore_Admin
Phase 2. Upgrade the application schema.
Create a new List of Values table. For the purposes of this demo, these values will be centrally defined only. (That is, they may be thought of as "seed data" that is part of the application definition.) Here we have created a new table, and specified that its data is to be centrally defined and shared from Application Root to all application PDBs. In the next step we go through the routine exercise of adding data to this new table.
Phase 3. Add corresponding new common seed data.
Now, things get interesting! In the next section, we convert the sharing mode of two existing tables:
_ wm_Campaigns: Centrally defined (sharing = data)
_ wm_Products: Centrally and locally defined (sharing = extended data)
Notice how we handle the seed data. In each case, we must disable any constraints that reference data which is about to be "moved" from the application PDB to the Root. (Technically this is a delete from the application PDB, followed by an insert into Application Root. We need the delete to work.)
Phase 4. Convert sharing mode of tables:
_ Campaigns are centrally defined
_ Products are both centrally and locally defined
Phase 4a. Disable constraints.
This is required because in these steps we will be deleting data from the Application PDBs and re-creating it in Application Root. We don't want the delete statements to fail because of these constraints.
Phase 4b. Delete the seed data from these tables.
These statements will execute in the Application PDBs because the sharing mode of the tables is currently sharing=metadata.
Phase 4c. Change the sharing mode of these tables.
Phase 4d. Re-insert the seed data. These statements will now execute in Application Root.
Phase 4e. Re-enable constraints.
These steps need to be skipped until cross-container constraints are supported.
In the previous section, we converted the sharing mode of two existing tables.
Phase 5. Declare the end of the upgrade.
We have now completed the definition of the upgrade to v3 of wmStore. As before, this upgrade script is defined in a "begin / end" block. Between the begin and end statements, much of the upgrade was a typical upgrade script. There was also some special handling to convert the data sharing mode of some of the tables. We have also seen how the steps in the upgrade script are captured in Application Root, to be replayed subsequently in the Application PDBs as they are sync'ed. The next step, therefore is to synchronize the Application PDBs for all tenants. In this lab, we'll do this by executing a script to do this in a single step.
Phase 6. Sync all Application PDBs.
This will be accomplished by executing a script. Application PDBs for all tenants / franchises have now been synchronized with the latest version of wmStore. In the next step, we'll connect to a particular franchise's Application PDB and look at the data in table wm_Products. The "sharing mode" of this table was changed to "sharing = extended data" in an earlier step of this Lab. In the next step we'll see the source of the data for the various rows in this table.