6 Downgrading Oracle Database to an Earlier Release

For supported releases of Oracle Database, you can downgrade a database to the release from which you last upgraded. For example, if you recently upgraded from release 11.2.0.4 to Oracle Database 12c, and you did not change the compatible initialization parameter to 12.1 or higher, then you can downgrade to release 11.2.0.4. If your Oracle Database 12c is release 12.1.0.2, and you did not change the compatible initialization parameter to 12.1.0.2, then you can downgrade to release 12.1.0.1 and so forth.

This chapter contains the following topics:

See Also:

Oracle Database Installation Guide for your operating system for discussions of downgrading that are operating system-specific

6.1 Supported Releases for Downgrading Oracle Database

You can downgrade both major releases and patchset releases, based on the original Oracle Database release from which the database was upgraded. Major release downgrades are supported back to Oracle Database 11g Release 2 (11.2.0.2) and subsequent 11.2 releases, and Oracle Database 11g Release 1 (11.1.0.7). Patchset downgrades are supported back to Oracle Database release 11.2 patchsets, except release 11.2.0.1.

Table 6-1 provides a summary of releases supported for downgrading. When using this table, also read about compatibility in "Checking for Incompatibilities When Downgrading Oracle Database."

Table 6-1 Supported Releases and Editions for Downgrading

Oracle Database Release or Edition Downgradable? Notes

12.1.0.2

Y

You cannot downgrade a database once you have set the compatible initialization parameter to 12.1.0.2.

Only if the compatibility is set to 12.1.0.1 will a downgrade be possible for a pluggable database (PDB).

The patch set update PSU4 is required in order to downgrade a CDB, or unplug and downgrade a PDB. PSUs are available for download on My Oracle Support (MOS) at https://support.oracle.com/. See MOS Note 756671.1 to obtain the latest patch set and any required set of additional fixes. (Search in the general search box on the Patches and Updates page.)

You cannot downgrade back to release 10.2.0.5 because the minimum compatibility setting for Oracle Database 12c is 11.0.

12.1.0.1

Y

If you unplug a 12.1.0.1 PDB from a 12.1.0.1 database and then plug this PDB into a 12.1.0.2 database for upgrade, then you cannot downgrade this PDB if the compatible initialization parameter in the 12.1.0.2 database is higher than '12.1.0.1.0'.

You cannot downgrade back to release 10.2.0.5 because the minimum compatibility setting for Oracle Database 12c is 11.0.

Oracle Enterprise Manager

N

You must reconfigure Oracle Enterprise Manager controls if you downgrade to an earlier supported release. See "Restoring Oracle Enterprise Manager after Downgrading Oracle Database."

Oracle Database Express Edition

N

You cannot downgrade a database that was upgraded from Oracle Database Express Edition


Important:

The following information is very important to understand regarding compatibility if you expect to be able to downgrade.

  • You cannot downgrade a database once you have set the compatible initialization parameter to 12.1.0.2.

  • Only if the compatibility is set to 12.1.0.1 will a downgrade be possible for a pluggable database (PDB).

  • If you unplug a release 12.1.0.1 PDB from a 12.1.0.1 database and then plug this PDB into a release 12.1.0.2 database, you cannot downgrade this PDB.

  • PSU4 (that is patch set update) is required in order to downgrade a CDB or unplug and downgrade a PDB. See MOS Note 756671.1 to obtain the latest patch set, and any required set of additional fixes, at My Oracle Support (MOS) at https://support.oracle.com/

Note:

You cannot downgrade back to release 10.2.0.5 because the minimum compatibility setting for Oracle Database 12c is 11.0. Additionally, you cannot downgrade a database that was upgraded from Oracle Database Express Edition.

