Application Upgrade inside the Application Root Container

Published: Mon, 08/02/21

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

Now you have a good understanding of how the Application Upgrade works. Have you tried it yet? Click reply and let me know, I read every email!

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

Did you know you can test the course for 30 days with no risk?

Remember - sharing is caring...

Forward this email to a friend!

You're the best!

Sincerely,

Diana Robete

diana@dbaparadise.com