Oracle database patching automation scripts

This is a blog post I had on my To Do list for quite some time. I finally found some time to write about it. I will try to keep it short and clear. The idea was to learn Ansible and, at the same time, make something valuable.

The module will use opatchauto if the Oracle home being patched is grid infrastructure, otherwise, it will use standard opatch steps. Note: If an error is encountered and you restart the process, the module will not automatically start previously stopped services. The module will note stopped services at the beginning of the process and it will leave the services stopped at the end of execution.

oracle database patching automation scripts

In other words, if you restart the Ansible process do not expect it to continue from where it stopped. As of now, however, there is no such option. At the end of the patching, the log file is copied over to the control machine from where the patching started.

So, if you patch multiple nodes you will get all log files. This is required because in some most cases OPatch will prompt ask questions. You can download all playbook files along with the module from here GitHub. Feel free to test and use the module and let me know if you need any assistance or encounter some bugs.

Is there room for improvement? As final note, here is an example where I have patched 18c binaries with patch: — Database Release Update Interested in working with Ivica? Schedule a tech call. Also, there are now changes many in the community have noticed with Oracle patching….

Ansible as automation framework, one of its benefits is that it gives you more control of how you structure the tasks steps. It makes whole management of the playbook much easier and in more readable. Changes to the steps can be short and easy, does not require code changes etc.

I am talking here in general for Ansible. My point is, the outcome would be the same regardless if you use shell script or Ansible approach. I think I have also encountered some problems if ownership is not correctly set.

The module does not cover that part updating OPatch and changing patch binary files ownership. It expects that to be already configured properly. Your email address will not be published. Other brands, product and company names on this website may be trademarks or registered trademarks of Pythian or of third parties. Use of trademarks without permission is strictly prohibited. Official Pythian Blog. Data Warehouse Migrations.Patching has always been one of the many joys or dreads of Oracle DBAs.

Ever since the initial days of Oracle, patches have been published by Oracle Support, downloaded by DBAs very early on, via posted floppy disks or CDsand then applied to the target database—whether to fix minor database bugs; apply security fixes; or upgrade the database from one patch version to the other, such as from 7. With Critical Patch Updates CPUs now published quarterly by Oracle and with several companies seeking to make application of CPUs mandatory for all their databases as part of their corporate security policy, the onus is on DBAs to make sure the myriad databases in their domain are patched in a controlled and timely manner.

In the case of Oracle Applications, patching takes on a whole new meaning: Oracle E-Business Suite patch sets are a superset of database patch sets; frequently take many hours to apply; and, in many cases, need restarting if their worker processes fail. But this still translates into late nights for many DBAs who apply the patches manually and are obliged to keep checking for errors, in case they have to restart the patch.

The free-thinking human mind has never liked machinelike monotony, and sooner or later, subconsciously or not, it is bound to commit an error in any repetitive manual process. The fact is that DBAs are not machines. All in all, DBAs did have a dreary future filled with continually increasing patching drudgery until Oracle Enterprise Manager came along. Oracle Enterprise Manager Grid Control achieved this by streamlining and automating many day-to-day DBA tasks—performance management diagnosis and tuning ; creation and execution of scheduled database backups; execution of scheduled scripts at the OS and database levels; creation and use of Oracle Data Guard standbys; and controlled configuration management of the server, the OS, and the database—and also by tackling head-on the necessary bugbear of database patching, the subject of this article.

To give you the best results for these varied tasks, your Oracle Enterprise Manager Grid Control site must be properly architected. For an overview, see "Grid Control Architecture for Very Large Sites ," which explains how a large central Oracle Enterprise Manager Grid Control site was able to manage and monitor to targets, including databases, servers, and listeners.

This article is based on the real-life experience of a major financial institution in Southeast Asia and explains how a proof of concept POC was conducted to convince the management and DBAs of the benefits of database patch automation with Oracle Enterprise Manager Grid Control for thousands of databases. And besides, why would you use anything other than an Oracle product to manage Oracle Database instances?

The home page of the console appears as in Figure My Oracle Support refresh job has not run successfully in 72 hours. Since the site was down, the job had not run for 72 hours. The job downloads the metadata of the latest patch advisories, products, and product versions that are available from My Oracle Support.