The following recommendations for earlier supported releases affect downgrading for Oracle Database:

  • This release includes multitenant architecture, which provides features for a multitentant container database (CDB) and pluggable databases (PDBs). Therefore, setting the compatible initialization parameter to the highest level after upgrading to this release prevents the capability to downgrade.

  • This release contains a new object privilege, READ, in addition to SELECT. After you downgrade:

    • If you have the SELECT and READ object privileges, the READ privilege is removed.

    • If you only had the READ object privilege, then this is transformed into the SELECT object privilege.

    See Oracle Database Security Guide for information about the READ and SELECT object privileges.

  • The earliest release that you can downgrade back to is Oracle Database release 11.1.0.7. Because the minimum compatibility for Oracle Database 12c is 11.0, after you upgrade to release 12.1, the compatibility must be set to at least 11.0. Once compatibility is set, this changes the on-disk structure of the database and enables new features that are controlled by the compatible setting. Therefore, downgrade to a release earlier than 11.1.0.7 is no longer an option.

  • If Oracle XML DB was not present in the database to be upgraded, then Oracle XML DB is uninstalled during the downgrade. For example, if you did not install Oracle XML DB with Oracle Database 11g Release 2 (11.2), then when upgrading to Oracle Database 12c, Oracle XML DB is installed because it is included with this release. During a downgrade back to Oracle Database 11g Release 2 (11.2), Oracle XML DB is removed.

  • During upgrade to Oracle Database 12c, the Database (DB) Control repository is removed. If you downgrade, you must reconfigure the Database (DB) Control to use it again in the earlier, downgraded release. See "Saving Oracle Enterprise Manager Database Control Configuration and Data."

  • Downgrade is not supported for Oracle Enterprise Manager. You must reconfigure Oracle Enterprise Manager controls if you downgrade to an earlier supported release. See "Restoring Oracle Enterprise Manager after Downgrading Oracle Database."

  • Oracle Clusterware for Oracle Database 12c does not support downgrading to Oracle Database release 10.2 and release 11.1 on Windows. A fresh install is required for releases earlier than release 11.2.

6.2 Checking for Incompatibilities When Downgrading Oracle Database

Check the compatibility level of your database to see if the database might have incompatibilities that prevent you from downgrading. If the compatibility level of your Oracle Database 12c database is 12.1.0, then you are not able to downgrade.

If you are downgrading to Oracle Database 11g Release 2 (11.2.0.2), Oracle Database 11g Release 2 (11.2.0.3), or Oracle Database 11g Release 2 (11.2.0.4), then the COMPATIBLE initialization parameter must be set to 11.2.0 or lower. Downgrading to release 11.2.0.1 is not supported.

If you are downgrading to Oracle Database 11g Release 1 (11.1.0.7), then the COMPATIBLE initialization parameter must be set to 11.1.0 or lower. Only downgrading to release 11.1.0.7 is supported for Oracle Database 11g Release 1.

Note:

For Oracle ASM disk groups, if you changed disk group compatibility to 12.1.0.0.0 when you upgraded your database, then when you downgrade to the earlier release you will be unable to mount your Oracle ASM disk groups.

You must manually restore compatibility of Oracle ASM disk groups before downgrade. Otherwise, the instance cannot mount the disk groups after downgrade.

See Oracle Automatic Storage Management Administrator's Guide for information about Oracle ASM disk group compatibility.

6.3 Performing a Full Backup Before Downgrading Oracle Database

Oracle strongly recommends that you perform a full backup of your Oracle Database 12c database before you downgrade to a supported earlier releases.

See Also:

Oracle Database Backup and Recovery User's Guide for information about performing RMAN backups

6.4 Performing Required Pre-Downgrade Steps for Oracle Database

Before downgrading to the earlier release from which you upgraded, you must complete required preparation. For example, you may need to determine if components in the database must be disabled first.

