19c downgrade

admin

Doc ID 2548962.1


Applies to:  Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 11.2.0.4 to 19.3.0.0.0 [Release 11.2 to 19]
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Goal How to Downgrade a 19c Non CDB or CDB Database to Previous Release
SolutionPurpose :
This document is created for use as a guideline and checklist for downgrading a previously upgraded database from Oracle 19c back to the Previous release: 12.2.0.1,12.1.0.2,11.2.0.4
An important note is that when downgrading a database instance from the current version to the version prior to the upgrade, the database IS NOT returned to the same state as it was pre-upgrade. Depending on the versions involved, the upgrade process makes changes that are not reversible. The downgrade process allows users to open and access the database instance in the previous version. This is usually sufficient.
Additional corrective actions (such as de-install / re-install or re-upgrade to current patchset levels) may be needed to settle left-over issues after a downgrade. If the goal is to have the instance back EXACTLY as it was pre-upgrade then other processes including a recovery to just before the upgrade should be used.
Table : Supported Releases and Editions for Downgrading
Downgrade Compatible MatrixOracle Database release Downgradable( Yes / No )
18cYes
12.2.0.1Yes
12.1.0.2Yes
11.2.0.4Yes for non-CDBs







Points to Note :

• You cannot downgrade to releases earlier than the minimum compatibility setting for the new Oracle Database release.
• You can downgrade a non-CDB from 19 to 18, or 12.2 to 12.1.0.2, or 11.2.0.4
• If you have changed your compatible parameter during upgrade to 19c  , then it is not possible to downgradeYou cannot downgrade if the database parameter COMPATIBLE was ever set higher than the version you upgraded from
  
  

• If you unplug an Oracle Database 12c release 1 (12.1.0.2) PDB from a 12.1.0.2 CDB database, and then plug this PDB into a 12.2.0.1 database for upgrade, then you cannot downgrade this PDB if the compatible initialization parameter in the 12.2.0.1 database is higher than '12.1.0.2.0'
• Install the latest bundle patch or patch set update (BP or PSU) before you downgrade a CDB, or before you unplug and downgrade a PDB. Patches (PSU, BP) are available for download on My Oracle Support. Refer to My Oracle Support note 756671.1 to obtain the latest patch set
  

• Oracle Enterprise Manager - If you downgrade to an earlier supported release, then you must reconfigure Oracle Enterprise Manager controls.
  

• Before you start your upgrade, you must use the emdwgrd utility to save DB Control files and data, so that you can restore Oracle Enterprise Manager Database Control (DB Control) after a downgrade.
  

• Oracle Database Express Edition - You cannot downgrade a database that is upgraded from Oracle Database Express Edition.
  

• For Oracle ASM disk groups, if you change disk group compatibility to 12.1.0.0.0 when you upgrade your database, then when you downgrade to the earlier release, you cannot 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 Also:Oracle Automatic Storage Management Administrator's Guide for information about Oracle ASM disk group compatibility
  

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

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

               following implications of this object privilege:

               a) If you have the SELECT and READ object privileges, then the READ privilege is removed.

               b) If you previously only had the READ object privilege, then the READ object privilege is transformed

                  into the SELECT object privilege. Refer to Oracle Database Security Guide for more information about

                 the READ and SELECT object privileges.

• If Oracle XML DB is not installed in the database that you upgrade, then during a downgrade, Oracle XML DB is uninstalled. For example, if you did not install Oracle XML DB with Oracle Database 11g Release 2 (11.2), then Oracle XML DB is installed with Oracle Database 12c. If you downgrade the database, then Oracle XML DB is uninstalled as part of the downgrade. Oracle XML DB is included by default with Oracle Database 12c release 1 (12.1), and later releases.
  

• During upgrade to Oracle Database 12c, the Database (DB) Control repository is removed. If you downgrade to an earlier release, then you must reconfigure the Database (DB) Control to use it after the downgrade.
  

Performing Required Predowngrade Steps for Oracle Database :
Complete the required preparation steps described here before you downgrade Oracle Database to the earlier release from which you upgraded.
Before you start a downgrade, you must resolve incompatibilities between database releases. For example, determine if you must disable components in the database before you start the downgrade.
1. If you are downgrading a CDB or unplugging and downgrading a PDB in Oracle Database, then you must first apply the latest quarterly release update (RU), release update revision (RUR), bundle patch or patch set update (BP or PSU) available. Apply any required set of additional fixes on My Oracle Support Note 756671.1
2. If you have enabled Oracle Database Vault on your database, then disable Oracle Database Vault before downgrading the database.Use DBA_DV_STATUS to find out if Oracle Database Vault is enabled:         
SQL> SELECT * FROM CDB_DV_STATUS;
If the output is TRUE, then Oracle Database Vault is enabled, so you must disable it.
3. If your database uses Oracle Label Security, and you are downgrading to release 11.2, then run the Oracle Label Security (OLS) preprocess downgrade olspredowngrade.sql script in the new Oracle Database 12c Oracle home.
Caution:
Run olspredowngrade.sql before you downgrade from Oracle Database 12c to database release 11.2 for databases that use Oracle Label Security and Oracle Database Vault.
a. Query the V$OPTION dynamic view. Determine if Oracle Label Security is enabled. For example:
SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Oracle Label Security';
b. Run the olspredowngrade.sql script:
SQL> @ORACLE_HOME/rdbms/admin/olspredowngrade.sql

