Tag: standby-first

Introduction to Patching Oracle Data Guard

Here’s a blog post series about patching Oracle Data Guard in single instance configuration. For simplicity, I am patching with Oracle AutoUpgrade to automate the process as much as possible.

First, a few ground rules:

The Methods

There are three ways of patching Data Guard:

All At Once

  • You patch all databases at the same time.
  • You need an outage until you’ve patched all databases.
  • You need to do more work during the outage.
  • You turn off redo transport while you patch.

Standby-first with restart

  • All the patches you apply must be standby-first installable (see appendix).
  • You need an outage to stop the primary database and restart it in the target Oracle home.
  • During the outage, you have to do less work to do compared to all at once and less work overall compared to standby-first with switchover.
  • The primary database remains the same. It is useful if you have an async configuration with a much more powerful primary database or just prefer to have a primary database at one specific location.

Standby-first with switchover

  • All the patches you apply must be standby-first installable (see appendix).
  • You need an outage to perform a switchover. If your application is well-configured, users will just experience it as a brownout (hanging for a short period while the switchover happens).
  • During the outage, you have little to do, but overall, there are more steps.
  • After the outage, if you switch over to an Active Data Guard, the workload from the read-only workload has pre-warmed the buffer cache and shared pool.

Summary

All at one Standby-first with restart Standby-first with switchover
Works for all patches Works for most patches Works for most patches
Bigger interruption Bigger interruption Smaller interruption
Downtime is a database restart Downtime is a database restart Downtime/brownout is a switchover
Slightly more effort Least effort Slightly more effort
Cold database Cold database Pre-warmed database if ADG

Here’s a decision tree you can use to find the method that suits you.

Decision tree showing which method to choose

What If

RAC

These blog posts focus on single instance configuration.

Conceptually, patching Data Guard with RAC databases is the same; you can’t use the step-by-step guides in this blog post series. Further, AutoUpgrade doesn’t support all methods of patching RAC databases (yet).

I suggest that you take a look at these blog posts instead:

Or even better, use Oracle Fleet Patching and Provisioning.

Oracle Restart

You can use these blog posts if you’re using Oracle Restart. You can even combine patching Oracle Restart and Oracle Database into one operation using standby-first with restart.

We’re Really Sensitive To Downtime?

In these blog posts, I choose the easy way – and that’s using AutoUpgrade. It automates many of the steps for me and has built-in safeguards to ensure things don’t go south.

But this convenience comes at a price: sligthly longer outage. Partly, because AutoUpgrade doesn’t finish a job before all post-upgrade tasks are done (like Datapatch and gathering dictionary stats).

If you’re really concerned about downtime, you might be better off with your own automation, where you can open the database for business as quickly as possible while you run Datapatch and other post-patching activities in the background.

Datapatch

Just a few words about patching Data Guard and Datapatch.

  • You always run Datapatch on the primary.
  • You run Datapatch just once, and the changes to the data dictionary propagates to the standby via redo.
  • You run Datapatch when all databases are running out of the new Oracle home or when redo transport is turned off. The important part is that the standby that applies the Datapatch redo must be on the same patch level as the primary.

Happy patching

Appendix

Standby-First Installable

You can only perform standby-first patch apply if all the patches are marked as standby-first installable.

Standby-first patch apply is when you patch the standby database first, and you don’t disable redo transport/apply.

You can only use standby-first patch apply if all the patches are classified as standby-first installable. For each of the patches, you must:

  • Examine the patch readme file.
  • One of the first lines will tell if this specific patch is standby-first installable. It typically reads: > This patch is Data Guard Standby-First Installable

Release Updates are always standby-first installable, and so are most of the patches for Oracle Database.

In rare cases, you find a non-standby-first installable patch, so you must patch Data Guard using all at once.

