Application Patching Inside the Application Root Container
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 !