To prepare for downgrading:

  1. If you are downgrading a CDB or unplugging and downgrading a PDB in Oracle Database release 12.1.0.2, then you must first apply PSU4 (patch set update 4). You obtain PSU4 and any required set of additional fixes by going to My Oracle Support at https://support.oracle.com/. The patch set information and link is contained in MOS Note 756671.1. Search for 756671.1 in the general search box on the Patches and Updates page. This is a requirement before you can downgrade.

  2. If you are downgrading to Oracle Database release 11.1, then before starting the downgrade procedures, ensure that you know the password for any newly-created database links. These are database links that you may have created while running releases 11.2 or 12.1. Knowing the database link password is necessary because you will need to reset the database link password after downgrading to Oracle Database release 11.1. See "Issue with Password for Database Links After Downgrading" for more information.

  3. If you have enabled Oracle Database Vault on your database, then disable Oracle Database Vault before downgrading the database.

    To find if Oracle Database Vault is enabled, query the V$OPTION dynamic view as follows:

    Enter Oracle Database Vault in the case shown in this query. If the output is TRUE, then Oracle Database Vault is enabled, so you must disable it. See Oracle Database Vault Administrator's Guide for instructions.

  4. If your database uses Oracle Label Security and you are downgrading to release 11.2 or earlier, then run the Oracle Label Security (OLS) preprocess downgrade olspredowngrade.sql script in the new Oracle Database 12c Oracle home. You do not need to run olspredowngrade.sql if you are downgrading release 12.1.0.2 to release 12.1.0.1.

    Important:

    Running the olspredowngrade.sql script before downgrading is mandatory for downgrading from Oracle Database 12c to database release 11.2 (and earlier) in databases that use Oracle Label Security and Oracle Database Vault.
    1. To find out if Oracle Label Security is enabled, query the V$OPTION dynamic view as follows:

      SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Oracle Label Security';
      
    2. Run the olspredowngrade.sql script.

      SQL> @ORACLE_HOME/rdbms/admin/olspredowngrade.sql
      

      See Oracle Label Security Administrator's Guide for more information.

  5. If you have enabled unified auditing, then optionally back up and purge the unified audit trail.

    1. Find if unified audit records exist.

    2. Back up the existing audit data to a table. For example:

      SQL> CREATE TABLE UA_DATA AS (SELECT * FROM V$UNIFIED_AUDIT_TRAIL);
      
    3. Clean up the audit trail.

      EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, use_last_arch_timestamp => FALSE);
      
  6. If you previously installed a recent version of the time zone file and used the DBMS_DST PL/SQL package to upgrade TIMESTAMP WITH TIME ZONE data to that version, then install the same version of the time zone file in the release to which you are downgrading. If after the database upgrade, you had used DBMS_DST to upgrade the TIMESTAMP WITH TIME ZONE data to the latest version of the time zone file, then install this latest version time zone file in the release to which you are downgrading. This ensures that your TIMESTAMP WITH TIME ZONE data is not logically corrupted during retrieval.

    To find which timze zone file version your database is currently using, query V$TIMEZONE_FILE as follows:

    SQL> select * from  V$TIMEZONE_FILE;
    

    Copy the time zone files from the current Oracle home to the Orache home to which you are downgrading. For example, after you see the results of your query, you might choose from the following:

    %ORACLE_HOME%\oracore\zoneinfo\timezlrg_18.dat
    %ORACLE_HOME%\oracore\zoneinfo\timezone_18.dat
    

    See Oracle Database Globalization Support Guide for more information on upgrading time zone files, and Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_DST package.

  7. If you set the ORA_TZFILE environment variable to the full path name of the time zone file when you upgraded to Oracle Database 12c, then you must unset it if you subsequently downgrade your database.

    Two time zone files are included in the Oracle home directory:

    • The default time zone file, which is for the database server, at

      $ORACLE_HOME/oracore/zoneinfo/timezonelrg.dat
      
    • A smaller time zone file, which is typically used for clients, at

      $ORACLE_HOME/oracore/zoneinfo/timezone.dat
      

    If you do not unset the ORA_TZFILE variable, then connecting to the database using the smaller time zone file might produce the following errors:

    SP2-1503: Unable to initialize Oracle call interface
    SP2-0152: ORACLE may not be functioning properly
    

    See Oracle Database Globalization Support Guide for more information about date and time data types and time zone support.

  8. Optional: If you are downgrading to Oracle Database 11g Release 1 (11.1.0.7) and you have Oracle Application Express in your database, then you must copy the apxrelod.sql file from the Oracle Database 12c ORACLE_HOME/apex/ directory to a directory outside of the Oracle home, such as a temporary directory on your system.

    Make a note of the new location of this file. You need this later in the procedure.

  9. If you have created objects based on fixed objects, then drop these objects to avoid possible ORA-00600 errors. You can re-create these objects after the downgrade.

  10. If you have Oracle Enterprise Manager configured in your database, then drop the Enterprise Manager user:

    DROP USER sysman CASCADE;
    

    Note:

    After this step, MGMT* synonyms may be invalid. You must reconfigure Oracle Enterprise Manager to use any Oracle Enterprise Manager controls in the downgraded database.

6.5 Downgrading a CDB or Non-CDB Oracle Database

Oracle provides a procedure for downgrading your Oracle Database 12c database to a supported major release or a relevant patchset upgrade. If you are downgrading from release 12.1.0.2 or higher to release 12.1.0.1, 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. See "Downgrading a Single Pluggable Oracle Database (PDB)" for the procedure for downgrading one PDB.