Other Blog Posts in the Series

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

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 interruption by doing standby-first patch apply with a switchover.

  • 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.

  • interruption starts!

  • Switch over to SALES_AARHUS:

    DGMGRL> switchover to sales_aarhus;
    • Perform draining in advance according to your practices.
    • Depending on how your application is configured, the users will experience this interruption as a brown-out or downtime.

  • Update listener.ora on the new standby host (copenhagen). 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 new standby database (see appendix):

    [oracle@copenhagen] java -jar autoupgrade.jar -config sales.cfg -mode deploy

  • 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;

Post-Patching

  • Connect to the new primary database and execute Datapatch. You do that by calling AutoUpgrade in upgrade mode:
    [oracle@aarhus] java -jar autoupgrade.jar -config sales.cfg -mode upgrade

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

Avoid Problems on the Primary Database by Testing on a Snapshot Standby

One of the advantages of standby-first patch apply, is that I can test the patches in a production-like environment (the standby) before applying them to the primary. Should I find any issues with the patches, I can stop the process and avoid impacting the primary database.

Here’s an overview of the process.

For demo purposes, 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

How To

This procedure starts right after I’ve patched the standby (SALES_AARHUS). It runs out of the target Oracle home, whereas the primary database (SALES_COPENHAGEN) still runs on the source Oracle home.

  • Convert the patched standby to a snapshot standby:

    DGMGRL> convert database SALES_AARHUS to snapshot standby;

  • Test the patch apply by running Datapatch on the standby:

    [oracle@aarhus] $ORACLE_HOME/OPatch/datapatch
    • One always runs Datapatch on the primary database and the changes made by the patches goes into redo to the standby.
    • But, since I converted to a snapshot standby, it is now opened like a normal database and I can run Datapatch on it.
    • If Datapatch completes without problems on the standby, I can be pretty sure it will do so on the primary as well. The standby is after all an exact copy of the primary database.

  • Optionally, perform additional testing on the standby.

    • I can connect any application and perform additional tests.
    • I can use SQL Performance Analyzer to check for regressing SQL statements.
    • I can make changes to any data in the standby. It is protected by a restore point.

  • When done, convert the snapshot standby back to a physical standby:

    DGMGRL> convert database SALES_AARHUS to physical standby;
    • This implicitly shuts down the standby, flashes back to the restore point and re-opens the database as a physical standby.
    • All changes made when it was a snapshot standby, including the Datapatch run, are undone.

Continue the patching procedure on the primary database as described elsewhere.

Is It Safe?

Sometimes, when I suggest using the standby for testing, people are like: Huh! Seriously?

What Happens If I Need to Switch Over or Fail Over?

I can still perform a switchover or a failover. However, they will take a little bit longer.

When I convert to snapshot standby:

  • Redo transport is still active.
  • Redo apply is turned off.

So, the standby receives all redo from the primary but doesn’t apply it. Since you normally test for 10-20 minutes, this would be the maximum apply lag. On a well-oiled standby, it shouldn’t take more than a minute or two to catch up.

When performing a switchover or failover on a snapshot standby, you should expect an increase with the time it takes to:

  • Shut down
  • Flashback
  • Apply redo

I’d be surprised if that would be more than 5 minutes. If your RTO doesn’t allow for a longer period:

  • Get a second standby.
  • Consider the reduction in risk you get when you test on the standby. Perhaps a short increase in RTO could be allowed after all.

What Happens If Datapatch Fails

If Datapatch fails on my snapshot standby, I should be proud of myself. I just prevented the same problem from hitting production.

  • I grab all the diagnostic information I need, so I can work with Oracle Support on the issue.
  • Convert back to physical standby, which will undo the failed Datapatch run.
  • If I expect to solve the issue quickly, leave the standby running in the target Oracle home. Otherwise, put it back into the source Oracle home.

So, yes, it’s safe to use!

Happy testing

Appendix

Other Blog Posts in the Series

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

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

How to Perform Standby-first Patch Apply When You Have Different Primary and Standby Databases in the Same Oracle Home

