To downgrade Oracle Database to an earlier supported major release, or to an earlier release update, Oracle recommends that you run the downgrade script dbdowngrade.

1. Log in to the system as the owner of the Oracle Database Oracle home directory.
2. Set the ORACLE_HOME environment variable to the Oracle home of the upgraded Oracle Database release.
3. Set the ORACLE_SID environment variable to the system identifier (SID) of the Oracle Database that you want to downgrade.
4. At a system prompt, change to the directory ORACLE_HOME/rdbms/admin, where ORACLE_HOME is the Oracle home on your system.

   Note:

   If you are downgrading a cluster database, then shut down the database completely, and change the value for the initialization parameter CLUSTER_DATABASE to FALSE. After the downgrade, set this parameter back to TRUE.

5. Using SQL*Plus, connect to the database instance that you want to downgrade as a user with SYSDBA privileges:

   sqlplus sys as sysdba
   Enter password: password
   

6. Start the instance in downgrade mode by issuing the following SQL*Plus command for your Oracle Database instance type. In case of errors during startup, you can be required to use the PFILE option to specify the location of your initialization parameter file.

   • Non-CDB instances:

     SQL> startup downgrade pfile=pfile_name
     

   • CDB instances:

     SQL> startup downgrade pfile=pfile_name
     SQL> alter pluggable database all open downgrade;

   Specify the location of your initialization parameter file PFILE.

   See Also:

   Oracle Database Administrator’s Guide for information about specifying initialization parameters at startup and the initialization parameter file

7. Start the dbdowngrade script, either with default values, or with an inclusion list.

   If you use an inclusion list, then CDB$ROOT must not be on your inclusion list.

   For example:

   Running with default values

   Linux and Unix

   $cd $ORACLE_HOME/bin 
   $./dbdowngrade

   Microsoft Windows

   $cd %ORACLE_HOME%\bin
   $dbdowngrade.cmd

   Running with inclusion list for CDB

   Linux and Unix

   $cd $ORACLE_HOME/bin 
   $./dbdowngrade –c 'PDB1 PDB2 PDBN'

   Microsoft Windows

   $cd %ORACLE_HOME%\bin 
   $dbdowngrade.cmd –c "PDB1 PDB2 PDBN"

$ $ORACLE_HOME/bin/orabasehome
/u01/app/oracle/product/19.0.0/dbhome_1

You can run the manual catdwgrd.sql script for downgrades of Oracle Database to an earlier supported major release, or an earlier release update, where you either prefer to run a manual script, or you want to avoid excessive thread issues.

1. Log in to the system as the owner of the Oracle Database Oracle home directory.
2. Set the ORACLE_HOME environment variable to the Oracle home of the upgraded Oracle Database release.
3. Set the ORACLE_SID environment variable to the system identifier (SID) of the Oracle Database that you want to downgrade.
4. At a system prompt, change to the directory ORACLE_HOME/rdbms/admin, where ORACLE_HOME is the Oracle home on your system.

   Note:

   If you are downgrading a cluster database, then shut down the database completely, and change the value for the initialization parameter CLUSTER_DATABASE to FALSE. After the downgrade, set this parameter back to TRUE.

5. Using SQL*Plus, connect to the database instance that you want to downgrade as a user with SYSDBA privileges:

   sqlplus sys as sysdba
   Enter password: password
   

6. Start the instance in downgrade mode by issuing the following SQL*Plus command for your Oracle Database instance type. In case of errors during startup, you can be required to use the PFILE option to specify the location of your initialization parameter file.

   • Non-CDB instances:

     SQL> startup downgrade pfile=pfile_name
     

   • CDB instances:

     SQL> startup downgrade pfile=pfile_name
     SQL> alter pluggable database all open downgrade;

   Specify the location of your initialization parameter file PFILE.

   See Also:

   Oracle Database Administrator’s Guide for information about specifying initialization parameters at startup and the initialization parameter file

7. (Recommended) If you are downgrading a non-CDB, then Oracle recommends that you set the system to spool results to a log file, so that you can track the changes and issues.

   If you are downgrading a CDB, then you do not need to perform this step. CDBs automatically spool output to the catcon_logs.

   On a non-CDB, enter the following command to spool results to a log file, where downgrade.log is the name of the log file:

   set echo on termout on serveroutput on timing on
   SQL> SPOOL downgrade.log
   

