How To Patch Oracle Data Guard Using AutoUpgrade And Standby-First Patch Apply With Restart

Patching 3 Minutes

Let me show you how I patch my Oracle Data Guard configuration. I make it as easy as possible using Oracle AutoUpgrade. I reduce the interuption by doing standby-first patch apply with a primary database restart.

  • My Data Guard configuration consists of two databases:
    • SID: SALES
    • Databases: SALES_COPENHAGEN and SALES_AARHUS
    • Hosts: copenhagen and aarhus
    • Primary database: SALES_COPENHAGEN running on copenhagen

Preparations

You should do these preparations in advance of your maintenance window. They don’t interupt operations on your databases.

  • I download the patches using AutoUpgrade.

    • Create a config file called sales-download.cfg:

      global.global_log_dir=/home/oracle/autoupgrade-patching/download
global.keystore=/home/oracle/autoupgrade-patching/keystore
patch1.folder=/home/oracle/autoupgrade-patching/patch
patch1.patch=RECOMMENDED,MRP
patch1.target_version=19
patch1.platform=linux.x64

    • Start AutoUpgrade in download mode:

      java -jar autoupgrade.jar -config sales-download.cfg -patch -mode download
      • I can download the patches from any computer. It doesn’t have to be one of the database hosts, which typically don’t have internet access.

  • I verify all patches are standby-first installable and my configuration meets the requirements for standby-first patch apply.

  • I create a new Oracle home on all hosts.

    • Create a config file called sales.cfg:
      global.global_log_dir=/home/oracle/autoupgrade-patching/sales
patch1.source_home=/u01/app/oracle/product/19.3.0.0/dbhome_1
patch1.target_home=/u01/app/oracle/product/19/dbhome_19_26_0
patch1.sid=SALES
patch1.folder=/home/oracle/autoupgrade-patching/patch
patch1.patch=RECOMMENDED,MRP
patch1.download=no
      • Start AutoUpgrade in create_home mode:
      java -jar autoupgrade.jar -config sales.cfg -patch -mode create_home
      • AutoUpgrade also runs root.sh. It requires either:
        • oracle user has sudo privileges
        • Or I’ve stored the root credentials in the AutoUpgrade keystore
        • Else, I must manually execute root.sh.

  • Optionally, but recommended, I run an analysis on the primary database:

    [oracle@copenhagen] java -jar autoupgrade.jar -config sales.cfg -patch -mode analyze
    • Check the findings in the summary report.

Patching

Proceed with the following when your maintenance window starts.

  • Update listener.ora on the standby host (see appendix). I change the ORACLE_HOME parameter in the static listener entry (suffixed _DGMGRL) so it matches my target Oracle home.

  • I reload the listener:

    [oracle@aarhus] lsnrctl reload

  • Patch the standby database:

    [oracle@aarhus] java -jar autoupgrade.jar -config sales.cfg -mode deploy
    • I don’t disable redo transport/apply.

  • Optionally, test the application of patches using a snapshot standby database.

  • Downtime starts!

    • Perform draining in advance according to your practices.
    • Shut down your application.

  • Update listener.ora on the primary host (see appendix). I change the ORACLE_HOME parameter in the static listener entry (suffixed _DGMGRL) so it matches my target Oracle home.

  • I reload the listener:

    [oracle@copenhagen] lsnrctl reload

  • Patch the primary database (see appendix):

    [oracle@copenhagen] java -jar autoupgrade.jar -config sales.cfg -patch -mode deploy
    • I use the sales.cfg config file.
    • AutoUpgrade detects it’s running against the primary database, and executes Datapatch and all the post-upgrade tasks.

  • Verify the Data Guard configuration and ensure the standby database is receiving and applying redo:

    DGMGRL> show database SALES_COPENHAGEN;
DGMGRL> show database SALES_AARHUS;
DGMGRL> validate database SALES_COPENHAGEN;
DGMGRL> validate database SALES_AARHUS;

That’s it.

Happy Patching!

Appendix

Static Listener Entry