It also computes the targets for the latest Critical Patch Updates. You must be sure to run this job before any patching is attempted with Oracle Enterprise Manager Grid Control.

oracle database patching automation scripts

To run the job immediately, create a copy. The job status shows that the job is running. If the Oracle Enterprise Manager Grid Control site does not have an internet connection to My Oracle Support many companies restrict internet accessoffline updates can be performed. To understand how to do this, look at the patching setup by clicking Setup at the top corner of the screen and then selecting Patching Setup. Figure 2. Click the Test button to verify that your login was successful.

You can select either a direct connection to the internet or, if your company allows connection to the internet via a proxy server, Manual Proxy Configurationwhich will let you use a proxy server host for both http and https.

Supply a username and password for each server. Finally, you can test the reachability of the agent via the displayed URL. On the second tab, Online and Offline Settings see Figure 3you select either the online or offline mode for patch advisories. The Oracle Enterprise Manager Grid Control servers may not have access to the internet, either directly or via a proxy. The offline mode is available for such cases.This is a topic I wrote about in the past understanding Oracle patching and new version numbering.

But now, after the new patching concept since Just to sum things up, Oracle has decided to abandon the concept of a few releases for each version e. So, 12cR2 was introduced with This is super important for support issues.

Since As you can see in MOS note Support for 18c will not be extended as well and will end in June Version 19c will be the long term support and will be supported until March with extended paid support until March According to multiple Oracle resources, it seems that the same concept will stay in future versions.

So expect to see Oracle 20c next year and 21c in the following year as a short-term support versions while 22c will be the long term support version. If you plan to upgrade your database now from an earlier version, consider to go straight to 19c, and plan for 22c in the future. With Oracle Windows patching has not changed and is still called Bundle Patch BP.

Database patch sets are released once a quarter January, April, July and October. There are 2 patch types available now:. If you wish to install Oracle This is what you should know about each patch on that list I took You might also hit specific bugs or issues in your environment.

One-off patches can be of any type, you should check their readme file. For each patch RU, RUR, one-off you should review the readme and follow the installation instructions. Standby environment assuming serverA is currently the primary and serverB is currently the standby :. Before starting up the primary in upgrade mode, the standby has to be mounted instead of open until all changes are applied.

I never realized myself of the technique you described for patching a non rollable patch on a RAC. Although simple and straightforward I never see it at least so well described before. Thanks a lot! Your email address will not be published. Notify me of follow-up comments by email. Notify me of new posts by email. Lately I had a weird problem with one of my customers regarding timezone in the database. Oracle Database Versions, Support and Upgrades Just to sum things up, Oracle has decided to abandon the concept of a few releases for each version e.

There are 2 patch types available now: RU Release Update — this is the major patch and it includes a large set of fixes. When installing RU you will get the 2nd digit of your database release changed.

For example, RU of July for 18c is The next one October is The same patch July for 19c is RUR Release Update Revision — this is a smaller patch set and it includes a smaller set of changes only security and regression fixes since the last RU.Register and Participate in Oracle's online communities. Learn from thousand of experts, get answers to your questions and share knowledge with peers. But what about the database? How can I find out if the corresponding sql has been applied? I've an environment Linux with If I create a new database regardless if I use a predefined default template like "General Purpose" or create a "custom database" I cannot see that the PSU has been applied.

My theory is that the predefined template shouldn't have the patch applied while the custom database should. You must execute the steps in Section 3. Can you verify the datapatch logs? I expected that the bundle patch stuff is applied by DBCA, but it was not. I needed to run ". It also described the background of this issue. My assumption is: why should the dbca NOT use the new sql scripts as they should replace the original ones. The DBUA uses the " catctl. The DBUA uses catcon. In previous releases this was not an issue as catbundle.

But as datapatch. The DBUA in Error: You don't have JavaScript enabled.

oracle database patching automation scripts

This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript back on and reload this page. Welcome to Oracle Communities. Please enter a title. You can not post a blank message.

