Test the Patch

---

Once you have a patch installed, it is time to test. You want to test not only to determine the success of the patch but also to determine if there were any unintentional side effects. Success of the patch will determine if you want to then migrate the patch to the other environments. Because of the complexity of the application, you will also want to determine if installing the patch had any unintended side effects in the product family that you are dealing with, or in any other product that you have implemented. Testing in connection with patches usually takes two tracks: technical testing and functional testing.

Technical testing is minimal. If ADPATCH ran to completion without obvious errors, you can assume that it passed that part of your test. But check the log files (e.g., the worker logs, the rebase and relink logs, the main log, the req files). Yes, if you have run a big patch that used several workers in both the C and D drivers, this can get to be a little tedious, but it is important that you at least run grep or a quick find in each of the files for the word "error" and the word "warning." There are times when the string "error" is legitimate (there are procedures that raise legitimate errors within the application and sometimes patches add functionality to these or repackage them into a jar file or a zip file); these can be safely ignored. Usually warnings are exactly that, a warning, and can also be ignored, but it is better to take note of them and make sure that there are no glaringly obvious problems that you see. Finally, you want to check for invalid objects in the database.

 Select count(*) from all_objects where status = 'INVALID'; 

While having invalid objects in the system is, to some extent, to be expected and accepted, it is best to keep as many valid as you can. There are several ways to validate database objects. One is to run ADADMIN and use the compile apps schema. This one will go through and compile all of the objects in the Apps schema. But it will only compile those objects belonging to Apps. And it does not always get all of the objects that it should. If you need to compile objects belonging to other schemas you can use the $ORACLE_HOME/rdbms/admin/utlrp script. This goes through and alters all objects that are invalid and compiles them. It still misses some, but it gets those in the Sys and system schemas as well. To determine which owner has invalid objects in the database, you can use the following query:

 Select owner,  object_name,  object_type from all_objects where status = 'INVALID' order by owner: 

You can use sql to create sql to try to compile more invalid objects by using the following script:

 Set head off; Spool compile_invalids.sql Select 'alter '||owner||'.'||'object_type||' '||object_name||' compile;' From all_objects where status = 'INVALID' Spool off @compile_invalids.sql 

There is also frequently a script located in the %AD_TOP%/sql/ directory called adcompsc.pls. It is a script that has often been included by Oracle Development to be one of the last jobs that a D driver runs. This script will allow you to compile all invalid objects in any given schema. To run the script, at a sql prompt on your Apps tier, run the following:

 @%AD_TOP/sql/adcompsc.pls <schema owner> <schema password> <case sensitive string that the objects start with that you want to compile> 

This would compile all objects in the apps schema (assuming that apps still has as its password apps) that begin with the "AD" string:

 @%AD_TOP/sql/adcompsc.pls apps apps AD 

This would compile all invalid objects in the apps schema:

 @%AD_TOP/sql/adcompsc.pls apps apps% 

There will always be some invalid objects in the database. There are often invalids in just an Oracle database; there can be several dozen invalid objects in an Oracle E-Business Suite database. If you look in the log file of a D driver, it will usually tell you how many invalid objects that you have before the patch starts and how many you have after it has finished. It will also likely produce a voluminous html file of all of the invalid objects that it found. Each patch that runs creates a uniquely named html file. It is unobtrusive and safe to run utlrp.sql periodically. Utlrp.sql is a script found in $ORACLE_HOME/rdbms/admin (%ORACLE_HOME\rdbms\admin) that recompiles invalid objects. Oracle recommends running utlrp after creating, upgrading, or migrating a database where running the upgrade scripts invalidates nearly every function, procedure, and trigger in the database. When you upgrade from 8i to 9i, utlrp can run for over 13 hours on a small Financials database. Because of the extensive work that a patch does in a D driver, it is also often recommended in the README that you run it as part of the postinstallation steps. Even if it is not recommended, if a patch that I have installed has a D driver that has performed more than a handful of jobs, I run utlrp just to make sure that as many of the objects in the database as possible have a status of VALID. Functional testing includes determining if the patch fixed what it was intended to fix. If it was a patch applied to fix a known existing problem, once it is applied, check. Did it fix that problem? If you are on a minipack or family pack patch implementation schedule and you are testing the installation of these patches, you may not have a known problem to test to determine if the patch fixed anything. This is okay.

Check to make sure that nothing else in the system has been affected by your installation. Because of the complexity and interconnected nature of the application, a patch applied in one product may have an affect on another product. While you cannot test the products that you do not have licensed, you can run a system test to make sure that everything your company uses, and all of the functions that they perform in their jobs perform at least as well as they did before the patches were applied. You may get lucky and see improved performance as a side effect.

Test customizations. Even patches that are not supposed to do anything with a product for which you have a customization may cause some of your customizations to quit working. Some of your interface tables may no longer line up exactly as they did before. A patch that is for a product, even a minor patch, has the potential to overlay a customized form or report. This is one of the inherent dangers of customizing the application. This is also one of the reasons to have a detailed list of all you have customized, as well as backup copies of everything that was customized and a detailed test plan that tests out everything thoroughly.

Patch Management

One more thing that deals with patching is patch management. For your own sanity, for the benefit of Oracle Support if you have to open an iTAR, for documentation purposes, and for knowing exactly what has gone on in your system, it is important that you keep some kind of document that says what patches have been applied in each of your environments. This does not have to be elaborate, only consistent so that you can see at a glance what you have (or have not) applied in each environment. Many companies make use of a spreadsheet similar to the one in Table 10.6. Make sure that you list every environment in the document. You can maintain this as a patch problem and resolution document, so that if you encounter a similar problem in the future you can quickly look back and see what you did to resolve the issue in the past. If you make this a tool rather than a task, you will find that it helps you in future planning and in being able to answer questions later that you never anticipated anyone ever asking. I have seen versions that maintain timing information for each driver, sign off, and test plan results added as an inserted comment to one of the cells.

Table 10.6: Patch Charting Spreadsheet

DEVL					TEST
Patch No.	Analyst	Date	Problem	Resolution	Analyst	Date	Problem	Resolution
12345	AJW	10/20/02	D driver hung	Restart	AJW	10/22/02	D driver hung	Restart

Creating a table or set of tables auxiliary to the application, but in the same database is another solution to this. If you build a simple table or set of tables that maintain the information that you have chosen to track in connection to patch management, you can easily maintain that information in each database as you patch. When you clone, that information will be accurate within the cloned tables. It will carry the date information with it and you can create a GUI front end for the tables with Access or with Forms to make maintenance and data entry into the table easier. You can use Access or Reports to enable you to pull out information on what patches are in each environment. Pulling information from across instances is less simple that just pulling up the information in one central spreadsheet. It is a choice that your situation can dictate.

I have seen suggestions that it is important to maintain a listing of files modified by patches, what versions of each file the patch brought with it, and what each patch replaced. This could be useful information to have at some point, but it could also become an incredibly unwieldy proposition. A minipack patch may have over a hundred files that it replaces. Some patches have as many as 25,000 or more jobs that run in the generate driver. If a patch replaces 25,000 files, it could become a full-time job just documenting what the patches did.

---