NOTE: This article was written using Oracle Public Cloud.
Introduction:
Developers and end users are the roles that mostly use the database. Developers keep fixing code, maintaining the legacy applications, creating new applications or creating new versions of the same applications. There area a lot of tasks involved in these activities, some of them would be creating new databases for new applications, cloning the data of production database, re-creating packages for new versions of the applications, and if we have several customers using those applications we have to sync those customer's application with the new data or performing refreshes of the new version. Developers and DBAs work together, Oracle knows that and that's why with every version of Oracle Database several functions, packages and features are introduced to help not only DBAs but also Developers. In Oracle Database 12.2 a new feature called “Application Container” was introduced, this new feature helps developers a lot with the day-to-day tasks. With “Application Container”, developers can create Applications, every Application can have its own data and version and Developers decide which database should have which version of the same Application and when to refresh the data. With “Application Containers” the developers keep the objects and data only in one side, not in every database in the organization, sync from that principal side all the dependent databases. Also there are three levels of “Sharing” for those data, some allow to store the data in each PDB. This what we will discuss in this article, how to create applications and how to sync them with the PDBs.
What is an Application Container? An Application Container is composed by One Application Root, zero or more Application Pluggable Databases (also known as Application Tenants), zero or one Application Seed and zero or more Applications.
Creating an Application Root:
An Application Root is an special Pluggable Database where the "Applications" are installed. Developers Maintain the objects and data only in this Pluggable Databases and later they can sync the Application PDBs with these objects and data. There may be only one Application Root per Application Container. Using different "Sharing" levels of the data we can store some data into each PDB.
In order to create an Application Root you have to be connected with SYS or other user with privileges:
SQL> show user USER is "SYS"
You have to be connected to CDB$ROOT:
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT
Then you create the Application Root. As you can see bellow, the syntax to create an Application Root is very similar to create a normal Pluggable Database, the difference is the addition of the clause "as Application Container".
SQL> create pluggable database AppRoot as application container admin user pdbadmin identified by xxxx; Pluggable database created.
Opening the Application Root:
SQL> alter pluggable database AppRoot open; Pluggable database altered.
Confirming the Application Root was created successfully:
SQL> select con_id, name , open_mode, application_root from v$pdbs where application_root='YES'; CON_ID NAME OPEN_MODE APP ---------- ---------- ---------- --- 5 APPROOT READ WRITE YES
From these steps you would realize that we added "as Application Container". So does that mean we created an Application Container or an Application Root? Well, this could be confusing but at the end it is simple. I prefer to see it this way: "When we create an Application Root, by default one Application Container is created because an Application Root cannot exist alone", or if you want… you can see it this way: "When we create an Application Container by default an Application Root is created". You can pick your preferred definition 🙂
Creating an Application Pluggable Database
An Application PDB or Application Tenant is one special Pluggable Database that can get metadata and data from the Application Root and also it can have its own metadata and data, it depends on how the "Application" was created. We will discuss this "depends" later. So basically the Application PDBs are those databases that belong to one and only one Application Root that's why when you create an Application PDB you must be connected to one Application Root. So far you have seen that an Application Root can have zero or many "Application PDBs" but an "Application PDB" belongs to only one "Application Root".
The first step to create an "Application PDB" is to be connected to an Application Root:
SQL> alter session set container=AppRoot; Session altered.
Verify you are connected to the Application Root:
SQL> show con_name CON_NAME ------------------------------ APPROOT
The creation of the Application PDB is exactly the same than creating a normal PDB, the only difference is that now we are connected to an Application Root:
SQL> create pluggable database AppPDB1 admin user apppdb1admin identified by xxxx; Pluggable database created.
Opening the Application Tenant:
SQL> alter pluggable database AppPDB1 open; Pluggable database altered.
Verifying the Application PDBs were created successfully:
SQL> select con_id, name , open_mode, application_root, application_pdb from v$pdbs; CON_ID NAME OPEN_MODE APPLICATION_ROOT APPLICATION_PDB ---------- -------- ---------- ---------------- --------------- 5 APPROOT READ WRITE YES NO 6 APPPDB1 READ WRITE NO YES
So far we have created 1 Application Container containing 1 Application Root and two Application PDBs. But there is not any Application yet. That is the next step.
Creating an Application
An Application is composed by Objects and Data. Every Object can be created with three levels of "Sharing": Metadata-linked, Data-Linked and Extended-Data Linked. Depending on which level of "Sharing" we used to create the objects, the objects and data will be shared from Application Root or stored in each container.
Applications can be created only in an Application Root.
SQL> show con_name CON_NAME --------------- APPROOT
To install an Application you have to declare that you will start installing it, you must specify the name of the Application and you must specify the version of the Application. You can have several "Applications" in an "Application Container" as long as their name are different inside that "Application Container".
SQL> alter pluggable database application MyApp begin install '1.0'; Pluggable database altered.
After declaring that you are installing an "Application", all the next sentences are marked as part of the installation, here is where you start to create all the objects and data:
SQL> create user test identified by xxxx; User created. SQL> grant connect, resource, unlimited tablespace to test; Grant succeeded.
Metadata-Linked: A metadata link shares the database object’s metadata, but its data is unique to each container.
SQL> create table test.metadataLinkedTable SHARING=METADATA (name varchar2(20)); Table created. SQL> insert into test.metadataLinkedTable values ('Guatemala'); 1 row created. SQL> commit; Commit complete.
Data-Linked: A data link shares the database object, and its data is the same for all containers in the application container. Its data is stored only in the application root.
SQL> create table test.dataLinkedTable SHARING=DATA (name varchar2(20)); Table created. SQL> insert into test.dataLinkedTable values ('Costa Rica'); 1 row created. SQL> commit; Commit complete.
Extended Data-Linked: An extended data link shares the database object, and its data in the application root is the same for all containers in the application container. However, each application PDB in the application container can store data that is unique to the application PDB. Personally, I like to call this "Row-Linked" because some rows are stored in the Application PDB and some others in the Application Root, basically you are sharing a set of rows from Application Root.
SQL> create table test.extendedDataLinkedTable SHARING=EXTENDED DATA (name varchar2(20)); Table created. SQL> insert into test.extendedDataLinkedTable values ('Nicaragua'); 1 row created. SQL> commit; Commit complete.
To finish the installation of the Application the following sentence has to be executed specifying the Application's name and the version:
SQL> alter pluggable database application MyApp end install '1.0'; Pluggable database altered.
Excellent! So far we have created an “Application Container” containing 1 Application Root, 1 Application Tenant and 1 Application with 3 Tables: 1 metadata-linked Table, 1 data-linked Table and 1 Row-Linked Table.
The Application PDBs don't see the Application yet. This is because the synchronization is not automatically as we can see bellow:
Checking out if the Application PDB "AppPDB1" has the objects of the Application "MyApp":
SQL> alter session set container=AppPDB1; Session altered. SQL> show con_name CON_NAME ------------------------------ APPPDB1 SQL> select * from test.metadataLinkedTable; select * from test.metadataLinkedTable * ERROR at line 1: ORA-00942: table or view does not exist
Synchronizing Application PDBs
In order to sync an "Application" to an "Application PDB" you have to open a session in that specific Application PDB: SQL> alter session set container=AppPDB1; Session altered. SQL> show con_name CON_NAME ------------ APPPDB1
Then execute the following sentence specifying the Application's name:
SQL> alter pluggable database application MyApp sync; Pluggable database altered.
After to execute the "Application Sync" we will be able to see the objects and data depending on how the SHARING clause was used:
SQL> select * from test.metadataLinkedTable; NAME -------------------- Guatemala SQL> select * from test.dataLinkedTable ; NAME -------------------- Costa Rica SQL> select * from test.extendedDataLinkedTable; NAME -------------------- Nicaragua
Now let's see the difference between the sharing levels. In order to explain this I have to do some more inserts into the tables. All these inserts will be executed from the Application PDB "AppPDB1":
SQL> show con_name CON_NAME ------------------------------ APPPDB1
Insert #1:
SQL> insert into test.metadataLinkedTable values ('Mexico'); 1 row created.
Insert #2:
SQL> insert into test.dataLinkedTable values ('Canada'); insert into test.dataLinkedTable values ('Canada') * ERROR at line 1: ORA-65097: DML into a data link table is outside an application action
Insert #3:
SQL> insert into test.extendedDataLinkedTable values ('USA'); 1 row created. SQL> commit; Commit complete.
Explanation of Insert #1: This insert was executed against a medata-linked table, the result is that the insert was accepted from the Application PDB and the row will be stored in each Application PDB. This means for every row inserted while the Application was being created there will be a row in each “Application PDB” that is Synchronized, this is because the rows are unique to each “Application PDB”. We can confirm that by checking out the ROWID:
SQL> alter session set container=AppRoot; Session altered. SQL> show con_name CON_NAME ---------- APPROOT SQL> select c.con_id, p.name PDB_NAME, dbms_rowid.rowid_to_absolute_fno(t.rowid,'TEST','METADATALINKEDTABLE') file_num, t.name from test.metadataLinkedTable t, v$datafile c, v$pdbs p where c.file#=dbms_rowid.rowid_to_absolute_fno(t.rowid,'TEST','METADATALINKEDTABLE') and c.con_id=p.con_id; CON_ID PDB_NAME FILE_NUM NAME ---------- ---------- ---------- ---------- 5 APPROOT 38 Guatemala
This means that row is stored in the datafile # 38, and that datafile belongs to the container called "AppRoot", in this case is the "Application Root".
SQL> alter session set container=AppPDB1; Session altered. SQL> show con_name CON_NAME ------------------------------ APPPDB1 SQL> select c.con_id, p.name PDB_NAME, dbms_rowid.rowid_to_absolute_fno(t.rowid,'TEST','METADATALINKEDTABLE') file_num, t.name from test.metadataLinkedTable t, v$datafile c, v$pdbs p where c.file#=dbms_rowid.rowid_to_absolute_fno(t.rowid,'TEST','METADATALINKEDTABLE') and c.con_id=p.con_id; CON_ID PDB_NAME FILE_NUM NAME ---------- -------- ---------- -------------------- 6 APPPDB1 41 Guatemala 6 APPPDB1 41 Mexico
And now you see that the same row "Guatemala" was also stored in a different datafile, in this case the datafile #41 which belongs to the PDB caled "AppPDB1" which is an "Application PDB". Additionally the row "Mexico" is also inserted in the same datafile. This confirms that with this level of "Sharing" each container has its own data.
As you see there are two rows with the same value "Guatemala", one inserted in "AppRoot" and other stored in "AppPDB1", this is because every row here is stored in each container.
Explanation of Insert #2: In this case we tried to insert a row in a Data-Linked Table and we received an error. This is because in a Table using Sharing=Data (Data-Linked) there can be only those rows inserted in the Application Root in order to later sync the Application PDBs. No rows are accepted in each Application PDB.
Explanation of Insert #3: This insert was executed against an extended data-linked table (Row-Linked), the result is that the insert was accepted from the Application PDB and the row will be stored in that specific Application PDB because the “INSERT” operation was executed inside the “Application PDB”, if we had executed the “INSERT” from “Application Root” then that row had been stored in the “Application Root” and shared to the “Application PDB”. I tried to confirm this by using ROWID but I saw that ROWID cannot be used against an row-linked table, the following error is returned:
ORA-02031: no ROWID for fixed tables or for external-organized tables
So you can use the following query to confirm that some rows are returned from "Application Root" and some others from the local "Application PDB":
SQL> select con_id, owner, table_name, common_data from cdb_tables where table_name='EXTENDEDDATALINKEDTABLE' CON_ID OWNER TABLE_NAME COMMON_DATA ---------- ------ ------------------------- ----------- 6 TEST EXTENDEDDATALINKEDTABLE YES
The meaning of the column COMMON_DATA is the following:
SQL>select owner, table_name, column_name, comments from cdb_COL_COMMENTS where column_name like 'COMMON_DATA%' and table_name='CDB_TABLES' and con_id=1 OWNER TABLE_NAME COLUMN_NAME COMMENTS ------ ---------- --------------- ----------------------------------------- SYS CDB_TABLES COMMON_DATA Whether the table is enabled for fetching common data from Root SYS CDB_TABLES COMMON_DATA_MAP Whether the table is enabled for use with common_data_map database property
I had to get the definition from the data dictionary because those columns are not documented at the time in 12.2 public oracle database documentation (Database Reference Book), I already sent an email to oracle asking why 🙂
Conclusion:
So far you have seen an introduction to "Application Containers". We created an "Application Container", by default an "Application Root" was created, then we created an "Application PDB" and we installed and application with three tables. And finally we inserted some rows and we saw how "Sharing" levels work.
Start the discussion at forums.toadworld.com