Tag: autoupgrade

Using Refreshable Clone PDBs from a Standby Database

The other day, I found myself praising refreshable clone PDBs to a customer (which I often do because it’s a killer feature). They liked the feature too but asked:

> We are concerned about the impact on the source database. When AutoUpgrade connects to the source database and clones the database, can we offload the work to a standby database?

Refreshable clone PDBs can eat up your resources if you don’t constrain the target CDB. So, let’s see what we can do.

Mounted Standby Database

This won’t work, because you must be able to connect to the database via a regular database link. Further, AutoUpgrade and the cloning process must be able to execute queries in the source database, which is not possible on a mounted database.

Open Standby Database / Active Data Guard

What if you stop redo apply and open the standby database? Or if you have Active Data Guard?

In this case, the database would be open in read-only mode, and those queries would work. However, the refreshable clone PDB feature was developed to work in and require a read-write database, so this won’t work either – Not even if you enable automatic redirection of DML operations (ADG_REDIRECT_DML).

Even if this case would work, we wouldn’t recommend it. Because, we recommend that you run analyze and fixups mode on the source database, which wouldn’t be possible on a read-only database. You could run analyze and fixups on the primary database. But is that really an option? If you’re worried about affecting your primary and want to offload to the standby, would running those commands on the primary be an option?

Snapshot Standby Database

What about a snapshot standby? That’s a read-write database. Let’s give it a try.

  1. Convert the source standby to a snapshot standby:
    DGMGRL> convert database '...' to snapshot standby;
    • The standby must remain a snapshot standby for the entire duration of the job. If you need to switch over or fail over to the standby, you must restart the entire operation.
  2. Ensure the PDB is open on the source standby.
    alter pluggable database ... open;
    • Otherwise, you will run into ORA-03150 when querying the source database over the database link.
  3. In the source standby, create the user used by the database link and grant appropriate permissions:
    create user dblinkuser identified by ...;
grant create session, create pluggable database, select_catalog_role to dblinkuser;
grant read on sys.enc$ to dblinkuser;
  4. In the target CDB, create a database link that points to the PDB in source standby:
    create database link clonepdb
connect to dblinkuser identified by ...
using '';
  5. Create an AutoUpgrade config file:
    global.global_log_dir=/home/oracle/autoupgrade/log
global.keystore=/home/oracle/autoupgrade/keystore
upg1.source_home=/u01/app/oracle/product/19.0.0.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/23.0.0/dbhome_1
upg1.sid=CDB19
upg1.target_cdb=CDB23
upg1.pdbs=PDB1
upg1.source_dblink.PDB1=CLONEPDB 300
upg1.target_pdb_copy_option.PDB1=file_name_convert=none
upg1.start_time=+12h
  6. Start AutoUpgrade in deploy mode:
    java -jar autoupgrade.jar ... -mode deploy
  7. Convert the source standby back to a physical standby:
    DGMGRL> convert database '...' to physical standby;

Is It Safe?

Using a standby database for anything else than your DR strategy, is sometimes perceived as risky. But it is not, as I explain in this blog post (section What Happens If I Need to Switch Over or Fail Over?).

Happy upgrading!

The Easiest Way to Download 19.27 Release Update

Oracle just released new Release Updates, so it’s time to patch your Oracle Database again. As much as I love patching, the part about finding and downloading patches is not that much fun.

Luckily, I can use AutoUpgrade to download the Release Update and other patches with just a few commands.

How to Download Release Updates

I can do the following on any computer. I just need Java 8 or 11 to run AutoUpgrade.

  1. I download the latest version of AutoUpgrade:

    wget https://download.oracle.com/otn-pub/otn_software/autoupgrade.jar

  2. I create an AutoUpgrade config file called get-patches:

    global.global_log_dir=/home/oracle/autoupgrade/logs
global.keystore=/home/oracle/autoupgrade/keystore
global.folder=/home/oracle/autoupgrade/patches

