How to Apply

---

Now that you have the general idea of what a patch is, we can look at what is entailed in applying one. It is not difficult to apply an application patch; it is a little more difficult to try to diagnose why one fails, but usually just challenging enough to keep you on your toes.

Patch Search and Download

In order to apply a patch, you first have to get the patch from Oracle Support. To get the patches on your system, you have to download them from Metalink. Before you can download it, you have to find it.

After you log into Metalink, you will have a series of navigation buttons on the left hand side of the page. The Patches button will take you to one of two screens. You will either find yourself directly at a screen from where you can search for patches (Figure 10.1 shows the New Patch Search screen) or to a screen that allows you to choose between the New Metalink Patch Search and Old Metalink Patch Search (Figure 10.2 shows the screen that allows you to choose).

Figure 10.1: New Patch Search Screen

Figure 10.2: Old/New Patch Search Choice Screen

Which screen you get depends on when you are doing this and when more of the client base migrates to Internet Explorer 5.5. The Old Patch Search (Figure 10.3) was supposed to have gone away already and be replaced by the new functionality of the New Patch Search, but too many people were using browsers that would not support the functionality. At some point, however, this old feature will no longer be available and only New Metalink Patch Search will be available. Some information is easier to find through the old interface, so I am not looking forward to this happening.

Figure 10.3: Old Patch Search Screen

Old Metalink Patch Search provides you with a series of drop-down list boxes (lists of valid values based on the combinations of all previous list boxes) from which you can choose. From the processing of the chosen values, you will have returned to you a list of applicable patches. One of the biggest drawbacks is that there are often many patches returned that qualify for the combination and you still have to comb through these for the one patch that you want to apply. You always want to download the patch that is either generic or that has been built specifically for your OS and for each OS in your configuration, except possibly (for most Apps patches) your database tier if your database is the only thing on that tier.

The New Metalink Patch Search has different options, but is also more flexible. You are taken initially to a simple search screen. Three buttons — Advanced, Quick Links, Saved Searches — grace the top of the screen.

Quick links allows you to access the most commonly accessed patch sets in just a few quick clicks. Figure 10.4 shows the quick links for the RDBMS and Tools. Figure 10.5 shows the quick links for the 11i Oracle E-Business Suite.

Figure 10.4: Quick Links for Database and Tools

Figure 10.5: Quick Links for Oracle E-Business Suite

If you look at Figure 10.6, you will see that you can hover your mouse cursor over the link to drill out to translations for 11i patches and further to the translations that are already ported. You can get directly to the README in this manner and directly to the download screen for any of the OS-specific downloads as well.

Figure 10.6: Patch Language Translations Available

Clicking on the patch link will take you to a screen, much like the one in Figure 10.7, where you can choose the OS for which you can download the patch, link out to the README, and see the size and priority of the given patch set as well as any patch sets that include or supersede the patch that was investigated.

Figure 10.7: Patch Download

Below the three buttons on the simple search page (see Figure 10.8), you are given the option to search by patch number, product family, or saved search. Patch number is the default selection and beside the drop-down box is a box into which you can enter a comma-delimited list of patch numbers for which you are searching.

Figure 10.8: Save Your Searches

Choosing Product Family from the drop-down list of values box returns another entry box into which you can enter search terms, as well as a drop-down box for product or product family, and another for patch set or minipack. Figure 10.9 shows the screen that results from selecting Product Family from the drop-down box. Product or product family searching requires that you enter a search term in the entry box, although clicking on the small flashlight will launch you through another set of screens from which you can decide what products you want by short name, wild card, or by means of hunting through a hierarchy of product families. Searching is somewhat easier than just entering the search term.

Figure 10.9: Product Family Search

Figure 10.9 shows the search screen where you can choose the product family from the drop-down list and Figure 10.10 shows you the Search and Select: Product or Family screen. If you know what you are searching for, Figure 10.10 is the quickest way to get your information returned.

Figure 10.10: Search by Name

If you do not know exactly what you are looking for, only that you are looking for patches for your products, you might want to choose to search by hierarchy. If you are going to be applying patches on a set schedule, this may be a better option for you. Database and Tools (in Figure 10.11) presents you with not only RDBMS patches but network services, Discoverer, Developer, Apps Server patches (9iAS), Java technology, and Oracle System Management (OSM) to name just a few of the choices. At each level, you have the ability to drill down even further (by clicking on the + sign) until you get to the lowest level of granularity that is logical for your particular hierarchy choice.

Figure 10.11: Search by Hierarchy

As an example, Figure 10.12 shows that if you want to search for all receivables patches, you can simply select the link entitled "Financial Receivables Suite Family" and that will be populated back to the original search screen (see Figure 10.13). From there you can perform your search.

Figure 10.12: Expanded Hierarchy

Figure 10.13: Results Returned to Search Screen

This method may return more patches than you anticipated; however, the result set is also presented with the ability to sort the results to better meet your needs.

Looking at Figure 10.14, you can see that you can either select the entire product family, drill-down further, or only select the particular product that you want to find the patch for.

Figure 10.14: Drill-Down to Product

Figure 10.15 and Figure 10.16 show you a sample of a Receivables Family search. If you look at Figure 10.15 somewhat closer, you can see a "Hot" or high priority patch marked with a red "!" so that you can pick only the high priority patches if that is what you want to do. On the same screen, you are given the ability to save this search to be used later in the saved searches option of the search screen.

Figure 10.15: Receivables Family Search Result

Figure 10.16: Critical Receivables Patch

By saving the search (see Figure 10.8), you can narrow the search by qualifying exactly what you are looking for from it (e.g., what OS, patches updated in the last specified amount of time — days, weeks, months), what languages you need this to be searched for (or all languages), and other limiting factors that may be specific to your particular configuration.