Please type your message and try again. This discussion is archived. My actual problem: I've an environment Linux with This content has been marked as final. Show 6 replies. Hi, 1. Regards Stefan. Hi Stefan the question is: is the patch not applied or is it applied but not shown?

Go to original post.Oracle DBA Automation Scripts shows you how to build the scripts that automate the daily operations of Oracle database management.

ORACLE DBA Telephonic Round Interview

You'll learn the command differences between these operating systems and how to write scripts to integrate SQL with your OS. Whether you're an aspiring DBA or you have a few years under your belt, you'll be able to use the commands and scripts presented here to install, operate, manage, and tune Oracle databases. Enter your mobile number or email address below and we'll send you a link to download the free Kindle App.

Then you can start reading Kindle books on your smartphone, tablet, or computer - no Kindle device required. To get the free app, enter your mobile phone number. This book provides valuable, time-tested scripts for any DBA to automate the daily administration activity of an Oracle database. It provides scripts for Space management, Backup and recovery, tuning and other handy scripts locking, user reports, resource usage etc. The title highlights the need to identify and fix potential problems before they can cause loss of data, and in turn loss of business and user productivity.

Our book will outperform the competition by providing more scripts and listing and explaining the syntax and implementation of each script for Unix and Windows platforms. More importantly, he spends his days solving the problems of experienced enterprise-level DBAs and knows the obstacles they face.

Would you like to tell us about a lower price? If you are a seller for this product, would you like to suggest updates through seller support? Read more Read less. Kindle Cloud Reader Read instantly in your browser. From the Back Cover This book provides valuable, time-tested scripts for any DBA to automate the daily administration activity of an Oracle database. Tell the Publisher!

I'd like to read this book on Kindle Don't have a Kindle? Customer reviews.

Oracle Automation-Applying PSU patch in Oracle 12c Database Using Ansible Tool

How does Amazon calculate star ratings? The model takes into account factors including the age of a rating, whether the ratings are from verified purchasers, and factors that establish reviewer trustworthiness.

Top Reviews Most recent Top Reviews. There was a problem filtering reviews right now. Please try again later. This collection of scripts cover all of the major DBA functions. It will give you a ready made collection of the basic scripts, which you can modify and perfect as your experience grows. I like this collection because it allowed me and my team to bundle production ready scripts into an application that we developed for internal company use.

Although me and most of my team were DBAs in the past out personal script collections were out of date. We bought this book, tailored the scripts we needed to production tasks to support our application and were able to release a turn key application to production.

Since the scripts were provided on CD ROM and were relatively bug free this book saved us hundreds of development hours and allowed us to deliver a system that had a higher value than one that is tossed into production. If you're a new DBA this book and the scripts that come with it will kickstart your tool library.

If you're a developer that is building an Oracle application and want to deliver a complete solution this book will save time and money. This is a superb book. Some scripts have really nice value added; for example the backup script provided also creates a file for restoring files backed up.

This is not a book for teaching you how to write scripts although it has a section devoted to itbut it's clear and simple style make it a really good help for your DBA needs. One person found this helpful. I bought this book as the author claims that it caters to the requirements of DBA's of different experience levels. I am involved in all phases of project developement. This book helped me in the tasks of the database creation,database maintenance and performance tuning.Thanks to Martin Berger for the information.

This approach is much better than the previous one, but still has some pitfalls:. This approach is very close to the previous one, with the exception that the new Oracle Home is not installed from scratch, but rather cloned from an existing one. This way, the rollback scripts used by the datapatch binary will be there and there will be no errors when patching the databases.

The procedure for Oracle Home cloning is described in the Oracle Documentation, here. Another cool thing is that you can clone Oracle Homes across different nodes, so that you might have the same patch level everywhere without repeating the tedious tasks of upgrading the opatch, patching the binaries, etc. But still, you have to identify which Oracle Home you have to clone and keep track of the latest version.

The best approach would consist in having a central repository for your software, where you store every version of your Oracle Homes, one for each patch level. You can, for example, use only one server to do all the patching and then distribute your software images to the different database servers.

This is the concept of Golden Images used by Rapid Home Provisioning that will be in the scope of my next blog post. Now that we have seen some Oracle Home patching approaches, is it worth to know which patches are important in a patching strategy.

