An Oracle White Paper

J uly 2011
Methods for Downgrading from Oracle
Database 11g Release 2


Downgrade Methods From Oracle Database 11g Release 2
1

Introduction ....................................................................................... 2
Oracle Database Downgrade Methods .............................................. 3
Downgrade Script .......................................................................... 3
Oracle Data Pump Export/Import and original Export/Import ......... 4
Oracle Streams ............................................................................. 6
Oracle GoldenGate........................................................................ 8
Conclusion ........................................................................................ 9
Downgrade Methods From Oracle Database 11g Release 2
2
Introduction
An important best practice when upgrading to Oracle Database 11g Release 2, or when
making any major change to a production database, is to always have a well tested fallback
strategy. Then, should something unforeseen happen with the production database, an
organization can recover and proceed with business as needed.
In some cases it may be acceptable for the fallback strategy to result in loss of changes that
were applied to the database over a period of time. For example, if the storage system suffers
a catastrophic hardware failure in the middle of a database upgrade, then restoring and
recovering a backup may be the best and fastest fallback strategy.
However, there are other situations where reverting to a previous backup may not meet your
business needs. For example, suppose that after upgrading to Oracle Database 11g Release
2, you find out a week later than a key application has not been certified with the new release.
As a result, it is necessary to downgrade back to the previous release. How can this be
accomplished without losing a full week of transactions and processing?
Several methods can be used to downgrade without loss of data. These include: Downgrade
script, Export and Import (including Data Pump Export and Import starting with Oracle
Database 10g), Oracle Streams, and Oracle GoldenGate. There is no data loss with any of
these methods, but each has characteristics that will make them more or less applicable to a
given downgrade scenario.
This white paper looks at these various downgrade methods and guidelines for their use. The
method you choose will depend on several factors, including the following:
Target database version
Different operating system architecture or hardware platform of source and target systems
Downtime requirements
Performance requirements
License Costs
Downgrade Methods From Oracle Database 11g Release 2
3
Oracle Database Downgrade Methods
Depending on the environment, there are several alternatives available when downgrading Oracle
Database. This section discusses why a particular method would be chosen, lists considerations when
using each method, and gives pointers to additional information.
Downgrade Script
A database downgrade can be accomplished by using the downgrade script, which is located in the file
$ORACLE_HOME/rdbms/admin/catdwgrd.sql. Downgrading in this manner does not rewind time
or make the database identical to its pre-upgrade state. Rather, it changes the data dictionary to be
compatible with the release from which the database was upgraded.
Oracle binaries of the release to which the user is downgrading should be installed on the server before
starting the downgrade process. If the Oracle executables to the desired release have been uninstalled,
the Oracle binaries need to be re-installed to the correct release and patch level for the downgrade.
The catdwgrd.sql script must be run in the Oracle Database 11g Release 2 environment (source). The
script downgrades all the Oracle Database components in the database, essentially undoing the upgrade
actions that brought the database up to the current release. If any problems are encountered when the
downgrade script is run, then they should be corrected and the script should be rerun. The script can
be rerun as many times as necessary.
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. The problem
must be identified and fixed before rerunning the catdwgrd.sql script.
After the downgrade script completes, the second step of the downgrade process is to run the reload
script (catrelod.sql) in the older (target) environment. This rebuilds the dictionary to the major release
or patch release from which the database was originally upgraded.
The major restriction on the downgrade script is that it cannot be run if the COMPATIBLE parameter
has been raised as part of, or after, the upgrade. This is because raising the COMPATIBLE parameter
will allow on-disk changes that cannot be understood by the earlier version of Oracle Database. For
this reason, downgrades from Oracle Database 11g Release 2 to Oracle Database 9i are not supported.
Downgrades from Oracle Database 11g Release 2 to the following versions are supported:
10.1.0.5
10.2.0.2, 10.2.0.3, 10.2.0.4, 10.2.0.5
11.1.0.6, 11.1.0.7

Downgrade Methods From Oracle Database 11g Release 2
4
Downgrade script is a good choice if the downgrade environment has the following characteristics:
Target database version is Oracle Database release 10.1.0.5 or later

Considerations when using Downgrade Script
Must run the catdwgrd.sql script in the Oracle Database 11g Release 2 environment and the
catrelod.sql script in the target environment
COMPATIBLE parameter must not have been changed during or after the upgrade to Oracle
Database 11g Release 2
Cannot downgrade to a system with a different operating system architecture
Not a minimal downtime method