The procedure in this section assumes that you understand "Supported Releases for Downgrading Oracle Database."

To downgrade the database, whether a CDB or not, to an earlier supported release:

  1. Log in to the system as the owner of the Oracle Database 12c Oracle home directory.

  2. At a system prompt, change to the ORACLE_HOME/rdbms/admin directory, 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 CLUSTER_DATABASE initialization parameter to FALSE. After the downgrade, you must set this parameter back to TRUE.
  3. Using SQL*Plus, connect to the database instance as a user with SYSDBA privileges.

    sqlplus sys as sysdba
    Enter password: password
    
  4. Log in to the system as the owner of the Oracle home under the new Oracle Database 12c.

  5. From the ORACLE_HOME/rdbms/admin directory, start SQL*Plus.

  6. Connect to the database to be upgraded using an account with DBA privileges:

    CONNECT / AS SYSDBA
    
  7. Start the instance in downgrade mode by issuing the following SQL*Plus command for your Oracle Database instance type. You may be required to use the PFILE option to specify the location of your initialization parameter file.

    For Non-CDB instances:

    SQL> startup downgrade pfile=pfile_name
    

    For 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 Oracle Database Administrator's Guide for information about specifying initialization parameters at startup and the initialization parameter file.

  8. Optional: If you are downgrading a non-CDB, you can set the system to spool results to a log file so you can track the changes and issues. (A CDB automatically spools output to the catcon_logs; therefore, you can skip this step for a CDB.)

    SQL> SPOOL downgrade.log
    
  9. Run catdwgrd.sql.

    For a non-CDB:

    SQL> @catdwgrd.sql
    

    For a 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 catcon.pl is run with catdwgrd. 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). You must use the -r parameter to run the two scripts together.

    Note:

    • You must use the version of the catdwgrd.sql script included with Oracle Database 12c.

    • You must run catdwgrd.sql in the Oracle Database 12c environment.

    • You must specify the -r parameter in order to run the two scripts together at the same time.

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

    If you encounter any problems when you run the catdwgrd.sql script, or any of the scripts in the remaining steps, then correct the causes of the problems and rerun the script. You can rerun any of the scripts described in this chapter as many times as necessary.

    Caution:

    If the downgrade for a component fails, then an ORA-39709 error is displayed and 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. You must identify and fix the problem before rerunning the catdwgrd.sql script.
  10. For non-CDB only, turn off the spooling of script results to the log file:

    SQL> SPOOL OFF
    

    Then, check the spool file and verify that there were no errors generated during the downgrade. You named the spool file in Step 8 and the suggested name was downgrade.log. Correct any problems you find in this file and rerun the downgrade script if necessary.

    Note:

    If you want to save the results of the first time you ran the downgrade script, then before you rerun it be sure to rename downgrade.log to something else.
  11. Shut down the instance:

    SQL> SHUTDOWN IMMEDIATE
    
  12. Exit SQL*Plus.

  13. 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:

    • ORACLE_HOME

    • PATH

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

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

  14. If your operating system is Windows, then complete the following steps:

    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 Oracle Database Platform Guide for Microsoft Windows for more information about stopping Oracle services on Windows.

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

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

      C:\> ORADIM -DELETE -SID ORCL
      
    3. Create the Oracle service of the database that you are downgrading at a command prompt using the ORADIM command.

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

      See Oracle Database Platform Guide for Microsoft Windows for information about administering an Oracle Database instance using ORADIM. The syntax for ORADIM includes the following variables:

      Variable Description
      SID Same SID name as the SID of the database being downgraded.
      PASSWORD Password for the database instance. This is the password for the user connected with SYSDBA privileges. The -INTPWD option is not required. If you are prompted for a password, use the password for the standard user account for this Windows platform.
      USERS Maximum number of users who 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 -PFILE option, including drive letter of the Oracle home directory. See Oracle Database Administrator's Guide for information about specifying initialization parameters at startup and 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 MANUAL -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.

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

    If this is an Oracle RAC database, execute 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 CLUSTER_DATABASE initialization parameter to FALSE. After the downgrade, you must set this initialization parameter back to TRUE.

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

  16. At a system prompt, change to the ORACLE_HOME/rdbms/admin directory of the earlier release to which you are downgrading.

  17. 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 database mount;
    alter database open upgrade;
    alter pluggable database all open upgrade;
    
  18. 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
    
  19. Run catrelod.sql.

    For a non-CDB:

    SQL> @catrelod.sql
    

    For a CDB:

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

    The catrelod.sql script reloads the appropriate version for each of the database components in the downgraded database.

  20. If you are downgrading to Oracle Database 11g Release 1 (11.1.0.7) and you have Oracle Application Express in your database, then change to the directory to which you had copied the apxrelod.sql script. Manually reload Oracle Application Express by running the apxrelod.sql script:

    SQL> @apxrelod.sql
    

    Running the apxrelod.sql script avoids package APEX_030200.WWV_FLOW_HELP being INVALID due to the following error:

    PLS-00201: identifier 'CTX_DDL' must be declared
    
  21. If you had turned spooling on for a non-CDB, now turn off the spooling of script results to the log file:

    SQL> SPOOL OFF
    

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

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

    SQL> SHUTDOWN IMMEDIATE
    SQL> STARTUP
    

    You might be required to use the PFILE option to specify the location of your initialization parameter file. See Oracle Database Administrator's Guide for information about specifying initialization parameters at startup and the initialization parameter file.

  23. Perform this step if the database is configured for Oracle Label Security.

    1. Copy the olstrig.sql script from the Oracle home under Oracle Database 12c to the Oracle home of the release to which the database is to be downgraded.

    2. From the Oracle home of the release you are downgrading to, run olstrig.sql to re-create DML triggers on tables with Oracle Label Security policies:

      SQL> @olstrig.sql
      

      See Oracle Label Security Administrator's Guide for more information about Oracle Label Security.

  24. Run the utlrp.sql script.

    For a non-CDB:

    SQL> @utlrp.sql
    

    For a CDB run catcon.pl to invoke utlrp.sql to recompile any remaining stored PL/SQL and Java code. Use the following syntax:

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

    The utlrp.sql script recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, types, and so on. The log file utlrp0.log is generated with the results of the recompilations.

  25. Exit SQL*Plus.

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

    [where db-unique-name is the database name (not the instance name), oraclehome is the location of the old Oracle home for the downgraded database, and to_version is the database release to which the database is downgraded. (For example: 11.2.0.3.0.)]

    Note:

    Execute this command from the current Oracle Database 12c Oracle home, not from the Oracle home to which the database is being downgraded.