I am a big fan of Oracle Data Guard Standby-First Patch Apply. You can:

  • Reduce downtime to the time it takes to perform a switchover.
  • Test the patching procedure on the standby database.

I received an interesting question the other day:

I have the following two Data Guard configurations. I want to patch all the databases using standby-first patch apply. How do I do that when I have primary and standby databases running out of the same Oracle home on the same machine?

Overview of Data Guard standby-first environment

Requirements

In this case, the databases are on 19.17.0, and the customer wants to patch them to 19.23.0.

To use standby-first patch apply, you must meet a set of requirements, one being:

Data Guard Standby-First Patch Apply is supported between database patch releases that are a maximum of one year (1 year) apart based on the patch release date.

Here are the release dates of the following Release Updates:

  • 19.17.0: October 2022
  • 19.23.0: April 2024

So, in this case, the customer can’t use standby-first patch apply directly. There is a year and a half in between. They need to patch cycles in this case:

  • Patch to 19.21.0 (release October 2023)
  • Patch to 19.23.0 (release April 2024)

In the future, they should apply patches more often to avoid ending up in this situation again.

Patching Oracle Home

The customer has one Oracle home on each server from where both databases run. On any server, there is a primary and a standby database (from two different Data Guard configs).

The customer uses in-place patching. If they patch the entire Oracle home, it means one of the primary databases is now on a higher Oracle home than its standby database, which is not allowed. The standby database is the only one which may run on a higher patch level.

Using the above configuration with primary and standby databases running out of the same Oracle home, you can’t use in-place patching and standby-first patch apply.

The customer must switch to out-of-place patching to achieve this. Then you can patch standby databases first, then the primaries.

Plus, you get all the other benefits of out-of-place patching.

Datapatch

Once all the databases in a Data Guard configuration run in the new Oracle home, you still haven’t completed the patching process:

A patch or patch bundle is not considered fully installed until all of the following actions have occurred:

  • Patch binary installation has been performed to the database home on all standby systems.
  • Patch binary installation has been performed to the database home on the primary system.
  • Patch SQL installation, if required by the patch, has been performed on the primary database and the redo applied to the standby database(s).

You must do the above steps in the specified order, and the last step is to execute Datapatch:

$ $ORACLE_HOME/OPatch/datapatch

Step-by-step

You can use AutoUpgrade to patch Oracle Data Guard.

Happy Patching!

How to Apply Patches Out-of-place to Oracle Grid Infrastructure and Oracle Data Guard Using Standby-First

I strongly recommend that you always patch out-of-place. Here’s an example of how to do it on Oracle Grid Infrastructure (GI) and Oracle Data Guard using Standby-First Patch Apply.

Standby-First Patch Apply allows you to minimize downtime to the time it takes to perform a Data Guard switchover. Further, it allows you to test the apply mechanism on the standby database by temporarily converting it into a snapshot standby database.

The scenario:

  • Oracle Grid Infrastructure 19c and Oracle Database 19c
  • Patching from Release Update 19.17.0 to 19.19.0
  • Vertical patching – GI and database at the same time
  • Data Guard setup with two RAC databases
    • Cluster 1: copenhagen1 and copenhagen2
    • Cluster 2: aarhus1 and aarhus2
    • DB_NAME: CDB1
    • DB_UNIQUE_NAME: CDB1_COPENHAGEN and CDB1_AARHUS
  • Using Data Guard broker
  • Patching GI using SwitchGridHome method

Let’s get started!

Step 1: Prepare

I can make the preparations without interrupting the database.

  • I ensure my environment meets the requirements for Standby-First Patch Apply.

  • I deploy new GI homes to all four hosts.

    • I use the SwitchGridHome method.
    • Very important: I only perform step 1 (Prepare a New Grid Home).
    • I apply the Release Update 19.19.0 as part of the deployment using gridSetup.sh ... -applyRU ... -applyOneOffs as described in the blog post.

  • I deploy new database homes to all four hosts.

  • I also recompile invalid objects. This can make it easier for Datapatch later in the process:

    PRIMARY SQL> @?/rdbms/admin/utlrp