In this blog post, I update the static listener entries required by Data Guard broker (suffixed DGMGRL). My demo environment doesn’t use Oracle Restart or Oracle Grid Infrastructure, so this entry is mandatory.

If you use Oracle Restart or Oracle Grid Infrastructure, such entry is no longer needed.

Further Reading

Other Blog Posts in the Series

Published

14 thoughts on “How To Patch Oracle Data Guard Using AutoUpgrade And Standby-First Patch Apply With Restart”

  1. Hi Daniel

    Wonder if you can help , MOS has now included 2FA , how does this affect autoupgrade to be able to download patches ?

    when i set up the keystore it just fails

    Enter your secret/Password:Re-enter your secret/Password:MOS>MOS>MOS>MOS>MOS> saveConvert the AutoUpgrade Patching keystore to auto-login [YES|NO] ? YESMOS> listMOS Credentials Loaded – Connection FailureConnection Failed – You entered an incorrect user name or password.

    have i got this wrong ? any help would be appreciated

    Thanks

    michael

    Like

    Reply

    2. Hi Michael,

      According to our information, the 2FA change for My Oracle Support shouldn’t cause trouble for the download mode in AutoUpgrade. When you enter the credentials in the AutoUpgrade keystore, the credentials are validated. Any chance you 1) don’t have access to *.oracle.com from those servers, or 2) entered a wrong username/password?

      If the problem persists, please reach out to me at daniel.overby.hansen (a) oracle.com.

      Thanks,
      Daniel

      Liked by 1 person

      Reply

      1. Hi Daniel thanks for the reply I do have a support account and have tried the password logging into the site with 2fa and works fine . I will have another go at it and see if it my fault just needed confirmation that it has not affected it so thanks for getting back to me

        Like

        Reply

        1. Hi Daniel I do apologize the issue was I had ## at the end of the password the wallet did not like that for some reason thanks for all your help it’s working fine

          Like

          Reply

    1. Hi,
      You don’t have to manually run Datapatch when you use this method. Since AutoUpgrade see it’s running on the primary database, it’ll automatically execute Datapatch and all the post-patching steps.
      If you use the other standby-first method with a switchover – then it’s different – because there you run AutoUpgrade twice on a standby, and then a manual Datapatch run is needed (at least for now, until we come up with something smarter).
      Regards,
      Daniel

      Liked by 1 person

      Reply

  3. Hello Daniel,

    With the new -patch option in Autoupgrade, it all is a bit different than above, right? I noticed that for analyze, I had to: stop the redo apply, disable configuration, open the standby db in read only, open all PDBs (in read only). Then I could do the analyze without ‘dirty’ java errors.

    After analyze had to revert things back. Is it enough to do analyze on primary only? Then create_home on both nodes, and do deploy on (standby first) both nodes?

    Paul

    Like

    Reply

    1. Hi Paul,

      There’s no use of running an Analyze on the standby database. We don’t have any checks specific to the standby, and it can’t run unless you open the database (with all the extra things you mention).
      So, just run analyze on the primary and that’s fine.

      Then, you can run -mode create_home on all hosts to all the new Oracle home, and eventually move on with patching in your maintenance window.

      I’ll update the blog post to reflect this.

      Regards,
      Daniel

      Like

      Reply

          1. One thing I just discovered. We made in the past changes to $ORACLE_HOME/sqlplus/admin/glogin.sql. Those are lost after using the AutoUpgrade for patching. You may consider those to be copied to the new home.

            Furthermore, on patching with Physical standby in place. We pre-created the Oracle Home. Then we did standby first patching (just a few seconds, like shutdown + startup mount). After that, the primary patch, which took longer of course, as the datapatch needed running. But all together, we are satisfied, especially now the Oracle Home is clean and fresh.

            Paul

            Like

            Reply

            1. Hi Paul,
              I’m glad to hear that you found the procedure useful and that it ran without problems. Good job!

              Now, as for glogin.sql, can you drop me an e-mail (daniel.overby.hansen (a) oracle.com) with a few more details? Which commands did you run and at what time did you expect that we should copy glogin.sql?
              I think I need a few more details to sort it out.

              Thanks,
              Daniel

              Like

              Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.