Your database is now downgraded.

6.6 Downgrading a Single Pluggable Oracle Database (PDB)

If you are downgrading the latest release of Oracle Database 12c, you can downgrade one PDB without downgrading the whole CDB. For example, you can unplug a PDB from a release 12.1.0.2 CDB, downgrade it, and then plug it in to a release 12.1.0.1 CDB or convert the database to a standalone database.

The procedure in this section assumes that you understand "Supported Releases for Downgrading Oracle Database."

Step 1   Downgrade the PDB

In this step you downgrade the PDB to release 12.1.0.1.

  1. Start up the release 12.1.0.2 PDB in DOWNGRADE mode. The CDB can be in normal mode when you do this.

    SQL> alter pluggable database CDB1_PDB1 open downgrade;
    
  2. Downgrade the PDB by running catdwgrd, which in this example is PDB1.

    Run catdwgrd as follows:

    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -d
     $ORACLE_HOME/rdbms/admin -n 1 -l <output directory> -e -b catdwgrd -c 'PDB1'
     catdwgrd.sql
    

    In the example catcon.pl is run with catdwgrd. 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). You must use the -r parameter to run the two scripts together at the same time.

  3. Close the PDB.

Step 2   Unplug the PDB from the CDB

In this step you unplug the downgraded PDB from the release 12.1.0.2 CDB.

  1. Connect to the 12.1.0.2 CDB.

  2. Close the PDB to be unplugged.

    SQL> alter pluggable database PDB1 close;
    
  3. Unplug the downgraded 12.1.0.1 PDB.

    SQL> alter pluggable database PDB1 unplug into 'path/PDB1.xml';
    

    You will see Pluggable database altered.

Step 3   Plug in the Downgraded 12.1.0.1 PDB