Step 2: Restart Standby in New Oracle Homes

Now, I can move the standby database to the new GI and database homes.

  • On the standby hosts, aarhus1 and aarhus2, I first move the database configuration files from the old database home to the new one.

  • I change the database configuration in GI. Next time the database restarts, it will be in the new Oracle Home:

    [oracle@aarhus1]$ $OLD_ORACLE_HOME/bin/srvctl modify database \
   -db $ORACLE_UNQNAME \
   -oraclehome $NEW_ORACLE_HOME

  • I switch to the new GI on all standby hosts, aarhus1 and aarhus2, by executing step 2 (Switch to the new Grid Home) of the SwitchGridHome method.

    • It involves running gridSetup.sh ... -switchGridHome and root.sh.
    • You can perform the switch in a rolling manner or all at once.
    • The switch restarts the standby database instance. The standby database instance restarts in the new Oracle Home.
    • If the profile of grid (like .bashrc) sets the ORACLE_HOME environment variable, I ensure to update it.

  • If I had multiple standby databases, I would process all standby databases in this step.

Step 3: Test Standby Database

This is an optional step, but I recommend that you do it.

  • I convert the standby database (CDB1_AARHUS) to a snapshot standby database:
    DGMGRL> convert database CDB1_AARHUS to snapshot standby;
  • I test Datapatch on the standby database. It is important that I run the command on the standby database:
    [oracle@aarhus1]$ $ORACLE_HOME/OPatch/datapatch -verbose
  • I can also test my application on the standby database.
  • At the end of my testing, I revert the standby database to a physical standby database. The database automatically reverts all the changes made during testing:
    DGMGRL> convert database CDB1_AARHUS to physical standby;

Step 4: Switchover

I can perform the previous steps without interrupting my users. This step requires a maintenance window because I am doing a Data Guard switchover.

  • I check that my standby database is ready to become primary. Then, I start a Data Guard switchover:
    DGMGRL> connect sys/<password> as sysdba
DGMGRL> validate database CDB1_AARHUS;
DGMGRL> switchover to CDB1_AARHUS;

A switchover does not have to mean downtime.

If my application is configured properly, the users will experience a brownout; a short hang, while the connections switch to the new primary database.

Step 5: Restart New Standby in New Oracle Homes

Now, the primary database runs on aarhus1 and aarhus2. Next, I can move the new standby hosts, copenhagen1 and copenhagen2, to the new GI and database homes.

  • I repeat step 2 (Restart Standby In New Oracle Homes) but this time for the new standby hosts, copenhagen1 and copenhagen2.

Step 6: Complete Patching

Now, both databases in my Data Guard configuration run out of the new Oracle Homes.

Only proceed with this step once all databases run out of the new Oracle Home.

I need to run this step as fast as possible after I have completed the previous step.

  • I complete the patching by running Datapatch on the primary database (CDB1_AARHUS). I add the recomp_threshold parameter to ensure Datapatch recompiles all objects that the patching invalidated:

    [orale@aarhus1]$ $ORACLE_HOME/OPatch/datapatch \
   -verbose \
   -recomp_threshold 10000
    • I only need to run Datapatch one time. On the primary database and only on one of the instances.

  • I can run Datapatch while users are connected to my database.

  • Optionally, I can switch back to the original primary database on copenhagen1 and copenhagen2, if I prefer to run it there.

That’s it. Happy patching!

Appendix

Further Reading

Other Blog Posts in This Series

Can I Run Datapatch When Users Are Connected

The short answer is: Yes! The longer answer is: Yes, but very busy systems or in certain situations, you might experience a few hiccups.

The obvious place to look for the answer would be in the documentation. Unfortunately, there is no Patching Guide similar to the Upgrade Guide. The information in this blog post is pieced together from many different sources.

