Oracle Database is capable of downgrading, and it is a cool fallback mechanism. You can use it even after go-live. Plus, Data Guard plays nicely together with downgrade. You don’t have to rebuild the standby database following a downgrade if you follow the correct procedure.

When To Use Downgrade

You should only consider downgrading after go-live. When users have made changes to your Oracle Database. In that case, you can downgrade to the previous release without any data loss. Before go-live, Flashback Database is our preferred fallback mechanism.

A comparison between the two:

General Considerations

It is a requirement that you have not changed the COMPATIBLE parameter. As soon as you change COMPATIBLE after upgrade, you can no longer downgrade your Oracle Database. If you have already changed the COMPATIBLE parameter, you must use other fallback methods like Data Pump or RMAN restore.

The old release Oracle Home must still exist on primary and standby hosts. I recommend that you keep them until you are absolutely sure you will not downgrade your Oracle Database.

When you downgrade your database, I recommend leaving your Grid Infrastructure at the new release. Don’t downgrade Grid Infrastructure as well. Ideally, you upgraded Grid Infrastructure to the new release in advance in a previous maintenance window. Thus, you know it can handle the old release of the database. Save yourself the added complexity of also downgrading Grid Infrastructure.

Data Guard broker does not support going back to a previous version. You must disable the broker before downgrade and afterward create a new configuration or restore broker configuration files from the old release.

The following works for Data Guard configuration with a physical standby database. Other considerations and a different procedure apply if you have a logical standby database.

Downgrade

To downgrade an Oracle Database protected by Data Guard the following applies:

• You can downgrade hours, days, or even months after the upgrade. As long as you haven’t changed the COMPATIBLE parameter.
• You must mount the standby database and start redo apply in real-time apply mode. Keep it running during the entire process.
• You must not open the standby database until the end of the process.
• The downgrade happens on the primary database. A downgrade will make changes to the data dictionary. Those changes are applied on the standby via redo.
• A downgrade with a standby database generally follows the same procedure as a regular database. However, at the end of each step, you must ensure that the standby database has applied all the redo generated by the primary database during that step. I usually issue a few log switches and check the current sequence (SEQUENCE#) on the primary database. Then I wait for that sequence to be applied on the standby database.

Check our video on YouTube for essential information on downgrade and flip through the slides.

How To Downgrade

AutoUpgrade can’t perform downgrades. You need to do this manually.

In the following, $NEW_ORACLE_HOME refers to the release you were upgrading to (e.g., 19c), and $OLD_ORACLE_HOME refers to the release you upgraded from (e.g., 12.1.0.2).

1. Follow the pre-downgrade instructions.
2. Set the environment in your session to the new Oracle Home.
3. For RAC, set CLUSTER_DATABASE parameter:

   alter system set cluster_database=false scope=spfile sid='*'
   

4. Stop the primary database:

   srvctl stop database -d $ORACLE_UNQNAME
   

5. Start the primary database (if RAC, just one instance) in downgrade mode:

   startup downgrade
   

6. Set the executable flag on downgrade script in the new Oracle Home:

   chmod +x $ORACLE_HOME/bin/dbdowngrade
   

7. Start downgrade script on primary database. The environment is still set to the new release Oracle Home.

   $ORACLE_HOME/bin/dbdowngrade
   

8. Important: Ensure that the standby database has applied all redo from the downgrade operation. Perform a few log switches and note the sequence number. Ensure the standby database has applied that sequence.
9. Shut down primary database and standby database (if RAC, all instances).
10. Downgrade the standby database in Grid Infrastructure. This tells Grid Infrastructure to start the database in the old Oracle Home (in this example, it is 12.1.0.2):

    $ORACLE_HOME/bin/srvctl downgrade database \
      -db $ORACLE_UNQNAME
      -oraclehome $OLD_ORACLE_HOME
      -targetversion 12.1.0.2
    

11. Important: Switch the environment in your session to the old Oracle Home.
12. Start the standby database (if RAC, all instances).
13. Start redo apply:

    alter database recover managed standby database disconnect from session
    

14. Start primary database in upgrade mode (if RAC, only one instance):

    startup upgrade
    

15. Finish the downgrade:

    set echo on termout on serveroutput on timing on
    spool catrelod.log
    @?/rdbms/admin/catrelod.sql
    

16. Recompile:

    @?/rdbms/admin/utlrp.sql
    

17. Set CLUSTER_DATABASE parameter and shut down:

    alter system set cluster_database=true scope=spfile sid='*'
    shutdown immediate
    

18. Downgrade the primary database in Grid Infrastructure. Grid Infrastructure will now start the database in the old Oracle Home (in this example it is 12.1.0.2):

    $NEW_ORACLE_HOME/bin/srvctl downgrade database \
      -db $ORACLE_UNQNAME
      -oraclehome $OLD_ORACLE_HOME
      -targetversion 12.1.0.2
    

19. Start the primary database:

    $ORACLE_HOME/bin/srvctl start database -d $ORACLE_UNQNAME
    

20. Ensure all components are valid or option off:

    select comp_id, status 
    from   dba_registry
    where  status not in ('VALID', 'OPTION OFF')
    

21. Important: Ensure that the standby database has applied all redo from the downgrade operation. Perform a few log switches and note the sequence number. Ensure the standby database has applied that sequence.
22. Start Data Guard broker on primary and standby database
    • Either restore broker config files from old release, and start Data Guard broker.
    • Or, start Data Guard broker and recreate the configuration.
23. : Ensure that your Data Guard configuration works. Use validate database command in Data Guard broker on all databases and ensure they are ready for switchover.
24. Optionally, test your Data Guard by doing a switchover.
25. Perform the post-downgrade instructions.

That’s it!

CDB

No specific Data Guard-related changes. The above procedure is written for a non-CDB database. The procedure is slightly different for a CDB; check the documentation or the demo below.

RAC

On the standby database, you can leave all instances running, and you don’t need to change CLUSTER_DATABASE.

Demo

Downgrade of a CDB running RAC and Data Guard environment:

Other Blog Posts in This Series

• Introduction
• How To Upgrade Data Guard – MAA Method
• How To Upgrade Data Guard – Standby Offline Method
• Changing COMPATIBLE Parameter
• Downtime
• Restore Points
• Flashback
• Downgrade