patch1.platform=LINUX.X64
patch1.patch=RU:19.27,OPATCH,OJVM
    • I want to download 19.27 Release Update and supporting patches for Linux and I specify that using the platform parameter.

  3. I’ve already used AutoUpgrade to download patches, so my My Oracle Support credentials are already stored in the AutoUpgrade keystore. If you’ve never used AutoUpgrade to download patches, follow the instructions below (see Creating an AutoUpgrade Keystore).

  4. I download the patches by starting AutoUpgrade in download mode:

    java -jar autoupgrade.jar -config get-patches -patch -mode download

  5. That’s it! I’ve download all the new Release Updates – plus OPatch and the matching OJVM bundle patch.

    • DATABASE RELEASE UPDATE 19.27.0.0.0 – p37642901_190000_Linux-x86-64.zip
    • OJVM RELEASE UPDATE 19.27.0.0.0 – p37499406_190000_Linux-x86-64.zip
    • OPatch 12.2.0.1.46 for DB 19.0.0.0.0 (Apr 2025) – p6880880_190000_Linux-x86-64.zip

Is it really that easy? Yes, it is…

Happy patching!

What About the Other Platforms

At the time of writing, Oracle has only released the Linux patches. Once the other platforms are available, you can easily download them as well. Check the Patch Availability Document for details. Add the following to your config file:

patch2.platform=ARM.x64
patch2.patch=RU:19.27,OPATCH,OJVM

patch3.platform=AIX.x64
patch3.patch=RU:19.27,OPATCH,OJVM

patch4.platform=SPARC.x64
patch4.patch=RU:19.27,OPATCH,OJVM

patch5.platform=SOLARIS.x64
patch5.patch=RU:19.27,OPATCH,OJVM

patch6.platform=WINDOWS.X64
patch6.patch=RU:19.27,OPATCH,OJVM

Creating an AutoUpgrade Keystore

The first time I use AutoUpgrade to download patches, I must store my MOS credentials in the AutoUpgrade keystore.

  1. I create my config file. It must include global.keystore to specify the location of the AutoUpgrade keystore.

  2. I start the password console:

    java -jar autoupgrade.jar -config get-patches -patch -load_password

  3. AutoUpgrade prompts for a password to protect its keystore. AutoUpgrade uses the password to encrypt the keystore, which stores my My Oracle Support credentials.

    • This is not the database keystore password that you use for TDE Tablespace Encryption.
    Processing config file ...

Starting AutoUpgrade Patching Password Loader - Type help for available options
Creating new AutoUpgrade Patching keystore - Password required
Enter password:
Enter password again:

  4. I specify my MOS username. AutoUpgrade then prompts me for the MOS password:

    MOS> add -user <mos-username-or-email>
Enter your secret/Password:
Re-enter your secret/Password:

  5. I save the changes, and I choose to create an auto-login keystore so I don’t have to enter the AutoUpgrade keystore password every time AutoUpgrade starts:

    MOS> save
Convert the AutoUpgrade Patching keystore to auto-login [YES|NO] ? YES

  6. I exit, and that’s it:

    MOS> exit

AutoUpgrade Patching Password Loader finished - Exiting AutoUpgrade Patching

Here’s a video explaining the use of the AutoUpgrade keystore:

Pro Tips

  • After downloading the patches, I still need to patch my databases. I can do that using AutoUpgrade as well.

  • Once MRPs (888.1) or critical fixes (555.1) become available for the Release Update, I can get those as well. Or, if I need specific one-off patches:

    patch1.patch=RU:19.27,OPATCH,OJVM,MRP,12345678,23456789,34567890

  • If the Data Pump bundle patch is also available, I can also get that:

    patch1.patch=RU:19.27,OPATCH,OJVM,DPBP,MRP,12345678,23456789,34567890
    • Notice the DPBP keyword, that toggles the download of the Data Pump bundle patch.

What About Grid Infrastructure

  • Although AutoUpgrade can’t patch Grid Infrastructure, it can still get the GI bundle patch:

    patch1.patch=RU:19.27,OCW

Be sure to patch Grid Infrastructure out-of-place and I’d recommend using the SwitchGridHome method.

Further Reading

AutoUpgrade New Features: Using Proxy to Download Patches

Once you try the download mode in AutoUpgrade to get patches from My Oracle Support, you will never download patches in any other way. It’s so simple and so easy.

Post from Bluesky from a user of AutoUpgrade satisified with the new download mode Post on Bluesky

Suppose you need to connect through a proxy server to reach My Oracle Support and get the patches. AutoUpgrade now supports several ways to use a proxy.

Download Patches Through a Proxy

  1. Get the latest version of AutoUpgrade:

    wget https://download.oracle.com/otn-pub/otn_software/autoupgrade.jar

  2. Set the proxy settings in an environment variable before calling AutoUpgrade:

    export https_proxy=https://proxy.weyland-yutani.net:8080