A few facts about patching with Datapatch:

  • The database must be open in read write mode.
  • You can’t run Datapatch on a physical standby database – even if it’s open (Active Data Guard).
  • A patch is not fully installed until you have executed Datapatch successfully.

How To

First, let me state that it is fully supported to run Datapatch on a running database with users connected.

The procedure:

  1. Install a new Oracle Home and use OPatch to apply the desired patches.
  2. Shut down the database.
  3. Restart the database in the new, patched Oracle Home.
  4. Downtime is over! Users are allowed to connect to the database
  5. Execute ./datapatch -verbose.
  6. End of procedure. The patch is now fully applied.

Often users move step 4 (Downtime is over) to the end of the procedure. That’s of course also perfectly fine, but it does extend the downtime needed and often is not needed.

What About RAC and Data Guard

The above procedure is exactly what happens in a rolling patch apply on a RAC database. When you perform a rolling patch apply on a RAC database, there is no downtime at all. You use opatchauto to patch a RAC database. opatchauto restarts all instances of the database in the patched Oracle Home in a rolling manner. Finally, it executes datapatch on the last node. Individual instances are down temporarily, but the database is always up.

It is a similar situation when you use the Standby First Patch Apply. First, you restart all standby databases in the patched Oracle Home. Then, you perform a switchover and restart the former primary database in the patched Oracle Home. Finally, you execute datapatch to complete the patch installation. You must execute datapatch on the primary database.

Either way, don’t use Datapatch until all databases or instances run on the new, patched Oracle Home.

That’s It?

Yes, but I did write initally that there might be hiccups.

Waits

Datapatch connects to the database like any other session to make changes inside the database. These changes could be:

  • Creating new tables
  • Altering existing tables
  • Creating or altering views
  • Recreating PL/SQL packages like DBMS_STATS

Imagine this scenario:

  1. You restart the database in the patched Oracle Home.
  2. A user connects and starts to use DBMS_STATS.
  3. You execute datapatch.
    1. Datapatch must replace DBMS_STATS to fix a bug.
    2. Datapatch executes CREATE OR REPLACE PACKAGE SYS.DBMS_STATS .....
    3. The Datapatch database session go into a wait.
  4. User is done with DBMS_STATS.
  5. The Datapatch session come out of wait and replace the package.

In this scenario, the patching procedure was prolonged due to the wait. But it completed eventually.

Hangs

From time to time, we are told that Datapatch hangs. Most likely, it is not a real hang, but just a wait on a lock. You can identify the blocking session by using How to Analyze Library Cache Timeout with Associated: ORA-04021 ‘timeout occurred while waiting to lock object %s%s%s%s%s.’ Errors (Doc ID 1486712.1).

You might even want to kill the blocking session to allow Datapatch to do its work.

Timeouts

What will happen in the above scenario if the user never releases the lock on DBMS_STATS? By default, Datapatch waits for 15 minutes (controlled by _kgl_time_to_wait_for_locks) before throwing an error:

ORA-04021: timeout occurred while waiting to lock object

To resolve this problem, restart Datapatch and ensure that there are no blocking sessions. Optionally, increase the DDL timeout:

./datapatch -ddl_lock_timeout <time-in-sec>

Really Busy Databases

I recommend patching at off-peak hours to reduce the likelihood of hitting the above problems.

If possible, you can also limit the activity in the database while you perform the patching. If your application is using e.g. DBMS_STATS and locking on that object is often a problem, you can hold off these sessions for a little while.

The Usual Suspects