8. Use the following command to start the downgrade, depending on your configuration:

   • Non-CDB:

     SQL> @catdwgrd.sql
     

   • CDB:

     $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -d $ORACLE_HOME/rdbms/admin -e -b catdwgrd -l output directory -r catdwgrd.sql
     

   In the CDB example, catdwgrd.sql is run on containers using catcon.pl. To run commands with the catcon.pl utility, you first start Perl. The -d parameter tells catcon.pl where to find catdwgrd. The -l parameter specifies the output directory for log files (instead of writing to the rdbms/admin directory). Specifying the -r parameter causes catdwgrd to run first on the PDBs, and second on CDB_ROOT.

   Run catdwgrd using the -r parameter when you downgrade a CDB. The –r parameter changes the default order that scripts are run, so that scripts run in all PDBs, and then in CDB_ROOT.

   Note:

   • Use the version of the catdwgrd.sql script included with your new Oracle Database release.

   • Run catdwgrd using the -r parameter when downgrading a CDB.

   • Run catdwgrd.sql in the new Oracle Database release environment.

   • The catdwgrd.sql script downgrades all Oracle Database components in the database to the release from which you upgraded. The downgrade is either to the supported major release from which you upgraded, or to the patch release from which you upgraded.

   If you are downgrading a multitenant environment database, and the catdwgrd.sql command encounters a failure, then review the error message. Check to see what issues are present in the CDB$ROOT or PDBs before proceeding. Check the section "Troubleshooting the Downgrade of Oracle Database." Fix the issues as stated in the errors. After you resolve the errors, rerun catdgwrd.sq with the catcon.pl utility, using the syntax catcon.pl -c 'cdb,pdb' -r.

   Caution:

   If the downgrade for a component fails, then an ORA-39709 error is displayed. The SQL*Plus session terminates without downgrading the Oracle Database data dictionary. All components must be successfully downgraded before the Oracle Database data dictionary is downgraded. Identify and fix the problem before rerunning the catdwgrd.sql script.

9. For Non-CDB only, if you turned the spool on, then turn off the spooling of script results to the log file:

   SQL> SPOOL OFF
   

   Check the spool file, and verify that no errors occurred during the downgrade. You named the spool file in Step 8, and the suggested name was downgrade.log. Correct any problems that you find in this file. If necessary, rerun the downgrade script.

   Note:

   Save the results from the first time you ran the downgrade script. Before you rerun the downgrade script, rename the file downgrade.log to a different name, so that it is not overwritten when you rerun the script.

10. Shut down the instance:

    SQL> SHUTDOWN IMMEDIATE

11. Exit SQL*Plus.
12. If your operating system is Linux or Unix, then change the following environment variables to point to the directories of the release to which you are downgrading:

    • Linux and UNIX systems

      Change the following environment variables to point to the directories of the release to which you are downgrading:

      • ORACLE_HOME

      • PATH

      Also check that your oratab file, and any client scripts that set the value of ORACLE_HOME, point to the downgraded Oracle home.

      See Also:

      Oracle Database Installation Guide for your operating system for information about setting other important environment variables on your operating system

    • Microsoft Windows systems

      1. Stop all Oracle services, including the OracleServiceSID Oracle service of the Oracle Database 12c database, where SID is the instance name.

         For example, if your SID is ORCL, then enter the following at a command prompt:

         C:\> NET STOP OracleServiceORCL
         

         See Also:

         Oracle Database Administrator’s Reference for Microsoft Windows for more information about stopping Oracle services on Windows

      2. Delete the Oracle service at a command prompt by issuing the command ORADIM.

         For example, if your SID is ORCL, then enter the following command:

         C:\> ORADIM -DELETE -SID ORCL
         

         Create the Oracle service of the database that you are downgrading at a command prompt using the command ORADIM:

         C:\> ORADIM -NEW -SID SID -INTPWD PASSWORD -MAXUSERS USERS
         -STARTMODE MANUAL -PFILE ORACLE_HOME\DATABASE\INITSID.ORA

         The syntax for ORADIM includes the following variables:

         Variable	Description
         SID	Same system identifier (SID) name as the SID of the database being downgraded.
         PASSWORD	Password for the database instance. This password is the password for the user connected with SYSDBA privileges. The -INTPWD option is not required. If you are prompted for a password, then use the password for the standard user account for this Windows platform.
         USERS	Maximum number of users that can be granted SYSDBA and SYSOPER privileges.
         ORACLE_HOME	Oracle home directory of the database to which you are downgrading. Ensure that you specify the full path name with the option -PFILE, including the drive letter where the Oracle home directory is mounted. See Oracle Database Administrator’s Guide for information about specifying initialization parameters at startup, and for information about the initialization parameter file.

         For example, if your SID is ORCL, your PASSWORD is TWxy5791, the maximum number of USERS is 10, and the ORACLE_HOME directory is C:\ORANT, then enter the following command:

         C:\> ORADIM -NEW -SID ORCL -INTPWD TWxy5791 -MAXUSERS 10
              -STARTMODE AUTO -PFILE C:\ORANT\DATABASE\INITORCL.ORA

         Note:

         The ORADIM command prompts you for the password for the Oracle home user account. You can specify other options using ORADIM.

         You are not required to change any Windows Registry settings when downgrading a database. The ORADIM utility makes all necessary changes automatically.

         See Also:

         Oracle Database Administrator’s Reference for Microsoft Windows for information about administering an Oracle Database instance using ORADIM