Figure 10.12 shows the ability to further drill into the Receivables Family and select only the product that you are interested in patching. This will also allow you to limit the result set returned and segregate the patches that you are applying into their specific components. This is particularly important if you have installed individual components. You may not have to patch shared components as often or as intensively as you patch the fully licensed ones. An example of this might be if you implemented Cash Management for some reason, but opted to not license General Ledger or you might have licensed Receivables, but do not want to implement iReceivables.

One of the most useful features of this interface is the ability to save your searches and your ability to recall those saved searches later to save the time of waiting for reinserting all of that information. The more different modules that you have installed, the more useful you will find this feature. It has the potential to save considerable time and aggravation over your life as an Apps administrator. You can narrow your patch searches so that you receive those patches for your OS, those for the languages that you have installed, or those patches updated in the last <you define> days, weeks, or months.

What Is in a Patch?

Okay, you have gotten your patch or patches downloaded and unzipped or untarred or both (depending on your platform). What is in the resulting patch directory?

Unzipping the patch will create a directory that is named using just the number from the patch's zipped name (or the same as the bug number for one-off patches). That means that if you downloaded p1234567_11i_AIX.zip, you will have unzipped the file and the resulting directory will be called 1234567. In this directory you will find some combination of the following components.

README.txt and Sometimes README.html

The html version of this file contains the same information as the text version, simply formatted differently. Typically, README files are not read by many people. When dealing with Oracle E-Business Suite and the patches for the application particularly, it is important that you not only read this file, but any other README files that it directs you to. The 11.5.8 upgrade patch contained or directed you to nearly a dozen different documents. It is important that you read every one that applies to your configuration. It is advisable that you read all of the files pointed to by the core README just to make sure that you do not miss anything. The README files contain detailed information on what prerequisite patches are required for the current patch, preinstallation steps you need to perform if any, postinstallation steps that you need to perform if any, and, occasionally, things that you need to do after one of the driver files but before you do anything else in the patch. Manual steps are included, scripts to run if they exist, and any prepatch or postpatch patches that need to be applied. If the steps are not followed in the correct order, it may cause your patch to not install correctly, if it allows it to install at all. The README will often also contain new features brought to the application with the installation of the patch or any bugs that were fixed by the patch.

Driver Files

C1234567.drv

This is a copy driver. It copies files from the patch directory to their required location, relinks application executables, and repackages jar files and zip files if necessary. It extracts the files from the patch's C library using ADLIBOUT and replaces them into the patch structure using ADLIBIN. After the files are extracted, it compares the extracted version with the corresponding version in the product's directory structure (e.g., Forms, Reports, Graphics, and sql scripts) and determines if the existing file is as new or newer than the patch's version. If it is as new or newer, ADPATCH replaces that file into the library, leaving the existing version. If the file in the patch is a newer version, ADPATCH copies the old file to a backup directory and replaces it with the newer version. If the copy driver has to repackage jar files or zip files, it can take what appears to be an inordinately long time to run (an hour or more) and appear to be doing nothing while it is doing it. ADPATCH and the C driver will relink the relevant Oracle E-Business Suite products with the OS, with the database, and with other Oracle products and libraries. Finally, the C driver will copy any new html files or media files into their final respective destinations after copying any older versions of the same files into the backup directory.

D1234567.drv

This is a database driver. The driver will bring with it new versions of packages, error messages, tables or views, table columns, or other database objects. It also could contain data to populate a table or column. Details of what exactly this driver did can be culled from the resulting worker log file that you can comb through after the patch has run or you can monitor it as the patch is running. The tail -f adwork01.log command allows you to watch exactly what each worker is doing. ADPATCH, while running the D driver, determines what proposed actions a previous patch already performed and runs scripts or exec commands to make changes to the database objects. It will use invoker's rights if part of the patch is delivering a package. ADPATCH runs D drivers, by default, in parallel; the number of workers you allow ADPATCH to spawn determines the degree to which the parallelism is carried out that you allow ADPATCH to assign to the tasks.

G1234567.drv

This is a generate driver. It generates forms, reports, libraries, and message files. It utilizes parallel workers the same as the database driver does. You can monitor the progress of the G driver workers the same way you can the D driver workers. This driver should be applied to all APPL_TOPs on all nodes in your configuration.

.ldt File

Recent 11i patches include a file with the .ldt extension. This file contains metadata about the patch to be applied that will get uploaded via the FNDLOAD utility. The new prerequisite checking feature in ADPATCH loads that metadata and using the patch history tables from the database, determines if all of the prerequisite patches for this particular patch have been applied. Patches that include the .ldt files also include, in their C driver files, a line that states "compatible feature prereq" that prevents the patch from being applied against Oracle E-Business Suite using an older version of ADPATCH that does not support the prerequisite checking feature. It is not recommended that you run these patches if ADPATCH tries to prevent you from applying them; however, some README files will instruct you to apply them using the noprereq parameter when you start ADPATCH.

The prerequisite checking is only done during the C driver portion of the patch, but since you have to apply all drivers in a patch and the C driver has to be the first one applied, this prevents the patch from being applied.

Further, this new prerequisite checking will prevent you from installing a translation of a patch until the base version (U.S. version) has been applied. It will also inform you if it determines that a patch has already been applied (it is occasionally in error on this count) and asks you if you really want to reapply it.

Product Directories

Each patch will usually contain a product directory structure that mimics the product's directory structure under the APPL_TOP. This will be the location from which the copy (C) driver will copy the new files and will also be the location to which (under the APPL_TOP version of the same directory structure) it will copy it.

After you have run an ADPATCH session, you will usually also find a backup directory within the patch's directory structure. In here will be the original version of the copied files. If you run the same driver more than once without either restarting ADPATCH (restarting starts where the previous run failed as opposed to starting over from the beginning of the patch driver) or renaming the backup directory, you will overwrite whatever is in there from your original run with whatever the subsequent runs puts in. There are times when you will want to have access to the original versions of the program units that the patch is replacing. This is where they will be. If there is any way to back out any part of a patch, having the original programs in this backup directory will prove invaluable, as the patch knows exactly what it replaced.

New Checkfile Feature