java -jar autoupgrade.jar ... -mode download

    • Set https_proxy in lowercase. At least on Linux, environment variables are case-sensitive and although some programs can handle any case, it’s better to use the official case.

    • AutoUpgrade automatically picks up and uses the proxy.

Other Proxy Types

You can set https_proxy to a value matching this specification:

[https|http|socks5|socks]://(user_info@)site:port

  • Here’s an example of how to use SOCKS5 proxy:

    export https_proxy=socks5://proxy.weyland-yutani.net:8080

  • The user_info is optional. Here’s an example::

    export https_proxy=https://ellen:Olympia2092@proxy.weyland-yutani.net:8080

It Doesn’t Work

  • This error message indicates that you forgot to set the https_proxy environment variable or set it correctly:

    There were conditions found preventing AutoUpgrade Patching from successfully running

*Downloading Oracle Patch files
Patch query failed
*login-ext.identity.oraclecloud.com*

  • This error message indicates that your proxy doesn’t allow connections to all the required servers:

    There were conditions found preventing AutoUpgrade Patching from successfully running

*Downloading Oracle Patch files
Patch query failed
*Unable to tunnel through proxy. Proxy returns "HTTP/1.1 403 Forbidden"*

Required Connections

AutoUpgrade must connect to:

URLs are part of a CDN, so expect changing IP addresses. Use DNS names instead of IP addresses in your firewall/proxy configuration.

Happy patching!

Hey, Let Me Kill Your Network!

This short story is about the awesomeness of AutoUpgrade and refreshable clone PDBs.

Colleagues of mine were testing upgrades to Oracle Database 23ai using refreshable clone PDBs. They wanted to see how fast AutoUpgrade would clone the PDB and how that affected the source system.

The Systems

The source and target systems were identical:

  • Exadata X10M
  • 2-node RAC
  • 190 CPU/node
  • 25Gbps network/node

The database:

  • 1 TB in size
  • All data files on ASM

The Results

The source database is Oracle Database 19c. They configured AutoUpgrade to upgrade to Oracle Database 23ai using refreshable clone PDBs. However, this test measured only the initial copy of the data files – the CLONEDB stage in AutoUpgrade.

Parallel Time Throughput Source CPU %
Default 269s 3,6 GB/s 3%
Parallel 4 2060 0,47 GB/s 1%
Parallel 8 850 1,14 GB/s 1%
Parallel 16 591 1,65 GB/s 2%

A few observations:

  • Cloning a 1 TB database in just 5 minutes.
  • Very little effect on CPU + I/O on source, entirely network-bound.
  • The throughput could scale almost up to the limit of the network.
  • By the way, this corresponds with reports we’ve received from other customers.

Learnings

  • The initial cloning of the database is very fast and efficient.
  • You should be prepared for the load on the source system. Especially since the network is a shared resource, it might affect other databases on the source system, too.
  • The target CDB determines the default parallel degree based on its own CPU_COUNT. If the target system is way more powerful than the source, this situation may worsen.
  • Use the AutoUpgrade config file entry parallel_pdb_creation_clause to select a specific parallel degree. Since the initial copy happens before the downtime, you might want to set it low enough to prevent overloading the source system.
  • Be careful. Don’t kill your network!

Happy upgrading!

AutoUpgrade Never Fails, But When It does

Any piece of software has errors. It’s just a fact of life.

Should you encounter problems with AutoUpgrade, you can help us by compiling a zip package. This package contains valuable information that we need to troubleshoot.

Are You Using the Latest Version

  • Before generating a zip package, check that you’re using the latest version of AutoUpgrade… Perhaps the issue is already fixed:
    java -jar autoupgrade.jar -version
  • Check the latest version on oracle.com or AutoUpgrade Tool (Doc ID 2485457.1).
  • Or, simply get the latest version, and compare:
    wget https://download.oracle.com/otn-pub/otn_software/autoupgrade.jar
java -jar autoupgrade.jar -version

How To Generate a Zip Package

  1. You add -zip to the AutoUpgrade command that failed.
  2. You remove all other parameters except -config.
  3. Execute the command:
    java -jar autoupgrade.jar -config db19.cfg -zip
  4. AutoUpgrade generates a zip package in the current directory and outputs the name of the file in the console.
    • You can specify the directory using -d <dir>.

What Does It Contain?