For More Information
Oracle Database Upgrade Guide 11g Release 2 (Part Number E10819-02) Chapter 6
My Oracle Support Note 883335.1 How to Downgrade from Database 11.2 to Previous Releases
My Oracle Support Note 443890.1 Complete Checklist for Downgrading the Database from 11g to
Lower Releases
Oracle Data Pump Export/Import and original Export/Import
Both Oracle Data Pump Export/Import (expdp/impdp) and original Export/Import (exp/imp) can
be used to move data between different versions of Oracle Database. These tools are a flexible and
powerful way to downgrade a database if the need arises.
Starting with Oracle Database 10g, Oracle Data Pump Export (expdp) and Import (impdp) can be used
to downgrade from Oracle Database 11g Release 2 to any previous version from Oracle Database 10g
or later. If you are downgrading to Oracle Database 9i or lower, then original Export (exp) and Import
(imp) must be used. In addition to the versions available for downgrade, original exp/imp and Oracle
Data Pump expdp/impdp differ in the way that they are used to perform the downgrade itself.
Oracle Data Pump
Any version of Oracle Data Pump expdp is able to produce dump files compatible with previous
versions of Oracle Data Pump impdp. Therefore, to downgrade with Oracle Data Pump, the current
release of expdp (11.2) is used with the VERSI ON parameter set to the target version of the subsequent
import. The import is then performed using Oracle Data Pump impdp for the target release. The rule
to remember for Oracle Data Pump operations is to always use the Oracle Data Pump client that is the
same version as the database server being accessed.
For example, the following table shows that when you downgrade from Oracle Database 11g Release 2
to Oracle Database 10g Release 2, Oracle Data Pump Export Release 11.2 is used with the VERSION
Parameter set to 10.2. The import version that is used is Oracle Data Pump Import Release 10.2.
Downgrade Methods From Oracle Database 11g Release 2
5


Exporting from Oracle Database 11g Release 11.2 and Importing Into Oracle Database 11g Release 11.1, 10g
Release 2, or 10g Release 1
Export From Import To Export Version to Use Import Version to Use
Release 11.2 Release 11.1 Data Pump Export Release 11.2 with VERSION=11.1 Data Pump Import Release 11.1
Release 11.2 Release 10.2 Data Pump Export Release 11.2 with VERSION=10.2 Data Pump Import Release 10.2
Release 11.2 Release 10.1 Data Pump Export Release 11.2 with VERSION=10.1 Data Pump Import Release 10.1

Original Export and Import
With original Export, the rule is to run the exp and imp clients corresponding to the target database
version. For the import, you must run an older version of Export to produce a dump file that is
compatible with an older database version. Also, you must run the
$ORACLE_HOME/rdbms/admin/catexp.sql script to create the old export views in Oracle Database
11g Release 2.
For example, if you are downgrading from Oracle Database 11g Release 2 to Oracle Database 9i
Release 2, the following steps are required:
1. Create an empty database with the original Export and Import utilities.
2. Run the $ORACLE_HOME/rdbms/admin/catexp.sql script in Oracle Database 11g Release
2 to create the Oracle Database 9i Release 2 export views.
3. Use the Oracle Database 9i Release 2 export utility to export the data into a dump file.
4. Use the Oracle Database 9i Release 2 import utility to import the data and reconstruct the
database back to Oracle Database 9i Release 2.
To ensure a consistent export, Oracle Database 11g Release 2 cannot be available for updates during
the export. If changes are made to Oracle Database 11g Release 2 after the export, then those changes
must be propagated to the new database prior to making it available to users.
Oracle Data Pump Export and Import / original Export and Import are good choices if the
downgrade environment has the following characteristics:
The downgrade involves migration to a different operating system architecture or hardware platform
The downgrade is to Oracle Database 9i Release 2 or earlier (for original Export/Import) or Oracle
Database 10g or later (for Data Pump Export/Import)
COMPATIBLE has been raised during or since the upgrade to Oracle Database 11g Release 2

Downgrade Methods From Oracle Database 11g Release 2
6
Considerations when using Oracle Data Pump Export and Import / original Export and Import
Downtime may be much longer than other methods, depending on the size of the database (10+
hours for large databases)
Enough disk space is required to hold two copies of the database, plus the export dump file
Downgrade to Oracle Database 10g or higher is done using Oracle Data Pump Export and Import
Downgrade to Oracle Database 9i or lower is only possible using original Export/Import

For More Information
Oracle Database Utilities 11g Release 2 (Part Number E16536)
My Oracle Support Note 208237.1 How to Downgrade a Database Using Export/Import
My Oracle Support Note 158845.1 How to Export/Import if Source Database is Newer/Older Than
Target Database
My Oracle Support Note 553337.1 Export/Import Data Pump Parameter VERSION - Compatibility
of Data Pump Between Different Oracle Versions
Oracle Database Utilities Technology Portal
Oracle Streams
Oracle Streams is a solution that supports real-time replication between Oracle databases across
various database releases and platforms. It does this in three steps. First, an Oracle Streams capture
process retrieves change data extracted from the redo log of a database, either by hot mining the online
redo log or by mining archived log files with Oracle LogMiner. After retrieving the data, the capture
process formats it into a logical change record (LCR) and places it in a staging area for further
processing. The capture process can intelligently filter the LCRs so that only changes are captured.
Finally, the changes are applied to the replicated database.
You can use Oracle Streams to downgrade from Oracle Database 11g Release 2 to Oracle Database 11g
Release 1, Oracle Database 10g Release 2 and Release 1, and Oracle Database 9i Release 2.
Downgrading using Oracle Streams is one of the methods that results in the least downtime. The
process involves the following steps:
1. Create an Oracle Streams capture process for Oracle Database 11g Release 2.
2. Make a copy of Oracle Database 11g Release 2 using Oracle Recovery Manager (RMAN),
transportable tablespaces, a physical standby, or Oracle Data Pump (Export and Import).
While the copy is being made, the original database remains fully operational.
3. Downgrade the copy of the database. While that process is going on, any changes that are
occurring in the Oracle Database 11g Release 2 production environment are stored in the redo
logs.
Downgrade Methods From Oracle Database 11g Release 2
7
4. After the downgraded copy is available, configure Oracle Streams propagations and apply
processes. A propagation is configured at the source database and identifies the target
database. Apply processes are configured at the target database.
5. After Oracle Streams propagation is configured, synchronize the source and target databases
by starting the Oracle Streams processes on both releases of the database.
6. After the source and target databases are synchronized, switch users from the source system
to the target system. The only downtime is the time needed for clients to reconnect.
If users want to go back to the original Oracle Database 11g Release 2 environment, Oracle Streams
can be configured to synchronize the database in reverse as well.
Oracle Streams is a good choice if the downgrade environment has the following characteristics:
Zero downtime is a requirement
The downgrade involves migration to a different operating system architecture or hardware platform
The target database is Oracle Database 9i Release 2 or higher