13. Restore the configuration files (for example, parameter files, password files, and so on) of the release to which you are downgrading.

    If the database is an Oracle RAC database, then run the following command to return the database to single instance mode:

    SET CLUSTER_DATABASE=FALSE

    Note:

    If you are downgrading a cluster database, then perform this step on all nodes on which this cluster database has instances configured. Set the value for the initialization parameter CLUSTER_DATABASE to FALSE. After the downgrade, set this initialization parameter back to TRUE.

    See Also:

    Oracle Real Application Clusters Administration and Deployment Guide for information about initialization parameter use in Oracle RAC

14. At a system prompt, change to the admin directory in the Oracle home directory of the earlier release to which you are downgrading. (ORACLE_HOME/rdbms/admin, where ORACLE_HOME is the path to the earlier release Oracle home.)
15. Start SQL*Plus, and connect to the database instance as a user with SYSDBA privileges.

    • For a non-CDB:

      SQL> CONNECT / AS SYSDBA
      SQL> STARTUP UPGRADE

    • For a CDB:

      connect / as sysdba
      startup upgrade;
      alter pluggable database all open upgrade;
      

16. (Optional) For a non-CDB, set the system to spool results to a log file to track changes and issues. This step is not needed for a CDB.

    SQL> SPOOL reload.log
    

17. Run catrelod.sql on non-CDB databases, or use catcon.pl to run catrelod.sql on CDB databases.

    • For a non-CDB:

      SQL> $ORACLE_HOME/rdbms/admin/catrelod.sql

    • For a CDB:

       $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -b catrelod -d $ORACLE_HOME/rdbms/admin catrelod.sql

    This command reloads the appropriate version for each of the database components in the downgraded database.

18. If you turned on spooling for a non-CDB, then turn off the spooling of script results to the log file:

    SQL> SPOOL OFF
    

    Check the spool file, and verify that the packages and procedures compiled successfully. Correct any problems that you find in this log file, and rerun the appropriate script, if necessary.

19. Shut down and restart the instance for normal operation:

    SQL> SHUTDOWN IMMEDIATE
    SQL> STARTUP
    

    You can be required to use the optionPFILE to specify the location of your initialization parameter file.

    See Also:

    Oracle Database Administrator’s Guide for information about specifying initialization parameters at startup, and in the initialization parameter file

20. If you configured your database to use Oracle Label Security, then complete this step. If you did not configure your database to use Oracle Label Security, then proceed to the next step.
    1. Copy the script olstrig.sql from the Oracle home under Oracle Database 12c to the Oracle home of the release number to which you are downgrading the database.
    2. From the Oracle home of the downgrade release, run olstrig.sql to recreate DML triggers on tables with Oracle Label Security policies:

       SQL> @olstrig.sql
       

21. (Optional) For a non-CDB, set the system to spool results to a log file to track changes and issues. This step is not needed for a CDB.

    Example:

    SQL> SPOOL utlrp.log

22. Run the utlrp.sql script to recompile any remaining stored PL/SQL and Java code. Use the procedure for your configuration:

    • non-CDB:

      SQL> $ORACLE_HOME/rdbms/admin/utlrp.sql 

    • CDB:

      $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -b utlrp -d $ORACLE_HOME/rdbms/admin utlrp.sql

      The utlrp.sql script recompiles all existing PL/SQL modules previously in INVALID state, such as packages, procedures, types, and so on. The log file utlrp0.log is generated. That log file lists the recompilation results.

23. If you turn on spooling for a non-CDB when you run utlrp.sql, then turn off the spooling of script results to the log file:

    SQL> SPOOL OFF
    

    Check the spool file, and verify that the packages and procedures compiled successfully. Correct any problems that you find in this log file. If necessary, rerun the appropriate script.

24. Exit SQL*Plus.
25. If you are downgrading a cluster database, then you must run the following command to downgrade the Oracle Clusterware database configuration:

    $ srvctl downgrade database -d db-unique-name -o oraclehome -t to_version
    

    Replace the variables in this syntax example with the values for your system:

    • db-unique-name is the database name (not the instance name).

    • oraclehome is the location of the old Oracle home for the downgraded database.

    • to_version is the database release to which the database is downgraded. (For example: 12.2.0.1.)

    Note:

    Run this command from the new release Oracle home, not from the Oracle home to which the database is being downgraded.