It contains:

  • All the files from the following locations:
    • global.global_log_dir
    • global.autoupg_log_dir
    • <prefix>.log_dir
  • Database alert log
  • Data Guard broker log
  • Attention log

When creating the zip package, AutoUpgrade doesn’t connect to the database but gathers the information from the file system.

If there are files that you don’t want to include in the package, you can exclude them using -zip_exclusion_list. Check the documentation for details.

AutoUpgrade New Features: Control Start Time When Using Refreshable Clone PDBs

When you migrate or upgrade with refreshable clone PDBs, you sometimes want to decide when the final refresh happens. Perhaps you must finish certain activities in the source database before moving on.

I’ve discussed this in a previous post, but now there’s a better way.

The Final Refresh Dilemma

In AutoUpgrade, the final refresh happens at the time specified by the config file parameter start_time. This is the cut-over time where no further changes to the source database, gets replicated in the target database.

Overview of the phases when using refreshable clone PDBs

You specify start_time in the config file, and then you start the job. Typically, you start it a long time before start_time to allow the creation of the new PDB.

So, you must specify start_time in the config file and that’s when you believe the final refresh should happen. But things might change in your maintenance window. Perhaps it takes a little longer to shut down your application or there’s a very important batch job that must finish. Or perhaps you can start even earlier.

In that case, a fixed start time is not very flexible.

The Solution

You can use the proceed command in the AutoUpgrade console to adjust the start time, i.e., the final refresh.

  1. Get the latest version of AutoUpgrade:

    wget https://download.oracle.com/otn-pub/otn_software/autoupgrade.jar

  2. Start the job in deploy mode as you normally would:

    java -jar autoupgrade.jar ... -mode deploy
    • AutoUpgrade now starts the CLONEPDB stage and begins to copy the database.

  3. Wait until the job reaches the REFRESHPDB stage:

    +----+-------+----------+---------+-------+----------+-------+--------------------+
|Job#|DB_NAME|     STAGE|OPERATION| STATUS|START_TIME|UPDATED|             MESSAGE|
+----+-------+----------+---------+-------+----------+-------+--------------------+
| 100|  CDB19|REFRESHPDB|EXECUTING|RUNNING|  14:10:29| 4s ago|Starts in 54 minutes|
+----+-------+----------+---------+-------+----------+-------+--------------------+
Total jobs 1
    • In this stage, AutoUpgrade is waiting for start_time to continue the migration. It refreshes the PDB with redo from the source at the specified refresh interval.
    • I must start well before the maintenance window, so AutoUpgrade has enough time to copy the database.

  4. You can now change the start time. If you want to perform the final refresh and continue immediately, use the proceed command:

    proceed -job 100

    Or, you can change the start time:

    proceed -job 100 -newStartTime 29/03/2025 02:00:00

    Or, you can change the start time to a relative value, example 1 hour 30 min from now:

    proceed -job 100 -newStartTime +1h30m

  5. After the final refresh, AutoUpgrade disconnects the refreshable clone PDB, turns it into a regular PDB, and moves on with the job.

Wrapping Up

AutoUpgrade offers complete control over the process. You define a start time upfront, but as things change, you can adjust it in flight.

Refreshable clone PDBs are a fantastic method for non-CDB to PDB migrations and for upgrades of individual PDBs.

There are a few quirks to be aware of, and if you are using Data Guard bear in mind that you can only plug in with deferred recovery. Other than that – it’s just to say…

Happy migrating, happy upgrading!

Further Reading

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 For Non-Standby-First Installable Patches

Let me show you how I patch my Oracle Data Guard configuration. I make it easy using Oracle AutoUpgrade. I patch all at once – all databases at the same time – which means a short downtime. I can use this approach for all patches – even those that are not standby-first installable.

I recommend this approach only when you have patches that are not standby-first installable.

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

  • I connect to the primary database using Data Guard broker and disable redo transport from the primary database:

    DGMGRL> edit database sales_copenhagen set state='TRANSPORT-OFF';

  • I update listener.ora on both hosts (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 on both hosts:

    lsnrctl reload

  • Downtime starts!

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

  • Patch the primary database:

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

  • Simultaneously, I patch the standby database:

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

  • I update my profile and scripts so they point to the target Oracle home.

  • When patching completes in both hosts, I re-enable redo transport:

    DGMGRL> edit database sales_copenhagen set state='TRANSPORT-ON';

  • 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