Another new feature brought with later versions of ADPATCH is the new Checkfile feature. Checkfile helps to reduce the amount of time a patch takes to apply by comparing database actions that are to be taken by the patch with entries into a new table called the AD_CHECK_FILES table. When a patch is applied that takes advantage of the new feature, ADPATCH checks the actions to be taken by the patch, compares them to actions that are found in the table (complete with parameter, argument, and version checking) and if it determines that the actions that are supposed to be performed are really redundant, it makes the decision not to reapply those changes. After it performs the prescribed actions, it updates the AD_CHECK_FILES table with the information it just applied.

If a patch is Checkfile enabled, it will contain the line "compatible feature Checkfile." Further, all sql statements and EXEC commands will have as one of their parameters the Checkfile keyword. This parameter is what allows ADPATCH to make the comparisons and resulting decisions on whether or not to apply a change and later updates the table with the information that it applied those changes to the database.

Backup All Tiers of the System

I cannot say it enough. Backup the system (all tiers in the system) before you start a patch session. Do not just backup, though. Verify that your backup did not have any errors when it ran. This verification will take longer, but if you can go into a patching session knowing that you have a good clean backup that you can confidently restore from, you will be less worried about something unforeseen happening and you will have the security in knowing that you can always fall back to this point if something should go horribly wrong.

Install Patch

Okay, now it is time to look at physically installing a patch.

Have an environment where you install patches first and test them. It can be in your Vision system, if you have one, or it can be in Test. Where you test them is irrelevant, it only matters that you install them somewhere other than production first to test them. This is the environment into which you want to install them first.

Read the README file. Make note of prerequisite and postrequisite patches and apply them in the order that their dependencies require. Note extra steps that need to be performed and where those are supposed to occur. Most steps that are required are supposed to be done after the patch has been successfully installed. However, this is not always the case so do not get complacent. There are times when a patch says to perform some outside step after one of the drivers. If this is the case, there is a reason that it should be done at this time and performing this out of order can cause problems with the patch installation or later when you are testing.

If at all possible, install a patch on a clean copy of Production cloned into your other environments. This will allow you to attempt to duplicate any problem that you are seeing and will provide you with a picture of Production data against which to run your tests so that you can later verify the test results that you saw in Test or the Vision system with what you are able to reproduce in Production.

Make sure that you have a good, clean backup before you start patching. Uninstalling a patch is nearly impossible. The best you can hope for is a clean place to restore to as close as possible to where the patch failed.

Test Mode

If you want to see exactly what a patch is going to be doing, you can run ADPATCH in test mode and see exactly what it would be doing if you were actually applying the patch. It lists all of the files that it would have copied, every file that it would have relinked, every command that would have been executed, and every file that would have been generated if the patch had actually been run in the apply mode. This will not give you all of the errors that you are likely to encounter, that is why you apply the patch on as many copies of a Production-like environment as you can, but it will give you some idea of which of your customizations you are liable to clobber with the patch so you can get a jump on what you might have to do to fix it later. It could be a good tool to alert developers as to what they may want to be on the look out for. It is also a good test to double-check to see if ADPATCH will find something that is missing that you might have to do before you actually apply the patch for real. Pass in the apply=no parameter to accomplish testing the patch without applying it.

 Adpatch apply=no 

Other things that running a patch in test mode will not do is update patch history (patch information in the database), release versions, or the older applptch.txt file. It will not run any commands that will update the database in any way. It will not copy or archive any files, will not relink any executables, or actually regenerate any forms, reports, menus, or libraries. It will extract objects, validate the patch's integrity, and load any metadata into ADPATCH to enable it to do version and prerequisite checking.

Concurrent Managers need to be down when patching, other services should be down when patching, and users should be off of the system. Some patches say that they can safely be applied with the services up or users are on and active; others (especially those that deal with replacing DLLs — dynamic-link libraries) cannot. I feel more comfortable making sure that there are no extraneous things that can have any impact on the success of the patch installation or on the health of the system when it comes back up. It only adds an extra half hour to the patch installation time to bring down the services elegantly and bring them back up when you are done. It also gives you a measure of comfort that the patch did not have any side effects on the services. I make this part of my test plan for patch installation. Do all of the services come up cleanly after I have applied a patch?

Most Apps patches are applied using the ADPATCH utility. ADPATCH is a menu-driven utility that will help walk you through the application of a patch. It will prompt you for several pieces of information.

You will want to be in the patch's home directory when you start ADPATCH. It is not required, but it does tend to make the installation easier from a logic perspective. It also means less typing and less chance of making a mistake and incurring even more typing.

If you are on a Windows server, run %APPL_TOP%\envshell.cmd to set up your environment and create a DOS command prompt window. From here, change to the patch directory and run ADPATCH. If you are on a UNIX server, make sure that the environment setup is accomplished by running the <SID>.env file that is created in $ORACLE_HOME or $APPL_TOP. Once the environment has been set up, you can change to the patch directory and run ADPATCH.

Apply the drivers in the correct order. I do not know if Oracle planned it this way, but patch drivers are applied in alphabetical order. The C (copy) driver is applied first, the D (database) driver is applied next, and the G (generate) driver gets applied last.

Maximize your hardware utilization. If you have a UNIX machine as your database layer and you have 24 processors on that machine, Oracle suggests that you should allow 54 workers to run on that machine (Processors * 2.5) when running a D driver or G driver. But remember, you should know your hardware and take your preexisting knowledge into account. If you are running this patch during a time when there are likely to be active users on other Oracle databases or other processes actively running on the machine, you need to take this into account as well. Workers running their processes can be resource intensive. You also have to make sure that you have enough processes available because every worker that you create will spawn its own process at the OS level. If you do not have your available processes set high enough, you could find workers failing because they cannot create another process.

By default, ADPATCH validates all passwords for all schemas before it performs any driver actions. To save time, if you are applying several patches on a system where the Applications' schemas or their passwords are not likely to change, you can omit the validation by adding a parameter (options=novalidate) to the ADPATCH command.

 Adpatch option=novalidate 