4. If you have enabled Unified Auditing, then you can choose to back up and purge the unified audit trail:
a . Find if unified audit records exist.
SQL> SELECT COUNT(*) FROM UNIFIED_AUDIT_TRAIL;
b. Back up the existing audit data to a table. For example:
SQL> CREATE TABLE UA_DATA AS (SELECT * FROM UNIFIED_AUDIT_TRAIL);
c. 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);
5. Before downgrade, ensure that the target Oracle Home for the downgraded database contains the version of the time zone file that your database is using.
To find which time zone file version your database is currently using, query V$TIMEZONE_FILE using the following command:
SQL> select * from V$TIMEZONE_FILE;
For example:
If the query returns timezlrg_18.dat in the column V$TIMEZONE_FILE.FILENAME, then check if the file is present in the target Oracle Home:
Linux and UNIX
$ORACLE_HOME/oracore/zoneinfo/timezlrg_18.dat
Windows
%ORACLE_HOME%\oracore\zoneinfo\timezlrg_18.dat
If the required time zone file is missing from the target Oracle Home, then do one of the following:

• If you installed the current version of the time zone file as a patch, and you still know the patch number, then use the same patch number to download   the  corresponding time zone file for the target release from the My Oracle Support website.
• Locate the correct patch by using the My Oracle Support website patch search function. Enter the following search criteria: "Product is 'Oracle Database'", "Release is 'target release'", and "Description contains 'DST'".
• If you cannot locate the patch on the My Oracle Support website, then log a service request with Oracle Support.
• After you find and download the required patch, install it in the target Oracle Home.
  

6. If you 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.
7. If you have Oracle Enterprise Manager configured in your database, then drop the Enterprise Manager user:            
DROP USER sysman CASCADE;
Note:
After you drop the Enterprise Manager user, you can find that MGMT* synonyms are invalid. You must reconfigure Oracle Enterprise Manager to use any Oracle Enterprise Manager controls in the downgraded database.
8. If you are downgrading to Oracle Database 12c release 1 (12.1.0.2) that has the JSON bundled patch applied, and that uses Simple Oracle Document Access (SODA), then ensure that you apply the JSON bundle patch (JSON Patch Bundle 1 or JSON Patch Bundle 2) to the Oracle Database 12.1.0.2 binary. For more information, review the following My Oracle Support note: 1992767.1
9. Obtain the appropriate ARUs for your server operating system from the PSE listed for your release. Download and install all the patches listed for your earlier                release before you start the downgrade:

• Log in to My Oracle Support: https://support.oracle.com
• Select Patches & Updates. In the Patch Search frame, provide the bug number listed for your release, and provide the platform for your server.
• Download and install the patch
• Repeat until you have installed all required patches for your server.
  

Obtain the patches for your release:
Downgrade from 19c to 12.2

• 2539751.1: Patches to apply before upgrading Oracle GI and DB to 19c or downgrading to previous release
  
• 2118136.2: Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU (CPU), Bundle Patches, Patchsets and Base Releases
  
  

Downgrade from 19c to 12.1.0.2.0 with CDB and PDB

• 25755742: DATABASE PATCH SET UPDATE 12.1.0.2.170718 or latest 12.1.0.2.0 Database patch set update
  
• 24438034: CDB_UPG: 12101 CDB UPGRADE LEFT OBJECT CDB_JAVA_POLICY INVALID
  
• 20348910: ALTER TYPE REPLACE IN PRVTAQJI.SQL TO BE REPLACE WITH CREATE OR REPLACE TYPE
  
• 20958816: INVALID OBJECTS AFTER DOWNGRADE FROM 12.2.0.1 TO 12.1.0.2
  
  

Downgrade from 19c to 12.1.0.2.0 non-CDB

• 25755742: DATABASE PATCH SET UPDATE 12.1.0.2.170718 or latest 12.1.0.2.0 Database patch set update
  
• 24438034: CDB_UPG: 12101 CDB UPGRADE LEFT OBJECT CDB_JAVA_POLICY INVALID
  
