Application Patching Inside the Application Root Container

In previous posts we talked about Application Maintenance: Application Installation and Application Upgrade. The last category of maintenance is the Application Patching.

Application Patching is a minor change to the application, meaning that some type of DDL operations might not be allowed. You will not be able to execute the same operations as in an Application Upgrade. In general, new objects are allowed: functions, packages, views, tables, synonyms and others, and destructive operations are not allowed, such as dropping objects: tables, views, columns. That being said, I tested and I was able to drop a table I just created during the patch, but I was not able to drop an already existing table.

If you try dropping a table or a column, you will receive ORA-65270: operation is not allowed in an application patch. If you need to execute these type of operations, then you must run an application upgrade instead of a patch.

This is obvious, but it’s worth mentioning, that you cannot patch an application that is being upgraded or patched currently. You will receive the following error: ORA-65213: existing application action in progress

The Application Patching follows the same 4 steps process, same as the application installation or upgrade.

1) login to the application root
2) start the maintenance process: patch
3) alter the application
4) end the maintenance process

Let’s see an example:

--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 PATCH 101;
 
--Step 3 - create a synonym, a new table, insert data, all allowed

create or replace synonym testing2 for hr_user.test_table_2;

create table hr_user.test_table_3 as select * from hr_user.test_table_2;

insert into hr_user.test_table_2
values (3,'Test3');
commit;

--drop the newly created table. It works. 
drop table hr_user.test_table3;

--drop an existing table, it doesn't work
drop table hr_user.test_table_2;

ERROR at line 1:
ORA-65270: operation is not allowed in an application patch

--add a new column. It works.
alter table  hr_user.test_table_2 add modified_by varchar2(30);

--drop the new column, it doesn't work
SQL> alter table  hr_user.test_table_2 drop column modified_by;

ERROR at line 1:
ORA-65270: operation is not allowed in an application patch

--delete data, it works
delete * from hr_user.test_table_2;
commit;

--Step 4 - end the maintenance process

ALTER PLUGGABLE DATABASE APPLICATION hr_app
END PATCH 101;

If you are wondering how can we tell what patches we have installed for the application HR_APP, we query the DBA_APP_PATCHES view

select app_name, patch_number, patch_status
from dba_app_patches where app_name='HR_APP';

APP_NAME             PATCH_NUMBER PATCH_STAT
-------------------- ------------ ----------
HR_APP                        101 INSTALLED

During patching the application root container is not cloned. The application patch name and number must match in the begin patch statement and end patch statement.

Next time we will learn about synchronizing the applications with the application root after maintenance.

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 Back To School Deal, only $13.99 CAD !