When talking about upgrades, Oracle Database has a great fallback mechanism; a downgrade. Even after going live on Oracle Database 23ai, you can get back to Oracle Database 19c or 21c – with no data loss.

How to Downgrade From Oracle Database 23ai

My PDB, PDB1, has already been upgraded to Oracle Database 23ai. Now, I want to downgrade to Oracle Database 19c. In the downgrade process, I will unplug from CDB23 and into CDB19.

1. I open the PDB in downgrade mode:

   alter pluggable database PDB1 close immediate;
   alter pluggable database PDB1 open downgrade;
   

   • Downgrade mode is a special mode – similar to upgrade mode. It enables exclusive access to the database and disables a lot of features.
2. I set the environment to CDB23 and start the downgrade process:

   cd $ORACLE_HOME/bin
   ./dbdowngrade -c 'PDB1'
   

   • The -c command line parameter starts a downgrade of a specific PDB; not the entire CDB.
3. After the downgrade, I close and unplug from CDB23:

   alter pluggable database PDB1 close;
   alter pluggable database PDB1 unplug into '/home/oracle/scripts/pdb1.xml';
   

4. Now, I connect to CDB19 running on Oracle Database 19c. I plug in and open the PDB in upgrade mode:

   create pluggable database PDB1 using '/home/oracle/scripts/pdb1.xml';
   alter pluggable database PDB1 open upgrade;
   

5. I switch to PDB1 and complete the downgrade by running the catrelod.sql script:

   alter session set container=PDB1;
   @$ORACLE_HOME/rdbms/admin/catrelod.sql
   

6. Then, I recompile all invalid objects:

   @$ORACLE_HOME/rdbms/admin/utlrp.sql
   

7. A restart of the PDB. I open in normal mode:

   alter pluggable database PDB1 close;
   alter pluggable database PDB1 open;
   

8. I gather new dictionary statistics:

   exec dbms_stats.gather_dictionary_stats;
   

   • After a while, when the database is warmed up, I also gather fixed objects statistics.
9. I verify that all components are VALID or OPTION OFF:

   select comp_id, version, status from dba_registry;
   

10. I run Datapatch to ensure all SQL patches are properly applied:

    $ORACLE_HOME/OPatch/datapatch
    

That’s it!

Worth Knowing About Downgrades

• The downgrade is a two-step process.

  • The first part happens while the database is running in the new Oracle Home. Startup in a special downgrade mode and execute catdwgrd.sql to start the downgrade.
  • Next, restart the database in the old Oracle Home and start in upgrade mode. catrelod.sql will re-install any missing objects in the database and finish the downgrade.

• Oracle recommends that you install the latest Release Update in both Oracle homes; the one that you downgrade from, and the one to which you downgrade.

• You can only downgrade if the compatible hasn’t been changed after the upgrade.

• If the timezone file was upgraded, the same timezone file must be present in the old Oracle Home.

• Before you start the downgrade, there’s no need to roll off any patches with Datapatch. The downgrade mechanism takes care of that.

• The data dictionary in a downgraded database is not identical to the pre-upgraded database. The data dictionary will be different, but compatible. Here are some examples:

  • Generally, dropping objects is avoided.
  • New tables are most likely not dropped but truncated.
  • New indexes are most likely kept.

• Although you can downgrade a database from Oracle Database 23ai to 19c, you can’t undo the multitenant migration. To get back to a non-CDB you must use other means like Data Pump, transportable tablespaces, or GoldenGate.

Want to Try?

Hopefully, you never need to downgrade a PDB. But I bet you can resist the urge to try it. Right? RIGHT?

In our hands-on lab, Hitchhiker’s Guide for Upgrading to Oracle Database 23ai, there is a downgrade exercise. Give it a try. The lab is free to use and doesn’t require any installation – it runs completely inside a browser.

Happy downgrading!

Appendix

Further Reading

• Documentation, Downgrading Oracle Database to an Earlier Release
• Blog post, Downgrade and Data Guard
• Webinar, Secure Your Job – Fallback Is Your Insurance