Here’s a question I received from a customer:

I’ve patched my database to 19.25 using AutoUpgrade and out-of-place patching. How do I roll back, if needed?

Basically, a rollback is the same as patching the database. You just do it the other way around – from the higher to the lower Oracle home. But let’s look at the details.

AutoUpgrade

I’m glad to hear that the customer uses AutoUpgrade for patching. It’s my recommended method, and it has many benefits.

If you use AutoUpgrade to patch your Oracle Database, you can also use it to roll back, but only before going live:

java -jar autoupgrade.jar -restore -jobs <n>

• n is the job ID of the patching job.
• AutoUpgrade undoes everything it did.

AutoUpgrade relies on Flashback Database as its rollback mechanism. So, it’s no good if users have already connected to the database and added/changed data.

Allow me to repeat: Only use AutoUpgrade to roll back before go-live!

After go-live, you must roll back manually.

Manually

You can manually roll back at any time – even after go-live.

Imagine you want to roll back from 19.25 (the new Oracle home) to 19.24 (the old Oracle home). Here’s how to do it.

• You start by setting the environment.

  export OLD_ORACLE_HOME=/u01/app/oracle/product/dbhome_19_24
  export NEW_ORACLE_HOME=/u01/app/oracle/product/dbhome_19_25
  export ORACLE_HOME=$NEW_ORACLE_HOME
  export PATH=$ORACLE_HOME/bin:$PATH
  

• Optionally, you run Datapatch sanity check in the new Oracle home (thanks Erik for pointing that out).

  $ORACLE_HOME/OPatch/datapatch -sanity_checks
  

• You shut down the database running in the new Oracle home.

  sqlplus / as sysdba<<EOF
     shutdown immediate
  EOF
  

• You move the following files back to the old Oracle home:
  • PFile
  • SPFile
  • Password file
  • Network files (like tnsnames.ora and sqlnet.ora)
  • Some of the files might not be present at all or be placed outside the Oracle home
  • Check this blog post for other files that might be stored in the Oracle home

  mv $NEW_ORACLE_HOME/dbs/init$ORACLE_SID.ora $OLD_ORACLE_HOME/dbs
  mv $NEW_ORACLE_HOME/dbs/spfile$ORACLE_SID.ora $OLD_ORACLE_HOME/dbs
  mv $NEW_ORACLE_HOME/dbs/orapw$ORACLE_SID $OLD_ORACLE_HOME/dbs
  mv $NEW_ORACLE_HOME/network/admin/sqlnet.ora $OLD_ORACLE_HOME/network/admin
  mv $NEW_ORACLE_HOME/network/admin/tnsnames.ora $OLD_ORACLE_HOME/network/admin
  

• Update /etc/oratab and set the Oracle home to the old one.
• Update your profile scripts to reflect the old Oracle home. It could be .bashrc.
• Start the database in the old Oracle home.

  export ORACLE_HOME=$OLD_ORACLE_HOME
  export PATH=$ORACLE_HOME/bin:$PATH
  sqlplus / as sysdba<<EOF
     startup
  EOF
  

• Run Datapatch.

  $ORACLE_HOME/OPatch/datapatch
  

• Finally, you fix internal directories that point to paths inside the Oracle home:

  @?/rdbms/admin/utlfixdirs.sql
  

Datapatch

When you roll back, you must execute Datapatch. It will automatically detect that you are rolling back and perform the necessary actions.

For each patch there is an apply script that brings changes into the database. Datapatch executes the apply script during patching.

For each apply script, there is always a rollback script. It will reverse the actions of the apply script. Datapatch executes the rollback script when you roll back.

You can learn much more about Datapatch in this video.

Normally, you would roll back to the Oracle home from where you came, but that’s not a requirement. This scenario is fully supported:

• Patch from 19.23 to 19.25
• Roll back to 19.24

How To Practice?

We have a hands-on lab in which you can try rollbacks – using AutoUpgrade and manually.

Patch Me If You Can

The lab runs in Oracle Live Labs.

• It’s completely free
• It runs in just a browser

Happy patching!

Further Reading

• Blog post, Files to Move During Oracle Database Out-Of-Place Patching
• Blog post, Can I Run Datapatch When Users Are Connected
• Virtual Classroom, Oracle Database Release and Patching Strategy for 19c and 23ai
• Virtual Classroom, One-Button Patching with AutoUpgrade – Making every DBA’s life easier
• Hands-on lab, instructions for roll back
• Mike Dietrich, blog post, Rolling back or removing all patch SQL changes
• Mike Dietrich, blog post, datapatch stores rollback patching files in the database