Apply database patch from OEM Cloud control

I will make the assumption that in every case, the critical patches should be applied quarterly, or at least once per year, in order to fix security bugs. Prior to From Sometimes Bundle Patches and Release Updates contain cool backports from the new releases; sometimes they contain just more bug fixes than the PSUs and RURs; sometimes they fix important stuff like disabling bad transformations that lead to wrong result bugs or other annoying bugs.

It does not matter your patch frequency: sometimes you hit a bug, and the only solution is either to apply the one-off patch or the workaroundif available.

If you apply the one-off patch for a specific bug, from an Oracle Home maintenance point of view, it would be better to.

There is, however, the alternative to one-offs: implementing the workaround instead of applying the patch. It might be a good idea to use the workaround instead of apply tha patch if you already know that the bug will be fixed in the next Release Update for exampleor that the workaround is so easy to implement that it is not worth to create another version of Oracle Home that will require special attention at the next quarter.

If you apply workarounds, anyway, be sure that you comment EXACTLY why, when and who, so you can decide to unset it at the next parameter review or maintenance… e.This chapter explains how you can patch Oracle Database. In particular, this chapter covers the following:. This section helps you get started with this chapter by providing an overview of the steps involved in patching Oracle Database. Consider this section to be a documentation map to understand the sequence of actions you must perform to successfully patch Oracle Database.

Click the reference links provided against the steps to reach the relevant sections that provide more information. Know how the Deployment Procedure functions, what use cases it covers, and so on. To learn about the releases supported by the Deployment Procedure, see Supported Releases.

If it has, then choose to patch in online mode. Otherwise, choose to patch in offline mode. Before you run any Deployment Procedure, you must meet the prerequisites, such as setting up of the environment for online or offline patching, applying mandatory patches, setting up of Oracle Software Library, and so on.

To learn about the prerequisites for patching a standalone Oracle Database in online mode, see Prerequisites. To learn about the prerequisites for patching a standalone Oracle Database in offline mode, see Prerequisites. To patch a standalone Oracle Database in online mode, follow the steps explained in Patching Procedure.

To patch a standalone Oracle Database in offline mode, follow the steps explained in Patching Procedure. If the Oracle home of the database you are patching also has an ASM installed, then the Deployment Procedure patches only the database instance, but appropriately shuts down the ASM instance before patching the database and restarts it after the operation is complete. The Deployment Procedure leaves several. For example, with a databases named UPGRD1, the following files will be left in the oracle home after patching:.

You cannot avoid the creation of these log files. If you want to delete them, you can either navigate to the Oracle home directory and manually delete them or customize the Deployment Procedure to include a step to automatically delete them. This section describes how you can patch a standalone Oracle Database in online mode, that is, by connecting to My Oracle Support. This option is best suited when you have an Internet connection on the host where Enterprise Manager Grid Control is running.

Mandatory Infrastructure Requirements for Provisioning and Patching. Ensure that you run the patchability report to understand whether the Oracle Database you want to patch is suitable for this patching operation. In the report, if the Oracle Database you want to patch appears as a target that cannot be patched, then fix the target configuration issues to make it a target that can be patched. For more information about the patchability report, and to understand how you can diagnose the issues with an Oracle Database target and take corrective actions, see Appendix H, "About Patching Automation Reports".

Ensure that you use an operating system user that has the privileges to run the Deployment Procedure, and that can switch to root user and run all commands on the target hosts. For example, commands such as mkdir, ls, and so on. If you do not have the privileges to do so, that is, if you are using a locked account, then request your administrator a designer to either customize the Deployment Procedure to run it as another user or ignore the steps that require special privileges.

For example, user account A might have the root privileges, but you might use user account B to run the Deployment Procedure. In this case, you can switch from user account B to A by customizing the Deployment Procedure. Ensure that you are the owner of the Oracle home of the targets being patched.

If you are not the owner or if you do not have direct access to those targets, then customize the Deployment Procedure to run as another user. Then click Schedule Deployment. This default value refers to a location on the target host where the database to be patched is running.


thoughts on “Oracle database patching automation scripts”

Leave a Reply

Your email address will not be published. Required fields are marked *