Considerations when using Oracle Streams
Performance restrictions may occur in an OLTP environment if the Oracle Streams capture,
propagation, and apply processes cannot keep up with the transaction load on the source database
A significant amount of expertise is required by the database administrator
There are some data type restrictions

For More Information
Oracle Database Administrators Guide 11g Release 2
Oracle Streams Concepts and Administration 11g Release 2, Appendix D
Oracle Streams Technology Portal

Downgrade Methods From Oracle Database 11g Release 2
8
Oracle GoldenGate
Oracle GoldenGate is a replication solution that is similar in concept to Oracle Streams. The main
technical difference is that Oracle GoldenGate operates outside of the database, whereas Oracle
Streams operates inside the database. Oracle GoldenGate uses a different technology to capture
information from redo logs. Oracle GoldenGate also supports more data types than Oracle Streams.
Oracle GoldenGate can be used to downgrade from Oracle Database 11g Release 2 to Oracle Database
11g Release 1, Oracle Database 10g Release 2 and Release 1, Oracle Database 9i Release 2 and Release1,
and Oracle Database 8i Release 1. It is the method that requires the least downtime, and can be used
to move between different platforms. Oracle GoldenGate is a separately licensed option of Oracle
Database. The general process for using it to downgrade is as follows:

1. Turn on GoldenGate online capture to capture any changes that occur in the Oracle Database
11g Release 2 production database.
2. Make a copy of Oracle Database 11g Release 2 using Oracle Recovery Manager (RMAN),
transportable tablespaces, a physical standby, or Oracle Data Pump (Export and Import).
While making the copy, the original database remains fully operational.
3. Downgrade the copy of the database. While that process is going on, any changes that are
occurring in the production environment are being captured.
4. After the downgrade of the target copy of the database is complete, start the Oracle
GoldenGate apply process to synchronize the source and target databases.
5. Once everything is synchronized, switch the users from the source system to the target
system. Downtime is limited to the amount of time it takes to move the users or application
servers to the target system.
If users want to go back to the original Oracle Database 11g Release 2 environment, Oracle
GoldenGate can be configured to synchronize the database in reverse as well.
Oracle GoldenGate is a good choice if the downgrade environment has the following characteristics:
Zero downtime is a requirement
The downgrade involves migration to a different operating system architecture or hardware platform
The migration requires transformations from one data type to another

Considerations when using Oracle GoldenGate:
An Oracle GoldenGate license is required
There are some data type restrictions
For More Information:
Oracle GoldenGate Technology Portal
Downgrade Methods From Oracle Database 11g Release 2
9
Conclusion
Having a well tested fallback strategy is vital to any production database environment. When the need
arises, it may be possible to downgrade an Oracle database to an earlier version without losing data.
Choosing the appropriate downgrade method depends on the database environment, the amount of
downtime that is acceptable, and the DBAs knowledge and tolerance for complexity. It is important
for the DBA to understand the various downgrade methods and choose the one that best suits
business requirements.





Methods for Downgrading fromOracle
Database 11g Release 2
Author: Carol Palmer
Oracle Corporation
World Headquarters
500 Oracle Parkway
Redwood Shores, CA 94065
U.S.A.
Worldwide Inquiries:
Phone: +1.650.506.7000
Fax: +1.650.506.7200
oracle.com
Copyright 2011, Oracle and/or its affiliates. All rights reserved. This document is provided for information purposes only and the
contents hereof are subject to change without notice. This document is not warranted to be error-free, nor subject to any other
warranties or conditions, whether expressed orally or implied in law, including implied warranties and conditions of merchantability or
fitness for a particular purpose. We specifically disclaimany liability with respect to this document and no contractual obligations are
formed either directly or indirectly by this document. This document may not be reproduced or transmitted in any formor by any
means, electronic or mechanical, for any purpose, without our prior written permission.
Oracle and J ava are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices.
Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license
and are trademarks or registered trademarks of SPARC International, Inc. UNIX is a registered trademark licensed through X/Open
Company, Ltd. 1010