In this step you plug the downgraded 12.1.0.1 PDB into the 12.1.0.1 CDB. To do this, you must create the PDB in this CDB. In this example, we create PDB1.

  1. Connect to the 12.1.0.1 CDB.

  2. Plug in the 12.1.0.1 PDB.

    SQL> create pluggable database PDB1 using 'path/PDB1.xml';
    

    You will see Pluggable database created.

  3. Open the PDB in upgrade mode in order to run the scripts shown in the subsequent steps in this procedure.

    SQL> alter pluggable database PDB1 open upgrade;
    
  4. Connect to the PDB:

    SQL> alter session set container=PDB1;
    
  5. Run catrelod in the PDB:

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

    The catrelod.sql script reloads the appropriate version for each of the database components in the downgraded database.

  6. Run utlrp in the PDB:

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

    The utlrp.sql script recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, types, and so on.

6.7 Post-Downgrade Tasks for Oracle Database Downgrades

Additional tasks may be required after downgrading an Oracle database due to changes that affect compatibility, components, and supported protocols.

This section contains the following topics:

6.7.1 Oracle XML DB Authentication Recommendations for an Oracle Database Downgrade

If you downgrade to a release that is earlier than Oracle Database 12c, in which digest authentication is not supported, digest authentication is disabled and made unavailable as an authentication choice. This affects HTTP authentication for Oracle XML DB Repository. If you did not take advantage of digest authentication and instead used the default configuration, then no further actions are necessary.

See Also:

Oracle XML DB Developer's Guide for information about authentication with Oracle XML DB for database installation, upgrade, and downgrade

6.7.2 Re-creating the Network Listener When Downgrading to Release 11.1.0.7

If you are downgrading to Oracle Database 11g Release 1 (11.1.0.7), then you must re-create the listener after performing the downgrade steps. Run Oracle Net Manager to delete the old listener and create a new listener.

  • To start Oracle Net Manager from the Oracle Enterprise Manager console, select Service Management from the Tools menu, then select Oracle Net Manager.

  • On Windows, select Programs from the Start menu, and then select Oracle - HOME_NAME. Next, select Configuration and Migration Tools, and then Net Manager.

6.7.3 Re-enabling Oracle Database Vault after Downgrading Oracle Database

If you use Oracle Database Vault, then you may have been instructed to disable it before downgrading your database. To use Oracle Database Vault after downgrading, you must re-enable it.

See Also:

Oracle Database Vault Administrator's Guide for the complete procedure

6.7.4 Restoration of the Configuration for Oracle Clusterware

You can restore the Oracle Clusterware configuration to the state it was in before the Oracle Clusterware 12c Release 1 (12.1) upgrade. To restore the configuration, you must restore the release from which you were upgrading. Any configuration changes that you have performed during or after the Oracle Database 12c upgrade process are removed and cannot be recovered.

6.7.5 Restoring Oracle Enterprise Manager after Downgrading Oracle Database

The restore task described in this section is required only if you are downgrading in some form and Oracle Enterprise Manager is configured on the host. To restore Oracle Enterprise Manager to its previous state, you must have saved your Oracle Enterprise Manager configuration files and data before upgrading. You restore your configuration by first running the Oracle Enterprise Manager configuration assistant (EMCA) as described in this section, and then running the emdwgrd utility as described in "Running the emdwgrd utility to restore Enterprise Manager Database Control."

If this is an Oracle RAC database using Oracle Clusterware, the database must be registered with the srvctl before running the emca -restore command. This needs to be executed from the ORACLE_HOME/bin of the release to which the database is being downgraded.

See Also:

Oracle Clusterware Administration and Deployment Guide for information about srvctl

Run the emca -restore command with the appropriate options to restore Oracle Enterprise Manager Database Control or Grid Control to the old Oracle home. Important: Use the Oracle Database 12c version of emca for this procedure. See Oracle Database Administrator's Guide for complete information about emca.

The options that you specify for emca depend on whether the database being downgraded is a single-instance database, an Oracle RAC database, or an Oracle ASM database as follows:

Running emca on a Single-Instance Oracle Database Without Oracle ASM

ORACLE_HOME/bin/emca -restore db

You are prompted to enter the following information:

  • Oracle home for the database to be restored

  • Database SID

  • Listener port number

Running emca on an Oracle RAC Database Without Oracle ASM

ORACLE_HOME/bin/emca -restore db -cluster

You are prompted to enter the following information:

  • Oracle home for the database to be restored

  • Database unique name

  • Listener port number