Copy (C) drivers have to be run on every node in a multi-node installation, except the database tier if only your database is residing on that tier. Database (D) drivers need only to be applied once on any given system (i.e., once on Vision, once on Test, once on Production). The generate (G) driver needs to be applied to every node that the C driver was applied to. To be safe, run the C and G drivers on every APPL_TOP and the D driver only from the first one.

Steps in Installing a Patch

When installing a patch, you need to be running Auto Patch (ADPATCH) as the applmgr user. This user is the one with all authority to the application's services and usually one of the owners of the system. Once you are logged in with this user ID, you need to set your environment variables for the system that you intend to maintain. In UNIX, you could add the command to set your environment automatically in the .profile file and every time you log in, you will have your environment already set up for you. This applies if you do not have all of your Applications owned by the same ID.

The file that will set your environment in UNIX is typically found in either the APPL_TOP or the ORACLE_HOME for your environment and is called <SID>.env. If you are running in a Windows environment, it will be located the same place, but will be named <SID>.cmd.

Your patch should be downloaded into a common directory where all patches get placed. Once downloaded there, it should be unzipped. Keeping all patches in a similar file system or directory structure helps keep maintenance of Oracle Applications clean, centralized, and logical. It is important that you not use the patch subdirectories under the product top directory (e.g., AR_TOP, AP_TOP, HR_TOP) to download and install patches from.

Read all README.txt or README.htm files. It is important that you look at and review all README documents either contained within the patch or referenced by a README contained in the patch (these are usually links to documents on the Metalink site). README files will inform you of special steps that need to be taken when applying the patch, other patches that have to be in place for this patch to run correctly, and manual steps that you will need to perform to have the patch installation be completely successful.

Shut down all services on Applications' layers. Do not shut down the database (unless you are patching the RDBMS, which is never done through ADPATCH). This includes Apache, Forms Servers, and the Apps Listener, but most importantly, the Concurrent Managers need to be shut down for patching.

Change to the directory where the patch you are applying resides (into the directory created when you unzipped the patch) and run ADPATCH at the command line. Respond to all of the ADPATCH prompts. It will ask you for SYSTEM's password, Apps' password, the name you want to use for your log file, and other pieces of information that it needs to run. If you are running in interactive mode, you will need to provide it one driver filename each time you start ADPATCH. This means that if you have all three drivers in a patch, you will have to run three ADPATCH sessions, one after the other, to get all three drivers to run. Never try to run more than one ADPATCH session at a time.

After each driver finishes, it is important to review the log files that are generated by that driver. The main log file and the log files for all of the utilities that it spawns are located in the APPL_TOP\admin\ <SID>\log directory. The main log file will have the default name of ADPATCH.log if you have not renamed it. It may get quite large, if you keep the default with every run of ADPATCH and you have run several patches as these log files are only appended to, never replaced. Keeping them clean and manageable sizes becomes important when you are resolving problems. Examine all logs for warnings and errors. Usually, warnings can be safely ignored, but knowing they exist and doing a cursory search on Metalink for them will help ensure peace of mind later.

Once you have made sure that your logs do not show errors, you will need to check your customizations. If you have customized a form or a report and maintained its original name, it is quite likely that some patch (or many patches) will overlay the customization with its own version of the object. It will have made a backup of the original and placed it in the patch's directory structure in a backup directory for your environment. You may need to rebuild, reapply, or merge the customizations into the new object if you want to take advantage of that new object's new features. You may simply need to replace the new one with the old object and determine if the old functionality still works. If the patch has made structural changes on database objects, you may find that you will have to do work on the custom objects to get them to function after the patch.

If you are running MRC, you should always use ADADMIN to maintain the MRC schema if the patch you have applied has run a D driver. ADPATCH will display a reminder to do this if it has applied a database driver to the environment. It is important that you not skip this step as your MRC will likely not function as anticipated otherwise.

If you have run a patch that has a D driver, and alterations have been made to the database objects, you may want to run ADXGNPIN.sql and ADXGNPNS.sql from under AD_TOP/sql directory to pin new and altered packages and sequences in the SGA. If the SGA becomes fragmented there may not be sufficient room for larger packages or functions to be loaded into memory to run. Pinning packages, functions, and sequences into the shared pool makes sure that there is enough room for these program units to reside in memory. ADXGNPIN pins packages and functions (primarily from the APPS schema). ADXGNPNS pins sequences and program units from the other base products. Both take, as a parameter, the schema name or % for all schemas. If you are running MRC, there are some packages in the MRC schema that have to run with definer's rights. You will need to rerun the ADXGNPIN.sql script in that schema as well. If your Apps tier and your database tier are not on the same machine, you can copy these two sql scripts into an appsutil/admin directory that you create in the ORACLE_HOME so you can call these scripts automatically when the database is restarted.

If you have run a patch with a D driver, you may want to run ORACLE_HOME\rdbms\admin\utlrp.sql to recompile invalid objects. It is not necessary to do this. The invalid objects will probably compile the first time they are accessed, but your system will run faster immediately after the patching if you take the time to run utlrp. It can even be run while the system is up and the users are testing. Running this after every D driver or periodically during a long series of patches will ensure that you have as few invalids in your database as possible and limit the effects of invalid objects on the running of the application.

Restart the server processes, run preliminary tests to make sure that your system is running, and then allow the functional users access to the system to test that the new changes are both working and have not had the unanticipated side effects of breaking something else.

