Manage Learn to apply best practices and optimize your operations.

How to troubleshoot Oracle critical patch updates using OPatch

Read this expert tip on how to troubleshoot Oracle critical patch updates and protect your Oracle data with Oracle Enterprise Manager, OPatch and Oracle Grid Control.

In previous Q/As, you mentioned that you are careful when using the Oracle Grid Provision and Patching functionality. We are just getting started with it and would like to understand why you are cautious. Is there some particular database characteristic that causes problems? Are there specific things we need to avoid using Enterprise Manager to deploy patches?
Thank you so much for your question. I would like to amend my earlier statement by expanding the scope. Whenever you are about to apply critical patches to any data store, through any method, you should take extreme care. Data and connectivity are the lifeblood of any modern business, and corruption and disruption of either can result in a devastating loss of income, potential customers and can permanently tarnish a company's excellent reputation. A prudent approach to security and bug patching is necessary for those reasons, and to protect your data from a rapidly expanding threat. Hacking has made a full transition from mischievous techies to a lucrative -- albeit illegal -- business.

Oracle has been making great strides in improving the accessibility and ease of patch application through Enterprise Manager, Grid Control and the Provisioning and Patch Automation Pack. This tool can certainly make patching easy, fast and the patches can be scheduled for any time that best fits your processing profile and downtime tolerance. However, "fast and easy" is often misleading. The actual execution is fast and easy, but you MUST perform some comprehensive testing on a production representative system before you consider using the tool and applying the patch on your production data stores. I've personally used this tool on several platforms from Solaris to Windows, and several points in between – I cannot say it has been successful every time.

There doesn't seem to be a consistent issue, package or other object that causes problems with the application of a patch. I would attribute this to the focused nature of the Oracle Critical Patch Update (CPU) . Each patch remediates specific security issues and vulnerabilities in various packages and objects discovered during the course of the quarter. Remember that the CPUs are cumulative, so upon INITIAL application, any issue from a previous patch will manifest itself. However, after the initial application of a CPU, the previous patches are not usually re-applied and the same problems rarely present themselves again.

The second issue becomes, "What happens when something goes wrong?." While the Enterprise Manager does a good job of rudimentary tuning and statistical aggregation, for operational use, it tends to insulate the DBA from the details. If you are performing system-level maintenance in Enterprise Manager, it is CRITICAL that you understand what EM is doing behind the scenes, in case the results do not meet your expectations. Backup, recovery and patch application are excellent examples of activities where the underlying processes must be thoroughly understood. Another issue to consider is the manner in which Enterprise Manager makes the patches accessible. To automatically pull down the list of available patches from My Oracle Support (formerly Metalink), Enterprise Manager must have access to the Internet-at-large. This is not a trivial risk to accept, and your network, database and host security must be up to the challenge, as the weakest link will be the most likely, exploitable target. You can manually download and transfer the appropriate patch files, but this largely circumvents much of the tool's ease of use. This is a substantial tradeoff that you must consider.

Based on my experience, I always initially recommend the bundled, Oracle patch application tool: OPatch. I have had a 100% success rate with OPatch, and it is the tool I use exclusively when dealing with critical production systems. I like it for the following reasons:

  • It has an extremely small footprint, and is equally reliable.
  • It does not require Enterprise Manager for use. I've run into situations where the EM is unavailable due to resource constraints or repository issues. This has no effect on the operation on the database, and OPatch can be used to apply patches independently of the management tool.
  • Both the OPatch utility and the CPU files can be easily be submitted for Configuration Management.
  • OPatch provides a comprehensive, granular inventory of all patches applied to the database. This is incredibly handy for verification and compliance.
  • OPatch is easily incorporated in shell scripts (also easily brought under CM control) for efficiency and basic automation.
The key to using OPatch successfully is thoroughly reading ALL the patch release notes for the particular CPU you are installing. Like nearly all of Oracle's documentation, the patch notes are very comprehensive and contain everything you will need. Particular attention must be paid to the 'Post Installation' activities and the 'Known Issues' section BEFORE you begin. These often-overlooked items are crucial for avoiding problems. Also, you must not assume that every CPU's instructions will be the same as the previous. Oracle has done a fine job of standardizing CPU application, however they DO introduce changes to the process periodically. If you keep an eye out for these changes, it may save you some time and effort during testing.

Using OPatch may not be appropriate for some Enterprise data stores, but I have yet to run into the situation where it will not work. Your situation, however, may be different. By closely examining your need for the tool and the licensing fees, you may come to find that your funding is better spent on a junior to mid-level DBA, who can greatly benefit from experience of performing the patching. Unlike the management pack, an additional DBA can bring ROI in the form of tuning, management and eventually design and planning for your data stores all year long, as opposed to the quarterly use of management pack.

Whichever route you decide to take, remember:

  • Thoroughly read the documentation.
  • Perform comprehensive testing on representative systems.
  • Take level 0 backups.
  • Be familiar with the remediation/rollback process in case of unexpected results.
  • Perform all post installation actions.
By doing this, and leveraging Oracle's best practices, you increase your chances of success.

Dig Deeper on Oracle database security

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.