Running emca on a Single-Instance Oracle ASM Instance

ORACLE_HOME/bin/emca -restore asm

You are prompted to enter the following information:

  • Oracle home for the database to be restored

  • Oracle ASM port

  • Oracle ASM SID

Running emca on an Oracle ASM on Oracle RAC Instance

ORACLE_HOME/bin/emca -restore asm -cluster

You are prompted to enter the following information:

  • Oracle home for the database to be restored

  • Oracle ASM port

Running emca on a Single-Instance Oracle Database With Oracle ASM

ORACLE_HOME/bin/emca -restore db_asm

You are prompted to enter the following information:

  • Oracle home for the database to be restored

  • Database SID

  • Listener port number

  • Oracle ASM port

  • Oracle ASM home

  • Oracle ASM SID [+ASM]

Running emca on an Oracle RAC Database and Oracle ASM Instance

ORACLE_HOME/bin/emca -restore db_asm -cluster

You are prompted to enter the following information:

  • Oracle home for the database to be restored

  • Database unique name

  • Listener port number

  • Oracle ASM port

  • Oracle ASM Oracle home

  • Oracle ASM SID [+ASM]

The output of emca varies according to the options that you specify and the values that you enter at the prompts. This step must be repeated on all the nodes in Oracle RAC environments.

You must now run the emdwgrd utility to restore Oracle Enterprise Manager Database Control and data.

6.7.5.1 Running the emdwgrd utility to restore Enterprise Manager Database Control

After completing the emca -restore procedure you are ready to restore the Oracle Enterprise Manager Database Control and data with the emdwgrd utility. You must run the emdwgrd utility from the Oracle Database 12c home. Then ORACLE_HOME and other environment variables must be set to point to the Oracle home from which the upgrade originally took place.

The following procedure is for Linux and UNIX. To run it on Windows, simply substitute emdwgrd.bat for emdwgrd.

Follow these steps to restore Oracle Enterprise Manager Database Control and data:

  1. Set ORACLE_HOME to the Oracle home from which the database upgrade originally took place.

  2. Set ORACLE_SID to the SID of the database that was upgraded and then downgraded.

  3. Set PATH, LD_LIBRARY_PATH and SHLIB_PATH to point to the Oracle home from which the database upgrade originally took place.

  4. Go to the Oracle Database 12c Oracle home:

    cd ORACLE_HOME/bin
    
  5. Execute one of the following:

    1. For a single-instance database, run the following command, where SID is the SID of the database that was upgraded and then downgraded and save_directory is the path to the storage location you chose when saving your database control files and data:

      emdwgrd -restore -sid SID -path save_sirectory -tempTablespace TEMP
      
    2. For an Oracle RAC database, remote copy is required across the cluster nodes. Define an environment variable to indicate which remote copy is configured. For example:

      setenv EM_REMCP /usr/bin/scp
      

      Then, execute the following restore command:

      emdwgrd -restore -tempTablespace TEMP -cluster -sid SID10g -path save_directory
      

      If the Oracle home is on a shared device, add -shared to the previous command line.

  6. Enter the SYS and SYSMAN passwords when prompted by emdwgrd.

  7. When emdwgrd completes, Oracle Enterprise Manager Database Control is downgraded to the old Oracle home.

6.7.6 Restoring Oracle Application Express to the Earlier Version

If the database that was upgraded had an Oracle Application Express version earlier than version 4.2, then downgrading the database will have caused Oracle Application Express to also be downgraded. You will need to compile the SYS-owned objects WWV_FLOW_VAL and WWV_DBMS_SQL from the earlier version of the Oracle Application Express source.

  1. Change your working directory to the core directory of the earlier version of Application Express source.

  2. Connect to the database as SYS / SYSDBA.

  3. Run the following scripts in the order shown:

    1. @wwv_flow_val.plb

    2. @wwv_dbms_sql.sql

    3. @wwv_dbms_sql.plb

  4. If you were using the embedded PL/SQL gateway of the Oracle XML DB Protocol Server, you will need to restore the images directory to the earlier version as follows.

    1. Change your working directory to the earlier version of the Application Express source.

    2. Invoke apxldimg.sql connected as SYS as SYSDBA passing the path to the earlier version source as in the following example.

      @apxldimg.sql /tmp/apex32
      