Once your testing is complete and everyone is satisfied that the patch runs as anticipated, you can clean up the backup files from your system. If you have room on disk, tape, or optical media, I advise you to archive these backups of replaced files for a period of time. Archiving in one central area allows you to quickly see what has changed and what history you have of all changes to the system. Once the patch is applied and the backups archived, you can safely recover used disk space by deleting the individual patch directories. Table 10.1 is an example of a portion of a log file for a D driver. First notice that all validation of passwords is complete, then all of the setup is done in connection to the FND_INSTALL_PROCESSES table, the driver gets processed, then elegantly (if all goes well) shuts down, and exits.

 ************* Start of AutoPatch session ************* AutoPatch version: 11.5.0 AutoPatch started at: Wed May 14 2003 04:36:04 APPL_TOP is set to d:\prodappl Please enter the batchsize [1000] : 1000 Please enter the name of the Oracle Applications System that this APPL_TOP belongs to. . . . Validating password for each schema… …. Connecting to ALR……Connected successfully. . Connecting to AX……Connected successfully. . Connecting to AK……Connected successfully. . Connecting to XLA……Connected successfully. . Connecting to GL……Connected successfully. . Connecting to RG……Connected successfully. . Connecting to FA……Connected successfully. . Connecting to HR……Connected successfully. …. Connecting to SSP……Connected successfully. . Connecting to BEN……Connected successfully. . . . . Creating FND_INSTALL_PROCESSES table… Connecting to APPLSYS……Connected successfully. CREATE TABLE fnd_install_processes(worker_id  number not null,  control_code varchar2(1)  not null, status varchar2(1)  not null,  context       varchar2(80) not null, pdi_product varchar2(10) not null,  pdi_username varchar2(32) not null, command  varchar2(30) not null, file_product varchar2(10) not null, subdirectory  varchar2(30) not null, 

 filename   varchar2(30) not null, phase     number not null,  install_group_num number  not null, skip_flag varchar2(1), arguments  varchar2(1996), phase_name   varchar2(30), start_time date, end_time      date, restart_time   date, elapsed_time  number, restart_count number,  defer_count number) TABLESPACE APPLSYSD initrans 100 storage(initial 4K  next 4K) CREATE UNIQUE INDEX fnd_install_processes_u1 on  fnd_install_processes(worker_id)  TABLESPACE  APPLSYSX storage(initial 4K  next 4K) GRANT ALL ON fnd_install_processes TO  APPS WITH GRANT OPTION Connecting to APPS……Connected successfully. Connecting to APPLSYS……Connected successfully. CREATE TABLE AD_DEFERRED_JOBS(phase        number not null,  file_product varchar2(10) not null, subdirectory varchar2(30) not null,  filename      varchar2(30) not null, arguments varchar2(1996),  start_time    date, restart_time  date, elapsed_time number,  restart_count number, defer_count number) TABLESPACE APPLSYSD initrans 100  storage(initial 4K next 4K) CREATE UNIQUE INDEX AD_DEFERRED_JOBS_U1 on AD_DEFERRED_JOBS  (phase,file_product,subdirectory,filename,arguments) TABLESPACE APPLSYSX  storage(initial 4K next 4K) GRANT ALL ON AD_DEFERRED_JOBS TO  APPS WITH GRANT OPTION Connecting to APPS……Connected successfully. Connecting to APPLSYS……Connected successfully. INSERT INTO fnd_install_processes (worker_id, control_code, status,  context, pdi_product, pdi_username, command, file_product, subdirectory,filename, phase, install_group_num, arguments) VALUES (0, 'W', 'W', 'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 0, 0,  rpad('-',100,'-')) 

 INSERT INTO fnd_install_processes (worker_id, control_code, status,  context, pdi_product, pdi_username, command, file_product, subdirectory,  filename, phase, install_group_num, arguments) VALUES (1, 'W', 'W',  'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 0, 0,  rpad('-',100,'-')) INSERT INTO fnd_install_processes (worker_id, control_code, status,  context, pdi_product, pdi_username, command, file_product, subdirectory,  filename, phase, install_group_num, arguments) VALUES (2, 'W', 'W',  'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 0, 0,  rpad('-',100,'-')) INSERT INTO fnd_install_processes (worker_id, control_code, status,  context, pdi_product, pdi_username, command, file_product, subdirectory,  filename, phase, install_group_num, arguments) VALUES (3, 'W', 'W',  'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 0, 0,  rpad('-',100,'-')) INSERT INTO fnd_install_processes (worker_id, control_code, status,  context, pdi_product, pdi_username, command, file_product, subdirectory,  filename, phase, install_group_num, arguments) VALUES (4, 'W', 'W',  'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 0, 0,  rpad('-',100,'-')) INSERT INTO fnd_install_processes (worker_id, control_code, status,     context, pdi_product, pdi_username, command,     file_product, subdirectory,  filename, phase, install_group_num, arguments) VALUES (5, 'W', 'W',  'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 0, 0,  rpad('-',100,'-')) 

 INSERT INTO fnd_install_processes (worker_id, control_code, status,  context, pdi_product, pdi_username, command, file_product, subdirectory,  filename, phase, install_group_num, arguments) VALUES (6, 'W', 'W',  'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 'UNDEF', 0, 0,  rpad('-',100,'-'))  . . . There are now 1 jobs remaining (current phase=A0):      0 running, 1 ready to run and 0 waiting. Reading completed jobs from restart file (if any). There are now 1 jobs remaining (current phase=A0):      0 running, 1 ready to run and 0 waiting. Connecting to APPS……Connected successfully. Starting worker processes. Worker process 1 started. Worker process 2 started. Worker process 3 started. Worker process 4 started. Worker process 5 started. Worker process 6 started. Writing jobs to run to restart file. Reading jobs from FND_INSTALL_PROCESSES table… . . . for details. Purging timing information for prior sessions. plus80 -s APPS/APPS @d:\oracle\ prodappl\ad\11.5.0\admin\sql\adtpurge.sql 10 1000 Done purging timing information for prior sessions. AutoPatch is complete. AutoPatch may have written informational messages to the file d:\oracle\ prodappl\admin\PROD\log\prod_d2554025_0514030436_ajw.lgi     You should check the file d:\oracle\ prodappl\admin\PROD\log\prod_d2554025_0514030436_ajw.log for errors. 

Noninteractive Mode

While it is common practice to run every patch interactively (answering every menu prompt requested by ADPATCH as it comes to the screen) this is not the only way to apply patches.

Noninteractive patching means that you supply a defaults file that ADPATCH reads. This defaults file has the answers to many of the prompts that you would be presented with in an interactive patching session.

To create a defaults file, you start ADPATCH as you would in an interactive session but in this case you pass in, as a parameter, the name that you want your defaults file to have. The defaults file has to be located in the %APPL_TOP%/admin/<SID> directory but can have any name that you choose to give it.

 Adpatch defaultsfile= %APPL_TOP%\admin\VIS\defaults.txt 

or

 Adpatch defaultsfile= $APPL_TOP/admin/VIS/defaults.txt 

You need to run ADPATCH, answering all of the questions up until the point where it asks you for the directory where the patch is located. At this point, type abort (the means by which you can get out of ADPATCH at any point) at the prompt and check to make sure that ADPATCH created the file with the specified name in the specified place.

If the file was not created, you will have to go through the same exercise again. If it was created, you can now apply patches one driver at a time or all of the drivers in a given patch at once using this defaults file.

To apply a patch one driver at a time, you would run ADPATCH passing in several parameters on the command line in a continuous string (do not hit enter when you near the end of the line).

 Adpatch defaultsfile=<path to the defaults file> logfile=<name of your log file> patchtop=<location where your patch resides> driver=<driver you want to run> workers=<number of workers you want to run> interactive=no 

From the above example, it would look something like this for applying patch driver d1234567 with a defaults file named defaults.txt on the Vision database creating a log file vis_d1234567_june-3-2002-900_ajw.log and assuming the patch to be located in the /patch directory structure in the subdirectory 12345678:

 ADPATCH defaultsfile== $APPL_TOP/admin/VIS/defaults.txt logfile=vis_d12345678_june-3-2002-900_ajw.log patchtop=/patch/12345678 driver=d12345678.drv work- ers=4 interactive=no 

If you want to apply an entire patch set noninteractively at one time and that patch has the standard structure (the patch's top directory has eight numeric characters in the name, the drivers are named c<8 numbers>. drv d<8 numbers>.drv g<8 numbers>.drv (c12345678.drv, d12345678.drv, g12345678.drv), you would use the following command line command:

 ADPATCH defaultsfile== $APPL_TOP/admin/VIS/defaults.txt logfile=vis_12345678_june-3-2002-900_ajw.log patchtop=/patch/12345678 workers=4 interactive=no 

This would run the entire 12345678 patch without requiring you to physically name every driver.

But what if your patch does not have the standard naming convention? You can still apply the patch in a similar manner, you would just pass in a comma-separated list of drivers to apply. For example, if the same patch (12345678) had drivers named c1.dri, d1.dri, and g1.dri, you would format the command as below:

 ADPATCH defaultsfile== $APPL_TOP/admin/VIS/defaults.txt logfile=vis_12345678_june-3-2002-900_ajw.log patchtop=/patch/12345678 driver=c1.dri, d1.dri, g1.dri workers=4 interactive=no 

This assumes that you are passing the parameters into the session in the C, D, and G order and will expect the driver files to have exactly the name and extension that you have passed in.

What if something in the noninteractive session errors out? Can you restart it? When ADPATCH is running noninteractive and it encounters an error, it exits immediately to the OS prompt and reports that it encountered an error. Once you have looked through all of the logs, found the error and resolved it (either with Oracle Support's help or independent of them), you can restart the patch (noninteractive again) by using exactly the same command (omitting or changing none of the input parameters) with restart=yes appended to the end. If ADPATCH sees restart=yes, it assumes that there was a patch running and all of its information is retained in the fnd_install_processes table and that you want to pick up at exactly the point where the patch failed. Do not use restart=yes if you are starting an entirely new patch session.

 ADPATCH defaultsfile== $APPL_TOP/admin/VIS/defaults.txt logfile=vis_12345678_june-3-2002-900_ajw.log patchtop=/patch/12345678 workers=4 interactive=no restart=yes 

Merge Patch

After you have been involved with patching Oracle E-Business Suite for any time at all, you will find that there are times when it can be patch intensive. Oracle provides a means by which you can merge several patches into one single patch that you will then apply as if it had been created as one patch. Applying several patches in this way will allow you to not only run fewer ADPATCH sessions to get through all of the patches that you need to at one time, but it will check to see if there are duplicate versions of any of the components that are common between the patches, find the newest versions of those components that are common, and only apply those. This minimizes potential downtime and impact to the business.

AD Merge Patch is the utility and process that will allow you to merge multiple uninterrelated patches into one all encompassing patch set. Located in the $AD_TOP/bin directory, this executable has, as its startup command ADMRGPCH.

It will allow you to merge dependent patches with each other, but it will not try to figure out what order components need to go in. That means you still have to read every README for every patch that you are planning on merging carefully. Some patches contain special instructions that need to be followed if that patch is to be included in a merged patch set or manual steps that AD Merge Patch will not execute or take into consideration.

Before you run Merge Patch, you need to create source and destination directories in your Patch Top directory. Your Patch Top is wherever, outside of the Apps directory structure, you have designated as the location from where your patches are installed. The source and destination directory can be named anything you want, but needs to preexist before you start your ADMRGPCH. session.

Copy all of the patches that you want to merge into the source directory and unzip them before you start your ADMRGPCH. session. If you have already unzipped them, you can either move the directories that they created into the source directory or unzip them again there.

Once all of these steps are done, you can run Merge Patch. Merge Patch takes the source directory, the target directory, and the proposed name (the default is "merged" if you do not provide this parameter) of the new patch as its arguments and will generate log files. It is important that you look at the log files for the ADMRGPCH. session before you try to apply the resulting patch set.

 Admrgpch d:\patches\my_source d:\patches\my_target my_merged_patch 

This will take everything in the source directory, merge all of the C drivers into one, merge all of the D drivers into one, and merge all of the G drivers into one. Any prepatch, interdriver, or postpatch steps that appear in any of the README files need to be performed at the appropriate times. If several of the README files refer to running ADADMIN to do any one particular step (e.g., recompile flex fields), you only need to perform this step once and it will recompile for all of the patches' changes.

The resulting patch can be applied either interactively or noninteractively just as any other patch. In the example it would be the my_merged_patch in the my_target directory of the patches directory on the D:\ drive on a Windows machine.

Restarting a Patch

If a patch fails or for some reason you had to abort the patch's installation, it is possible to restart ADPATCH at the point where the patch was stopped. Simply running ADPATCH again, if you have not run anything in the interim, will allow ADPATCH to restart. If there is an ADPATCH session, an ADADMIN session, or any other AD utility running in any other session connected to the application, those have to be completed or cancelled before you can restart ADPATCH. If another session of ADPATCH has run (or is running), the fnd_install_processes table may no longer be available or may contain information from another patching session. Fnd_install_processes table stores the information on what is happening at any time during the patching session. This table is what provides Oracle with the knowledge of where to restart a patch if there was an interruption in the patch's run. Therefore, it is important to make sure that if there is any chance of someone trying to run any of the AD utilities while you are trying to figure out why a patch failed and to fix the problem, that you make a backup copy of the fnd_install_processes table that you can name back when you are ready to finish running the original patch.

If the fnd_install_processes table exists and you are restarting the patch, ADPATCH will ask you, after you have restarted, if you want to continue the previous patching session. Answering yes and providing ADPATCH with a log filename (if you provide the same log name as before, it will be appended) will allow ADPATCH to pick up where it left off and complete. If you answer no and confirm that you do not want to complete the previous ADPATCH session, it will prompt you to confirm that decision and start a new ADPATCH session, first dropping the fnd_install_processes table that exists and creating a new one.

One caution that Oracle support makes is that if you try to run a copy driver that has encountered errors part way through, you may find inconsistencies in the installation and in the log files.

I would probably add one more caution, especially in rerunning copy drivers from the beginning. When ADPATCH runs, it creates a backup directory under the patch's top. The directory structure of this backup directory looks something like Table 10.2.

 12345678 backup     VIS          Visappl              AP                   11.5.0                       <subdir>                           file.fmx                       <subdir2>                           file.rdx              AR…….. 

If for any reason you need to find out what version of a form or a report or a PL/SQL statement a patch replaced, you can find out by combing through the backup directory. ADPATCH will put the components that it replaces in the same place in the patch's backup directory that it found them in the application's directory structure. Sometimes Oracle Support will ask for the version of the component that you replaced using the patch and this is where you would look for that information. But, if you have run the patch or part of the patch more than once, the original versions of these files are overwritten with the versions that the patch laid into place before it stopped.

Another thing that this backup directory is used for is if you have to attempt to back out a patch. More about attempting to back out a patch will be covered later in the chapter.

ADCTRL's Part in Patching

ADCTRL is a handy utility that allows you to monitor what state each worker is in during a patching session. Again, it is a menu-driven utility that is designed especially for ADWORKER monitoring and to allow you some freedom in directing what a worker is doing and to tell it what you want it to do. It is a simple but useful utility that provides you the opportunity to restart workers that have failed.

Many times, while you are monitoring worker progress, you will see that some workers fail while others continue to run. This happens for many reasons; one common one is that there are several workers running and sometimes the timing gets off between the different jobs and one takes longer than ADPATCH anticipates it taking. When this happens, sometimes dependencies get off between the workers and then something that one worker was anticipating being in place before it started is not there. Restarting the failed worker, in this case, will usually work. You may have to restart it several times, but eventually, you will get the worker to run. This happens frequently when you throw many workers at a big patch (which is what you should do, if you can) and one of the jobs is running longer than anticipated.

Self-Documenting Logs

When you use the ADPATCH utility, it asks you for a log name. Into this log (placed in the APPL_TOP/admin/<SID>/log directory) goes all of the details of the ADPATCH run. Each driver of the patch that runs via this utility creates its own log, unless you take the default log name (ADPATCH.log). Taking the default means that everything from every ADPATCH run for which you take the default name gets appended to the same log. This can result in extremely large log files.

This is not the only log file created by ADPATCH. When running a C driver, you will usually have a rebase and a relink log. You do not have the option to change the names of these logs when they are created. ADPATCH also creates an ADLIBIN log in a C driver. These logs are created when ADPATCH unloads the pieces of the patch; the results of the success or failure go into this log file.

D and G drivers, if they exist, will create worker log files. Adwork## is the naming convention of worker files. These files contain all of the information on what is going on in each worker. Again, the files are always created with the default names and there is no way, at creation time, to change these names.

Because all information on what is going on inside of the patches goes into the log files, they end up being important files when you are figuring out what broke in a patch that failed. For that reason as well as to keep file sizes manageable and to allow you to clean out old files from your default patch directory when you have installed several iterations of files, you can choose a logical name for your main patch log file when it is time to create it and rename the others when the patch has run its course.

When I run an ADPATCH session, I name the log file putting as much information into the filename as I can. If I were installing patch number 123456 and that patch had all three driver files (C, D, and G) in the Vision Application at 10:45 A.M. on June 3, 2002, I would name the patch using the following format:

 <SID>_<DRIVER>_<date and time>_<initials of installer>.log 

or

 vis_c123456_0603021045_ajw.log 

This format allows me to know what I installed, where I installed it, what time the driver file started, and who at my location installed the patch. From this information and the time stamp on the file, I can infer how long each driver took to run. I can quickly group together all of the drivers for a particular patch and I can see how many times I had to restart a patch before it ran successfully. Because of the limited size of the resulting file (although if you are running a big patch, the file can still become quite large), it is quicker and easier to find any errors that might have occurred. You also know that any errors that you find in a grep/find/search are all attributable to this patch and this driver.

But this only takes care of the default log file, not those auxiliary logs that are created by ADPATCH while it is running. Those I rename as soon as each driver is finished. I append to the beginning of each filename the detailed information that I put into the default log name so it is, again, easy to group files together that belong to the same driver. This means that, if I ran the D driver for the same patch at 11:20 A.M. using four drivers, I would have the following set of files:

 vis_d123456_0603021120_ajw.log adwork01.log adwork02.log adwork03.log adwork04.log 

After the rename of the worker logs:

 vis_d123456_0603021120_ajw.log vis_d123456_0603021120_ajw _adwork01.log vis_d123456_0603021120_ajw _adwork02.log vis_d123456_0603021120_ajw _adwork03.log vis_d123456_0603021120_ajw _adwork04.log 

While this is a lot of documentation, it is useful when you have been patching for 10 or 12 hours and you need to remember later what you did and what broke. It also will help you when you are looking at planning the time that it will take when you move from, for example, the Vision environment to the Production or Test environment. If you know how long it took to apply the patch in one environment (and you have the later advantage of knowing what might have broken and how you fixed it), you will be able to plan for how long you will have to have production unavailable. You will also have the details to back up your claims on the time required if anyone should question your estimates.

Backing Out a Patch

While it is possible to back out a patch, it is neither the desired course of action nor a simple one to take. It would be preferable from a time and effort standpoint and from a practical and safe perspective to restore to a recent full backup, if possible, and reapply patches from a known point in time. However, there may be times when backing out a patch is your only alternative. I would never suggest even attempting to back a failed patch out of Production. Taking frequent backups right before patching sessions allows you to safely and cleanly restore. This backup should include the entire applications file system and, if you are on a Windows platform, the registry as well.

One situation where you may find it necessary to back out rather than try to restore is if you are in a new implementation and you have finished the base installation of the Vision, Test, or Development system and are applying minor patches that will allow your system to be started the first time. You may not have taken a backup as soon as the main installation was finished, because you were not at a known point to which you could comfortably fall back. Restoring back to your backup would take you back to before you started and may take half a day or more and reinstalling would take an additional half day or more and your project team decides that backing out a patch would be the better course of action.

There is no automated means by which you can back out a patch, it is fully manual and the time required to reverse all of the actions a patch took increases with the size and complexity of the patch involved.

Backing out a C Driver

If possible, determine the cause of the failure. In the vast majority of cases, you will be able to resolve issues rather than having to resort to backing out patches. Resolving an issue that a patch is having, even if it means taking the time to work with Oracle Support through an iTAR is always the preferable course of action compared to backing out a patch.

If, after careful consideration, you find that you cannot resolve the issue, you will need to walk through the log files to determine what actions the copy driver took. Find out what files the patch copied and what update actions were performed by it.

When applying a patch, ADPATCH checks to see if a file that it is trying to put into place is more recent than the current file in the product's directory. If it is, it backs up the in-place version to the patch directory's backup directory in the structure layout that it found the file in under the system's directory structure.

If the files were originally in the Vision system's appl_top and were part of the AP product family and you were applying patch 12345678, then ADPATCH would backup the files in:

 $VISAPPL/<subdirectory path to the files>/<original files> 

to

 $PATCH/12345678/backup/VIS/visappl/ap/<same subdirectory path>/<original files> 

or

 $PATCH/12345678/backup/VIS/visappl/ap/11.5.0/forms /form1.fmx 

If there are not many files (and in some patches there can be dozens to thousands), you can use these backup versions (provided you have only started ADPATCH from the beginning once for the copy driver) to replace the versions that ADPATCH put in their place. Simply copy these backups to their original location.

Relink any files that the copy driver relinked. You can relink files with ADADMIN or ADRELINK (if they are AD programs that you need to relink, ADRELINK is the only way you can relink them).

Restore the previous apps.zip file. Apps.zip will be altered if the copy driver included any Java updates. Restoring this file can be done at the command line by invoking the ADJAVA utility as in the command below on a single command line with no carriage returns or line breaks:

 Adjava oracle.apps.ad.jri.adjcopy -masterArchive apps.zip -deltaArchives c12345678.zip -favorLowRevisions -mode Apply 

This command is case sensitive and tells adjava to regenerate the apps.zip file from the backup copy that the copy driver made (c12345678.zip for the copy driver c12345678.drv), replace all newer files by favoring the lower revision or all of the files contained in the zip file, and apply the changed apps.zip to where it belongs.

If there are Java files included in the patch, you will have to regenerate the jar files using ADADMIN's regen jar files menu option.

Finally, regenerate any forms, reports, graphics, or message files that might have been replaced by using the appropriate options in ADADMIN.

Restoring from a Failed D Driver

C drivers are complicated enough to back out. Database drivers make changes to the structure and workings of the database. This makes them even more complex to back out. It is important that you get a good database backup before applying a number of patches, minipacks, family packs, or maintenance packs. It is far simpler and safer to restore to before the patch than it is to back out the drivers.

Database drivers replace packages and procedures, alter structures within the database, and add functionality to the system. To try to recover from a failed database driver, you could carefully examine all of the log files, including all of the worker log files in an attempt to determine what caused the failure. If you can find out what caused it and there is a way to fix the cause, fix the issue that caused the failure and restart the driver.

If you cannot resolve what caused the driver to fail, the database must be restored from the most recent backup. If this is the case and there have been other database drivers run between the time of your most recent backup and the patch that failed, you will have to reapply the database driver portions of every intervening patch up to the one that failed in the order you originally installed them. This allows your database to match your application, patch for patch, and be viable to run up to that point, provided the C driver that is associated with the failed D driver had all of its changes successfully removed.

While the 9i version of log miner can reverse DDL (data definition language) with some effort, I do not believe it can reverse changes to packages and procedures and functions and triggers. These do not get written to the log files to begin with (except as a side effect of being put into the data dictionary from where the details can be retrieved via the data dictionary view), therefore, mining them back out would be nearly impossible.

As a last step, take a backup of your system at this point. Backing out the patch may not have been completely successful. Great care needs to be taken to undo everything that a patch did before you can have any confidence that backing it out was successful.

---