Application Upgrade inside the Application Root Container

In the previous post, we talked about application maintenance within an Application Root Container, specifically an application installation. As promised, let’s talk about application upgrade, find out what it is and how to perform it.

The application upgrade can be performed only on an existing application (that is obvious), and it involves changing the physical architecture of the application. During the upgrade you might add new tables, packages, users, alter tables, views…you get the idea, anything that would change the physical architecture of the application. You can also run DML statements.

The following three things are mandatory to be specified when upgrading the application:
1)the name of the application
2)old application version
3)new application version

The statement you use is similar to the one you used for installing the application, with the slight difference of BEGIN UPGRADE instead:

ALTER PLUGGABLE DATABASE APPLICATION hr_app BEGIN UPGRADE ‘v1.0’ to ‘v2.0’;

The application upgrade follows the same 4 steps as the installation, and as any maintenance operation:
1) login to the application root
2) start the maintenance process: upgrade
3) alter the application
4) end the maintenance process

Did you know that during the application upgrade process the application remains available, because Oracle clones the application root? The name of the container will be a random combination of letter and numbers, usually starting with the letter F: F3213955783_41_1. When you issue the BEGIN UPGRADE statement, the original version of the Application Root is cloned, and all the application PDBs belonging to the Application Root, are pointed to the cloned Application Root. When you issue the END UPGRADE statement, the Application Root is upgraded to the newer version, the Cloned Application Root is still at the original version and the Application PDBs are pointing to the Cloned Application Root. When you Sync the application PDBs, some of them will point to the upgraded Application Root, and some of the will still point to the Cloned Application Root. The fact that the Application Root is cloned, means that you must have the free space available to accommodate the clone. The Cloned Application Root will be present until all the application PDBs are synced with the application root, and the compatibility version is set. We’ll discuss this in an upcoming post.

Here is an example of the Application Upgrade process.

--Step 1 - login to the application root, in this case app_cont1
sqlplus / as sysdba

alter session set container=app_cont1;

--Step 2 - start the upgrade process
ALTER PLUGGABLE DATABASE APPLICATION hr_app 
BEGIN UPGRADE 'v1.0' to 'v2.0';

--Step 3 - create a new table and insert rows.
create table hr_user.test_table2 SHARING=DATA
(id number (5),
 description varchar2(20)) tablespace hr_app_data;

insert into hr_user.test_table2
values (1,'Test1');
insert into hr_user.test_table2
values (2,'Test2');
commit;

--Step 4 - end the upgrade process
ALTER PLUGGABLE DATABASE APPLICATION hr_app
end UPGRADE to 'v2.0';

You can check DBA_APPLICATIONS to confirm the version of the application, when connected to the Application Root container.

SET linesize 100
COLUMN app_name FORMAT A20
COLUMN app_version FORMAT A10

SELECT app_name, app_version, app_status
FROM   DBA_APPLICATIONS
WHERE  app_name = 'HR_APP';

APP_NAME             APP_VERSIO APP_STATUS
-------------------- ---------- ------------
HR_APP               v2.0       NORMAL

Since we ran an application upgrade, the application root container was cloned. Connect back to the CDB root and check the containers you have. If you try connecting to the cloned application root, you will receive ORA-65252 error.

connect / as sysdba

show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TESTDBPDB                      READ WRITE NO
... 
         6 APP_CONT1                      READ WRITE NO
         7 F3213955783_41_1               READ WRITE NO

SQL> alter session set container = F3213955783_41_1;
ERROR:
ORA-65252: cannot connect or ALTER SESSION SET CONTAINER to an application root clone

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get the August 2021 deal, only $16.99 CAD !