Based on my experience, when there is a locking situation, these are often the sinner:

  • Scheduler Jobs – if you have jobs runnings very frequently, they may all try to start when you restart your database in the new Oracle Home. Suspend the workload temporarilty by setting job_queue_processes to 0.
  • Advanced Queeing – if you have lots of activities happening via AQ, you can suspend it temporarily by setting aq_tm_processes to 0. If you disable the scheduler, you also disable AQ.
  • Materialized Views – when the database refreshes materialized views it uses internal functionality (or depending objects) that Datapatch needs to replace. By disabling the scheduler, you also disable the materialized view refreshes.
  • Backup jobs – I have seen several situations where Datapatch couldn’t replace the package dbms_backup_restore because the backup system took archive backups.

Last Resort

If you want to be absolutely sure no one intervenes with your patching, use this approach. But it means downtime:

  1. SQL> startup restrict
  2. ./datapatch -verbose
  3. SQL> alter system disable restricted session;

I don’t recommend starting in upgrade mode. To get out of upgrade mode a database restart is needed extending the downtime window.

Datapatch And Resources

How much resources does Datapatch need? Should I be worried about Datapatch depleting the system?

No, you should not. The changes that Datapatch needs to make are not resource-intensive. However, a consequence of the DDL statements might be object invalidation. But even here, you should not worry. Datapatch will automatically recompile any ORACLE_MAINTAINED object that was invalidated by the patch apply. But the recompilation happens serially, i.e., less resources needed.

Of course, if you system is running at 99% capacity, it might be a problem. On the other hand, if your system is at 99%, patching problems are probably the least of your worries.

What About OJVM

If you are using OJVM and you apply the OJVM bundle patch, things are a little different.

Release RAC Rolling Standby-First Datapatch
Oracle Database 21c Fully No No Datapatch downtime.
Oracle Database 19c + 18c Partial No No Datapatch downtime, but java system is patched which requires ~10 second outage. Connected clients using java will receive ORA-29548.
Oracle Database 12.2 + 12.1 No No Datapatch must execute in upgrade mode.
Oracle Database 11.2.0.4 No No Similar to 12.2 and 12.1 except you don’t use Datapatch.

Mike Dietrich also has a good blog that you might want to read: Do you need STARTUP UPGRADE for OJVM?

What About Oracle GoldenGate

You should stop Oracle GoldenGate when you execute datapatch. When datapatch is done, you can restart Oracle GoldenGate.

If you are manually recompiling objects after datapatch, I recommend that you restart Oracle GoldenGate after the recompilation.

The above applies even if the patches being applied does not contain any Oracle GoldenGate specific patches.

Oracle GoldenGate uses several objects owned by SYS. When datapatch is running it might change some of those objects. In that case, unexpected errors might occur.

Recommendations

Based on my experience, these are my recommendations

Before Patching

  • Recompile invalid objects (utlrp).
  • Perform a Datapatch sanity check ($ORACLE_HOME/OPatch/datapatch -sanity_checks).
  • Postpone your backup jobs.
  • Stop any Oracle GoldenGate processes that connects to the database.
  • Disable the scheduler.

Patching

  • Always use the latest OPatch.
  • Always use out-of-place patching, even for RAC databases.
  • Always enable verbose output in Datapatch ($ORACLE_HOME/OPatch/datapatch -verbose).

After Patching

  • If applicable, re-enable
    • Backup jobs.
    • Oracle GoldenGate processes.
    • The scheduler.
  • Check Datapatch output. If Datapatch failed to recompile any objects, a message is printed to the console. If you patch interactively, you can find the same information in the log files.

Still Don’t Believe Me?

In Autonomous Database (ADB), there is no downtime for patching. An ADB runs on RAC and patching is fully rolling. The automation tooling executes Datapatch while users are connected to the database.

Of course, one might run into the same issues described above. But Oracle have automation to handle the situation. If necessary, the database kills any sessions blocking Datapatch. In the defined maintenance window in your ADB, you may end up in a situation that a long-running, blocking session terminates because it was blocking a Datapatch execution. But if you minimize your activities in the defined maintenance windows, then chances of that happening is minimal.

Conclusion

Go ahead and patch your database with Datapatch while users are connected.

Further Reading