Using Scripts to Downgrade Oracle Database 23ai

To automate downgrades, Oracle provides the dbdowngrade utility script. When necessary, you can also continue to run catdwgrd.sql manually, as in previous releases.

Parent topic: Downgrading Oracle Database to an Earlier Release

Using Dbdowngrade to Downgrade Oracle Databases To an Earlier Release

To downgrade to a previous database release, Oracle recommends that you run the downgrade script dbdowngrade.

Oracle provides the Downgrade Utility script dbdowngrade. When you use the dbdowngrade utility, it sets appropriate values for the downgrade, and simplifies how you start a downgrade. Specifically, it ensures that the underlying calls to catcon.pl use recommended values, so that potential errors due to excessive threads being spawned are reduced. This feature is especially of value for downgrades of multitenant architecture (CDB) databases. If you prefer to be in control of the number of resources used for a downgrade, then you can run the catdwgrd.sql script manually, as in previous releases. After you downgrade to the earlier database release, you can then appy any release update to that earlier release.

The dbdowngrade shell command is located in the file path $ORACLE_HOME/bin on Linux and Unix, and %ORACLE_HOME%\bin on Microsoft Windows based systems. If you are downgrading a CDB, then you can provide the inclusion list as argument to the script.

When you downgrade multitenant architecture databases (CDBs), the dbdowngrade script has two behaviors, depending on whether you use an inclusion list.

  • Without an inclusion list. The downgrade runs on all the containers that are open in the CDB (PDB and CDB).

    Run the downgrade without an inclusion list when you want to downgrade the entire CDB. In this scenario, all open containers are downgraded. You must open all the PDBs in the CDB manually before you start the dbdowngrade script.

  • With an inclusion list. The downgrade runs only on the PDBs within the inclusion list, and CDB$ROOT is not downgraded during the downgrade operation.

    Run the downgrade with an inclusion list when you want to downgrade only the set of PDBs listed in the inclusion list. In this scenario, where you want to use unplug and plug upgrades, only the set of PDBs that you list in the inclusion list are downgraded. The CDB and the PDBs that are not on the inclusion list remain upgraded to the later release.

Prerequisites:

  • You can downgrade all databases in a multitenant container database (CDB), or one pluggable database (PDB) within a CDB.
  • After the COMPATIBLE parameter is increased, database downgrade is not possible.
  • You can only downgrade to the release from which you upgraded.
  • If you are downgrading without an inclusion list, then you must open all PDB containers before you run the dbdowngrade script.
  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.

    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.

  4. 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
  5. 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.
    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

  6. 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"
  7. 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.

  8. Start up the database in the lower version Oracle home in Upgrade mode. Run catcon.pl to run catrelod.sql on the CDB.
    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -e -n 1 -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.

  9. Run the utlrp.sql script to recompile any remaining stored PL/SQL and Java code:
    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl 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.

As a result of running the dbdowngrade script, the utility runs catdwgrd and catcon.pl. These scripts perform the downgrade, using the recommended values for the release to which you are downgrading.

These scripts create log files. If you run dbdowngrade with the -l filepath, where filepath is the path where you want log files created, then dbdowngrade creates the directory you specify, and places log files there. For example: 

./dbdowngrade -l /databases/downgrade/logs

If you do not specify a directory for log files, then the log files produced by the downgrade scripts are placed under the first directory found of one of these three options, in order of precedence:

  • The Oracle base home identified by the orabasehome command

  • The Oracle base home identified by the orabase command

  • The Oracle home identified by the oracle_home command

For example: 
$ $ORACLE_HOME/bin/orabasehome
/u01/app/oracle/product/23.0.0/dbhome_1
In this example, the $ORACLE_BASE directory is /u01/app/oracle/product/23.0.0/dbhome_1, and the logs are located in /u01/app/oracle/product/23.0.0/dbhome_1/cfgtoollogs/downgrade. In the directory, the log files are prefixed with the string catdwgrd.

To further manage how the dbdowngrade script runs, you can specify the following additional options:

  • -d directory-path Specify the directory path, defined by directory-path, where you want the catdwgrd.sql file placed
  • -e Specify that you want to turn echo off while catdwgrd.sql runs (the default is set to on).
  • -n number Specify the number of parallel processes you want the dbdowngrade command to use. By default, the number of processes is equal to the number of CPUs divided by 2 (cpu_count/2).
  • -b log-file-name-base Specify a different base file name (the value you provide for the variable log-file-name-base) for log files generated by the manual downgrade script catdwgrd. If you do not specify a different base file name, then the default file base name is catdwgrd.
  • -h Specify that you want dbdowngrade to display a list of command options. The dbdowngade script then outputs command options to the screen, and exits.

Note:

  • Read-write Oracle homes: the commands orabaseconfig and orabasehome both return the environment setting for ORACLE_HOME.

  • Read-only Oracle homes: the command orabaseconfig returns the read-only path configuration for the Oracle base in the path $ORACLE_BASE/homes.

Related Topics

Parent topic: Using Scripts to Downgrade Oracle Database 23ai

Downgrading Oracle Databases Manually with catdwgrd.sql

When you prefer to downgrade Oracle Database manually, or if you are concerned about excessive thread issues, you can run the manual catdwgrd.sql script.

You can use the manual catdwgrd.sql script to downgrade Oracle Database to an earlier a supported major release, or an earlier release update.

If you are downgrading from Oracle Database 23ai to Oracle Database 21c then you can downgrade all databases in a multitenant container database (CDB), or one pluggable database (PDB) within a CDB. Oracle Database releases earlier than Oracle Database 12c did not use multitenant architecture.

Note:

Starting with Oracle Database 21c, non-CDB architecture is desupported. If you upgraded a non-CDB and converted to a PDB, you can only downgrade back to a PDB. It's not possible to revert the non-CDB to PDB conversion. Starting with Oracle Database 23ai, Oracle Label Security is desupported.

  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. In case of errors during startup, you can be required to use the PFILE option to specify the location of your initialization parameter file.
    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. Use the following command to start the downgrade on the CDB, using catdwgrd as the log file base:
    $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 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.

  8. Shut down the instance:
    SQL> SHUTDOWN IMMEDIATE
  9. Exit SQL*Plus.
  10. 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 Oracle Database OracleServiceSID Oracle service, 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

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

  12. 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.)
  13. Start SQL*Plus, and connect to the database instance as a user with SYSDBA privileges.
    connect / as sysdba
startup upgrade;
alter pluggable database all open upgrade;
  14. Use catcon.pl to run catrelod.sql on CDB databases.

    Example:

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

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

  16. 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
  17. Run the utlrp.sql script to recompile any remaining stored PL/SQL and Java code.

    Use this procedure on the CDB, using log file base utlrp: 

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

    If you are downgrading back to Oracle Database 19c, and if Oracle APEX (formerly Application Express) exists in the database, then after ultrp.sql completes running, run the script sys.validate_apex to validate the APEX downgrade. You must validate APEX manually only for Oracle Database 19c. For other releases, running sys.validate_apex manually is not required.

  18. Exit SQL*Plus.

At the completion of this procedure, your database is downgraded.

Related Topics

Parent topic: Using Scripts to Downgrade Oracle Database 23ai