6.8 Troubleshooting the Downgrade of Oracle Database

Oracle provides troubleshooting information for issues that may occur when downgrading Oracle Database. This section contains known errors that may occur and corresponding workarounds when needed.

This section contains the following topics:

6.8.1 Oracle Multimedia Downgrade and imrelod.sql Script Error

When downgrading Oracle Database from release 12.1 to release 11.2.0.2, an error may be raised from the imrelod.sql script, which is included with release 11.2.0.2.

ORA-20000: Oracle ORDIM component in registry is status: DOWNGRADED. Oracle ORDIM must be installed and valid prior to Oracle Multimedia upgrade, downgrade, or patch.
Cause: The imrelod.sql script raises this error because it does not know the status of ORDIM.
Action: No action. This error can safely be ignored.

6.8.2 Oracle Database Vault and dvrelod.sql Script Error

When downgrading Oracle Database from release 12.1 to release 11.2.0.3, databases that use Oracle Database Vault may encounter an error from the dvrelod.sql script, which is included with release 11.2.0.3.

ORA-31011: XML parsing failed; Oracle Database Vault downgrade to release 11.2.0.3
Cause: The dvrelod.sql script does not know the status of XML parsing.
Action: No action. This error can safely be ignored.

6.8.3 Downgrading Grid Infrastructure Standalone (Oracle Restart) After Successful or Failed Upgrade

In order to downgrade Oracle Restart, you must deconfigure and then reinstall Oracle Grid Infrastructure. You can then add back the databases and services.

See Also:

6.8.4 Issue with Downgrading Oracle ACFS and Oracle Grid Infrastructure

If you use Oracle ASM Cluster File System (Oracle ACFS), and you upgrade to Oracle Grid Infrastructure 12c, either for a cluster or for an Oracle Restart standalone server, and then you choose to downgrade to Oracle Grid Infrastructure 11g Release 2 (11.2), you must run the command acfsroot install from the release 11.2 Oracle home before you attempt to start the release 11.2 software stack.

See Also:

Oracle Database Readme and the release notes for your operating system platform for the latest information about isssues and solutions

6.8.5 Issue with Password for Database Links After Downgrading

After downgrading to Oracle Database release 11.1, you are required to reset the passwords for any database links that were created in releases 11.2 or 12.1. See "Using Oracle Data Pump Export to Create a Dump File Containing All Existing Database Links." Unless the database link password is reset, an internal error will be displayed when anyone attempts to make use of the database link. For reference, the internal error which is reported in the oracle trace file when the oracle server fails to retrieve the password of the database link is as follows: "ORA-00600: [kzdlk_zt2 err], [18446744073709551601]".

To reset the password for the database link after downgrading to release 11.1, use the ALTER DATABASE LINK command to change the password back to the original password by specifying the original password in the IDENTIFIED BY clause. See Oracle Database SQL Language Reference for information about ALTER DATABASE LINK, and Oracle Database Administrator's Guide for information about controlling connections established by database links.

If you want to create new database links while running Oracle Database release 11.2 or 12.1 that do not have this password issue, then you can contact Oracle support for information on how to use the IDENTIFIED BY VALUES clause of the CREATE DATABASE LINK command.

6.8.5.1 Using Oracle Data Pump Export to Create a Dump File Containing All Existing Database Links

Before performing the downgrade, please use Oracle Data Pump export to create a dump file that contains all the existing database links. This will include any newly-created database links. The procedure uses the FULL=Y and INCLUDE=DB_LINK parameters with the expdp command.To create the dump file that contains all the existing database links:

  1. Log in to SQL*Plus.

    sqlplus system/manager
    
  2. Drop the dump directory in case one exists.

    SQL> DROP DIRECTORY dpump_dir;
    SQL> CREATE DIRECTORY dpump_dir AS '/location_to_write_datapump_dump_file';
    
  3. Export the database links as follows:

    $ expdp system/manager FULL=Y directory=dpump_dir 
      dumpfile=saved_dblinks.dmp INCLUDE=DB_LINK;
    
  4. Optional step for troubleshooting. After the downgrade, if any of the downgraded database links are not working properly (for example, exhibiting the internal error ORA-00600), they can be dropped and imported from the dump file as follows, after which they should work as intended.

    $ impdp system/manager directory=dpump_dir dumpfile=saved_dblinks.dmp;