• 20348910 : ALTER TYPE REPLACE IN PRVTAQJI.SQL TO BE REPLACE WITH CREATE OR REPLACE TYPE
  
• 20958816: INVALID OBJECTS AFTER DOWNGRADE FROM 12.2.0.1 TO 12.1.0.2
  
• 21856522: UPGRADE OF 12.1 TO 12.2 CAUSE XOQ COMPONENT TO BE INVALID
  
  

Downgrade from 19c to 11.2.0.4.0

• 23054359: DATABASE PATCH SET UPDATE 11.2.0.4
  
• 20898997: XMLTYPESUP: QCTOXSNLB SHOULD NOT CHECK AGAINST SNAPSHOT SIZE
  
• 20348910: ALTER TYPE REPLACE IN PRVTAQJI.SQL TO BE REPLACE WITH CREATE OR REPLACE TYPE
  
  

9. If there are any one-off patches applied on the source database running from the upgraded home, then roll these patches back before beginning the downgrade process
Steps To downgrade :-

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

1. Log in to the system as the owner of the Oracle Database Oracle home directory.
2. 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.
CLUSTER_DATABASE=FALSE
4.Log in to the system as the Oracle user (owner) of the new Oracle Database release Oracle home .
5.Change directory to ORACLE_HOME/rdbms/admin, and start SQL*Plus.
6.Connect to the database that you want to upgrade 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 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
Linux and Unix
$cd $ORACLE_HOME/bin $./dbdowngradeMicrosoft Windows
$cd %ORACLE_HOME%\bin$dbdowngrade.cmdCDB Database as a whole :
SQL> startup downgrade pfile=pfile_nameSQL> alter pluggable database all open downgrade;$cd $ORACLE_HOME/bin $./dbdowngrade  
Running with inclusion list for CDB
Linux and Unix
CDB instances:
SQL> startup downgrade pfile=pfile_name SQL> alter pluggable database all open downgrade;
$cd $ORACLE_HOME/bin $./dbdowngrade –c 'PDB1 PDB2 PDBN'Microsoft Windows
$cd %ORACLE_HOME%\bin $dbdowngrade.cmd –c "PDB1 PDB2 PDBN"
8.(Optional)If you are downgrading a non-CDB, then you can set the system to spool results to a log file so you can track the changes and issues.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:
SQL> SPOOL downgrade.log
SQL> @catdwgrd.sql

9.Use the following command to start the downgrade, depending on your configuration:
$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)

• Use the version of the catdwgrd.sql script included with Oracle Database 19c.
• Run catdwgrd.sql in the Oracle Database 19c 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.
  

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.
10.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
Next, 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:
You can 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.
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
  

Also check that your oratab file, and any client scripts that set the value of ORACLE_HOME, point to the downgraded Oracle home.
14. If your operating system is Windows, then complete the following steps:
a) 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
b) 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
c) 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:
ORADIM SyntaxVariableDescription
SIDSame system identifier (SID) name as the SID of the database being downgraded.
PASSWORDPassword 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.
USERSMaximum number of users that can be granted SYSDBA and SYSOPER privileges.
ORACLE_HOMEOracle 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.







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.

15. 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.
16 . 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.)
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 CDB :
SQL> connect / as sysdba
SQL> startup upgrade;
SQL> 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 on non-CDB databases, or use catcon.pl to run utlrp.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.
20. 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.
21. Shut down and restart the instance for normal operation.
SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP
22. 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.
a) 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.
b) 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
23. (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
24 .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.
25. 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.
26. Exit SQL*Plus.
27. If you changed the CLUSTER_DATABASE parameter to FALSE prior to the Downgrade then set it back to TRUE after Downgrade
CLUSTER_DATABASE=TRUE
28. 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.1.0.2.0.)
  

Note :

During a downgrade from Oracle Database 19c Standard Edition to Oracle Database 12c Release 2 (12.2.0.1) Standard Edition, you have OLAP and Spatial components upgraded to the 19c release versions, though they are set to FALSE. After the downgrade, the OLAP and Spatial components are still not available, but they are not reloaded into the 12.2.0.1 release. You then see this result in the DBA registry, where you have later release objects listed in your 12.2 registry:
OLAP Analytic Workspace             OPTION OFF   19.1.0Oracle OLAP API                     OPTION OFF   19.1.0
This result is expected behavior in downgrades from Oracle Database 18c and later to Oracle Database 12c. The reason that this behavior is expected is because the option setting for these options in Standard Edition databases is FALSE. During the upgrade, all options are upgraded, even though they are set to FALSE. During the run of catrelod.sql, the reload scripts do not run to replace these objects with the earlier release versions. As a result, downgrading back to Standard Edition leaves many OLAP and Spatial objects at their later release versions.